站長資訊網本篇文章給大家帶來了關于mysql的相關知識，其中主要介紹了關于存儲過程的相關內容，包括了存儲過程傳參、流程控制、以及游標和異常處理等問題，下面一起來看一下，希望對大家有幫助。
推薦學習：mysql視頻教程
MySQL5.0版本開始支持存儲過程,存儲過程就是一組SQL語句集,功能強大,可以實現一些比較復雜的邏輯功能,類似于JAVA語言中的方法,存儲過就是數據庫SQL與層層面的代碼封裝與重用
1.有輸入輸出參數,可以聲明變量,有if/else/case/while等控制語句,通過編寫存儲過程,可以實現復雜的邏輯功能
2.函數的普通特性:模塊化,封裝,代碼復用
3.速度快,只有首次執行需要經過編譯和優化步驟,后續被調用可以直接執行,省去以上步驟
格式
創建存儲過程
-- 創建存儲過程 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.全局變量與會話變量的區別在于,對全局變量的修改會影響整個服務器,但是對會話變量的修改,只會影響到當前的會話(也就是當前的數據庫連接)
6.有些系統變量的值是可以利用語句來動態進行更改的,但是有些系統變量的值缺是只讀的,對于那些可以更改的系統變量,我們可以利用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表示從外部傳入的參數經過修改后可以返回的變量,既可以使用傳入變量的值也可以修改變量的值(即使函數執行完)
流程控制
流程控制-判斷
IF語句包含多個條件判斷,根據結果為TURE和FALSE執行語句,與編程語言中的if、else if、else語法類似,其語法格式如下
-- 存儲過程-if -- 案例1 -- 輸入學生的成績,來判斷成績的級別 /* score<60:不及格 score>=60 , score<80 :及格 score>=80 , score<90 :良好 score>=90 , score<=100 :優秀 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 '優秀'; 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后面的語句相等時,則會執行then后面的語句,如果均不匹配則執行else
語法二:直接寫case 當when后面的條件成立則執行then后面的語句,如果都不成立,則執行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 :優秀 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 '優秀'; else select '成績錯誤' end case; end $$ delimiter ; call proc_15_case(88);
控制流程-循環
概述
循環是一段在程序中只出現一次,但可能會連續運行多次的代碼
循環中的代碼會運行特定的次數,或者是運行到特定條件成立時結束循環
循環分類
while、repeat、loop
循環控制
leave 類似于break,跳出,結束當前所在的循環
iteater 類似于continue,繼續,結束本次循環,繼續下一次
while循環
-- 需求:向表中添加指定條數據 -- while delimiter $$ create procedure proc16_while(in insertCount int) begin declare i int default 1; -- 設置循環的增量 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; -- 設置循環的增量 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則跳出循環 set i=i+1; end while label; end$$ delimiter ; call proc16_while(10);
repeat循環
格式
-- 存儲過程-循環控制-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 -- 這里不能寫; 表示跳出循環 end repeat label; select '循環結束'; end$$ delimiter ; call proc18_repeat(100);
loop循環
-- 存儲過程-循環控制-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)是庸才存儲查詢結果集的數據類型,在存儲過程和函數中可以使用光標對結果集進行循環的處理,光標的使用包括光標的聲明、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; -- 注意:以上兩句只可以獲取一個數據,因為游標是一次一行,所以采用循環 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聲明必須按照先后順序書寫的,否則創建存儲過程出錯
DECALARE handler_action 創建句柄
FOR 后面為條件值,為什么異常提供處理
statment 當沒有數據發現這個異常發生時采取什么措施
handler_action :{
CONTINUE 繼續執行
EXIT 退出
UNDO 取消(目前MySQL不支持)
}
condition_value :{
mysql_error_code 錯誤碼
condition 條件名
SQLWARNING SQL警告
NOT FOUND 數據沒有發現
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:異常處理完之后,程序應該如何執行 continue :繼續執行剩余代碼 exit:直接終止程序 undo:不支持 2:觸發條件 條件碼:1329..... 條件名: SQLWARNING NOT FOUND SQLEXCEPTION 3:異常觸發之后執行什么代碼 設置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; -- 注意:以上兩句只可以獲取一個數據,因為游標是一次一行,所以采用循環 label:loop fetch my_cursor into var_empno,var_ename,var_sal; -- 判斷flag的值如果為1則執行,否則不執行 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視頻教程
