跳转到内容

MySQL/Stored Programs

维基教科书,自由的教学读本

基础知识:标准SQL的过程化扩展

[编辑]

分隔符

[编辑]

MySQL使用一个字符作为SQL语句之间的分隔符。缺省是 ';'。当创建一个存储过程时,过程体内部使用';'分隔了多条语句。这时不希望MySql理解 ';'是CREATE语句的结束标志,这就需要给出其他的分隔符。

例如,指定 '|'为分隔符:

 delimiter |
 CREATE EVENT myevent
    ON SCHEDULE EVERY 1 DAY
    DO
      BEGIN
        TRUNCATE `my_db`.`my_table`;
        TRUNCATE `my_db`.`another_table`;
      END |
 delimiter ;

流程控制

[编辑]

关键字: IF, CASE, ITERATE, LEAVE LOOP, WHILE, REPEAT[1].

循环

[编辑]
WHILE循环
[编辑]

WHILE循环

[ label: ]   WHILE  expression DO
    statements
END   WHILE   [ label ]  ;
DELIMITER $$
CREATE PROCEDURE counter()
  BEGIN
    DECLARE x INT;
    SET x = 1;
    WHILE x  <= 5 DO
      SET  x = x + 1;
    END WHILE;
    SELECT x;  -- 6
  END$$
DELIMITER ;
LOOP循环
[编辑]

LOOP循环

[ label: ]  LOOP
   statements
 END  LOOP  [ label ] ;
DELIMITER $$
CREATE PROCEDURE counter2()
  BEGIN
    DECLARE x INT;
    SET x = 1;
    boucle1: LOOP
      SET  x = x + 1;
      IF x > 5 THEN
         LEAVE boucle1;
      END IF;
    END LOOP boucle1;
    SELECT x;  -- 6
  END$$
DELIMITER ;
REPEAT循环
[编辑]

REPEAT UNTIL循环:

[ label: ]  REPEAT
statements
UNTIL expression
END  REPEAT  [ label ]  ;
DELIMITER $$
CREATE PROCEDURE counter3()
  BEGIN
    DECLARE x INT;
    SET x = 1;
    REPEAT
      SET x = x + 1; 
    UNTIL x > 5
    END REPEAT;
    SELECT x;  -- 6
  END$$
DELIMITER ;

错误处理

[编辑]

handler声明,对一种错误提出处置[2]:

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION

Moreover, the error type can be indicated:

DECLARE CONTINUE HANDLER FOR SQLSTATE [VALUE] sqlstate_value
DECLARE CONTINUE HANDLER FOR SQLWARNING
DECLARE CONTINUE HANDLER FOR NOT FOUND

存储过程

[编辑]

存储过程是用SQL(以及一些过程式扩展)来编写。使用CALL命令调用存储过程。

存储过程如果返回一个结果,叫做FUNCTION;否则叫做PROCEDURE。

存储过程的优点

[编辑]
  • 降低网络交通:只需要发出一条语句。存储过程里面可能有很多条语句。
  • 保持数据库里面的逻辑
  • 是可重用的模块
  • 可以修改存储过程而不必改变应用程序
  • 存储过程的调用者不需要有表的读写权限
  • 调用存储过程比执行一条SQL语句更快

管理存储过程

[编辑]

创建过程

[编辑]
 CREATE DEFINER = `root`@`localhost` PROCEDURE `Module1` ( ) NOT DETERMINISTIC NO SQL SQL SECURITY DEFINER OPTIMIZE TABLE wiki1_page;

CALL

[编辑]
 CALL `Module1` ();

删除存储过程

[编辑]
 DROP PROCEDURE `Module1` ;

修改

[编辑]
 DROP PROCEDURE `Module1` ;
 CREATE DEFINER = `root`@`localhost` PROCEDURE `Module1` ( ) NOT DETERMINISTIC NO SQL SQL SECURITY DEFINER
 BEGIN
  OPTIMIZE TABLE wiki1_page;
  OPTIMIZE TABLE wiki1_user;
 END

元数据

[编辑]

SHOW FUNCTION / PROCEDURE状态

[编辑]
 SHOW PROCEDURE STATUS;

SHOW CREATE FUNCTION / PROCEDURE

[编辑]
 SHOW CREATE PROCEDURE Module1;

INFORMATION_SCHEMA.ROUTINES

[编辑]

虚拟数据库INFORMATION_SCHEMA有一张表叫做`ROUTINES`

INFORMATION_SCHEMA.PARAMETERS

[编辑]

该表包含所有存储函数的值.

参考文献

[编辑]
  1. http://dev.mysql.com/doc/refman/5.0/en/flow-control-statements.html
  2. http://dev.mysql.com/doc/refman/5.7/en/declare-handler.html