第20章:儲存程式和函數

目錄

20.1. 儲存程式和授權資料表

20.2. 儲存程式的語法

20.2.1. CREATE PROCEDURE和CREATE FUNCTION

20.2.2. ALTER PROCEDURE和ALTER FUNCTION

20.2.3. DROP PROCEDURE和和DROP FUNCTION

20.2.4. SHOW CREATE PROCEDURE和SHOW CREATE FUNCTION

20.2.5. SHOW PROCEDURE STATUS和SHOW FUNCTION STATUS

20.2.6. CALL語句

20.2.7. BEGIN ... END復合語句

20.2.8. DECLARE語句

20.2.9. 儲存程式中的變數

20.2.10. 條件和處理程式

20.2.11. 光標

20.2.12. 流程控制構造

20.3. 儲存程式、函數、觸發程式和複製:常見問題

20.4. 儲存子程式和觸發程式的二進制日誌功能

MySQL 5.1版支援存儲程式和函數。一個儲存程式是可以被儲存在伺服器中的一套SQL語句。一旦它被儲存了,客戶端不需要再重新發佈單獨的語句,而是可以引用儲存程式來替代

下面一些情況下儲存程式尤其有用:

·         當用不同語言編寫多客戶應用程式,或多客戶應用程式在不同平台上運行且需要執行相同的資料庫操作之時。

·         安全極為重要之時。比如,銀行對所有普通操作使用儲存程式。這提供一個堅固而安全的環境,程式可以確保每一個操作都被妥善記入日誌。在這樣一個設置中,應用程式和用戶不可能直接訪問資料庫資料表,但是僅可以執行指定的儲存程式

儲程式可以提供改良後的性能,因為只有較少的訊息需要在伺服器和客戶算之間傳送。代價是增加資料庫伺服器系統的負荷,因為更多的工作在伺服器這邊完成,更少的在客戶端(應用程式)那邊完成上。如果許多客戶端機器(比如網頁伺服器)只由一個或少數幾個資料庫伺服器提供服務,可以考慮一下儲存程式。

儲存程式也允許您在資料庫伺服器上有函數庫。這是一個被現代應用程式語言共享的特徵,它允許這樣的內部設計,比如通過使用類。使用這些客戶端應用程式語言特徵對甚至於資料庫使用範圍以外的編程人員都有好處。

MySQL為儲存程式遵循SQL:2003語法,這個語法也被用在IBM的DB2資料庫上。

MySQL對儲存程式的實現還在進度中。所有本章敘述的語法都被支援,在有限制或延伸的地方會恰當地指出來。有關使用 儲存程式的限制的更多討論在附錄 I, 特性限制裡提到。

20.4節,「儲存子程式和觸發程式的二進制日誌功能”裡所說的, 儲存子程式的二進制日誌功能已經完成。

20.1. 儲存程式和授權資料表

儲存程式需要在mysql資料庫中有proc資料表。這個資料表在MySQL 5.1安裝過程中建立。如果您從早期的版本升級到MySQL 5.1 ,請確定更新您的授權資料表以確保proc資料表的存在。請參閱2.10.2節 “升級授權資料表”

在MySQL 5.1中,授權系統如下考慮儲存子程式:

·         建立儲存子程式需要CREATE ROUTINE權限。

·         提醒或移除儲存子程式需要ALTER ROUTINE權限。這個權限自動授予子程式的建立者。

·         執行子程式需要EXECUTE權限。然而,這個權限自動授予 子程式的建立者。同樣,子程式預設的SQL SECURITY 特徵是DEFINER,它允許用該子程式訪問資料庫的用戶與執行子程式聯繫到一起。

20.2. 儲存程式的語法

20.2.1. CREATE PROCEDURE和CREATE FUNCTION

20.2.2. ALTER PROCEDURE和ALTER FUNCTION

20.2.3. DROP PROCEDURE和DROP FUNCTION

20.2.4. SHOW CREATE PROCEDURE和SHOW CREATE FUNCTION

20.2.5. SHOW PROCEDURE STATUS和SHOW FUNCTION STATUS

20.2.6. CALL語句

20.2.7. BEGIN ... END復合語句

20.2.8. DECLARE語句

20.2.9. 儲存程式中的變數

20.2.10. 條件和處理程式

20.2.11. 光標

20.2.12. 流程控制構造

儲存程式和函數是用CREATE PROCEDURE和CREATE FUNCTION語句建立的子程式。一個子程式要麼是一個程式要麼是一個函數。使用CALL語句來使用 程式,程式只能用輸出變數傳回值。就像別其它函數使用一樣,函數可以被從語句外使用(即通過引用函數名),函數能返回標量值。 儲存子程式也可以使用其它儲存子程式。

在MySQL 5.1中,一個儲存子程式或函數與特定的資料庫相聯繫。這裡有幾個意思:

·         當一個子程式被使用時,一個隱含的USE db_name 被執行(當子程式終止時停止執行)。儲存子程式內的USE語句時不允許的。

·         您可以使用資料庫名限定子程式名。這可以被用來引用一個不在當前資料庫中的子程式。比如,要引用一個與test資料庫關聯的儲存程式p或函數f,您可以說CALL test.p()或test.f()。

·         資料庫移除的時候,與它關聯的所有儲存子程式也都被移除。

MySQL 支援非常有用的延伸,即它允許在儲存程式中使用常規的SELECT語句(那就是說,不使用光標或 局部變數)。這個一個查詢的結果包被簡單地直接送到客戶端。多SELECT語句生成多個結果包,所以客戶端必須使用支援多結果包的MySQL客戶端庫。這意味這客戶端必須 使用至少MySQL 4.1以來的近期版本上的客戶端庫。

