共計 6954 個字符，預計需要花費 18 分鐘才能閱讀完成。
今天丸趣 TV 小編給大家分享一下 MySQL 的存儲過程實例分析的相關知識點，內容詳細，邏輯清晰，相信大部分人都還太了解這方面的知識，所以分享這篇文章給大家參考一下，希望大家閱讀完這篇文章后有所收獲，下面我們一起來了解一下吧。
MySQL5.0 版本開始支持存儲過程, 存儲過程就是一組 SQL 語句集, 功能強大, 可以實現一些比較復雜的邏輯功能, 類似于 JAVA 語言中的方法, 存儲過就是數據庫 SQL 與層層面的代碼封裝與重用
1. 有輸入輸出參數, 可以聲明變量, 有 if/else/case/while 等控制語句, 通過編寫存儲過程, 可以實現復雜的邏輯功能
2. 函數的普通特性: 模塊化, 封裝, 代碼復用
3. 速度快, 只有首次執(zhí)行需要經過編譯和優(yōu)化步驟, 后續(xù)被調用可以直接執(zhí)行, 省去以上步驟
格式創(chuàng)建存儲過程
-- 創(chuàng)建存儲過程
delimiter $$
create procedure proc01()
begin
select empno,ename from emp;
end $$
delimiter ;
-- 調用存儲過程
call proc01;定義變量局部變量
用戶自定義, 在 begin/end 塊中有效
delimiter $$
create procedure proc02()
begin
declare var_name01 varchar(20) default aaa -- 聲明 / 定義變量
set var_naem01 = zhangsan -- 給變量賦值
select var_name01; -- 輸出變量的值
end $$
dilimiter ;
call proc02();用戶變量
用戶自定義, 當前會話 (連接) 有效. 類比 java 的成員變量
delimiter $$
create procedure proc04()
begin
set @var_name01 = beijing
select @var_name01;
end $$
dilimiter ;
call proc04();
select @var_name01; -- 外部也是可以使用用戶變量系統變量
1. 系統變量有分為全局變量與會話變量
2. 全局變量在 MYSQL 啟動的時候由服務器自動將他的初始化為默認值, 這些默認值可以通過更改 my.ini 這個文件來更改
3. 會話變量在每次建立一個新的連接的時候, 由 MYSQL 來初始化,MYSQL 會將當前所有全局變量的值復制一份, 來作為會話變量
4. 也就是說, 如果在建立會話以后, 沒有手動更改過會話變量與全局變量的值, 那所有這些變量的值都是一樣的
5. 全局變量與會話變量的區(qū)別在于, 對全局變量的修改會影響整個服務器, 但是對會話變量的修改, 只會影響到當前的會話(也就是當前的數據庫連接)
6. 有些系統變量的值是可以利用語句來動態(tài)進行更改的, 但是有些系統變量的值缺是只讀的, 對于那些可以更改的系統變量, 我們可以利用 set 語句進行更改
系統變量 - 全局變量
由系統系統, 在整個數據庫中有效
存儲過程存儲過程傳參 -in
in 表示傳入的參數, 可以傳入數值或者變量, 即使傳入變量, 并不會改變變量的值, 可以內部更改, 僅僅作用在函數范圍內
delimiter $$
create procedure proc06(in param_empno int)
begin
select*from emp where empno = param_empno
end $$
delimiter ;
call proc06(1001);多個參數傳參
存儲過程傳參 -out
out 表示從存儲過程內部傳值給調用者
存儲過程傳參 -inout
inout 表示從外部傳入的參數經過修改后可以返回的變量, 既可以使用傳入變量的值也可以修改變量的值(即使函數執(zhí)行完)
流程控制流程控制 - 判斷
IF 語句包含多個條件判斷, 根據結果為 TURE 和 FALSE 執(zhí)行語句, 與編程語言中的 if、else if、else 語法類似, 其語法格式如下
-- 存儲過程 -if
-- 案例 1
-- 輸入學生的成績, 來判斷成績的級別
score 60: 不及格
score =60 , score 80 : 及格
score =80 , score 90 : 良好
score =90 , score =100 : 優(yōu)秀
score 100 : 成就錯誤
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 成績錯誤
end if;
end $$
delimiter ;
set @score=55;
call proc_12_if(@score)
-- 輸入員工的名字, 判斷工資的情況
sal 10000 : 適用薪資
sal =10000 and sal 20000 : 轉正薪資
sal =20000 : 元老薪資
delimiter $$
create procedure proc_13_if(in in_ename varchar)
begin
declare var_sal decimal(7,2); -- 定義局部變量,7 個長度,2 個小數
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 = 轉正薪資
else set result = 元老薪資
end if;
select result;
end$$
delimiter ;
-- 調用
call proc_13_if(劉備流程控制語句 -case
CASE 是另一個條件判斷語句, 類似于編程語言中的 switch 語法
語法一: 當 case 后面的語句與 when 后面的語句相等時, 則會執(zhí)行 then 后面的語句, 如果均不匹配則執(zhí)行 else
語法二: 直接寫 case 當 when 后面的條件成立則執(zhí)行 then 后面的語句, 如果都不成立, 則執(zhí)行 else
-- 流程控制語句: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);
-- 語法二
-- 輸入學生的成績, 來判斷成績的級別
score 60: 不及格
score =60 , score 80 : 及格
score =80 , score 90 : 良好
score =90 , score =100 : 優(yōu)秀
score 100 : 成就錯誤
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 成績錯誤
end case;
end $$
delimiter ;
call proc_15_case(88);控制流程 - 循環(huán)
概述
循環(huán)是一段在程序中只出現一次, 但可能會連續(xù)運行多次的代碼
循環(huán)中的代碼會運行特定的次數, 或者是運行到特定條件成立時結束循環(huán)
循環(huán)分類
while、repeat、loop
循環(huán)控制
leave 類似于 break, 跳出, 結束當前所在的循環(huán)
iteater 類似于 continue, 繼續(xù), 結束本次循環(huán), 繼續(xù)下一次
while 循環(huán)
-- 需求: 向表中添加指定條數據
-- while
delimiter $$
create procedure proc16_while(in insertCount int)
begin
declare i int default 1; -- 設置循環(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);
-- 需求: 向表中添加指定條數據
-- while+leave
delimiter $$
create procedure proc16_while(in insertCount int)
begin
declare i int default 1; -- 設置循環(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)
格式
-- 存儲過程 - 循環(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 -- 這里不能寫; 表示跳出循環(huán)
end repeat label;
select 循環(huán)結束
end$$
delimiter ;
call proc18_repeat(100);
loop 循環(huán)
-- 存儲過程 - 循環(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()
游標
游標 (cursor) 是庸才存儲查詢結果集的數據類型, 在存儲過程和函數中可以使用光標對結果集進行循環(huán)的處理, 光標的使用包括光標的聲明、OPEN、FETCH、CLOSE
-- 操作游標(cursor)
-- 聲明游標
-- 打開游標
-- 通過游標獲取值
-- 關閉游標
-- 需求: 輸入一個部門名, 查詢該部門員工的編號、名字、薪資, 將查詢的結果集添加游標
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);
-- 聲明游標
declare my_cursor cursor for select empno,ename,sal from dept a,emp b where a.deptno=b.deptno and a.dname=in_dname;
-- 打開游標
open my_cursor;
-- 通過游標獲取值
-- fetch my_cursor into var_empno,var_ename,var_sal;
-- select var_empno,var_ename,var_sal; -- 注意: 以上兩句只可以獲取一個數據, 因為游標是一次一行, 所以采用循環(huán)
label:loop
fetch my_cursor into var_empno,var_ename,var_sal;
select var_empno,var_ename,var_sal;
end loop label;
-- 關閉游標
close my_cursor;
end$$
delimiter ;
call proc19_cursor(銷售部
異常處理 -HANDLER 句柄
MySQL 存儲過程也提供了對異常處理的功能: 通過定義 HANDLER 來完成異常聲明的實現
格式
在語法中, 變量聲明、游標聲明、handler 聲明必須按照先后順序書寫的, 否則創(chuàng)建存儲過程出錯
DECALARE handler_action 創(chuàng)建句柄
FOR 后面為條件值, 為什么異常提供處理
statment 當沒有數據發(fā)現這個異常發(fā)生時采取什么措施
handler_action :{
CONTINUE 繼續(xù)執(zhí)行
EXIT 退出
UNDO 取消(目前 MySQL 不支持)
}
condition_value :{
mysql_error_code 錯誤碼
condition 條件名
SQLWARNING SQL 警告
NOT FOUND 數據沒有發(fā)現
SQLEXCEPTION SQL 異常
}
/* 用于測試句柄, 改造上面游標代碼會出現 1329 錯誤代碼的情況 */
-- 操作游標(cursor)
-- 聲明游標
-- 打開游標
-- 通過游標獲取值
-- 關閉游標
-- 需求: 輸入一個部門名, 查詢該部門員工的編號、名字、薪資, 將查詢的結果集添加游標
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);
-- 定義標記值
declare flag int default 1;
-- 聲明游標
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: 異常處理完之后, 程序應該如何執(zhí)行
continue : 繼續(xù)執(zhí)行剩余代碼
exit: 直接終止程序
undo: 不支持
2: 觸發(fā)條件
條件碼:1329.....
條件名:
SQLWARNING
NOT FOUND
SQLEXCEPTION
3: 異常觸發(fā)之后執(zhí)行什么代碼
設置 flag 的值 - 0
*/
declare continue handler for 1329 set flag=0 ;
-- 打開游標
open my_cursor;
-- 通過游標獲取值
-- fetch my_cursor into var_empno,var_ename,var_sal;
-- select var_empno,var_ename,var_sal; -- 注意: 以上兩句只可以獲取一個數據, 因為游標是一次一行, 所以采用循環(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;
-- 關閉游標
close my_cursor;
end$$
delimiter ;
call proc19_cursor(銷售部
以上就是“MySQL 的存儲過程實例分析”這篇文章的所有內容，感謝各位的閱讀！相信大家閱讀完這篇文章都有很大的收獲，丸趣 TV 小編每天都會為大家更新不同的知識，如果還想學習更多的知識，請關注丸趣 TV 行業(yè)資訊頻道。
