MySQL 5 儲存過程編寫實戰：語法規範、參數模式、分隔符陷阱與加密算法範例

語法規範

CREATE PROCEDURE 過程名 ([過程參數[,...]])
[特性 ...] 過程體

CREATE FUNCTION 函數名 ([函數參數[,...]])
RETURNS 返回類型
[特性 ...] 函數體

參數模式

• 過程參數： [ IN | OUT | INOUT ] 參數名 參數類型
• 函數參數： 參數名 參數類型（無 IN/OUT）
• 返回類型： 任意合法 MySQL 資料類型

特性子句

LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'

本文不涉及與 SQL 標準不兼容的專有特性。

過程體/函數體格式

BEGIN
    有效 SQL 語句
END

開發關鍵注意事項

1. 註釋： 支援 -- (行註釋) 與 /* */ (區塊註釋)
2. 變數宣告： 必須用 DECLARE，暫時變數可用 @ 前綴直接引用
3. 分隔符陷阱（關鍵）： MySQL 預設以 ; 為分隔符，腳本導入時過程體內每句會被誤判為結束。必須先用 DELIMITER 宣告自訂分隔符，結束後還原：

   
   DELIMITER $$
   -- 儲存過程/函數定義
   DELIMITER ;
   

4. 內建函數差異： 大多與 Oracle/Informix/Sybase 一致，但部分同名異義或異名同義，遷移 DBA 需特別注意
5. 調試方法： 官方工具包調試功能未深入研究。業務流程調試可用「笨辦法」：建立調試表，在關鍵流程點插入記錄觀察執行路徑

實戰範例：字串加密算法（每次相同輸入產生不同結果）

函數版

CREATE FUNCTION fun_addmm(inpass VARCHAR(10)) RETURNS VARCHAR(11)
BEGIN
    DECLARE string_in VARCHAR(39);
    DECLARE string_out VARCHAR(78);
    DECLARE offset TINYINT(2);
    DECLARE outpass VARCHAR(30) DEFAULT '';
    DECLARE len TINYINT;
    SET len = LENGTH(inpass);
    IF (len <= 0 OR len > 10) THEN RETURN ''; END IF;
    SET offset = (SECOND(NOW()) MOD 39) + 1;
    SET string_out = 'YN8K1JOZVURB3MDETS5GPL27AXWIHQ94C6F0#$_';
    SET string_in = '_$#ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
    SET outpass = CONCAT(outpass, SUBSTRING(string_out, offset, 1));
    SET string_out = CONCAT(string_out, string_out);
    SET @i = 0;
    REPEAT
        SET @i = @i + 1;
        SET outpass = CONCAT(outpass, SUBSTR(string_out, INSTR(string_in, SUBSTRING(inpass, @i, 1)) + offset, 1));
    UNTIL (@i >= len) END REPEAT;
    RETURN outpass;
END

過程版

CREATE PROCEDURE pro_addmm(IN inpass VARCHAR(10), OUT outpass VARCHAR(11))
BEGIN
    DECLARE string_in VARCHAR(39);
    DECLARE string_out VARCHAR(78);
    DECLARE offset TINYINT(2);
    DECLARE len TINYINT;
    SET outpass = '';
    SET len = LENGTH(inpass);
    IF (len <= 0 OR len > 10) THEN SET outpass = '';
    ELSE
        SET offset = (SECOND(NOW()) MOD 39) + 1;
        SET string_out = 'YN8K1JOZVURB3MDETS5GPL27AXWIHQ94C6F0#$_';
        SET string_in = '_$#ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
        SET outpass = CONCAT(outpass, SUBSTRING(string_out, offset, 1));
        SET string_out = CONCAT(string_out, string_out);
        SET @i = 0;
        REPEAT
            SET @i = @i + 1;
            SET outpass = CONCAT(outpass, SUBSTR(string_out, INSTR(string_in, SUBSTRING(inpass, @i, 1)) + offset, 1));
        UNTIL (@i >= len) END REPEAT;
    END IF;
END

執行結果

mysql> CALL pro_addmm('zhouys', @a); SELECT @a;
+---------+ | U_PI6$4 |
mysql> CALL pro_addmm('zhouys', @a); SELECT @a;
+---------+ | 9P8UEGM |
mysql> SELECT fun_submm('U_PI6$4'); -- 解密驗證
+----------------------+ | ZHOUYS |

算法弱點

• 不支援大小寫
• 不支援中文
• 加密強度不足

有興趣可研究解密函數寫法。