目錄：
一、PROCEDURE：
PROCEDURE ，事務(wù)，一個存儲過程，實際上就是在服務(wù)器端直接在數(shù)據(jù)庫中編寫一段代碼作運算，在服務(wù)器端進(jìn)行高效的運算，運算結(jié)果直接返還給客戶端。
它和 FUNCTION 一個明顯的不同點是， FUNCTION 最后會有 RETURN 語句，返回運算結(jié)果， PROCEDURE 不允許有 RETURN 語句的，但是可以在參數(shù)表中指定返還數(shù)據(jù)。
PROCEDURE 編譯完成后會存儲在數(shù)據(jù)庫中，需要調(diào)用的時候使用 CALL 語句對事務(wù)或者函數(shù)進(jìn)行調(diào)用。編寫 PROCEDURE 不僅可以避免重復(fù)編碼，同時還可以提高計算效率。
二、CREATE PROCEDURE基本語法：
下面不妨先看一看 CREATE PROCEDURE 以及 CREATE FUNCTION 的語法：
1
CREATE
2
[
DEFINER = { user | CURRENT_USER }
]
3
PROCEDURE
sp_name (
[
proc_parameter[,...
]
])
4
[
characteristic ...
]
routine_body
5
CREATE
6
[
DEFINER = { user | CURRENT_USER }
]
7
FUNCTION
sp_name (
[
func_parameter[,...
]
])
8
RETURNS
type
9
[
characteristic ...
]
routine_body
10
proc_parameter:
11
[
IN | OUT | INOUT
]
param_name type
12
func_parameter:
13
param_name type
14
type:
15
Any
valid MySQL data type
16
characteristic:
17
COMMENT
'
string
'
18
|
LANGUAGE SQL
19
|
[
NOT
]
DETERMINISTIC
20
|
{
CONTAINS
SQL
|
NO SQL
|
READS SQL DATA
|
MODIFIES SQL DATA }
21
|
SQL SECURITY { DEFINER
|
INVOKER }
22
routine_body:
23
Valid SQL routine statement
其中有幾個注意點是：
1, DEFINER 你可以用這個選項指定可以調(diào)用該 PROCEDURE 的用戶，比如說允許本地的用戶nero使用，那么可以指定為：DEFINER='nero'@'localhost'，如果這個事務(wù)就是創(chuàng)建給當(dāng)前用戶使用的，那么可以指定為：DEFINER=CURRENT_USER。
2,事務(wù)安全性：characteristic中如果使用了SQL SECURITY，那么事務(wù)每次執(zhí)行的時候，指定的安全上下文都會被執(zhí)行，它們會檢查當(dāng)前執(zhí)行這個事務(wù)的人是否擁有執(zhí)行權(quán)限。
比如說下面這個小例子：
delimiter
//
--
指定臨時分隔符
CREATE
DEFINER
=
'
nero
'
@
'
localhost
'
PROCEDURE
simpleTest(OUT outParam
int
,
IN
inParam
int
) SQL SECURITY INVOKE
BEGIN
SELECT
COUNT
(
*
)
INTO
outParam
FROM
tbl
WHERE
col
<
inParam;
END
;
delimter ;
像上面這個例子，我們在定義的時候啟用了 “SQL SECURITY INVOKE”， 只有是：a，對這個事務(wù)有調(diào)用權(quán)限；b，對這個表tbl有select權(quán)限的用戶才能成功執(zhí)行該 PROCEDURE 。
而在形參部分，則是通過 OUT 和 IN 指明參數(shù)傳入還是傳出，如果某個參數(shù)在傳入之后要作為結(jié)果傳出，那么不需要作特定指示，直接寫明參數(shù)名稱和參數(shù)類型即可。
調(diào)用這個事務(wù)則用 CALL 表達(dá)式即可：
SET
@b
=
100
;
CALL simpleTest(
@a
,
@b
);
SELECT
@a
;
--
顯示結(jié)果
三、PROCEDURE小進(jìn)階：
知道PROCEDURE的基本語法以后，學(xué)習(xí)一下編寫一個PROCEDURE經(jīng)常需要用到的語句，分別有： DECLARE 聲明語句， SET 設(shè)值語句， DECLARE...HANDLER 句柄聲明語句， DECLARE...CURSOR 游標(biāo)聲明語句；條件判斷 IF 和 CASE ；三種循環(huán)體： LOOP，REPEAT，WHILE 。
3.1、基本的DECLARE語句：
DECLARE基本語法：
DECLARE
var_name
[
, var_name
]
... type
[
DEFAULT value
]
比如說在某個事務(wù)中聲明幾個臨時變量：
CREATE
PROCEDURE
test()
BEGIN
DECLARE
usrID
INT
;
DECLARE
usrName
VARCHAR
(
10
)
DEFAULT
'
NERO
'
;
..........
--
一些事務(wù)操作
END
;
3.2、聲明HANDLER句柄：
基本語法：
1
DECLARE
handler_type HANDLER
2
FOR
condition_value
[
, condition_value
]
...
3
statement
4
handler_type:
5
CONTINUE
6
|
EXIT
7
|
UNDO
8
condition_value:
9
SQLSTATE
[
VALUE
]
sqlstate_value
10
|
condition_name
11
|
SQLWARNING
12
|
NOT
FOUND
13
|
SQLEXCEPTION
14
|
mysql_error_code
句柄的作用，就是在condition_value中，如果指定的任意條件出現(xiàn)了，那么statement這里的指定語句就會被執(zhí)行。conditions條件有幾種類型：
1、SQLSTATE指的是當(dāng)前SQL返回的狀態(tài)，這個對應(yīng)的狀態(tài)就比較多了，比如狀態(tài)Error: 1169 SQLSTATE: 23000,指的是”因特定限制而導(dǎo)致的無法寫入的錯誤“；Error: 1162 SQLSTATE: 42000 ，指的是”結(jié)果字符串超過了最大限制“。相關(guān)的狀態(tài)代碼請自行查閱幫助文檔的” Server Error Codes and Messages “詞條。
2、SQLWARNING，但凡是SQL發(fā)出的警告信息。
3、NOT FOUND，一般來說出現(xiàn)在SELECT語句中，游標(biāo)觸底；
4、SQLEXCEPTION，SQL錯誤。
不同的結(jié)果分別對應(yīng)：
1、CONTINUE，如果條件成立，那么，在執(zhí)行句柄的statement之后再繼續(xù)執(zhí)行程序，比如說下面這個例子：
1
CREATE
TABLE
tbl(col
INT
,
PRIMARY
KEY
(col));
2
3
delimiter
//
4
5
CREATE
PROCEDURE
HANDLER_DEMO()
6
BEGIN
7
DECLARE
CONTINUE
HANDLER
FOR
SQLSTATE
'
23000
'
SET
@x2
=
1
;
8
SET
@x
=
1
;
9
INSERT
INTO
tbl
VALUES
(
1
);
10
SET
@x
=
2
;
11
INSERT
INTO
tbl
VALUES
(
1
);
--
觸發(fā)句柄的statement執(zhí)行
12
SET
@x
=
3
;
13
END
;
14
//
15
16
CALLL HANDLER_DEMO()
//
17
18
SELECT
@x
//
19
SELECT
@x2
//
20
21
delimiter ;
結(jié)果當(dāng)然是 @x為3,@x2為1 了。在代碼11行，重復(fù)插入相同的值到主鍵上觸發(fā)了23000錯誤，因而執(zhí)行statement： SET @x2 = 1 ,然后再繼續(xù)執(zhí)行主程序的 SET @x = 3 .
2、EXIT，一旦條件被觸發(fā)，當(dāng)前BEGIN...END閉合語句將會終止執(zhí)行，比如說：
1
delimiter
//
2
CREATE
PROCEDURE
EXIT_DEMO()
3
BEGIN
4
BEGIN
5
DECLARE
EXIT
HANDLER
FOR
SQLSTATE
'
23000
'
6
.......
7
END
;
8
END
;
9
delimiter ;
上述代碼中，一旦出現(xiàn)23000錯誤，代碼行4到7的BEGIN...END閉合語句立刻終止執(zhí)行。
3.3、聲明CURSOR游標(biāo) ：
?聲明一個CURSOR游標(biāo)：
DECLARE
cursor_name
CURSOR
FOR
select_statement
比如說最基本的：
DECLARE
cur1
CURSOR
FOR
SELECT
id,data
FROM
tbl;
此時cur1表示的即是SELECT語句返回的首個結(jié)果，有點類似于指針。
下面不妨看一個比較完整的例子：
1
delimiter
//
2
CREATE
PROCEDURE
CURSOR_DEMO()
3
BEGIN
4
DECLARE
done
INT
DEFAULT
0
;
--
INT型值，默認(rèn)為0
5
DECLARE
a
CHAR
(
5
);
6
DECLARE
b,c
INT
;
7
/*
聲明兩個游標(biāo)
*/
8
DECLARE
CUR1
CURSOR
FOR
SELECT
ID,DATA
FROM
tbl1;
9
DECLARE
CUR2
CURSOR
FOR
SELECT
I
FROM
tbl2;
10
/*
聲明CONTINUE句柄，當(dāng)游標(biāo)觸底時被觸發(fā)
*/
11
DECLARE
CONTINUE
HANDLER
FOR
NOT
FOUND
SET
done
=
1
;
12
13
/*
打開游標(biāo)
*/
14
OPEN
CUR1;
15
OPEN
CUR2;
16
17
/*
循環(huán)插入數(shù)據(jù)，使用REPEAT...UNTIL語句
*/
18
REPEAT
19
FETCH
CUR1
INTO
a,b;
--
讀取游標(biāo)中的數(shù)據(jù)，并移向下一行
20
FETCH
CUR2
INTO
c;
21
IF
NOT
done
THEN
--
當(dāng)done為0的時候條件成立
22
IF
b
<
c
THEN
--
取b和c的較小者插入表3
23
INSERT
INTO
tbl3
VALUES
(a,b);
24
ELSE
25
INSERT
INTO
tbl3
VALUES
(a,c);
26
END
IF
;
27
END
IF
;
--
當(dāng)游標(biāo)觸底，句柄將被觸發(fā)，done值被設(shè)為1，然后從這里繼續(xù)執(zhí)行主程序
28
UNTIL done
--
句柄觸發(fā)后，done為1,執(zhí)行UNTIL
29
END
REPEAT;
30
31
/*
使用完畢，關(guān)閉游標(biāo)
*/
32
CLOSE
CUR1;
33
CLOSE
CUR2;
34
END
//
35
36
delimiter ;
其中，F(xiàn)ETCH語句的基本語法如下：
FETCH
cursor_name
INTO
var_name
[
, var_name
]
...
該語句每次都會返回SELECT結(jié)果中的下一行（如果有的話）。
?3.4、循環(huán)語句：
循環(huán)涉及到的語句有：1、 LOOP、ITERATE和LEAVE ；2、 REPEAT ；3、 WHILE 。
下面直接給出對應(yīng)的循環(huán)例子：
1、 LOOP、ITERATE和LEAVE：
/*
LOOP,ITERATE,LEAVE
*/
delimiter
//
CREATE
PROCEDURE
LOOP_DEMO(param
INT
)
BEGIN
label1: LOOP
SET
param
=
param
+
1
;
IF
param
<
100
THEN
ITERATE label1;
--
回到標(biāo)簽開始處
END
IF
;
LEAVE label1;
--
離開標(biāo)簽，退出流控制結(jié)構(gòu)
END
LOOP label1;
--
結(jié)束循環(huán)
END
;
delimiter ;
2、REPEAT：
先給出REPEAT語法定義：
1
[
begin_label:] REPEAT
2
statement_list
3
UNTIL search_condition
4
END
REPEAT
[
end_label
]
可見，同樣是可以在代碼開始處插入label標(biāo)簽，不過REPEAT循環(huán)是自己有控制條件的，最好能直接使用UNTIL來進(jìn)行條件判斷。
比如下面這個例子：
1
delimiter
//
2
CREATE
PROCEDURE
REPEAT_DEMO
(param
INT
)
3
BEGIN
4
SET
@x
=
0
;
5
REPEAT
6
SET
@x
=
@x
+
1
;
7
UNTIL
@x
>
param
8
END
REPEAT;
9
END
//
10
11
delimiter ;
3、WHILE：
WHILE循環(huán)語法定義如下：
[
begin_label:
]
WHILE
search_condition DO
statement_list
END
WHILE
[
end_label
]
比如下面這個小例子：
delimiter
//
CREATE
PROCEDURE
WHILE_DEMO()
BEGIN
SET
param
INT
DEFAULT
10
;
WHILE
param
<
1000
....
--
循環(huán)內(nèi)書寫具體需要處理的事務(wù)
SET
param
=
param
+
100
;
END
WHILE
;
END
;
delimiter ;
四、順帶提一下觸發(fā)器TRIGGER：
觸發(fā)器都是和某個特定的表相關(guān)聯(lián)的，對該表設(shè)定觸發(fā)器以后，一旦對這個表進(jìn)行了某個特定操作（諸如INSERT，UPDATE，DELETE），觸發(fā)器就會被觸發(fā)。
先給出CREATE TRIGGER語法定義：
CREATE
[
DEFINER = { user | CURRENT_USER }
]
TRIGGER
trigger_name trigger_time trigger_event
ON
tbl_name
FOR
EACH ROW trigger_body
同樣的，可以通過DEFINER自行指定觸發(fā)器的適用對象。
在trigger_time中可以指定觸發(fā)時間（諸如：BEFORE，AFTER），trigger_event前面已經(jīng)提到過了，另外，DROP TABLE或TRUNCATE TABLE這種操作是不會觸發(fā)TRIGGER的。
下面給出個小例子：
1
delimiter
//
2
CREATE
DEFINER
'
nero
'
@
'
localhost
'
TRIGGER
trigger_demo
3
BEFORE
INSERT
ON
tbl1
FOR
EACH ROW
4
BEGIN
5
INSERT
INTO
tbl2
VALUES
(...........);
--
INSERT操作
6
DELETE
FROM
tbl3
WHERE
.......... ;
--
刪除操作
7
UPDATE
tbl4
SET
col1
=
...... ;
--
更新操作
8
END
;
//
9
delimiter ;
這樣，一旦本地用戶nero對表tbl1進(jìn)行INSERT操作的時候（之前，這里設(shè)置的是BEFORE），BEGIN...END內(nèi)的內(nèi)容就會被執(zhí)行。
更多文章、技術(shù)交流、商務(wù)合作、聯(lián)系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯(lián)系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
