MySQL的Stored Function的調用

MySQL的Stored Function和Trigger

Stored Function
儲存Function可以作為運算式在內建方法可以調用的地方使用以及SELECT、UPDATE、DELETE、INSERT語句中使用

Java代碼

1. CREATE FUNCTION function_name(parameter[,...])   
2.     RETURNS datatype   
3.     [LANGUAGE SQL]   
4.     [ [NOT] DETERMINISTIC]   
5.     [ {CONTAINS SQL | NO SQL | MODIFIES SQL DATA | READS SQL DATA} ]   
6.     [ SQL SECURITY {DEFINER|INVOKER} ]   
7.     [COMMENT comment_string ]   
8.     function_statements  

CREATE FUNCTION function_name(parameter[,...])    RETURNS datatype    [LANGUAGE SQL]    [ [NOT] DETERMINISTIC]    [ {CONTAINS SQL | NO SQL | MODIFIES SQL DATA | READS SQL DATA} ]    [ SQL SECURITY {DEFINER|INVOKER} ]    [COMMENT comment_string ]    function_statements

三點與預存程序不同的地方：
1，必須要一個RETURNS語句來定義傳回值類型
2，不能指定參數的IN、OUT或INOUT修飾符，所有參數隱式的為IN
3，Function體必須包含RETURN語句來終結Function執行並返回指定的結果給調用者

Example:

Java代碼

1. CREATE FUNCTION cus_status(in_status CHAR(1))   
2.     RETURNS VARCHAR(20)   
3. BEGIN   
4.     DECLARE long_status VARCHAR(20);   
5.   
6.     IF in_status = 'O' THEN   
7.         SET long_status='Overdue';   
8.     ELSEIF in_status = 'U' THEN   
9.         SET long_status='Up to date';   
10.     ELSEIF in_status = 'N' THEN   
11.         SET long_status='New';   
12.     END IF;   
13.   
14.     RETURN(long_status);   
15. END;  

CREATE FUNCTION cus_status(in_status CHAR(1))    RETURNS VARCHAR(20)BEGIN    DECLARE long_status VARCHAR(20);    IF in_status = 'O' THEN        SET long_status='Overdue';    ELSEIF in_status = 'U' THEN        SET long_status='Up to date';    ELSEIF in_status = 'N' THEN        SET long_status='New';    END IF;    RETURN(long_status);END;

好像MySQL當前最新版本(5.1)還不支援嵌套的Stored Function，僅支援嵌套的Stored Procedure

Trigger
Trigger是資料庫中的事件觸發，當前MySQL的實現是對特定table的DML語句(INSERT/UPDATE/DELETE)調用時觸發

Java代碼

1. CREATE [DEFINER={user|CURRENT_USER}] TRIGGER trigger_name   
2.   {BEFORE|AFTER}   
3.   {INSERT|UPDATE|DELETE}   
4. ON table_name   
5. FOR EACH ROW   
6. trigger_statements  

CREATE [DEFINER={user|CURRENT_USER}] TRIGGER trigger_name  {BEFORE|AFTER}  {INSERT|UPDATE|DELETE}ON table_nameFOR EACH ROWtrigger_statements

AFTER類型的Trigger不能修改NEW記錄的值
如果同時對大量的行做操作，Trigger可能效能開銷較大，所以盡量避免在Trigger裡放入效能消耗大的SQL語句

Example: Using trigger to implememt audit logging

Java代碼

1. CREATE TRIGGER account_balance_au   
2.     AFTER UPDATE ON account_balance FOR EACH ROW   
3.     BEGIN   
4.         INSERT into transaction_log   
5.             (user_id, description)   
6.             VALUES(user(),   
7.                 CONCAT('Adjusted account ',   
8.                     NEW.account_id, ' from ', OLD.balance,   
9.                         ' to ', NEW.balance));   
10. END;