Mysql完全支持SQL 2003標(biāo)準(zhǔn)。某些數(shù)據(jù)庫(kù)（如DB2、Mimer）同樣支持。但也有部分不支持的，如Oracle、SQL Server不支持。我們將會(huì)給予足夠幫助和工具，使為其他DBMS編寫(xiě)的代碼能更容易轉(zhuǎn)移到Mysql上。 Setting up with MySQL 5.0 設(shè)置并開(kāi)始MySQL 5.0服務(wù)
這是我啟動(dòng)mysql客戶(hù)端的方式。你也許會(huì)使用其他方式，如果你使用的是二進(jìn)制版本或者是Windows系統(tǒng)的電腦，你可能會(huì)在其他子目錄下運(yùn)行以下程序： easy@phpv:~> /usr/local/mysql/bin/mysql --user=root Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 1 to server version: 5.0.3-alpha-debug Type 'help;' or 'h' for help. Type 'c' to clear the buffer. 在演示中，我將會(huì)展示以root身份登陸后的mysql客戶(hù)端返回的結(jié)果，這樣意味著我有極大的特權(quán)。轉(zhuǎn)載請(qǐng)注明翻譯者陳朋奕及轉(zhuǎn)自:www.phpv.net
Check for the Correct Version 核對(duì)版本 為了確認(rèn)使用的MySQL的版本是正確的，我們要查詢(xún)版本。我有兩種方法確認(rèn)我使用的是5.0版本： SHOW VARIABLES LIKE 'version'; or SELECT VERSION();
例如： mysql> SHOW VARIABLES LIKE 'version'; +---------------+-------------------+ | Variable_name | Value | +---------------+-------------------+ | version | 5.0.3-alpha-debug | +---------------+-------------------+ 1 row in set (0.00 sec) mysql> SELECT VERSION(); +-------------------+ | VERSION() | +-------------------+ | 5.0.3-alpha-debug | +-------------------+ 1 row in set (0.00 sec) 當(dāng)看見(jiàn)數(shù)字'5.0.x' 后就可以確認(rèn)存儲(chǔ)過(guò)程能夠在這個(gè)客戶(hù)端上正常工作。 The Sample "Database" 示例數(shù)據(jù)庫(kù) 現(xiàn)在要做的第一件事是創(chuàng)建一個(gè)新的數(shù)據(jù)庫(kù)然后設(shè)定為默認(rèn)數(shù)據(jù)庫(kù)實(shí)現(xiàn)這個(gè)步驟的SQL 語(yǔ)句如下： CREATE DATABASE db5; USE db5; 例如： mysql> CREATE DATABASE db5; Query OK, 1 row affected (0.00 sec) mysql> USE db5; Database changed 在這里要避免使用有重要數(shù)據(jù)的實(shí)際的數(shù)據(jù)庫(kù)然后我們創(chuàng)建一個(gè)簡(jiǎn)單的工作表。 實(shí)現(xiàn)這個(gè)步驟的SQL 語(yǔ)句如下： mysql> CREATE DATABASE db5; Query OK, 1 row affected (0.01 sec) mysql> USE db5; Database changed mysql> CREATE TABLE t (s1 INT); Query OK, 0 rows affected (0.01 sec) mysql> INSERT INTO t VALUES (5); Query OK, 1 row affected (0.00 sec) 你會(huì)發(fā)現(xiàn)我只在表中插入了一列。這樣做的原因是我要保持表的簡(jiǎn)單，因?yàn)樵谶@里并不需要展示查詢(xún)數(shù)據(jù)的技巧，而是教授存儲(chǔ)過(guò)程，不需要使用大的數(shù)據(jù)表，因?yàn)樗旧硪呀?jīng)夠復(fù)雜了。 這就是示例數(shù)據(jù)庫(kù)，我們將從這個(gè)名字為t的只包含一列的表開(kāi)始Pick a Delimiter 選擇分隔符 現(xiàn)在我們需要一個(gè)分隔符，實(shí)現(xiàn)這個(gè)步驟的SQL語(yǔ)句如下： DELIMITER // 例如： mysql> DELIMITER // 分 隔符是你通知mysql客戶(hù)端你已經(jīng)完成輸入一個(gè)SQL語(yǔ)句的字符或字符串符號(hào)。一直以來(lái)我們都使用分號(hào)“；”，但在存儲(chǔ)過(guò)程中，這會(huì)產(chǎn)生不少問(wèn)題，因?yàn)?存儲(chǔ)過(guò)程中有許多語(yǔ)句，所以每一個(gè)都需要一個(gè)分號(hào)因此你需要選擇一個(gè)不太可能出現(xiàn)在你的語(yǔ)句或程序中的字符串作為分隔符。我曾用過(guò)雙斜杠“//”，也有人 用豎線“|”。我曾見(jiàn)過(guò)在DB2程序中使用“@”符號(hào)的，但我不喜歡這樣。你可以根據(jù)自己的喜好來(lái)選擇，但是在這個(gè)課程中為了更容易理解，你最好選擇跟我 一樣。如果以后要恢復(fù)使用“;”（分號(hào)）作為分隔符，輸入下面語(yǔ)句就可以了： "DELIMITER ;//". CREATE PROCEDURE Example 創(chuàng)建程序?qū)嵗?br />CREATE PROCEDURE p1 () SELECT * FROM t; // 也許這是你使用Mysql創(chuàng)建的第一個(gè)存儲(chǔ)過(guò)程。假如是這樣的話，最好在你的日記中記下這個(gè)重要的里程碑。 CREATE PROCEDURE p1 () SELECT * FROM t; // <-- SQL語(yǔ)句存儲(chǔ)過(guò)程的第一部分是“CREATE PROCEDURE”： CREATE PROCEDURE p1 () SELECT * FROM t; // <-- 第二部分是過(guò)程名，上面新存儲(chǔ)過(guò)程的名字是p1。 Digression: Legal Identifiers 題外話：合法標(biāo)識(shí)符的問(wèn)題 存儲(chǔ)過(guò)程名對(duì)大小寫(xiě)不敏感，因此‘P1’和‘p1’是同一個(gè)名字，在同一個(gè)數(shù)據(jù)庫(kù)中你將不能給兩個(gè)存儲(chǔ)過(guò)程取相同的名字，因?yàn)檫@樣將會(huì)導(dǎo)致重載。某些DBMS允許重載（Oracle支持），但是MySQL不支持（譯者話：希望以后會(huì)支持吧。）。
你可以采取“數(shù)據(jù)庫(kù)名.存儲(chǔ)過(guò)程名”這樣的折中方法，如“db5.p1”。存儲(chǔ)過(guò)程名可以分開(kāi)，它可以包括空格符，其長(zhǎng)度限制為64個(gè)字符，但注意不要使用MySQL內(nèi)建函數(shù)的名字，如果這樣做了，在調(diào)用時(shí)將會(huì)出現(xiàn)下面的情況： mysql> CALL pi(); Error 1064 (42000): You have a syntax error. mysql> CALL pi (); Error 1305 (42000): PROCEDURE does not exist.
在上面的第一個(gè)例子里，我調(diào)用的是一個(gè)名字叫pi的函數(shù)，但你必須在調(diào)用的函數(shù)名后加上空格，就像第二個(gè)例子那樣。 CREATE PROCEDURE p1 () SELECT * FROM t; // <-- 其中“（）”是“參數(shù)列表”。 CREATE PROCEDURE 語(yǔ)句的第三部分是參數(shù)列表。通常需要在括號(hào)內(nèi)添加參數(shù)。例子中的存儲(chǔ)過(guò)程沒(méi)有參數(shù)，因此參數(shù)列表是空的—所以我只需要鍵入空括號(hào)，然而這是必須的。 CREATE PROCEDURE p1 () SELECT * FROM t; // <-- "SELECT * FROM t;" 是存儲(chǔ)過(guò)程的主體。 然后到了語(yǔ)句的最后一個(gè)部分了，它是存儲(chǔ)過(guò)程的主體，是一般的SQL語(yǔ)句。過(guò)程體中語(yǔ)句 "SELECT * FROM t;" 包含一個(gè)分號(hào)，如果后面有語(yǔ)句結(jié)束符號(hào)（//）時(shí)可以不寫(xiě)這個(gè)分號(hào)。 如果你還記得我把這部分叫做程序的主體將會(huì)是件好事，因?yàn)椋╞ody）這個(gè)詞是大家使用的技術(shù)上的術(shù)語(yǔ)。通常我們不會(huì)將SELECT語(yǔ)句用在存儲(chǔ)過(guò)程中，這里只是為了演示。所以使用這樣的語(yǔ)句，能在調(diào)用時(shí)更好的看出程序是否正常工作。 ySQL 5.0 新特性教程 第一部分存儲(chǔ)過(guò)程:第二講 Why MySQL Statements are Legal in a Procedure Body 什么MySQL語(yǔ)句在存儲(chǔ)過(guò)程體中是合法的？
什么樣的SQL語(yǔ)句在Mysql存儲(chǔ)過(guò)程中才是合法的呢？你可以創(chuàng)建一個(gè)包含INSERT, UPDATE,DELETE, SELECT, DROP, CREATE, REPLACE等的語(yǔ)句。你唯一需要記住的是如果代碼中包含MySQL擴(kuò)充功能，那么代碼將不能移植。在標(biāo)準(zhǔn)SQL語(yǔ)句中：任何數(shù)據(jù)庫(kù)定義語(yǔ)言都是合法 的，如： CREATE PROCEDURE p () DELETE FROM t; // SET、COMMIT以及ROLLBACK 也是合法的，如： CREATE PROCEDURE p () SET @x = 5; // MySQL的附加功能：任何數(shù)據(jù)操作語(yǔ)言的語(yǔ)句都將合法。 CREATE PROCEDURE p () DROP TABLE t; // MySQL擴(kuò)充功能：直接的SELECT也是合法的： CREATE PROCEDURE p () SELECT 'a'; // 順便提一下，我將存儲(chǔ)過(guò)程中包括DDL語(yǔ)句的功能稱(chēng)為MySQL附加功能的原因是在SQL標(biāo)準(zhǔn)中把這個(gè)定義為非核心的，即可選組件。轉(zhuǎn)載請(qǐng)注明翻譯者陳朋奕及轉(zhuǎn)自:www.phpv.net
在過(guò)程體中有一個(gè)約束，就是不能有對(duì)例程或表操作的數(shù)據(jù)庫(kù)操作語(yǔ)句。例如下面的例子就是非法的： CREATE PROCEDURE p1 () CREATE PROCEDURE p2 () DELETE FROM t; // 下面這些對(duì)MySQL 5.0來(lái)說(shuō)全新的語(yǔ)句，過(guò)程體中是非法的?lt;BR>CREATE PROCEDURE, ALTER PROCEDURE, DROP PROCEDURE, CREATE FUNCTION, DROP FUNCTION, CREATE TRIGGER, DROP TRIGGER. 不過(guò)你可以使用 "CREATE PROCEDURE db5.p1 () DROP DATABASE db5//" ，但是類(lèi)似 "USE database" 語(yǔ)句也是非法的，因?yàn)镸ySQL假定默認(rèn)數(shù)據(jù)庫(kù)就是過(guò)程的工作場(chǎng)所。 Call the Procedure 調(diào)用存儲(chǔ)過(guò)程 1. 現(xiàn)在我們就可以調(diào)用一個(gè)存儲(chǔ)過(guò)程了，你所需要輸入的全部就是CALL和你過(guò)程名以及一個(gè)括號(hào)再一次強(qiáng)調(diào)，括號(hào)是必須的當(dāng)你調(diào)用例子里面的p1過(guò)程時(shí)，結(jié)果是屏幕返回了t表的內(nèi)容 mysql> CALL p1() // +------+ | s1 | +------+ | 5 | +------+ 1 row in set (0.03 sec) Query OK, 0 rows affected (0.03 sec) 因?yàn)檫^(guò)程中的語(yǔ)句是 "SELECT * FROM t;"
2. Let me say that again, another way. 其他實(shí)現(xiàn)方式 mysql> CALL p1() // 和下面語(yǔ)句的執(zhí)行效果一樣： mysql> SELECT * FROM t; // 所以，你調(diào)用p1過(guò)程就相當(dāng)于你執(zhí)行了下面語(yǔ)句： "SELECT * FROM t;". 好了，主要的知識(shí)點(diǎn)"創(chuàng)建和調(diào)用過(guò)程方法"已經(jīng)清楚了。我希望你能對(duì)自己說(shuō)這相當(dāng)簡(jiǎn)單。但是很快我們就有一系列的練習(xí)，每次都加一條子句，或者改變已經(jīng)存在的子句。那樣在寫(xiě)復(fù)雜部件前我們將會(huì)有很多可用的子句。轉(zhuǎn)載請(qǐng)注明翻譯者陳朋奕及轉(zhuǎn)自:www.phpv.net Characteristics Clauses 特征子句 1.
CREATE PROCEDURE p2 () LANGUAGE SQL <-- NOT DETERMINISTIC <-- SQL SECURITY DEFINER <-- COMMENT 'A Procedure' <-- SELECT CURRENT_DATE, RAND() FROM t //
CREATE PROCEDURE p7 () BEGIN 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. 這個(gè)語(yǔ)句將不會(huì)被調(diào)用 */
The New SQL Statements 新SQL語(yǔ)句 Variables 變量 在復(fù)合語(yǔ)句中聲明變量的指令是DECLARE。 (1) Example with two DECLARE statements 兩個(gè)DECLARE語(yǔ)句的例子 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 */
(2) Example with no DEFAULT clause and SET statement 沒(méi)有默認(rèn)子句和設(shè)定語(yǔ)句的例子 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 */ 有很多初始化變量的方法。如果沒(méi)有默認(rèn)的子句，那么變量的初始值為NULL。你可以在任何時(shí)候使用SET語(yǔ)句給變量賦值。
(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子句來(lái)設(shè)定初 始值，這就不需要把DECLARE和SET語(yǔ)句的實(shí)現(xiàn)分開(kāi)了。 (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é)果顯示了過(guò)程能正常工作
Conditions and IF-THEN-ELSE 條件式和IF-THEN-ELSE 1. 現(xiàn)在我們可以寫(xiě)一些包含條件式的例子： 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語(yǔ)句的過(guò)程。里面有兩個(gè)IF語(yǔ)句，一個(gè)是IF語(yǔ)句END IF，另一個(gè)是IF語(yǔ)句ELSE語(yǔ)句END IF。我們可以在這里使用復(fù)雜的過(guò)程，但我會(huì)盡量使其簡(jiǎn)單讓你能更容易弄清楚。 2. CALL p12 (0) // 我們調(diào)用這個(gè)過(guò)程，傳入值為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 被跳過(guò)，沒(méi)有被執(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é)果為真，于是中間語(yǔ)句被執(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語(yǔ)句被執(zhí)行。如果parameter1值為NULL，則下一條 UPDATE 語(yǔ)句將被執(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語(yǔ)句。CASE語(yǔ)句使用和IF一樣簡(jiǎn)單。 我們可以參考上面的例子： 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í)行過(guò)程后，傳入值1，如上面例子，值19被插入到表t中。 Question 問(wèn)題 問(wèn)題: CALL p13(NULL) //的作用是什么？ 另一個(gè)：這個(gè)CALL語(yǔ)句做了那些動(dòng)作？ 你可以通過(guò)執(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語(yǔ)句的ELSE部分就被執(zhí)行了。希望這對(duì)大家有意義。如果你回答不出來(lái)，沒(méi)有問(wèn)題，我們可以繼續(xù)向下走。
Loops 循環(huán)語(yǔ)句 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（譯者語(yǔ)：最好不要用吧，用了就使流程混亂）。 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語(yǔ)句相似，因此不需要掌握很多新的語(yǔ)法。這里的INSERT和SET語(yǔ)句在WHILE和END WHILE之間，當(dāng)變量v大于5的時(shí)候循環(huán)將會(huì)退出。使用 "SET v = 0;" 語(yǔ)句使為了防止一個(gè)常見(jiàn)的錯(cuò)誤，如果沒(méi)有初始化，默認(rèn)變量值為NULL，而NULL和任何值操作結(jié)果都為NULL。 WHILE ... END WHILE example mysql> CALL p14()// Query OK, 1 row affected (0.00 sec) 以上就是調(diào)用過(guò)程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ù)庫(kù)中插入了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í)行前檢查。（譯者語(yǔ)：可能等同于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語(yǔ)句后面沒(méi)有分號(hào)，在這里可以不寫(xiě)分號(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過(guò)程后又插入了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語(yǔ)句，在IF語(yǔ)句中包含LEAVE語(yǔ)句。這里L(fēng)EAVE語(yǔ)句的意義是離開(kāi)循環(huán)。 LEAVE的語(yǔ)法是LEAVE加循環(huán)語(yǔ)句標(biāo)號(hào)，關(guān)于循環(huán)語(yǔ)句的標(biāo)號(hào)問(wèn)題我會(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)用過(guò)程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)例子中我使用了語(yǔ)句標(biāo)號(hào)。現(xiàn)在這里有一個(gè)包含4個(gè)語(yǔ)句標(biāo)號(hào)的過(guò)程的例子。我們可以在BEGIN、 WHILE、REPEAT或者LOOP語(yǔ)句前使用語(yǔ)句標(biāo)號(hào)，語(yǔ)句標(biāo)號(hào)只能在合法的語(yǔ)句前面使用。因此"LEAVE label_3"意味著離開(kāi)語(yǔ)句標(biāo)號(hào)名定義為label_3的語(yǔ)句或復(fù)合語(yǔ)句。
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 ; // 你也可以在語(yǔ)句結(jié)束時(shí)使用語(yǔ)句標(biāo)號(hào)，和在開(kāi)頭時(shí)使用一樣。這些標(biāo)號(hào)結(jié)束符并不是十分有用。 它們是可選的。如果你需要，他們必須和開(kāi)始定義的標(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 語(yǔ)句使程序跳出復(fù)雜的復(fù)合語(yǔ)句。 ITERATE 迭代如果目標(biāo)是ITERATE（迭代）語(yǔ)句的話，就必須用到LEAVE語(yǔ)句 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 深入循環(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)行起來(lái)。
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; // 然后開(kāi)始ITERATE（迭代）過(guò)程。
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語(yǔ)句
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ù)合語(yǔ)句的最后一步。
mysql> CREATE PROCEDURE p9 () -> BEGIN -> DECLARE EXIT HANDLER FOR NOT FOUND BEGIN END; -> DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN END; -> DECLARE EXIT HANDLER FOR SQLWARNING BEGIN END; -> END;// Query OK, 0 rows affected (0.00 sec)
這里是三個(gè)預(yù)聲明的條件：NOT FOUND (找不到行), SQLEXCEPTION (錯(cuò)誤),SQLWARNING ( 警告或注釋)。因?yàn)樗鼈兪穷A(yù)聲明的，因此不需要聲明條件就可以使用。不過(guò)如果你去做這樣的聲明："DECLARE SQLEXCEPTION CONDITION ..."，你將會(huì)得到錯(cuò)誤信息提示。
Cursors 游標(biāo)
游標(biāo)實(shí)現(xiàn)功能摘要： DECLARE cursor-name CURSOR FOR SELECT ...; OPEN cursor-name; FETCH cursor-name INTO variable [, variable]; CLOSE cursor-name; 現(xiàn)在我們開(kāi)始著眼游標(biāo)了。雖然我們的存儲(chǔ)過(guò)程中的游標(biāo)語(yǔ)法還并沒(méi)有完整的實(shí)現(xiàn)，但是 已經(jīng)可以完成基本的事務(wù)如聲明游標(biāo)，打開(kāi)游標(biāo)，從游標(biāo)里讀取，關(guān)閉游標(biāo)。
1. Cursor Example CREATE PROCEDURE p25 (OUT return_val INT) BEGIN DECLARE a,b INT; DECLARE cur_1 CURSOR FOR SELECT s1 FROM t; DECLARE CONTINUE HANDLER FOR NOT FOUND SET b = 1; OPEN cur_1; REPEAT FETCH cur_1 INTO a; UNTIL b = 1 END REPEAT; CLOSE cur_1; SET return_val = a; END;// 我們看一下包含游標(biāo)的存儲(chǔ)過(guò)程的新例子。
2. Cursor Example
CREATE PROCEDURE p25 (OUT return_val INT) BEGIN DECLARE a,b INT; <-- DECLARE cur_1 CURSOR FOR SELECT s1 FROM t; DECLARE CONTINUE HANDLER FOR NOT FOUND SET b = 1; OPEN cur_1; REPEAT FETCH cur_1 INTO a; UNTIL b = 1 END REPEAT; CLOSE cur_1; SET return_val = a; END;// 這個(gè)過(guò)程開(kāi)始聲明了三個(gè)變量。附帶說(shuō)一下，順序是十分重要的。首先要進(jìn)行變量聲明， 然后聲明條件，隨后聲明游標(biāo)，再后面才是聲明錯(cuò)誤處理器。如果你沒(méi)有按順序聲明， 系統(tǒng)會(huì)提示錯(cuò)誤信息。
3. Cursor Example
CREATE PROCEDURE p25 (OUT return_val INT) BEGIN DECLARE a,b INT; DECLARE cur_1 CURSOR FOR SELECT s1 FROM t; <-- DECLARE CONTINUE HANDLER FOR NOT FOUND SET b = 1; OPEN cur_1; REPEAT FETCH cur_1 INTO a; UNTIL b = 1 END REPEAT; CLOSE cur_1; SET return_val = a; END;//
CREATE PROCEDURE p25 (OUT return_val INT) BEGIN DECLARE a,b INT; DECLARE cur_1 CURSOR FOR SELECT s1 FROM t; DECLARE CONTINUE HANDLER FOR NOT FOUND <-- SET b = 1; <-- OPEN cur_1; REPEAT FETCH cur_1 INTO a; UNTIL b = 1 END REPEAT; CLOSE cur_1; SET return_val = a; END;//
最后進(jìn)行的是錯(cuò)誤處理器的聲明。這個(gè)CONTINUE處理沒(méi)有引用SQL錯(cuò)誤代碼和SQLSTATE值。 它使用的是NOT FOUND系統(tǒng)返回值，這和SQLSTATE 02000是一樣的。 轉(zhuǎn)載請(qǐng)注明翻譯者陳朋奕及轉(zhuǎn)自:www.phpv.net 5. Cursor Example CREATE PROCEDURE p25 (OUT return_val INT) BEGIN DECLARE a,b INT; DECLARE cur_1 CURSOR FOR SELECT s1 FROM t; DECLARE CONTINUE HANDLER FOR NOT FOUND SET b = 1; OPEN cur_1; <-- REPEAT FETCH cur_1 INTO a; UNTIL b = 1 END REPEAT; CLOSE cur_1; SET return_val = a; END;//
過(guò)程第一個(gè)可執(zhí)行的語(yǔ)句是OPEN cur_1，它與SELECT s1 FROM t語(yǔ)句是關(guān)聯(lián)的，過(guò)程將執(zhí)行 SELECT s1 FROM t，返回一個(gè)結(jié)果集。
6. Cursor Example CREATE PROCEDURE p25 (OUT return_val INT) BEGIN DECLARE a,b INT; DECLARE cur_1 CURSOR FOR SELECT s1 FROM t; DECLARE CONTINUE HANDLER FOR NOT FOUND SET b = 1; OPEN cur_1; REPEAT FETCH cur_1 INTO a; <-- UNTIL b = 1 END REPEAT; CLOSE cur_1; SET return_val = a; END;//
CREATE PROCEDURE p25 (OUT return_val INT) BEGIN DECLARE a,b INT; DECLARE cur_1 CURSOR FOR SELECT s1 FROM t; DECLARE CONTINUE HANDLER FOR NOT FOUND SET b = 1; <-- OPEN cur_1; REPEAT FETCH cur_1 INTO a; UNTIL b = 1 END REPEAT; CLOSE cur_1; SET return_val = a; END;//
CREATE PROCEDURE p25 (OUT return_val INT) BEGIN DECLARE a,b INT; DECLARE cur_1 CURSOR FOR SELECT s1 FROM t; DECLARE CONTINUE HANDLER FOR NOT FOUND SET b = 1; OPEN cur_1; REPEAT FETCH cur_1 INTO a; UNTIL b = 1 END REPEAT; CLOSE cur_1; <-- SET return_val = a; END;//
CREATE PROCEDURE p25 (OUT return_val INT) BEGIN DECLARE a,b INT; DECLARE cur_1 CURSOR FOR SELECT s1 FROM t; DECLARE CONTINUE HANDLER FOR NOT FOUND SET b = 1; OPEN cur_1; REPEAT FETCH cur_1 INTO a; UNTIL b = 1 END REPEAT; CLOSE cur_1; SET return_val = a; <-- END;// 這個(gè)例程中我們?yōu)檩敵鰠?shù)指派了一個(gè)局部變量，這樣在過(guò)程結(jié)束后的結(jié)果仍能使用。 轉(zhuǎn)載請(qǐng)注明翻譯者陳朋奕及轉(zhuǎn)自:www.phpv.net 10. Cursor Example
CREATE PROCEDURE p25 (OUT return_val INT) BEGIN DECLARE a,b INT; DECLARE cur_1 CURSOR FOR SELECT s1 FROM t; DECLARE CONTINUE HANDLER FOR NOT FOUND SET b = 1; OPEN cur_1; REPEAT FETCH cur_1 INTO a; UNTIL b = 1 END REPEAT; CLOSE cur_1; SET return_val = a; END;//
Cursor Characteristics 游標(biāo)的特性 摘要：轉(zhuǎn)載請(qǐng)注明翻譯者陳朋奕及轉(zhuǎn)自:www.phpv.net READ ONLY只讀屬性 NOT SCROLLABLE順序讀取 ASENSITIVE敏感
在5.0版的MySQL中，你只可以從游標(biāo)中取值，不能對(duì)其進(jìn)行更新。因?yàn)橛螛?biāo)是（READ ONLY）只讀的。你可以這樣做： FETCH cursor1 INTO variable1; UPDATE t1 SET column1 = 'value1' WHERE CURRENT OF cursor1;
游標(biāo)也是不可以滾動(dòng)的，只允許逐一讀取下一行，不能在結(jié)果集中前進(jìn)或后退。下面代碼就 是錯(cuò)誤的： FETCH PRIOR cursor1 INTO variable1; FETCH ABSOLUTE 55 cursor1 INTO variable1;
同時(shí)也不允許在已打開(kāi)游標(biāo)進(jìn)行操作的表上執(zhí)行updates事務(wù)，因?yàn)橛螛?biāo)是（ASENSITIVE）敏感的。因?yàn)槿绻悴蛔柚箄pdate事務(wù)，那就不知道結(jié)果會(huì)變成什么。如果你使用的是InnoDB 而不是MyISAM存儲(chǔ)引擎的話，結(jié)果也會(huì)不一樣。 Security 安全措施 摘要 Privileges (1) CREATE ROUTINE Privileges (2) EXECUTE Privileges (3) GRANT SHOW ROUTINE? Privileges (4) INVOKERS AND DEFINERS
GRANT SHOW ROUTINE ON db6.* TO joey [WITH GRANT OPTION];
因?yàn)槲覀円呀?jīng)有控制視圖的特權(quán)了：GRANT SHOW VIEW。所以在這個(gè)基礎(chǔ)上，為了保證兼容， 日后可能會(huì)添加GRANT SHOW ROUTINE特權(quán)。這樣做是不太符合標(biāo)準(zhǔn)的，在寫(xiě)本書(shū)的時(shí)候，MySQL還沒(méi)實(shí)現(xiàn)這個(gè)功能。
4. Privileges Invokers and Definers 特權(quán)調(diào)用者和定義者
CREATE PROCEDURE p26 () SQL SECURITY INVOKER SELECT COUNT(*) FROM t // CREATE PROCEDURE p27 () SQL SECURITY DEFINER SELECT COUNT(*) FROM t // GRANT INSERT ON db5.* TO peter; //
Conventions and Styles約定和編程風(fēng)格 每次我想要演示實(shí)際代碼時(shí)，我會(huì)對(duì)mysql客戶(hù)端的屏幕就出現(xiàn)的代碼進(jìn)行調(diào)整，將字體改成Courier，使他們看起來(lái)與普通文本不一樣（讓大家區(qū)別程序代碼和正文）。在這里舉個(gè)例子：
mysql> DROP FUNCTION f; Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @x, t22.* FROM t22// +------------------------+------+ | @x | s1 | +------------------------+------+ | Trigger was activated! | 55 | +------------------------+------+ 1 row in set (0.00 sec)
大家可以看到INSERT動(dòng)作之后的結(jié)果，和我們預(yù)期的一樣，x標(biāo)記被改動(dòng)了，同時(shí)這里插入的數(shù)據(jù)不是我們開(kāi)始輸入的插入數(shù)據(jù)，而是觸發(fā)器自己的數(shù)據(jù)。 Example of a "check" constraint "check"完整性約束例子
