MySQL的存儲(chǔ)過(guò)程實(shí)例解析

2024-07-24 12:32:51

字體：大中小

供稿：網(wǎng)友

　　今天小編給大家分享一下MySQL的存儲(chǔ)過(guò)程實(shí)例分析的相關(guān)知識(shí)點(diǎn)，內(nèi)容詳細(xì)，邏輯清晰，相信大部分人都還太了解這方面的知識(shí)，所以分享這篇文章給大家參考一下，希望大家閱讀完這篇文章后有所收獲，下面我們一起來(lái)了解一下吧。

　　MySQL5.0版本開(kāi)始支持存儲(chǔ)過(guò)程,存儲(chǔ)過(guò)程就是一組SQL語(yǔ)句集,功能強(qiáng)大,可以實(shí)現(xiàn)一些比較復(fù)雜的邏輯功能,類似于JAVA語(yǔ)言中的方法,存儲(chǔ)過(guò)就是數(shù)據(jù)庫(kù)SQL與層層面的代碼封裝與重用

　　1.有輸入輸出參數(shù),可以聲明變量,有if/else/case/while等控制語(yǔ)句,通過(guò)編寫(xiě)存儲(chǔ)過(guò)程,可以實(shí)現(xiàn)復(fù)雜的邏輯功能

　　2.函數(shù)的普通特性:模塊化,封裝,代碼復(fù)用

　　3.速度快,只有首次執(zhí)行需要經(jīng)過(guò)編譯和優(yōu)化步驟,后續(xù)被調(diào)用可以直接執(zhí)行,省去以上步驟

　　格式

　　創(chuàng)建存儲(chǔ)過(guò)程

　　-- 創(chuàng)建存儲(chǔ)過(guò)程

　　delimiter $$

　　create procedure proc01()

　　begin

　　 select empno,ename from emp;

　　end $$

　　delimiter ;

　　-- 調(diào)用存儲(chǔ)過(guò)程

　　call proc01;

　　定義變量

　　局部變量

　　用戶自定義,在begin/end塊中有效

　　MySQL的存儲(chǔ)過(guò)程實(shí)例分析

　　delimiter $$

　　create procedure proc02()

　　begin

　　 declare var_name01 varchar(20) default 'aaa'; -- 聲明/定義變量

　　 set var_naem01 = 'zhangsan'; -- 給變量賦值

　　 select var_name01; -- 輸出變量的值

　　end $$

　　dilimiter ;

　　call proc02();

　　用戶變量

　　用戶自定義,當(dāng)前會(huì)話(連接)有效.類比java的成員變量

　　delimiter $$

　　create procedure proc04()

　　begin

　　 set @var_name01 = 'beijing';

　　 select @var_name01;

　　end $$

　　dilimiter ;

　　call proc04();

　　select @var_name01; -- 外部也是可以使用用戶變量

　　系統(tǒng)變量

　　1.系統(tǒng)變量有分為全局變量與會(huì)話變量

　　2.全局變量在MYSQL啟動(dòng)的時(shí)候由服務(wù)器自動(dòng)將他的初始化為默認(rèn)值,這些默認(rèn)值可以通過(guò)更改my.ini這個(gè)文件來(lái)更改

　　3.會(huì)話變量在每次建立一個(gè)新的連接的時(shí)候,由MYSQL來(lái)初始化,MYSQL會(huì)將當(dāng)前所有全局變量的值復(fù)制一份,來(lái)作為會(huì)話變量

　　4.也就是說(shuō),如果在建立會(huì)話以后,沒(méi)有手動(dòng)更改過(guò)會(huì)話變量與全局變量的值,那所有這些變量的值都是一樣的

　　5.全局變量與會(huì)話變量的區(qū)別在于,對(duì)全局變量的修改會(huì)影響整個(gè)服務(wù)器,但是對(duì)會(huì)話變量的修改,只會(huì)影響到當(dāng)前的會(huì)話(也就是當(dāng)前的數(shù)據(jù)庫(kù)連接)

　　6.有些系統(tǒng)變量的值是可以利用語(yǔ)句來(lái)動(dòng)態(tài)進(jìn)行更改的,但是有些系統(tǒng)變量的值缺是只讀的,對(duì)于那些可以更改的系統(tǒng)變量,我們可以利用set語(yǔ)句進(jìn)行更改

　　系統(tǒng)變量-全局變量

　　由系統(tǒng)系統(tǒng),在整個(gè)數(shù)據(jù)庫(kù)中有效

　　存儲(chǔ)過(guò)程

　　存儲(chǔ)過(guò)程傳參-in

