站長資訊網(wǎng)本篇文章給大家分享學(xué)習(xí)mysql存儲(chǔ)過程時(shí)的筆記，其咋哄詳細(xì)介紹了mysql儲(chǔ)存過程的相關(guān)知識(shí)，希望對(duì)大家有幫助。
一、定義
存儲(chǔ)過程（Stored Procedure）是在大型數(shù)據(jù)庫系統(tǒng)中，一組為了完成特定功能的SQL 語句集，存儲(chǔ)在數(shù)據(jù)庫中，經(jīng)過第一次編譯后調(diào)用不需要再次編譯，用戶通過指定存儲(chǔ)過程的名字并給出參數(shù)（如果該存儲(chǔ)過程帶有參數(shù)）來執(zhí)行它。存儲(chǔ)過程是數(shù)據(jù)庫中的一個(gè)重要對(duì)象。
二、存儲(chǔ)過程的特點(diǎn)
1、能完成較復(fù)雜的判斷和運(yùn)算
2、可編程行強(qiáng)，靈活
3、SQL編程的代碼可重復(fù)使用
4、執(zhí)行的速度相對(duì)快一些
5、減少網(wǎng)絡(luò)之間的數(shù)據(jù)傳輸，節(jié)省開銷
三、創(chuàng)建一個(gè)簡單的存儲(chǔ)過程
1、創(chuàng)建存儲(chǔ)過程的簡單語法
create procedure 名稱() begin ......... end
2、創(chuàng)建一個(gè)簡單的存儲(chǔ)過程
create procedure testa() begin select * from users; select * from orders; end;
3、調(diào)用存儲(chǔ)過程
call testa();
運(yùn)行結(jié)果如圖（1）和圖（2）：
圖（1）
圖（2）
四、存儲(chǔ)過程的變量
1、先通過一個(gè)簡單的例子來學(xué)習(xí)變量的聲明和賦值
create procedure test2() begin -- 使用 declare語句聲明一個(gè)變量 declare username varchar(32) default ''; -- 使用set語句給變量賦值 set username='xiaoxiao'; -- 將users表中id=1的名稱賦值給username select name into username from users where id=1; -- 返回變量 select username; end;
2、概括
(1)、變量的聲明使用declare,一句declare只聲明一個(gè)變量，變量必須先聲明后使用；
(2)、變量具有數(shù)據(jù)類型和長度，與mysql的SQL數(shù)據(jù)類型保持一致，因此甚至還能制定默認(rèn)值、字符集和排序規(guī)則等；
(3)、變量可以通過set來賦值，也可以通過select into的方式賦值；
(4)、變量需要返回，可以使用select語句，如：select 變量名。
五、變量的作用域
1、變量作用域說明：
(1)、存儲(chǔ)過程中變量是有作用域的，作用范圍在begin和end塊之間，end結(jié)束變量的作用范圍即結(jié)束。
(2)、需要多個(gè)塊之間傳值，可以使用全局變量，即放在所有代碼塊之前
(3)、傳參變量是全局的，可以在多個(gè)塊之間起作用
2、通過一個(gè)實(shí)例來驗(yàn)證變量的作用域
需求: 創(chuàng)建一個(gè)存儲(chǔ)過程，用來統(tǒng)計(jì)表users、orders表中行數(shù)數(shù)量和orders表中的最大金額和最小金額
create procedure test3() begin begin declare userscount int default 0; -- 用戶表中的數(shù)量 declare ordercount int default 0; -- 訂單表中的數(shù)量 select count(*) into userscount from users; select count(*) into ordercount from orders; select userscount,ordercount; -- 返回用戶表中的數(shù)量、訂單表中的數(shù)量 end; begin declare maxmoney int default 0; -- 最大金額 declare minmoney int default 0; -- 最小金額 select max(money) into maxmoney from orders; select min(money) into minmoney from orders; select maxmoney,minmoney; -- 返回最金額、最小金額 end; end;
調(diào)用以上存儲(chǔ)過程，結(jié)果如圖（3）和圖（4）：
（3）
（4）
3、我將過程test(3)改為如下：
create procedure test3()
begin
begin
declare userscount int default 0; — 用戶表中的數(shù)量
declare ordercount int default 0; — 訂單表中的數(shù)量
select count(*) into userscount from users;
select count(*) into ordercount from orders;
select userscount,ordercount; — 返回用戶表中的數(shù)量、訂單表中的數(shù)量
end;
begin
declare maxmoney int default 0; — 最大金額
declare minmoney int default 0; — 最小金額
select max(money) into maxmoney from orders;
select min(money) into minmoney from orders;
select userscount,ordercount，maxmoney,minmoney; — 返回最金額、最小金額
end;
end;
再次調(diào)用call test3(); 會(huì)報(bào)錯(cuò)如圖（5）：
圖（5）
4、將userscount,ordercount改為全局變量，再次驗(yàn)證
create procedure test3()
begin
declare userscount int default 0; — 用戶表中的數(shù)量
declare ordercount int default 0; — 訂單表中的數(shù)量
begin
select count(*) into userscount from users;
select count(*) into ordercount from orders;
select userscount,ordercount; — 返回用戶表中的數(shù)量、訂單表中的數(shù)量
end;
begin
declare maxmoney int default 0; — 最大金額
declare minmoney int default 0; — 最小金額
select max(money) into maxmoney from orders;
select min(money) into minmoney from orders;
select userscount,ordercount，maxmoney,minmoney; — 返回最金額、最小金額
end;
end;
再次調(diào)用call test3(); 會(huì)報(bào)錯(cuò)如圖（6）和圖（7）：
圖（6）
圖（7）
因此，存儲(chǔ)過程中變量的作用域，作用范圍在begin和end塊之間，end結(jié)束變量的作用范圍即結(jié)束
六、存儲(chǔ)過程參數(shù)
1、基本語法
create procedure 名稱([IN|OUT|INOUT] 參數(shù)名 參數(shù)數(shù)據(jù)類型 ) begin ......... end
存儲(chǔ)過程的參數(shù)類型有：IN,OUT,INOUT，下面分別介紹這個(gè)三種類型：
2、存儲(chǔ)過程的傳出參數(shù)IN
說明：
（1）、傳入?yún)?shù)：類型為in,表示該參數(shù)的值必須在調(diào)用存儲(chǔ)過程事指定，如果不顯示指定為in,那么默認(rèn)就是in類型。
（2）、IN類型參數(shù)一般只用于傳入，在調(diào)用過程中一般不作為修改和返回
（3）、如果調(diào)用存儲(chǔ)過程中需要修改和返回值，可以使用OUT類型參數(shù)
通過一個(gè)實(shí)例來演示：
需求：編寫存儲(chǔ)過程，傳入id,根據(jù)id返回name
create procedure test4(userId int) begin declare username varchar(32) default ''; declare ordercount int default 0; select name into username from users where id=userId; select username; end;
運(yùn)行如圖(8)
圖(8)
3、存儲(chǔ)過程的傳出參數(shù)out
需求：調(diào)用存儲(chǔ)過程時(shí)，傳入userId返回該用戶的name
create procedure test5(in userId int,out username varchar(32))
begin
select name into username from users where id=userId;
end;
調(diào)用及運(yùn)行結(jié)果如圖(9)：
圖(9)
概括：
1、傳出參數(shù)：在調(diào)用存儲(chǔ)過程中，可以改變其值，并可返回；
2、out是傳出參數(shù)，不能用于傳入?yún)?shù)值；
3、調(diào)用存儲(chǔ)過程時(shí)，out參數(shù)也需要指定，但必須是變量，不能是常量；
4、如果既需要傳入，同時(shí)又需要傳出，則可以使用INOUT類型參數(shù)
(3).存儲(chǔ)過程的可變參數(shù)INOUT
需求：調(diào)用存儲(chǔ)過程時(shí)，傳入userId和userName,即使傳入，也是傳出參數(shù)。
create procedure test6(inout userId int,inout username varchar(32)) begin set userId=2; set username=''; select id,name into userId,username from users where id=userId; end;
調(diào)用及運(yùn)行結(jié)果如圖(10)
圖(10)
概括：
1、可變變量INOUT:調(diào)用時(shí)可傳入值，在調(diào)用過程中，可修改其值，同時(shí)也可返回值；
2、INOUT參數(shù)集合了IN和OUT類型的參數(shù)功能；
3、INOUT調(diào)用時(shí)傳入的是變量，而不是常量；
七、存儲(chǔ)過程條件語句
1、基本結(jié)構(gòu)
（1）、條件語句基本結(jié)構(gòu)：
if() then...else...end if;
（2）、多條件判斷語句：
if() then... elseif() then... else ... end if;
2、實(shí)例
實(shí)例1：編寫存儲(chǔ)過程，如果用戶userId是偶數(shù)則返回username,否則返回userId
create procedure test7(in userId int) begin declare username varchar(32) default ''; if(userId%2=0) then select name into username from users where id=userId; select username; else select userId; end if; end;
調(diào)用及運(yùn)行結(jié)果如圖(11)和圖(12)：
圖(11)
圖(12)
2、存儲(chǔ)過程的多條件語句應(yīng)用示例
需求：根據(jù)用戶傳入的uid參數(shù)判斷
（1）、如果用戶狀態(tài)status為1，則給用戶score加10分；
（2）、 如果用戶狀態(tài)status為2，則給用戶score加20分；
（3）、 其他情況加30分
create procedure test8(in userid int) begin declare my_status int default 0; select status into my_status from users where id=userid; if(my_status=1) then update users set score=score+10 where id=userid; elseif(my_status=2) then update users set score=score+20 where id=userid; else update users set score=score+30 where id=userid; end if; end;
調(diào)用程之前的users表的數(shù)據(jù)如圖(13)，調(diào)用 call test8(1); 及運(yùn)行結(jié)果圖(14)：
圖(13)
圖(14)
八、存儲(chǔ)過程循環(huán)語句
1、while語句
（1）、while語句的基本結(jié)構(gòu)
while(表達(dá)式) do ...... end while;
（2）、示例
需求：使用循環(huán)語句，向表test1(id)中插入10條連續(xù)的記錄
create procedure test9() begin declare i int default 0; while(i<10) do begin select i; set i=i+1; insert into test1(id) values(i); end; end while; end;
調(diào)用及運(yùn)行結(jié)果結(jié)果如圖(15)和圖(16)：
圖(15)
圖(16)
2、repeat語句
（1）、repeat語句基本的結(jié)構(gòu)：
repeat...until...end repeat;
（2）、示例
需求：給test1表中的id字段插入數(shù)據(jù)，從1到10
create procedure test10() begin declare i int default 0; repeat begin select i; set i=i+1; insert into test1(id) values(i); end; until i>=10 -- 如果i>=10,則跳出循環(huán) end repeat; end;
調(diào)用及運(yùn)行結(jié)果結(jié)果如圖(17)和圖(18)
圖(17)
圖(18)
概括：
until判斷返回邏輯真或者假，表達(dá)式可以是任意返回真或者假的表達(dá)式，只有當(dāng)until語句為真是，循環(huán)結(jié)束。
九、存儲(chǔ)過程游標(biāo)的使用
1、什么是游標(biāo)
游標(biāo)是保存查詢結(jié)果的臨時(shí)區(qū)域
2、示例
需求：編寫存儲(chǔ)過程，使用游標(biāo)，把users表中 id為偶數(shù)的記錄逐一更新用戶名
create procedure test11() begin declare stopflag int default 0; declare username VARCHAR(32); -- 創(chuàng)建一個(gè)游標(biāo)變量，declare 變量名 cursor ... declare username_cur cursor for select name from users where id%2=0; -- 游標(biāo)是保存查詢結(jié)果的臨時(shí)區(qū)域 -- 游標(biāo)變量username_cur保存了查詢的臨時(shí)結(jié)果，實(shí)際上就是結(jié)果集 -- 當(dāng)游標(biāo)變量中保存的結(jié)果都查詢一遍(遍歷),到達(dá)結(jié)尾，將變量stopflag設(shè)置為1，用于循環(huán)中判斷是否結(jié)束 declare continue handler for not found set stopflag=1; open username_cur; -- 打卡游標(biāo) fetch username_cur into username; -- 游標(biāo)向前走一步，取出一條記錄放到變量username中 while(stopflag=0) do -- 如果游標(biāo)還沒有結(jié)尾，就繼續(xù) begin -- 在用戶名前門拼接 '_cur' 字符串 update users set name=CONCAT(username,'_cur') where name=username; fetch username_cur into username; end; end while; -- 結(jié)束循環(huán) close username_cur; -- 關(guān)閉游標(biāo) end;
調(diào)用結(jié)果如圖(19)：
圖(19)
十、自定義函數(shù)
函數(shù)與存儲(chǔ)過程最大的區(qū)別是函數(shù)必須有返回值，否則會(huì)報(bào)錯(cuò)
1、一個(gè)簡單的函數(shù)
create function getusername(userid int) returns varchar(32) reads sql data -- 從數(shù)據(jù)庫中讀取數(shù)據(jù)，但不修改數(shù)據(jù) begin declare username varchar(32) default ''; select name into username from users where id=userid; return username; end;
調(diào)用及運(yùn)行結(jié)果如圖(20)：
圖(20)
概括：
1.創(chuàng)建函數(shù)使用create function 函數(shù)名(參數(shù)) returns 返回類型；
2.函數(shù)體放在begin和end之間；
3.returns指定函數(shù)的返回值；
4.函數(shù)調(diào)用使用select getusername()。
2、示例
需求：根據(jù)userid，獲取accoutid,id,name組合成UUID作為用戶的唯一標(biāo)識(shí)
create function getuuid(userid int) returns varchar(64) reads sql data -- 從數(shù)據(jù)庫中讀取數(shù)據(jù)，但不修改數(shù)據(jù) begin declare uuid varchar(64) default ''; select concat(accontid,'_',id,'_',name) into uuid from users where id=userid; return uuid; end;
調(diào)用及運(yùn)行結(jié)果如圖(21)
圖(21)
十一、觸發(fā)器
1、什么是觸發(fā)器
觸發(fā)器與函數(shù)、存儲(chǔ)過程一樣，觸發(fā)器是一種對(duì)象，它能根據(jù)對(duì)表的操作時(shí)間，觸發(fā)一些動(dòng)作，這些動(dòng)作可以是insert,update,delete等修改操作。
2、示例1
（1）、需求：出于審計(jì)目的，當(dāng)有人往表users插入一條記錄時(shí)，把插入的userid,username,插入動(dòng)作和操作時(shí)間記錄下來。
create trigger tr_users_insert after insert on users for each row begin insert into oplog(userid,username,action,optime) values(NEW.id,NEW.name,'insert',now()); end;
創(chuàng)建成功后，給uses表中插入一條記錄：
insert into users(id,name,age,status,score,accontid) values(6,'小周',23,1,'60','10001')；
執(zhí)行成功后，打開oplog表，可以看到oplog表中插入了一條記錄如圖(22)
圖(22)
（2）、總結(jié)
1、創(chuàng)建觸發(fā)器使用create trigger 觸發(fā)器名
2、什么時(shí)候觸發(fā)？after insert on users,除了after還有before,是在對(duì)表操作之前（before）或者之后（after）觸發(fā)動(dòng)作的。
3、對(duì)什么操作事件觸發(fā)？ after insert on users,操作事件包括insert,update,delete等修改操作；
4、對(duì)什么表觸發(fā)? after insert on users
5、影響的范圍？for each row
3、示例2
需求：出于審計(jì)目的，當(dāng)刪除users表時(shí)，記錄刪除前該記錄的主要字段值
create trigger tr_users_delete before delete on users for each row begin insert into oplog(userid,username,action,optime) values(OLD.id,OLD.name,'delete',now()); end;
刪除users表中的一條記錄
delete from users where id=6;
執(zhí)行成功后，打開oplog表，可以看到oplog表中插入了一條記錄如圖（23）
圖（23）
十二、流程控制
1、case分支
（1）、基本語法結(jié)構(gòu)
case ... when ... then.... when.... then.... else ... end case;
（2）、示例
users表中，根據(jù)userid獲取status值，如果status為1，則修改score為10；如果status為2，則修改為20，如果status3，則修改為30；否則修改為40。
create procedure testcate(userid int) begin declare my_status int default 0; select status into my_status from users where id=userid; case my_status when 1 then update users set score=10 where id=userid; when 2 then update users set score=20 where id=userid; when 3 then update users set score=30 where id=userid; else update users set score=40 where id=userid; end case; end;
調(diào)用過程 call testcate(1); ，執(zhí)行結(jié)果如圖（24）；
圖（24）
十四、存儲(chǔ)過程+event（事件）
1、使用存儲(chǔ)過程+事件事件一個(gè)簡單的實(shí)現(xiàn)福彩3D開獎(jiǎng)
需求：設(shè)計(jì)一個(gè)福彩的開獎(jiǎng)過程，沒3分鐘開獎(jiǎng)一次
第一步：先編寫一個(gè)存儲(chǔ)過程open_lottery,產(chǎn)生3個(gè)隨機(jī)數(shù)，生成一條開獎(jiǎng)記錄
第二步：編寫一個(gè)時(shí)間調(diào)度器，每3分鐘調(diào)用一次這個(gè)過程
create procedure open_lottery() begin insert into lottery(num1,num2,num3,ctime) select FLOOR(rand()*9)+1,FLOOR(rand()*9)+1,FLOOR(rand()*9)+1,now(); end;
create event if not exists lottery_event -- 創(chuàng)建一個(gè)事件 on schedule every 3 minute -- on schedule 什么時(shí)候來執(zhí)行，沒三分鐘執(zhí)行一次 on completion preserve do call open_lottery;
運(yùn)行結(jié)果如圖(25)
圖(25)
注意，如果event之一沒有運(yùn)行，請(qǐng)按照以下辦法解決：
（1）、 show variables like '%event_scheduler%';
set global event_scheduler=on;
（2）、 alert event lottery_event enable;
2、解析event的創(chuàng)建格式
（1）、基本語法
create event[IF NOT EXISTS]event_name -- 創(chuàng)建使用create event ON SCHEDULE schedule -- on schedule 什么時(shí)候來執(zhí)行 [ON COMPLETION [NOT] PRESERVE] -- 調(diào)度計(jì)劃執(zhí)行完成后是否還保留 [ENABLE | DISABLE] -- 是否開啟事件，默認(rèn)開啟 [COMMENT 'comment'] -- 事件的注釋 DO sql_statement; -- 這個(gè)調(diào)度計(jì)劃要做什么？
（2）、執(zhí)行時(shí)間說明
1.單次計(jì)劃任務(wù)示例
在2019年2月1日4點(diǎn)執(zhí)行一次
on schedule at '2019-02-01 04:00:00'
2. 重復(fù)計(jì)劃執(zhí)行
on schedule every 1 second 每秒執(zhí)行一次
on schedule every 1 minute 每分鐘執(zhí)行一次
on schedule every 1 day 沒天執(zhí)行一次
3.指定時(shí)間范圍的重復(fù)計(jì)劃任務(wù)
每天在20:00:00執(zhí)行一次
on schedule every 1 day starts '2019-02-01 20:00:00'
十五、本文所用到的表
1、lottery表
2、oplog表
3、orders表
4、test1表
5、user表
推薦學(xué)習(xí)：mysql視頻教程
