本篇文章給大家分享學(xué)習(xí)mysql存儲(chǔ)過(guò)程時(shí)的筆記，其咋哄詳細(xì)介紹了mysql儲(chǔ)存過(guò)程的相關(guān)知識(shí)，希望對(duì)大家有幫助。

一、定義

存儲(chǔ)過(guò)程（Stored Procedure）是在大型數(shù)據(jù)庫(kù)系統(tǒng)中，一組為了完成特定功能的SQL 語(yǔ)句集，存儲(chǔ)在數(shù)據(jù)庫(kù)中，經(jīng)過(guò)第一次編譯后調(diào)用不需要再次編譯，用戶(hù)通過(guò)指定存儲(chǔ)過(guò)程的名字并給出參數(shù)（如果該存儲(chǔ)過(guò)程帶有參數(shù)）來(lái)執(zhí)行它。存儲(chǔ)過(guò)程是數(shù)據(jù)庫(kù)中的一個(gè)重要對(duì)象。

二、存儲(chǔ)過(guò)程的特點(diǎn)

1、能完成較復(fù)雜的判斷和運(yùn)算
2、可編程行強(qiáng)，靈活
3、SQL編程的代碼可重復(fù)使用
4、執(zhí)行的速度相對(duì)快一些
5、減少網(wǎng)絡(luò)之間的數(shù)據(jù)傳輸，節(jié)省開(kāi)銷(xiāo)

三、創(chuàng)建一個(gè)簡(jiǎn)單的存儲(chǔ)過(guò)程

1、創(chuàng)建存儲(chǔ)過(guò)程的簡(jiǎn)單語(yǔ)法

create procedure 名稱(chēng)() begin ......... end

2、創(chuàng)建一個(gè)簡(jiǎn)單的存儲(chǔ)過(guò)程

create procedure testa() begin     select * from users;     select * from orders; end;

3、調(diào)用存儲(chǔ)過(guò)程

call testa();

運(yùn)行結(jié)果如圖（1）和圖（2）：

圖（1）

圖（2）

四、存儲(chǔ)過(guò)程的變量

1、先通過(guò)一個(gè)簡(jiǎn)單的例子來(lái)學(xué)習(xí)變量的聲明和賦值

create procedure test2() begin   -- 使用 declare語(yǔ)句聲明一個(gè)變量   declare username varchar(32) default '';   -- 使用set語(yǔ)句給變量賦值   set username='xiaoxiao';   -- 將users表中id=1的名稱(chēng)賦值給username   select name into username from users where id=1;   -- 返回變量   select username; end;

2、概括
(1)、變量的聲明使用declare,一句declare只聲明一個(gè)變量，變量必須先聲明后使用；

(2)、變量具有數(shù)據(jù)類(lèi)型和長(zhǎng)度，與mysql的SQL數(shù)據(jù)類(lèi)型保持一致，因此甚至還能制定默認(rèn)值、字符集和排序規(guī)則等；
(3)、變量可以通過(guò)set來(lái)賦值，也可以通過(guò)select into的方式賦值；
(4)、變量需要返回，可以使用select語(yǔ)句，如：select 變量名。

五、變量的作用域

1、變量作用域說(shuō)明：
(1)、存儲(chǔ)過(guò)程中變量是有作用域的，作用范圍在begin和end塊之間，end結(jié)束變量的作用范圍即結(jié)束。
(2)、需要多個(gè)塊之間傳值，可以使用全局變量，即放在所有代碼塊之前
(3)、傳參變量是全局的，可以在多個(gè)塊之間起作用
2、通過(guò)一個(gè)實(shí)例來(lái)驗(yàn)證變量的作用域

需求: 創(chuàng)建一個(gè)存儲(chǔ)過(guò)程，用來(lái)統(tǒng)計(jì)表users、orders表中行數(shù)數(shù)量和orders表中的最大金額和最小金額

create procedure test3() begin   begin     declare userscount int default 0; -- 用戶(hù)表中的數(shù)量     declare ordercount int default 0; -- 訂單表中的數(shù)量     select count(*) into userscount from users;     select count(*) into ordercount from orders;     select userscount,ordercount; -- 返回用戶(hù)表中的數(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ǔ)過(guò)程，結(jié)果如圖（3）和圖（4）：