　　in表示傳入的參數(shù),可以傳入數(shù)值或者變量,即使傳入變量,并不會(huì)改變變量的值,可以內(nèi)部更改,僅僅作用在函數(shù)范圍內(nèi)

　　delimiter $$

　　create procedure proc06(in param_empno int)

　　begin

　　 select*from emp where empno = param_empno

　　end $$

　　delimiter ;

　　call proc06(1001);

　　多個(gè)參數(shù)傳參

　　存儲(chǔ)過(guò)程傳參-out

　　out表示從存儲(chǔ)過(guò)程內(nèi)部傳值給調(diào)用者

　　存儲(chǔ)過(guò)程傳參-inout

　　inout表示從外部傳入的參數(shù)經(jīng)過(guò)修改后可以返回的變量,既可以使用傳入變量的值也可以修改變量的值(即使函數(shù)執(zhí)行完)

　　流程控制

　　流程控制-判斷

　　IF語(yǔ)句包含多個(gè)條件判斷,根據(jù)結(jié)果為TURE和FALSE執(zhí)行語(yǔ)句,與編程語(yǔ)言中的if、else if、else語(yǔ)法類似,其語(yǔ)法格式如下

　　-- 存儲(chǔ)過(guò)程-if

　　-- 案例1

　　-- 輸入學(xué)生的成績(jī),來(lái)判斷成績(jī)的級(jí)別

　　/*

　　score<60:不及格

　　score>=60 , score<80 :及格

　　score>=80 , score<90 :良好

　　score>=90 , score<=100 :優(yōu)秀

　　score>100 :成就錯(cuò)誤

　　*/

　　delimiter $$

　　create procedure proc_12_if (in score int)

　　begin

　　 if score<60 then select '不及格';

　　 elseif score>=60 and score<80 then select '及格';

　　 elseif score>=80 and score<90 then select '良好';

　　 elseif score>=90 and score<=100 then select '優(yōu)秀';

　　 else select '成績(jī)錯(cuò)誤'

　　 end if;

　　end $$

　　delimiter ;

　　set @score=55;

　　call proc_12_if(@score)

　　-- 輸入員工的名字,判斷工資的情況

　　/*

　　sal<10000 :適用薪資

　　sal>=10000 and sal<20000 :轉(zhuǎn)正薪資

　　sal>=20000 :元老薪資

　　*/

　　delimiter $$

　　create procedure proc_13_if(in in_ename varchar)

　　begin

　　 declare var_sal decimal(7,2); -- 定義局部變量,7個(gè)長(zhǎng)度,2個(gè)小數(shù)

　　 declare result varchar(20);

　　 select sal into var_sal from emp where ename=in_eanme; -- 將值賦給局部變量

　　 if var_sal <10000 then set result ='適用薪資';

　　 elseif var_sal <20000 then set result ='轉(zhuǎn)正薪資';

　　 else set result ='元老薪資';

　　 end if;

　　 select result;

　　end$$

　　delimiter ;

　　-- 調(diào)用

　　call proc_13_if('劉備');

　　流程控制語(yǔ)句-case

　　CASE是另一個(gè)條件判斷語(yǔ)句,類似于編程語(yǔ)言中的switch語(yǔ)法

　　語(yǔ)法一:當(dāng)case后面的語(yǔ)句與when后面的語(yǔ)句相等時(shí),則會(huì)執(zhí)行then后面的語(yǔ)句,如果均不匹配則執(zhí)行else

　　語(yǔ)法二:直接寫(xiě)case 當(dāng)when后面的條件成立則執(zhí)行then后面的語(yǔ)句,如果都不成立,則執(zhí)行else

　　-- 流程控制語(yǔ)句:case

　　/*

　　支付方式:

　　 1.微信支付

　　 2.支付寶支付

　　 3.銀行卡支付

　　 4.其他支付

　　*/

　　-- 格式1

　　delimiter $$

　　create procedure proc14_case(in pay_type int)

　　begin

　　 case pay_type

　　 when 1 then select '微信支付';

　　 when 2 then select '支付寶支付';

　　 when 3 then select '銀行卡支付';

　　 else select '其他方式支付';

　　 end case;

　　end$$

　　delimiter ;

　　call proc14_case(2);

　　call proc14_case(3);

　　-- 語(yǔ)法二

　　-- 輸入學(xué)生的成績(jī),來(lái)判斷成績(jī)的級(jí)別

　　/*

　　score<60:不及格

　　score>=60 , score<80 :及格

　　score>=80 , score<90 :良好

　　score>=90 , score<=100 :優(yōu)秀

　　score>100 :成就錯(cuò)誤

　　*/

　　delimiter $$

　　create procedure proc_15_case (in score int)

　　begin

　　 case

　　 when score<60 then select '不及格';

