MySQL5.0新特性教程存儲過程:第三講

suncly 2006-06-21

展開全文

The New SQL Statements 新SQL語句

Variables 變量

　　在復(fù)合語句中聲明變量的指令是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

　　沒有默認(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。你可以在任何時候使用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; //

我們在這里做了一些改變，但是結(jié)果還是一樣的。在這里使用了DEFAULT子句來設(shè)定初始值，這就不需要把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)

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

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

mysql> CALL p11()// +-------+ | x1 | +-------+ | inner | +-------+ +-------+ | x1 | +-------+ | outer | +-------+

　　我們看到的結(jié)果時第一個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。我們可以在這里使用復(fù)雜的過程，但我會盡量使其簡單讓你能更容易弄清楚。

　　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條件，判斷結(jié)果為真，于是中間語句被執(zhí)行了

　　6.

因為參數(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)

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

　　如果需要進行更多條件真假的判斷我們可以使用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)

　　答案是當(dāng)你調(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)。我們有三種標(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的時候循環(huán)將會退出。使用

"SET v = 0;"

　　語句使為了防止一個常見的錯誤，如果沒有初始化，默認(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"，因為這里的計數(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í)行后檢查結(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語句后面沒有分號，在這里可以不寫分號，當(dāng)然你加上額外的分號更好。

　　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)一樣也不需要結(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)號，關(guān)于循環(huán)語句的標(biāo)號問題我會在后面進一步講解。
　　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)號

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

End Labels 標(biā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é)束時使用語句標(biāo)號，和在開頭時使用一樣。這些標(biāo)號結(jié)束符并不是十分有用。

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

CREATE PROCEDURE p19 (parameter1 CHAR) label_1: BEGIN label_2: BEGIN label_3: BEGIN IF parameter1 IS NOT NULL THEN IF parameter1 = ‘a(chǎn)‘ 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

　?。ǖ┱Z句和LEAVE語句一樣也是在循環(huán)內(nèi)部的循環(huán)引用，它有點像C語言中的“Continue”，同樣它可以出現(xiàn)在復(fù)合語句中，引用復(fù)合語句標(biāo)號，ITERATE（迭代）意思是重新開始復(fù)合語句。

　　那我們啟動并觀察下面這個循環(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)定義了標(biāo)號的循環(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; //

　　當(dāng)v的值變?yōu)?時，程序?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)，使運行指令到達復(fù)合語句的最后一步。

GOTO

CREATE PROCEDURE p...

BEGIN

...

LABEL label_name;

...

GOTO label_name;

...

END;

　　MySQL的存儲過程中可以使用GOTO語句。雖然這不是標(biāo)準(zhǔn)SQL語句，而且在這里建立標(biāo)號的方法也和慣例中的不一樣。由于為了和其他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塊復(fù)合語句，變量聲明，IF，WHILE，LOOP，REPEAT，LEAVE，ITERATE，GOTO。這是一個荒謬的存儲過程，我不會運行它，因為里面有無盡的循環(huán)。但是里面的語法卻十分合法。這些是新的流程控制和變量聲明語句。下面我們將要接觸更多新的東西。

未完待續(xù)...