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標準中把這個定義為非核心的，即可選組件。

在過程體中有一個約束，就是不能有對例程或表操作的數據庫操作語句。例如下面的例子就是非法的：

create procedure p1 ()

create procedure p2 () delete from t; //

下面這些對mysql 5.0來說全新的語句，過程體中是非法的:

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;"

好了，主要的知識點"創建和調用過程方法"已經清楚了。我希望你能對自己說這相當簡單。但是很快我們就有一系列的練習，每次都加一條子句，或者改變已經存在的子句。那樣在寫復雜部件前我們將會有很多可用的子句。

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內置的優化程序不會注意這個，至少在現在不注意。

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）則是告訴服務器在這一步仍然要檢查調用者的權限。

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一些題外話

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，不用擔心，它仍然能工作，只要它第一次使用時能正常工作。

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中。

在過程體中，我們給參數賦值-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塊也被稱為復合語句，在這里你可以進行變量定義和流程控制。

未完待續...