　　 when score>=60 and score<80 then select '及格';

　　 when score>=80 and score<90 then select '良好';

　　 when score>=90 and score<=100 then select '優(yōu)秀';

　　 else select '成績(jī)錯(cuò)誤'

　　 end case;

　　end $$

　　delimiter ;

　　call proc_15_case(88);

　　控制流程-循環(huán)

　　概述

　　循環(huán)是一段在程序中只出現(xiàn)一次,但可能會(huì)連續(xù)運(yùn)行多次的代碼

　　循環(huán)中的代碼會(huì)運(yùn)行特定的次數(shù),或者是運(yùn)行到特定條件成立時(shí)結(jié)束循環(huán)

　　循環(huán)分類

　　while、repeat、loop

　　循環(huán)控制

　　leave 類似于break,跳出,結(jié)束當(dāng)前所在的循環(huán)

　　iteater 類似于continue,繼續(xù),結(jié)束本次循環(huán),繼續(xù)下一次

　　while循環(huán)

　　-- 需求:向表中添加指定條數(shù)據(jù)

　　-- while

　　delimiter $$

　　create procedure proc16_while(in insertCount int)

　　begin

　　 declare i int default 1; -- 設(shè)置循環(huán)的增量

　　 label:while i<=insertCount do insert into user(id,username,password) values(i,concat('user-',i),'123456');

　　 set i=i+1;

　　end while label;

　　end$$

　　delimiter ;

　　call proc16_while(10);

　　-- 需求:向表中添加指定條數(shù)據(jù)

　　-- while+leave

　　delimiter $$

　　create procedure proc16_while(in insertCount int)

　　begin

　　 declare i int default 1; -- 設(shè)置循環(huán)的增量

　　 label:while i<=insertCount do insert into user(id,username,password) values(i,concat('user-',i),'123456');

　　 if i=5 then leave label;   -- 如果i=5則跳出循環(huán)

　　 set i=i+1;

　　end while label;

　　end$$

　　delimiter ;

　　call proc16_while(10);

　　repeat循環(huán)

　　格式

　　-- 存儲(chǔ)過(guò)程-循環(huán)控制-repeat

　　delimiter $$

　　create procedure proc18_repaet(in insertCount int)

　　begin

　　 declare i int default 1;

　　 label:repeat

　　 insert into user(uid,username,password)values(i,concat('user-',i),'123456');

　　 set i = i+1;

　　 until i>inserCount -- 這里不能寫(xiě); 表示跳出循環(huán)

　　 end repeat label;

　　 select '循環(huán)結(jié)束';

　　end$$

　　delimiter ;

　　call proc18_repeat(100);

　　loop循環(huán)

　　-- 存儲(chǔ)過(guò)程-循環(huán)控制-loop

　　delimiter $$

　　create procedure proc19_loop(in insertCount int)

　　begin

　　 declare i default 1;

　　 label:loop insert into user (uid,username,password) values (i,concat('user-')i,'123456');

　　 set i= i+1;

　　 if i>insertCount then leave label;

　　 end if;

　　 end loop label;

　　end$$

　　delimiter ;

　　call proc19_loop()

　　游標(biāo)

　　游標(biāo)(cursor)是庸才存儲(chǔ)查詢結(jié)果集的數(shù)據(jù)類型,在存儲(chǔ)過(guò)程和函數(shù)中可以使用光標(biāo)對(duì)結(jié)果集進(jìn)行循環(huán)的處理,光標(biāo)的使用包括光標(biāo)的聲明、OPEN、FETCH、CLOSE

　　-- 操作游標(biāo)(cursor)

　　-- 聲明游標(biāo)

　　-- 打開(kāi)游標(biāo)

　　-- 通過(guò)游標(biāo)獲取值

　　-- 關(guān)閉游標(biāo)

　　-- 需求:輸入一個(gè)部門名,查詢?cè)摬块T員工的編號(hào)、名字、薪資,將查詢的結(jié)果集添加游標(biāo)

　　delimiter $$

　　create procedure proc19_cursor(in in_danme varchar(50))

　　begin

　　 -- 定義局部變量

　　 declare var_empno int;

　　 declare var_ename varchar(50);

　　 declare var_sal decimal(7,2);

　　 -- 聲明游標(biāo)

　　 declare my_cursor cursor for select empno,ename,sal from dept a,emp b where a.deptno=b.deptno and a.dname=in_dname;

　　 -- 打開(kāi)游標(biāo)

　　 open my_cursor;

　　 -- 通過(guò)游標(biāo)獲取值

　　-- fetch my_cursor into var_empno,var_ename,var_sal;

