作者：MySQL AB;翻譯:陳朋奕

The New SQL Statements 新SQL語句

Variables 變量

　　在復(fù)合語句中聲明變量的指令是DECLARE。

　　(1) Example with two DECLARE statements

　　兩個(gè)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塊內(nèi)定義了而已（譯注：也就是形參）。

　　注意這些變量和會(huì)話變量不一樣，不能使用修飾符@你必須清楚的在BEGIN/END塊中聲明變量和它們的類型。

　　變量一旦聲明，你就能在任何能使用會(huì)話變量、文字、列名的地方使用。

　　(2) Example with no DEFAULT clause and SET statement

　　沒有默認(rèn)子句和設(shè)定語句的例子

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 */
　　有很多初始化變量的方法。如果沒有默認(rèn)的子句，那么變量的初始值為NULL。你可以在任何時(shí)候使用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; //

　　我們?cè)谶@里做了一些改變，但是結(jié)果還是一樣的。在這里使用了DEFAULT子句來設(shè)定初始值，這就不需要把DECLARE和SET語句的實(shí)現(xiàn)分開了。

　　(4) Example of CALL

　　調(diào)用的例子

mysql> CALL p10() //

+--------+

| s1 * a |

+--------+

| 25 |

| 25 |

+--------+

2 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)
　　結(jié)果顯示了過程能正常工作

　　(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; //

　　現(xiàn)在我們來討論一下作用域的問題。例子中有嵌套的BEGIN/END塊，當(dāng)然這是合法的。同時(shí)包含兩個(gè)變量，名字都是x1，這樣也是合法的。內(nèi)部的變量在其作用域內(nèi)享有更高的優(yōu)先權(quán)。當(dāng)執(zhí)行到END語句時(shí)，內(nèi)部變量消失，此時(shí)已經(jīng)在其作用域外，變量不再可見了，因此在存儲(chǔ)過程外再也不能找到這個(gè)聲明了的變量，但是你可以通過OUT參數(shù)或者將其值指派 給會(huì)話變量來保存其值。

　　調(diào)用作用域例子的過程：

mysql> CALL p11()//

+-------+

| x1 |

+-------+

| inner |

+-------+

+-------+

| x1 |

+-------+

| outer |

+-------+
　　我們看到的結(jié)果時(shí)第一個(gè)SELECT語句檢索到最內(nèi)層的變量，第二個(gè)檢索到第二層的變量

Conditions and IF-THEN-ELSE 條件式和IF-THEN-ELSE

　　1.

　　現(xiàn)在我們可以寫一些包含條件式的例子：

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; //
　　這里是一個(gè)包含IF語句的過程。里面有兩個(gè)IF語句，一個(gè)是IF語句END IF，另一個(gè)是IF語句ELSE語句END IF。我們可以在這里使用復(fù)雜的過程，但我會(huì)盡量使其簡單讓你能更容易弄清楚。

　　2.

CALL p12 (0) //
　　我們調(diào)用這個(gè)過程，傳入值為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的值，所以執(zhí)行后變量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; //

　　因?yàn)樽兞縱ariable1值為1，因此條件"if variable1 = 0"為假，

IF

……

END IF
　　被跳過，沒有被執(zhí)行。

　　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; //
　　到第二個(gè)IF條件，判斷結(jié)果為真，于是中間語句被執(zhí)行了

　　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; //

　　因?yàn)閰?shù)parameter1值等于0，UPDATE語句被執(zhí)行。如果parameter1值為NULL，則下一條UPDATE語句將被執(zhí)行現(xiàn)在表t中有兩行，他們都包含值5，所以如果我們調(diào)用p12，兩行的值會(huì)變成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)
　　結(jié)果也是我們所期望的那樣。

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; //
　　如果需要進(jìn)行更多條件真假的判斷我們可以使用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)
　　執(zhí)行過程后，傳入值1，如上面例子，值19被插入到表t中。

　　Question

　　問題

　　問題: CALL p13(NULL) //的作用是什么？

　　另一個(gè)：這個(gè)CALL語句做了那些動(dòng)作？

　　你可以通過執(zhí)行后觀察SELECT做了什么，也可以根據(jù)代碼判斷，在5秒內(nèi)做出。

　　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)
　　答案是當(dāng)你調(diào)用p13時(shí)，MySQL插入了另一條包含數(shù)值19的記錄。原因是變量variable1的值為NULL，CASE語句的ELSE部分就被執(zhí)行了。希望這對(duì)大家有意義。如果你回答不出來，沒有問題，我們可以繼續(xù)向下走。

Loops 循環(huán)語句

WHILE ... END WHILE

LOOP ... END LOOP

REPEAT ... END REPEAT

GOTO
　　下面我們將會(huì)創(chuàng)建一些循環(huán)。我們有三種標(biāo)準(zhǔn)的循環(huán)方式：

　　WHILE循環(huán)，LOOP循環(huán)以及REPEAT循環(huán)。還有一種非標(biāo)準(zhǔn)的循環(huán)方式：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循環(huán)的方式。我很喜歡這種方式，它跟IF語句相似，因此不需要掌握很多新的語法。這里的INSERT和SET語句在WHILE和END WHILE之間，當(dāng)變量v大于5的時(shí)候循環(huán)將會(huì)退出。使用

"SET v = 0;"
　　語句使為了防止一個(gè)常見的錯(cuò)誤，如果沒有初始化，默認(rèn)變量值為NULL，而NULL和任何值操作結(jié)果都為NULL。

