MySQL數(shù)據(jù)庫下存儲(chǔ)過程的綜合運(yùn)用研究

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

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

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

1? 相關(guān)概念

1.1? 存儲(chǔ)過程簡(jiǎn)介

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

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

1.2? 游標(biāo)簡(jiǎn)介

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

1.3? 處理程序簡(jiǎn)介

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

2? 綜合運(yùn)用設(shè)計(jì)

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

存儲(chǔ)過程在處理數(shù)據(jù)量不同的數(shù)據(jù)時(shí)是無差別的，在此設(shè)計(jì)問題時(shí)，設(shè)計(jì)的問題并不復(fù)雜，但是解決問題都需要用到存儲(chǔ)過程、游標(biāo)、處理程序和流程控制語句，通過簡(jiǎn)單的問題簡(jiǎn)述復(fù)雜的應(yīng)用。在此建立兩個(gè)簡(jiǎn)單的成績表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）;--無記錄

具體要求：逐行獲取第一個(gè)score表中的數(shù)據(jù)，把score表中的分?jǐn)?shù)大于80的記錄插入到第二個(gè)表score1中，并驗(yàn)證代碼的正確性。

2.2? 分析思路

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

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

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

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

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

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

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

delimiter //

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

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

declare f float default 0;

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

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í)候，此時(shí)設(shè)置變量t為1，從而控制循環(huán)的退出。

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

while t<>1 do--第9-13行代表循環(huán)控制語句，通過循環(huán)取出數(shù)據(jù)并進(jìn)行條件判定，符合條件的存儲(chǔ)到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ù)，以驗(yàn)證代碼是否正確。

end//，

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

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

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

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

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

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

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

在以上代碼中，針對(duì)定義處理程序的語法結(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)問題時(shí)，定義的處理程序起作用，并且代碼繼續(xù)執(zhí)行，也可以用exit實(shí)現(xiàn)，此時(shí)代碼不再執(zhí)行，直接跳出存儲(chǔ)過程。除此之外，對(duì)于循環(huán)中的條件和循環(huán)而言，也可以用其他循環(huán)結(jié)構(gòu)實(shí)現(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)條件成立的時(shí)候，退出整個(gè)存儲(chǔ)過程。

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;--此行可以去掉，因?yàn)槭莈xit，循環(huán)截止的時(shí)候，直接結(jié)束程序運(yùn)行，不會(huì)執(zhí)行此行代碼。

end//

3? 結(jié)? 論

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

參考文獻(xiàn)：

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

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

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

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

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

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

收稿日期：2023-01-17

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