　　-- select var_empno,var_ename,var_sal; -- 注意:以上兩句只可以獲取一個(gè)數(shù)據(jù),因?yàn)橛螛?biāo)是一次一行,所以采用循環(huán)

　　 label:loop

　　 fetch my_cursor into var_empno,var_ename,var_sal;

　　 select var_empno,var_ename,var_sal;

　　 end loop label;

　　 -- 關(guān)閉游標(biāo)

　　 close my_cursor;

　　end$$

　　delimiter ;

　　call proc19_cursor('銷售部');

　　異常處理-HANDLER句柄

　　MySQL存儲(chǔ)過(guò)程也提供了對(duì)異常處理的功能:通過(guò)定義HANDLER來(lái)完成異常聲明的實(shí)現(xiàn)

　　格式

　　在語(yǔ)法中,變量聲明、游標(biāo)聲明、handler聲明必須按照先后順序書(shū)寫(xiě)的,否則創(chuàng)建存儲(chǔ)過(guò)程出錯(cuò)

　　DECALARE handler_action 創(chuàng)建句柄

　　FOR 后面為條件值,為什么異常提供處理

　　statment 當(dāng)沒(méi)有數(shù)據(jù)發(fā)現(xiàn)這個(gè)異常發(fā)生時(shí)采取什么措施

　　handler_action :{

　　CONTINUE    繼續(xù)執(zhí)行

　　EXIT 退出

　　UNDO 取消(目前MySQL不支持)

　　}

　　condition_value :{

　　mysql_error_code 錯(cuò)誤碼

　　condition 條件名

　　SQLWARNING   SQL警告

　　NOT FOUND 數(shù)據(jù)沒(méi)有發(fā)現(xiàn)

　　SQLEXCEPTION SQL異常

　　}

　　/*用于測(cè)試句柄,改造上面游標(biāo)代碼會(huì)出現(xiàn)1329錯(cuò)誤代碼的情況*/

　　-- 操作游標(biāo)(cursor)

　　-- 聲明游標(biāo)

　　-- 打開(kāi)游標(biāo)

　　-- 通過(guò)游標(biāo)獲取值

　　-- 關(guān)閉游標(biāo)

　　-- 需求:輸入一個(gè)部門名,查詢?cè)摬块T員工的編號(hào)、名字、薪資,將查詢的結(jié)果集添加游標(biāo)

　　delimiter $$

　　create procedure proc21_cursor_handler(in in_danme varchar(50))

　　begin

　　 -- 定義局部變量

　　 declare var_empno int;

　　 declare var_ename varchar(50);

　　 declare var_sal decimal(7,2);

　　 -- 定義標(biāo)記值

　　 declare flag int default 1;

　　 -- 聲明游標(biāo)

　　 declare my_cursor cursor for select empno,ename,sal from dept a,emp b where a.deptno=b.deptno and a.dname=in_dname;

　　 -- 定義句柄:定義異常的處理方式

　　 /*

　　 1:異常處理完之后,程序應(yīng)該如何執(zhí)行

　　 continue :繼續(xù)執(zhí)行剩余代碼

　　 exit:直接終止程序

　　 undo:不支持

　　 2:觸發(fā)條件

　　條件碼:1329.....

　　條件名:

　　 SQLWARNING

　　 NOT FOUND

　　 SQLEXCEPTION

　　 3:異常觸發(fā)之后執(zhí)行什么代碼

　　設(shè)置flag的值 -> 0

　　 */

　　 declare continue handler for 1329 set flag=0 ;

　　 -- 打開(kāi)游標(biāo)

　　 open my_cursor;

　　 -- 通過(guò)游標(biāo)獲取值

　　-- fetch my_cursor into var_empno,var_ename,var_sal;

　　-- select var_empno,var_ename,var_sal; -- 注意:以上兩句只可以獲取一個(gè)數(shù)據(jù),因?yàn)橛螛?biāo)是一次一行,所以采用循環(huán)

　　 label:loop

　　 fetch my_cursor into var_empno,var_ename,var_sal;

　　 -- 判斷flag的值如果為1則執(zhí)行,否則不執(zhí)行

　　 if flag =1 then

　　 select var_empno,var_ename,var_sal;

　　 else leave label;

　　 end if;

　　 end loop label;

　　 -- 關(guān)閉游標(biāo)

　　 close my_cursor;

　　end$$

　　delimiter ;

　　call proc19_cursor('銷售部');

　　以上就是“MySQL的存儲(chǔ)過(guò)程實(shí)例分析”這篇文章的所有內(nèi)容，感謝各位的閱讀！

（編輯：武林網(wǎng)）

上一篇：在mysql中有沒(méi)有嵌套查詢語(yǔ)句

下一篇：mysql的log文件要怎么看