20. 存儲程序和函數 · mysql5.1參考手冊

### 第20章：存儲程序和函數 MySQL 5.1版支持存儲程序和函數。一個存儲程序是可以被存儲在服務器中的一套SQL語句。一旦它被存儲了，客戶端不需要再重新發布單獨的語句，而是可以引用存儲程序來替代。下面一些情況下存儲程序尤其有用： ·????????當用不同語言編寫多客戶應用程序，或多客戶應用程序在不同平臺上運行且需要執行相同的數據庫操作之時。 ·????????安全極為重要之時。比如，銀行對所有普通操作使用存儲程序。這提供一個堅固而安全的環境，程序可以確保每一個操作都被妥善記入日志。在這樣一個設置中，應用程序和用戶不可能直接訪問數據庫表，但是僅可以執行指定的存儲程序。存儲程序可以提供改良后的性能，因為只有較少的信息需要在服務器和客戶算之間傳送。代價是增加數據庫服務器系統的負荷，因為更多的工作在服務器這邊完成，更少的在客戶端（應用程序）那邊完成上。如果許多客戶端機器（比如網頁服務器）只由一個或少數幾個數據庫服務器提供服務，可以考慮一下存儲程序。存儲程序也允許你在數據庫服務器上有函數庫。這是一個被現代應用程序語言共享的特征，它允許這樣的內部設計，比如通過使用類。使用這些客戶端應用程序語言特征對甚至于數據庫使用范圍以外的編程人員都有好處。 MySQL為存儲程序遵循SQL:2003語法，這個語法也被用在IBM的DB2數據庫上。 MySQL對存儲程序的實現還在進度中。所有本章敘述的語法都被支持，在有限制或擴展的地方會恰當地指出來。有關使用存儲程序的限制的更多討論在[附錄?I, _特性限制_](# "Appendix?I.?Feature Restrictions")里提到。如[20.4節，“存儲子程序和觸發程序的二進制日志功能”](# "20.4.?Binary Logging of Stored Routines and Triggers")里所說的，存儲子程序的二進制日志功能已經完成。 ### 20.1.?存儲程序和授權表存儲程序需要在mysql數據庫中有proc表。這個表在MySQL 5.1安裝過程中創建。如果你從早期的版本升級到MySQL 5.1 ，請確定更新你的授權表以確保proc表的存在。請參閱[2.10.2節 “升級授權表”](# "2.10.2.?Upgrading the Grant Tables")。在MySQL 5.1中，授權系統如下考慮存儲子程序： ·????????創建存儲子程序需要CREATE ROUTINE權限。 ·????????提醒或移除存儲子程序需要ALTER ROUTINE權限。這個權限自動授予子程序的創建者。 ·????????執行子程序需要EXECUTE權限。然而，這個權限自動授予子程序的創建者。同樣，子程序默認的SQLSECURITY 特征是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和CREATEFUNCTION語句創建的子程序。一個子程序要么是一個程序要么是一個函數。使用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.1.?CREATE PROCEDURE和CREATE 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節，“存儲子程序和觸發程序的二進制日志功能”](# "20.4.?Binary Logging of Stored Routines and Triggers")。默認地，子程序與當前數據庫關聯。要明確地把子程序與一個給定數據庫關聯起來，可以在創建子程序的時候指定其名字為_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.7.?BEGIN ... END Compound Statement")。復合語句可以包含聲明，循環和其它控制結構語句。這些語句的語法在本章后免介紹，舉例，請參閱[20.2.8節，“DECLARE語句”](# "20.2.8.?DECLARE Statement")和[20.2.12節，“流程控制構造”](# "20.2.12.?Flow Control Constructs")。 CREATE FUNCTION語句被用在更早的MySQL版本上以支持UDF （自定義函數）。請參閱[27.2節，“給MySQL添加新函數”](# "27.2.?Adding New Functions to MySQL")。 UDF繼續被支持，即使現在有了存儲函數。UDF會被認為一個外部存儲函數。然而，不要讓存儲函數與UDF函數共享名字空間。外部存儲程序的框架將在不久的將來引入。這將允許你用SQL之外的語言編寫存儲程序。最可能的是，第一個被支持語言是PHP，因為核心PHP引擎很小，線程安全，且可以被方便地嵌入。因為框架是公開的，它希望許多其它語言也能被支持。如果程序或線程總是對同樣的輸入參數產生同樣的結果，則被認為它是“確定的”，否則就是“非確定”的。如果既沒有給定DETERMINISTIC也沒有給定NOTDETERMINISTIC，默認的就是NOT DETERMINISTIC。為進行復制，使用NOW()函數（或它的同義詞）或RAND()函數會不必要地使得一個子程序非確定。對NOW()而言，二進制日志包括時間戳并被正確復制。RAND() 只要在一個子程序被內應用一次也會被正確復制。（你可以把子程序執行時間戳和隨機數種子認為強制輸入，它們在主從上是同樣的。）當前來講，DETERMINISTIC特征被接受，但還沒有被優化程序所使用。然而如果二進制日志功能被允許了，這個特征影響到MySQL是否會接受子程序定義。請參閱[20.4](# "20.4.?Binary Logging of Stored Routines and Triggers")[節，“存儲子程序和觸發程序的二進制日志功能”](# "20.4.?Binary Logging of Stored Routines and Triggers")。一些特征提供子程序使用數據的內在信息。CONTAINS SQL表示子程序不包含讀或寫數據的語句。NO SQL表示子程序不包含SQL語句。READSSQL 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 PROCEDURE和ALTER 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節，“](# "20.4.?Binary Logging of Stored Routines and Triggers")[存儲子程序和觸發程序的二進制日志功能”](# "20.4.?Binary Logging of Stored Routines and Triggers")中所述，如果二進制日志功能被允許了，你可能也需要SUPER權限。在ALTER PROCEDURE和ALTER FUNCTION語句中，可以指定超過一個的改變。 ### 20.2.3.?DROP PROCEDURE和DROP FUNCTION DROP {PROCEDURE | FUNCTION} [IF EXISTS] sp_name 這個語句被用來移除一個存儲程序或函數。即，從服務器移除一個制定的子程序。在MySQL 5.1中，你必須有ALTER ROUTINE權限才可用此子程序。這個權限被自動授予子程序的創建者。 IF EXISTS 子句是一個MySQL的擴展。如果程序或函數不存儲，它防止發生錯誤。產生一個可以用SHOW WARNINGS查看的警告。 ### 20.2.4.?SHOW CREATE PROCEDURE和SHOW 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 STATUS和SHOW 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 表”](# "23.1.14.?The INFORMATION_SCHEMA ROUTINES Table")。 ### 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.9.?Variables in Stored Procedures")[存儲程序中的變量”](# "20.2.9.?Variables in Stored Procedures"))，條件和處理程序（請參閱[20.2.10節，“](# "20.2.10.?Conditions and Handlers")[條件和處理程序”](# "20.2.10.?Conditions and Handlers")) 及光標（請參閱[20.2.11節，“](# "20.2.11.?Cursors")[光標”](# "20.2.11.?Cursors"))。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節，“](# "9.3.?User Variables")[用戶變量”](# "9.3.?User Variables")。 **重要**: 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節，“存儲子程序和觸發程序的限制”](# "I.1.?Restrictions on Stored Routines and Triggers")。 ### 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處理程序”](# "20.2.10.2.?DECLARE Handlers")。除了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節，“](# "12.2.?Control Flow Functions")[控制流程函數”](# "12.2.?Control Flow Functions")。 #### 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節，“](# "12.2.?Control Flow Functions")[控制流程函數”](# "12.2.?Control Flow Functions")里描述的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節，“](# "20.4.?Binary Logging of Stored Routines and Triggers")[存儲子程序和觸發程序二進制日志功能”](# "20.4.?Binary Logging of Stored Routines and Triggers")中詳述。 - 在主服務器上創建的存儲程序和函數可以被復制到從服務器上么？是的，通過一般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節，“](# "6.2.?Replication Implementation Overview")[復制概述”](# "6.2.?Replication Implementation Overview")。 ·????????特定的數據恢復操作許要使用二進制日志。備份的文件被恢復之后，備份后紀錄的二進制日志里的事件被重新執行。這些事件把數據庫帶從備份點的日子帶到當前。請參閱[5.9.2.2節，“](# "5.9.2.2.?Using Backups for Recovery")[使用備份恢復”](# "5.9.2.2.?Using Backups for Recovery")。 MySQL中，以存儲子程序的二進制日志功能引發了很多問題，這些在下面討論中列出，作為參考信息。除了要另外注意的之外，這些談論假設你已經通過用--log-bin選項啟動服務器允許了二進制日志功能。（如果二進制日志功能不被允許，復制將不可能，為數據恢復的二進制日志也不存在。）請參閱[5.11.3節，“](# "5.11.3.?The Binary Log")[二進制日志”](# "5.11.3.?The Binary Log")。對存儲子程序語句的二進制日志功能的特征在下面列表中描述。一些條目指出你應該注意到的問題。但是在一些情況下，有你可以更改的婦五七設置或你可以用來處理它們的工作區。 ·????????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; 對于這個例子來說，CREATEPROCEDURE 和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](http://dev.mysql.com/doc/mysql/en)。原始參考手冊為英文版，與英文版參考手冊相比，本翻譯版可能不是最新的。