stored-function and stored-prcedure

註解
範例(黑字,青藍底)：procedure and function的命名
範例(黑字,橘底)：帶入參數
範例(紅字,白底)：內部使用變數
範例(白字,黑底)：如何執行範例



補充說明 - 常用方式解釋：

#宣告變數方式：
DECLARE 變數名稱 資料型態;
ex: DECLARE var1 INT;

#判斷式：
IF 判斷條件1 THEN
ELSEIF 判斷條件2 THEN
ELSE
END IF;
ex:
IF var1=1 THEN
  SET result=1
ELSEIF var1=2 THEN
  SET result=2
ELSE
  SET result=3
END IF;

#while：
WHILE 條件1 DO
END WHILE;
ex:
SET result = 'NO';
WHILE 1 < 2 DO
  SET result = 'YES';
END WHILE;

---

Stored-procedure

#取得BonusGame每日登入-開始時間

DROP PROCEDURE IF EXISTS  `stored-procedure-example`;

DELIMITER ;;

CREATE PROCEDURE `stored-procedure-example`(

IN id INT,

OUT outResult INT)

BEGIN

SET outResult= id;

END;;

DELIMITER ;

call spGetBonusGame_DailyLogin_User(411, @result);

select @result;

---

Stored-function

DROP FUNCTION IF EXISTS `stored-function-exmaple`;
DELIMITER $$
CREATE FUNCTION `stored-function-exmaple`
(
    id INT,
    created_at DATETIME
)
RETURNS DATETIME

BEGIN
    DECLARE outDatetime DATETIME;

    SELECT DATE_ADD(created_at,INTERVAL id MINUTE) INTO outDatetime;
    RETURN outDatetime;

END;
$$

DELIMITER ;


SELECT *, stored-function-exmaple(id, created_at) FROM TEST-TABLE;

---

相關連結：

1. http://forum.slime.com.tw/thread222603.html
2. http://www.codedata.com.tw/database/mysql-tutorial-13-stored-routines/