您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

MySQL 存儲過程結構詳解：無參、輸入參、輸出參、INOUT 完整語法與調用示例

編輯：MySQL 綜合教學

存儲過程基礎語法

CREATE PROCEDURE `procedure_name` ([IN|OUT|INOUT] param_name type [, ...])
[CHARACTERISTICS ...]
BEGIN
    -- 声明變量
    DECLARE var_name type [DEFAULT value];
    
    -- 賦值
    SET var_name = value;
    SELECT col INTO var_name FROM table WHERE ...;
    
    -- 流程控制
    IF condition THEN ... ELSEIF ... ELSE ... END IF;
    CASE ... WHEN ... THEN ... ELSE ... END CASE;
    WHILE condition DO ... END WHILE;
    REPEAT ... UNTIL condition END REPEAT;
    LOOP ... END LOOP;
    LEAVE label; ITERATE label;
    
    -- 錯誤處理
    DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @err = 1;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;
    
    -- 動態 SQL
    SET @sql = CONCAT('SELECT * FROM ', @table);
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END

四大參數模式完整示例

1. 無參數

DROP PROCEDURE IF EXISTS `p_no_param`;
CREATE PROCEDURE `p_no_param`()
BEGIN
    DECLARE v_msg VARCHAR(50) DEFAULT 'Hello MySQL Procedure';
    SELECT v_msg AS message;
END;

CALL `p_no_param`();

2. 輸入參數 (IN) - 預設模式

DROP PROCEDURE IF EXISTS `p_in_param`;
CREATE PROCEDURE `p_in_param`(IN p_user_id INT, IN p_name VARCHAR(50))
BEGIN
    SELECT * FROM `users` WHERE `id` = p_user_id AND `name` = p_name;
END;

CALL `p_in_param`(1001, '張三');

3. 輸出參數 (OUT) - 返回值給調用者

DROP PROCEDURE IF EXISTS `p_out_param`;
CREATE PROCEDURE `p_out_param`(IN p_user_id INT, OUT p_total_orders INT, OUT p_latest_order_date DATETIME)
BEGIN
    SELECT COUNT(*), MAX(`created_at`) 
    INTO p_total_orders, p_latest_order_date
    FROM `orders` WHERE `user_id` = p_user_id;
END;

-- 調用需用戶變量接收
CALL `p_out_param`(1001, @total, @latest);
SELECT @total AS total_orders, @latest AS latest_order;

4. 輸入輸出參數 (INOUT) - 可讀可寫

DROP PROCEDURE IF EXISTS `p_inout_param`;
CREATE PROCEDURE `p_inout_param`(INOUT p_counter INT)
BEGIN
    SET p_counter = p_counter + 10;
    SELECT p_counter AS new_value;
END;

SET @cnt = 5;
CALL `p_inout_param`(@cnt);
SELECT @cnt AS final_value; -- 結果 15

進階實戰：帶事務與異常處理的轉賬存儲過程

DROP PROCEDURE IF EXISTS `sp_transfer`;
CREATE PROCEDURE `sp_transfer`(
    IN p_from_account VARCHAR(32),
    IN p_to_account VARCHAR(32),
    IN p_amount DECIMAL(15,2),
    OUT p_result_code INT,
    OUT p_result_msg VARCHAR(100)
)
BEGIN
    DECLARE v_from_balance DECIMAL(15,2);
    DECLARE v_sqlstate CHAR(5);
    DECLARE v_errno INT;
    DECLARE v_msg TEXT;
    
    -- 異常處理器
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        GET DIAGNOSTICS CONDITION 1 v_sqlstate = RETURNED_SQLSTATE, v_errno = MYSQL_ERRNO, v_msg = MESSAGE_TEXT;
        ROLLBACK;
        SET p_result_code = v_errno;
        SET p_result_msg = CONCAT('SQL Error: ', v_msg);
    END;
    
    START TRANSACTION;
    
    -- 鎖定並檢查轉出賬戶餘額 (SELECT ... FOR UPDATE)
    SELECT `balance` INTO v_from_balance 
    FROM `accounts` WHERE `account_no` = p_from_account FOR UPDATE;
    
    IF v_from_balance < p_amount THEN
        ROLLBACK;
        SET p_result_code = 40001;
        SET p_result_msg = 'Insufficient balance';
    ELSE
        UPDATE `accounts` SET `balance` = `balance` - p_amount WHERE `account_no` = p_from_account;
        UPDATE `accounts` SET `balance` = `balance` + p_amount WHERE `account_no` = p_to_account;
        INSERT INTO `transfer_log` (`from_acc`, `to_acc`, `amount`, `status`) VALUES (p_from_account, p_to_account, p_amount, 'SUCCESS');
        COMMIT;
        SET p_result_code = 0;
        SET p_result_msg = 'Success';
    END IF;
END;

-- 調用
CALL `sp_transfer`('ACC001', 'ACC002', 100.00, @code, @msg);
SELECT @code AS code, @msg AS msg;

存儲過程管理指令

操作	指令
查看定義	`SHOW CREATE PROCEDURE p_name;`
列出所有	`SHOW PROCEDURE STATUS [WHERE Db='db_name'];`
刪除	`DROP PROCEDURE IF EXISTS p_name;`
權限授予	`GRANT EXECUTE ON PROCEDURE db.p_name TO 'user'@'host';`

優缺點與最佳實踐建議

優點	缺點/風險
減少網路往返 (批量邏輯下推)	業務邏輯下沉，導致「胖資料庫」、難以版本控制、代碼審查困難
封裝複雜邏輯、保證原子性	調試困難 (無 IDE 斷點、日誌有限)、效能分析受限
權限控制 (僅授 EXECUTE)	移植性差 (方言差異大)、擴展性受限 (難以 Sharding)
減少客戶端代碼量	鎖持有時間長、併發擴展性差