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

一、定義

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

二、存儲過程的特點

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

三、創(chuàng)建一個簡單的存儲過程

1、創(chuàng)建存儲過程的簡單語法

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

2、創(chuàng)建一個簡單的存儲過程

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數據類型保持一致，因此甚至還能制定默認值、字符集和排序規(guī)則等；
(3)、變量可以通過set來賦值，也可以通過select into的方式賦值；
(4)、變量需要返回，可以使用select語句，如：select 變量名。

五、變量的作用域

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

需求: 創(chuàng)建一個存儲過程，用來統計表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）、如果用戶狀態(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;

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

圖(13)

圖(14)

八、存儲過程循環(huán)語句

1、while語句

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

while(表達式) 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;

調用及運行結果結果如圖(15)和圖(16)：

圖(15)

圖(16)

2、repeat語句
（1）、repeat語句基本的結構：

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,則跳出循環(huán)     end repeat; end;

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

圖(17)

圖(18)

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

九、存儲過程游標的使用

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

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

調用結果如圖(19)：

圖(19)

十、自定義函數

函數與存儲過程最大的區(qū)別是函數必須有返回值，否則會報錯

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.創(chuàng)建函數使用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)

十一、觸發(fā)器

1、什么是觸發(fā)器

觸發(fā)器與函數、存儲過程一樣，觸發(fā)器是一種對象，它能根據對表的操作時間，觸發(fā)一些動作，這些動作可以是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;

創(chuàng)建成功后，給uses表中插入一條記錄：

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

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

圖(22)

（2）、總結

1、創(chuàng)建觸發(fā)器使用create trigger 觸發(fā)器名
2、什么時候觸發(fā)？after insert on users,除了after還有before,是在對表操作之前（before）或者之后（after）觸發(fā)動作的。
3、對什么操作事件觸發(fā)？ after insert on users,操作事件包括insert,update,delete等修改操作；
4、對什么表觸發(fā)? 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;

執(zhí)行成功后，打開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); ，執(zhí)行結果如圖（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 -- 創(chuàng)建一個事件         on schedule every  3 minute  -- on schedule 什么時候來執(zhí)行，沒三分鐘執(zhí)行一次         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的創(chuàng)建格式
（1）、基本語法

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

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

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

on schedule at '2019-02-01 04:00:00'

2. 重復計劃執(zhí)行
on schedule every 1 second 每秒執(zhí)行一次
on schedule every 1 minute 每分鐘執(zhí)行一次
on schedule every 1 day 沒天執(zhí)行一次

3.指定時間范圍的重復計劃任務
每天在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表

推薦學習：mysql視頻教程