Mysql存儲過程、觸發(fā)器、事件調(diào)度器使用入門指南
更新時間：2022年01月22日 11:36:44 作者：長不大的大灰狼
存儲過程（Stored Procedure）是一種在數(shù)據(jù)庫中存儲復(fù)雜程序的數(shù)據(jù)庫對象。為了完成特定功能的SQL語句集，經(jīng)過編譯創(chuàng)建并保存在數(shù)據(jù)庫中，本文給大家介紹Mysql存儲過程、觸發(fā)器、事件調(diào)度器使用入門指南，感興趣的朋友一起看看吧
存儲過程（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(表達(dá)式) 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、需求：出于審計(jì)目的，當(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、需求：出于審計(jì)目的，當(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)度計(jì)劃執(zhí)行完成后是否還保留
[ENABLE | DISABLE] -- 是否開啟事件，默認(rèn)開啟
[COMMENT 'comment'] -- 事件的注釋
DO sql_statement; -- 這個調(diào)度計(jì)劃要做什么？需求：設(shè)計(jì)一個福彩的開獎過程，每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)用存儲過程到此這篇關(guān)于Mysql存儲過程、觸發(fā)器、事件調(diào)度器使用入門的文章就介紹到這了,更多相關(guān)Mysql存儲過程、觸發(fā)器、事件調(diào)度器內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
本篇文章是對SQL字符串以及數(shù)字的常用操作進(jìn)行了詳細(xì)的總結(jié)與分析，需要的朋友參考下2013-06-06
這篇文章主要介紹了MySQL基礎(chǔ)知識的相關(guān)資料，文中講解非常細(xì)致，幫助大家更好的理解和學(xué)習(xí)，感興趣的朋友可以了解下2020-07-07
這篇文章主要介紹了MySQL中從庫延遲狀況排查的一則案例,針對其從庫無業(yè)務(wù)狀態(tài)下的CPU大量占用情況,需要的朋友可以參考下2015-05-05
這篇文章主要介紹了MySQL Proxy的安裝及基本命令使用教程,MySQL Proxy通常被用作實(shí)現(xiàn)讀寫分離,需要的朋友可以參考下2015-12-12
眾所周知sysbench是一個模塊化的、跨平臺、多線程基準(zhǔn)測試工具，主要用于評估測試各種不同系統(tǒng)參數(shù)下的數(shù)據(jù)庫負(fù)載情況。下面這篇文章就來詳細(xì)介紹sysbench如何對mysql進(jìn)行壓力測試，有需要的可以一起來看看。2016-09-09
MySQL 檢查約束(CHECK)是用來檢查數(shù)據(jù)表中字段值有效性的一種手段,下面這篇文章主要介紹了MySQL中檢查約束的相關(guān)資料,文中通過代碼介紹的非常詳細(xì),需要的朋友可以參考下2025-03-03
Linux系統(tǒng)徹底卸載MySQL數(shù)據(jù)庫詳解
這篇文章主要介紹了Linux系統(tǒng)徹底卸載MySQL數(shù)據(jù)庫,首先查詢系統(tǒng)是否安裝了mysql，如果安裝需要提前卸載，并刪除mysql安裝的組建服務(wù)，本文給大家介紹的非常詳細(xì)，需要的朋友可以參考下2022-09-09
mysql誤刪數(shù)據(jù)后快速恢復(fù)的辦法推薦
手抖不小心把表里的數(shù)據(jù)刪除或修改錯誤怎么辦?該如何快速恢復(fù)呢?遇到這樣的問題怎么辦?下面這篇文章主要給大家介紹了關(guān)于mysql誤刪數(shù)據(jù)后快速恢復(fù)的相關(guān)資料,需要的朋友可以參考下2023-02-02
mysql中向表中某字段追加字符串的方法...2007-03-03
選擇MySQL數(shù)據(jù)庫的命令以及PHP腳本下的操作方法
這篇文章主要介紹了選擇MySQL數(shù)據(jù)庫的命令以及PHP腳本下的操作方法,此外文中還對MySQL的基本數(shù)據(jù)類型作了介紹,需要的朋友可以參考下2015-11-11