您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

MySQL 5.0 存儲過程與函數完整指南：語法、範例與實戰技巧

編輯：MySQL 資料庫相關知識

MySQL 存儲過程基礎架構

MySQL 5.0 版本開始正式支援存儲過程與函數，其核心元數據儲存在 mysql.proc 系統表中。掌握存儲過程開發需理解幾個關鍵機制：

分隔符管理

MySQL 預設以分號（;）作為語句結束符，但在存儲過程主體中包含多條 SQL 語句時，若不變更分隔符，編譯器會在第一個分號處截斷導致錯誤。標準做法是：

DELIMITER $

建立完成後務必還原：

DELIMITER ;

過程主體結構

使用 BEGIN ... END 包裹程式邏輯，並可透過 SHOW PROCEDURE STATUS 查看過程資訊。

實戰範例：輸出參數與無參數過程

範例 1：帶 OUT 參數的統計過程

DELIMITER $
DROP PROCEDURE IF EXISTS `firstdb`.`getCount` $
CREATE PROCEDURE `getCount`(OUT totalCount INT)
DETERMINISTIC
BEGIN
    SELECT COUNT(*) INTO totalCount FROM users;
    SELECT totalCount;
END $
DELIMITER ;

-- 呼叫方式
CALL getCount(@count);

範例 2：直接回傳結果集的過程

DELIMITER $
DROP PROCEDURE IF EXISTS `firstdb`.`getCount2` $
CREATE PROCEDURE `firstdb`.`getCount2` ()
BEGIN
    SELECT COUNT(*) FROM users;
END $
DELIMITER ;

USE firstdb;
CALL getCount2();

自訂函數開發

DELIMITER $
DROP FUNCTION IF EXISTS `firstdb`.`helloFunc` $
CREATE FUNCTION `firstdb`.`helloFunc` (s CHAR(20)) RETURNS CHAR(50)
BEGIN
    RETURN CONCAT('Hello, ', s, '!');
END $
DELIMITER ;

SELECT helloFunc('king');

完整語法規格

建立存儲過程

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

建立函數

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

參數模式

過程參數：[ IN | OUT | INOUT ] 參數名參數類型
函數參數：參數名參數類型（預設為 IN）

特性選項

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

主體結構

BEGIN
    有效的 SQL 語句
END

開發實戰注意事項

註釋語法：支援 -- 單行註釋與 /* ... */ 區塊註釋
變數宣告：區域變數用 DECLARE，用戶變數用 @ 前綴
腳本導入技巧：在自動化腳本中建立過程時，必須先宣告 DELIMITER $$，結束後還原 DELIMITER ;
內建函數差異：遷移自 Oracle、Sybase 等資料庫時，注意函數名稱與功能的差異
除錯策略：建立除錯記錄表，在關鍵流程點插入記錄追蹤執行路徑

加密演算法實作範例（教學用途）

以下示範一個簡單的字串加密演算法，同樣輸入每次產生不同結果（利用秒數作為種子），僅供教學示範，不具備實際加密強度。

函數版實作

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);
MySQL> SELECT @a;
+---------+
| @a      |
+---------+
| U_PI6$4 |
+---------+

MySQL> CALL pro_addmm('zhouys', @a);
MySQL> SELECT @a;
+---------+
| @a      |
+---------+
| 9P8UEGM |
+---------+

MySQL> SELECT fun_submm('U_PI6$4');
+----------------------+
| fun_submm('U_PI6$4') |
+----------------------+
| ZHOUYS               |
+----------------------+