下面一節描述用來建立,改變,移除和查詢儲存程式和函數的語法。

20.2.1CREATE PROCEDURECREATE FUNCTION

CREATE PROCEDURE sp_name ([proc_parameter[,...]])
    [characteristic ...] routine_body
 
CREATE FUNCTION sp_name ([func_parameter[,...]])
    RETURNS type
    [characteristic ...] routine_body
    
    proc_parameter:
    [ IN | OUT | INOUT ] param_name type
    
    func_parameter:
    param_name type
 
type:
    Any valid MySQL data type
 
characteristic:
    LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }
  | COMMENT 'string'
 
routine_body:
    Valid SQL procedure statement or statements

這些語句建立儲存子程式。要在MySQL 5.1中建立子程式,必須具有CREATE ROUTINE權限,並且ALTER ROUTINE和EXECUTE權限被自動授予它的建立者。如果二進制日誌功能被允許,您也可能需要SUPER權限,請參閱20.4節「儲存子程式和觸發程式的二進制日誌功能”

預設地,子程式與當前資料庫關聯。要明確地把子程式與一個給定資料庫關聯起來,可以在建立子程式的時候指定其名字為db_name.sp_name

如果子程式名和內建的SQL函數名一樣,定義子程式時,您需要在這個名字和隨後括號中間插入一個空格,否則發生語法錯誤。當您隨後使用子程式的時候也要插入。為此,即使有可能出現這種情況,我們還是建議最好避免給您自己的 儲存子程式取與存在的SQL函數一樣的名字。

由括號包圍的參數列必須總是存在。如果沒有參數,也該使用一個空參數列()。每個參數 預設都是一個IN參數。要指定為其它參數,可在參數名之前使用關鍵詞 OUT或INOUT

注意: 指定參數為IN, OUT, 或INOUT 只對PROCEDURE是合法的。(FUNCTION參數總是被認為是IN參數)

RETURNS字句只能對FUNCTION指定,對函數而言這是強制的。它用來指定函數的返回類型,而且函數體必須包含一個RETURN value語句。

routine_body 包含合法的SQL過程語句。可以使用復合語句語法,請參閱20.2.7節,「BEGIN ... END復合語句。復合語句可以包含 聲明,循環和其它控制結構語句。這些語句的語法在本章後免介紹,舉例,請參閱20.2.8節,「DECLARE語句20.2.12節,「流程控制構造」

CREATE FUNCTION語句被用在更早的MySQL版本上支援UDF (自行定義函數)。請參閱27.2節,「給MySQL新增新函數”。 UDF繼續被支援,即使現在 有了儲存函數。UDF會被認為一個外部儲存函數。然而,不要讓儲存函數與UDF函數共享名字空間。

外部儲存程式的框架將在不久的將來引入。這將允許您用SQL之外的語言編寫儲存程式。最可能的是,第一個被支援語言是PHP,因為核心PHP引擎很小,線程安全,且可以被方便地嵌入。因為框架是公開的,它希望許多其它語言也能被支援。

如果程式或線程總是對同樣的輸入參數產生同樣的結果,則被認為它是“確定的”,否則就是“非確定”的。如果既沒有給定DETERMINISTIC也沒有給定NOT DETERMINISTIC,預設的就是NOT DETERMINISTIC。

為進行複製,使用NOW()函數(或它的同義詞)或RAND()函數會不必要地使得一個子程式非確定。對NOW()而言,二進制日誌包括時間戳並被正確複製。RAND() 只要在一個子程式被內應用一次也會被正確複製。(您可以把子程式執行時間戳和隨機數種子認為強制輸入,它們在主從上是同樣的。)

當前來講,DETERMINISTIC特徵被接受,但還沒有被最佳化程式所使用。然而如果二進制日誌功能被允許了,這個特徵影響到MySQL是否會接受子程式定義。請參閱20.4,「儲存子程式和觸發程式的二進制日誌功能”

一些特徵提供子程式使用數據的內在訊息。CONTAINS SQL資料表示子程式不包含讀或寫數據的語句。NO SQL資料表示子程式不包含SQL語句。READS SQL DATA資料表示子程式包含讀數據的語句,但不包含寫數據的語句。MODIFIES SQL DATA資料表示子程式包含寫數據的語句。如果這些特徵沒有明確給定,預設的是CONTAINS SQL。

SQL SECURITY特徵可以用來指定 子程式該用建立子程式者的授權來執行,還是使用使用者的授權來執行。預設值是DEFINER。在SQL:2003中者是一個新特性。建立者或使用者必須由訪問 子程式關聯的資料庫的授權。在MySQL 5.1中,必須有EXECUTE權限才能執行子程式。必須擁有這個權限的用戶要麼是定義者,要麼是使用者,這取決於SQL SECURITY特徵是如何設置的。

MySQL儲存sql_mode系統變數設置,這個設置在子程式被建立的時候起作用,MySQL總是強制使用這個設置來執行 子程式。

COMMENT子句是一個MySQL的延伸,它可以被用來描述 儲存程式。這個訊息被SHOW CREATE PROCEDURE和 SHOW CREATE FUNCTION語句來顯示。

MySQL允許子程式包含DDL語句,如CREATE和DROP。MySQL也允許儲存程式(但不是 儲存函數)包含SQL 交互語句,如COMMIT。儲存函數不可以包含那些做明確的和絕對的提交或者做回滾的語。SQL標準不要求對這些語句的支援,SQL標準聲明每個DBMS提供商可以決定是否允許支援這些語句。

