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

該表包含所有存儲函數的值.