(範例) MySQL Stored Procedure/Stored Function/Trigger


在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。

STORED PROCEDURE
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=0;
mysql> set @[email protected]+1;

以上@v變數在該連線期間都會存在,可以一直使用。而local變數大多用在預存程序或函數中,例如

CREATE PROCEDURE TEST()
DECLARE v INT;
SET v=(select count(*) from table_name);
....

以上v變數只在程序內存在。


(15)
SHOW triggers; 顯示目前存在那些觸發
DROP trigger trigger_name; 刪除觸發

更多參考資料

留言

這個網誌中的熱門文章

如何使用EXCEL連接MYSQL

關聯模式的五大鍵 Super key、Candidate Key、Primary Key、Alternate Key、Foreign Key

SELECT SQL語法總整理