[From] http://www.uplinux.com/www/dev/04/index.shtml
Introduction 簡介
MySQL 5.0
新特性教程是為需要了解5.0版本新特性的MySQL老用戶而寫的。簡單的來說是介紹了“存儲過程、觸發器、視圖、信息架構視圖”,我打算每天一部分,來
連載這個教程.如果你想看一次看完PDF版本的教程,請到down.phpv.net下載.在此感謝譯者陳朋奕的努力.
希望這本書能像內行專家那樣與您進行對話，用簡單的問題、例子讓你學到需要的知識。為了達到這樣的目的，我會從每一個細節開始慢慢的為大家建立概念，最后會給大家展示較大的實用例，在學習之前也許大家會認為這個用例很難，但是只要跟著課程去學，相信很快就能掌握。
Conventions and Styles 約定和編程風格
每次我想要演示實際代碼時，我會對mysql客戶端的屏幕就出現的代碼進行調整，將字體改成Courier，使他們看起來與普通文本不一樣。
在這里舉個例子：mysql> DROP FUNCTION f;Query OK, 0 rows affected (0.00 sec)
如果實例比較大，則需要在某些行和段落間加注釋，同時我會用將“<--”符號放在頁面的右邊以表示強調。
例如：
mysql> CREATE PROCEDURE p ()
-> BEGIN
-> /* This procedure does nothing */ <--
-> END;//Query OK, 0 rows affected (0.00 sec)
有時候我會將例子中的"mysql>"和"->"這些系統顯示去掉，你可以直接將代碼復制到mysql客戶端程序中（如果你現在所讀的
不是電子版的，可以在mysql.com網站下載相關腳本）所以的例子都已經在Suse 9.2 Linux、Mysql 5.0.3公共版上測試通過。
在您閱讀本書的時候，Mysql已經有更高的版本，同時能支持更多OS了，包括Windows，Sparc，HP-UX。因此這里的例子將能正常的運行在您的電腦上。但如果運行仍然出現故障，可以咨詢你認識的資深Mysql用戶，以得到長久的支持和幫助。
A Definition and an Example 定義及實例
定義及實例存儲過程是一種存儲在書庫中的程序（就像正規語言里的子程序一樣），準確的來說，MySQL支持的“routines（例程）”有兩種：
一是我們說的存儲過程，二是在其他SQL語句中可以返回值的函數（使用起來和Mysql預裝載的函數一樣，如pi()）。我在本書里面會更經常使用存儲過
程，因為這是我們過去的習慣，相信大家也會接受。
一個存儲過程包括名字，參數列表，以及可以包括很多SQL語句的SQL語句集。
在這里對局部變量，異常處理，循環控制和IF條件句有新的語法定義。
下面是一個包括存儲過程的實例聲明：（譯注：為了方便閱讀，此后的程序不添任何中文注釋）
CREATE PROCEDURE procedure1 /* name存儲過程名*/
(IN parameter1 INTEGER) /* parameters參數*/
BEGIN /* start of block語句塊頭*/
DECLARE variable1 CHAR(10); /* variables變量聲明*/
IF parameter1 = 17 THEN /* start of IF IF條件開始*/
SET variable1 = 'birds'; /* assignment賦值*/
ELSE
SET variable1 = 'beasts'; /* assignment賦值*/
END IF; /* end of IF IF結束*/
INSERT INTO table1 VALUES (variable1);/* statement SQL語句*/
END /* end of block語句塊結束*/
下面我將會介紹你可以利用存儲過程做的工作的所有細節。同時我們將介紹新的數據庫對象—觸發器，因為觸發器和存儲過程的關聯是必然的。
Why Stored Procedures 為什么要用存儲過程
由于存儲過程對于MySQL來說是新的功能，很自然的在使用時你需要更加注意。
畢竟，在此之前沒有任何人使用過，也沒有很多大量的有經驗的用戶來帶你走他們走過的路。然而你應該開始考慮把現有程序（可能在服務器應用程序中，用戶自定義函數（UDF）中，或是腳本中）轉移到存儲過程中來。這樣做不需要原因，你不得不去做。
因為存儲過程是已經被認證的技術！雖然在Mysql中它是新的，但是相同功能的函數在其他DBMS中早已存在，而它們的語法往是相同的。因此你可以從其他人那里獲得這些概念，也有很多你可以咨詢或者雇用的經驗用戶，還有許多第三方的文檔可供你閱讀。
存
儲過程會使系統運行更快！雖然我們暫時不能在Mysql上證明這個優勢，用戶得到的體驗也不一樣。我們可以說的就是Mysql服務器在緩存機制上做了改
進，就像Preparedstatements（預處理語句）所做的那樣。由于沒有編譯器，因此SQL存儲過程不會像外部語言（如C）編寫的程序運行起來
那么快。但是提升速度的主要方法卻在于能否降低網絡信息流量。如果你需要處理的是需要檢查、循環、多語句但沒有用戶交互的重復性任務，你就可以使用保存在
服務器上的存儲過程來完成。這樣在執行任務的每一步時服務器和客戶端之間就沒那么多的信息來往了。轉載請注明翻譯者陳朋奕及轉自:www.phpv.net
所
以存儲過程是可復用的組件！想象一下如果你改變了主機的語言，這對存儲過程不會產生影響，因為它是數據庫邏輯而不是應用程序。存儲過程是可以移植的！當你
用SQL編寫存儲過程時，你就知道它可以運行在Mysql支持的任何平臺上，不需要你額外添加運行環境包，也不需要為程序在操作系統中執行設置許可，或者
為你的不同型號的電腦存儲過程將被保存！如果你編寫好了一個程序，例如顯示銀行事物處理中的支票撤消，那想要了解支票的人就可以找到你的程序。
它會以源代碼的形式保存在數據庫中。這將使數據和處理數據的進程有意義的關聯這可能跟你在課上聽到的規劃論中說的一樣。存儲過程可以遷移！
Mysql完全支持SQL 2003標準。某些數據庫（如DB2、Mimer）同樣支持。但也有部分不支持的，如Oracle、SQL Server不支持。我們將會給予足夠幫助和工具，使為其他DBMS編寫的代碼能更容易轉移到Mysql上。
Setting up with MySQL 5.0 設置并開始MySQL 5.0服務
?
通過
mysql_fix_privilege_tables
或者
~/mysql-5.0/scripts/mysql_install_db
來開始MySQL服務
作為我們練習的準備工作的一部分，我假定MySQL 5.0已經安裝。如果沒有數據庫管理員為你安裝好數據庫以及其他軟件，你就需要自己去安裝了。不過你很容易忘掉一件事，那就是你需要有一個名為mysql.proc的表。
在安裝了最新版本后，你必須運行
mysql_fix_privilege_tables
或者
mysql_install_db
（只需要運行其中一個就夠了）——不然存儲過程將不能工作。我同時啟用在root身份后運行一個非正式的SQL腳本，如下：
mysql>source/home/pgulutzan/mysql-5.0/scripts/mysql_prepare_privilege_tables_for_5.sql
?
Starting the MySQL Client 啟動MySQL客戶端
?
這是我啟動mysql客戶端的方式。你也許會使用其他方式，如果你使用的是二進制版本或者是Windows系統的電腦，你可能會在其他子目錄下運行以下程序：
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.
在演示中，我將會展示以root身份登陸后的mysql客戶端返回的結果，這樣意味著我有極大的特權。轉載請注明翻譯者陳朋奕及轉自:www.phpv.net
?
Check for the Correct Version 核對版本
為了確認使用的MySQL的版本是正確的，我們要查詢版本。我有兩種方法確認我使用的是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)
當看見數字'5.0.x'? 后就可以確認存儲過程能夠在這個客戶端上正常工作。
The Sample "Database" 示例數據庫
現在要做的第一件事是創建一個新的數據庫然后設定為默認數據庫實現這個步驟的SQL
語句如下：
CREATE DATABASE db5;
USE db5;
例如：
mysql> CREATE DATABASE db5;
Query OK, 1 row affected (0.00 sec)
mysql> USE db5;
Database changed
在這里要避免使用有重要數據的實際的數據庫然后我們創建一個簡單的工作表。
實現這個步驟的SQL
語句如下：
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)
你會發現我只在表中插入了一列。這樣做的原因是我要保持表的簡單，因為在這里并不需要展示查詢數據的技巧，而是教授存儲過程，不需要使用大的數據表，因為它本身已經夠復雜了。
這就是示例數據庫，我們將從這個名字為t的只包含一列的表開始Pick a Delimiter 選擇分隔符
現在我們需要一個分隔符，實現這個步驟的SQL語句如下：
DELIMITER //
例如：
mysql> DELIMITER //
分
隔符是你通知mysql客戶端你已經完成輸入一個SQL語句的字符或字符串符號。一直以來我們都使用分號“；”，但在存儲過程中，這會產生不少問題，因為
存儲過程中有許多語句，所以每一個都需要一個分號因此你需要選擇一個不太可能出現在你的語句或程序中的字符串作為分隔符。我曾用過雙斜杠“//”，也有人
用豎線“|”。我曾見過在DB2程序中使用“@”符號的，但我不喜歡這樣。你可以根據自己的喜好來選擇，但是在這個課程中為了更容易理解，你最好選擇跟我
一樣。如果以后要恢復使用“;”（分號）作為分隔符，輸入下面語句就可以了：
"DELIMITER ;//".
CREATE PROCEDURE Example 創建程序實例
CREATE PROCEDURE p1 () SELECT * FROM t; //
也許這是你使用Mysql創建的第一個存儲過程。假如是這樣的話，最好在你的日記中記下這個重要的里程碑。
CREATE PROCEDURE p1 () SELECT * FROM t; // <--
SQL語句存儲過程的第一部分是“CREATE PROCEDURE”：
CREATE PROCEDURE p1 () SELECT * FROM t; // <--
第二部分是過程名，上面新存儲過程的名字是p1。
Digression: Legal Identifiers 題外話：合法標識符的問題
存儲過程名對大小寫不敏感，因此‘P1’和‘p1’是同一個名字，在同一個數據庫中你將不能給兩個存儲過程取相同的名字，因為這樣將會導致重載。某些DBMS允許重載（Oracle支持），但是MySQL不支持（譯者話：希望以后會支持吧。）。
你可以采取“數據庫名.存儲過程名”這樣的折中方法，如“db5.p1”。存儲過程名可以分開，它可以包括空格符，其長度限制為64個字符，但注意不要使用MySQL內建函數的名字，如果這樣做了，在調用時將會出現下面的情況：
mysql> CALL pi();
Error 1064 (42000): You have a syntax error.
mysql> CALL pi ();
Error 1305 (42000): PROCEDURE does not exist.
在上面的第一個例子里，我調用的是一個名字叫pi的函數，但你必須在調用的函數名后加上空格，就像第二個例子那樣。
CREATE PROCEDURE p1 () SELECT * FROM t; // <--
其中“（）”是“參數列表”。
CREATE PROCEDURE
語句的第三部分是參數列表。通常需要在括號內添加參數。例子中的存儲過程沒有參數，因此參數列表是空的—所以我只需要鍵入空括號，然而這是必須的。
CREATE PROCEDURE p1 () SELECT * FROM t; // <--
"SELECT * FROM t;"
是存儲過程的主體。
然后到了語句的最后一個部分了，它是存儲過程的主體，是一般的SQL語句。過程體中語句
"SELECT * FROM t;"
包含一個分號，如果后面有語句結束符號（//）時可以不寫這個分號。
如果你還記得我把這部分叫做程序的主體將會是件好事，因為（body）這個詞是大家使用的技術上的術語。通常我們不會將SELECT語句用在存儲過程中，這里只是為了演示。所以使用這樣的語句，能在調用時更好的看出程序是否正常工作。
Why MySQL Statements are Legal in a Procedure Body
什么MySQL語句在存儲過程體中是合法的？
什么樣的SQL語句在Mysql存儲過程中才是合法的呢？你可以創建一個包含INSERT, UPDATE,DELETE, SELECT,
DROP, CREATE,
REPLACE等的語句。你唯一需要記住的是如果代碼中包含MySQL擴充功能，那么代碼將不能移植。在標準SQL語句中：任何數據庫定義語言都是合法
的，如：
CREATE PROCEDURE p () DELETE FROM t; //
SET、COMMIT以及ROLLBACK
也是合法的，如：
CREATE PROCEDURE p () SET @x = 5; //
MySQL的附加功能：任何數據操作語言的語句都將合法。
CREATE PROCEDURE p () DROP TABLE t; //
MySQL擴充功能：直接的SELECT也是合法的：
CREATE PROCEDURE p () SELECT 'a'; //
順便提一下，我將存儲過程中包括DDL語句的功能稱為MySQL附加功能的原因是在SQL標準中把這個定義為非核心的，即可選組件。轉載請注明翻譯者陳朋奕及轉自:www.phpv.net
在過程體中有一個約束，就是不能有對例程或表操作的數據庫操作語句。例如下面的例子就是非法的：
CREATE PROCEDURE p1 ()
CREATE PROCEDURE p2 () DELETE FROM t; //
下面這些對MySQL 5.0來說全新的語句，過程體中是非法的?lt;BR>CREATE PROCEDURE, ALTER PROCEDURE, DROP PROCEDURE, CREATE FUNCTION,
DROP FUNCTION, CREATE TRIGGER, DROP TRIGGER.
不過你可以使用
"CREATE PROCEDURE db5.p1 () DROP DATABASE db5//"
，但是類似
"USE database"
語句也是非法的，因為MySQL假定默認數據庫就是過程的工作場所。
Call the Procedure 調用存儲過程
1.
現在我們就可以調用一個存儲過程了，你所需要輸入的全部就是CALL和你過程名以及一個括號再一次強調，括號是必須的當你調用例子里面的p1過程時，結果是屏幕返回了t表的內容
mysql> CALL p1() //
+------+
| s1 |
+------+
| 5 |
+------+
1 row in set (0.03 sec)
Query OK, 0 rows affected (0.03 sec)
因為過程中的語句是
"SELECT * FROM t;"
2. Let me say that again, another way.
其他實現方式
mysql> CALL p1() //
和下面語句的執行效果一樣：
mysql> SELECT * FROM t; //
所以，你調用p1過程就相當于你執行了下面語句：
"SELECT * FROM t;".
好了，主要的知識點"創建和調用過程方法"已經清楚了。我希望你能對自己說這相當簡單。但是很快我們就有一系列的練習，每次都加一條子句，或者改變已經存在的子句。那樣在寫復雜部件前我們將會有很多可用的子句。轉載請注明翻譯者陳朋奕及轉自: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 //
這里我給出的是一些能反映存儲過程特性的子句。子句內容在括號之后，主體之前。這些子句都是可選的，他們有什么作用呢？
2.
CREATE PROCEDURE p2 ()
LANGUAGE SQL <--
NOT DETERMINISTIC
SQL SECURITY DEFINER
COMMENT 'A Procedure'
SELECT CURRENT_DATE, RAND() FROM t //
很 好，這個LANGUAGE
SQL子句是沒有作用的。僅是為了說明下面過程的主體使用SQL語言編寫。這條是系統默認的，但你在這里聲明是有用的，因為某些DBMS（IBM的
DB2）需要它，如果你關注DB2的兼容問題最好還是用上。此外，今后可能會出現除SQL外的其他語言支持的存儲過程。
3.
CREATE PROCEDURE p2 ()
LANGUAGE SQL
NOT DETERMINISTIC <--
SQL SECURITY DEFINER
COMMENT 'A Procedure'
SELECT CURRENT_DATE, RAND() FROM t //
下 一個子句，NOT
DETERMINISTIC，是傳遞給系統的信息。這里一個確定過程的定義就是那些每次輸入一樣輸出也一樣的程序。在這個案例中，既然主體中含有
SELECT語句，那返回肯定是未知的因此我們稱其NOT DETERMINISTIC。但是MySQL內置的優化程序不會注意這個，至少在現在不注意。轉載請注明翻譯者陳朋奕及轉自:www.phpv.net
4.
CREATE PROCEDURE p2 ()
LANGUAGE SQL
NOT DETERMINISTIC
SQL SECURITY DEFINER <--
COMMENT 'A Procedure'
SELECT CURRENT_DATE, RAND() FROM t //
下一個子句是SQL SECURITY，可以定義為SQL SECURITY DEFINER或SQL SECURITY INVOKER。
這就進入了權限控制的領域了，當然我們在后面將會有測試權限的例子。
SQL SECURITY DEFINER
意味著在調用時檢查創建過程用戶的權限（另一個選項是SQLSECURITY INVOKER）。
現在而言，使用
SQL SECURITY DEFINER
指令告訴MySQL服務器檢查創建過程的用戶就可以了，當過程已經被調用，就不檢查執行調用過程的用戶了。而另一個選項（INVOKER）
則是告訴服務器在這一步仍然要檢查調用者的權限。轉載請注明翻譯者陳朋奕及轉自: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'
是一個可選的注釋說明。
最后，注釋子句會跟過程定義存儲在一起。這個沒有固定的標準，我在文中會指出沒有固定標準的語句，不過幸運的是這些在我們標準的SQL中很少。
6.
CREATE PROCEDURE p2 ()
LANGUAGE SQL
NOT DETERMINISTIC
SQL SECURITY DEFINER
COMMENT ''
SELECT CURRENT_DATE, RAND() FROM t //
上面過程跟下面語句是等效的：
CREATE PROCEDURE p2 ()
SELECT CURRENT_DATE, RAND() FROM t //
特征子句也有默認值，如果省略了就相當于：
LANGUAGE SQL NOT DETERMINISTIC SQL SECURITY DEFINER COMMENT ''.
Digressions一些題外話
轉載請注明翻譯者陳朋奕及轉自:www.phpv.net
Digression:
調用p2()//的結果
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)
當調用過程p2時，一個SELECT語句被執行返回我們期望獲得的隨機數。
Digression: sql_mode unchanging
不會改變的
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在過程創建時會自動保持運行環境。例如：我們需要使用兩條豎線來連接字符串但是這只有在sql mode為ansi的時候才合法。如果我們將sql mode改為non-ansi，不用擔心，它仍然能工作，只要它第一次使用時能正常工作。轉載請注明翻譯者陳朋奕及轉自:www.phpv.net
Exercise 練習
Question
問題
如果你不介意練習一下的話，試能否不看后面的答案就能處理這些請求。
創建一個過程，顯示`Hello world`。用大約5秒時間去思考這個問題，既然你已經學到了這里，這個應該很簡單。當你思考問題的時候，我們再隨機選擇一些剛才講過的東西復習：
DETERMINISTIC
（確定性）子句是反映輸出和輸入依賴特性的子句…調用過程使用CALL過程名（參數列表）方式。好了，我猜時間也到了。
Answer
答案
好的，答案就是在過程體中包含
"SELECT 'Hello, world'"
語句
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 參數
讓我們更進一步的研究怎么在存儲過程中定義參數
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) ...
回憶一下前面講過的參數列表必須在存儲過程名后的括號中。上面的第一個例子中的參數列表是空的，第二個例子中有一個輸入參數。這里的詞IN可選，因為默認參數為IN（input）。
第三個例子中有一個輸出參數，第四個例子中有一個參數，既能作為輸入也可以作為輸出。
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)
這個IN的例子演示的是有輸入參數的過程。在過程體中我將會話變量x設定為參數p的值。然后調用過程，將12345傳入參數p。選擇顯示會話變量@x，證明我們已經將參數值12345傳入。
OUT example 輸出的例子
mysql> CREATE PROCEDURE p6 (OUT p INT)
-> SET p = -5 //
mysql> CALL p6(@y)//
mysql> SELECT @y//
+------+
| @y |
+------+
| -5 |
+------+
這是另一個例子。這次的p是輸出參數，然后在過程調用中將p的值傳入會話變量@y中。轉載請注明翻譯者陳朋奕及轉自:www.phpv.net
在過程體中，我們給參數賦值-5，在調用后我們可以看出，OUT是告訴DBMS值是從過程中傳出的。
同樣我們可以用語句
"SET @y = -5;".
來達到同樣的效果
Compound Statements 復合語句
現在我們展開的詳細分析一下過程體：
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.
這個語句將不會被調用
*/
完
成過程體的構造就是BEGIN/END塊。這個BEGIN/END語句塊和Pascal語言中的BEGIN/END是基本相同的，和C語言的框架是很相似
的。我們可以使用塊去封裝多條語句。在這個例子中，我們使用了多條設定會話變量的語句，然后完成了一些insert和select語句。如果你的過程體中
有多條語句，那么你就需要BEGIN/END塊了。BEGIN/END塊也被稱為復合語句，在這里你可以進行變量定義和流程控制。
The New SQL Statements 新SQL語句
Variables 變量
在復合語句中聲明變量的指令是DECLARE。
(1) Example with two DECLARE statements
兩個DECLARE語句的例子?
CREATE PROCEDURE p8 ()
BEGIN
DECLARE a INT;
DECLARE b INT;
SET a = 5;
SET b = 5;
INSERT INTO t VALUES (a);
SELECT s1 * a FROM t WHERE s1 >= b;
END; // /* I won't CALL this */
?
在過程中定義的變量并不是真正的定義，你只是在BEGIN/END塊內定義了而已（譯注：也就是形參）。
注意這些變量和會話變量不一樣，不能使用修飾符@你必須清楚的在BEGIN/END塊中聲明變量和它們的類型。
變量一旦聲明，你就能在任何能使用會話變量、文字、列名的地方使用。
?
(2) Example with no DEFAULT clause and SET statement
沒有默認子句和設定語句的例子
CREATE PROCEDURE p9 ()
BEGIN
DECLARE a INT /* there is no DEFAULT clause */;
DECLARE b INT /* there is no DEFAULT clause */;
SET a = 5; /* there is a SET statement */
SET b = 5; /* there is a SET statement */
INSERT INTO t VALUES (a);
SELECT s1 * a FROM t WHERE s1 >= b;
END; // /* I won't CALL this */
有很多初始化變量的方法。如果沒有默認的子句，那么變量的初始值為NULL。你可以在任何時候使用SET語句給變量賦值。
?
(3) Example with DEFAULT clause
含有DEFAULT子句的例子
CREATE PROCEDURE p10 ()
BEGIN
DECLARE a, b INT DEFAULT 5;
INSERT INTO t VALUES (a);
SELECT s1 * a FROM t WHERE s1 >= b;
END; //
我們在這里做了一些改變，但是結果還是一樣的。在這里使用了DEFAULT子句來設定初
始值，這就不需要把DECLARE和SET語句的實現分開了。
(4) Example of CALL
調用的例子
mysql> CALL p10() //
+--------+
| s1 * a |
+--------+
| 25 |
| 25 |
+--------+
2 rows in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
結果顯示了過程能正常工作
?
(5) Scope
作用域
CREATE PROCEDURE p11 ()
BEGIN
DECLARE x1 CHAR(5) DEFAULT 'outer';
BEGIN
DECLARE x1 CHAR(5) DEFAULT 'inner';
SELECT x1;
END;
SELECT x1;
END; //
現在我們來討論一下作用域的問題。例子中有嵌套
的BEGIN/END塊，當然這是合法的。同時包含兩個變量，名字都
是x1，這樣也是合法的。內部的變量在其作用域內享有更高的優先權。當執行到END語句時，內部變量消失，此時已經在其作用域外，變量不再可見了，因此在
存儲過程外再也不能找到這個聲明了的變量，但是你可以通過OUT參數或者將其值指派 給會話變量來保存其值。
?
調用作用域例子的過程：
mysql> CALL p11()//
+-------+
| x1 |
+-------+
| inner |
+-------+
+-------+
| x1 |
+-------+
| outer |
+-------+
我們看到的結果時第一個SELECT語句檢索到最內層的變量，第二個檢索到第二層的變量
?
Conditions and IF-THEN-ELSE 條件式和IF-THEN-ELSE
1.
現在我們可以寫一些包含條件式的例子：
CREATE PROCEDURE p12 (IN parameter1 INT)
BEGIN
DECLARE variable1 INT;
SET variable1 = parameter1 + 1;
IF variable1 = 0 THEN
INSERT INTO t VALUES (17);
END IF;
IF parameter1 = 0 THEN
UPDATE t SET s1 = s1 + 1;
ELSE
UPDATE t SET s1 = s1 + 2;
END IF;
END; //
這里是一個包含IF語句的過程。里面有兩個IF語句，一個是IF語句END IF，另一個是IF語句ELSE語句END IF。我們可以在這里使用復雜的過程，但我會盡量使其簡單讓你能更容易弄清楚。
2.
CALL p12 (0) //
我們調用這個過程，傳入值為0，這樣parameter1的值將為0。
?
3.
CREATE PROCEDURE p12 (IN parameter1 INT)
BEGIN
DECLARE variable1 INT;
SET variable1 = parameter1 + 1; <--
IF variable1 = 0 THEN
INSERT INTO t VALUES (17);
END IF;
IF parameter1 = 0 THEN
UPDATE t SET s1 = s1 + 1;
ELSE
UPDATE t SET s1 = s1 + 2;
END IF;
END; //
這里變量variable1被賦值為parameter1加1的值，所以執行后變量variable1為1。
4.
CREATE PROCEDURE p12 (IN parameter1 INT)
BEGIN
DECLARE variable1 INT;
SET variable1 = parameter1 + 1;
IF variable1 = 0 THEN <--
INSERT INTO t VALUES (17);
END IF;
IF parameter1 = 0 THEN
UPDATE t SET s1 = s1 + 1;
ELSE
UPDATE t SET s1 = s1 + 2;
END IF;
END; //
因為變量variable1值為1，因此條件"if variable1 = 0"為假，
IF
……
END IF
被跳過，沒有被執行。
5.
CREATE PROCEDURE p12 (IN parameter1 INT)
BEGIN
DECLARE variable1 INT;
SET variable1 = parameter1 + 1;
IF variable1 = 0 THEN
INSERT INTO t VALUES (17);
END IF;
IF parameter1 = 0 THEN <--
UPDATE t SET s1 = s1 + 1;
ELSE
UPDATE t SET s1 = s1 + 2;
END IF;
END; //
?
到第二個IF條件，判斷結果為真，于是中間語句被執行了
6.
CREATE PROCEDURE p12 (IN parameter1 INT)
BEGIN
DECLARE variable1 INT;
SET variable1 = parameter1 + 1;
IF variable1 = 0 THEN
INSERT INTO t VALUES (17);
END IF;
IF parameter1 = 0 THEN
UPDATE t SET s1 = s1 + 1; <--
ELSE
UPDATE t SET s1 = s1 + 2;
END IF;
END; //
因為參數parameter1值等于0，UPDATE語句被執行。如果parameter1值為NULL，則下一條
UPDATE
語句將被執行現在表t中有兩行，他們都包含值5，所以如果我們調用p12，兩行的值會變成6。
7.
mysql> CALL p12(0)//
Query OK, 2 rows affected (0.28 sec)
mysql> SELECT * FROM t//
+------+
| s1 |
+------+
| 6 |
| 6 |
+------+
2 rows in set (0.01 sec)
結果也是我們所期望的那樣。
CASE 指令
1.
CREATE PROCEDURE p13 (IN parameter1 INT)
BEGIN
DECLARE variable1 INT;
SET variable1 = parameter1 + 1;
CASE variable1
WHEN 0 THEN INSERT INTO t VALUES (17);
WHEN 1 THEN INSERT INTO t VALUES (18);
ELSE INSERT INTO t VALUES (19);
END CASE;
END; //
Error Handling 異常處理
好了，我們現在要講的是異常處理
1. Sample Problem: Log Of Failures?? 問題樣例：故障記錄
當INSERT失敗時，我希望能將其記錄在日志文件中我們用來展示出錯處理的問題樣例是很
普通的。我希望得到錯誤的記錄。當INSERT失敗時，我想在另一個文件中記下這些錯誤的
信息，例如出錯時間，出錯原因等。我對插入特別感興趣的原因是它將違反外鍵關聯的約束
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（這里顯示的是系統的出錯信息）
我開始要創建一個主鍵表，以及一個外鍵表。我們使用的是InnoDB，因此外鍵關聯檢查是打
開的。然后當我向外鍵表中插入非主鍵表中的值時，動作將會失敗。當然這種條件下可以很
快找到錯誤號1216。轉載請注明翻譯者陳朋奕及轉自:www.phpv.net
3. Sample Problem: Log Of Failures
CREATE TABLE error_log (error_message
CHAR(80))//
下一步就是建立一個在做插入動作出錯時存儲錯誤的表。
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;//
上面就是我們的程序。這里的第一個語句DECLARE EXIT HANDLER是用來處理異常的。意
思是如果錯誤1215發生了，這個程序將會在錯誤記錄表中插入一行。EXIT意思是當動作成功提交后退出這個復合語句。轉載請注明翻譯者陳朋奕及轉自:www.phpv.net
5. Sample Problem: Log Of Errors
CALL p22 (5) //
調用這個存儲過程會失敗，這很正常，因為5值并沒有在主鍵表中出現。但是沒有錯誤信息
返回因為出錯處理已經包含在過程中了。t3表中沒有增加任何東西，但是error_log表中記錄
下了一些信息，這就告訴我們INSERT into table t3動作失敗。
DECLARE HANDLER syntax 聲明異常處理的語法
DECLARE
{ EXIT | CONTINUE }
HANDLER FOR
{ error-number | { SQLSTATE error-string } | condition }
SQL statement
上面就是錯誤處理的用法，也就是一段當程序出錯后自動觸發的代碼。MySQL允許兩種處理器，
一種是EXIT處理，我們剛才所用的就是這種。另一種就是我們將要演示的，CONTINUE處理，
它跟EXIT處理類似，不同在于它執行后，原主程序仍然繼續運行，那么這個復合語句就沒有出
口了。
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參考手冊上的CONTINUE處理的例子，這個例子十分好，所以我把它拷貝到這里。
通過這個例子我們可以看出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值定義一個處理程序。還記得前面我們使用的MySQL錯誤代碼1216嗎？
事實上這里的23000SQLSTATE是更常用的，當外鍵約束出錯或主鍵約束出錯就被調用了。
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;//
這個存儲過程的第一個執行的語句是"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;//
運行后值1被插入到主鍵表中。轉載請注明翻譯者陳朋奕及轉自: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的值變為2。
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;//
然后程序嘗試再次往主鍵表中插入數值，但失敗了，因為主鍵有唯一性限制。
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;//
由于插入失敗，錯誤處理程序被觸發，開始進行錯誤處理。下一個執行的語句是錯誤處理的語
句，@x2被設為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;//
到這里并沒有結束，因為這是CONTINUE異常處理。所以執行返回到失敗的插入語句之后，
繼續執行將@x設定為3動作。轉載請注明翻譯者陳朋奕及轉自: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)
運行過程后我們觀察@x的值，很確定的可以知道是3，觀察@x2的值，為1。從這里可以
判斷程序運行無誤，完全按照我們的思路進行。大家可以花點時間去調整錯誤處理器，讓
檢查放在語句段的首部，而不是放在可能出現錯誤的地方，雖然那樣看起來程序很紊亂，
跳來跳去的感覺。但是這樣的代碼很安全也很清楚。
?
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; //
這是另外一個錯誤處理的例子，在前面的基礎上修改的。事實上你可給SQLSTATE或者錯誤代碼其他的名字，你就可以在處理中使用自己定義的名字了。下面看它是怎么實現的：我把表t2
定義為InnoDB表，所以對這個表的插入操作都會ROLLBACK（回滾），ROLLBACK（回滾事務）也是恰好會發生的。因為對主鍵插入兩個同樣的值會導致SQLSTATE 23000錯誤發生，這里SQLSTATE 23000是約束錯誤。
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; //
這個約束錯誤會導致ROLLBACK（回滾事務）和SQLSTATE 23000錯誤發生。
3. DECLARE CONDITION
mysql> CALL p24()//
Query OK, 0 rows affected (0.28 sec)
mysql> SELECT * FROM t2//
Empty set (0.00 sec)
我們調用這個存儲過程看結果是什么，從上面結果我們看到表t2沒有插入任何記錄。全部事務都回滾了。這正是我們想要的。
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)
這里是三個預聲明的條件：NOT FOUND (找不到行), SQLEXCEPTION (錯誤),SQLWARNING (
警告或注釋)。因為它們是預聲明的，因此不需要聲明條件就可以使用。不過如果你去做這樣的聲明："DECLARE SQLEXCEPTION CONDITION ..."，你將會得到錯誤信息提示。
?
Cursors 游標
游標實現功能摘要：
DECLARE cursor-name CURSOR FOR SELECT ...;
OPEN cursor-name;
FETCH cursor-name INTO variable [, variable];
CLOSE cursor-name;
現在我們開始著眼游標了。雖然我們的存儲過程中的游標語法還并沒有完整的實現，但是
已經可以完成基本的事務如聲明游標，打開游標，從游標里讀取，關閉游標。
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;//
我們看一下包含游標的存儲過程的新例子。
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;//
這個過程開始聲明了三個變量。附帶說一下，順序是十分重要的。首先要進行變量聲明，
然后聲明條件，隨后聲明游標，再后面才是聲明錯誤處理器。如果你沒有按順序聲明，
系統會提示錯誤信息。
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;//
程序第二步聲明了游標cur_1，如果你使用過嵌入式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;//
最后進行的是錯誤處理器的聲明。這個CONTINUE處理沒有引用SQL錯誤代碼和SQLSTATE值。
它使用的是NOT FOUND系統返回值，這和SQLSTATE 02000是一樣的。
轉載請注明翻譯者陳朋奕及轉自: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;//
過程第一個可執行的語句是OPEN cur_1，它與SELECT s1 FROM t語句是關聯的，過程將執行
SELECT s1 FROM t，返回一個結果集。
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;//
這里第一個FETCH語句會獲得一行從SELECT產生的結果集中檢索出來的值，然而表t中有多
行，因此這個語句會被執行多次，當然這是因為語句在循環塊內。
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;//
最后當MySQL的FETCH沒有獲得行時，CONTINUE處理被觸發，將變量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條件就為真，循環結束。在這里我們可以自己編寫代碼關閉游標，
也可以由系統執行，系統會在復合語句結束時自動關閉游標，但是最好不要太依賴系統的
自動關閉行為（譯注：這可能跟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;//
這個例程中我們為輸出參數指派了一個局部變量，這樣在過程結束后的結果仍能使用。
轉載請注明翻譯者陳朋奕及轉自: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)
上面是過程調用后的結果。可以看到return_val參數獲得了數值5，因為這是表t的最后一行。
由此可以知道游標工作正常，出錯處理也工作正常。
?
Cursor Characteristics 游標的特性
摘要：轉載請注明翻譯者陳朋奕及轉自:www.phpv.net
READ ONLY只讀屬性
NOT SCROLLABLE順序讀取
ASENSITIVE敏感
在5.0版的MySQL中，你只可以從游標中取值，不能對其進行更新。因為游標是（READ
ONLY）只讀的。你可以這樣做：
FETCH cursor1 INTO variable1;
UPDATE t1 SET column1 = 'value1' WHERE CURRENT OF cursor1;
游標也是不可以滾動的，只允許逐一讀取下一行，不能在結果集中前進或后退。下面代碼就
是錯誤的：
FETCH PRIOR cursor1 INTO variable1;
FETCH ABSOLUTE 55 cursor1 INTO variable1;
同時也不允許在已打開游標進行操作的表上執行updates事務，因為游標是（ASENSITIVE）敏感的。因為如果你不阻止update事務，那就不知道結果會變成什么。如果你使用的是InnoDB
而不是MyISAM存儲引擎的話，結果也會不一樣。
Security 安全措施
摘要
Privileges (1) CREATE ROUTINE
Privileges (2) EXECUTE
Privileges (3) GRANT SHOW ROUTINE?
Privileges (4) INVOKERS AND DEFINERS
這里我們要討論一些關于特權和安全相關的問題。但因為在MySQL安全措施的功能并沒有完全，所以我們不會對其進行過多討論。
1. Privileges CREATE ROUTINE
GRANT CREATE ROUTINE
ON database-name . *
TO user(s)
[WITH GRANT OPTION];
現在用root就可以了
在這里要介紹的特權是CREATE ROUTINE，它不僅同其他特權一樣可以創建存儲過程和函數，
還可以創建視圖和表。Root用戶擁有這種特權，同時還有ALTER ROUTINE特權。
2. Privileges EXECUTE
GRANT EXECUTE ON p TO peter
[WITH GRANT OPTION];
上面的特權是決定你是否可以使用或執行存儲過程的特權，過程創建者默認擁有這個特權。
3. Privileges SHOW ROUTINE?
GRANT SHOW ROUTINE ON db6.* TO joey
[WITH GRANT OPTION];
因為我們已經有控制視圖的特權了：GRANT SHOW VIEW。所以在這個基礎上，為了保證兼容，
日后可能會添加GRANT SHOW ROUTINE特權。這樣做是不太符合標準的，在寫本書的時候，MySQL還沒實現這個功能。
4. Privileges Invokers and Definers 特權調用者和定義者
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; //
現在我們測試一下SQL SECURITY子句吧。Security是我們前面提到的程序特性的一部分。你root
用戶，將插入權賦給了peter。然后使用peter登陸進行新的工作，我們看peter可以怎么使用存儲過程，注意：peter沒有對表t的select權力，只有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)
當peter嘗試調用含有調用保密措施的過程p26時會失敗。那是因為peter沒有對表的select的權力。
但是當petre調用含有定義保密措施的過程時就能成功。原因是root有select權力，Peter有root的
權力，因此過程可以執行。
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
［From］http://www.linuxeden.com/doc/23907.html
mysql5存儲過程編寫實踐
作者：松哥
email：jccz_zys@tom.com
MSN:?jccz_zys@163.net
QQ:?15210449
MySql5.0以后均支持存儲過程，最近有空，研究了一把這個
?
格式：
????????
CREATE?PROCEDURE?過程名?([過程參數[,...]])
????[特性?...]?過程體
CREATE?FUNCTION?函數名?([函數參數[,...]])
????RETURNS?返回類型
????[特性?...]?函數體
????
過程參數:
????[?IN?|?OUT?|?INOUT?]?參數名?參數類型
????
函數參數:
????參數名?參數類型
返回類型:
????有效的MySQL數據類型即可
特性:
????LANGUAGE?SQL
??|?[NOT]?DETERMINISTIC
??|?{?CONTAINS?SQL?|?NO?SQL?|?READS?SQL?DATA?|?MODIFIES?SQL?DATA?}
??|?SQL?SECURITY?{?DEFINER?|?INVOKER?}
??|?COMMENT?'string'
過程體/函數體:格式如下：
BEGIN
????有效的SQL語句
END????
????
我在這里不關心專有的特性，這些與SQL規范不兼容，所以characteristic(特性)的相關內容不作考慮。
//
在開發過程中有幾點要注意：
1、存儲過程注釋:MySQL支持采用--或者/**/注釋，其中前者是行注釋，后者是段式注釋
2、變量首先用declare申明，其中臨時變量可以直接以@前綴修飾以供引用
3、直接采用MySQL的Administrator管理器編輯時，可以直接采用如下函數文本錄入；
???但若在腳本中自動導入存儲過程或函數時，由于MySQL默認以";"為分隔符，則過程體的每一句
???都被MySQL以存儲過程編譯，則編譯過程會報錯；所以要事先用DELIMITER關鍵字申明當前段分隔符
???用完了就把分隔符還原。??如下所示：
???????DELIMITER?＄＄
???????Stored?Procedures?and?Functions
???????DELIMITER?;
4、MySQL支持大量的內嵌函數，有些是和大型商用數據庫如oracle、informix、sybase等一致，
???但也有些函數名稱不一致，但功能一致；或者有些名稱一致，但功能相異，這個特別對于從
???這些數據庫開發轉過來的DBA要注意。
5、存儲過程或函數的調試：我目前還沒有研究MySQL所帶的各種工具包，還不清楚其提供了調試工具
???沒有，不過編譯錯誤相對好查找；至于業務流程的調試，可以采用一個比較笨的方法，就是創建一
???個調試表，在包體中各個流程點都插入一條記錄，以觀察程序執行流程。這也是一個比較方便的笨
???辦法。^_^
???
???下面是2個例子，提供了一種字串加密的算法，每次以相同的入參調用都會得到不同的加密結果，
???算法相對比較簡單，不具備強度。分別以函數和過程的形式分別實現如下：
(1)函數
eg:
CREATE?FUNCTION?fun_addmm(inpass?varchar(10))?RETURNS?varchar(11)
BEGIN
?????declare?string_in?varchar(39);
?????declare?string_out?varchar(78);
?????declare?offset?tinyint(2);
?????declare?outpass?varchar(30)?default?';
?????declare?len?tinyint;
?????/*declare?i?tinyint;*/
?????/**/
?????set?len=LENGTH(inpass);
?????if((len<=0)?or?(len>10))?then
?????????return?"";
?????end?if;
?????set?offset=(SECOND(NOW())?mod?39)+1;?/*根據秒數取模*/
?????/*insert?into??testtb?values(offset,'offset:?');*/
?????set?string_out='YN8K1JOZVURB3MDETS5GPL27AXWIHQ94C6F0#＄_';??/*密鑰*/
?????set?string_in='_＄#ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
?????set?outpass=CONCAT(outpass,SUBSTRING(string_out,offset,1));
/*?????insert?into??testtb?values(2,outpass);*/
?????set?string_out=CONCAT(string_out,string_out);
?????set?@i=0;
?????REPEAT
???????set?@i=@i+1;
???????set?outpass=CONCAT(outpass,SUBSTR(string_out,INSTR(string_in,SUBSTRING(inpass,@i,1))+offset,1));
/*???????insert?into??testtb?values(@i+2,outpass);*/
?????UNTIL?(@i>=len)
?????end?REPEAT;
?????return?outpass;
END?
(2)過程
CREATE?PROCEDURE?`pro_addmm`(IN?inpass?varchar(10),OUT?outpass?varchar(11))
BEGIN
?????declare?string_in?varchar(39);
?????declare?string_out?varchar(78);
?????declare?offset?tinyint(2);????????????????
?????declare?len?tinyint;
?????set?outpass=';
?????set?len=LENGTH(inpass);
?????if((len<=0)?or?(len>10))?then
?????????set?outpass=';
?????else
?????????set?offset=(SECOND(NOW())?mod?39)+1;
?????????set?string_out='YN8K1JOZVURB3MDETS5GPL27AXWIHQ94C6F0#＄_';
?????????set?string_in='_＄#ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
?????????set?outpass=CONCAT(outpass,SUBSTRING(string_out,offset,1));
?????????set?string_out=CONCAT(string_out,string_out);
?????????set?@i=0;
?????????REPEAT
???????????????set?@i=@i+1;
???????????????set?outpass=CONCAT(outpass,SUBSTR(string_out,INSTR(string_in,SUBSTRING(inpass,@i,1))+offset,1));
?????????UNTIL?(@i>=len)
?????????end?REPEAT;
?????end?if;
END
//
執行結果如下：
mysql>?call?pro_addmm('zhouys',@a);
Query?OK,?0?rows?affected?(0.00?sec)
mysql>?SELECT?@a;
+---------+
|?@a??????|
+---------+
|?U_PI6＄4?|
+---------+
1?row?in?set?(0.00?sec)
mysql>?call?pro_addmm('zhouys',@a);
Query?OK,?0?rows?affected?(0.00?sec)
mysql>?SELECT?@a;
+---------+
|?@a??????|
+---------+
|?9P8UEGM?|
+---------+
1?row?in?set?(0.00?sec)
mysql>?select?fun_submm('U_PI6＄4');
+----------------------+
|?fun_submm('U_PI6＄4')?|
+----------------------+
|?ZHOUYS???????????????|
+----------------------+
1?row?in?set?(0.00?sec)
加密算法有幾個弱點：
1、不支持大小寫
2、不支持中文
3、加密強度不夠
有興趣的人可以研究一下解密函數如何編寫，這里就不贅述了。