PHP 操作 MySQL 存儲過程完整實戰教程：從入門到進階

環境準備

$conn = mysqli_connect('localhost', 'root', 'root', 'test');
if (!$conn) die('連線失敗: ' . mysqli_connect_error());

※ 建議用 mysqli 或 PDO，舊 mysql_* 函數已棄用。

範例 1：無參數過程

$sql = "
CREATE PROCEDURE sp_insert_user()
BEGIN
    INSERT INTO user (username, sex) VALUES ('auto_user', '0');
END;
";
mysqli_query($conn, $sql);

mysqli_query($conn, "CALL sp_insert_user()");

範例 2：IN 參數

$sql = "
CREATE PROCEDURE sp_check_score(IN score INT)
BEGIN
    IF score >= 60 THEN SELECT 'pass'; ELSE SELECT 'fail'; END IF;
END;
";
mysqli_query($conn, $sql);
mysqli_query($conn, "CALL sp_check_score(70)");

範例 3：OUT 參數

$sql = "
CREATE PROCEDURE sp_get_max_id(OUT max_id INT)
BEGIN
    SELECT MAX(id) INTO max_id FROM user;
END;
";
mysqli_query($conn, $sql);
mysqli_query($conn, "CALL sp_get_max_id(@max_id)");
$res = mysqli_query($conn, "SELECT @max_id");
$row = mysqli_fetch_row($res);
echo "Max ID: $row[0]";

範例 4：INOUT 參數

$sql = "
CREATE PROCEDURE sp_double(INOUT val INT)
BEGIN
    SET val = val * 2;
END;
";
mysqli_query($conn, $sql);
mysqli_query($conn, "SET @v = 5");
mysqli_query($conn, "CALL sp_double(@v)");
$res = mysqli_query($conn, "SELECT @v");
$row = mysqli_fetch_row($res);
echo $row[0];  // 10

範例 5：區域變數與運算

$sql = "
CREATE PROCEDURE sp_sum(IN a INT, IN b INT)
BEGIN
    DECLARE s INT DEFAULT 0;
    SET s = a + b;
    SELECT s AS sum;
END;
";
mysqli_query($conn, $sql);
mysqli_query($conn, "CALL sp_sum(4, 6)");

範例 6：CASE 分支

$sql = "
CREATE PROCEDURE sp_grade(IN score INT)
BEGIN
    CASE score
        WHEN 100 THEN SELECT '完美';
        WHEN 90 THEN SELECT '優秀';
        WHEN 60 THEN SELECT '及格';
        ELSE SELECT '不及格';
    END CASE;
END;
";
mysqli_query($conn, $sql);
mysqli_query($conn, "CALL sp_grade(100)");

範例 7：WHILE 迴圈

$sql = "
CREATE PROCEDURE sp_sum_loop()
BEGIN
    DECLARE i INT DEFAULT 0;
    DECLARE s INT DEFAULT 0;
    WHILE i < 10 DO
        SET s = s + i;
        SET i = i + 1;
    END WHILE;
    SELECT s;  -- 45
END;
";
mysqli_query($conn, $sql);
mysqli_query($conn, "CALL sp_sum_loop()");

範例 8：REPEAT 迴圈

$sql = "
CREATE PROCEDURE sp_repeat_demo()
BEGIN
    DECLARE i INT DEFAULT 0;
    DECLARE s INT DEFAULT 0;
    REPEAT
        SET s = s + i;
        SET i = i + 1;
    UNTIL s >= 10
    END REPEAT;
    SELECT s;
END;
";
mysqli_query($conn, $sql);
mysqli_query($conn, "CALL sp_repeat_demo()");

範例 9：LOOP + LEAVE

$sql = "
CREATE PROCEDURE sp_loop_demo()
BEGIN
    DECLARE i INT DEFAULT 0;
    DECLARE s INT DEFAULT 0;
    loop_label: LOOP
        SET s = s + i;
        SET i = i + 1;
        IF i >= 5 THEN LEAVE loop_label; END IF;
    END LOOP;
    SELECT s;  -- 10
END;
";
mysqli_query($conn, $sql);
mysqli_query($conn, "CALL sp_loop_demo()");

範例 10：刪除過程

mysqli_query($conn, "DROP PROCEDURE IF EXISTS sp_insert_user");

範例 11：游標處理

$sql = "
CREATE PROCEDURE sp_cursor_demo()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE v_name VARCHAR(50);
    DECLARE cur CURSOR FOR SELECT username FROM user WHERE sex='1';
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO v_name;
        IF done THEN LEAVE read_loop; END IF;
        -- 處理單行，例如寫日誌表
        INSERT INTO log (msg) VALUES (CONCAT('處理用戶: ', v_name));
    END LOOP;
    CLOSE cur;
END;
";
mysqli_query($conn, $sql);
mysqli_query($conn, "CALL sp_cursor_demo()");

管理指令

SHOW PROCEDURE STATUS WHERE Db='test';
SHOW CREATE PROCEDURE sp_insert_user;

最佳實踐提醒

• 所有 DDL 在 PHP 執行前先在測試庫驗證，納入版控遷移腳本。
• 過程內避免長事務、大量資料處理，防止鎖爭用。
• OUT/INOUT 參數需用使用者變數 @var 接收，再 SELECT @var 取值。
• 生產環境嚴禁直接在 PHP 頁面建立/修改過程，走審核流程。