CREATE PROCEDURE sp_name()
BEGIN
-- SQL 語句
INSERT INTO user (username, sex) VALUES ('test', '0');
END;
| 模式 | 關鍵字 | 方向 | 說明 |
|---|---|---|---|
| 輸入 | IN | 調用者 → 過程 | 預設,不可在過程中修改返回 |
| 輸出 | OUT | 過程 → 調用者 | 需在調用時用用戶變數 @var 接收 |
| 輸入輸出 | INOUT | 雙向 | 調用前賦值,調用後可被修改 |
-- 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 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;
$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();");
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);");
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 )
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';