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;
SELECT `name` FROM mysql.proc
WHERE db = 'your_db' AND `type` = 'PROCEDURE';
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'@'%';