MySQL數據庫下存儲過程的綜合運用研究

摘? 要：在數據庫設計過程中，僅用存儲過程一種對象很難解決復雜問題。文章提出將游標、定義處理程序和流程控制語句3項內容融合應用于存儲過程中以解決復雜問題；存儲過程是數據庫中的重要對象，同時也是這3項內容的載體，3項內容的綜合運用非常關鍵，除了有助于緩解數據庫中數據的復雜操作，還可以把數據表中大量的數據單獨提取出來應用到其他代碼段或文件中，從而解決數據庫中的復雜問題。

關鍵詞：存儲過程；游標；處理程序

中圖分類號：TP311.1? 文獻標識碼：A? 文章編號：2096-4706（2023）11-0080-04

Research on the Comprehensive Application of Stored Procedures in MySQL Database

LI Yanjie

（School of Information Engineering， Shandong Huayu University of Technology， Dezhou? 253034， China）

Abstract： In the process of database design， it is difficult to solve complex problems with only one object， stored procedures. This paper proposes to integrate cursor， definition processing program and process control statement into stored procedures to solve complex problems; Stored procedures are important objects in databases and also carriers of these three contents. The comprehensive application of these three contents is crucial. In addition to helping to alleviate the complex operation of data in the database， a large amount of data in the data table can be extracted separately and applied to other code segments or files， thereby solving complex problems in the database.

Keywords： stored procedure; cursor; processing program

0? 引? 言

在MySQL數據庫下，存儲過程是非常重要的一項內容，但要發揮存儲過程的重要作用，必須讓存儲過程結合游標、處理程序、流程控制語句對數據進行處理，這樣既能發揮出游標和處理程序的優勢，也能體現流程控制語句在數據庫中的應用。利用數據庫開發信息系統或開發網站平臺時，開發人員會編寫大量代碼，有些功能是相似的，代碼會重復編寫，浪費開發人員的時間，也會增加代碼的冗余，如果利用存儲過程，則可以簡化開發人員的工作量，并能減少數據在數據庫和應用服務器之間的傳輸，從而有效提高數據庫的處理速度，還可以提高數據庫編程的靈活性。

1? 相關概念

1.1? 存儲過程簡介

存儲過程是一批被編譯了的語句的集合，存儲在數據庫的服務器端，用戶僅需要通過指定存儲過程名稱來執行操作。存儲過程具有良好的封裝性，被創建之后，可在程序中被多次調用，而不必重新編寫該存儲過程中的SQL語句，后臺管理人員可以隨時對存儲過程進行修改，并不會影響到調用存儲過程的應用程序源代碼，在存儲過程中可以加入流程控制語句，類似具有了C語言程序設計的功能，可以解決數據庫編程中的復雜問題。

存儲過程的優點是可以處理復雜問題，并且能提高執行的性能，因為在服務器端，由于執行完1次之后，其執行過程就會存放在緩存中，后面的多次調用執行，僅需要執行緩存中的二進制代碼即可，既提高了性能又節約了時間。

1.2? 游標簡介

游標是用來存儲結果集的數據類型，用SQL語言從數據庫中查詢數據后，結果往往是一個含有多條記錄的結果集，它放在內存的一塊區域中，游標會通過循環結構，允許用戶逐行地訪問這些記錄，按照用戶自己的意愿來顯示和處理每一條記錄。游標不能單獨使用，可以在存儲過程或函數中使用。使用游標設計程序時，必須有4個步驟：聲明游標、打開游標、獲取數據、關閉游標。聲明游標是開辟空間并存儲查詢結果集，此時游標在第一條記錄的前面，打開游標是讓游標指向查詢結果集的第一條記錄，獲取數據是從結果集中獲取單條記錄，獲取此條記錄后，游標自動指向下一條記錄。關閉游標是釋放資源，無法再獲取數據。

1.3? 處理程序簡介

