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

關(guān)鍵詞：存儲過程；游標(biāo)；處理程序

中圖分類號：TP311.1? 文獻(xiàn)標(biāo)識碼：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數(shù)據(jù)庫下，存儲過程是非常重要的一項內(nèi)容，但要發(fā)揮存儲過程的重要作用，必須讓存儲過程結(jié)合游標(biāo)、處理程序、流程控制語句對數(shù)據(jù)進(jìn)行處理，這樣既能發(fā)揮出游標(biāo)和處理程序的優(yōu)勢，也能體現(xiàn)流程控制語句在數(shù)據(jù)庫中的應(yīng)用。利用數(shù)據(jù)庫開發(fā)信息系統(tǒng)或開發(fā)網(wǎng)站平臺時，開發(fā)人員會編寫大量代碼，有些功能是相似的，代碼會重復(fù)編寫，浪費開發(fā)人員的時間，也會增加代碼的冗余，如果利用存儲過程，則可以簡化開發(fā)人員的工作量，并能減少數(shù)據(jù)在數(shù)據(jù)庫和應(yīng)用服務(wù)器之間的傳輸，從而有效提高數(shù)據(jù)庫的處理速度，還可以提高數(shù)據(jù)庫編程的靈活性。

1? 相關(guān)概念

1.1? 存儲過程簡介

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

存儲過程的優(yōu)點是可以處理復(fù)雜問題，并且能提高執(zhí)行的性能，因為在服務(wù)器端，由于執(zhí)行完1次之后，其執(zhí)行過程就會存放在緩存中，后面的多次調(diào)用執(zhí)行，僅需要執(zhí)行緩存中的二進(jìn)制代碼即可，既提高了性能又節(jié)約了時間。

1.2? 游標(biāo)簡介

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

1.3? 處理程序簡介

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

2? 綜合運用設(shè)計

2.1? 設(shè)計基礎(chǔ)表和條件

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

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表中的數(shù)據(jù)，把score表中的分?jǐn)?shù)大于80的記錄插入到第二個表score1中，并驗證代碼的正確性。

2.2? 分析思路

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

2）利用游標(biāo)取數(shù)據(jù)的同時需要定義問題處理程序，當(dāng)游標(biāo)獲取不到數(shù)據(jù)的時候?qū)栴}進(jìn)行處理，此時定義處理程序也需要用到變量判定是否發(fā)現(xiàn)了問題。

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

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

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

2.3? 代碼實現(xiàn)及錯誤問題分析

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

delimiter //

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

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

declare f float default 0;

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

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

declare c cursor for select * from score;--第6行為聲明游標(biāo)。

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

open c;--第8行代表打開游標(biāo)。

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

fetch c into x，f; --第10行代表獲取表中數(shù)據(jù)

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

end if;

end while;

close c;--第14行關(guān)閉游標(biāo)

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

end//，

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

下面執(zhí)行存儲過程，會發(fā)現(xiàn)雖然編譯正確，但是執(zhí)行結(jié)果是錯誤的。根據(jù)建立數(shù)據(jù)表時輸入的3條記錄判斷，應(yīng)該有2條記錄符合條件，但這里顯示了3條記錄，如圖2所示。

通過以上執(zhí)行結(jié)果發(fā)現(xiàn)，score1表中的最后兩行是重復(fù)的，正確結(jié)果應(yīng)該不重復(fù)，表中應(yīng)該有兩條符合條件的記錄，即第1條和第2條記錄。錯誤原因分析如下：

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

調(diào)用修改后的存儲過程cc2，可以得出正確的執(zhí)行結(jié)果，代碼和結(jié)果如圖4所示。

2.4? 優(yōu)化后正確代碼

針對存儲過程、游標(biāo)、定義處理的應(yīng)用，以上代碼已經(jīng)是最簡潔狀態(tài)，無法進(jìn)行優(yōu)化，但在while循環(huán)結(jié)構(gòu)中，有兩個if條件的嵌套，使得代碼的行數(shù)和可讀性減弱，可以在此基礎(chǔ)上對代碼進(jìn)行優(yōu)化，兩個if條件合并成一個if條件。結(jié)果不變，但可以提高代碼的可讀性，如圖5所示。

在以上代碼中，針對定義處理程序的語法結(jié)構(gòu)，declare參數(shù)1 handler for參數(shù)2SQL語句。

參數(shù)1：exit退出當(dāng)前程序，continue繼續(xù)執(zhí)行程序。

參數(shù)2：not found代表當(dāng)fetch抓取不到數(shù)據(jù)的狀態(tài)，或者游標(biāo)指針走到最后一條記錄后面的狀態(tài)。

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

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;--把循環(huán)結(jié)構(gòu)中的if條件編輯到查詢語句中，提升代碼的可讀性。

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

open c;

repeat–此處用repeat循環(huán)代替while循環(huán)。

fetch c into x，f;

insert into score1 values（x，f）;

until t=1

end repeat;

close c;

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

end//

3? 結(jié)? 論

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

參考文獻(xiàn)：

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

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

[3] 余艷，劉云冰，邢遠(yuǎn)秀.信息與計算科學(xué)專業(yè)數(shù)據(jù)庫課程實驗教學(xué)探索 [J].計算機(jī)教育，2023（1）：176-179+183.

[4] 張潤，方繼才.基于SQL游標(biāo)的數(shù)據(jù)庫應(yīng)用與探析 [J].智能計算機(jī)與應(yīng)用，2016，6（6）：84-86+89.

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

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

收稿日期：2023-01-17

基金項目：大數(shù)據(jù)與智能信息處理研究中心建設(shè)項目