跳至內容

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