MySQL的存儲(chǔ)過(guò)程
什么是存儲(chǔ)過(guò)程
MySQL 5.0 版本開(kāi)始支持存儲(chǔ)過(guò)程刨秆。
簡(jiǎn)單的說(shuō)犬金,存儲(chǔ)過(guò)程就是一組SQL語(yǔ)句集囱井,功能強(qiáng)大驹尼,可以實(shí)現(xiàn)一些比較復(fù)雜的邏輯功能,類(lèi)似于JAVA語(yǔ)言中的方法庞呕;
存儲(chǔ)過(guò)就是數(shù)據(jù)庫(kù) SQL 語(yǔ)言層面的代碼封裝與重用新翎。
有哪些特性
- 有輸入輸出參數(shù),可以聲明變量住练,有 if/else, case,while 等控制語(yǔ)句地啰,通過(guò)編寫(xiě)存儲(chǔ)過(guò)程,可以實(shí)現(xiàn)復(fù)雜的邏輯功
能讲逛; - 函數(shù)的普遍特性:模塊化亏吝,封裝,代碼復(fù)用盏混;
- 速度快蔚鸥,只有首次執(zhí)行需經(jīng)過(guò)編譯和優(yōu)化步驟惜论,后續(xù)被調(diào)用可以直接執(zhí)行,省去以上步驟止喷;
入門(mén)案例
操作 - 創(chuàng)建存儲(chǔ)過(guò)程
delimiter $$
create procedure proc01()
begin
select empno,ename from emp;
end $$
delimiter ;
-- 調(diào)用存儲(chǔ)過(guò)程
call proc01();
MySQL 操作 - 變量定義
局部變量
格式
用戶自定義馆类,在 begin/end 塊中有效
語(yǔ)法:聲明變量
/codedeclare var_name type [default var_value];
舉例:declare nickname varchar(32);
操作
delimiter $$
create procedure proc02()
begin
declare var_name01 varchar(20) default ‘a(chǎn)aa’;
set var_name01 = ‘zhangsan’;
select var_name01;
end $$
delimiter ;
-- 調(diào)用存儲(chǔ)過(guò)程
call proc02();
MySQL 中還可以使用 SELECT..INTO
語(yǔ)句為變量賦值。其基本語(yǔ)法如下:
select col_name [...] into var_name[,...]
from table_name wehre condition
--其中:
col_name --參數(shù)表示查詢的字段名稱(chēng)弹谁;
var_name --參數(shù)是變量的名稱(chēng)乾巧;
table_name --參數(shù)指表的名稱(chēng);
condition --參數(shù)指查詢條件预愤。
? --注意:當(dāng)將查詢結(jié)果賦值給變量時(shí)沟于,該查詢語(yǔ)句的返回結(jié)果只能是單行單列。
delimiter $$
create procedure proc03()
begin
declare my_ename varchar(20) ;
select ename into my_ename from emp where empno=1001;
select my_ename;
end $$
delimiter ;
-- 調(diào)用存儲(chǔ)過(guò)程
call proc03();
用戶變量
格式
用戶自定義鳖粟,當(dāng)前會(huì)話(連接)有效社裆。類(lèi)比 java 的成員變量
語(yǔ)法:
@var_name
--不需要提前聲明,使用即聲明
操作
delimiter $$
create procedure proc04()
begin
set @var_name01 = 'ZS';
end $$
delimiter;
call proc04() ;
select @var_name01 ; -- 可以看到結(jié)果
系統(tǒng)變量
介紹
- 系統(tǒng)變量又分為全局變量與會(huì)話變量
- 全局變量在 MYSQL 啟動(dòng)的時(shí)候由服務(wù)器自動(dòng)將它們初始化為默認(rèn)值向图,這些默認(rèn)值可以通過(guò)更改 my.ini 這個(gè)文件來(lái)更改。
- 會(huì)話變量在每次建立一個(gè)新的連接的時(shí)候标沪,由 MYSQL 來(lái)初始化榄攀。 MYSQL 會(huì)將當(dāng)前所有全局變量的值復(fù)制一份。來(lái)做為會(huì)話變量金句。
- 也就是說(shuō)檩赢,如果在建立會(huì)話以后,沒(méi)有手動(dòng)更改過(guò)會(huì)話變量與全局變量的值违寞,那所有這些變量的值都是一樣的贞瞒。
- 全局變量與會(huì)話變量的區(qū)別就在于,對(duì)全局變量的修改會(huì)影響到整個(gè)服務(wù)器趁曼,但是對(duì)會(huì)話變量的修改军浆,只會(huì)影響到當(dāng)前的會(huì)話(也就是當(dāng)前的數(shù)據(jù)庫(kù)接)。
- 有些系統(tǒng)變量的值是可以利用語(yǔ)句來(lái)動(dòng)態(tài)進(jìn)行更改的挡闰,但是有些系統(tǒng)變量的值卻是只讀的乒融,對(duì)于那些可以更改的系統(tǒng)變量,我們可以利用 set 語(yǔ)句進(jìn)行更改摄悯。
系統(tǒng)變量 - 全局變量
由系統(tǒng)提供赞季,在整個(gè)數(shù)據(jù)庫(kù)有效。
格式
語(yǔ)法:
@@global.var_name
操作
查看全局變量
show global variables;
查看某全局變量
select @@global.auto_increment_increment;
-修改全局變量的值
set global sort_buffer_size = 40000; set @@global.sort_buffer_size = 40000;
系統(tǒng)變量 - 會(huì)話變量
由系統(tǒng)提供奢驯,當(dāng)前會(huì)話(連接)有效
格式
語(yǔ)法:
@@session.var_name
操作
- 查看會(huì)話變量
show session variables;
- 查看某會(huì)話變量
select @@session.auto_in
crement_increment; - 修改會(huì)話變量的值
set session sort_buffer_size = 50000; set @@session.sort_buffer_size = 50000 ;
存儲(chǔ)過(guò)程傳參 -in
in 表示傳入的參數(shù)申钩, 可以傳入數(shù)值或者變量,即使傳入變量瘪阁,并不會(huì)更改變量的
值撒遣,可以內(nèi)部更改断盛,僅僅作用在函數(shù)范圍內(nèi)。
-- 封裝有參數(shù)的存儲(chǔ)過(guò)程愉舔,傳入員工編號(hào)钢猛,查找員工信息
delimiter $$
create procedure dec_param01(in param_empno varchar(20))
begin
select * from emp where empno = param_empno;
end $$
delimiter ;
call dec_param01('1001');
-- 封裝有參數(shù)的存儲(chǔ)過(guò)程,可以通過(guò)傳入部門(mén)名和薪資轩缤,查詢指定部門(mén)命迈,并且薪資大于指定值的員工信息
delimiter $$
create procedure dec_param0x(in dname varchar(50),in sal decimal(7,2),)
begin
select * from dept a, emp b where b.sal > sal and a.dname = dname;
end $$
delimiter ;
call dec_param0x(' 學(xué)工部 ',20000);
存儲(chǔ)過(guò)程傳參 -out
out 表示從存儲(chǔ)過(guò)程內(nèi)部傳值給調(diào)用者
-- --------- 傳出參數(shù): out---------------------------------
use mysql7_procedure;
-- 封裝有參數(shù)的存儲(chǔ)過(guò)程,傳入員工編號(hào)火的,返回員工名字
delimiter $$
create procedure proc08(in empno int ,out out_ename varchar(50) )
begin
select ename into out_ename from emp where emp.empno = empno;
end $$
delimiter ;
call proc08(1001, @o_ename);
select @o_ename;
-- 封裝有參數(shù)的存儲(chǔ)過(guò)程壶愤,傳入員工編號(hào),返回員工名字和薪資
delimiter $$
create procedure proc09(in empno int ,out out_ename varchar(50) ,out out_sal
decimal(7,2))
begin
select ename,sal into out_ename,out_sal from emp where emp.empno = empno;
end $$
delimiter ;
call proc09(1001, @o_dname,@o_sal);
select @o_dname;
select @o_sal;
存儲(chǔ)過(guò)程傳參 -inout
inout 表示從外部傳入的參數(shù)經(jīng)過(guò)修改后可以返回的變量馏鹤,既可以使用傳入變量的值也可以
修改變量的值(即使函數(shù)執(zhí)行完)
-- 傳入員工名征椒,拼接部門(mén)號(hào),傳入薪資湃累,求出年薪
delimiter $$
create procedure proc10(inout inout_ename varchar(50),inout inout_sal int)
begin
select concat(deptno,"_",inout_ename) into inout_ename from emp where ename
= inout_ename;
set inout_sal = inout_sal * 12;
end $$
delimiter ;
set @inout_ename = ' 關(guān)羽 ';
set @inout_sal = 3000;
call proc10(@inout_ename, @inout_sal) ;
select @inout_ename ;
select @inout_sal ;
存儲(chǔ)過(guò)程傳參 -in 勃救, out, inout
- in 輸入?yún)?shù),意思說(shuō)你的參數(shù)要傳到存過(guò)過(guò)程的過(guò)程里面去治力,在存儲(chǔ)過(guò)程中修改該參數(shù)的
值不能被返回 - out 輸出參數(shù) : 該值可在存儲(chǔ)過(guò)程內(nèi)部被改變蒙秒,并向外輸出
- inout 輸入輸出參數(shù),既能輸入一個(gè)值又能傳出來(lái)一個(gè)值 )
MySQL 操作 - 流程控制
流程控制 - 判斷
流程控制 - IF
格式
IF 語(yǔ)句包含多個(gè)條件判斷宵统,根據(jù)結(jié)果為 TRUE 晕讲、 FALSE 執(zhí)行語(yǔ)句,與編程語(yǔ)言中的 if 马澈、 else if 瓢省、 else 語(yǔ)法類(lèi)似,其語(yǔ)法格式如下:
-- 語(yǔ)法
if search_condition_1 then statement_list_1
[elseif search_condition_2 then statement_list_2] ...
[else statement_list_n]
end 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 : 成績(jī)錯(cuò)誤
*/
delimiter $$
create procedure proc_12_if(in score int)
begin
if score < 60
then
select ' 不及格 ';
elseif 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 ;
call proc_12_if(120)
-- 2)輸入員工的名字勤婚,判斷工資的情況。
delimiter $$
create procedure proc12_if(in in_ename varchar(50))
begin
declare result varchar(20);
declare var_sal decimal(7,2);
select sal into var_sal from emp where ename = in_ename;
if var_sal < 10000
then set result = ' 試用薪資 ';
elseif var_sal < 30000
then set result = ' 轉(zhuǎn)正薪資 ';
else
set result = ' 元老薪資 ';
end if;
select result;
end$$
delimiter ;
call proc12_if(' 龐統(tǒng) ');
流程控制 -case
CASE 是另一個(gè)條件判斷的語(yǔ)句辩块,類(lèi)似于編程語(yǔ)言中的 switch 語(yǔ)法
格式
-- 語(yǔ)法一(類(lèi)比 java 的 switch ):
case case_value
when when_value then statement_list
[when when_value then statement_list] ...
[else statement_list]
end case
-- 語(yǔ)法二:
case
when search_condition then statement_list
[when search_condition then statement_list] ...
[else statement_list]
end case
操作
-- 語(yǔ)法一
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(4);
-- 語(yǔ)法二
delimiter $$
create procedure proc_15_case(in score int)
begin
case
when score < 60
then
select ' 不及格 ';
when 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)
[圖片上傳失敗...(image-ab450e-1671302309946)]
循環(huán)分類(lèi):
- while
- repeat
- loop
循環(huán)控制:
- leave 類(lèi)似于 break ,跳出废亭,結(jié)束當(dāng)前所在的循環(huán)
- iterate 類(lèi)似于 continue 国章,繼續(xù),結(jié)束本次循環(huán)豆村,繼續(xù)下一次
流程控制 - 循環(huán) -while
格式
【標(biāo)簽 : 】 while 循環(huán)條件 do
循環(huán)體 ;
end while 【 標(biāo)簽】 ;
操作
-- 創(chuàng)建測(cè)試表
create table user (
uid int primary_key,
username varchar ( 50 ),
password varchar ( 50 )
);
-- ------- 存儲(chǔ)過(guò)程 -while
delimiter $$
create procedure proc16_while1(in insertcount int)
begin
declare i int default 1;
label:while i<=insertcount do
insert into user(uid,username,`password`)
values(i,concat('user-',i),'123456');
set i=i+1;
end while label;
end $$
delimiter ;
call proc16_while(10);
-- ------- 存儲(chǔ)過(guò)程 -while + leave
truncate table user;
delimiter $$
create procedure proc16_while2(in insertcount int)
begin
declare i int default 1;
label:while i<=insertcount do
insert into user(uid,username,`password`)
values(i,concat('user-',i),'123456');
if i=5 then leave label;
end if;
set i=i+1;
end while label;
end $$
delimiter ;
call proc16_while2(10);
-- ------- 存儲(chǔ)過(guò)程 -while+iterate
朝夕同行
truncate table user;
delimiter $$
create procedure proc16_while3(in insertcount int)
begin
declare i int default 1;
label:while i<=insertcount do
set i=i+1;
if i=5 then iterate label;
end if;
insert into user(uid,username,`password`)
values(i,concat('user-',i),'123456');
end while label;
end $$
delimiter ;
call proc16_while3(10);
流程控制 - 循環(huán) -repeat
格式
[ 標(biāo)簽 :]repeat
循環(huán)體 ;
until 條件表達(dá)式
end repeat [ 標(biāo)簽 ];
操作
-- ------- 存儲(chǔ)過(guò)程 - 循環(huán)控制 -repeat
use mysql7_procedure;
truncate table user;
delimiter $$
create procedure proc18_repeat(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 > insertCount
end repeat label;
select ' 循環(huán)結(jié)束 ';
end $$
delimiter ;
call proc18_repeat(100);
流程控制 - 循環(huán) -loop
格式
[ 標(biāo)簽 :] loop
循環(huán)體 ;
if 條件表達(dá)式 then
leave [ 標(biāo)簽 ];
end if;
end loop;
操作
-- ------- 存儲(chǔ)過(guò)程 - 循環(huán)控制 -loop
truncate table user;
delimiter $$
create procedure proc19_loop(in insertCount int)
begin
declare i int default 1;
label:loop
insert into user(uid, username, password) values(i,concat('user-',i),'123456');
set i = i + 1;
if i > 5
then
leave label;
end if;
end loop label;
select ' 循環(huán)結(jié)束 ';
end $$
delimiter ;
call proc19_loop(10);
游標(biāo)
游標(biāo) (cursor) 是用來(lái)存儲(chǔ)查詢結(jié)果集的數(shù)據(jù)類(lèi)型 , 在存儲(chǔ)過(guò)程和函數(shù)中可以使用光標(biāo)對(duì)結(jié)果集進(jìn)行循環(huán)的處理液兽。光標(biāo)的使用包括光標(biāo)的聲明、 OPEN 、 FETCH 和 CLOSE.
格式
- 聲明語(yǔ)法
declare cursor_name cursor for select_statement
- 打開(kāi)語(yǔ)法
open cursor_name
- 取值語(yǔ)法
fetch cursor_name into var_name [, var_name] ...
- 關(guān)閉語(yǔ)法
close cursor_name
操作
use mysql7_procedure;
delimiter $$
create procedure proc20_cursor(in in_dname varchar(50))
begin
-- 定義局部變量
declare var_empno varchar(50);
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)獲取每一行數(shù)據(jù)
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
朝夕同行
-- 調(diào)用存儲(chǔ)過(guò)程
call proc20_cursor(' 銷(xiāo)售部 ');
異常處理 -HANDLER 句柄
MySql 存儲(chǔ)過(guò)程也提供了對(duì)異常處理的功能:通過(guò)定義 HANDLER 來(lái)完成異常聲明的實(shí)現(xiàn) .
官方文檔:https://dev.mysql.com/doc/refman/5.7/en/declare-handler.html
DECLARE handler_action HANDLER
FOR condition_value [, condition_value] ...
statement
handler_action: {
CONTINUE
| EXIT
| UNDO
}
condition_value: {
mysql_error_code
| condition_name
| SQLWARNING
| NOT FOUND
| SQLEXCEPTION
?在語(yǔ)法中四啰,變量聲明宁玫、游標(biāo)聲明、 handler 聲明是必須按照先后順序書(shū)寫(xiě)的柑晒,否則創(chuàng)建存儲(chǔ)過(guò)程出錯(cuò)欧瘪。
存儲(chǔ)過(guò)程中的 handler
use mysql7_procedure;
drop procedure if exists proc21_cursor_handler;
-- 需求:輸入一個(gè)部門(mén)名,查詢?cè)摬块T(mén)員工的編號(hào)匙赞、名字佛掖、薪資 ,將查詢的結(jié)果集添加游標(biāo)
delimiter $$
create procedure proc20_cursor(in in_dname varchar(50))
begin
-- 定義局部變量
declare var_empno int;
declare var_ename varchar(50);
declare var_sal decimal(7,2);
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;
-- 定義句柄涌庭,當(dāng)數(shù)據(jù)未發(fā)現(xiàn)時(shí)將標(biāo)記位設(shè)置為 0
declare continue handler for NOT FOUND set flag = 0;
--打開(kāi)游標(biāo)
open my_cursor;
-- 通過(guò)游標(biāo)獲取值
label:loop
fetch my_cursor into var_empno, var_ename,var_sal;
-- 判斷標(biāo)志位
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 $$;
call proc21_cursor_handler(' 銷(xiāo)售部 ');