萬盛學電腦網

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

MySQL 存儲過程語法大全:建立、參數、查看、刪除、調用與權限管理完整指南

建立存儲過程語法

CREATE PROCEDURE proc_name ([proc_parameter[,...]])
[characteristic ...] routine_body

proc_parameter: [ IN | OUT | INOUT ] param_name type

characteristic:
    LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }
  | COMMENT 'string'

最小範例

CREATE PROCEDURE product_pricing()
BEGIN
    SELECT AVG(pro_price) AS price_average FROM products;
END

呼叫方式

CALL product_pricing();  -- 括號不可省略

帶參數範例(三種模式)

CREATE PROCEDURE product_pricing(
    OUT p_min DECIMAL(8,2),
    OUT p_max DECIMAL(8,2),
    OUT p_avg DECIMAL(8,2)
)
BEGIN
    SELECT MIN(prod_price) INTO p_min FROM products;
    SELECT MAX(prod_price) INTO p_max FROM products;
    SELECT AVG(prod_price) INTO p_avg FROM products;
END

呼叫:

CALL product_pricing(@min, @max, @avg);
SELECT @min, @max, @avg;

查看存儲過程

方法一:查詢 mysql.proc 表

SELECT `name` FROM mysql.proc 
WHERE db = 'your_db' AND `type` = 'PROCEDURE';

方法二:SHOW 指令

SHOW PROCEDURE STATUS [WHERE db = 'your_db'];
SHOW CREATE PROCEDURE proc_name;

刪除存儲過程

DROP PROCEDURE [IF EXISTS] proc_name;

變數宣告與賦值

DECLARE var_name [, var_name...] datatype [DEFAULT value];
SET var_name = expr [, var_name = expr] ...

資料型別:INT, FLOAT, DATE, VARCHAR(n), DECIMAL(p,s) 等。

流程控制

IF condition THEN ... ELSE ... END IF;
CASE WHEN ... THEN ... ELSE ... END CASE;
WHILE condition DO ... END WHILE;
REPEAT ... UNTIL condition END REPEAT;
LOOP ... END LOOP;
ITERATE / LEAVE label;

錯誤處理

DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @dup = 1;
DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;

權限管理

GRANT EXECUTE ON PROCEDURE db.proc_name TO 'user'@'host';
GRANT CREATE ROUTINE, ALTER ROUTINE ON db.* TO 'dev'@'%';
copyright © 萬盛學電腦網 all rights reserved