本篇文章給大家分享學習mysql存儲過程時的筆記，其咋哄詳細介紹了mysql儲存過程的相關知識，希望對大家有幫助。

一、定義

? ? ? ?存儲過程（Stored Procedure）是在大型數據庫系統中，一組為了完成特定功能的SQL 語句集，存儲在數據庫中，經過第一次編譯后調用不需要再次編譯，用戶通過指定存儲過程的名字并給出參數（如果該存儲過程帶有參數）來執行它。存儲過程是數據庫中的一個重要對象。

二、存儲過程的特點

? ? 1、能完成較復雜的判斷和運算
?? ?2、可編程行強，靈活
?? ?3、SQL編程的代碼可重復使用
?? ?4、執行的速度相對快一些
?? ?5、減少網絡之間的數據傳輸，節省開銷? ??

三、創建一個簡單的存儲過程

? ? 1、創建存儲過程的簡單語法

create?procedure?名稱() begin ......... end

? ? 2、創建一個簡單的存儲過程

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

??? 3、調用存儲過程

call?testa();

運行結果如圖（1）和圖（2）：

? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?圖（1）

?

? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?圖（2）

四、存儲過程的變量

? ? 1、先通過一個簡單的例子來學習變量的聲明和賦值

create?procedure?test2() begin ??--?使用?declare語句聲明一個變量 ??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只聲明一個變量，變量必須先聲明后使用；

?? ??? ?(2)、變量具有數據類型和長度，與mysql的SQL數據類型保持一致，因此甚至還能制定默認值、字符集和排序規則等；
?? ??? ?(3)、變量可以通過set來賦值，也可以通過select into的方式賦值；
?? ??? ?(4)、變量需要返回，可以使用select語句，如：select 變量名。
? ? ? ?

五、變量的作用域

? ? 1、變量作用域說明：
? ? ? ? (1)、存儲過程中變量是有作用域的，作用范圍在begin和end塊之間，end結束變量的作用范圍即結束。
? ? ? ? (2)、需要多個塊之間傳值，可以使用全局變量，即放在所有代碼塊之前
? ? ? ? (3)、傳參變量是全局的，可以在多個塊之間起作用
? ? 2、通過一個實例來驗證變量的作用域

? ? ? ? ?需求: 創建一個存儲過程，用來統計表users、orders表中行數數量和orders表中的最大金額和最小金額

create?procedure?test3() begin ??begin ????declare?userscount?int?default?0;?--?用戶表中的數量 ????declare?ordercount?int?default?0;?--?訂單表中的數量 ????select?count(*)?into?userscount?from?users; ????select?count(*)?into?ordercount?from?orders; ????select?userscount,ordercount;?--?返回用戶表中的數量、訂單表中的數量 ??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;

調用以上存儲過程，結果如圖（3）和圖（4）：

? ? ? ? ? ?

? ? ? ? ? ? ? ? ? ?（3）?? ?

? ? ?
? ? ? ? ? ? ? ? ? ? ? ?（4）

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

? ? create procedure test3()

? ? begin

? ? ? begin

? ? ? ? declare userscount int default 0; — 用戶表中的數量

? ? ? ? declare ordercount int default 0; — 訂單表中的數量

? ? ? ? select count(*) into userscount from users;

? ? ? ? select count(*) into ordercount from orders;

? ? ? ? select userscount,ordercount; — 返回用戶表中的數量、訂單表中的數量

? ? ? 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;

再次調用call test3(); 會報錯如圖（5）：

? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?圖（5）

? ? 4、將userscount,ordercount改為全局變量，再次驗證

? ? create procedure test3()

? ? begin

? ? ? ? declare userscount int default 0; — 用戶表中的數量

? ? ? ? declare ordercount int default 0; — 訂單表中的數量

? ? ? ? begin

? ? ? ? ? ? select count(*) into userscount from users;

? ? ? ? ? ? select count(*) into ordercount from orders;

? ? ? ? ? ? select userscount,ordercount; — 返回用戶表中的數量、訂單表中的數量

? ? ? 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;

再次調用call test3(); 會報錯如圖（6）和圖（7）：

