2008-05-20

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

关键字: 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;
评论
发表评论

您还没有登录，请登录后发表评论

hideto
搜索本博客
我的相册
A6bdc31c-c66e-468e-961e-9cc721e82adc-thumb
screenshot
共 1 张
存档
最新评论