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

创建和维护存储程序

[b]1，创建和维护存储程序语法[/b]
1)CREATE PROCEDURE
[code]
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
[/code]
2)CREATE FUNCTION
[code]
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
[/code]
3)CREATE TRIGGER
[code]
CREATE [DEFINER = {user|CURRENT_USER}] TRIGGER trigger_name
{BEFORE|AFTER}
{UPDATE|INSERT|DELETE}
ON table_name
FOR EACH ROW
trigger_statements
[/code]
4)ALTER PROCEDURE/FUNCTION
[code]
ALTER {PROCEDURE|FUNCTION} procedure_or_function_name
[SQL SECURITY {DEFINER|INVOKER}]
[COMMENT comment_string]
[/code]
5)DROP PROCEDURE/FUNCTION/TRIGGER
[code]
DROP {PROCEDURE|FUNCTION|TRIGGER} {IF EXISTS} program_name
[/code]

[b]2，存储程序的分号问题[/b]
MySQL使用分号“;”作为SQL语句的结束，但是存储程序的代码块里面经常也需要用到分号，这样就会导致错误：
[code]
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 >
[/code]
为了避免此错误，可以使用DELIMITER语句修改分隔符：
[code]
mysql > DELIMITER $$

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

[b]3，显示存储程序信息[/b]
显示PROCEDURE/FUNCTION STATUS
[code]
SHOW {PROCEDURE|FUNCTION} STATUS [LIKE pattern]
[/code]
显示存储过程和存储function信息
[code]
select * from information_schema.routtines;
[/code]
显示trigger信息
[code]
select * from information_schema.triggers;
[/code]