WHILE ... END WHILE example

mysql> CALL p14()//

Query OK, 1 row affected (0.00 sec)

　　以上就是調(diào)用過程p14的結(jié)果不用關(guān)注系統(tǒng)返回是"one row affected"還是"five rows affected"，因?yàn)檫@里的計(jì)數(shù)只對(duì)最后一個(gè)INSERT動(dòng)作進(jìn)行計(jì)數(shù)。

WHILE ... END WHILE example: CALL

mysql> select * from t; //

+------+

| s1 |

+------+

....

| 0 |

| 1 |

| 2 |

| 3 |

| 4 |

+------+

9 rows in set (0.00 sec)
　　調(diào)用后可以看到程序向數(shù)據(jù)庫中插入了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; //

　　這是一個(gè)REPEAT循環(huán)的例子，功能和前面WHILE循環(huán)一樣。區(qū)別在于它在執(zhí)行后檢查結(jié)果，而WHILE則是執(zhí)行前檢查。（譯者語：可能等同于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語句后面沒有分號(hào)，在這里可以不寫分號(hào)，當(dāng)然你加上額外的分號(hào)更好。

　　REPEAT ... END REPEAT: calling ：調(diào)用

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)
　　我們可以看到調(diào)用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循環(huán)的例子。

　　LOOP循環(huán)不需要初始條件，這點(diǎn)和WHILE循環(huán)相似，同時(shí)它又和REPEAT循環(huán)一樣也不需要結(jié)束條件。

　　LOOP ... END LOOP: with IF and LEAVE 包含IF和LEAVE的LOOP循環(huán)

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; //
　　在循環(huán)內(nèi)部加入IF語句，在IF語句中包含LEAVE語句。這里L(fēng)EAVE語句的意義是離開循環(huán)。

　　LEAVE的語法是LEAVE加循環(huán)語句標(biāo)號(hào)，關(guān)于循環(huán)語句的標(biāo)號(hào)問題我會(huì)在后面進(jìn)一步講解。
　　LOOP ... END LOOP: calling ：調(diào)用

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)
　　調(diào)用過程p16后，結(jié)果是另5行被插入表t中。

Labels 標(biāo)號(hào)

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; //
　　最后一個(gè)循環(huán)例子中我使用了語句標(biāo)號(hào)。現(xiàn)在這里有一個(gè)包含4個(gè)語句標(biāo)號(hào)的過程的例子。我們可以在BEGIN、WHILE、REPEAT或者LOOP語句前使用語句標(biāo)號(hào)，語句標(biāo)號(hào)只能在合法的語句前面使用。因此"LEAVE label_3"意味著離開語句標(biāo)號(hào)名定義為label_3的語句或復(fù)合語句。

End Labels 標(biāo)號(hào)結(jié)束符

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 ; //
　　你也可以在語句結(jié)束時(shí)使用語句標(biāo)號(hào)，和在開頭時(shí)使用一樣。這些標(biāo)號(hào)結(jié)束符并不是十分有用。

　　它們是可選的。如果你需要，他們必須和開始定義的標(biāo)號(hào)名字一樣當(dāng)然為了有良好的編程習(xí)慣，方便他人閱讀，最好還是使用標(biāo)號(hào)結(jié)束符。

LEAVE and Labels 跳出和標(biāo)號(hào)

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
　　語句使程序跳出復(fù)雜的復(fù)合語句。

ITERATE

　　迭代如果目標(biāo)是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語句一樣也是在循環(huán)內(nèi)部的循環(huán)引用，它有點(diǎn)像C語言中的“Continue”，同樣它可以出現(xiàn)在復(fù)合語句中，引用復(fù)合語句標(biāo)號(hào)，ITERATE（迭代）意思是重新開始復(fù)合語句。

　　那我們啟動(dòng)并觀察下面這個(gè)循環(huán)，這是個(gè)需要迭代過程的循環(huán)：

ITERATE: Walking through the loop

深入循環(huán)

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; //
　　讓這個(gè)已經(jīng)定義了標(biāo)號(hào)的循環(huán)運(yùn)行起來。

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（迭代）讓循環(huán)又回到了循環(huán)的頭部。

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; //
　　當(dāng)v的值變?yōu)?時(shí)，程序?qū)?zhí)行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的結(jié)果就是跳出循環(huán)，使運(yùn)行指令到達(dá)復(fù)合語句的最后一步。

GOTO

CREATE PROCEDURE p...

BEGIN

...

LABEL label_name;

...

GOTO label_name;

...

END;
　　MySQL的存儲(chǔ)過程中可以使用GOTO語句。雖然這不是標(biāo)準(zhǔn)SQL語句，而且在這里建立標(biāo)號(hào)的方法也和慣例中的不一樣。由于為了和其他DBMS兼容，這個(gè)語句會(huì)慢被淘汰，所以我們?cè)贛ySQL參考手冊(cè)中沒有提及。

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;//
　　上面例子中的語句包含了我們之前講的所有語法，包括參數(shù)列表，特性參數(shù)，BEGIN/END塊復(fù)合語句，變量聲明，IF，WHILE，LOOP，REPEAT，LEAVE，ITERATE，GOTO。這是一個(gè)荒謬的存儲(chǔ)過程，我不會(huì)運(yùn)行它，因?yàn)槔锩嬗袩o盡的循環(huán)。但是里面的語法卻十分合法。這些是新的流程控制和變量聲明語句。下面我們將要接觸更多新的東西。

未完待續(xù)...