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

MySQL 預存程序結構完整指南：無參、輸入參、輸出參、動態 SQL

編輯：MySQL 資料庫相關知識

MySQL 預存程序支援 IN (輸入)、OUT (輸出)、INOUT (雙向) 三種參數模式，結合流程控制、動態 SQL (PREPARE/EXECUTE)、異常處理，構建可復用的資料庫端業務邏輯單元。

1. 無參數預存程序

DROP PROCEDURE IF EXISTS `P_XSRBB`;
CREATE PROCEDURE `P_XSRBB`()
BEGIN
    DECLARE `VP1` VARCHAR(20);
    SET `VP1` = '4';
    SELECT `VP1` FROM DUAL;
END;

CALL `P_XSRBB`();

DECLARE 區域變量 (僅在 BEGIN...END 內有效)
SET 賦值
DUAL 虛擬表，單純為 SELECT 服務

2. 輸入參數 (IN)

DROP PROCEDURE IF EXISTS `P_XSRBB`;
CREATE PROCEDURE `P_XSRBB`(IN `vp2` INT)
BEGIN
    DECLARE `VP1` VARCHAR(20);
    SET `VP1` = '4';
    SELECT `vp2` FROM DUAL;  -- 直接引用參數名
END;

CALL `P_XSRBB`(3434);

特點： 調用者傳值，程序內只讀，修改不影響外部。

3. 輸出參數 (OUT)

CREATE PROCEDURE `GetUserCount`(OUT `total` INT)
BEGIN
    SELECT COUNT(*) INTO `total` FROM `user`;
END;

SET @cnt = 0;
CALL `GetUserCount`(@cnt);
SELECT @cnt;  -- 顯示結果

特點： 程序內賦值，調用者透過用戶變量 @var 接收，調用前無需初始化 (但習慣初始化)。

4. 雙向參數 (INOUT)

CREATE PROCEDURE `IncValue`(INOUT `val` INT)
BEGIN
    SET `val` = `val` + 10;
END;

SET @v = 5;
CALL `IncValue`(@v);
SELECT @v;  -- 15

特點： 調用者傳入初始值，程序修改並返回新值。

5. 完整參數範例 (IN/OUT/INOUT 混用)

CREATE PROCEDURE `CalcOrder`(
    IN  `uid` INT,
    IN  `discount` DECIMAL(3,2),
    OUT `total_amt` DECIMAL(10,2),
    OUT `item_cnt` INT,
    INOUT `bonus_points` INT
)
BEGIN
    DECLARE `subtotal` DECIMAL(10,2);
    
    SELECT SUM(`price` * `qty`), COUNT(*)
    INTO `subtotal`, `item_cnt`
    FROM `order_item` WHERE `user_id` = `uid`;
    
    SET `total_amt` = `subtotal` * `discount`;
    SET `bonus_points` = `bonus_points` + FLOOR(`total_amt`);
END;

6. 動態 SQL (PREPARE/EXECUTE)

用於表名/欄位名/條件動態構建，防止 SQL 注入必須用佔位符 ?。

CREATE PROCEDURE `DynamicQuery`(IN `tbl` VARCHAR(64), IN `col` VARCHAR(64), IN `val` VARCHAR(100))
BEGIN
    SET @sql = CONCAT('SELECT * FROM `', `tbl`, '` WHERE `', `col`, '` = ?');
    PREPARE stmt FROM @sql;
    SET @v = `val`;
    EXECUTE stmt USING @v;
    DEALLOCATE PREPARE stmt;
END;

關鍵： USING 子句綁定參數，自動處理轉義/引號，杜絕拼接注入。

7. 流程控制與異常處理

結構	語法	範例
條件	`IF ... THEN ... ELSEIF ... ELSE ... END IF;`	`IF v > 0 THEN ... END IF;`
分支	`CASE v WHEN 1 THEN ... WHEN 2 THEN ... ELSE ... END CASE;`	或 `CASE WHEN cond THEN ... END CASE;`
迴圈	`WHILE cond DO ... END WHILE;` / `REPEAT ... UNTIL cond END REPEAT;` / `LOOP ... END LOOP;`	`LEAVE label` 跳出、`ITERATE label` 繼續
異常處理	`DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @err=1;`	捕獲重複鍵錯誤不中斷
自定義錯誤	`SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '自定義錯誤';`	業務邏輯拋錯

8. 管理與查看

-- 查看程序定義
SHOW CREATE PROCEDURE `P_XSRBB`;

-- 列出所有程序
SHOW PROCEDURE STATUS WHERE Db = 'mydb';

-- 刪除
DROP PROCEDURE IF EXISTS `P_XSRBB`;

-- 查看參數
SELECT * FROM INFORMATION_SCHEMA.PARAMETERS WHERE SPECIFIC_NAME = 'P_XSRBB';