第三講:新sql語句,loops 循環(huán)語句 
 

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

注意這些變量和會話變量不一樣，不能使用修飾符@你必須清楚的在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語句的實現(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)

結果顯示了過程能正常工作

(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塊，當然這是合法的。同時包含兩個變量，名字都是x1，這樣也是合法的。內(nèi)部的變量在其作用域內(nèi)享有更高的優(yōu)先權。當執(zhí)行到end語句時，內(nèi)部變量消失，此時已經(jīng)在其作用域外，變量不再可見了，因此在存儲過程外再也不能找到這個聲明了的變量，但是你可以通過out參數(shù)或者將其值指派 給會話變量來保存其值。

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

mysql> call p11()//

+-------+

| x1 |

+-------+

| inner |

+-------+

+-------+

| x1 |

+-------+

| outer |

+-------+

我們看到的結果時第一個select語句檢索到最內(nèi)層的變量，第二個檢索到第二層的變量

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

這里是一個包含if語句的過程。里面有兩個if語句，一個是if語句end if，另一個是if語句else語句end if。我們可以在這里使用復雜的過程，但我會盡量使其簡單讓你能更容易弄清楚。

2.

call p12 (0) //

我們調(diào)用這個過程，傳入值為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; //

因為變量variable1值為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; //

到第二個if條件，判斷結果為真，于是中間語句被執(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; //

因為參數(shù)parameter1值等于0，update語句被執(zhí)行。如果parameter1值為null，則下一條

update

語句將被執(zhí)行現(xiàn)在表t中有兩行，他們都包含值5，所以如果我們調(diào)用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)

執(zhí)行過程后，傳入值1，如上面例子，值19被插入到表t中。

question

問題

問題: call p13(null) //的作用是什么？

另一個：這個call語句做了那些動作？

你可以通過執(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)

答案是當你調(diào)用p13時，mysql插入了另一條包含數(shù)值19的記錄。原因是變量variable1的值為null，case語句的else部分就被執(zhí)行了。希望這對大家有意義。如果你回答不出來，沒有問題，我們可以繼續(xù)向下走。

loops 循環(huán)語句

while ... end while

loop ... end loop

repeat ... end repeat

goto

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

while循環(huán)，loop循環(huán)以及repeat循環(huá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之間，當變量v大于5的時候循環(huán)將會退出。使用

"set v = 0;"

語句使為了防止一個常見的錯誤，如果沒有初始化，默認變量值為null，而null和任何值操作結果都為null。

while ... end while example

mysql> call p14()//

query ok, 1 row affected (0.00 sec)

以上就是調(diào)用過程p14的結果不用關注系統(tǒng)返回是"one row affected"還是"five rows affected"，因為這里的計數(shù)只對最后一個insert動作進行計數(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; //

這是一個repeat循環(huán)的例子，功能和前面while循環(huán)一樣。區(qū)別在于它在執(zhí)行后檢查結果，而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語句后面沒有分號，在這里可以不寫分號，當然你加上額外的分號更好。

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)不需要初始條件，這點和while循環(huán)相似，同時它又和repeat循環(huán)一樣也不需要結束條件。

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語句。這里leave語句的意義是離開循環(huán)。

leave的語法是leave加循環(huán)語句標號，關于循環(huá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后，結果是另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; //

最后一個循環(huán)例子中我使用了語句標號。現(xiàn)在這里有一個包含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語句一樣也是在循環(huán)內(nèi)部的循環(huán)引用，它有點像c語言中的“continue”，同樣它可以出現(xiàn)在復合語句中，引用復合語句標號，iterate（迭代）意思是重新開始復合語句。

那我們啟動并觀察下面這個循環(huán)，這是個需要迭代過程的循環(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; //

讓這個已經(jīng)定義了標號的循環(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; //

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

當v的值變?yōu)?時，程序?qū)?zhí)行l(wèi)eave語句

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的結果就是跳出循環(huán)，使運行指令到達復合語句的最后一步。

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

上面例子中的語句包含了我們之前講的所有語法，包括參數(shù)列表，特性參數(shù)，begin/end塊復合語句，變量聲明，

if，while，loop，repeat，leave，iterate，goto。這是一個荒謬的存儲過程，我不會運行它，因為里面有無盡的循環(huán)。但是里面的語法卻十分合法。這些是新的流程控制和變量聲明語句。下面我們將要接觸更多新的東西。
 

最大的網(wǎng)站源碼資源下載站，