MySQL的存儲過程實(shí)例分析

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

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

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

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

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

格式

創(chuàng)建存儲過程

-- 創(chuàng)建存儲過程
delimiter $$
create procedure proc01()
begin
	select empno,ename from emp;
end $$
delimiter ;
-- 調(diào)用存儲過程
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();

用戶變量

用戶自定義,當(dāng)前會話(連接)有效.類比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)變量有分為全局變量與會話變量

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

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

4.也就是說,如果在建立會話以后,沒有手動更改過會話變量與全局變量的值,那所有這些變量的值都是一樣的

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

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

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

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

存儲過程

存儲過程傳參-in

in表示傳入的參數(shù),可以傳入數(shù)值或者變量,即使傳入變量,并不會改變變量的值,可以內(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ù)傳參

存儲過程傳參-out

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

存儲過程傳參-inout

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

流程控制

流程控制-判斷

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

-- 存儲過程-if
-- 案例1
-- 輸入學(xué)生的成績,來判斷成績的級別
/*
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 '成績錯(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è)長度,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('劉備');

流程控制語句-case

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

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

語法二:直接寫case 當(dāng)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);

-- 語法二
-- 輸入學(xué)生的成績,來判斷成績的級別
/*
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 '成績錯(cuò)誤'
	end case;
end $$
delimiter ;

call proc_15_case(88);

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

概述

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

循環(huán)中的代碼會運(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)

格式

-- 存儲過程-循環(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)結(jié)束';
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()

游標(biāo)

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

-- 操作游標(biāo)(cursor)
-- 聲明游標(biāo)
-- 打開游標(biāo)
-- 通過游標(biāo)獲取值
-- 關(guān)閉游標(biāo)
-- 需求:輸入一個(gè)部門名,查詢該部門員工的編號、名字、薪資,將查詢的結(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;
 -- 打開游標(biāo)
	open my_cursor;
 -- 通過游標(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存儲過程也提供了對異常處理的功能:通過定義HANDLER來完成異常聲明的實(shí)現(xiàn)

格式

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

DECALARE handler_action 創(chuàng)建句柄

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

statment 當(dāng)沒有數(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ù)沒有發(fā)現(xiàn)

SQLEXCEPTION  SQL異常

}

/*用于測試句柄,改造上面游標(biāo)代碼會出現(xiàn)1329錯(cuò)誤代碼的情況*/
-- 操作游標(biāo)(cursor)
-- 聲明游標(biāo)
-- 打開游標(biāo)
-- 通過游標(biāo)獲取值
-- 關(guān)閉游標(biāo)
-- 需求:輸入一個(gè)部門名,查詢該部門員工的編號、名字、薪資,將查詢的結(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 ;
 -- 打開游標(biāo)
	open my_cursor;
 -- 通過游標(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的存儲過程實(shí)例分析”這篇文章的所有內(nèi)容，感謝各位的閱讀！相信大家閱讀完這篇文章都有很大的收獲，小編每天都會為大家更新不同的知識，如果還想學(xué)習(xí)更多的知識，請關(guān)注億速云行業(yè)資訊頻道。