儲存子程式不能使用LOAD DATA INFILE。

返回結果包的語句不能被用在儲存函數種。這包括不使用INTO給變數讀取 列值的SELECT語句,SHOW 語句,及其它諸如EXPLAIN這樣的語句。對於可在函數定義時間被決定要返回一個結果包的語句,發生一個允許從函數錯誤返回結果包的Not(ER_SP_NO_RETSET_IN_FUNC)。對於只可在運行時決定要返回一個結果包的語句, 發生一個不能在給定上下文錯誤返回結果包的PROCEDURE %s (ER_SP_BADSELECT)。

下面是一個使用OUT參數的簡單的儲存程式的例子。例子為,在 程式被定義的時候,用mysql客戶端delimiter命令來把語句定界符從 ;變為//。這就允許用在 程式體中的;定界符被傳遞到伺服器而不是被mysql自己來解釋。

mysql> delimiter //
 
mysql> CREATE PROCEDURE simpleproc (OUT param1 INT)
    -> BEGIN
    ->   SELECT COUNT(*) INTO param1 FROM t;
    -> END
    -> //
Query OK, 0 rows affected (0.00 sec)
 
mysql> delimiter ;
 
mysql> CALL simpleproc(@a);
Query OK, 0 rows affected (0.00 sec)
 
mysql> SELECT @a;
+------+
| @a   |
+------+
| 3    |
+------+
1 row in set (0.00 sec)

當使用delimiter命令時,您應該避免使用反斜槓(\)字元,因為那是MySQL的 轉義字元。

下列是一個例子,一個採用參數的函數使用一個SQL函數執行一個操作,並返回結果:

mysql> delimiter //
 
mysql> CREATE FUNCTION hello (s CHAR(20)) RETURNS CHAR(50)
    -> RETURN CONCAT('Hello, ',s,'!');
    -> //
Query OK, 0 rows affected (0.00 sec)
 
mysql> delimiter ;
 
mysql> SELECT hello('world');
+----------------+
| hello('world') |
+----------------+
| Hello, world!  |
+----------------+
1 row in set (0.00 sec)

如果在儲存函數中的RETURN語句返回一個類型不同於在函數的RETURNS子句中指定類型的值,返回值被強制為恰當的類型。比如,如果一個函數返回一個ENUM或SET值,但是RETURN語句返回一個整數,對於SET成員集的相應的ENUM成員,從函數返回的值是字串。

20.2.2. ALTER PROCEDUREALTER FUNCTION

ALTER {PROCEDURE | FUNCTION} sp_name [characteristic ...]
 
characteristic:
    { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }
  | COMMENT 'string'

這個語句可以被用來改變一個儲存程式或函數的特徵。在MySQL 5.1中,您必須用ALTER ROUTINE權限才可用此子程式。這個權限被自動授予子程式的建立者。如20.4節,「儲存子程式和觸發程式的二進制日誌功能”中所述, 如果二進制日誌功能被允許了,您可能也需要SUPER權限。

在ALTER PROCEDURE和ALTER FUNCTION語句中,可以指定超過一個的改變。

20.2.3. DROP PROCEDUREDROP FUNCTION

DROP {PROCEDURE | FUNCTION} [IF EXISTS] sp_name

這個語句被用來移除一個儲存程式或函數。即,從伺服器移除一個制定的子程式。在MySQL 5.1中,您必須有ALTER ROUTINE權限才可用此子程式。這個權限被自動授予子程式的建立者。

IF EXISTS 子句是一個MySQL的延伸。如果程式或函數不儲存,它防止發生錯誤。產生一個可以用SHOW WARNINGS查看的警告。

20.2.4. SHOW CREATE PROCEDURESHOW CREATE FUNCTION

SHOW CREATE {PROCEDURE | FUNCTION} sp_name

這個語句是一個MySQL的延伸。類似於SHOW CREATE TABLE,它返回一個可用來重新建立已命名 子程式的確切字串。

mysql> SHOW CREATE FUNCTION test.hello\G
*************************** 1. row ***************************
       Function: hello
       sql_mode:
Create Function: CREATE FUNCTION `test`.`hello`(s CHAR(20)) RETURNS CHAR(50)
RETURN CONCAT('Hello, ',s,'!')

20.2.5. SHOW PROCEDURE STATUSSHOW FUNCTION STATUS

SHOW {PROCEDURE | FUNCTION} STATUS [LIKE 'pattern']

這個語句是一個MySQL的延伸。它返回子程式的特徵,如資料庫,名字,類型,建立者及建立和修改日期。如果沒有指定樣式,根據您使用的語句,所有 儲存程式和所有儲存函數的訊息都被列出。

mysql> SHOW FUNCTION STATUS LIKE 'hello'\G
*************************** 1. row ***************************
           Db: test
         Name: hello
         Type: FUNCTION
      Definer: testuser@localhost
     Modified: 2004-08-03 15:29:37
      Created: 2004-08-03 15:29:37
Security_type: DEFINER
      Comment:

您可以從INFORMATION_SCHEMA中的ROUTINES資料表獲得有關儲存子程式的訊息。請參閱23.1.14節,「INFORMATION_SCHEMA ROUTINES 資料表

20.2.6. CALL語句

CALL sp_name([parameter[,...]])

CALL語句使用一個先前用CREATE PROCEDURE建立的程式。

