The New SQL Statements 新SQL語句
Variables 變量
在復合語句中聲明變量的指令是DECLARE。
(1) Example with two DECLARE statements
兩個DECLARE語句的例子
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（譯者語：最好不要用吧，用了就使流程混亂）。
WHILE ... END WHILE
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。這是一個荒謬的存儲過程，我不會運行它，因為裡面有無盡的循環。但是裡面的語法卻十分合法。這些是新的流程控制和變量聲明語句。下面我們將要接觸更多新的東西。
未完待續...
