存儲過程基礎結構

CREATE PROCEDURE proc_name (IN param INT)
BEGIN
    DECLARE var VARCHAR(20);
    IF param = 1 THEN
        SET var = 'MySQL';
    ELSE
        SET var = 'PHP';
    END IF;
    INSERT INTO tb (name) VALUES (var);
END;

• 參數模式：IN（輸入）、OUT（輸出）、INOUT（輸入輸出）。默認 IN 可省略。
• 名稱不區分大小寫，不可與內建函數重名。
• 語句塊用 BEGIN ... END 包裹，內部語句以分號結束，故定義前需用 DELIMITER // 修改結束符。

實戰示例

示例一：條件查詢排序

DELIMITER //
CREATE PROCEDURE proc_name (IN param INT)
BEGIN
    IF param = 0 THEN
        SELECT * FROM user ORDER BY id ASC;
    ELSE
        SELECT * FROM user ORDER BY id DESC;
    END IF;
END//
DELIMITER ;
CALL proc_name(1);

示例二：變量賦值與返回

DELIMITER //
CREATE PROCEDURE proc_name (IN param INT)
BEGIN
    DECLARE var VARCHAR(20);
    IF param = 1 THEN SET var = 'Windows';
    ELSE SET var = 'Linux'; END IF;
    SELECT param, var;
END//
DELIMITER ;

存儲過程中使用事務

模式一：捕獲異常自動回滾/提交

DELIMITER $$
CREATE PROCEDURE test_sp1()
BEGIN
    DECLARE t_error INT DEFAULT 0;
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET t_error = 1;

    START TRANSACTION;
    INSERT INTO test VALUES (NULL, 'test sql 001');
    INSERT INTO test VALUES ('1', 'test sql 002');  -- 故意錯誤觸發異常

    IF t_error = 1 THEN ROLLBACK; ELSE COMMIT; END IF;
END$$
DELIMITER ;

模式二：返回事務執行狀態給調用者

CREATE PROCEDURE p_userConfirmPay(...)
BEGIN
    DECLARE t_error INT DEFAULT 0;
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET t_error = 1;

    START TRANSACTION;
    -- 多條業務 SQL...
    IF t_error = 1 THEN ROLLBACK; ELSE COMMIT; END IF;
    SELECT t_error;  -- 0 成功，1 失敗
END

游標使用步驟

1. DECLARE cur_name CURSOR FOR SELECT ... 定義游標
2. OPEN cur_name 打開
3. FETCH cur_name INTO var_list 逐行取數據
4. CLOSE cur_name 關閉釋放

注意： 必須聲明 DECLARE CONTINUE HANDLER FOR NOT FOUND SET flag = 1; 避免遍歷結束報錯。

游標完整示例：記錄庫存 < 100 的產品代碼

DELIMITER $$
CREATE PROCEDURE CursorProc()
BEGIN
    DECLARE no_more INT DEFAULT 0;
    DECLARE qty INT;
    DECLARE code VARCHAR(255);
    DECLARE cur CURSOR FOR SELECT code FROM products;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more = 1;

    CREATE TEMPORARY TABLE infologs (Id INT AUTO_INCREMENT PRIMARY KEY, Msg VARCHAR(255));

    OPEN cur;
    FETCH cur INTO code;
    REPEAT
        SELECT quantity INTO qty FROM products WHERE code = code;
        IF qty < 100 THEN INSERT INTO infologs (Msg) VALUES (code); END IF;
        FETCH cur INTO code;
    UNTIL no_more = 1 END REPEAT;
    CLOSE cur;

    SELECT * FROM infologs;
    DROP TABLE infologs;
END$$
DELIMITER ;