萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL 5 存儲過程入門完全指南:創建、參數、調用、刪除、流程控制、變量、PHP 調用實戰

存儲過程基礎語法

1. 創建無參存儲過程

CREATE PROCEDURE sp_name()
BEGIN
    -- SQL 語句
    INSERT INTO user (username, sex) VALUES ('test', '0');
END;

2. 參數模式

模式關鍵字方向說明
輸入IN調用者 → 過程預設,不可在過程中修改返回
輸出OUT過程 → 調用者需在調用時用用戶變數 @var 接收
輸入輸出INOUT雙向調用前賦值,調用後可被修改

3. 帶參數創建範例

-- IN 參數
CREATE PROCEDURE check_score(IN score INT)
BEGIN
    IF score >= 60 THEN SELECT 'pass'; ELSE SELECT 'fail'; END IF;
END;

-- OUT 參數
CREATE PROCEDURE get_max_score(OUT max_score INT)
BEGIN
    SET max_score = 100;
END;

-- INOUT 參數
CREATE PROCEDURE filter_by_sex(INOUT sex_flag INT)
BEGIN
    SELECT * FROM user WHERE sex = sex_flag;
END;

調用與刪除

-- 調用(必須加括號)
CALL sp_name();
CALL check_score(70);
CALL get_max_score(@max_score);
SELECT @max_score;

-- 刪除
DROP PROCEDURE IF EXISTS sp_name;

流程控制結構

IF-THEN-ELSE

IF condition THEN
    statements;
ELSEIF condition THEN
    statements;
ELSE
    statements;
END IF;

循環結構

-- WHILE
WHILE expr DO
    statements;
END WHILE;

-- REPEAT UNTIL
REPEAT
    statements;
UNTIL expr
END REPEAT;

-- LOOP + LEAVE
label: LOOP
    statements;
    IF condition THEN LEAVE label; END IF;
END LOOP label;

變量聲明與賦值

-- 局部變量 (僅在 BEGIN...END 內有效)
DECLARE var_name TYPE [DEFAULT value];

-- 賦值
SET var_name = expr;
SET a = 1, b = 2;

-- SELECT INTO 賦值 (單行結果)
SELECT col1, col2 INTO var1, var2 FROM table WHERE ... LIMIT 1;

重要:變量名不能與欄位名同名,否則 SELECT ... INTO 會歧義解析為變量。

實用元數據查詢

-- 列出所有存儲過程
SHOW PROCEDURE STATUS;

-- 查看特定過程定義
SHOW CREATE PROCEDURE sp_name;

PHP 調用 MySQL 存儲過程實戰

1. 無參過程

$conn = mysql_connect('localhost','root','root');
mysql_select_db('test', $conn);
mysql_query("CREATE PROCEDURE myproce() BEGIN INSERT INTO user (id,username,sex) VALUES (NULL,'s','0'); END;");
mysql_query("CALL test.myproce();");

2. IN 參數

mysql_query("CREATE PROCEDURE myproce2(IN score INT) BEGIN IF score>=60 THEN SELECT 'pass'; ELSE SELECT 'no'; END IF; END;");
mysql_query("CALL test.myproce2(70);");

3. OUT 參數(需兩步:調用 + 查詢用戶變數)

mysql_query("CREATE PROCEDURE myproce3(OUT score INT) BEGIN SET score=100; END;");
mysql_query("CALL test.myproce3(@score);");
$result = mysql_query('SELECT @score;');
$row = mysql_fetch_array($result);
print_r($row);  // Array ( [0] => 100, [@score] => 100 )

4. INOUT 參數

mysql_query("CREATE PROCEDURE myproce4(INOUT sexflag INT) BEGIN SELECT * FROM user WHERE sex = sexflag; END;");
mysql_query("SET @sexflag = 1;");
mysql_query("CALL test.myproce4(@sexflag);");
// 結果集在調用過程中直接返回

進階提示

  • 標籤跳轉:label: BEGIN ... LEAVE label; ... END label; 可跳出嵌套塊
  • 游標:DECLARE cur CURSOR FOR SELECT ...; OPEN cur; FETCH cur INTO ...; CLOSE cur; 處理結果集逐行邏輯
  • 條件處理:DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @err=1; 捕獲異常
  • 權限:調用者需 EXECUTE 權限,GRANT EXECUTE ON PROCEDURE db.sp TO 'user'@'host';
  • 效能:存儲過程減少網路往返,但複雜邏輯建議在應用層完成,資料庫專注資料操作
copyright © 萬盛學電腦網 all rights reserved