MySQL的Stored Function和Trigger

MySQL的Stored Function和Trigger

Stored Function
存储Function可以作为表达式在内建方法可以调用的地方使用以及SELECT、UPDATE、DELETE、INSERT语句中使用

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:

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)调用时触发

CREATE [DEFINER={user|CURRENT_USER}] TRIGGER trigger_name
  {BEFORE|AFTER}
  {INSERT|UPDATE|DELETE}
ON table_name
FOR EACH ROW
trigger_statements

AFTER类型的Trigger不能修改NEW记录的值
如果同时对大量的行做操作，Trigger可能性能开销较大，所以尽量避免在Trigger里放入性能消耗大的SQL语句

Example: Using trigger to implememt audit logging

CREATE TRIGGER account_balance_au
    AFTER UPDATE ON account_balance FOR EACH ROW
    BEGIN
        INSERT into transaction_log
            (user_id, description)
            VALUES(user(),
                CONCAT('Adjusted account ',
                    NEW.account_id, ' from ', OLD.balance,
                        ' to ', NEW.balance));
END;