The New SQL Statements 新SQL語句
Variables 變量
在復合語句中聲明變量的指令是DECLARE。
(1) Example with two DECLARE statements
兩個DECLARE語句的例子
WHILE ... END WHILE CREATE PROCEDURE p8 () BEGIN DECLARE a INT; DECLARE b INT; SET a = 5; SET b = 5; INSERT INTO t VALUES (a); SELECT s1 * a FROM t WHERE s1 >= b; END; // /* I won't CALL this */
在過程中定義的變量並不是真正的定義，你只是在BEGIN/END塊內定義了而已（譯注：也就是形參）。
注意這些變量和會話變量不一樣，不能使用修飾符@你必須清楚的在BEGIN/END塊中聲明變量和它們的類型。
變量一旦聲明，你就能在任何能使用會話變量、文字、列名的地方使用。
(2) Example with no DEFAULT clause and SET statement
沒有默認子句和設定語句的例子
CREATE PROCEDURE p9 () BEGIN DECLARE a INT /* there is no DEFAULT clause */; DECLARE b INT /* there is no DEFAULT clause */; SET a = 5; /* there is a SET statement */ SET b = 5; /* there is a SET statement */ INSERT INTO t VALUES (a); SELECT s1 * a FROM t WHERE s1 >= b; END; // /* I won't CALL this */
有很多初始化變量的方法。如果沒有默認的子句，那麼變量的初始值為NULL。你可以在任何時候使用SET語句給變量賦值。
(3) Example with DEFAULT clause
含有DEFAULT子句的例子
CREATE PROCEDURE p10 () BEGIN DECLARE a, b INT DEFAULT 5; INSERT INTO t VALUES (a); SELECT s1 * a FROM t WHERE s1 >= b; END; //
我們在這裡做了一些改變，但是結果還是一樣的。在這裡使用了DEFAULT子句來設定初始值，這就不需要把DECLARE和SET語句的實現分開了。
(4) Example of CALL
調用的例子
MySQL> CALL p10() // +--------+ | s1 * a | +--------+ | 25 | | 25 | +--------+ 2 rows in set (0.00 sec) Query OK, 0 rows affected (0.00 sec)
結果顯示了過程能正常工作
(5) Scope
作用域
CREATE PROCEDURE p11 () BEGIN DECLARE x1 CHAR(5) DEFAULT 'outer'; BEGIN DECLARE x1 CHAR(5) DEFAULT 'inner'; SELECT x1; END; SELECT x1; END; //
現在我們來討論一下作用域的問題。例子中有嵌套的BEGIN/END塊，當然這是合法的。同時包含兩個變量，名字都是x1，這樣也是合法的。內部的變量在其作用域內享有更高的優先權。當執行到END語句時，內部變量消失，此時已經在其作用域外，變量不再可見了，因此在存儲過程外再也不能找到這個聲明了的變量，但是你可以通過OUT參數或者將其值指派 給會話變量來保存其值。
調用作用域例子的過程：
MySQL> CALL p11()// +-------+ | x1 | +-------+ | inner | +-------+ +-------+ | x1 | +-------+ | outer | +-------+
我們看到的結果時第一個SELECT語句檢索到最內層的變量，第二個檢索到第二層的變量
Conditions and IF-THEN-ELSE 條件式和IF-THEN-ELSE
1.
現在我們可以寫一些包含條件式的例子：
CREATE PROCEDURE p12 (IN parameter1 INT) BEGIN DECLARE variable1 INT; SET variable1 = parameter1 + 1; IF variable1 = 0 THEN INSERT INTO t VALUES (17); END IF; IF parameter1 = 0 THEN UPDATE t SET s1 = s1 + 1; ELSE UPDATE t SET s1 = s1 + 2; END IF; END; //
這裡是一個包含IF語句的過程。裡面有兩個IF語句，一個是IF語句END IF，另一個是IF語句ELSE語句END IF。我們可以在這裡使用復雜的過程，但我會盡量使其簡單讓你能更容易弄清楚。
2.
CALL p12 (0) //
我們調用這個過程，傳入值為0，這樣parameter1的值將為0。
3.
CREATE PROCEDURE p12 (IN parameter1 INT) BEGIN DECLARE variable1 INT; SET variable1 = parameter1 + 1; <-- IF variable1 = 0 THEN INSERT INTO t VALUES (17); END IF; IF parameter1 = 0 THEN UPDATE t SET s1 = s1 + 1; ELSE UPDATE t SET s1 = s1 + 2; END IF; END; //
這裡變量variable1被賦值為parameter1加1的值，所以執行後變量variable1為1。
4.
CREATE PROCEDURE p12 (IN parameter1 INT) BEGIN DECLARE variable1 INT; SET variable1 = parameter1 + 1; IF variable1 = 0 THEN <-- INSERT INTO t VALUES (17); END IF; IF parameter1 = 0 THEN UPDATE t SET s1 = s1 + 1; ELSE UPDATE t SET s1 = s1 + 2; END IF; END; //
因為變量variable1值為1，因此條件"if variable1 = 0"為假，
IF
……
END IF
被跳過，沒有被執行。
5.
CREATE PROCEDURE p12 (IN parameter1 INT) BEGIN DECLARE variable1 INT; SET variable1 = parameter1 + 1; IF variable1 = 0 THEN INSERT INTO t VALUES (17); END IF; IF parameter1 = 0 THEN <-- UPDATE t SET s1 = s1 + 1; ELSE UPDATE t SET s1 = s1 + 2; END IF; END; //
到第二個IF條件，判斷結果為真，於是中間語句被執行了
6.
CREATE PROCEDURE p12 (IN parameter1 INT) BEGIN DECLARE variable1 INT; SET variable1 = parameter1 + 1; IF variable1 = 0 THEN INSERT INTO t VALUES (17); END IF; IF parameter1 = 0 THEN UPDATE t SET s1 = s1 + 1; <-- ELSE UPDATE t SET s1 = s1 + 2; END IF; END; //
因為參數parameter1值等於0，UPDATE語句被執行。如果parameter1值為NULL，則下一條UPDATE語句將被執行現在表t中有兩行，他們都包含值5，所以如果我們調用p12，兩行的值會變成6。
7.
mysql> CALL p12(0)// Query OK, 2 rows affected (0.28 sec) MySQL> SELECT * FROM t// +------+ | s1 | +------+ | 6 | | 6 | +------+ 2 rows in set (0.01 sec)
結果也是我們所期望的那樣。
CASE 指令
1.
CREATE PROCEDURE p13 (IN parameter1 INT) BEGIN DECLARE variable1 INT; SET variable1 = parameter1 + 1; CASE variable1 WHEN 0 THEN INSERT INTO t VALUES (17); WHEN 1 THEN INSERT INTO t VALUES (18); ELSE INSERT INTO t VALUES (19); END CASE; END; //
如果需要進行更多條件真假的判斷我們可以使用CASE語句。CASE語句使用和IF一樣簡單。
我們可以參考上面的例子： 2.
mysql> CALL p13(1)// Query OK, 1 row affected (0.00 sec) MySQL> SELECT * FROM t// +------+ | s1 | +------+ | 6 | | 6 | | 19 | +------+ 3 rows in set (0.00 sec)
執行過程後，傳入值1，如上面例子，值19被插入到表t中。
Question
問題
問題: CALL p13(NULL) //的作用是什麼？
另一個：這個CALL語句做了那些動作？
你可以通過執行後觀察SELECT做了什麼，也可以根據代碼判斷，在5秒內做出。
Answer
答案
mysql> CALL p13(NULL)// Query OK, 1 row affected (0.00 sec) MySQL> SELECT * FROM t// +------+ | s1 | +------+ | 6 | | 6 | | 19 | | 19 | +------+ 4 rows in set (0.00 sec)
答案是當你調用p13時，MySQL插入了另一條包含數值19的記錄。原因是變量variable1的值為NULL，CASE語句的ELSE部分就被執行了。希望這對大家有意義。如果你回答不出來，沒有問題，我們可以繼續向下走。
Loops 循環語句 WHILE ... END WHILE LOOP ... END LOOP REPEAT ... END REPEAT GOTO
下面我們將會創建一些循環。我們有三種標准的循環方式：
WHILE循環，LOOP循環以及REPEAT循環。還有一種非標准的循環方式：GO TO（譯者語：最好不要用吧，用了就使流程混亂）。
CREATE PROCEDURE p14 () BEGIN DECLARE v INT; SET v = 0; WHILE v < 5 DO INSERT INTO t VALUES (v); SET v = v + 1; END WHILE; END; //
這是WHILE循環的方式。我很喜歡這種方式，它跟IF語句相似，因此不需要掌握很多新的語法。這裡的INSERT和SET語句在WHILE和END WHILE之間，當變量v大於5的時候循環將會退出。使用
"SET v = 0;"
語句使為了防止一個常見的錯誤，如果沒有初始化，默認變量值為NULL，而NULL和任何值操作結果都為NULL。
WHILE ... END WHILE example MySQL> CALL p14()// Query OK, 1 row affected (0.00 sec)
以上就是調用過程p14的結果不用關注系統返回是"one row affected"還是"five rows affected"，因為這裡的計數只對最後一個INSERT動作進行計數。
WHILE ... END WHILE example: CALL MySQL> select * from t; // +------+ | s1 | +------+ .... | 0 | | 1 | | 2 | | 3 | | 4 | +------+ 9 rows in set (0.00 sec)
調用後可以看到程序向數據庫中插入了5行。
REPEAT ... END REPEAT CREATE PROCEDURE p15 () BEGIN DECLARE v INT; SET v = 0; REPEAT INSERT INTO t VALUES (v); SET v = v + 1; UNTIL v >= 5 END REPEAT; END; //
這是一個REPEAT循環的例子，功能和前面WHILE循環一樣。區別在於它在執行後檢查結果，而WHILE則是執行前檢查。（譯者語：可能等同於DO WHILE吧）
REPEAT ... END REPEAT: look at the UNTIL： UNTIL的作用 CREATE PROCEDURE p15 () BEGIN DECLARE v INT; SET v = 0; REPEAT INSERT INTO t VALUES (v); SET v = v + 1; UNTIL v >= 5 <-- END REPEAT; END; //
注意到UNTIL語句後面沒有分號，在這裡可以不寫分號，當然你加上額外的分號更好。
REPEAT ... END REPEAT: calling ：調用
mysql> CALL p15()// Query OK, 1 row affected (0.00 sec) MySQL> SELECT COUNT(*) FROM t// +----------+ | COUNT(*) | +----------+ | 14 | +----------+ 1 row in set (0.00 sec)
我們可以看到調用p15過程後又插入了5行記錄
LOOP ... END LOOP CREATE PROCEDURE p16 () BEGIN DECLARE v INT; SET v = 0; loop_label: LOOP INSERT INTO t VALUES (v); SET v = v + 1; IF v >= 5 THEN LEAVE loop_label; END IF; END LOOP; END; //
以上是LOOP循環的例子。
LOOP循環不需要初始條件，這點和WHILE循環相似，同時它又和REPEAT循環一樣也不需要結束條件。
LOOP ... END LOOP: with IF and LEAVE 包含IF和LEAVE的LOOP循環
CREATE PROCEDURE p16 () BEGIN DECLARE v INT; SET v = 0; loop_label: LOOP INSERT INTO t VALUES (v); SET v = v + 1; IF v >= 5 THEN <-- LEAVE loop_label; END IF; END LOOP; END; //
在循環內部加入IF語句，在IF語句中包含LEAVE語句。這裡LEAVE語句的意義是離開循環。
LEAVE的語法是LEAVE加循環語句標號，關於循環語句的標號問題我會在後面進一步講解。
LOOP ... END LOOP: calling ：調用
mysql> CALL p16()// Query OK, 1 row affected (0.00 sec) MySQL> SELECT COUNT(*) FROM t// +----------+ | COUNT(*) | +----------+ | 19 | +----------+ 1 row in set (0.00 sec)
調用過程p16後，結果是另5行被插入表t中。
Labels 標號
CREATE PROCEDURE p17 () label_1: BEGIN label_2: WHILE 0 = 1 DO LEAVE label_2; END WHILE; label_3: REPEAT LEAVE label_3; UNTIL 0 =0 END REPEAT; label_4: LOOP LEAVE label_4; END LOOP; END; //
最後一個循環例子中我使用了語句標號。現在這裡有一個包含4個語句標號的過程的例子。我們可以在BEGIN、WHILE、REPEAT或者LOOP語句前使用語句標號，語句標號只能在合法的語句前面使用。因此"LEAVE label_3"意味著離開語句標號名定義為label_3的語句或復合語句。
End Labels 標號結束符
CREATE PROCEDURE p18 () label_1: BEGIN label_2: WHILE 0 = 1 DO LEAVE label_2; END WHILE label_2; label_3: REPEAT LEAVE label_3; UNTIL 0 =0 END REPEAT label_3 ; label_4: LOOP LEAVE label_4; END LOOP label_4 ; END label_1 ; //
你也可以在語句結束時使用語句標號，和在開頭時使用一樣。這些標號結束符並不是十分有用。
它們是可選的。如果你需要，他們必須和開始定義的標號名字一樣當然為了有良好的編程習慣，方便他人閱讀，最好還是使用標號結束符。
LEAVE and Labels 跳出和標號
CREATE PROCEDURE p19 (parameter1 CHAR) label_1: BEGIN label_2: BEGIN label_3: BEGIN IF parameter1 IS NOT NULL THEN IF parameter1 = 'a' THEN LEAVE label_1; ELSE BEGIN IF parameter1 = 'b' THEN LEAVE label_2; ELSE LEAVE label_3; END IF; END; END IF; END IF; END; END; END;// LEAVE
語句使程序跳出復雜的復合語句。
ITERATE
迭代如果目標是ITERATE（迭代）語句的話，就必須用到LEAVE語句
CREATE PROCEDURE p20 () BEGIN DECLARE v INT; SET v = 0; loop_label: LOOP IF v = 3 THEN SET v = v + 1; ITERATE loop_label; END IF; INSERT INTO t VALUES (v); SET v = v + 1; IF v >= 5 THEN LEAVE loop_label; END IF; END LOOP; END; // ITERATE
（迭代）語句和LEAVE語句一樣也是在循環內部的循環引用，它有點像C語言中的“Continue”，同樣它可以出現在復合語句中，引用復合語句標號，ITERATE（迭代）意思是重新開始復合語句。
那我們啟動並觀察下面這個循環，這是個需要迭代過程的循環：
ITERATE: Walking through the loop
深入循環
CREATE PROCEDURE p20 () BEGIN DECLARE v INT; SET v = 0; loop_label: LOOP <-- IF v = 3 THEN SET v = v + 1; ITERATE loop_label; END IF; INSERT INTO t VALUES (v); SET v = v + 1; IF v >= 5 THEN LEAVE loop_label; END IF; END LOOP; END; //
讓這個已經定義了標號的循環運行起來。
ITERATE: Walking through the loop CREATE PROCEDURE p20 () BEGIN DECLARE v INT; SET v = 0; loop_label: LOOP IF v = 3 THEN <-- SET v = v + 1; ITERATE loop_label; END IF; INSERT INTO t VALUES (v); SET v = v + 1; IF v >= 5 THEN LEAVE loop_label; END IF; END LOOP; END; //
v的值變成3，然後我們把它增加到4。
ITERATE: walking through the loop CREATE PROCEDURE p20 () BEGIN DECLARE v INT; SET v = 0; loop_label: LOOP IF v = 3 THEN SET v = v + 1; ITERATE loop_label; <-- END IF; INSERT INTO t VALUES (v); SET v = v + 1; IF v >= 5 THEN LEAVE loop_label; END IF; END LOOP; END; //
然後開始ITERATE（迭代）過程。
ITERATE: walking through the loop CREATE PROCEDURE p20 () BEGIN DECLARE v INT; SET v = 0; loop_label: LOOP <-- IF v = 3 THEN SET v = v + 1; ITERATE loop_label; END IF; INSERT INTO t VALUES (v); SET v = v + 1; IF v >= 5 THEN LEAVE loop_label; END IF; END LOOP; END; //
這裡的ITERATE（迭代）讓循環又回到了循環的頭部。
ITERATE: walking through the loop CREATE PROCEDURE p20 () BEGIN DECLARE v INT; SET v = 0; loop_label: LOOP IF v = 3 THEN SET v = v + 1; ITERATE loop_label; END IF; INSERT INTO t VALUES (v); SET v = v + 1; IF v >= 5 THEN LEAVE loop_label; <-- END IF; END LOOP; END; //
當v的值變為5時，程序將執行LEAVE語句
ITERATE: walking through the loop CREATE PROCEDURE p20 () BEGIN DECLARE v INT; SET v = 0; loop_label: LOOP IF v = 3 THEN SET v = v + 1; ITERATE loop_label; END IF; INSERT INTO t VALUES (v); SET v = v + 1; IF v >= 5 THEN LEAVE loop_label; END IF; END LOOP; END; // <--
LEAVE的結果就是跳出循環，使運行指令到達復合語句的最後一步。
GOTO CREATE PROCEDURE p... BEGIN ... LABEL label_name; ... GOTO label_name; ... END;
MySQL的存儲過程中可以使用GOTO語句。雖然這不是標准SQL語句，而且在這裡建立標號的方法也和慣例中的不一樣。由於為了和其他DBMS兼容，這個語句會慢被淘汰，所以我們在MySQL參考手冊中沒有提及。
Grand combination
大組合
CREATE PROCEDURE p21 (IN parameter_1 INT, OUT parameter_2 INT) LANGUAGE SQL DETERMINISTIC SQL SECURITY INVOKER BEGIN DECLARE v INT; label goto_label; start_label: LOOP IF v = v THEN LEAVE start_label; ELSE ITERATE start_label; END IF; END LOOP start_label; REPEAT WHILE 1 = 0 DO BEGIN END; END WHILE; UNTIL v = v END REPEAT; GOTO goto_label; END;//
上面例子中的語句包含了我們之前講的所有語法，包括參數列表，特性參數，BEGIN/END塊復合語句，變量聲明，IF，WHILE，LOOP，REPEAT，LEAVE，ITERATE，GOTO。這是一個荒謬的存儲過程，我不會運行它，因為裡面有無盡的循環。但是裡面的語法卻十分合法。這些是新的流程控制和變量聲明語句。下面我們將要接觸更多新的東西。
