Mysql存儲過程、觸發(fā)器、事件調(diào)度器怎么使用

本文小編為大家詳細介紹“Mysql存儲過程、觸發(fā)器、事件調(diào)度器怎么使用”，內(nèi)容詳細，步驟清晰，細節(jié)處理妥當(dāng)，希望這篇“Mysql存儲過程、觸發(fā)器、事件調(diào)度器怎么使用”文章能幫助大家解決疑惑，下面跟著小編的思路慢慢深入，一起來學(xué)習(xí)新知識吧。

存儲過程（Stored Procedure）是一種在數(shù)據(jù)庫中存儲復(fù)雜程序的數(shù)據(jù)庫對象。為了完成特定功能的SQL語句集，經(jīng)過編譯創(chuàng)建并保存在數(shù)據(jù)庫中。

一、存儲過程的簡單使用

創(chuàng)建存儲過程

create procedure test()
begin
    select * from users;
end;

調(diào)用存儲過程

call test();

二、存儲過程中的變量

create procedure test()
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;

注意：

• 變量可以通過set來賦值，也可以通過select into的方式賦值；

• 變量需要返回，可以使用select語句，如：select 變量名。

三、變量的作用域

存儲過程的作用域在begin和end塊之間，變量聲明在begin之外，可以作為全局變量使用：

 create procedure test()
    begin
      declare userscount int default 0; -- 用戶表中的數(shù)量
      begin
            select count(*) into userscount from users;
            select userscount; -- 返回用戶表中的數(shù)量
      end;
      begin 
        declare maxmoney int default 0; -- 最大金額
        select max(money) into maxmoney from orders;
        select userscount,maxmoney; -- 返回用戶表中的數(shù)量、最大金額
       end;
    end;

四、存儲過程參數(shù)

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

IN: 傳入?yún)?shù)（不指定時，默認(rèn)就是IN類型）

 create procedure test(userId int)
    begin
        declare username varchar(32) default '';
        select name into username from users where id=userId;
        select username;
    end;

OUT：傳出參數(shù)

create procedure test(in userId int,out username varchar(32))
   begin
     select name into username from users where id=userId;
   end;

INOUT: 既是傳入又是傳出參數(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;

五、邏輯控制語句

1、條件語句

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

create procedure test(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;

2、循環(huán)語句

（1）while

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

create procedure test()
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;

（2）repeat

repeat...until...end repeat;

只有當(dāng)until為真是才跳出循環(huán)：

create procedure test()
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;

3、case分支

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

 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;

六、游標(biāo)

游標(biāo)保存了查詢結(jié)果的臨時區(qū)域

declare 變量名 cursor ... -- 創(chuàng)建一個游標(biāo)變量
close 變量名; -- 關(guān)閉游標(biāo)

create procedure test()
    begin
        declare stopflag int default 0;
        declare username VARCHAR(32);
        declare username_cur cursor for select name from users where id%2=0;
        -- 游標(biāo)變量username_cur保存了查詢的臨時結(jié)果，即結(jié)果集
        -- 在游標(biāo)變量中數(shù)據(jù)的結(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;-- 游標(biāo)向前走一步，取出一條記錄放到變量username中
            end;
        end while; -- 結(jié)束循環(huán)
        close username_cur; -- 關(guān)閉游標(biāo)
    end;

七、自定義函數(shù)

-- 創(chuàng)建函數(shù)
create function 函數(shù)名(參數(shù)) returns 返回類型；
-- 函數(shù)體
begin ...... end；
-- 指定函數(shù)的返回值
returns
--函數(shù)調(diào)用
select 函數(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;

八、觸發(fā)器

觸發(fā)器也是一種數(shù)據(jù)庫對象，在滿足定義條件時觸發(fā)，并執(zhí)行觸發(fā)器中定義的語句集合。

創(chuàng)建觸發(fā)器：create trigger 觸發(fā)器名
after、before：在對表操作之前（before）或者之后（after）觸發(fā)動作。
操作事件：insert,update,delete等修改操作
影響的范圍：for each row

1、需求：出于審計目的，當(dāng)有人往表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.userid,NEW.name,'insert',now());
    end;

2、需求：出于審計目的，當(dāng)刪除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;

九、事件

觸發(fā)器只是針對某個表產(chǎn)生的事件執(zhí)行一些語句,而事件調(diào)度器則是在某一個(間隔)時間執(zhí)行一些語句。

在使用這個功能之前必須確保事件調(diào)度器event_scheduler已開啟：

SET GLOBAL event_scheduler = 1;
-- 或者
SET GLOBAL event_scheduler = on;

--查看開啟情況
show variables like '%event_scheduler%';

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

需求：設(shè)計一個福彩的開獎過程，每3分鐘開獎一次

-- 存儲過程
create procedure test()
        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 test_event -- 創(chuàng)建一個事件
        on schedule every  3 minute -- on schedule 每三分鐘執(zhí)行一次
        on completion preserve 
        do call test;  --調(diào)用存儲過程

讀到這里，這篇“Mysql存儲過程、觸發(fā)器、事件調(diào)度器怎么使用”文章已經(jīng)介紹完畢，想要掌握這篇文章的知識點還需要大家自己動手實踐使用過才能領(lǐng)會，如果想了解更多相關(guān)內(nèi)容的文章，歡迎關(guān)注億速云行業(yè)資訊頻道。