我的MYSQL學習心得（十）
?
?
這一篇《我的MYSQL學習心得（十）》將會講解MYSQL的存儲過程和函數
MYSQL中創建存儲過程和函數分別使用CREATE PROCEDURE和CREATE FUNCTION
使用CALL語句來調用存儲過程，存儲過程也可以調用其他存儲過程
函數可以從語句外調用，能返回標量值
創建存儲過程
語法
CREATE
PROCEDURE
sp_name (
[
proc_parameter
]
)
[
characteristics..
]
routine_body
?proc_parameter指定存儲過程的參數列表，列表形式如下：
[
IN|OUT|INOUT
]
param_name type
其中in表示輸入參數，out表示輸出參數，inout表示既可以輸入也可以輸出；param_name表示參數名稱；type表示參數的類型
該類型可以是MYSQL數據庫中的任意類型
有以下取值：
characteristic:
LANGUAGE SQL
|
[
NOT
]
DETERMINISTIC
|
{
CONTAINS
SQL
|
NO SQL
|
READS SQL DATA
|
MODIFIES SQL DATA }
|
SQL SECURITY { DEFINER
|
INVOKER }
|
COMMENT
'
string
'
routine_body:
Valid SQL
procedure
statement
or
statements
LANGUAGE SQL ：說明routine_body部分是由SQL語句組成的，當前系統支持的語言為SQL，SQL是LANGUAGE特性的唯一值
[NOT] DETERMINISTIC ：指明存儲過程執行的結果是否正確。DETERMINISTIC 表示結果是確定的。每次執行存儲過程時，相同的輸入會得到
相同的輸出。
[NOT] DETERMINISTIC 表示結果是不確定的，相同的輸入可能得到不同的輸出。如果沒有指定任意一個值，默認為[NOT] DETERMINISTIC?
CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA ：指明子程序使用SQL語句的限制。
CONTAINS SQL表明子程序包含SQL語句，但是不包含讀寫數據的語句；
NO SQL表明子程序不包含SQL語句；
READS SQL DATA：說明子程序包含讀數據的語句；
MODIFIES SQL DATA表明子程序包含寫數據的語句。
默認情況下，系統會指定為CONTAINS SQL
SQL SECURITY { DEFINER | INVOKER } ：指明誰有權限來執行。DEFINER 表示只有定義者才能執行
INVOKER 表示擁有權限的調用者可以執行。默認情況下，系統指定為DEFINER?
COMMENT 'string' ：注釋信息，可以用來描述存儲過程或函數
?
routine_body是SQL代碼的內容，可以用BEGIN...END來表示SQL代碼的開始和結束。
?
下面的語句創建一個查詢t1表全部數據的存儲過程
DROP
PROCEDURE
IF
EXISTS
Proc
;
DELIMITER
//
CREATE
PROCEDURE
Proc
()
BEGIN
SELECT
*
FROM
t3;
END
//
DELIMITER ;
CALL
Proc
();
t3表是我們上一節創建的表
這里的邏輯是
1、先判斷是否有Proc() 這個存儲過程，有就drop掉
2、創建Proc() 存儲過程
3、執行Proc()?存儲過程
?
注意：“DELIMITER //”語句的作用是將MYSQL的結束符設置為//，因為MYSQL默認的語句結束符為分號;，為了避免與存儲過程
中SQL語句結束符相沖突，需要使用DELIMITER 改變存儲過程的結束符，并以“END//”結束存儲過程。
存儲過程定義完畢之后再使用DELIMITER ;恢復默認結束符。DELIMITER 也可以指定其他符號為結束符！！！！！！！！！！！
?
?
?如果你是這樣寫的話，就會得到如下錯誤，初學者很容易犯這個錯誤，包括本人
CREATE
PROCEDURE
Proc
()
BEGIN
SELECT
*
FROM
t3;
END
Query:
CREATE
PROCEDURE
Proc
()
BEGIN
SELECT
*
FROM
t3
Error Code:
1064
You have an error
in
your SQL syntax;
check
the manual that corresponds
to
your MySQL server version
for
the
right
syntax
to
use
near
''
at line
3
Execution Time :
0
sec
Transfer Time :
0
sec
Total Time :
0.001
sec
--
-------------------------------------------------
Query:
END
Error Code:
1064
?
創建名為CountProc的存儲過程，代碼如下：
DELIMITER
//
CREATE
PROCEDURE
CountProc(OUT param1
INT
)
BEGIN
SELECT
COUNT
(
*
)
INTO
param1
FROM
t3;
END
//
DELIMITER ;
上面代碼的作用是創建一個獲取t3表記錄數的存儲過程，名稱是CountProc，
COUNT(*)計算后把結果放入參數param1中。
?
注意：當使用DELIMITER命令時，應該避免使用反斜杠（\）字符，因為反斜杠是MYSQL的轉義字符！！！
存儲函數
創建存儲函數，需要使用CREATE FUNCTION語句，基本語法如下：
CREATE
FUNCTION
func_name(
[
func_parameter
]
)
RETURNS
TYPE
[
characteristics...
]
routine_body
CREATE FUNCTION為用來創建存儲函數的關鍵字；func_name表示存儲函數的名稱
func_parameter為存儲函數的參數列表，參數列表如下
[
IN|OUT|INOUT
]
PARAM_NAMETYPE
其中，IN表示輸入參數，OUT表示輸出參數，INOUT表示既可以輸入也可以輸出；
param_name表示參數名稱；type表示參數類型，該類型可以是MYSQL數據庫中的任意類型
RETURNS TYPE語句表示函數返回數據的類型；characteristics：指定存儲函數的特性，取值與創建存儲過程時相同
?
創建存儲函數，名稱為NameByT，該函數返回SELECT語句的查詢結果，數值類型為字符串型
DELIMITER
//
CREATE
FUNCTION
NameByT()
RETURNS
CHAR
(
50
)
RETURN
(
SELECT
NAME
FROM
t3
WHERE
id
=
2
);
//
DELIMITER ;
注意：RETURNS CHAR(50)數據類型的時候，RETURNS 是有S的，而RETURN (SELECT NAME FROM t3 WHERE id=2)的時候RETURN是沒有S的
所以有時候大家可能覺得MYSQL很煩，誰不知是自己寫錯了
這里有一個方法，就是利用SQLYOG的代碼格式化功能，選中要格式化的代碼，然后按F12，如果能格式化，證明你的代碼沒有問題，如果不能格式化
證明你寫的代碼有問題！！！
不加s的話就會出現語法錯誤了
Query:
create
function
NameByT()
return
char
(
50
)
return
(
select
name
from
t3
where
id
=
2
)
Error Code:
1064
You have an error
in
your SQL syntax;
check
the manual that corresponds
to
your MySQL server version
for
the
right
syntax
to
use
near
'
return char(50)
return (select name from t3 where id=2)
'
at line
2
Execution Time :
0
sec
Transfer Time :
0
sec
Total Time :
0.003
sec
--
---------------------------
調用函數
SELECT
nameByT()
?
如果在存儲函數中的RETURN語句返回一個類型不同于函數的RETURNS子句中指定類型的值，返回值將被強制轉換為恰當的類型。
例如，如果一個函數返回一個SET或ENUM值，但是RETURN語句返回一個整數，對于SET成員集的相應ENUM成員，從函數返回的值
是字符串。
指定參數為IN、OUT、INOUT只對PROCEDURE是合法的。
（FUNCTION中總是默認是IN參數）RETURNS子句對FUNCTION做指定，對函數而言這是強制的。
他用來指定函數的返回類型，而且函數體必須包含一個RETURN value語句
變量的使用
變量可以在子程序中聲明并使用，這些變量的作用范圍是在BEGIN...END程序中
1、定義變量
在存儲過程中定義變量
DECLARE
var_name
[
,varname
]
...date_type
[
DEFAULT VALUE
]
;
var_name為局部變量的名稱。DEFAULT VALUE子句給變量提供一個默認值。值除了可以被聲明為一個常數外，還可以被指定為一個表達式。
如果沒有DEFAULT子句，初始值為NULL
DECLARE
MYPARAM
INT
DEFAULT
100
;
?
2、為變量賦值
定義變量之后，為變量賦值可以改變變量的默認值，MYSQL中使用SET語句為變量賦值
SET
var_name
=
expr
[
,var_name=expr
]
...
在存儲過程中的SET語句是一般SET語句的擴展版本。
被SET的變量可能是子程序內的變量，或者是全局服務器變量，如系統變量或者用戶變量
?
他運行SET a=x,b=y,....
聲明3個變量，分別為var1，var2和var3
DECLARE
var1,var2,var3
INT
;
SET
var1
=
10
,var2
=
20
;
SET
var3
=
var1
+
var2;
?MYSQL中還可以通過SELECT...INTO為一個或多個變量賦值
DECLARE
NAME
CHAR
(
50
);
DECLARE
id
DECIMAL
(
8
,
2
);
SELECT
id,NAME
INTO
id ,NAME
FROM
t3
WHERE
id
=
2
;
定義條件和處理程序
特定條件需要特定處理。這些條件可以聯系到錯誤，以及子程序中的一般流程控制。定義條件是事先定義程序執行過程中遇到的問題，
處理程序定義了在遇到這些問題時候應當采取的處理方式，并且保證存儲過程或函數在遇到警告或錯誤時能繼續執行。
這樣可以增強存儲程序處理問題的能力，避免程序異常停止運行
?
1、定義條件
DECLARE
condition_name CONDITION
FOR
[
condition_type
]
[
condition_type
]
:
SQLSTATE
[
VALUE
]
sqlstate_value
|
mysql_error_code
condition_name：表示條件名稱
condition_type：表示條件的類型
sqlstate_value和mysql_error_code都可以表示mysql錯誤
sqlstate_value為長度5的字符串錯誤代碼
mysql_error_code為數值類型錯誤代碼，例如：ERROR1142(42000)中，sqlstate_value的值是42000，
mysql_error_code的值是1142
?
這個語句指定需要特殊處理條件。他將一個名字和指定的錯誤條件關聯起來。
這個名字隨后被用在定義處理程序的DECLARE HANDLER語句中
?
定義ERROR1148(42000)錯誤，名稱為command_not_allowed。
可以用兩種方法定義
//
方法一：使用sqlstate_value
DECLARE
command_not_allowed CONDITION
FOR
SQLSTATE
'
42000
'
//
方法二：使用mysql_error_code
DECLARE
command_not_allowed CONDITION
FOR
SQLSTATE
1148
?
2．定義處理程序
MySQL中可以使用DECLARE關鍵字來定義處理程序。其基本語法如下：
DECLARE
handler_type HANDLER
FOR
condition_value
[
,...
]
sp_statement
handler_type:
CONTINUE
|
EXIT
|
UNDO
condition_value:
SQLSTATE
[
VALUE
]
sqlstate_value
|
condition_name
|
SQLWARNING
|
NOT
FOUND
|
SQLEXCEPTION
|
mysql_error_code
其中，handler_type參數指明錯誤的處理方式，該參數有3個取值。這3個取值分別是CONTINUE、EXIT和UNDO。
CONTINUE表示遇到錯誤不進行處理，繼續向下執行；
EXIT表示遇到錯誤后馬上退出；
UNDO表示遇到錯誤后撤回之前的操作，MySQL中暫時還不支持這種處理方式。
注意：通常情況下，執行過程中遇到錯誤應該立刻停止執行下面的語句，并且撤回前面的操作。
但是，MySQL中現在還不能支持UNDO操作。
因此，遇到錯誤時最好執行EXIT操作。如果事先能夠預測錯誤類型，并且進行相應的處理，那么可以執行CONTINUE操作。
condition_value參數指明錯誤類型，該參數有6個取值。
sqlstate_value和mysql_error_code與條件定義中的是同一個意思。
condition_name是DECLARE定義的條件名稱。
SQLWARNING表示所有以01開頭的sqlstate_value值。
NOT FOUND表示所有以02開頭的sqlstate_value值。
SQLEXCEPTION表示所有沒有被SQLWARNING或NOT FOUND捕獲的sqlstate_value值。
sp_statement表示一些存儲過程或函數的執行語句。
下面是定義處理程序的幾種方式。代碼如下：
//
方法一：捕獲sqlstate_value
DECLARE
CONTINUE
HANDLER
FOR
SQLSTATE
'
42000
'
SET
@info
=
'
CAN NOT FIND
'
;
//
方法二：捕獲mysql_error_code
DECLARE
CONTINUE
HANDLER
FOR
1148
SET
@info
=
'
CAN NOT FIND
'
;
//
方法三：先定義條件，然后調用
DECLARE
can_not_find CONDITION
FOR
1146
;
DECLARE
CONTINUE
HANDLER
FOR
can_not_find
SET
@info
=
'
CAN NOT FIND
'
;
//
方法四：使用SQLWARNING
DECLARE
EXIT
HANDLER
FOR
SQLWARNING
SET
@info
=
'
ERROR
'
;
//
方法五：使用NOT FOUND
DECLARE
EXIT
HANDLER
FOR
NOT
FOUND
SET
@info
=
'
CAN NOT FIND
'
;
//
方法六：使用SQLEXCEPTION
DECLARE
EXIT
HANDLER
FOR
SQLEXCEPTION
SET
@info
=
'
ERROR
'
;
上述代碼是6種定義處理程序的方法。
第一種方法是捕獲sqlstate_value值。如果遇到sqlstate_value值為42000，執行CONTINUE操作，并且輸出"CAN NOT FIND"信息。
第二種方法是捕獲mysql_error_code值。如果遇到mysql_error_code值為1148，執行CONTINUE操作，并且輸出"CAN NOT FIND"信息。
第三種方法是先定義條件，然后再調用條件。這里先定義can_not_find條件，遇到1148錯誤就執行CONTINUE操作。
第四種方法是使用SQLWARNING。SQLWARNING捕獲所有以01開頭的sqlstate_value值，然后執行EXIT操作，并且輸出"ERROR"信息。
第五種方法是使用NOT FOUND。NOT FOUND捕獲所有以02開頭的sqlstate_value值，然后執行EXIT操作，并且輸出"CAN NOT FIND"信息。
第六種方法是使用SQLEXCEPTION。SQLEXCEPTION捕獲所有沒有被SQLWARNING或NOT FOUND捕獲的sqlstate_value值，然后執行EXIT操作，并且輸出"ERROR"信息
?
定義條件和處理程序
?
CREATE
TABLE
t8(s1
INT
,
PRIMARY
KEY
(s1))
DELIMITER
//
CREATE
PROCEDURE
handlerdemo()
BEGIN
DECLARE
CONTINUE
HANDLER
FOR
SQLSTATE
'
23000
'
SET
@X2
=
1
;
SET
@X
=
1
;
INSERT
INTO
t8
VALUES
(
1
);
SET
@X
=
2
;
INSERT
INTO
t8
VALUES
(
1
);
SET
@X
=
3
;
END
;
//
DELIMITER ;
/*
調用存儲過程
*/
CALL handlerdemo();
/*
查看調用存儲過程結果
*/
SELECT
@X
?
@X是一個用戶變量，執行結果@X等于3，這表明MYSQL執行到程序的末尾。
如果DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @X2=1;，這一行不存在
第二個INSERT因PRIMARY KEY約束而失敗之后，MYSQL可能已經采取EXIT策略，并且SELECT?@X可能已經返回2
注意：@X表示用戶變量，使用SET語句為其賦值，用戶變量與連接有關，一個客戶端定義的變量不能被其他客戶端所使用
即有作用域的，該客戶端退出時，客戶端連接的所有變量將自動釋放
?
這里的變量跟SQLSERVER沒有什么區別，都是用來存儲臨時值的
MYSQL這里的條件和預定義程序其實跟 SQLSERVER的自定義錯誤 是一樣的
光標
MYSQL里叫光標，SQLSERVER里叫游標，實際上一樣的
?
查詢語句可能查詢出多條記錄，在存儲過程和函數中使用光標來逐條讀取查詢結果集中的記錄。
光標的使用包括聲明光標、打開光標、使用光標和關閉光標。光標必須聲明在處理程序之前，并且聲明在變量和條件之后。
1．聲明光標
MySQL中使用DECLARE關鍵字來聲明光標。其語法的基本形式如下：
DECLARE
cursor_name
CURSOR
FOR
select_statement ;
其中，cursor_name參數表示光標的名稱；select_statement參數表示SELECT語句的內容，返回一個用于創建光標的結果集
下面聲明一個名為cur_employee的光標。代碼如下：
DECLARE
cur_employee
CURSOR
FOR
SELECT
name, age
FROM
employee ;
上面的示例中，光標的名稱為cur_employee；SELECT語句部分是從employee表中查詢出name和age字段的值。
?
2．打開光標
MySQL中使用OPEN關鍵字來打開光標。其語法的基本形式如下：
OPEN
cursor_name ;
其中，cursor_name參數表示光標的名稱。
下面打開一個名為cur_employee的光標，代碼如下：
OPEN
cur_employee ;
?
3．使用光標
MySQL中使用FETCH關鍵字來使用光標。其語法的基本形式如下：
?
FETCH
cur_employee
INTO
var_name
[
,var_name…
]
;
?
其中，cursor_name參數表示光標的名稱；var_name參數表示將光標中的SELECT語句查詢出來的信息存入該參數中。var_name必須在聲明光標之前就定義好。
下面使用一個名為cur_employee的光標。將查詢出來的數據存入emp_name和emp_age這兩個變量中，代碼如下：
FETCH
cur_employee
INTO
emp_name, emp_age ;
上面的示例中，將光標cur_employee中SELECT語句查詢出來的信息存入emp_name和emp_age中。emp_name和emp_age必須在前面已經定義。
4．關閉光標
MySQL中使用CLOSE關鍵字來關閉光標。其語法的基本形式如下：
?
CLOSE
cursor_name ;
?
其中，cursor_name參數表示光標的名稱。
【示例14-11】 下面關閉一個名為cur_employee的光標。代碼如下：
CLOSE
cur_employee ;
上面的示例中，關閉了這個名稱為cur_employee的光標。關閉之后就不能使用FETCH來使用光標了。
?
注意：MYSQL中，光標只能在存儲過程和函數中使用！！
?
到目前為止存儲函數，存儲過程、變量、條件、預定義程序、光標跟SQLSERVER差不多，只不過語法不同，結構不同
剛開始的時候會有不適應
流程控制的使用
存儲過程和函數中可以使用流程控制來控制語句的執行。
MySQL中可以使用IF語句、CASE語句、LOOP語句、LEAVE語句、ITERATE語句、REPEAT語句和WHILE語句來進行流程控制。
每個流程中可能包含一個單獨語句，或者是使用BEGIN...END構造的復合語句，構造可以被嵌套
1．IF語句
IF語句用來進行條件判斷。根據是否滿足條件，將執行不同的語句。其語法的基本形式如下：
IF
search_condition
THEN
statement_list
[
ELSEIF search_condition THEN statement_list
]
...
[
ELSE statement_list
]
END
IF
其中，search_condition參數表示條件判斷語句；statement_list參數表示不同條件的執行語句。
注意：MYSQL還有一個IF()函數，他不同于這里描述的IF語句
下面是一個IF語句的示例。代碼如下：
IF
age
>
20
THEN
SET
@count1
=
@count1
+
1
;
ELSEIF age
=
20
THEN SET
@count2
=
@count2
+
1
;
ELSE SET
@count3
=
@count3
+
1
;
END
IF
;
該示例根據age與20的大小關系來執行不同的SET語句。
如果age值大于20，那么將count1的值加1；如果age值等于20，那么將count2的值加1；
其他情況將count3的值加1。IF語句都需要使用END IF來結束。
2．CASE語句
CASE語句也用來進行條件判斷，其可以實現比IF語句更復雜的條件判斷。CASE語句的基本形式如下：
CASE
case_value
WHEN
when_value
THEN
statement_list
[
WHEN when_value THEN statement_list
]
...
[
ELSE statement_list
]
END
CASE
其中，case_value參數表示條件判斷的變量；
when_value參數表示變量的取值；
statement_list參數表示不同when_value值的執行語句。
CASE語句還有另一種形式。該形式的語法如下：
CASE
WHEN
search_condition
THEN
statement_list
[
WHEN search_condition THEN statement_list
]
...
[
ELSE statement_list
]
END
CASE
其中，search_condition參數表示條件判斷語句；
statement_list參數表示不同條件的執行語句。
下面是一個CASE語句的示例。代碼如下：
CASE
age
WHEN
20
THEN
SET
@count1
=
@count1
+
1
;
ELSE
SET
@count2
=
@count2
+
1
;
END
CASE
;
代碼也可以是下面的形式：
CASE
WHEN
age
=
20
THEN
SET
@count1
=
@count1
+
1
;
ELSE
SET
@count2
=
@count2
+
1
;
END
CASE
;
本示例中，如果age值為20，count1的值加1；否則count2的值加1。CASE語句都要使用END CASE結束。
注意：這里的CASE語句和“控制流程函數”里描述的SQL CASE表達式的CASE語句有輕微不同。這里的CASE語句不能有ELSE NULL子句
并且用END CASE替代END來終止！！
?
3．LOOP語句
LOOP語句可以使某些特定的語句重復執行，實現一個簡單的循環。
但是LOOP語句本身沒有停止循環的語句，必須是遇到LEAVE語句等才能停止循環。
LOOP語句的語法的基本形式如下：
[
begin_label:
]
LOOP
statement_list
END
LOOP
[
end_label
]
其中，begin_label參數和end_label參數分別表示循環開始和結束的標志，這兩個標志必須相同，而且都可以省略；
statement_list參數表示需要循環執行的語句。
下面是一個LOOP語句的示例。代碼如下：
add_num: LOOP
SET
@count
=
@count
+
1
;
END
LOOP add_num ;
該示例循環執行count加1的操作。因為沒有跳出循環的語句，這個循環成了一個死循環。
LOOP循環都以END LOOP結束。
?
4．LEAVE語句
LEAVE語句主要用于跳出循環控制。其語法形式如下：
LEAVE label
其中，label參數表示循環的標志。
?
下面是一個LEAVE語句的示例。代碼如下：
add_num: LOOP
SET
@count
=
@count
+
1
;
IF
@count
=
100
THEN
LEAVE add_num ;
END
LOOP add_num ;
該示例循環執行count加1的操作。當count的值等于100時，則LEAVE語句跳出循環。
?
5．ITERATE語句
ITERATE語句也是用來跳出循環的語句。但是，ITERATE語句是跳出本次循環，然后直接進入下一次循環。
ITERATE語句只可以出現在LOOP、REPEAT、WHILE語句內。
ITERATE語句的基本語法形式如下：
ITERATE label
其中，label參數表示循環的標志。
下面是一個ITERATE語句的示例。代碼如下：
add_num: LOOP
SET
@count
=
@count
+
1
;
IF
@count
=
100
THEN
LEAVE add_num ;
ELSE
IF
MOD(
@count
,
3
)
=
0
THEN
ITERATE add_num;
SELECT
*
FROM
employee ;
END
LOOP add_num ;
該示例循環執行count加1的操作，count值為100時結束循環。如果count的值能夠整除3，則跳出本次循環，不再執行下面的SELECT語句。
說明：LEAVE語句和ITERATE語句都用來跳出循環語句，但兩者的功能是不一樣的。
LEAVE語句是跳出整個循環，然后執行循環后面的程序。而ITERATE語句是跳出本次循環，然后進入下一次循環。
使用這兩個語句時一定要區分清楚。
?
6．REPEAT語句
REPEAT語句是有條件控制的循環語句。當滿足特定條件時，就會跳出循環語句。REPEAT語句的基本語法形式如下：
[
begin_label:
]
REPEAT
statement_list
UNTIL search_condition
END
REPEAT
[
end_label
]
其中，statement_list參數表示循環的執行語句；search_condition參數表示結束循環的條件，滿足該條件時循環結束。
下面是一個ITERATE語句的示例。代碼如下：
REPEAT
SET
@count
=
@count
+
1
;
UNTIL
@count
=
100
END
REPEAT ;
該示例循環執行count加1的操作，count值為100時結束循環。
REPEAT循環都用END REPEAT結束。
?
7．WHILE語句
WHILE語句也是有條件控制的循環語句。但WHILE語句和REPEAT語句是不一樣的。
WHILE語句是當滿足條件時，執行循環內的語句。
WHILE語句的基本語法形式如下：
[
begin_label:
]
WHILE
search_condition DO
statement_list
END
WHILE
[
end_label
]
其中，search_condition參數表示循環執行的條件，滿足該條件時循環執行；
statement_list參數表示循環的執行語句。
下面是一個ITERATE語句的示例。代碼如下：
WHILE
@count
<
100
DO
SET
@count
=
@count
+
1
;
END
WHILE
;
該示例循環執行count加1的操作，count值小于100時執行循環。
如果count值等于100了，則跳出循環。WHILE循環需要使用END WHILE來結束。
調用存儲過程和函數
存儲過程和存儲函數都是存儲在服務器端的SQL語句的集合，要使用這些已經定義好的存儲過程和存儲函數就必須要通過調用的方式來實現
存儲過程是通過CALL語句來調用的。而存儲函數的使用方法與MySQL內部函數的使用方法是一樣的
執行存儲過程和存儲函數需要擁有EXECUTE權限
EXECUTE權限的信息存儲在information_schema數據庫下面的 USER_PRIVILEGES 表中
?
調用存儲過程
MySQL中使用CALL語句來調用存儲過程。調用存儲過程后，數據庫系統將執行存儲過程中的語句。
然后，將結果返回給輸出值。
CALL語句的基本語法形式如下：
CALL sp_name(
[
parameter[,…
]
]) ;
其中，sp_name是存儲過程的名稱；parameter是指存儲過程的參數。
CALL
proc
()
?
?
調用存儲函數
在MySQL中，存儲函數的使用方法與MySQL內部函數的使用方法是一樣的。
換言之，用戶自己定義的存儲函數與MySQL內部函數是一個性質的。
區別在于，存儲函數是用戶自己定義的，而內部函數是MySQL的開發者定義的。
下面定義一個存儲函數，然后調用這個存儲函數。
代碼執行如下：
--
創建存儲函數
DELIMITER
//
CREATE
FUNCTION
name_from_t3(id
INT
)
RETURNS
CHAR
(
80
)
RETURN
(
SELECT
NAME
FROM
t3
WHERE
id
=
id );
//
DELIMITER ;
SELECT
name_from_t3(
2
);
上述存儲函數的作用是根據輸入的id值到t3表中查詢記錄。
查詢出id字段的值等于id的記錄。然后將該記錄的name字段的值返回。
查看存儲過程和函數
存儲過程和函數創建以后，可以查看存儲過程和函數的狀態和定義。
通過SHOW STATUS語句來查看存儲過程和函數的狀態，也可以通過SHOW CREATE語句來查看存儲過程和函數的定義。
通過查詢information_schema數據庫下的Routines表來查看存儲過程和函數的信息
?
1、SHOW STATUS語句查看存儲過程和函數的狀態
MySQL中可以通過SHOW STATUS語句查看存儲過程和函數的狀態。其基本語法形式如下：
SHOW {
PROCEDURE
|
FUNCTION
} STATUS
[
LIKE ' pattern '
]
;
其中，PROCEDURE參數表示查詢存儲過程；FUNCTION參數表示查詢存儲函數；
LIKE ' pattern '參數用來匹配存儲過程或函數的名稱。
?
下面查詢名為name_from_t3的函數的狀態。代碼執行如下：
SHOW
FUNCTION
STATUS
LIKE
'
%name_from_t3%
'
Db Name Type Definer Modified Created Security_type Comment character_set_client collation_connection
Database
Collation
--
---- ------------ -------- -------------- ------------------- ------------------- ------------- ------- -------------------- -------------------- ------------------
school name_from_t3
FUNCTION
root
@localhost
2014
-
06
-
21
18
:
52
:
39
2014
-
06
-
21
18
:
52
:
39
DEFINER utf8 utf8_general_ci utf8_general_ci
查詢結果顯示了函數的創建時間、修改時間和字符集等信息。
?
注意：SHOW STATUS語句只能查看存儲過程或函數是操作哪一個數據庫、存儲過程或函數的名稱、類型、誰定義的、創建和修改時間、字符編碼等信息。
但是，這個語句不能查詢存儲過程或函數的具體定義。如果需要查看詳細定義，需要使用SHOW CREATE語句
?
2、SHOW CREATE語句查看存儲過程和函數的定義
MySQL中可以通過SHOW CREATE語句查看存儲過程和函數的狀態。其基本語法形式如下：
SHOW
CREATE
{
PROCEDURE
|
FUNCTION
} sp_name ;
其中，PROCEDURE參數表示查詢存儲過程；
FUNCTION參數表示查詢存儲函數；
sp_name參數表示存儲過程或函數的名稱
?
下面查詢名為name_from_t3的函數的定義。代碼執行如下
SHOW
CREATE
FUNCTION
name_from_t3
Function
sql_mode
Create
Function
character_set_client collation_connection
Database
Collation
--
---------- -------- ----------------------------------------------------------------------------------------------------------------------------------------------- -------------------- -------------------- ------------------
name_from_t3
CREATE
DEFINER
=
`root`@`localhost`
FUNCTION
`name_from_t3`(id
INT
)
RETURNS
char
(
80
) CHARSET utf8 utf8 utf8_general_ci utf8_general_ci
RETURN
(
SELECT
NAME
FROM
t3
WHERE
id
=
id )
?
3、從information_schema.Routines表中查看存儲過程和函數的信息
存儲過程和函數的信息存儲在information_schema數據庫下的Routines表中。可以通過查詢該表的記錄來查詢存儲過程和函數的信息。
其基本語法形式如下：
SELECT
*
FROM
information_schema.Routines
WHERE
ROUTINE_NAME
=
'
sp_name
'
;
其中，ROUTINE_NAME字段中存儲的是存儲過程和函數的名稱；
sp_name參數表示存儲過程或函數的名稱。
?
下面從Routines表中查詢名為name_from_t3函數的信息。
代碼執行如下：
SELECT
*
FROM
information_schema.Routines
WHERE
ROUTINE_NAME
=
'
name_from_t3
'
SPECIFIC_NAME ROUTINE_CATALOG ROUTINE_SCHEMA ROUTINE_NAME ROUTINE_TYPE DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE CHARACTER_SET_NAME COLLATION_NAME DTD_IDENTIFIER ROUTINE_BODY ROUTINE_DEFINITION EXTERNAL_NAME EXTERNAL_LANGUAGE PARAMETER_STYLE IS_DETERMINISTIC SQL_DATA_ACCESS SQL_PATH SECURITY_TYPE CREATED LAST_ALTERED SQL_MODE ROUTINE_COMMENT DEFINER CHARACTER_SET_CLIENT COLLATION_CONNECTION DATABASE_COLLATION
--
----------- --------------- -------------- ------------ ------------ --------- ------------------------ ---------------------- ----------------- ------------- ------------------ --------------- -------------- ------------ ---------------------------------------------- ------------- ----------------- --------------- ---------------- --------------- -------- ------------- ------------------- ------------------- -------- --------------- -------------- -------------------- -------------------- ------------------
name_from_t3 def school name_from_t3
FUNCTION
char
80
240
(
NULL
) (
NULL
) utf8 utf8_general_ci
char
(
80
) SQL
RETURN
(
SELECT
NAME
FROM
t3
WHERE
id
=
id ) (
NULL
) (
NULL
) SQL NO
CONTAINS
SQL (
NULL
) DEFINER
2014
-
06
-
21
18
:
52
:
39
2014
-
06
-
21
18
:
52
:
39
root
@localhost
utf8 utf8_general_ci utf8_general_ci
查詢結果顯示name_from_t3的詳細信息。
?
注意：在information_schema數據庫下的Routines表中，存儲著所有存儲過程和函數的定義。
如果使用SELECT語句查詢Routines表中的存儲過程和函數的定義時，一定要使用ROUTINE_NAME字段指定存儲過程或函數的名稱。
否則，將查詢出所有的存儲過程或函數的定義。
修改存儲過程和函數
修改存儲過程和函數是指修改已經定義好的存儲過程和函數。
MySQL中通過ALTER PROCEDURE語句來修改存儲過程。
通過ALTER FUNCTION語句來修改存儲函數。
MySQL中修改存儲過程和函數的語句的語法形式如下：
ALTER
{
PROCEDURE
|
FUNCTION
} sp_name
[
characteristic ...
]
characteristic:
{
CONTAINS
SQL
|
NO SQL
|
READS SQL DATA
|
MODIFIES SQL DATA }
|
SQL SECURITY { DEFINER
|
INVOKER }
|
COMMENT
'
string
'
其中，sp_name參數表示存儲過程或函數的名稱；
characteristic參數指定存儲函數的特性。
CONTAINS SQL表示子程序包含SQL語句，但不包含讀或寫數據的語句；
NO SQL表示子程序中不包含SQL語句；
READS SQL DATA表示子程序中包含讀數據的語句；
MODIFIES SQL DATA表示子程序中包含寫數據的語句。
SQL SECURITY { DEFINER | INVOKER }指明誰有權限來執行。
DEFINER表示只有定義者自己才能夠執行；
INVOKER表示調用者可以執行。
COMMENT 'string'是注釋信息。
說明：修改存儲過程使用ALTER PROCEDURE語句，修改存儲函數使用ALTER FUNCTION語句。
但是，這兩個語句的結構是一樣的，語句中的所有參賽都是一樣的。
而且，它們與創建存儲過程或函數的語句中的參數也是基本一樣的。
?
修改存儲過程和函數，只能修改他們的權限，目前MYSQL還不提供對已存在的存儲過程和函數代碼的修改
如果要修改，只能通過先DROP掉，然后重新建立新的存儲過程和函數來實現
在SQLYOG里選中選中函數，然后右鍵ALTER FUNCTION的時候，也是這樣，先DROP掉，然后重新建立新的函數來實現
DELIMITER $$
DROP
FUNCTION
IF
EXISTS
`name_from_t3`$$
CREATE
DEFINER
=
`root`@`localhost`
FUNCTION
`name_from_t3`(id
INT
)
RETURNS
CHAR
(
80
) CHARSET utf8
RETURN
(
SELECT
NAME
FROM
t3
WHERE
id
=
id )$$
DELIMITER ;
?
刪除存儲過程和函數
刪除存儲過程和函數指刪除數據庫中已經存在的存儲過程和函數。
MySQL中使用DROP?PROCEDURE語句來刪除存儲過程。通過DROP FUNCTION語句來刪除存儲函數。
DROP
{
PROCEDURE
|
FUNCTION
} sp_name;
其中，sp_name參數表示存儲過程或函數的名稱
?
下面刪除存儲過程Proc和存儲函數name_from_t3。刪除存儲過程的代碼如下：
DROP
PROCEDURE
Proc
;
刪除存儲函數name_from_employee的代碼如下：
DROP
FUNCTION
name_from_t3;
刪除完畢之后，查詢information_schema.Routines來確認上面的刪除是否成功
SELECT
*
FROM
information_schema.Routines
WHERE
ROUTINE_NAME
=
'
proc
'
OR
ROUTINE_NAME
=
'
name_from_t3
'
;
總結
TIPS:
存儲過程里面是可以調用其他存儲過程的，使用CALL語句調用其他存儲過程就可以了
存儲過程參數列表里的參數名盡量不要和數據庫中表的字段名一樣，否則有可能出錯
存儲過程的參數可以使用中文，在定義存儲過程的時候加上character set gbk就可以了
例如
CREATE
PROCEDURE
useInfo(
IN
u_name
VARCHAR
(
50
)
CHARACTER
SET
gbk,OUT u_age
INT
)
?
如有不對的地方，歡迎大家拍磚o(∩_∩)o?
?
2014-6-22補充
DECLARE 語句要寫在存儲過程里面，否則會報錯！！
DROP
PROCEDURE
IF
EXISTS
Proc
;
DELIMITER
//
CREATE
PROCEDURE
Proc
()
BEGIN
DECLARE
command_not_allowed CONDITION
FOR
SQLSTATE
'
42000
'
;
END
//
DELIMITER ;
CALL
Proc
();
?
?
更多文章、技術交流、商務合作、聯系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
