MySQL5.0新特性
Introduction 簡(jiǎn)介
MySQL 5.0 新特性教程是為需要了解5.0版本新特性的MySQL老用戶而寫的。簡(jiǎn)單的來(lái)說(shuō)是介紹了“存儲(chǔ)過(guò)程、觸發(fā)器、視圖、信息架構(gòu)視圖”,我打算每天一部分,來(lái) 連載這個(gè)教程.如果你想看一次看完P(guān)DF版本的教程,請(qǐng)到down.phpv.net下載.在此感謝譯者陳朋奕的努力.
希望這本書(shū)能像內(nèi)行專家那樣與您進(jìn)行對(duì)話，用簡(jiǎn)單的問(wèn)題、例子讓你學(xué)到需要的知識(shí)。為了達(dá)到這樣的目的，我會(huì)從每一個(gè)細(xì)節(jié)開(kāi)始慢慢的為大家建立概念，最后會(huì)給大家展示較大的實(shí)用例，在學(xué)習(xí)之前也許大家會(huì)認(rèn)為這個(gè)用例很難，但是只要跟著課程去學(xué)，相信很快就能掌握。[@more@]MySQL 5.0 新特性教程 第一部分:第一講
Conventions and Styles 約定和編程風(fēng)格
每次我想要演示實(shí)際代碼時(shí)，我會(huì)對(duì)mysql客戶端的屏幕就出現(xiàn)的代碼進(jìn)行調(diào)整，將字體改成Courier，使他們看起來(lái)與普通文本不一樣。
在這里舉個(gè)例子：mysql> DROP FUNCTION f;Query OK, 0 rows affected (0.00 sec)
如果實(shí)例比較大，則需要在某些行和段落間加注釋，同時(shí)我會(huì)用將“<--”符號(hào)放在頁(yè)面的右邊以表示強(qiáng)調(diào)。
例如：
mysql> CREATE PROCEDURE p ()
-> BEGIN
-> /* This procedure does nothing */ <--
-> END;//Query OK, 0 rows affected (0.00 sec)
有時(shí)候我會(huì)將例子中的"mysql>"和"->"這些系統(tǒng)顯示去 掉，你可以直接將代碼復(fù)制到mysql客戶端程序中（如果你現(xiàn)在所讀的 不是電子版的，可以在mysql.com網(wǎng)站下載相關(guān)腳本）所以的例子都已經(jīng)在Suse 9.2 Linux、Mysql 5.0.3公共版上測(cè)試通過(guò)。
在您閱讀本書(shū)的時(shí)候，Mysql已經(jīng)有更高的版本，同時(shí)能支持更多OS了，包括Windows，Sparc，HP-UX。因此這里的例子將能正常的運(yùn)行在您的電腦上。但如果運(yùn)行仍然出現(xiàn)故障，可以咨詢你認(rèn)識(shí)的資深Mysql用戶，以得到長(zhǎng)久的支持和幫助。
A Definition and an Example 定義及實(shí)例
定義及實(shí)例存儲(chǔ)過(guò)程是一種存儲(chǔ)在書(shū)庫(kù)中的程序（就像正規(guī)語(yǔ)言里的子程序一樣）， 準(zhǔn)確的來(lái)說(shuō)，MySQL支持的“routines（例程）”有兩種： 一是我們說(shuō)的存儲(chǔ)過(guò)程，二是在其他SQL語(yǔ)句中可以返回值的函數(shù)（使用起來(lái)和Mysql預(yù)裝載的函數(shù)一樣，如pi()）。我在本書(shū)里面會(huì)更經(jīng)常使用存儲(chǔ)過(guò) 程，因?yàn)檫@是我們過(guò)去的習(xí)慣，相信大家也會(huì)接受。
一個(gè)存儲(chǔ)過(guò)程包括名字，參數(shù)列表，以及可以包括很多SQL語(yǔ)句的SQL語(yǔ)句集。
在這里對(duì)局部變量，異常處理，循環(huán)控制和IF條件句有新的語(yǔ)法定義。
下面是一個(gè)包括存儲(chǔ)過(guò)程的實(shí)例聲明：（譯注：為了方便閱讀，此后的程序不添任何中文注釋）
CREATE PROCEDURE procedure1 /* name存儲(chǔ)過(guò)程名*/
(IN parameter1 INTEGER) /* parameters參數(shù)*/
BEGIN /* start of block語(yǔ)句塊頭*/
DECLARE variable1 CHAR(10); /* variables變量聲明*/
IF parameter1 = 17 THEN /* start of IF IF條件開(kāi)始*/
SET variable1 = 'birds'; /* assignment賦值*/
ELSE
SET variable1 = 'beasts'; /* assignment賦值*/
END IF; /* end of IF IF結(jié)束*/
INSERT INTO table1 VALUES (variable1);/* statement SQL語(yǔ)句*/
END /* end of block語(yǔ)句塊結(jié)束*/
下面我將會(huì)介紹你可以利用存儲(chǔ)過(guò)程做的工作的所有細(xì)節(jié)。同時(shí)我們將介紹新的數(shù)據(jù)庫(kù)對(duì)象—觸發(fā)器，因?yàn)橛|發(fā)器和存儲(chǔ)過(guò)程的關(guān)聯(lián)是必然的。
Why Stored Procedures 為什么要用存儲(chǔ)過(guò)程
由于存儲(chǔ)過(guò)程對(duì)于MySQL來(lái)說(shuō)是新的功能，很自然的在使用時(shí)你需要更加注意。
畢竟，在此之前沒(méi)有任何人使用過(guò)，也沒(méi)有很多大量的有經(jīng)驗(yàn)的用戶來(lái)帶你走他們走過(guò)的路。然而你應(yīng)該開(kāi)始考慮把現(xiàn)有程序（可能在服務(wù)器應(yīng)用程序中，用戶自定義函數(shù)（UDF）中，或是腳本中）轉(zhuǎn)移到存儲(chǔ)過(guò)程中來(lái)。這樣做不需要原因，你不得不去做。
因?yàn)榇鎯?chǔ)過(guò)程是已經(jīng)被認(rèn)證的技術(shù)！雖然在Mysql中它是新的，但是相同功能的函數(shù)在其他DBMS中早已存在，而它們的語(yǔ)法往是相同的。因此你可以從其他人那里獲得這些概念，也有很多你可以咨詢或者雇用的經(jīng)驗(yàn)用戶，還有許多第三方的文檔可供你閱讀。
存 儲(chǔ)過(guò)程會(huì)使系統(tǒng)運(yùn)行更快！雖然我們暫時(shí)不能在Mysql上證明這個(gè)優(yōu)勢(shì)，用戶得到的體驗(yàn)也不一樣。我們可以說(shuō)的就是Mysql服務(wù)器在緩存機(jī)制上做了改 進(jìn)，就像Preparedstatements（預(yù)處理語(yǔ)句）所做的那樣。由于沒(méi)有編譯器，因此SQL存儲(chǔ)過(guò)程不會(huì)像外部語(yǔ)言（如C）編寫的程序運(yùn)行起來(lái) 那么快。但是提升速度的主要方法卻在于能否降低網(wǎng)絡(luò)信息流量。如果你需要處理的是需要檢查、循環(huán)、多語(yǔ)句但沒(méi)有用戶交互的重復(fù)性任務(wù)，你就可以使用保存在 服務(wù)器上的存儲(chǔ)過(guò)程來(lái)完成。這樣在執(zhí)行任務(wù)的每一步時(shí)服務(wù)器和客戶端之間就沒(méi)那么多的信息來(lái)往了。轉(zhuǎn)載請(qǐng)注明翻譯者陳朋奕及轉(zhuǎn)自:www.phpv.net
所 以存儲(chǔ)過(guò)程是可復(fù)用的組件！想象一下如果你改變了主機(jī)的語(yǔ)言，這對(duì)存儲(chǔ)過(guò)程不會(huì)產(chǎn)生影響，因?yàn)樗菙?shù)據(jù)庫(kù)邏輯而不是應(yīng)用程序。存儲(chǔ)過(guò)程是可以移植的！當(dāng)你 用SQL編寫存儲(chǔ)過(guò)程時(shí)，你就知道它可以運(yùn)行在Mysql支持的任何平臺(tái)上，不需要你額外添加運(yùn)行環(huán)境包，也不需要為程序在操作系統(tǒng)中執(zhí)行設(shè)置許可，或者 為你的不同型號(hào)的電腦存儲(chǔ)過(guò)程將被保存！如果你編寫好了一個(gè)程序，例如顯示銀行事物處理中的支票撤消，那想要了解支票的人就可以找到你的程序。
它會(huì)以源代碼的形式保存在數(shù)據(jù)庫(kù)中。這將使數(shù)據(jù)和處理數(shù)據(jù)的進(jìn)程有意義的關(guān)聯(lián)這可能跟你在課上聽(tīng)到的規(guī)劃論中說(shuō)的一樣。存儲(chǔ)過(guò)程可以遷移！
Mysql完全支持SQL 2003標(biāo)準(zhǔn)。某些數(shù)據(jù)庫(kù)（如DB2、Mimer）同樣支持。但也有部分不支持的，如Oracle、SQL Server不支持。我們將會(huì)給予足夠幫助和工具，使為其他DBMS編寫的代碼能更容易轉(zhuǎn)移到Mysql上。
Setting up with MySQL 5.0 設(shè)置并開(kāi)始MySQL 5.0服務(wù)
通過(guò)
mysql_fix_privilege_tables
或者
~/mysql-5.0/scripts/mysql_install_db
來(lái)開(kāi)始MySQL服務(wù)
作為我們練習(xí)的準(zhǔn)備工作的一部分，我假定MySQL 5.0已經(jīng)安裝。如果沒(méi)有數(shù)據(jù)庫(kù)管理員為你安裝好數(shù)據(jù)庫(kù)以及其他軟件，你就需要自己去安裝了。不過(guò)你很容易忘掉一件事，那就是你需要有一個(gè)名為mysql.proc的表。
在安裝了最新版本后，你必須運(yùn)行
mysql_fix_privilege_tables
或者
mysql_install_db
（只需要運(yùn)行其中一個(gè)就夠了）——不然存儲(chǔ)過(guò)程將不能工作。我同時(shí)啟用在root身份后運(yùn)行一個(gè)非正式的SQL腳本，如下：
mysql>source/home/pgulutzan/mysql-5.0/scripts/mysql_prepare_privilege_tables_for_5.sql
Starting the MySQL Client 啟動(dòng)MySQL客戶端
這是我啟動(dòng)mysql客戶端的方式。你也許會(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客戶端返回的結(jié)果，這樣意味著我有極大的特權(quán)。轉(zhuǎn)載請(qǐng)注明翻譯者陳朋奕及轉(zhuǎn)自:www.phpv.net
Check for the Correct Version 核對(duì)版本
為了確認(rèn)使用的MySQL的版本是正確的，我們要查詢版本。我有兩種方法確認(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è)客戶端上正常工作。
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)樵谶@里并不需要展示查詢數(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客戶端你已經(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ì)大小寫不敏感，因此‘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í)可以不寫這個(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ǔ)句的功能稱為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//"
，但是類似
"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)存在的子句。那樣在寫復(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 //
這里我給出的是一些能反映存儲(chǔ)過(guò)程特性的子句。子句內(nèi)容在括號(hào)之后，主體之前。這些子句都是可選的，他們有什么作用呢？
2.
CREATE PROCEDURE p2 ()
LANGUAGE SQL <--
NOT DETERMINISTIC
SQL SECURITY DEFINER
COMMENT 'A Procedure'
SELECT CURRENT_DATE, RAND() FROM t //
很 好，這個(gè)LANGUAGE SQL子句是沒(méi)有作用的。僅是為了說(shuō)明下面過(guò)程的主體使用SQL語(yǔ)言編寫。這條是系統(tǒng)默認(rèn)的，但你在這里聲明是有用的，因?yàn)槟承〥BMS（IBM的 DB2）需要它，如果你關(guān)注DB2的兼容問(wèn)題最好還是用上。此外，今后可能會(huì)出現(xiàn)除SQL外的其他語(yǔ)言支持的存儲(chǔ)過(guò)程。
3.
CREATE PROCEDURE p2 ()
LANGUAGE SQL
NOT DETERMINISTIC <--
SQL SECURITY DEFINER
COMMENT 'A Procedure'
SELECT CURRENT_DATE, RAND() FROM t //
下 一個(gè)子句，NOT DETERMINISTIC，是傳遞給系統(tǒng)的信息。這里一個(gè)確定過(guò)程的定義就是那些每次輸入一樣輸出也一樣的程序。在這個(gè)案例中，既然主體中含有 SELECT語(yǔ)句，那返回肯定是未知的因此我們稱其NOT DETERMINISTIC。但是MySQL內(nèi)置的優(yōu)化程序不會(huì)注意這個(gè)，至少在現(xiàn)在不注意。轉(zhuǎn)載請(qǐng)注明翻譯者陳朋奕及轉(zhuǎn)自:www.phpv.net
4.
CREATE PROCEDURE p2 ()
LANGUAGE SQL
NOT DETERMINISTIC
SQL SECURITY DEFINER <--
COMMENT 'A Procedure'
SELECT CURRENT_DATE, RAND() FROM t //
下一個(gè)子句是SQL SECURITY，可以定義為SQL SECURITY DEFINER或SQL SECURITY INVOKER。
這就進(jìn)入了權(quán)限控制的領(lǐng)域了，當(dāng)然我們?cè)诤竺鎸?huì)有測(cè)試權(quán)限的例子。
SQL SECURITY DEFINER
意味著在調(diào)用時(shí)檢查創(chuàng)建過(guò)程用戶的權(quán)限（另一個(gè)選項(xiàng)是SQLSECURITY INVOKER）。
現(xiàn)在而言，使用
SQL SECURITY DEFINER
指令告訴MySQL服務(wù)器檢查創(chuàng)建過(guò)程的用戶就可以了，當(dāng)過(guò)程已經(jīng)被調(diào)用，就不檢查執(zhí)行調(diào)用過(guò)程的用戶了。而另一個(gè)選項(xiàng)（INVOKER）
則是告訴服務(wù)器在這一步仍然要檢查調(diào)用者的權(quán)限。轉(zhuǎn)載請(qǐng)注明翻譯者陳朋奕及轉(zhuǎn)自:www.phpv.net
5.
CREATE PROCEDURE p2 ()
LANGUAGE SQL
NOT DETERMINISTIC
SQL SECURITY DEFINER
COMMENT 'A Procedure' <--
SELECT CURRENT_DATE, RAND() FROM t //
COMMENT 'A procedure'
是一個(gè)可選的注釋說(shuō)明。
最后，注釋子句會(huì)跟過(guò)程定義存儲(chǔ)在一起。這個(gè)沒(méi)有固定的標(biāo)準(zhǔn)，我在文中會(huì)指出沒(méi)有固定標(biāo)準(zhǔn)的語(yǔ)句，不過(guò)幸運(yùn)的是這些在我們標(biāo)準(zhǔn)的SQL中很少。
6.
CREATE PROCEDURE p2 ()
LANGUAGE SQL
NOT DETERMINISTIC
SQL SECURITY DEFINER
COMMENT ''
SELECT CURRENT_DATE, RAND() FROM t //
上面過(guò)程跟下面語(yǔ)句是等效的：
CREATE PROCEDURE p2 ()
SELECT CURRENT_DATE, RAND() FROM t //
特征子句也有默認(rèn)值，如果省略了就相當(dāng)于：
LANGUAGE SQL NOT DETERMINISTIC SQL SECURITY DEFINER COMMENT ''.
Digressions一些題外話轉(zhuǎn)載請(qǐng)注明翻譯者陳朋奕及轉(zhuǎn)自:www.phpv.net
Digression:
調(diào)用p2()//的結(jié)果
mysql> call p2() //
+--------------+-----------------+
| CURRENT_DATE | RAND() |
+--------------+-----------------+
| 2004-11-09 | 0.7822275075896 |
+--------------+-----------------+
1 row in set (0.26 sec)
Query OK, 0 rows affected (0.26 sec)
當(dāng)調(diào)用過(guò)程p2時(shí)，一個(gè)SELECT語(yǔ)句被執(zhí)行返回我們期望獲得的隨機(jī)數(shù)。
Digression: sql_mode unchanging
不會(huì)改變的
sql_mode
mysql> set sql_mode='ansi' //
mysql> create procedure p3()select'a'||'b'//
mysql> set sql_mode=''//
mysql> call p3()//
+------------+
| 'a' || 'b' |
+------------+
| ab |
+------------+
MySQL在過(guò)程創(chuàng)建時(shí)會(huì)自動(dòng)保持運(yùn)行環(huán)境。例如：我們需要使用兩條豎線來(lái)連接字符串但是這只有在sql mode為ansi的時(shí)候才合法。如果我們將sql mode改為non-ansi，不用擔(dān)心，它仍然能工作，只要它第一次使用時(shí)能正常工作。轉(zhuǎn)載請(qǐng)注明翻譯者陳朋奕及轉(zhuǎn)自:www.phpv.net
Exercise 練習(xí)
Question
問(wèn)題
如果你不介意練習(xí)一下的話，試能否不看后面的答案就能處理這些請(qǐng)求。
創(chuàng)建一個(gè)過(guò)程，顯示`Hello world`。用大約5秒時(shí)間去思考這個(gè)問(wèn)題，既然你已經(jīng)學(xué)到了這里，這個(gè)應(yīng)該很簡(jiǎn)單。當(dāng)你思考問(wèn)題的時(shí)候，我們?cè)匐S機(jī)選擇一些剛才講過(guò)的東西復(fù)習(xí)：
DETERMINISTIC
（確定性）子句是反映輸出和輸入依賴特性的子句…調(diào)用過(guò)程使用CALL過(guò)程名（參數(shù)列表）方式。好了，我猜時(shí)間也到了。
Answer
答案
好的，答案就是在過(guò)程體中包含
"SELECT 'Hello, world'"
語(yǔ)句
MySQL
mysql> CREATE PROCEDURE p4 () SELECT 'Hello, world' //
Query OK, 0 rows affected (0.00 sec)
mysql> CALL p4()//
+--------------+
| Hello, world |
+--------------+
| Hello, world |
+--------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Parameters 參數(shù)
讓我們更進(jìn)一步的研究怎么在存儲(chǔ)過(guò)程中定義參數(shù)
1. CREATE PROCEDURE p5
() ...
2. CREATE PROCEDURE p5
([IN] name data-type) ...
3. CREATE PROCEDURE p5
(OUT name data-type) ...
4. CREATE PROCEDURE p5
(INOUT name data-type) ...
回憶一下前面講過(guò)的參數(shù)列表必須在存儲(chǔ)過(guò)程名后的括號(hào)中。上面的第一個(gè)例子中的參數(shù)列表是空的，第二個(gè)例子中有一個(gè)輸入?yún)?shù)。這里的詞IN可選，因?yàn)槟J(rèn)參數(shù)為IN（input）。
第三個(gè)例子中有一個(gè)輸出參數(shù)，第四個(gè)例子中有一個(gè)參數(shù)，既能作為輸入也可以作為輸出。
IN example 輸入的例子
mysql> CREATE PROCEDURE p5(p INT) SET @x = p //
Query OK, 0 rows affected (0.00 sec)
mysql> CALL p5(12345)//
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @x//
+-------+
| @x |
+-------+
| 12345 |
+-------+
1 row in set (0.00 sec)
這個(gè)IN的例子演示的是有輸入?yún)?shù)的過(guò)程。在過(guò)程體中我將會(huì)話變量x設(shè)定為參數(shù)p的值。然后調(diào)用過(guò)程，將12345傳入?yún)?shù)p。選擇顯示會(huì)話變量@x，證明我們已經(jīng)將參數(shù)值12345傳入。
OUT example 輸出的例子
mysql> CREATE PROCEDURE p6 (OUT p INT)
-> SET p = -5 //
mysql> CALL p6(@y)//
mysql> SELECT @y//
+------+
| @y |
+------+
| -5 |
+------+
這是另一個(gè)例子。這次的p是輸出參數(shù)，然后在過(guò)程調(diào)用中將p的值傳入會(huì)話變量@y中。轉(zhuǎn)載請(qǐng)注明翻譯者陳朋奕及轉(zhuǎn)自:www.phpv.net
在過(guò)程體中，我們給參數(shù)賦值-5，在調(diào)用后我們可以看出，OUT是告訴DBMS值是從過(guò)程中傳出的。
同樣我們可以用語(yǔ)句
"SET @y = -5;".
來(lái)達(dá)到同樣的效果
Compound Statements 復(fù)合語(yǔ)句
現(xiàn)在我們展開(kāi)的詳細(xì)分析一下過(guò)程體：
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)用
*/
完 成過(guò)程體的構(gòu)造就是BEGIN/END塊。這個(gè)BEGIN/END語(yǔ)句塊和Pascal語(yǔ)言中的BEGIN/END是基本相同的，和C語(yǔ)言的框架是很相似 的。我們可以使用塊去封裝多條語(yǔ)句。在這個(gè)例子中，我們使用了多條設(shè)定會(huì)話變量的語(yǔ)句，然后完成了一些insert和select語(yǔ)句。如果你的過(guò)程體中 有多條語(yǔ)句，那么你就需要BEGIN/END塊了。BEGIN/END塊也被稱為復(fù)合語(yǔ)句，在這里你可以進(jìn)行變量定義和流程控制。
第三講:新SQL語(yǔ)句,Loops 循環(huán)語(yǔ)句
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 */
在過(guò)程中定義的變量并不是真正的定義，你只是在BEGIN/END塊內(nèi)定義了而已（譯注：也就是形參）。
注意這些變量和會(huì)話變量不一樣，不能使用修飾符@你必須清楚的在BEGIN/END塊中聲明變量和它們的類型。
變量一旦聲明，你就能在任何能使用會(huì)話變量、文字、列名的地方使用。
(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ò)程能正常工作
(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)在我們來(lái)討論一下作用域的問(wèn)題。例子中有嵌套 的BEGIN/END塊，當(dāng)然這是合法的。同時(shí)包含兩個(gè)變量，名字都 是x1，這樣也是合法的。內(nèi)部的變量在其作用域內(nèi)享有更高的優(yōu)先權(quán)。當(dāng)執(zhí)行到END語(yǔ)句時(shí)，內(nèi)部變量消失，此時(shí)已經(jīng)在其作用域外，變量不再可見(jiàn)了，因此在 存儲(chǔ)過(guò)程外再也不能找到這個(gè)聲明了的變量，但是你可以通過(guò)OUT參數(shù)或者將其值指派 給會(huì)話變量來(lái)保存其值。
調(diào)用作用域例子的過(guò)程：
mysql> CALL p11()//
+-------+
| x1 |
+-------+
| inner |
+-------+
+-------+
| x1 |
+-------+
| outer |
+-------+
我們看到的結(jié)果時(shí)第一個(gè)SELECT語(yǔ)句檢索到最內(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語(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)，在這里可以不寫分號(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)?，F(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
（迭代）語(yǔ)句和LEAVE語(yǔ)句一樣也是在循環(huán)內(nèi)部的循環(huán)引用，它有點(diǎn)像C語(yǔ)言中的“Continue”，同樣它可以出現(xiàn)在復(fù)合語(yǔ)句中，引用復(fù)合語(yǔ)句標(biāo)號(hào)，ITERATE（迭代）意思是重新開(kāi)始復(fù)合語(yǔ)句。
那我們啟動(dòng)并觀察下面這個(gè)循環(huán)，這是個(gè)需要迭代過(guò)程的循環(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)行起來(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ǔ)句的最后一步。
GOTO
CREATE PROCEDURE p...
BEGIN
...
LABEL label_name;
...
GOTO label_name;
...
END;
MySQL的存儲(chǔ)過(guò)程中可以使用GOTO語(yǔ)句。雖然這不是標(biāo)準(zhǔn)SQL語(yǔ)句，而且在這里建立標(biāo)號(hào)的方法也和慣例中的不一樣。由于為了和其他DBMS兼容，這個(gè)語(yǔ)句會(huì)慢被淘汰，所以我們?cè)贛ySQL
參考手冊(cè)中沒(méi)有提及。
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;//
上面例子中的語(yǔ)句包含了我們之前講的所有語(yǔ)法，包括參數(shù)列表，特性參數(shù)，BEGIN/END塊復(fù)合語(yǔ)句，變量聲明，
IF，WHILE，LOOP，REPEAT，LEAVE，ITERATE，GOTO。這是一個(gè)荒謬的存儲(chǔ)過(guò)程，我不會(huì)運(yùn)行它，因?yàn)槔锩嬗袩o(wú)盡的循環(huán)。但是里面的語(yǔ)法卻十分合法。這些是新的流程控制和變量聲明語(yǔ)句。下面我們將要接觸更多新的東西。
第一部分存儲(chǔ)過(guò)程:異常處理,游標(biāo),游標(biāo)的特性,安全措施
Error Handling 異常處理
好了，我們現(xiàn)在要講的是異常處理
1. Sample Problem: Log Of Failures 問(wèn)題樣例：故障記錄
當(dāng)INSERT失敗時(shí)，我希望能將其記錄在日志文件中我們用來(lái)展示出錯(cuò)處理的問(wèn)題樣例是很
普通的。我希望得到錯(cuò)誤的記錄。當(dāng)INSERT失敗時(shí)，我想在另一個(gè)文件中記下這些錯(cuò)誤的
信息，例如出錯(cuò)時(shí)間，出錯(cuò)原因等。我對(duì)插入特別感興趣的原因是它將違反外鍵關(guān)聯(lián)的約束
2. Sample Problem: Log Of Failures (2)
mysql> CREATE TABLE t2
s1 INT, PRIMARY KEY (s1))
engine=innodb;//
mysql> CREATE TABLE t3 (s1 INT, KEY (s1),
FOREIGN KEY (s1) REFERENCES t2 (s1))
engine=innodb;//
mysql> INSERT INTO t3 VALUES (5);//
...
ERROR 1216 (23000): Cannot add or update a child row: a foreign key
constraint fails（這里顯示的是系統(tǒng)的出錯(cuò)信息）
我開(kāi)始要?jiǎng)?chuàng)建一個(gè)主鍵表，以及一個(gè)外鍵表。我們使用的是InnoDB，因此外鍵關(guān)聯(lián)檢查是打
開(kāi)的。然后當(dāng)我向外鍵表中插入非主鍵表中的值時(shí)，動(dòng)作將會(huì)失敗。當(dāng)然這種條件下可以很
快找到錯(cuò)誤號(hào)1216。轉(zhuǎn)載請(qǐng)注明翻譯者陳朋奕及轉(zhuǎn)自:www.phpv.net
3. Sample Problem: Log Of Failures
CREATE TABLE error_log (error_message
CHAR(80))//
下一步就是建立一個(gè)在做插入動(dòng)作出錯(cuò)時(shí)存儲(chǔ)錯(cuò)誤的表。
4. Sample Problem: Log Of Errors
CREATE PROCEDURE p22 (parameter1 INT)
BEGIN
DECLARE EXIT HANDLER FOR 1216
INSERT INTO error_log VALUES
(CONCAT('Time: ',current_date,
'. Foreign Key Reference Failure For
Value = ',parameter1));
INSERT INTO t3 VALUES (parameter1);
END;//
上面就是我們的程序。這里的第一個(gè)語(yǔ)句DECLARE EXIT HANDLER是用來(lái)處理異常的。意
思是如果錯(cuò)誤1215發(fā)生了，這個(gè)程序?qū)?huì)在錯(cuò)誤記錄表中插入一行。EXIT意思是當(dāng)動(dòng)作成功提交后退出這個(gè)復(fù)合語(yǔ)句。轉(zhuǎn)載請(qǐng)注明翻譯者陳朋奕及轉(zhuǎn)自:www.phpv.net
5. Sample Problem: Log Of Errors
CALL p22 (5) //
調(diào)用這個(gè)存儲(chǔ)過(guò)程會(huì)失敗，這很正常，因?yàn)?值并沒(méi)有在主鍵表中出現(xiàn)。但是沒(méi)有錯(cuò)誤信息
返回因?yàn)槌鲥e(cuò)處理已經(jīng)包含在過(guò)程中了。t3表中沒(méi)有增加任何東西，但是error_log表中記錄
下了一些信息，這就告訴我們INSERT into table t3動(dòng)作失敗。
DECLARE HANDLER syntax 聲明異常處理的語(yǔ)法
DECLARE
{ EXIT | CONTINUE }
HANDLER FOR
{ error-number | { SQLSTATE error-string } | condition }
SQL statement
上面就是錯(cuò)誤處理的用法，也就是一段當(dāng)程序出錯(cuò)后自動(dòng)觸發(fā)的代碼。MySQL允許兩種處理器，
一種是EXIT處理，我們剛才所用的就是這種。另一種就是我們將要演示的，CONTINUE處理，
它跟EXIT處理類似，不同在于它執(zhí)行后，原主程序仍然繼續(xù)運(yùn)行，那么這個(gè)復(fù)合語(yǔ)句就沒(méi)有出
口了。
1. DECLARE CONTINUE HANDLER example CONTINUE處理例子
CREATE TABLE t4 (s1 int,primary key(s1));//
CREATE PROCEDURE p23 ()
BEGIN
DECLARE CONTINUE HANDLER
FOR SQLSTATE '23000' SET @x2 = 1;
SET @x = 1;
INSERT INTO t4 VALUES (1);
SET @x = 2;
INSERT INTO t4 VALUES (1);
SET @x = 3;
END;//
這是MySQL參考手冊(cè)上的CONTINUE處理的例子，這個(gè)例子十分好，所以我把它拷貝到這里。
通過(guò)這個(gè)例子我們可以看出CONTINUE處理是如何工作的。
2. DECLARE CONTINUE HANDLER聲明CONTINUE異常處理
CREATE TABLE t4 (s1 int,primary key(s1));//
CREATE PROCEDURE p23 ()
BEGIN
DECLARE CONTINUE HANDLER
FOR SQLSTATE '23000' SET @x2 = 1; <--
SET @x = 1;
INSERT INTO t4 VALUES (1);
SET @x = 2;
INSERT INTO t4 VALUES (1);
SET @x = 3;
END;//
這次我將為SQLSTATE值定義一個(gè)處理程序。還記得前面我們使用的MySQL錯(cuò)誤代碼1216嗎？
事實(shí)上這里的23000SQLSTATE是更常用的，當(dāng)外鍵約束出錯(cuò)或主鍵約束出錯(cuò)就被調(diào)用了。
3. DECLARE CONTINUE HANDLER
CREATE TABLE t4 (s1 int,primary key(s1));//
CREATE PROCEDURE p23 ()
BEGIN
DECLARE CONTINUE HANDLER
FOR SQLSTATE '23000' SET @x2 = 1;
SET @x = 1; <--
INSERT INTO t4 VALUES (1);
SET @x = 2;
INSERT INTO t4 VALUES (1);
SET @x = 3;
END;//
這個(gè)存儲(chǔ)過(guò)程的第一個(gè)執(zhí)行的語(yǔ)句是"SET @x = 1"。
4. DECLARE CONTINUE HANDLER example
CREATE TABLE t4 (s1 int,primary key(s1));//
CREATE PROCEDURE p23 ()
BEGIN
DECLARE CONTINUE HANDLER
FOR SQLSTATE '23000' SET @x2 = 1;
SET @x = 1;
INSERT INTO t4 VALUES (1);
SET @x = 2;
INSERT INTO t4 VALUES (1); <--
SET @x = 3;
END;//
運(yùn)行后值1被插入到主鍵表中。轉(zhuǎn)載請(qǐng)注明翻譯者陳朋奕及轉(zhuǎn)自:www.phpv.net
5. DECLARE CONTINUE HANDLER
CREATE TABLE t4 (s1 int,primary key(s1));//
CREATE PROCEDURE p23 ()
BEGIN
DECLARE CONTINUE HANDLER
FOR SQLSTATE '23000' SET @x2 = 1;
SET @x = 1;
INSERT INTO t4 VALUES (1);
SET @x = 2; <--
INSERT INTO t4 VALUES (1);
SET @x = 3;
END;//
然后@x的值變?yōu)?。
6. DECLARE CONTINUE HANDLER example
CREATE TABLE t4 (s1 int,primary key(s1));//
CREATE PROCEDURE p23 ()
BEGIN
DECLARE CONTINUE HANDLER
FOR SQLSTATE '23000' SET @x2 = 1;
SET @x = 1;
INSERT INTO t4 VALUES (1);
SET @x = 2;
INSERT INTO t4 VALUES (1); <--
SET @x = 3;
END;//
然后程序嘗試再次往主鍵表中插入數(shù)值，但失敗了，因?yàn)橹麈I有唯一性限制。
7. DECLARE CONTINUE HANDLER example
CREATE TABLE t4 (s1 int,primary key(s1));//
CREATE PROCEDURE p23 ()
BEGIN
DECLARE CONTINUE HANDLER
FOR SQLSTATE '23000' SET @x2 = 1; <--
SET @x = 1;
INSERT INTO t4 VALUES (1);
SET @x = 2;
INSERT INTO t4 VALUES (1);
SET @x = 3;
END;//
由于插入失敗，錯(cuò)誤處理程序被觸發(fā)，開(kāi)始進(jìn)行錯(cuò)誤處理。下一個(gè)執(zhí)行的語(yǔ)句是錯(cuò)誤處理的語(yǔ)
句，@x2被設(shè)為2。
8. DECLARE CONTINUE HANDLER example
CREATE TABLE t4 (s1 int,primary key(s1));//
CREATE PROCEDURE p23 ()
BEGIN
DECLARE CONTINUE HANDLER
FOR SQLSTATE '23000' SET @x2 = 1;
SET @x = 1;
INSERT INTO t4 VALUES (1);
SET @x = 2;
INSERT INTO t4 VALUES (1);
SET @x = 3; <--
END;//
到這里并沒(méi)有結(jié)束，因?yàn)檫@是CONTINUE異常處理。所以執(zhí)行返回到失敗的插入語(yǔ)句之后，
繼續(xù)執(zhí)行將@x設(shè)定為3動(dòng)作。轉(zhuǎn)載請(qǐng)注明翻譯者陳朋奕及轉(zhuǎn)自:www.phpv.net
9. DECLARE CONTINUE HANDLER example
mysql> CALL p23()//
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @x, @x2//
+------+------+
| @x | @x2 |
+------+------+
| 3 | 1 |
+------+------+
1 row in set (0.00 sec)
運(yùn)行過(guò)程后我們觀察@x的值，很確定的可以知道是3，觀察@x2的值，為1。從這里可以
判斷程序運(yùn)行無(wú)誤，完全按照我們的思路進(jìn)行。大家可以花點(diǎn)時(shí)間去調(diào)整錯(cuò)誤處理器，讓
檢查放在語(yǔ)句段的首部，而不是放在可能出現(xiàn)錯(cuò)誤的地方，雖然那樣看起來(lái)程序很紊亂，
跳來(lái)跳去的感覺(jué)。但是這樣的代碼很安全也很清楚。
1. DECLARE CONDITION
CREATE PROCEDURE p24 ()
BEGIN
DECLARE `Constraint Violation`
CONDITION FOR SQLSTATE '23000';
DECLARE EXIT HANDLER FOR
`Constraint Violation` ROLLBACK;
START TRANSACTION;
INSERT INTO t2 VALUES (1);
INSERT INTO t2 VALUES (1);
COMMIT;
END; //
這是另外一個(gè)錯(cuò)誤處理的例子，在前面的基礎(chǔ)上修改的。事實(shí)上你可給SQLSTATE或者錯(cuò)誤代碼其他的名字，你就可以在處理中使用自己定義的名字了。下面看它是怎么實(shí)現(xiàn)的：我把表t2
定義為InnoDB表，所以對(duì)這個(gè)表的插入操作都會(huì)ROLLBACK（回滾），ROLLBACK（回滾事務(wù)）也是恰好會(huì)發(fā)生的。因?yàn)閷?duì)主鍵插入兩個(gè)同樣的值會(huì)導(dǎo)致SQLSTATE 23000錯(cuò)誤發(fā)生，這里SQLSTATE 23000是約束錯(cuò)誤。
2. DECLARE CONDITION聲明條件
CREATE PROCEDURE p24 ()
BEGIN
DECLARE `Constraint Violation`
CONDITION FOR SQLSTATE '23000';
DECLARE EXIT HANDLER FOR
`Constraint Violation` ROLLBACK;
START TRANSACTION;
INSERT INTO t2 VALUES (1);
INSERT INTO t2 VALUES (1);
COMMIT;
END; //
這個(gè)約束錯(cuò)誤會(huì)導(dǎo)致ROLLBACK（回滾事務(wù)）和SQLSTATE 23000錯(cuò)誤發(fā)生。
3. DECLARE CONDITION
mysql> CALL p24()//
Query OK, 0 rows affected (0.28 sec)
mysql> SELECT * FROM t2//
Empty set (0.00 sec)
我們調(diào)用這個(gè)存儲(chǔ)過(guò)程看結(jié)果是什么，從上面結(jié)果我們看到表t2沒(méi)有插入任何記錄。全部事務(wù)都回滾了。這正是我們想要的。
4. DECLARE CONDITION
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;//
程序第二步聲明了游標(biāo)cur_1，如果你使用過(guò)嵌入式SQL的話，就知道這和嵌入式SQL差不多。
4. 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;//
最后進(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;//
這里第一個(gè)FETCH語(yǔ)句會(huì)獲得一行從SELECT產(chǎn)生的結(jié)果集中檢索出來(lái)的值，然而表t中有多
行，因此這個(gè)語(yǔ)句會(huì)被執(zhí)行多次，當(dāng)然這是因?yàn)檎Z(yǔ)句在循環(huán)塊內(nèi)。
7. 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;//
最后當(dāng)MySQL的FETCH沒(méi)有獲得行時(shí)，CONTINUE處理被觸發(fā)，將變量b賦值為1。
8. 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;//
到了這一步UNTIL b=1條件就為真，循環(huán)結(jié)束。在這里我們可以自己編寫代碼關(guān)閉游標(biāo)，
也可以由系統(tǒng)執(zhí)行，系統(tǒng)會(huì)在復(fù)合語(yǔ)句結(jié)束時(shí)自動(dòng)關(guān)閉游標(biāo)，但是最好不要太依賴系統(tǒng)的
自動(dòng)關(guān)閉行為（譯注：這可能跟Java的Gc一樣，不可信）。
9. 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è)例程中我們?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;//
mysql> CALL p25(@return_val)//
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @return_val//
+-------------+
| @return_val |
+-------------+
| 5 |
+-------------+
1 row in set (0.00 sec)
上面是過(guò)程調(diào)用后的結(jié)果?？梢钥吹絩eturn_val參數(shù)獲得了數(shù)值5，因?yàn)檫@是表t的最后一行。
由此可以知道游標(biāo)工作正常，出錯(cuò)處理也工作正常。
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
這里我們要討論一些關(guān)于特權(quán)和安全相關(guān)的問(wèn)題。但因?yàn)樵贛ySQL安全措施的功能并沒(méi)有完全，所以我們不會(huì)對(duì)其進(jìn)行過(guò)多討論。
1. Privileges CREATE ROUTINE
GRANT CREATE ROUTINE
ON database-name . *
TO user(s)
[WITH GRANT OPTION];
現(xiàn)在用root就可以了
在這里要介紹的特權(quán)是CREATE ROUTINE，它不僅同其他特權(quán)一樣可以創(chuàng)建存儲(chǔ)過(guò)程和函數(shù)，
還可以創(chuàng)建視圖和表。Root用戶擁有這種特權(quán)，同時(shí)還有ALTER ROUTINE特權(quán)。
2. Privileges EXECUTE
GRANT EXECUTE ON p TO peter
[WITH GRANT OPTION];
上面的特權(quán)是決定你是否可以使用或執(zhí)行存儲(chǔ)過(guò)程的特權(quán)，過(guò)程創(chuàng)建者默認(rèn)擁有這個(gè)特權(quán)。
3. Privileges SHOW ROUTINE?
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)的，在寫本書(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; //
現(xiàn)在我們測(cè)試一下SQL SECURITY子句吧。Security是我們前面提到的程序特性的一部分。你root
用戶，將插入權(quán)賦給了peter。然后使用peter登陸進(jìn)行新的工作，我們看peter可以怎么使用存儲(chǔ)過(guò)程，注意：peter沒(méi)有對(duì)表t的select權(quán)力，只有root用戶有。
5. Privileges Invokers and Definers
/* Logged on with current_user = peter */使用帳戶peter登陸
mysql> CALL p26();
ERROR 1142 (42000): select command denied to user
'peter'@'localhost' for table 't'
mysql> CALL p27();
+----------+
| COUNT(*) |
+----------+
| 1 |
+----------+
1 row in set (0.00 sec)
當(dāng)peter嘗試調(diào)用含有調(diào)用保密措施的過(guò)程p26時(shí)會(huì)失敗。那是因?yàn)閜eter沒(méi)有對(duì)表的select的權(quán)力。
但是當(dāng)petre調(diào)用含有定義保密措施的過(guò)程時(shí)就能成功。原因是root有select權(quán)力，Peter有root的
權(quán)力，因此過(guò)程可以執(zhí)行。
MySQL 5.0 觸發(fā)器
Introduction
本書(shū)是為需要了解5.0版本新特性的MySQL老用戶而寫的。簡(jiǎn)單的來(lái)說(shuō)介紹了 "存儲(chǔ)過(guò)程、觸發(fā)器、視圖、信息架構(gòu)視圖"，這是介紹MySQL 5.0新特性叢書(shū)的第一集。希望這本書(shū)能像內(nèi)行專家那樣與您進(jìn)行對(duì)話，用簡(jiǎn)單的問(wèn)題、例子讓你學(xué)到需要的知識(shí)。為了達(dá)到這樣的目的，我會(huì)從每一個(gè)細(xì)節(jié)開(kāi)始 慢的為大家建立概念，最后會(huì)給大家展示較大的實(shí)用例，在學(xué)習(xí)之前也許大家會(huì)認(rèn)為這個(gè)用例很難，但是只要跟著課程去學(xué)，相信很快就能掌握。
Conventions and Styles約定和編程風(fēng)格
每次我想要演示實(shí)際代碼時(shí)，我會(huì)對(duì)mysql客戶端的屏幕就出現(xiàn)的代碼進(jìn)行調(diào)整，將字體改成Courier，使他們看起來(lái)與普通文本不一樣（讓大家區(qū)別程序代碼和正文）。在這里舉個(gè)例子：
mysql> DROP FUNCTION f;
Query OK, 0 rows affected (0.00 sec)
如果實(shí)例比較大，則需要在某些行和段落間加注釋，同時(shí)我會(huì)用將"<--"符號(hào)放在頁(yè)面的右邊以表示強(qiáng)調(diào)。例如：
mysql> CREATE PROCEDURE p ()
-> BEGIN
-> /* This procedure does nothing */ <--
-> END;//
Query OK, 0 rows affected (0.00 sec)
有時(shí)候我會(huì)
Conventions and Styles 約定和編程風(fēng)格
每次我想要演示實(shí)際代碼時(shí)，我會(huì)對(duì)mysql客戶端的屏幕就出現(xiàn)的代碼進(jìn)行調(diào)整，將字體改成Courier，使他們看起來(lái)與普通文本不一樣。
在這里舉個(gè)例子：mysql> DROP FUNCTION f;Query OK, 0 rows affected (0.00 sec)
如果實(shí)例比較大，則需要在某些行和段落間加注釋，同時(shí)我會(huì)用將“<--”符號(hào)放在頁(yè)面的右邊以表示強(qiáng)調(diào)。
例如：
mysql> CREATE PROCEDURE p ()
-> BEGIN
-> /* This procedure does nothing */ <--
-> END;//Query OK, 0 rows affected (0.00 sec)
有時(shí)候我會(huì)將例子中的"mysql>"和"->"這些系統(tǒng)顯示去 掉，你可以直接將代碼復(fù)制到mysql客戶端程序中（如果你現(xiàn)在所讀的 不是電子版的，可以在mysql.com網(wǎng)站下載相關(guān)腳本）所以的例子都已經(jīng)在Suse 9.2 Linux、Mysql 5.0.3公共版上測(cè)試通過(guò)。
在您閱讀本書(shū)的時(shí)候，Mysql已經(jīng)有更高的版本，同時(shí)能支持更多OS了，包括Windows，Sparc，HP-UX。因此這里的例子將能正常的運(yùn)行在您的電腦上。但如果運(yùn)行仍然出現(xiàn)故障，可以咨詢你認(rèn)識(shí)的資深Mysql用戶，以得到長(zhǎng)久的支持和幫助。
A Definition and an Example 定義及實(shí)例
定義及實(shí)例存儲(chǔ)過(guò)程是一種存儲(chǔ)在書(shū)庫(kù)中的程序（就像正規(guī)語(yǔ)言里的子程序一樣）， 準(zhǔn)確的來(lái)說(shuō)，MySQL支持的“routines（例程）”有兩種： 一是我們說(shuō)的存儲(chǔ)過(guò)程，二是在其他SQL語(yǔ)句中可以返回值的函數(shù)（使用起來(lái)和Mysql預(yù)裝載的函數(shù)一樣，如pi()）。我在本書(shū)里面會(huì)更經(jīng)常使用存儲(chǔ)過(guò) 程，因?yàn)檫@是我們過(guò)去的習(xí)慣，相信大家也會(huì)接受。
一個(gè)存儲(chǔ)過(guò)程包括名字，參數(shù)列表，以及可以包括很多SQL語(yǔ)句的SQL語(yǔ)句集。
在這里對(duì)局部變量，異常處理，循環(huán)控制和IF條件句有新的語(yǔ)法定義。
下面是一個(gè)包括存儲(chǔ)過(guò)程的實(shí)例聲明：（譯注：為了方便閱讀，此后的程序不添任何中文注釋）
CREATE PROCEDURE procedure1 /* name存儲(chǔ)過(guò)程名*/
(IN parameter1 INTEGER) /* parameters參數(shù)*/
BEGIN /* start of block語(yǔ)句塊頭*/
DECLARE variable1 CHAR(10); /* variables變量聲明*/
IF parameter1 = 17 THEN /* start of IF IF條件開(kāi)始*/
SET variable1 = 'birds'; /* assignment賦值*/
ELSE
SET variable1 = 'beasts'; /* assignment賦值*/
END IF; /* end of IF IF結(jié)束*/
INSERT INTO table1 VALUES (variable1);/* statement SQL語(yǔ)句*/
END /* end of block語(yǔ)句塊結(jié)束*/
下面我將會(huì)介紹你可以利用存儲(chǔ)過(guò)程做的工作的所有細(xì)節(jié)。同時(shí)我們將介紹新的數(shù)據(jù)庫(kù)對(duì)象—觸發(fā)器，因?yàn)橛|發(fā)器和存儲(chǔ)過(guò)程的關(guān)聯(lián)是必然的。
Why Stored Procedures 為什么要用存儲(chǔ)過(guò)程
由于存儲(chǔ)過(guò)程對(duì)于MySQL來(lái)說(shuō)是新的功能，很自然的在使用時(shí)你需要更加注意。
畢竟，在此之前沒(méi)有任何人使用過(guò)，也沒(méi)有很多大量的有經(jīng)驗(yàn)的用戶來(lái)帶你走他們走過(guò)的路。然而你應(yīng)該開(kāi)始考慮把現(xiàn)有程序（可能在服務(wù)器應(yīng)用程序中，用戶自定義函數(shù)（UDF）中，或是腳本中）轉(zhuǎn)移到存儲(chǔ)過(guò)程中來(lái)。這樣做不需要原因，你不得不去做。
因?yàn)榇鎯?chǔ)過(guò)程是已經(jīng)被認(rèn)證的技術(shù)！雖然在Mysql中它是新的，但是相同功能的函數(shù)在其他DBMS中早已存在，而它們的語(yǔ)法往是相同的。因此你可以從其他人那里獲得這些概念，也有很多你可以咨詢或者雇用的經(jīng)驗(yàn)用戶，還有許多第三方的文檔可供你閱讀。
存 儲(chǔ)過(guò)程會(huì)使系統(tǒng)運(yùn)行更快！雖然我們暫時(shí)不能在Mysql上證明這個(gè)優(yōu)勢(shì)，用戶得到的體驗(yàn)也不一樣。我們可以說(shuō)的就是Mysql服務(wù)器在緩存機(jī)制上做了改 進(jìn)，就像Preparedstatements（預(yù)處理語(yǔ)句）所做的那樣。由于沒(méi)有編譯器，因此SQL存儲(chǔ)過(guò)程不會(huì)像外部語(yǔ)言（如C）編寫的程序運(yùn)行起來(lái) 那么快。但是提升速度的主要方法卻在于能否降低網(wǎng)絡(luò)信息流量。如果你需要處理的是需要檢查、循環(huán)、多語(yǔ)句但沒(méi)有用戶交互的重復(fù)性任務(wù)，你就可以使用保存在 服務(wù)器上的存儲(chǔ)過(guò)程來(lái)完成。這樣在執(zhí)行任務(wù)的每一步時(shí)服務(wù)器和客戶端之間就沒(méi)那么多的信息來(lái)往了。轉(zhuǎn)載請(qǐng)注明翻譯者陳朋奕及轉(zhuǎn)自:www.phpv.net
所 以存儲(chǔ)過(guò)程是可復(fù)用的組件！想象一下如果你改變了主機(jī)的語(yǔ)言，這對(duì)存儲(chǔ)過(guò)程不會(huì)產(chǎn)生影響，因?yàn)樗菙?shù)據(jù)庫(kù)邏輯而不是應(yīng)用程序。存儲(chǔ)過(guò)程是可以移植的！當(dāng)你 用SQL編寫存儲(chǔ)過(guò)程時(shí)，你就知道它可以運(yùn)行在Mysql支持的任何平臺(tái)上，不需要你額外添加運(yùn)行環(huán)境包，也不需要為程序在操作系統(tǒng)中執(zhí)行設(shè)置許可，或者 為你的不同型號(hào)的電腦存儲(chǔ)過(guò)程將被保存！如果你編寫好了一個(gè)程序，例如顯示銀行事物處理中的支票撤消，那想要了解支票的人就可以找到你的程序。
它會(huì)以源代碼的形式保存在數(shù)據(jù)庫(kù)中。這將使數(shù)據(jù)和處理數(shù)據(jù)的進(jìn)程有意義的關(guān)聯(lián)這可能跟你在課上聽(tīng)到的規(guī)劃論中說(shuō)的一樣。存儲(chǔ)過(guò)程可以遷移！
Mysql完全支持SQL 2003標(biāo)準(zhǔn)。某些數(shù)據(jù)庫(kù)（如DB2、Mimer）同樣支持。但也有部分不支持的，如Oracle、SQL Server不支持。我們將會(huì)給予足夠幫助和工具，使為其他DBMS編寫的代碼能更容易轉(zhuǎn)移到Mysql上。
Setting up with MySQL 5.0 設(shè)置并開(kāi)始MySQL 5.0服務(wù)
通過(guò)
mysql_fix_privilege_tables
或者
~/mysql-5.0/scripts/mysql_install_db
來(lái)開(kāi)始MySQL服務(wù)
作為我們練習(xí)的準(zhǔn)備工作的一部分，我假定MySQL 5.0已經(jīng)安裝。如果沒(méi)有數(shù)據(jù)庫(kù)管理員為你安裝好數(shù)據(jù)庫(kù)以及其他軟件，你就需要自己去安裝了。不過(guò)你很容易忘掉一件事，那就是你需要有一個(gè)名為mysql.proc的表。
在安裝了最新版本后，你必須運(yùn)行
mysql_fix_privilege_tables
或者
mysql_install_db
（只需要運(yùn)行其中一個(gè)就夠了）——不然存儲(chǔ)過(guò)程將不能工作。我同時(shí)啟用在root身份后運(yùn)行一個(gè)非正式的SQL腳本，如下：
mysql>source/home/pgulutzan/mysql-5.0/scripts/mysql_prepare_privilege_tables_for_5.sql
Starting the MySQL Client 啟動(dòng)MySQL客戶端
這是我啟動(dòng)mysql客戶端的方式。你也許會(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客戶端返回的結(jié)果，這樣意味著我有極大的特權(quán)。轉(zhuǎn)載請(qǐng)注明翻譯者陳朋奕及轉(zhuǎn)自:www.phpv.net
Check for the Correct Version 核對(duì)版本
為了確認(rèn)使用的MySQL的版本是正確的，我們要查詢版本。我有兩種方法確認(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è)客戶端上正常工作。
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)樵谶@里并不需要展示查詢數(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客戶端你已經(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ì)大小寫不敏感，因此‘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í)可以不寫這個(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ǔ)句的功能稱為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//"
，但是類似
"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)存在的子句。那樣在寫復(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 //
這里我給出的是一些能反映存儲(chǔ)過(guò)程特性的子句。子句內(nèi)容在括號(hào)之后，主體之前。這些子句都是可選的，他們有什么作用呢？
2.
CREATE PROCEDURE p2 ()
LANGUAGE SQL <--
NOT DETERMINISTIC
SQL SECURITY DEFINER
COMMENT 'A Procedure'
SELECT CURRENT_DATE, RAND() FROM t //
很 好，這個(gè)LANGUAGE SQL子句是沒(méi)有作用的。僅是為了說(shuō)明下面過(guò)程的主體使用SQL語(yǔ)言編寫。這條是系統(tǒng)默認(rèn)的，但你在這里聲明是有用的，因?yàn)槟承〥BMS（IBM的 DB2）需要它，如果你關(guān)注DB2的兼容問(wèn)題最好還是用上。此外，今后可能會(huì)出現(xiàn)除SQL外的其他語(yǔ)言支持的存儲(chǔ)過(guò)程。
3.
CREATE PROCEDURE p2 ()
LANGUAGE SQL
NOT DETERMINISTIC <--
SQL SECURITY DEFINER
COMMENT 'A Procedure'
SELECT CURRENT_DATE, RAND() FROM t //
下 一個(gè)子句，NOT DETERMINISTIC，是傳遞給系統(tǒng)的信息。這里一個(gè)確定過(guò)程的定義就是那些每次輸入一樣輸出也一樣的程序。在這個(gè)案例中，既然主體中含有 SELECT語(yǔ)句，那返回肯定是未知的因此我們稱其NOT DETERMINISTIC。但是MySQL內(nèi)置的優(yōu)化程序不會(huì)注意這個(gè)，至少在現(xiàn)在不注意。轉(zhuǎn)載請(qǐng)注明翻譯者陳朋奕及轉(zhuǎn)自:www.phpv.net
4.
CREATE PROCEDURE p2 ()
LANGUAGE SQL
NOT DETERMINISTIC
SQL SECURITY DEFINER <--
COMMENT 'A Procedure'
SELECT CURRENT_DATE, RAND() FROM t //
下一個(gè)子句是SQL SECURITY，可以定義為SQL SECURITY DEFINER或SQL SECURITY INVOKER。
這就進(jìn)入了權(quán)限控制的領(lǐng)域了，當(dāng)然我們?cè)诤竺鎸?huì)有測(cè)試權(quán)限的例子。
SQL SECURITY DEFINER
意味著在調(diào)用時(shí)檢查創(chuàng)建過(guò)程用戶的權(quán)限（另一個(gè)選項(xiàng)是SQLSECURITY INVOKER）。
現(xiàn)在而言，使用
SQL SECURITY DEFINER
指令告訴MySQL服務(wù)器檢查創(chuàng)建過(guò)程的用戶就可以了，當(dāng)過(guò)程已經(jīng)被調(diào)用，就不檢查執(zhí)行調(diào)用過(guò)程的用戶了。而另一個(gè)選項(xiàng)（INVOKER）
則是告訴服務(wù)器在這一步仍然要檢查調(diào)用者的權(quán)限。轉(zhuǎn)載請(qǐng)注明翻譯者陳朋奕及轉(zhuǎn)自:www.phpv.net
5.
CREATE PROCEDURE p2 ()
LANGUAGE SQL
NOT DETERMINISTIC
SQL SECURITY DEFINER
COMMENT 'A Procedure' <--
SELECT CURRENT_DATE, RAND() FROM t //
COMMENT 'A procedure'
是一個(gè)可選的注釋說(shuō)明。
最后，注釋子句會(huì)跟過(guò)程定義存儲(chǔ)在一起。這個(gè)沒(méi)有固定的標(biāo)準(zhǔn)，我在文中會(huì)指出沒(méi)有固定標(biāo)準(zhǔn)的語(yǔ)句，不過(guò)幸運(yùn)的是這些在我們標(biāo)準(zhǔn)的SQL中很少。
6.
CREATE PROCEDURE p2 ()
LANGUAGE SQL
NOT DETERMINISTIC
SQL SECURITY DEFINER
COMMENT ''
SELECT CURRENT_DATE, RAND() FROM t //
上面過(guò)程跟下面語(yǔ)句是等效的：
CREATE PROCEDURE p2 ()
SELECT CURRENT_DATE, RAND() FROM t //
特征子句也有默認(rèn)值，如果省略了就相當(dāng)于：
LANGUAGE SQL NOT DETERMINISTIC SQL SECURITY DEFINER COMMENT ''.
Digressions一些題外話轉(zhuǎn)載請(qǐng)注明翻譯者陳朋奕及轉(zhuǎn)自:www.phpv.net
Digression:
調(diào)用p2()//的結(jié)果
mysql> call p2() //
+--------------+-----------------+
| CURRENT_DATE | RAND() |
+--------------+-----------------+
| 2004-11-09 | 0.7822275075896 |
+--------------+-----------------+
1 row in set (0.26 sec)
Query OK, 0 rows affected (0.26 sec)
當(dāng)調(diào)用過(guò)程p2時(shí)，一個(gè)SELECT語(yǔ)句被執(zhí)行返回我們期望獲得的隨機(jī)數(shù)。
Digression: sql_mode unchanging
不會(huì)改變的
sql_mode
mysql> set sql_mode='ansi' //
mysql> create procedure p3()select'a'||'b'//
mysql> set sql_mode=''//
mysql> call p3()//
+------------+
| 'a' || 'b' |
+------------+
| ab |
+------------+
MySQL在過(guò)程創(chuàng)建時(shí)會(huì)自動(dòng)保持運(yùn)行環(huán)境。例如：我們需要使用兩條豎線來(lái)連接字符串但是這只有在sql mode為ansi的時(shí)候才合法。如果我們將sql mode改為non-ansi，不用擔(dān)心，它仍然能工作，只要它第一次使用時(shí)能正常工作。轉(zhuǎn)載請(qǐng)注明翻譯者陳朋奕及轉(zhuǎn)自:www.phpv.net
Exercise 練習(xí)
Question
問(wèn)題
如果你不介意練習(xí)一下的話，試能否不看后面的答案就能處理這些請(qǐng)求。
創(chuàng)建一個(gè)過(guò)程，顯示`Hello world`。用大約5秒時(shí)間去思考這個(gè)問(wèn)題，既然你已經(jīng)學(xué)到了這里，這個(gè)應(yīng)該很簡(jiǎn)單。當(dāng)你思考問(wèn)題的時(shí)候，我們?cè)匐S機(jī)選擇一些剛才講過(guò)的東西復(fù)習(xí)：
DETERMINISTIC
（確定性）子句是反映輸出和輸入依賴特性的子句…調(diào)用過(guò)程使用CALL過(guò)程名（參數(shù)列表）方式。好了，我猜時(shí)間也到了。
Answer
答案
好的，答案就是在過(guò)程體中包含
"SELECT 'Hello, world'"
語(yǔ)句
MySQL
mysql> CREATE PROCEDURE p4 () SELECT 'Hello, world' //
Query OK, 0 rows affected (0.00 sec)
mysql> CALL p4()//
+--------------+
| Hello, world |
+--------------+
| Hello, world |
+--------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Parameters 參數(shù)
讓我們更進(jìn)一步的研究怎么在存儲(chǔ)過(guò)程中定義參數(shù)
1. CREATE PROCEDURE p5
() ...
2. CREATE PROCEDURE p5
([IN] name data-type) ...
3. CREATE PROCEDURE p5
(OUT name data-type) ...
4. CREATE PROCEDURE p5
(INOUT name data-type) ...
回憶一下前面講過(guò)的參數(shù)列表必須在存儲(chǔ)過(guò)程名后的括號(hào)中。上面的第一個(gè)例子中的參數(shù)列表是空的，第二個(gè)例子中有一個(gè)輸入?yún)?shù)。這里的詞IN可選，因?yàn)槟J(rèn)參數(shù)為IN（input）。
第三個(gè)例子中有一個(gè)輸出參數(shù)，第四個(gè)例子中有一個(gè)參數(shù)，既能作為輸入也可以作為輸出。
IN example 輸入的例子
mysql> CREATE PROCEDURE p5(p INT) SET @x = p //
Query OK, 0 rows affected (0.00 sec)
mysql> CALL p5(12345)//
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @x//
+-------+
| @x |
+-------+
| 12345 |
+-------+
1 row in set (0.00 sec)
這個(gè)IN的例子演示的是有輸入?yún)?shù)的過(guò)程。在過(guò)程體中我將會(huì)話變量x設(shè)定為參數(shù)p的值。然后調(diào)用過(guò)程，將12345傳入?yún)?shù)p。選擇顯示會(huì)話變量@x，證明我們已經(jīng)將參數(shù)值12345傳入。
OUT example 輸出的例子
mysql> CREATE PROCEDURE p6 (OUT p INT)
-> SET p = -5 //
mysql> CALL p6(@y)//
mysql> SELECT @y//
+------+
| @y |
+------+
| -5 |
+------+
這是另一個(gè)例子。這次的p是輸出參數(shù)，然后在過(guò)程調(diào)用中將p的值傳入會(huì)話變量@y中。轉(zhuǎn)載請(qǐng)注明翻譯者陳朋奕及轉(zhuǎn)自:www.phpv.net
在過(guò)程體中，我們給參數(shù)賦值-5，在調(diào)用后我們可以看出，OUT是告訴DBMS值是從過(guò)程中傳出的。
同樣我們可以用語(yǔ)句
"SET @y = -5;".
來(lái)達(dá)到同樣的效果
Compound Statements 復(fù)合語(yǔ)句
現(xiàn)在我們展開(kāi)的詳細(xì)分析一下過(guò)程體：
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)用
*/
完 成過(guò)程體的構(gòu)造就是BEGIN/END塊。這個(gè)BEGIN/END語(yǔ)句塊和Pascal語(yǔ)言中的BEGIN/END是基本相同的，和C語(yǔ)言的框架是很相似 的。我們可以使用塊去封裝多條語(yǔ)句。在這個(gè)例子中，我們使用了多條設(shè)定會(huì)話變量的語(yǔ)句，然后完成了一些insert和select語(yǔ)句。如果你的過(guò)程體中 有多條語(yǔ)句，那么你就需要BEGIN/END塊了。BEGIN/END塊也被稱為復(fù)合語(yǔ)句，在這里你可以進(jìn)行變量定義和流程控制。
第三講:新SQL語(yǔ)句,Loops 循環(huán)語(yǔ)句
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 */
在過(guò)程中定義的變量并不是真正的定義，你只是在BEGIN/END塊內(nèi)定義了而已（譯注：也就是形參）。
注意這些變量和會(huì)話變量不一樣，不能使用修飾符@你必須清楚的在BEGIN/END塊中聲明變量和它們的類型。
變量一旦聲明，你就能在任何能使用會(huì)話變量、文字、列名的地方使用。
(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ò)程能正常工作
(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)在我們來(lái)討論一下作用域的問(wèn)題。例子中有嵌套 的BEGIN/END塊，當(dāng)然這是合法的。同時(shí)包含兩個(gè)變量，名字都 是x1，這樣也是合法的。內(nèi)部的變量在其作用域內(nèi)享有更高的優(yōu)先權(quán)。當(dāng)執(zhí)行到END語(yǔ)句時(shí)，內(nèi)部變量消失，此時(shí)已經(jīng)在其作用域外，變量不再可見(jiàn)了，因此在 存儲(chǔ)過(guò)程外再也不能找到這個(gè)聲明了的變量，但是你可以通過(guò)OUT參數(shù)或者將其值指派 給會(huì)話變量來(lái)保存其值。
調(diào)用作用域例子的過(guò)程：
mysql> CALL p11()//
+-------+
| x1 |
+-------+
| inner |
+-------+
+-------+
| x1 |
+-------+
| outer |
+-------+
我們看到的結(jié)果時(shí)第一個(gè)SELECT語(yǔ)句檢索到最內(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語(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)，在這里可以不寫分號(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)?，F(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
（迭代）語(yǔ)句和LEAVE語(yǔ)句一樣也是在循環(huán)內(nèi)部的循環(huán)引用，它有點(diǎn)像C語(yǔ)言中的“Continue”，同樣它可以出現(xiàn)在復(fù)合語(yǔ)句中，引用復(fù)合語(yǔ)句標(biāo)號(hào)，ITERATE（迭代）意思是重新開(kāi)始復(fù)合語(yǔ)句。
那我們啟動(dòng)并觀察下面這個(gè)循環(huán)，這是個(gè)需要迭代過(guò)程的循環(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)行起來(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ǔ)句的最后一步。
GOTO
CREATE PROCEDURE p...
BEGIN
...
LABEL label_name;
...
GOTO label_name;
...
END;
MySQL的存儲(chǔ)過(guò)程中可以使用GOTO語(yǔ)句。雖然這不是標(biāo)準(zhǔn)SQL語(yǔ)句，而且在這里建立標(biāo)號(hào)的方法也和慣例中的不一樣。由于為了和其他DBMS兼容，這個(gè)語(yǔ)句會(huì)慢被淘汰，所以我們?cè)贛ySQL
參考手冊(cè)中沒(méi)有提及。
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;//
上面例子中的語(yǔ)句包含了我們之前講的所有語(yǔ)法，包括參數(shù)列表，特性參數(shù)，BEGIN/END塊復(fù)合語(yǔ)句，變量聲明，
IF，WHILE，LOOP，REPEAT，LEAVE，ITERATE，GOTO。這是一個(gè)荒謬的存儲(chǔ)過(guò)程，我不會(huì)運(yùn)行它，因?yàn)槔锩嬗袩o(wú)盡的循環(huán)。但是里面的語(yǔ)法卻十分合法。這些是新的流程控制和變量聲明語(yǔ)句。下面我們將要接觸更多新的東西。
第一部分存儲(chǔ)過(guò)程:異常處理,游標(biāo),游標(biāo)的特性,安全措施
Error Handling 異常處理
好了，我們現(xiàn)在要講的是異常處理
1. Sample Problem: Log Of Failures 問(wèn)題樣例：故障記錄
當(dāng)INSERT失敗時(shí)，我希望能將其記錄在日志文件中我們用來(lái)展示出錯(cuò)處理的問(wèn)題樣例是很
普通的。我希望得到錯(cuò)誤的記錄。當(dāng)INSERT失敗時(shí)，我想在另一個(gè)文件中記下這些錯(cuò)誤的
信息，例如出錯(cuò)時(shí)間，出錯(cuò)原因等。我對(duì)插入特別感興趣的原因是它將違反外鍵關(guān)聯(lián)的約束
2. Sample Problem: Log Of Failures (2)
mysql> CREATE TABLE t2
s1 INT, PRIMARY KEY (s1))
engine=innodb;//
mysql> CREATE TABLE t3 (s1 INT, KEY (s1),
FOREIGN KEY (s1) REFERENCES t2 (s1))
engine=innodb;//
mysql> INSERT INTO t3 VALUES (5);//
...
ERROR 1216 (23000): Cannot add or update a child row: a foreign key
constraint fails（這里顯示的是系統(tǒng)的出錯(cuò)信息）
我開(kāi)始要?jiǎng)?chuàng)建一個(gè)主鍵表，以及一個(gè)外鍵表。我們使用的是InnoDB，因此外鍵關(guān)聯(lián)檢查是打
開(kāi)的。然后當(dāng)我向外鍵表中插入非主鍵表中的值時(shí)，動(dòng)作將會(huì)失敗。當(dāng)然這種條件下可以很
快找到錯(cuò)誤號(hào)1216。轉(zhuǎn)載請(qǐng)注明翻譯者陳朋奕及轉(zhuǎn)自:www.phpv.net
3. Sample Problem: Log Of Failures
CREATE TABLE error_log (error_message
CHAR(80))//
下一步就是建立一個(gè)在做插入動(dòng)作出錯(cuò)時(shí)存儲(chǔ)錯(cuò)誤的表。
4. Sample Problem: Log Of Errors
CREATE PROCEDURE p22 (parameter1 INT)
BEGIN
DECLARE EXIT HANDLER FOR 1216
INSERT INTO error_log VALUES
(CONCAT('Time: ',current_date,
'. Foreign Key Reference Failure For
Value = ',parameter1));
INSERT INTO t3 VALUES (parameter1);
END;//
上面就是我們的程序。這里的第一個(gè)語(yǔ)句DECLARE EXIT HANDLER是用來(lái)處理異常的。意
思是如果錯(cuò)誤1215發(fā)生了，這個(gè)程序?qū)?huì)在錯(cuò)誤記錄表中插入一行。EXIT意思是當(dāng)動(dòng)作成功提交后退出這個(gè)復(fù)合語(yǔ)句。轉(zhuǎn)載請(qǐng)注明翻譯者陳朋奕及轉(zhuǎn)自:www.phpv.net
5. Sample Problem: Log Of Errors
CALL p22 (5) //
調(diào)用這個(gè)存儲(chǔ)過(guò)程會(huì)失敗，這很正常，因?yàn)?值并沒(méi)有在主鍵表中出現(xiàn)。但是沒(méi)有錯(cuò)誤信息
返回因?yàn)槌鲥e(cuò)處理已經(jīng)包含在過(guò)程中了。t3表中沒(méi)有增加任何東西，但是error_log表中記錄
下了一些信息，這就告訴我們INSERT into table t3動(dòng)作失敗。
DECLARE HANDLER syntax 聲明異常處理的語(yǔ)法
DECLARE
{ EXIT | CONTINUE }
HANDLER FOR
{ error-number | { SQLSTATE error-string } | condition }
SQL statement
上面就是錯(cuò)誤處理的用法，也就是一段當(dāng)程序出錯(cuò)后自動(dòng)觸發(fā)的代碼。MySQL允許兩種處理器，
一種是EXIT處理，我們剛才所用的就是這種。另一種就是我們將要演示的，CONTINUE處理，
它跟EXIT處理類似，不同在于它執(zhí)行后，原主程序仍然繼續(xù)運(yùn)行，那么這個(gè)復(fù)合語(yǔ)句就沒(méi)有出
口了。
1. DECLARE CONTINUE HANDLER example CONTINUE處理例子
CREATE TABLE t4 (s1 int,primary key(s1));//
CREATE PROCEDURE p23 ()
BEGIN
DECLARE CONTINUE HANDLER
FOR SQLSTATE '23000' SET @x2 = 1;
SET @x = 1;
INSERT INTO t4 VALUES (1);
SET @x = 2;
INSERT INTO t4 VALUES (1);
SET @x = 3;
END;//
這是MySQL參考手冊(cè)上的CONTINUE處理的例子，這個(gè)例子十分好，所以我把它拷貝到這里。
通過(guò)這個(gè)例子我們可以看出CONTINUE處理是如何工作的。
2. DECLARE CONTINUE HANDLER聲明CONTINUE異常處理
CREATE TABLE t4 (s1 int,primary key(s1));//
CREATE PROCEDURE p23 ()
BEGIN
DECLARE CONTINUE HANDLER
FOR SQLSTATE '23000' SET @x2 = 1; <--
SET @x = 1;
INSERT INTO t4 VALUES (1);
SET @x = 2;
INSERT INTO t4 VALUES (1);
SET @x = 3;
END;//
這次我將為SQLSTATE值定義一個(gè)處理程序。還記得前面我們使用的MySQL錯(cuò)誤代碼1216嗎？
事實(shí)上這里的23000SQLSTATE是更常用的，當(dāng)外鍵約束出錯(cuò)或主鍵約束出錯(cuò)就被調(diào)用了。
3. DECLARE CONTINUE HANDLER
CREATE TABLE t4 (s1 int,primary key(s1));//
CREATE PROCEDURE p23 ()
BEGIN
DECLARE CONTINUE HANDLER
FOR SQLSTATE '23000' SET @x2 = 1;
SET @x = 1; <--
INSERT INTO t4 VALUES (1);
SET @x = 2;
INSERT INTO t4 VALUES (1);
SET @x = 3;
END;//
這個(gè)存儲(chǔ)過(guò)程的第一個(gè)執(zhí)行的語(yǔ)句是"SET @x = 1"。
4. DECLARE CONTINUE HANDLER example
CREATE TABLE t4 (s1 int,primary key(s1));//
CREATE PROCEDURE p23 ()
BEGIN
DECLARE CONTINUE HANDLER
FOR SQLSTATE '23000' SET @x2 = 1;
SET @x = 1;
INSERT INTO t4 VALUES (1);
SET @x = 2;
INSERT INTO t4 VALUES (1); <--
SET @x = 3;
END;//
運(yùn)行后值1被插入到主鍵表中。轉(zhuǎn)載請(qǐng)注明翻譯者陳朋奕及轉(zhuǎn)自:www.phpv.net
5. DECLARE CONTINUE HANDLER
CREATE TABLE t4 (s1 int,primary key(s1));//
CREATE PROCEDURE p23 ()
BEGIN
DECLARE CONTINUE HANDLER
FOR SQLSTATE '23000' SET @x2 = 1;
SET @x = 1;
INSERT INTO t4 VALUES (1);
SET @x = 2; <--
INSERT INTO t4 VALUES (1);
SET @x = 3;
END;//
然后@x的值變?yōu)?。
6. DECLARE CONTINUE HANDLER example
CREATE TABLE t4 (s1 int,primary key(s1));//
CREATE PROCEDURE p23 ()
BEGIN
DECLARE CONTINUE HANDLER
FOR SQLSTATE '23000' SET @x2 = 1;
SET @x = 1;
INSERT INTO t4 VALUES (1);
SET @x = 2;
INSERT INTO t4 VALUES (1); <--
SET @x = 3;
END;//
然后程序嘗試再次往主鍵表中插入數(shù)值，但失敗了，因?yàn)橹麈I有唯一性限制。
7. DECLARE CONTINUE HANDLER example
CREATE TABLE t4 (s1 int,primary key(s1));//
CREATE PROCEDURE p23 ()
BEGIN
DECLARE CONTINUE HANDLER
FOR SQLSTATE '23000' SET @x2 = 1; <--
SET @x = 1;
INSERT INTO t4 VALUES (1);
SET @x = 2;
INSERT INTO t4 VALUES (1);
SET @x = 3;
END;//
由于插入失敗，錯(cuò)誤處理程序被觸發(fā)，開(kāi)始進(jìn)行錯(cuò)誤處理。下一個(gè)執(zhí)行的語(yǔ)句是錯(cuò)誤處理的語(yǔ)
句，@x2被設(shè)為2。
8. DECLARE CONTINUE HANDLER example
CREATE TABLE t4 (s1 int,primary key(s1));//
CREATE PROCEDURE p23 ()
BEGIN
DECLARE CONTINUE HANDLER
FOR SQLSTATE '23000' SET @x2 = 1;
SET @x = 1;
INSERT INTO t4 VALUES (1);
SET @x = 2;
INSERT INTO t4 VALUES (1);
SET @x = 3; <--
END;//
到這里并沒(méi)有結(jié)束，因?yàn)檫@是CONTINUE異常處理。所以執(zhí)行返回到失敗的插入語(yǔ)句之后，
繼續(xù)執(zhí)行將@x設(shè)定為3動(dòng)作。轉(zhuǎn)載請(qǐng)注明翻譯者陳朋奕及轉(zhuǎn)自:www.phpv.net
9. DECLARE CONTINUE HANDLER example
mysql> CALL p23()//
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @x, @x2//
+------+------+
| @x | @x2 |
+------+------+
| 3 | 1 |
+------+------+
1 row in set (0.00 sec)
運(yùn)行過(guò)程后我們觀察@x的值，很確定的可以知道是3，觀察@x2的值，為1。從這里可以
判斷程序運(yùn)行無(wú)誤，完全按照我們的思路進(jìn)行。大家可以花點(diǎn)時(shí)間去調(diào)整錯(cuò)誤處理器，讓
檢查放在語(yǔ)句段的首部，而不是放在可能出現(xiàn)錯(cuò)誤的地方，雖然那樣看起來(lái)程序很紊亂，
跳來(lái)跳去的感覺(jué)。但是這樣的代碼很安全也很清楚。
1. DECLARE CONDITION
CREATE PROCEDURE p24 ()
BEGIN
DECLARE `Constraint Violation`
CONDITION FOR SQLSTATE '23000';
DECLARE EXIT HANDLER FOR
`Constraint Violation` ROLLBACK;
START TRANSACTION;
INSERT INTO t2 VALUES (1);
INSERT INTO t2 VALUES (1);
COMMIT;
END; //
這是另外一個(gè)錯(cuò)誤處理的例子，在前面的基礎(chǔ)上修改的。事實(shí)上你可給SQLSTATE或者錯(cuò)誤代碼其他的名字，你就可以在處理中使用自己定義的名字了。下面看它是怎么實(shí)現(xiàn)的：我把表t2
定義為InnoDB表，所以對(duì)這個(gè)表的插入操作都會(huì)ROLLBACK（回滾），ROLLBACK（回滾事務(wù)）也是恰好會(huì)發(fā)生的。因?yàn)閷?duì)主鍵插入兩個(gè)同樣的值會(huì)導(dǎo)致SQLSTATE 23000錯(cuò)誤發(fā)生，這里SQLSTATE 23000是約束錯(cuò)誤。
2. DECLARE CONDITION聲明條件
CREATE PROCEDURE p24 ()
BEGIN
DECLARE `Constraint Violation`
CONDITION FOR SQLSTATE '23000';
DECLARE EXIT HANDLER FOR
`Constraint Violation` ROLLBACK;
START TRANSACTION;
INSERT INTO t2 VALUES (1);
INSERT INTO t2 VALUES (1);
COMMIT;
END; //
這個(gè)約束錯(cuò)誤會(huì)導(dǎo)致ROLLBACK（回滾事務(wù)）和SQLSTATE 23000錯(cuò)誤發(fā)生。
3. DECLARE CONDITION
mysql> CALL p24()//
Query OK, 0 rows affected (0.28 sec)
mysql> SELECT * FROM t2//
Empty set (0.00 sec)
我們調(diào)用這個(gè)存儲(chǔ)過(guò)程看結(jié)果是什么，從上面結(jié)果我們看到表t2沒(méi)有插入任何記錄。全部事務(wù)都回滾了。這正是我們想要的。
4. DECLARE CONDITION
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;//
程序第二步聲明了游標(biāo)cur_1，如果你使用過(guò)嵌入式SQL的話，就知道這和嵌入式SQL差不多。
4. 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;//
最后進(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;//
這里第一個(gè)FETCH語(yǔ)句會(huì)獲得一行從SELECT產(chǎn)生的結(jié)果集中檢索出來(lái)的值，然而表t中有多
行，因此這個(gè)語(yǔ)句會(huì)被執(zhí)行多次，當(dāng)然這是因?yàn)檎Z(yǔ)句在循環(huán)塊內(nèi)。
7. 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;//
最后當(dāng)MySQL的FETCH沒(méi)有獲得行時(shí)，CONTINUE處理被觸發(fā)，將變量b賦值為1。
8. 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;//
到了這一步UNTIL b=1條件就為真，循環(huán)結(jié)束。在這里我們可以自己編寫代碼關(guān)閉游標(biāo)，
也可以由系統(tǒng)執(zhí)行，系統(tǒng)會(huì)在復(fù)合語(yǔ)句結(jié)束時(shí)自動(dòng)關(guān)閉游標(biāo)，但是最好不要太依賴系統(tǒng)的
自動(dòng)關(guān)閉行為（譯注：這可能跟Java的Gc一樣，不可信）。
9. 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è)例程中我們?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;//
mysql> CALL p25(@return_val)//
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @return_val//
+-------------+
| @return_val |
+-------------+
| 5 |
+-------------+
1 row in set (0.00 sec)
上面是過(guò)程調(diào)用后的結(jié)果?？梢钥吹絩eturn_val參數(shù)獲得了數(shù)值5，因?yàn)檫@是表t的最后一行。
由此可以知道游標(biāo)工作正常，出錯(cuò)處理也工作正常。
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
這里我們要討論一些關(guān)于特權(quán)和安全相關(guān)的問(wèn)題。但因?yàn)樵贛ySQL安全措施的功能并沒(méi)有完全，所以我們不會(huì)對(duì)其進(jìn)行過(guò)多討論。
1. Privileges CREATE ROUTINE
GRANT CREATE ROUTINE
ON database-name . *
TO user(s)
[WITH GRANT OPTION];
現(xiàn)在用root就可以了
在這里要介紹的特權(quán)是CREATE ROUTINE，它不僅同其他特權(quán)一樣可以創(chuàng)建存儲(chǔ)過(guò)程和函數(shù)，
還可以創(chuàng)建視圖和表。Root用戶擁有這種特權(quán)，同時(shí)還有ALTER ROUTINE特權(quán)。
2. Privileges EXECUTE
GRANT EXECUTE ON p TO peter
[WITH GRANT OPTION];
上面的特權(quán)是決定你是否可以使用或執(zhí)行存儲(chǔ)過(guò)程的特權(quán)，過(guò)程創(chuàng)建者默認(rèn)擁有這個(gè)特權(quán)。
3. Privileges SHOW ROUTINE?
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)的，在寫本書(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; //
現(xiàn)在我們測(cè)試一下SQL SECURITY子句吧。Security是我們前面提到的程序特性的一部分。你root
用戶，將插入權(quán)賦給了peter。然后使用peter登陸進(jìn)行新的工作，我們看peter可以怎么使用存儲(chǔ)過(guò)程，注意：peter沒(méi)有對(duì)表t的select權(quán)力，只有root用戶有。
5. Privileges Invokers and Definers
/* Logged on with current_user = peter */使用帳戶peter登陸
mysql> CALL p26();
ERROR 1142 (42000): select command denied to user
'peter'@'localhost' for table 't'
mysql> CALL p27();
+----------+
| COUNT(*) |
+----------+
| 1 |
+----------+
1 row in set (0.00 sec)
當(dāng)peter嘗試調(diào)用含有調(diào)用保密措施的過(guò)程p26時(shí)會(huì)失敗。那是因?yàn)閜eter沒(méi)有對(duì)表的select的權(quán)力。
但是當(dāng)petre調(diào)用含有定義保密措施的過(guò)程時(shí)就能成功。原因是root有select權(quán)力，Peter有root的
權(quán)力，因此過(guò)程可以執(zhí)行。
MySQL 5.0 觸發(fā)器
Introduction
本書(shū)是為需要了解5.0版本新特性的MySQL老用戶而寫的。簡(jiǎn)單的來(lái)說(shuō)介紹了 "存儲(chǔ)過(guò)程、觸發(fā)器、視圖、信息架構(gòu)視圖"，這是介紹MySQL 5.0新特性叢書(shū)的第一集。希望這本書(shū)能像內(nèi)行專家那樣與您進(jìn)行對(duì)話，用簡(jiǎn)單的問(wèn)題、例子讓你學(xué)到需要的知識(shí)。為了達(dá)到這樣的目的，我會(huì)從每一個(gè)細(xì)節(jié)開(kāi)始 慢的為大家建立概念，最后會(huì)給大家展示較大的實(shí)用例，在學(xué)習(xí)之前也許大家會(huì)認(rèn)為這個(gè)用例很難，但是只要跟著課程去學(xué)，相信很快就能掌握。
Conventions and Styles約定和編程風(fēng)格
每次我想要演示實(shí)際代碼時(shí)，我會(huì)對(duì)mysql客戶端的屏幕就出現(xiàn)的代碼進(jìn)行調(diào)整，將字體改成Courier，使他們看起來(lái)與普通文本不一樣（讓大家區(qū)別程序代碼和正文）。在這里舉個(gè)例子：
mysql> DROP FUNCTION f;
Query OK, 0 rows affected (0.00 sec)
如果實(shí)例比較大，則需要在某些行和段落間加注釋，同時(shí)我會(huì)用將"<--"符號(hào)放在頁(yè)面的右邊以表示強(qiáng)調(diào)。例如：
mysql> CREATE PROCEDURE p ()
-> BEGIN
-> /* This procedure does nothing */ <--
-> END;//
Query OK, 0 rows affected (0.00 sec)
有時(shí)候我會(huì)
分享名稱：MySQL5.0新特性
文章分享：http://aaarwkj.com/article22/jegijc.html
成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供搜索引擎優(yōu)化、網(wǎng)站建設(shè)、移動(dòng)網(wǎng)站建設(shè)、定制網(wǎng)站、商城網(wǎng)站、軟件開(kāi)發(fā)
聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源： 創(chuàng)新互聯(lián)
- 公司做網(wǎng)站如何策劃網(wǎng)站策劃方案要點(diǎn) 2022-12-02
- 淺談網(wǎng)站策劃前期工作 2016-11-04
- 成都網(wǎng)站建設(shè)該如何做好網(wǎng)站策劃？ 2022-06-23
- 做網(wǎng)站不僅僅要懂網(wǎng)站設(shè)計(jì),還要懂網(wǎng)站策劃 2014-01-18
- 企業(yè)如何制定一份高質(zhì)量的網(wǎng)站策劃方案？需要注意哪幾點(diǎn)？ 2016-10-12
- 網(wǎng)站策劃如何寫出富有說(shuō)服力的超級(jí)文案 2013-08-06
- 網(wǎng)站建設(shè)怎么樣才能做好網(wǎng)站策劃？ 2014-01-28
- 網(wǎng)站策劃必須遵守的三條基本準(zhǔn)則 2022-11-12
- 如何才能做好企業(yè)網(wǎng)站策劃方案的思路方法知識(shí) 2016-09-27
- 如何做出一份優(yōu)質(zhì)的網(wǎng)站策劃方案 2022-05-29
- 公司網(wǎng)站策劃的步驟 2022-05-09
- 網(wǎng)站制作淺談網(wǎng)站策劃及意義 2021-11-16