處理程序用于解決數據庫中的錯誤，由于錯誤在執行程序時是不確定的，當有錯誤出現時，需要通過處理程序解決，從而保證程序正常運行。當數據表中的記錄數不確定的時候，如果用游標來獲取單行數據，需要利用循環語句實現。由于記錄條數不確定，導致循環次數也不確定，此時無法寫出退出循環的條件語句，所以需要用事先定義好的處理程序自動處理問題。

2? 綜合運用設計

2.1? 設計基礎表和條件

存儲過程在處理數據量不同的數據時是無差別的，在此設計問題時，設計的問題并不復雜，但是解決問題都需要用到存儲過程、游標、處理程序和流程控制語句，通過簡單的問題簡述復雜的應用。在此建立兩個簡單的成績表score和score1，分別包含姓名和分數兩列，代碼為：

create table score（name char（10），fsh float）;

insert into score values（'zhaoli'，82），（'sunyu'， 50），（'liqiang'，95）;

create table score1（name char（10），fsh float）;--無記錄

具體要求：逐行獲取第一個score表中的數據，把score表中的分數大于80的記錄插入到第二個表score1中，并驗證代碼的正確性。

2.2? 分析思路

1）把表中的記錄逐行取出，解決此問題需利用游標取出表中的數據，并利用變量進行存儲。如果單純用select語句查詢數據，僅可以看到所有的查詢結果，并且結果集并不能被存儲到其他數據表中。

2）利用游標取數據的同時需要定義問題處理程序，當游標獲取不到數據的時候對問題進行處理，此時定義處理程序也需要用到變量判定是否發現了問題。

3）由于是逐行獲取數據，為了提高程序的可讀性和簡化代碼，需利用流程控制語句中的循環結構，通過循環去匹配游標定位到數據表中的逐行記錄。此時一定要考慮循環的次數，避免出現死循環。所有循環結束的判定條件要和定義處理程序進行結合，通過定義處理程序的變量獲得退出循環的條件。

4）為了能夠把獲取到的數據添加到第二個表中，還需要判定存放到變量中的記錄的值是否符合條件，利用條件判斷語句解決問題。

5）需要把各個代碼段集合成一個整體去執行，需要存儲過程解決此問題。

2.3? 代碼實現及錯誤問題分析

對于沒有經驗的初學者而言，通常會按照以上思路直接寫出以下代碼：

delimiter //

create procedure cc1（）--第1行定義存儲過程。

Begin--第2行和第16行是開始和結束的代碼段。

declare f float default 0;

declare x char（10）;--第3行和第4行定義變量用于存放游標從數據表中取出的每一條記錄的兩個值。

declare t int default 0;--第5行用于給定義處理程序的變量賦值為0。

declare c cursor for select * from score;--第6行為聲明游標。

declare continue handler for not found set t=1;--第7行代表定義處理程序，當不能獲取數據的時候，此時設置變量t為1，從而控制循環的退出。

open c;--第8行代表打開游標。

while t<>1 do--第9-13行代表循環控制語句，通過循環取出數據并進行條件判定，符合條件的存儲到score1表中。

fetch c into x，f; --第10行代表獲取表中數據

if f>80 then insert into score1 values（x，f）;

end if;

end while;

close c;--第14行關閉游標

select * from score1;--第15行代表查看存入的數據，以驗證代碼是否正確。

end//，

通過編譯可以看出，代碼編譯過程無語法錯誤，提示正確，如圖1所示。

下面執行存儲過程，會發現雖然編譯正確，但是執行結果是錯誤的。根據建立數據表時輸入的3條記錄判斷，應該有2條記錄符合條件，但這里顯示了3條記錄，如圖2所示。

通過以上執行結果發現，score1表中的最后兩行是重復的，正確結果應該不重復，表中應該有兩條符合條件的記錄，即第1條和第2條記錄。錯誤原因分析如下：

由于score表中有3條記錄，根據定義的處理程序和循環結構，會循環4次去提取數據，每次提取完成之后，會把一條記錄中的兩個值賦值給變量x和f，在第4次循環取數據時，由于沒有記錄可以獲取，此時，變量t設置為1，所以第4次并未取到值給變量，但是x和f的值是保留了第3次取數據時所賦給的值，并且符合大于80的條件，此時又把此條記錄加入score2表中。又因為定義處理程序時，declare后面的關鍵字是continue，當處理程序發現問題后，程序會繼續執行，所以score2表中出現了重復的記錄。如何解決這個問題，僅需要在提取到數據的第10行代碼后面加入條件即可。如圖3所示。

