在MySQL中，把Procedure和Function統稱為Routine，我們會把常用的程序用Stored Procedure或是Stored Function來表示，需要的時候就可以重複呼叫。
Stored Procedure(預儲程序)：把一連串的SQL程序步驟儲存起來，最後透過 『call 預儲程序名稱;』來呼叫。
Stored Function(預儲函數)：或稱為使用者定義函數，跟預儲程序很像，不過最後會傳回值。呼叫方式不是 call，而是Select，例如：『select 預儲函數名稱(引數值);』或『select 預儲函數名稱();』。
MySQL 正式支援觸發器(trigger)是在 MySQL 5.0.2 的版本之後。觸發器是註冊在資料庫表格上的程式。所以在事件發生（對資料列做新增/修改/刪除）時，資料庫會依照觸發條件（事件前/事件後）幫你執行預先儲存好的程式。
現在來看看一個實際需求的案例 ~
資料表格式如下
Student(sid, idno, sname, did, syear, sclass)
OpenCourse(oid, cid, tid, yearlimit, roomno, tot)
Schedule(oid, timeno)
Roll(sid, oid, score)
現在需要建立預存程序/預存函數/觸發
【需求】
預存函數希望可以檢查學生要修某個開課編號的課程時，是否衝堂?
預存程序則是檢查是否衝堂之外，如果不衝堂，則加入選課資料表。
並且希望在加入選課資料表時，可以用觸發tot加1。
在刪除選課資料表時，可以用觸發tot減1。
delimiter //
drop procedure if exists addcourse//
CREATE PROCEDURE addcourse(psid char(5), poid char(5), OUT flag int)
BEGIN
DECLARE a char(5);
SET a =(SELECT oid FROM opencourse WHERE oid=poid and oid NOT IN (SELECT o.oid FROM opencourse o, schedule s WHERE o.oid=s.oid AND s.timeno IN (SELECT timeno FROM roll r,schedule s WHERE sid=psid AND r.oid=s.oid)));
IF (a IS NULL) THEN
SET flag=0;
select 'course is NOT added' as Message;
ELSE
SET flag=1;
insert into roll (sid, oid) values (psid,poid);
select 'course is ADDed' as Message;
END IF;
END//
delimiter ;
【STORED FUNCTION】
DELIMITER //
CREATE FUNCTION CheckConflict (psid char(5), poid char(5))
RETURNS INT
DETERMINISTIC
BEGIN
DECLARE ret_val INT;
DECLARE a char(5);
SET a =(SELECT oid FROM opencourse WHERE oid=poid and oid NOT IN (SELECT o.oid FROM opencourse o, schedule s WHERE o.oid=s.oid AND s.timeno IN (SELECT timeno FROM roll r,schedule s WHERE sid=psid AND r.oid=s.oid)));
IF (a IS NULL) THEN
SET ret_val=0;
ELSE
SET ret_val=1;
END IF;
RETURN ret_val;
END
//
DELIMITER ;
【TRIGGER】
DELIMITER //
create trigger regtot
after insert
on roll for each row
update opencourse set tot=tot+1 where oid=new.oid;
create trigger regtot2
after delete
on roll for each row
update opencourse set tot=tot-1 where oid=old.oid;
DELIMITER ;
【測試 】
SET @f=0;
CALL addcourse('s0001','o0003',@f);
select * from roll;
select * from opencourse;
delete from roll where score is null;
select * from roll;
select * from opencourse;
select checkconflict('s0001','o0003');
【以PHP測試 】
<?php
//建立連線
$connection = mysqli_connect("host", "account", "password", "database", "port");
//設定變數
$a='s0001';
$b='o0003';
//呼叫
$query = "CALL addcourse('".$a."','".$b."',@f);";
$result = mysqli_query($connection, $query) or die("Query fail: " . mysqli_error());
//取出結果
while($row = mysqli_fetch_array($result)) {
Echo $row[0];
}
?>
更多範例~
Stored Procedure範例#1
DELIMITER //
CREATE PROCEDURE test_rollback()
BEGIN
DECLARE `_rollback` BOOL DEFAULT 0;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET `_rollback` = 1;
START TRANSACTION;
update tbl1 set fld1=1 where id=1;
update tbl2 set fld1=1 where id=1;
IF `_rollback` THEN
SELECT 'rolling back' AS status;
ROLLBACK;
ELSE
SELECT 'committing' AS status;
COMMIT;
END IF;
END//
DELIMITER ;
Stored Procedure範例#2
delimiter //
create procedure test_in_and_out(in a int, out b int, inout c int)
begin
set a = 1;
set c = c * 2;
end//
delimiter ;
然後可以呼叫
mysql> set @x = 0, @y = 2, @z = 4;
mysql> call test_in_and_out(@x, @y, @z);
mysql> select @x, @y, @z;
Stored Procedure範例#3
delimiter //
CREATE PROCEDURE simpleproc (OUT param1 INT)
BEGIN
SELECT COUNT(*) INTO param1 FROM t;
END
//
delimiter ;
-- 呼叫預儲程序，並得到的值設為@a
mysql> CALL simpleproc(@a);
mysql> SELECT @a;
Stored Function範例#1
DELIMITER //
CREATE FUNCTION my_func (var1 INT, var2 INT)
RETURNS INT
DETERMINISTIC
-- 表示只要輸入的資料一樣, 返回值也會相同.
BEGIN
DECLARE ret_val INT;
SET ret_val := var1 + var2;
RETURN ret_val;
END
//
DELIMITER ;
然後可以呼叫
SELECT my_func(1, 2);
Stored Function範例#2
DELIMITER //
CREATE FUNCTION F_Dist3D (x1 decimal, y1 decimal)
RETURNS decimal
DETERMINISTIC
BEGIN
DECLARE dist decimal;
SET dist = SQRT(x1 - y1);
RETURN dist;
END//
DELIMITER ;
Trigger範例#1
CREATE TABLE account (acct_num INT, amount DECIMAL(10,2));
CREATE TRIGGER ins_sum
BEFORE INSERT ON account
FOR EACH ROW SET @sum = @sum + NEW.amount;
SET @sum = 0;
INSERT INTO account VALUES(137,14.98),(141,1937.50),(97,-100.00);
SELECT @sum AS 'Total amount inserted';
Trigger範例#2
CREATE TRIGGER ins_transaction
BEFORE INSERT ON account
FOR EACH ROW PRECEDES ins_sum
SET @deposits = @deposits + IF(NEW.amount>0,NEW.amount,0),
@withdrawals = @withdrawals + IF(NEW.amount<0,-NEW.amount,0);
Trigger範例#3
delimiter //
CREATE TRIGGER upd_check
BEFORE UPDATE ON account
FOR EACH ROW
BEGIN
IF NEW.amount < 0 THEN
SET NEW.amount = 0;
ELSEIF NEW.amount > 100
THEN SET NEW.amount = 100;
END IF;
END;//
delimiter ;
Trigger範例#4
delimiter //
create trigger ai_t1
after insert
on t1
for each row
begin
insert into i1 values (new.id, new.name);
end//
delimiter ;
其他語法
(1)顯示procedure/function狀態
show procedure status;
show function status;
(2)顯示procedure p1/function f1的建立內容
show create procedure p1;
show create function f1;
(3)暫停mysql程序1秒，可精確設定到0.01秒，用於procedure中可避免迴圈執行一下占用系統太多資源
Select sleep(1);
(4)如果test存在時先刪除 PROCEDURE/FUNCTION
drop procedure if exists test;
drop function if exists test;
(5)變數宣告
語法為：delcare [變數名] [型別] default [預設值];
declare max_count int default 10;
declare n varchar(20) default '';
(6)條件宣告
語法為：declare [條件名] condition for sqlstate [錯誤代碼];
declare not_found condition for sqlstate '02000';
(7)Cursor相關的指令
DECLARE: 宣告Cursor的資料結構及資料來源, 使用SELECT指令來配合
OPEN: 把Cursor啟用並放到Cache中
FETCH: 由Cursor中讀取一筆資料錄
CLOSE: 闗閉Cursor, 由Cache中移除Cursor的暫時資料集合及其定義
Cursor是一個暫存在Cache中的資料集合, 利用Cursor能將這個資料集合中的每筆資料錄進行固定的處理工作, 這可以很方便的用在各種應用上
使用Cursor有些限制:
Cursor是僅讀的, 無法用於更新
Cursor只能順向一筆一筆順序讀取, 無法逆向也無法指定要跳到那一筆資料錄
範例
CREATE PROCEDURE curdemo()
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE a CHAR(16);
DECLARE b,c INT;
DECLARE cur1 CURSOR FOR SELECT id,data FROM test.t1;
DECLARE cur2 CURSOR FOR SELECT i FROM test.t2;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN cur1;
OPEN cur2;
REPEAT
FETCH cur1 INTO a, b;
FETCH cur2 INTO c;
IF NOT done THEN
IF b < c THEN
INSERT INTO test.t3 VALUES (a,b);
ELSE
INSERT INTO test.t3 VALUES (a,c);
END IF;
END IF;
UNTIL done END REPEAT;
CLOSE cur1;
CLOSE cur2;
END
(8)處理器宣告
語法為：declare [處理器名] handler for [條件名] [sql 語句];
declare continue handler for not_found set done=1;
(9)SET宣告變數
SET @name = 43;
SET @total_tax = (SELECT SUM(tax) FROM taxable_transactions);
(10)WHILE
WHILE counter < 10 DO
...
SET counter = counter + 1;
END WHILE;
(11)IF THEN
IF search_condition THEN statement_list
[ELSEIF search_condition THEN statement_list] ...
[ELSE statement_list]
END IF
(12)CASE
CASE case_value
WHEN when_value THEN statement_list
[WHEN when_value THEN statement_list] ...
[ELSE statement_list]
END CASE
範例
CASE
WHEN EXISTS(SELECT * FROM table WHERE id=1)
THEN (UPDATE table SET txt='test' WHERE id=1)
ELSE (INSERT INTO table(id,txt) VALUES(1,'text'))
END;
範例
CASE v
WHEN 2 THEN SELECT v;
WHEN 3 THEN SELECT 0;
ELSE BEGIN END;
END CASE;
(13)REPEAT
[begin_label:] REPEAT
statement_list
UNTIL search_condition
END REPEAT [end_label]
範例
SET @x = 0;
REPEAT
-> SET @x = @x + 1;
-> UNTIL @x > p1
END REPEAT;
(14) 變數前面加上@有何差別?
可以用變數留存時間來解釋，@variable與variable的差別在於，前者是session變數，後者是local變數。
例如
mysql> set @v=@v+1;
以上@v變數在該連線期間都會存在，可以一直使用。而local變數大多用在預存程序或函數中，例如
DECLARE v INT;
SET v=(select count(*) from table_name);
....
以上v變數只在程序內存在。
更多參考資料
https://stackoverflow.com/questions/1009954/mysql-variable-vs-variable-whats-the-difference/1010042
https://stackoverflow.com/questions/1009954/mysql-variable-vs-variable-whats-the-difference/1010042
(15)
SHOW triggers; 顯示目前存在那些觸發
DROP trigger trigger_name; 刪除觸發
更多參考資料
 MySQL Stored Procedure/Stored Function/Trigger){kind=link}
0 留言