? ? ? ?

? ? ? ? ? ? ? ? ? ? 圖（6）? ? ? ?

? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?

?? ? ? ? ? ? ? ? ? ?圖（7）

因此，存儲過程中變量的作用域，作用范圍在begin和end塊之間，end結束變量的作用范圍即結束
?? ?

六、存儲過程參數

? 1、基本語法

create?procedure?名稱([IN|OUT|INOUT]?參數名?參數數據類型?) begin ......... end

?存儲過程的參數類型有：IN,OUT,INOUT，下面分別介紹這個三種類型：
?? ?
? 2、存儲過程的傳出參數IN

? ? ?說明：? ? ? ??

? ? ? ? （1）、傳入參數：類型為in,表示該參數的值必須在調用存儲過程事指定，如果不顯示指定為in,那么默認就是in類型。

? ? ? ? （2）、IN類型參數一般只用于傳入，在調用過程中一般不作為修改和返回

? ? ? ? （3）、如果調用存儲過程中需要修改和返回值，可以使用OUT類型參數

通過一個實例來演示：

需求：編寫存儲過程，傳入id,根據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;

運行如圖(8)

?? ?

? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?圖(8)
??
?? ?
? ?3、存儲過程的傳出參數out

? ? ? ? 需求：調用存儲過程時，傳入userId返回該用戶的name

? ? ? ? create procedure test5(in userId int,out username varchar(32))

? ? ? ? begin

? ? ? ? ? ? select name into username from users where id=userId;

? ? ? ? end;

?? ??? ?調用及運行結果如圖(9)：

? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?圖(9)
?? ??? ?
? 概括：

? ? ? ? 1、傳出參數：在調用存儲過程中，可以改變其值，并可返回；

? ? ? ? 2、out是傳出參數，不能用于傳入參數值；

? ? ? ? 3、調用存儲過程時，out參數也需要指定，但必須是變量，不能是常量；

? ? ? ? 4、如果既需要傳入，同時又需要傳出，則可以使用INOUT類型參數

?? ?(3).存儲過程的可變參數INOUT
?? ?
?? ??? ?需求：調用存儲過程時，傳入userId和userName,即使傳入，也是傳出參數。

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;

?調用及運行結果如圖(10)

?? ??? ?

? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?圖(10)
? ? 概括：
?? ??? ?1、可變變量INOUT:調用時可傳入值，在調用過程中，可修改其值，同時也可返回值；
?? ??? ?2、INOUT參數集合了IN和OUT類型的參數功能；
?? ??? ?3、INOUT調用時傳入的是變量，而不是常量；

七、存儲過程條件語句

? ?1、基本結構

? ?（1）、條件語句基本結構：

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

? ?（2）、多條件判斷語句：

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

? ?2、實例
? ? 實例1：編寫存儲過程，如果用戶userId是偶數則返回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;

? ? 調用及運行結果如圖(11)和圖(12)：

? ? ? ?

? ? ? ? ? ? ? ? ? ? ? ? 圖(11)? ? ? ? ? ?

? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?

? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?圖(12)

? ? 2、存儲過程的多條件語句應用示例

? ? ? ? 需求：根據用戶傳入的uid參數判斷

? ? ? ? （1）、如果用戶狀態status為1，則給用戶score加10分；

? ? ? ? （2）、 如果用戶狀態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;

調用程之前的users表的數據如圖(13)，調用 call test8(1); 及運行結果圖(14)：

?? ? ? ? ??
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?圖(13)? ? ? ?

? ? ?

? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? 圖(14)
?? ???

八、存儲過程循環語句

?? ?1、while語句

? ? ? ?（1）、while語句的基本結構

while(表達式)?do? ???......?? end?while;

? ? ? ? ?（2）、示例
?? ?需求：使用循環語句，向表test1(id)中插入10條連續的記錄

