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.

循环

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声明，对一种错误提出处置:

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

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