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。這是一個荒謬的存儲過程，我不會運行它，因為里面有無盡的循環。但是里面的語法卻十分合法。這些是新的流程控制和變量聲明語句。下面我們將要接觸更多新的東西。