create?procedure?test9() begin ??declare?i?int?default?0; ??while(i<p>? ? 調用及運行結果結果如圖(15)和圖(16)：</p><p><br> ?? ?<img alt="" class="has"    style="max-width:90%" src="https://img.php.cn/upload/article/000/000/067/ee40b57740a401709e8615b02b632908-14.png"  style="max-width:90%"></p><p>? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? 圖(15)</p><p>? ?<img alt="" class="has"    style="max-width:90%" src="https://img.php.cn/upload/article/000/000/067/a8b01b8d1cd23aa76fd06c3005d016bf-15.jpg"  style="max-width:90%"></p><p>? ? ? ? ?圖(16)</p><p><br> ?? ?2、repeat語句<br> ? ? （1）、repeat語句基本的結構：</p><pre class="brush:sql;toolbar:false;">repeat...until...end?repeat;

? ? ?（2）、示例

需求：給test1表中的id字段插入數據，從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,則跳出循環 ????end?repeat; end;

? ?調用及運行結果結果如圖(17)和圖(18)

? ?

? ? 圖(17)? ? ? ? ? ? ?

? ?

? ? ? ? ? ? ? 圖(18)
?? ??? ?
? ? 概括：
?? ??? ?until判斷返回邏輯真或者假，表達式可以是任意返回真或者假的表達式，只有當until語句為真是，循環結束。
?? ??? ?

九、存儲過程游標的使用

?? ?1、什么是游標
? ? ? ? 游標是保存查詢結果的臨時區域
? ? 2、示例
?? ?需求：編寫存儲過程，使用游標，把users表中 id為偶數的記錄逐一更新用戶名
?? ?

create?procedure?test11() ????begin ????????declare?stopflag?int?default?0; ????????declare?username?VARCHAR(32); ????????--?創建一個游標變量，declare?變量名?cursor?... ????????declare?username_cur?cursor?for?select?name?from?users?where?id%2=0; ????????--?游標是保存查詢結果的臨時區域 ????????--?游標變量username_cur保存了查詢的臨時結果，實際上就是結果集 ????????--?當游標變量中保存的結果都查詢一遍(遍歷),到達結尾，將變量stopflag設置為1，用于循環中判斷是否結束 ????????declare?continue?handler?for?not?found?set?stopflag=1;  ????????open?username_cur;?--?打卡游標 ????????fetch?username_cur?into?username;?--?游標向前走一步，取出一條記錄放到變量username中 ????????while(stopflag=0)?do?--?如果游標還沒有結尾，就繼續 ????????????begin? ????????????????--?在用戶名前門拼接?'_cur'?字符串 ????????????????update?users?set?name=CONCAT(username,'_cur')?where?name=username; ????????????????fetch?username_cur?into?username; ????????????end; ????????end?while;?--?結束循環 ????????close?username_cur;?--?關閉游標 ????end;

調用結果如圖(19)：

? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?圖(19)

十、自定義函數

?? ?函數與存儲過程最大的區別是函數必須有返回值，否則會報錯
?? ?
?? ?1、一個簡單的函數

create?function?getusername(userid?int)?returns?varchar(32) ????reads?sql?data??--?從數據庫中讀取數據，但不修改數據 ????begin ????????declare?username?varchar(32)?default?''; ????????select?name?into?username?from?users?where?id=userid; ????????return?username; ????end;

?? ?調用及運行結果如圖(20)：

? ? ? ? ? ? ? ? 圖(20)
?? ?

? ? 概括：

? ? 1.創建函數使用create function 函數名(參數) returns 返回類型；

? ? 2.函數體放在begin和end之間；

? ? 3.returns指定函數的返回值；

? ? 4.函數調用使用select getusername()。

2、示例

?? ?需求：根據userid，獲取accoutid,id,name組合成UUID作為用戶的唯一標識

??create?function?getuuid(userid?int)?returns?varchar(64) ????reads?sql?data??--?從數據庫中讀取數據，但不修改數據 ????begin ????????declare?uuid?varchar(64)?default?''; ????????select?concat(accontid,'_',id,'_',name)?into?uuid?from?users?where?id=userid; ????????return?uuid; ????end;

調用及運行結果如圖(21)

? ? ? ? ? ? ?圖(21)

? ?
十一、觸發器

?? ?1、什么是觸發器

?? ?觸發器與函數、存儲過程一樣，觸發器是一種對象，它能根據對表的操作時間，觸發一些動作，這些動作可以是insert,update,delete等修改操作。

?? ?2、示例1

（1）、需求：出于審計目的，當有人往表users插入一條記錄時，把插入的userid,username,插入動作和操作時間記錄下來。

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;

? ? 創建成功后，給uses表中插入一條記錄：

insert?into?users(id,name,age,status,score,accontid) ????values(6,'小周',23,1,'60','10001')；

? 執行成功后，打開oplog表，可以看到oplog表中插入了一條記錄如圖(22)
?? ?

? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?圖(22)
??

??（2）、總結

? ?? ???1、創建觸發器使用create trigger 觸發器名
?? ??? ?2、什么時候觸發？after insert on users,除了after還有before,是在對表操作之前（before）或者之后（after）觸發動作的。
?? ??? ?3、對什么操作事件觸發？ after insert on users,操作事件包括insert,update,delete等修改操作；
?? ??? ?4、對什么表觸發? after insert on users
?? ??? ?5、影響的范圍？for each row

3、示例2

?? ?需求：出于審計目的，當刪除users表時，記錄刪除前該記錄的主要字段值

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;

?? ?執行成功后，打開oplog表，可以看到oplog表中插入了一條記錄如圖（23）

? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? 圖（23）

?? ??? ??
十二、流程控制

?1、case分支

? ?（1）、基本語法結構

case?... when?...?then.... when....?then.... else?...? end?case;

（2）、示例

users表中，根據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;

?? ?調用過程 call testcate(1); ，執行結果如圖（24）；

? ? ? ? ? ? ? ? ? ? ? ? ? ? ?圖（24）

十四、存儲過程+event（事件）

? ? ?1、使用存儲過程+事件事件一個簡單的實現福彩3D開獎
?? ??? ?
?? ??? ?需求：設計一個福彩的開獎過程，沒3分鐘開獎一次
?? ??? ??? ?第一步：先編寫一個存儲過程open_lottery,產生3個隨機數，生成一條開獎記錄
?? ??? ??? ?第二步：編寫一個時間調度器，每3分鐘調用一次這個過程
?? ??? ???

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?--?創建一個事件 ????????on?schedule?every??3?minute??--?on?schedule?什么時候來執行，沒三分鐘執行一次 ????????on?completion?preserve? ????????do?call?open_lottery;

?? ??? ?運行結果如圖(25)

?? ??? ?

? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?圖(25)

注意，如果event之一沒有運行，請按照以下辦法解決：

（1）、?show variables like ‘%event_scheduler%’;
?? ??? ?set global event_scheduler=on;

（2）、?alert event lottery_event enable;

?? ?2、解析event的創建格式
? ? （1）、基本語法

create?event[IF?NOT?EXISTS]event_name?--?創建使用create?event ????ON?SCHEDULE?schedule?--?on?schedule?什么時候來執行 ????[ON?COMPLETION?[NOT]?PRESERVE]?--?調度計劃執行完成后是否還保留 ????[ENABLE?|?DISABLE]?--?是否開啟事件，默認開啟 ????[COMMENT?'comment']?--?事件的注釋 ????DO?sql_statement;?--?這個調度計劃要做什么？

（2）、執行時間說明

? ? 1.單次計劃任務示例
?? ??? ?在2019年2月1日4點執行一次

? ? ? ? on schedule at?? ?‘2019-02-01 04:00:00’
?? ?? ? ?
?? ?2. 重復計劃執行
?? ??? ?on schedule every 1 second 每秒執行一次
?? ??? ?on schedule every 1 minute 每分鐘執行一次
?? ??? ?on schedule every 1 day 沒天執行一次
?? ??? ?
? ? 3.指定時間范圍的重復計劃任務
?? ??? ?每天在20:00:00執行一次
?? ??? ?on schedule every 1 day starts ‘2019-02-01 20:00:00’

十五、本文所用到的表

1、lottery表

2、oplog表

3、orders表

4、test1表

5、user表

推薦學習：mysql視頻教程