調用修改后的存儲過程cc2，可以得出正確的執行結果，代碼和結果如圖4所示。

2.4? 優化后正確代碼

針對存儲過程、游標、定義處理的應用，以上代碼已經是最簡潔狀態，無法進行優化，但在while循環結構中，有兩個if條件的嵌套，使得代碼的行數和可讀性減弱，可以在此基礎上對代碼進行優化，兩個if條件合并成一個if條件。結果不變，但可以提高代碼的可讀性，如圖5所示。

在以上代碼中，針對定義處理程序的語法結構，declare參數1 handler for參數2SQL語句。

參數1：exit退出當前程序，continue繼續執行程序。

參數2：not found代表當fetch抓取不到數據的狀態，或者游標指針走到最后一條記錄后面的狀態。

SQL語句：set temp=1，temp是變量，必須提前聲明，其值只能是0（false）或者1（true）。在上面的代碼中，用到的參數為continue，continue所代表的含義是當程序出現問題時，定義的處理程序起作用，并且代碼繼續執行，也可以用exit實現，此時代碼不再執行，直接跳出存儲過程。除此之外，對于循環中的條件和循環而言，也可以用其他循環結構實現，提高代碼的靈活性。代碼為：

delimiter //

create procedure cc4（）

begin

declare f float default 0;

declare x char（10）;

declare t int default 0;

declare c cursor for select * from score? where fsh>80;--把循環結構中的if條件編輯到查詢語句中，提升代碼的可讀性。

declare exit handler for not found set t=1;--此處用exit代替continue，當條件成立的時候，退出整個存儲過程。

open c;

repeat–此處用repeat循環代替while循環。

fetch c into x，f;

insert into score1 values（x，f）;

until t=1

end repeat;

close c;

select * from score1;--此行可以去掉，因為是exit，循環截止的時候，直接結束程序運行，不會執行此行代碼。

end//

3? 結? 論

在設計數據庫的過程中，需要根據內容選擇合適的數據庫對象，在選擇之后，還要考慮此數據庫對象所需要加載的其他內容，例如僅創建一個存儲過程很簡單，但僅能解決簡單問題，遇到復雜問題或者數據表中的數據量特別大的情況，就需要考慮知識的綜合運用，運用變量、游標、循環結構、條件結構、處理程序等多項內容，進而解決復雜問題。此方案以簡單數據表為例進行描述，可為基于MySQL數據庫的信息系統或信息平臺提供借鑒，以解決實際問題。在后續的研究中，將進一步優化案例設計，選擇多個數據表的大數據量展開對比，擬通過此種方式，進一步挖掘綜合運用方面的優勢，提供更寶貴的經驗借鑒。

參考文獻：

[1] 沈黎，張本文，雷申洪.存儲過程在教務管理系統中的應用研究 [J].軟件，2022，43（7）：13-15.

[2] 譚凱中，秦勃，何亞文.面向過程的海洋時空數據分布式存儲與并行檢索 [J].中國海洋大學學報：自然科學版，2021，51（11）：94-101+134.

[3] 余艷，劉云冰，邢遠秀.信息與計算科學專業數據庫課程實驗教學探索 [J].計算機教育，2023（1）：176-179+183.

[4] 張潤，方繼才.基于SQL游標的數據庫應用與探析 [J].智能計算機與應用，2016，6（6）：84-86+89.

[5] 李治君，周俊杰，范延平，等.國家級國土空間基礎信息平臺分布式數據庫設計與實現 [J].自然資源信息化，2022（5）：80-85.

作者簡介：李艷杰（1978—），女，漢族，山東德州人，副教授，碩士，主要研究方向：數據挖掘技術。

收稿日期：2023-01-17

基金項目：大數據與智能信息處理研究中心建設項目