MySQL存储程序之创建和维护存储程序

创建和维护存储程序

1，创建和维护存储程序语法
1)CREATE PROCEDURE

CREATE PROCEDURE procedure_name ([parameter[,...])
  [LANGUAGE SQL]
  [ [NOT] DETERMINISTIC]
  [ {CONTAINS SQL|MODIFIES SQL DATA|READS SQL DATA|NO SQL} ]
  [SQL SECURITY {DEFINER|INVOKER}]
  [COMMENT comment_string]
  procedure_statements

/* parameters */
[{IN|OUT|INOUT}] parameter_name datatype

2)CREATE FUNCTION

CREATE FUNCTION function_name ([parameter[,...])
    RETURNS datatype
    [LANGUAGE SQL]
    [ [NOT] DETERMINISTIC]
    [ {CONTAINS SQL|NO SQL|MODIFIES SQL DATA|READS SQL DATA} ]
    [SQL SECURITY {DEFINER|INVOKER}]
    [COMMENT comment_string]
    function_statements

3)CREATE TRIGGER

CREATE [DEFINER = {user|CURRENT_USER}] TRIGGER trigger_name
    {BEFORE|AFTER}
    {UPDATE|INSERT|DELETE}
    ON table_name
    FOR EACH ROW
    trigger_statements

4)ALTER PROCEDURE/FUNCTION

ALTER {PROCEDURE|FUNCTION} procedure_or_function_name
    [SQL SECURITY {DEFINER|INVOKER}]
    [COMMENT comment_string]

5)DROP PROCEDURE/FUNCTION/TRIGGER

DROP {PROCEDURE|FUNCTION|TRIGGER} {IF EXISTS} program_name

2，存储程序的分号问题
MySQL使用分号“;”作为SQL语句的结束，但是存储程序的代码块里面经常也需要用到分号，这样就会导致错误：

mysql > CREATE PROCEDURE HelloWorld()
     -> BEGIN
     ->   SELECT 'Hello World';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'SELECT 'Hello
World'' at line 3
mysql >

为了避免此错误，可以使用DELIMITER语句修改分隔符：

mysql > DELIMITER $$

mysql > CREATE PROCEDURE HelloWorld()
     -> BEGIN
     ->    SELECT 'Hello World';
     -> END$$
Query OK, 0rows affected (0.00 sec)

3，显示存储程序信息
显示PROCEDURE/FUNCTION STATUS

SHOW {PROCEDURE|FUNCTION} STATUS [LIKE pattern]

显示存储过程和存储function信息

select * from information_schema.routtines;

显示trigger信息

select * from information_schema.triggers;