（3）

（4）

3、我將過(guò)程test(3)改為如下：

create procedure test3()

begin

begin

declare userscount int default 0; — 用戶(hù)表中的數(shù)量

declare ordercount int default 0; — 訂單表中的數(shù)量

select count(*) into userscount from users;

select count(*) into ordercount from orders;

select userscount,ordercount; — 返回用戶(hù)表中的數(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; — 用戶(hù)表中的數(shù)量

declare ordercount int default 0; — 訂單表中的數(shù)量

begin

select count(*) into userscount from users;

select count(*) into ordercount from orders;

select userscount,ordercount; — 返回用戶(hù)表中的數(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ǔ)過(guò)程中變量的作用域，作用范圍在begin和end塊之間，end結(jié)束變量的作用范圍即結(jié)束

六、存儲(chǔ)過(guò)程參數(shù)

1、基本語(yǔ)法

create procedure 名稱(chēng)([IN|OUT|INOUT] 參數(shù)名 參數(shù)數(shù)據(jù)類(lèi)型 ) begin ......... end

存儲(chǔ)過(guò)程的參數(shù)類(lèi)型有：IN,OUT,INOUT，下面分別介紹這個(gè)三種類(lèi)型：

2、存儲(chǔ)過(guò)程的傳出參數(shù)IN

說(shuō)明：

（1）、傳入?yún)?shù)：類(lèi)型為in,表示該參數(shù)的值必須在調(diào)用存儲(chǔ)過(guò)程事指定，如果不顯示指定為in,那么默認(rèn)就是in類(lèi)型。

（2）、IN類(lèi)型參數(shù)一般只用于傳入，在調(diào)用過(guò)程中一般不作為修改和返回

（3）、如果調(diào)用存儲(chǔ)過(guò)程中需要修改和返回值，可以使用OUT類(lèi)型參數(shù)

通過(guò)一個(gè)實(shí)例來(lái)演示：

需求：編寫(xiě)存儲(chǔ)過(guò)程，傳入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ǔ)過(guò)程的傳出參數(shù)out

需求：調(diào)用存儲(chǔ)過(guò)程時(shí)，傳入userId返回該用戶(hù)的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ǔ)過(guò)程中，可以改變其值，并可返回；

2、out是傳出參數(shù)，不能用于傳入?yún)?shù)值；

3、調(diào)用存儲(chǔ)過(guò)程時(shí)，out參數(shù)也需要指定，但必須是變量，不能是常量；

4、如果既需要傳入，同時(shí)又需要傳出，則可以使用INOUT類(lèi)型參數(shù)

(3).存儲(chǔ)過(guò)程的可變參數(shù)INOUT

需求：調(diào)用存儲(chǔ)過(guò)程時(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)用過(guò)程中，可修改其值，同時(shí)也可返回值；
2、INOUT參數(shù)集合了IN和OUT類(lèi)型的參數(shù)功能；
3、INOUT調(diào)用時(shí)傳入的是變量，而不是常量；

七、存儲(chǔ)過(guò)程條件語(yǔ)句

1、基本結(jié)構(gòu)

（1）、條件語(yǔ)句基本結(jié)構(gòu)：

if() then...else...end if;

（2）、多條件判斷語(yǔ)句：

if() then... elseif() then... else ... end if;

2、實(shí)例
實(shí)例1：編寫(xiě)存儲(chǔ)過(guò)程，如果用戶(hù)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ǔ)過(guò)程的多條件語(yǔ)句應(yīng)用示例

需求：根據(jù)用戶(hù)傳入的uid參數(shù)判斷

（1）、如果用戶(hù)狀態(tài)status為1，則給用戶(hù)score加10分；

（2）、 如果用戶(hù)狀態(tài)status為2，則給用戶(hù)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ǔ)過(guò)程循環(huán)語(yǔ)句

1、while語(yǔ)句

（1）、while語(yǔ)句的基本結(jié)構(gòu)

while(表達(dá)式) do     ......   end while;