CALL語句可以用 聲明為OUT或的INOUT參數的參數給它的使用者傳回值。它也“返回”受影響的行數,客戶端程式可以在SQL級別通過使用ROW_COUNT()函數獲得這個數,從C中是使用the mysql_affected_rows() C API函數獲得。

20.2.7. BEGIN ... END復合語句

[begin_label:] BEGIN
    [statement_list]
END [end_label]

儲存子程式可以使用BEGIN ... END復合語句來包含多個語句。statement_list 代資料表一個或多個語句的列資料表。statement_list之內每個語句都必須用分號(;)來結尾。

復合語句可以被標記。除非begin_label存在,否則end_label不能被給出,並且如果二者都存在,他們必須是同樣的。

請注意,可選的[NOT] ATOMIC子句現在還不被支援。這意味著在指令塊的開始沒有交互的儲存點被設置,並且在上下文中用到的BEGIN子句對當前交互動作沒有影響。

使用多重語句需要客戶端能發送包含語句定界符;的查詢字串。這個符號在命令行客戶端被用delimiter命令來處理。改變查詢結尾定界符;(比如改變為//)使得; 可被用在子程式體中。

20.2.8. DECLARE語句

DECLARE語句被用來把不同項目局域到一個 子程式:局部變數(請參閱20.2.9節,「儲存程式中的變數”),條件和 處理程式(請參閱20.2.10節,「條件和處理程式”) 及光標(請參閱20.2.11節,「光標”)。SIGNAL和RESIGNAL語句當前還不被支援。

DECLARE僅被用在BEGIN ... END復合語句裡,並且必須在復合語句的開頭,在任何其它語句之前。

光標必須在聲明處理程式之前被聲明,並且變數和條件必須在聲明光標或處理程式之前被聲明。

20.2.9. 儲存程式中的變數

20.2.9.1. DECLARE局部變數

20.2.9.2. 變數SET語句

20.2.9.3. SELECT ... INTO語句

您可以在子程式中聲明並使用變數。

20.2.9.1. DECLARE局部變數

DECLARE var_name[,...] type [DEFAULT value]

這個語句被用來聲明局部變數。要給變數提供一個預設值,請包含一個DEFAULT子句。值可以被指定為一個資料表達式,不需要為一個常數。如果沒有DEFAULT子句,初始值為NULL。

局部變數的作用範圍在它被聲明的BEGIN ... END塊內。它可以被用在嵌套的塊中,除了那些用相同名字 聲明變數的塊。

20.2.9.2. 變數SET語句

SET var_name = expr [, var_name = expr] ...

在儲存程式中的SET語句是一般SET語句的延伸版本。被參考變數可能是子程式內聲明的變數,或者是全局伺服器變數。

在儲存程式中的SET語句作為預先存在的SET語法的一部分來實現。這允許SET a=x, b=y, ...這樣的延伸語法。其中不同的變數類型(局域 聲明變數及全局和集體變數)可以被混合起來。這也允許把局部變數和一些只對系統變數有意義的選項合併起來。在那種情況下,此選項被識別,但是被忽略了。

20.2.9.3. SELECT ... INTO語句

SELECT col_name[,...] INTO var_name[,...] table_expr

這個SELECT語法把選定的列直接儲存到變數。因此,只有單一的行可以被取回。

SELECT id,data INTO x,y FROM test.t1 LIMIT 1;

注意,用戶變數名在MySQL 5.1中是對大小寫不敏感的。請參閱9.3節,「用戶變數”

重要: SQL變數名不能和列名一樣。如果SELECT ... INTO這樣的SQL語句包含一個對列的參考,並包含一個與列相同名字的 局部變數,MySQL當前把參考解釋為一個變數的名字。例如,在下面的語句中,xname 被解釋為到xname variable 的參考而不是到xname column的:

CREATE PROCEDURE sp1 (x VARCHAR(5))
  BEGIN
    DECLARE xname VARCHAR(5) DEFAULT 'bob';
    DECLARE newname VARCHAR(5);
    DECLARE xid INT;
    
    SELECT xname,id INTO newname,xid 
      FROM table1 WHERE xname = xname;
    SELECT newname;
  END;

當這個程式被使用的時候,無論table.xname列的值是什麼,變數newname將返回值‘bob’。

請參閱I.1節,「儲存子程式和觸發程式的限制”

20.2.10. 條件和處理程式

20.2.10.1. DECLARE條件

20.2.10.2. DECLARE處理程式

特定條件需要特定處理。這些條件可以聯繫到錯誤,以及子程式中的一般流程控制。

20.2.10.1. DECLARE條件

DECLARE condition_name CONDITION FOR condition_value
 
condition_value:
    SQLSTATE [VALUE] sqlstate_value
  | mysql_error_code

這個語句指定需要特殊處理的條件。它將一個名字和指定的錯誤條件關聯起來。這個名字可以隨後被用在DECLARE HANDLER語句中。請參閱20.2.10.2節,「DECLARE處理程式

除了SQLSTATE值,也支援MySQL錯誤代碼。

20.2.10.2. DECLARE處理程式

DECLARE handler_type HANDLER FOR condition_value[,...] sp_statement
 
handler_type:
    CONTINUE
  | EXIT
  | UNDO
 
condition_value:
    SQLSTATE [VALUE] sqlstate_value
  | condition_name
  | SQLWARNING
  | NOT FOUND
  | SQLEXCEPTION
  | mysql_error_code

這個語句指定每個可以處理一個或多個條件的處理程式。如果產生一個或多個條件,指定的語句被執行。

對一個CONTINUE處理程式,當前子程式的執行在執行 處理程式語句之後繼續。對於EXIT處理程式,當前BEGIN...END復合語句的執行被終止。UNDO 處理程式類型語句還不被支援。

·         SQLWARNING是對所有以01開頭的SQLSTATE代碼的速記。

·         NOT FOUND是對所有以02開頭的SQLSTATE代碼的速記。

·         SQLEXCEPTION是對所有沒有被SQLWARNING或NOT FOUND捕獲的SQLSTATE代碼的速記。

除了SQLSTATE值,MySQL錯誤代碼也不被支援。

例如:

mysql> CREATE TABLE test.t (s1 int,primary key (s1));
Query OK, 0 rows affected (0.00 sec)
 
mysql> delimiter //
 
mysql> CREATE PROCEDURE handlerdemo ()
    -> BEGIN
    ->   DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @x2 = 1;
    ->   SET @x = 1;
    ->   INSERT INTO test.t VALUES (1);
    ->   SET @x = 2;
    ->   INSERT INTO test.t VALUES (1);
    ->   SET @x = 3;
    -> END;
    -> //
Query OK, 0 rows affected (0.00 sec)
 
mysql> CALL handlerdemo()//
Query OK, 0 rows affected (0.00 sec)
 
mysql> SELECT @x//
    +------+
    | @x   |
    +------+
    | 3    |
    +------+
    1 row in set (0.00 sec)

注意到,@x是3,這表明MySQL被執行到程式的末尾。如果DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @x2 = 1; 這一行不在,第二個INSERT因PRIMARY KEY強制而失敗之後,MySQL可能已經採取 預設(EXIT)路徑,並且SELECT @x可能已經返回2。

20.2.11. 光標

20.2.11.1.聲明光標

20.2.11.2. 光標OPEN語句

20.2.11.3. 光標FETCH語句

20.2.11.4. 光標CLOSE語句

簡單光標在儲存程式和函數內被支援。語法如同在嵌入的SQL中。光標當前是不敏感的,只讀的及不滾動的。不敏感意為伺服器可以活不可以複製它的結果資料表。

光標必須在聲明處理程式之前被聲明,並且變數和條件必須在聲明光標或處理程式之前被聲明。

例如:

CREATE PROCEDURE curdemo()
BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE a CHAR(16);
  DECLARE b,c INT;
  DECLARE cur1 CURSOR FOR SELECT id,data FROM test.t1;
  DECLARE cur2 CURSOR FOR SELECT i FROM test.t2;
  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
 
  OPEN cur1;
  OPEN cur2;
 
  REPEAT
    FETCH cur1 INTO a, b;
    FETCH cur2 INTO c;
    IF NOT done THEN
       IF b < c THEN
          INSERT INTO test.t3 VALUES (a,b);
       ELSE
          INSERT INTO test.t3 VALUES (a,c);
       END IF;
    END IF;
  UNTIL done END REPEAT;
 
  CLOSE cur1;
  CLOSE cur2;
END

20.2.11.1.聲明光標

DECLARE cursor_name CURSOR FOR select_statement

這個語句聲明一個光標。也可以在子程式中定義多個光標,但是一個塊中的每一個光標必須有唯一的名字。

SELECT語句不能有INTO子句。

20.2.11.2. 光標OPEN語句

OPEN cursor_name

這個語句打開先前聲明的光標。

20.2.11.3. 光標FETCH語句

FETCH cursor_name INTO var_name [, var_name] ...

這個語句用指定的打開光標讀取下一行(如果有下一行的話),並且前進光標指針。

20.2.11.4. 光標CLOSE語句

CLOSE cursor_name

這個語句關閉先前打開的光標。

如果未被明確地關閉,光標在它被聲明的復合語句的末尾被關閉。

20.2.12. 流程控制構造

20.2.12.1. IF語句

20.2.12.2. CASE語句

20.2.12.3. LOOP語句

20.2.12.4. LEAVE語句

20.2.12.5. ITERATE語句

20.2.12.6. REPEAT語句

20.2.12.7. WHILE語句

IF, CASE, LOOP, WHILE, ITERATE, 及 LEAVE 構造被完全實現。

這些構造可能每個包含要麼一個單獨語句,要麼是使用BEGIN ... END復合語句的一塊語句。構造可以被嵌套。

目前還不支援FOR循環。

20.2.12.1. IF語句

IF search_condition THEN statement_list
    [ELSEIF search_condition THEN statement_list] ...
    [ELSE statement_list]
END IF

IF實現了一個基本的條件構造。如果search_condition求值為真,相應的SQL語句列資料表被執行。如果沒有search_condition匹配,在ELSE子句裡的語句列資料表被執行。statement_list可以包括一個或多個語句。

請注意,也有一個IF() 函數,它不同於這裡描述的IF語句。請參閱12.2節,「控制流程函數”

20.2.12.2. CASE語句

CASE case_value
    WHEN when_value THEN statement_list
    [WHEN when_value THEN statement_list] ...
    [ELSE statement_list]
END CASE

Or:

CASE
    WHEN search_condition THEN statement_list
    [WHEN search_condition THEN statement_list] ...
    [ELSE statement_list]
END CASE

儲存程式的CASE語句實現一個複雜的條件構造。如果search_condition 求值為真,相應的SQL被執行。如果沒有搜索條件匹配,在ELSE子句裡的語句被執行。

注意:這裡介紹的用在 儲存程式裡的CASE語句與12.2節,「控制流程函數」裡描述的SQL CASE資料表達式的CASE語句有輕微不同。這裡的CASE語句不能有ELSE NULL子句,並且用END CASE替代END來終止。

20.2.12.3. LOOP語句

[begin_label:] LOOP
    statement_list
END LOOP [end_label]

LOOP允許某特定語句或語句群的重複執行,實現一個簡單的循環構造。在循環內的語句一直重複直循環被退出,退出通常伴隨著一個LEAVE 語句。

LOOP語句可以被標注。除非begin_label存在,否則end_label不能被給出,並且如果兩者都出現,它們必須是同樣的。

20.2.12.4. LEAVE語句

LEAVE label

這個語句被用來退出任何被標注的流程控制構造。它和BEGIN ... END或循環一起被使用。

20.2.12.5. ITERATE語句

ITERATE label

ITERATE只可以出現在LOOP, REPEAT, 和WHILE語句內。ITERATE意思為:“再次循環。”

例如:

CREATE PROCEDURE doiterate(p1 INT)
BEGIN
  label1: LOOP
    SET p1 = p1 + 1;
    IF p1 < 10 THEN ITERATE label1; END IF;
    LEAVE label1;
  END LOOP label1;
  SET @x = p1;
END

20.2.12.6. REPEAT語句

[begin_label:] REPEAT
    statement_list
UNTIL search_condition
END REPEAT [end_label]

REPEAT語句內的語句或語句群被重複,直至search_condition 為真。

REPEAT 語句可以被標注。 除非begin_label也存在,end_label才能被用,如果兩者都存在,它們必須是一樣的。

例如

mysql> delimiter //
 
mysql> CREATE PROCEDURE dorepeat(p1 INT)
    -> BEGIN
    ->   SET @x = 0;
    ->   REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT;
    -> END
    -> //
Query OK, 0 rows affected (0.00 sec)
 
mysql> CALL dorepeat(1000)//
Query OK, 0 rows affected (0.00 sec)
 
mysql> SELECT @x//
+------+
| @x   |
+------+
| 1001 |
+------+
1 row in set (0.00 sec)

20.2.12.7. WHILE語句

[begin_label:] WHILE search_condition DO
    statement_list
END WHILE [end_label]

WHILE語句內的語句或語句群被重複,直至search_condition 為真。

WHILE語句可以被標注。 除非begin_label也存在,end_label才能被用,如果兩者都存在,它們必須是一樣的。

例如:

CREATE PROCEDURE dowhile()
BEGIN
  DECLARE v1 INT DEFAULT 5;
 
  WHILE v1 > 0 DO
    ...
    SET v1 = v1 - 1;
  END WHILE;
END

20.3. 儲存程式 、函數、觸發程式及複製:常見問題

  • MySQL 5.1儲存程式和函數對複製起作用嗎? 

    是的,在儲存程式和函數中被執行標準行為被從主MySQL伺服器複製到從伺服器。有少數限制,它們在20.4節,「儲存子程式和 觸發程式二進制日誌功能」中詳述。

  • 在主伺服器上建立的儲存程式和函數可以被複製到從伺服器上麼?

    是的,通過一般DDL語句執行的儲存程式和函數,其在主伺服器上的建立被複製到從伺服器,所以目標將存在兩個伺服器上。對儲存程式和函數的ALTER 和DROP語句也被複製。

  • 行為如何在已複製的儲存程式和函數里發生?

    MySQL紀錄每個發生在儲存程式和函數里的DML事件,並複製這些單獨的行為到從伺服器。執行儲存程式和函數的切實使用不被複製。

  • 對一起使用儲存程式,函數和複製有什麼特別的安全要求麼?

    是的,因為一個從伺服器有權限來執行任何讀自主伺服器的二進制日誌的語句,指定的安全約束因與複製一起使用的儲存程式和函數而存在。如果複製或二進制日誌大體上是激活的(為point-in-time恢復的目的),那麼MySQL DBA 有兩個安全選項可選:

    • 任何想建立儲存程式的用戶必須被賦予SUPER權限。
    • 作為選擇,一個DBA可以設置log_bin_trust_routine_creators系統變數為1,它將會允許有標準CREATE ROUTINE權限的人來建立一個儲存程式和函數。
     
  • 對複製儲存程式和函數的行為有什麼限制?

    嵌入到儲存程式中的不確定(隨機)或時基行不能適當地複製。隨機產生的結果,僅因其本性,是您可預測的和不能被確實克隆的。因此,複製到從伺服器的隨機行為將不會鏡像那些產生在主伺服器上的。注意, 聲明儲存程式或函數為DETERMINISTIC或者在log_bin_trust_routine_creators中設置系統變數為0 將會允許隨即值操作被使用。

    此外,時基行為不能在從伺服器上重新產生,因為在儲存程式中通過對複製使用的二進制日誌來計時這樣的時基行為是不可重新產生的,因為該二進制日誌僅紀錄DML事件且不包括計時約束。

    最後,在大型DML行為(如大批插入)中非交互資料表發生錯誤,該非交互資料表可能經歷複製,在複製版的非交互資料表中主伺服器可以被部分地從DML行為更新。但是因為發生的那個錯誤,對從伺服器沒有更新。 對函數的DML行為,工作區將被用IGNORE關鍵詞來執行,以便於在主伺服器上導致錯誤的更新被忽略,並且不會導致錯誤的更新被複製到從伺服器。

     

  • 上述的限制會影響MySQL作 point-in-time恢復的能力嗎?

    影響複製的同一限制會影響point-in-time恢復。

  •  MySQL要做什麼來改正前述的限制呢?

    將來發行的MySQL預期有一個功能去選擇複製該如何被處理:

    •  基於語句的複製(當前實現)。
    • 行級別複製(它將解決所有早先描述的限制)。
  • 觸發程式對複製起作用麼?

    MySQL 5.1中的觸發程式和複製像在大多數其它資料庫引擎中一樣工作,在那些引擎中,通過觸發程式在主伺服器上執行的行為不被複製到從伺服器。取而代之的是,位於主MySQL伺服器的資料表中的 觸發程式需要在那些存在於任何MySQL從伺服器上的資料表內被建立,以便於觸發程式可以也可以在從伺服器上被激活。

     

  •  一個行為如何通過從主伺服器上複製到從伺服器上的觸發程式來執行呢?

    首先,主伺服器上的觸發程式必須在從伺服器上重建。一旦重建了,複製流程就像其它參與到複製中的標準DML語句一樣工作。例如:考慮一個已經插入觸發程式AFTER的EMP資料表,它位於主MySQL伺服器上。同樣的EMP資料表和AFTER插入 觸發程式也存在於從伺服器上。複製流程可能是:

1.    對EMP做一個INSERT語句。

2.   EMP上的AFTER觸發程式激活。

3.    INSERT語句被寫進二進制日誌。

4.    從伺服器上的複製拾起INSERT語句給EMP資料表,並在從伺服器上執行它。

5.    位於從伺服器EMP上的AFTER觸發程式激活。

20.4. 儲存子程式和觸發程式的二進制日誌功能

,這一節介紹MySQL 5.1如何考慮二進制日誌功能來處理儲存子程式(程式和函數) 。這一節也適用於觸發程式。

二進制日誌包含修改資料庫內容的SQL語句的訊息。這個訊息以描述修改的事件的形式保存起來。

二進制日誌有兩個重要目的:

·         複製的基礎是主伺服器發送包含在二進制日誌裡的事件到從伺服器,從伺服器執行這些事件來造成與對主伺服器造成的同樣的數據改變,請參閱6.2節,「複製概述”

·         特定的數據恢復操作許要使用二進制日誌。備份的檔案被恢復之後,備份後紀錄的二進制日誌裡的事件被重新執行。這些事件把資料庫帶從備份點的日子帶到當前。請參閱5.9.2.2節,「使用備份恢復”

MySQL中,以儲存子程式的二進制日誌功能引發了很多問題,這些在下面討論中列出,作為參考訊息。

除了要另外注意的之外,這些談論假設您已經通過用--log-bin選項啟動伺服器允許了二進制日誌功能。(如果二進制日誌功能不被允許,複製將不可能,為數據恢復的二進制日誌也不存在。)請參閱5.11.3節,「二進制日誌”

對儲存子程式語句的二進制日誌功能的特徵在下面列資料表中描述。一些條目指出您應該注意到的問題。但是在一些情況下,有您可以更改的婦五七設置或您可以用來處理它們的工作區。

·         CREATE PROCEDURE, CREATE FUNCTION, ALTER PROCEDURE,和ALTER FUNCTION 語句被寫進二進制日誌,CALL, DROP PROCEDURE, 和DROP FUNCTION 也一樣。

儘管如此,對複製有一個安全暗示:要建立一個子程式,用戶必須有CREATE ROUTINE權限,但有這個權限的用戶不能寫一個 子程式在從伺服器上執行任何操作。因為在從伺服器上的SQL線程用完全權限來運行。例如,如果主伺服器和從伺服器分別有伺服器ID值1和2,在主伺服器上的用戶可能建立並使用如下一個 程式:

mysql> delimiter //
mysql> CREATE PROCEDURE mysp ()
    -> BEGIN
    ->   IF @@server_id=2 THEN DROP DATABASE accounting; END IF;
    -> END;
    -> //
mysql> delimiter ;
mysql> CALL mysp();

CREATE PROCEDURE和CALL語句將被寫進二進制日誌,所以從伺服器將執行它們。因為從SQL線程有完全權限,它將移除accounting資料庫。

要使允許二進制日誌功能的伺服器避免這個危險,MySQL 5.1已經要求 儲存程式和函數的建立者除了通常需要的CREATE ROUTINE的權限外,還必須有SUPER 權限。類似地,要使用ALTER PROCEDURE或ALTER FUNCTION,除了ALTER ROUTINE權限外您必須有SUPER權限。沒有SUPER權限,將會發生一個錯誤:

ERROR 1419 (HY000): You do not have the SUPER privilege and
binary logging is enabled (you *might* want to use the less safe
log_bin_trust_routine_creators variable)

您可能不想強制要求子程式建立者必須有SUPER權限。例如,您系統上所有有CREATE ROUTINE權限的用戶可能是有經驗的應用程式開發者。要禁止掉對SUPER權限的要求,設置log_bin_trust_routine_creators 全局系統變數為1。預設地,這個變數值為0,但您可以像這樣改變這樣:

mysql> SET GLOBAL log_bin_trust_routine_creators = 1;

您也可以在啟動伺服器之時用--log-bin-trust-routine-creators選項來設置允許這個變數。

如果二進制日誌功能不被允許,log_bin_trust_routine_creators 沒有被用上,子程式建立需要SUPER權限。

·         一個執行更新的非確定子程式是不可重複的,它能有兩個不如意的影響:

o        它會使得從伺服器不同於主伺服器

-        恢復的數據與原始數據不同。

要解決這些問題,MySQL強制做下面要求:在主伺服器上,除非子程式被聲明為確定性的或者不更改數據,否則建立或者替換子程式將被拒絕。這意味著當您建立一個子程式的時候,您必須要麼聲明它是確定性的,要麼它不改變數據。兩套子程式特徵在這裡適用:

-        DETERMINISTIC和NOT DETERMINISTIC指出一個子程式是否對給定的輸入總是產生同樣的結果。如果沒有給定任一特徵,預設是NOT DETERMINISTIC,所以您必須明確指定DETERMINISTIC來 聲明一個 子程式是確定性的。

使用NOW() 函數(或它的同義)或者RAND() 函數不是必要地使也一個子程式非確定性。對NOW()而言,二進制日誌包括時間戳並正確複製。RAND()只要在一個 子程式內被使用一次也可以正確複製。(您可以認為子程式執行時間戳和隨機數種子作為毫無疑問地輸入,它們在主伺服器和從伺服器上是一樣的。)

-        CONTAINS SQL, NO SQL, READS SQL DATA, 和 MODIFIES SQL數據提供子程式是讀還是寫數據的訊息。無論NO SQL 還是READS SQL DATA i都指出,子程式沒有改變數據,但您必須明白地指明這些中的一個,因為如果任何這些特徵沒有被給出, 預設的特徵是CONTAINS SQL。

預設地,要一個CREATE PROCEDURE 或 CREATE FUNCTION 語句被接受,DETERMINISTIC 或 NO SQL與READS SQL DATA 中的一個必須明白地指定,否則會產生如下錯誤:

ERROR 1418 (HY000): This routine has none of DETERMINISTIC, NO SQL,
or READS SQL DATA in its declaration and binary logging is enabled
(you *might* want to use the less safe log_bin_trust_routine_creators
variable)

如果設置log_bin_trust_routine_creators 為1, 移除對子程式必須是確定的或不修改數據的要求。

注意,子程式本性的評估是基於建立者的“誠實度” :MySQL不檢查聲明為確定性的子程式是否不含產生非確定性結果的語句。

·         如果子程式返回無錯,CALL語句被寫進二進制日誌,否則就不寫。當一個子程式修改數據失敗了,您會得到這樣的警告:

·                ERROR 1417 (HY000): A routine failed and has neither NO SQL nor
·                READS SQL DATA in its declaration and binary logging is enabled; if
·                non-transactional tables were updated, the binary log will miss their
·                changes

這個記日誌行為潛在地導致問題.如果一個子程式部分地修改一個非交互資料表(比如一個MyISAM資料表able)並且返回一個錯誤,二進制日誌將反映這些變化。要防止這種情況,您應該在 子程式中使用交互資料表並且在交互動作內修改資料表。

在一個子程式內,如果您在INSERT, DELETE, 或者UPDATE裡使用IGNORE關鍵詞來忽略錯誤,可能發生一個部分更新,但沒有錯誤產生。這樣的語句被記錄日誌,且正常複製。

·         如果一個儲存函數在一個如SELECT這樣不修改數據的語句內被使用,即使函數本身更改數據,函數的執行也將不被寫進二進制日誌裡。這個記錄日誌的行為潛在地導致問題。假設函數myfunc()如下定義:

·                CREATE FUNCTION myfunc () RETURNS INT
·                BEGIN
·                  INSERT INTO t (i) VALUES(1);
·                  RETURN 0;
·                END;

按照上面定義,下面的語句修改資料表t,因為myfunc()修改資料表t, 但是語句不被寫進二進制日誌,因為它是一個SELECT語句:

SELECT myfunc();

對這個問題的工作區將使用在做更新的語句裡做更新的函數。注意,雖然DO語句有時為了其估算資料表達式的副效應而被執行,DO在這裡不是一個工作區,因為它不被寫進二進制日誌。

·         在一個子程式內執行的語句不被寫進二進制日誌。假如您發佈下列語句:

·                CREATE PROCEDURE mysp INSERT INTO t VALUES(1);
·                CALL mysp;

對於這個例子來說,CREATE PROCEDURE 和CALL語句出現在二進制日誌裡,但INSERT語句並未出現。

·         在從伺服器上,當決定複製哪個來自主伺服器的事件時,下列限制被應用:--replicate-*-table規則不適用於CALL語句或子程式內的語句:在這些情況下,總是返回「複製!”

觸發程式類似於儲存函數,所以前述的評論也適用於觸發程式,除了下列情況: CREATE TRIGGER沒有可選的DETERMINISTIC特徵,所以觸發程式被假定為總是確定性的。然而,這個假設在一些情況下是非法的。比如,UUID()函數是非確定性的(不能複製)。您應該小心在 觸發程式中使用這個函數。

觸發程式目前不能更新資料表,但是在將來會支援。因為這個原因,如果您沒有SUPER權限且log_bin_trust_routine_creators 被設為0,得到的錯誤訊息類似於儲存子程式與CREATE TRIGGER產生的錯誤訊息。

在本節中敘述的問題來自發生在SQL語句級別的二進制日誌記錄的事實。未來發行的MySQL期望能實現行級的二進制日誌記錄,記錄發生在更 細緻的級別並且指出哪個改變作為執行SQL的結果對單個記錄而做。


這是MySQL參考手冊的翻譯版本,關於MySQL參考手冊,請訪問dev.mysql.com。原始參考手冊為英文版,與英文版參考手冊相比,本翻譯版可能不是最新的。