MySQL Stored Function 範例

範例1：

/* 暫時修改 delimiter 為 $$ */
DELIMITER $$
/*DROP FUNCTION `GetComWin`$$*/

CREATE DEFINER = `root`@`localhost` FUNCTION `GetComWin` (
`v_uid` INT
) RETURNS INT( 11 ) NOT DETERMINISTIC NO SQL SQL SECURITY DEFINER
BEGIN 
/* 宣告變數 */
DECLARE v_ComWin INT DEFAULT 0;
DECLARE v_u_money INT DEFAULT 0;
DECLARE v_u_multi TINYINT DEFAULT 0;

/* select 的結果設定到宣告的變數 */
SELECT u_money,u_multi INTO v_u_money,v_u_multi 
FROM f_user WHERE u_id=v_uid FOR UPDATE;

/* IF ... THEN ... ELSE ...END IF; */
IF v_u_multi = 1 THEN
    SELECT SUM(o_money) - SUM(o_result) INTO v_ComWin
    FROM f_order
    WHERE u_id =v_uid FOR UPDATE;
    IF v_ComWin <=> NULL THEN /* 判斷是否為 NULL */
        SET v_ComWin = 0; 
    END IF;
ELSE
    SET v_ComWin = v_u_money; /* 設定變數值 */
END IF;

RETURN v_ComWin;

END$$
/* 將 delimiter 改回 ; */
DELIMITER ;

範例2：

/* 暫時修改 delimiter 為 $$ */
DELIMITER $$
CREATE FUNCTION  `GetAllParentID` (
`p_id` INT UNSIGNED
) RETURNS TEXT NOT DETERMINISTIC NO SQL SQL SECURITY DEFINER
BEGIN 
/* 宣告變數 */
DECLARE x_res TEXT;
DECLARE x_id INT UNSIGNED;
DECLARE x_up_id INT UNSIGNED;
DECLARE x_times TINYINT DEFAULT 0;

/* repeat 迴圈 */
REPEAT
    SELECT u_id,u_up_id INTO x_id,x_up_id FROM f_user WHERE u_id=p_id;
    SET x_times = x_times+1;
    IF x_times = 1 THEN
        SET x_res = x_up_id;
    ELSE
        SET x_res = CONCAT(x_up_id,',',x_res);
    END IF;
    
    SET p_id = x_up_id;
    UNTIL x_up_id <= 1 OR x_id <=> NULL
END REPEAT;

IF x_up_id<=>NULL OR x_up_id=0 THEN
    /* 丟出異常 */
    SIGNAL SQLSTATE '99999' SET MESSAGE_TEXT = 'GetAllParentID Error';
END IF;

RETURN x_res;

END$$
/* 將 delimiter 改回 ; */
DELIMITER ;