（2）、示例
需求：使用循環(huán)語(yǔ)句，向表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語(yǔ)句
（1）、repeat語(yǔ)句基本的結(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語(yǔ)句為真是，循環(huán)結(jié)束。

九、存儲(chǔ)過(guò)程游標(biāo)的使用

1、什么是游標(biāo)
游標(biāo)是保存查詢(xún)結(jié)果的臨時(shí)區(qū)域
2、示例
需求：編寫(xiě)存儲(chǔ)過(guò)程，使用游標(biāo)，把users表中 id為偶數(shù)的記錄逐一更新用戶(hù)名

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)是保存查詢(xún)結(jié)果的臨時(shí)區(qū)域         -- 游標(biāo)變量username_cur保存了查詢(xún)的臨時(shí)結(jié)果，實(shí)際上就是結(jié)果集         -- 當(dāng)游標(biāo)變量中保存的結(jié)果都查詢(xún)一遍(遍歷),到達(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)還沒(méi)有結(jié)尾，就繼續(xù)             begin                  -- 在用戶(hù)名前門(mén)拼接 '_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ǔ)過(guò)程最大的區(qū)別是函數(shù)必須有返回值，否則會(huì)報(bào)錯(cuò)

1、一個(gè)簡(jiǎn)單的函數(shù)

create function getusername(userid int) returns varchar(32)     reads sql data  -- 從數(shù)據(jù)庫(kù)中讀取數(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 返回類(lèi)型；

2.函數(shù)體放在begin和end之間；

3.returns指定函數(shù)的返回值；

4.函數(shù)調(diào)用使用select getusername()。

2、示例

需求：根據(jù)userid，獲取accoutid,id,name組合成UUID作為用戶(hù)的唯一標(biāo)識(shí)

  create function getuuid(userid int) returns varchar(64)     reads sql data  -- 從數(shù)據(jù)庫(kù)中讀取數(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ǔ)過(guò)程一樣，觸發(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í)間記錄下來(lái)。

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í)行成功后，打開(kāi)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í)行成功后，打開(kāi)oplog表，可以看到oplog表中插入了一條記錄如圖（23）

圖（23）

十二、流程控制

1、case分支

（1）、基本語(yǔ)法結(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)用過(guò)程 call testcate(1); ，執(zhí)行結(jié)果如圖（24）；

圖（24）

十四、存儲(chǔ)過(guò)程+event（事件）

1、使用存儲(chǔ)過(guò)程+事件事件一個(gè)簡(jiǎn)單的實(shí)現(xiàn)福彩3D開(kāi)獎(jiǎng)

需求：設(shè)計(jì)一個(gè)福彩的開(kāi)獎(jiǎng)過(guò)程，沒(méi)3分鐘開(kāi)獎(jiǎng)一次
第一步：先編寫(xiě)一個(gè)存儲(chǔ)過(guò)程open_lottery,產(chǎn)生3個(gè)隨機(jī)數(shù)，生成一條開(kāi)獎(jiǎng)記錄
第二步：編寫(xiě)一個(gè)時(shí)間調(diào)度器，每3分鐘調(diào)用一次這個(gè)過(guò)程

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í)候來(lái)執(zhí)行，沒(méi)三分鐘執(zhí)行一次         on completion preserve          do call open_lottery;

運(yùn)行結(jié)果如圖(25)

圖(25)

注意，如果event之一沒(méi)有運(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）、基本語(yǔ)法

create event[IF NOT EXISTS]event_name -- 創(chuàng)建使用create event     ON SCHEDULE schedule -- on schedule 什么時(shí)候來(lái)執(zhí)行     [ON COMPLETION [NOT] PRESERVE] -- 調(diào)度計(jì)劃執(zhí)行完成后是否還保留     [ENABLE | DISABLE] -- 是否開(kāi)啟事件，默認(rèn)開(kāi)啟     [COMMENT 'comment'] -- 事件的注釋     DO sql_statement; -- 這個(gè)調(diào)度計(jì)劃要做什么？

（2）、執(zhí)行時(shí)間說(shuō)明

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 沒(méi)天執(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視頻教程