目录：
一、PROCEDURE：
PROCEDURE ，事务，一个存储过程，实际上就是在服务器端直接在数据库中编写一段代码作运算，在服务器端进行高效的运算，运算结果直接返还给客户端。
它和 FUNCTION 一个明显的不同点是， FUNCTION 最后会有 RETURN 语句，返回运算结果， PROCEDURE 不允许有 RETURN 语句的，但是可以在参数表中指定返还数据。
PROCEDURE 编译完成后会存储在数据库中，需要调用的时候使用 CALL 语句对事务或者函数进行调用。编写 PROCEDURE 不仅可以避免重复编码，同时还可以提高计算效率。
二、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 你可以用这个选项指定可以调用该 PROCEDURE 的用户，比如说允许本地的用户nero使用，那么可以指定为：DEFINER='nero'@'localhost'，如果这个事务就是创建给当前用户使用的，那么可以指定为：DEFINER=CURRENT_USER。
2,事务安全性：characteristic中如果使用了SQL SECURITY，那么事务每次执行的时候，指定的安全上下文都会被执行，它们会检查当前执行这个事务的人是否拥有执行权限。
比如说下面这个小例子：
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，对这个事务有调用权限；b，对这个表tbl有select权限的用户才能成功执行该 PROCEDURE 。
而在形参部分，则是通过 OUT 和 IN 指明参数传入还是传出，如果某个参数在传入之后要作为结果传出，那么不需要作特定指示，直接写明参数名称和参数类型即可。
调用这个事务则用 CALL 表达式即可：
SET
@b
=
100
;
CALL simpleTest(
@a
,
@b
);
SELECT
@a
;
--
显示结果
三、PROCEDURE小进阶：
知道PROCEDURE的基本语法以后，学习一下编写一个PROCEDURE经常需要用到的语句，分别有： DECLARE 声明语句， SET 设值语句， DECLARE...HANDLER 句柄声明语句， DECLARE...CURSOR 游标声明语句；条件判断 IF 和 CASE ；三种循环体： LOOP，REPEAT，WHILE 。
3.1、基本的DECLARE语句：
DECLARE基本语法：
DECLARE
var_name
[
, var_name
]
... type
[
DEFAULT value
]
比如说在某个事务中声明几个临时变量：
CREATE
PROCEDURE
test()
BEGIN
DECLARE
usrID
INT
;
DECLARE
usrName
VARCHAR
(
10
)
DEFAULT
'
NERO
'
;
..........
--
一些事务操作
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中，如果指定的任意条件出现了，那么statement这里的指定语句就会被执行。conditions条件有几种类型：
1、SQLSTATE指的是当前SQL返回的状态，这个对应的状态就比较多了，比如状态Error: 1169 SQLSTATE: 23000,指的是”因特定限制而导致的无法写入的错误“；Error: 1162 SQLSTATE: 42000 ，指的是”结果字符串超过了最大限制“。相关的状态代码请自行查阅帮助文档的” Server Error Codes and Messages “词条。
2、SQLWARNING，但凡是SQL发出的警告信息。
3、NOT FOUND，一般来说出现在SELECT语句中，游标触底；
4、SQLEXCEPTION，SQL错误。
不同的结果分别对应：
1、CONTINUE，如果条件成立，那么，在执行句柄的statement之后再继续执行程序，比如说下面这个例子：
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
);
--
触发句柄的statement执行
12
SET
@x
=
3
;
13
END
;
14
//
15
16
CALLL HANDLER_DEMO()
//
17
18
SELECT
@x
//
19
SELECT
@x2
//
20
21
delimiter ;
结果当然是 @x为3,@x2为1 了。在代码11行，重复插入相同的值到主键上触发了23000错误，因而执行statement： SET @x2 = 1 ,然后再继续执行主程序的 SET @x = 3 .
2、EXIT，一旦条件被触发，当前BEGIN...END闭合语句将会终止执行，比如说：
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 ;
上述代码中，一旦出现23000错误，代码行4到7的BEGIN...END闭合语句立刻终止执行。
3.3、声明CURSOR游标 ：
声明一个CURSOR游标：
DECLARE
cursor_name
CURSOR
FOR
select_statement
比如说最基本的：
DECLARE
cur1
CURSOR
FOR
SELECT
id,data
FROM
tbl;
此时cur1表示的即是SELECT语句返回的首个结果，有点类似于指针。
下面不妨看一个比较完整的例子：
1
delimiter
//
2
CREATE
PROCEDURE
CURSOR_DEMO()
3
BEGIN
4
DECLARE
done
INT
DEFAULT
0
;
--
INT型值，默认为0
5
DECLARE
a
CHAR
(
5
);
6
DECLARE
b,c
INT
;
7
/*
声明两个游标
*/
8
DECLARE
CUR1
CURSOR
FOR
SELECT
ID,DATA
FROM
tbl1;
9
DECLARE
CUR2
CURSOR
FOR
SELECT
I
FROM
tbl2;
10
/*
声明CONTINUE句柄，当游标触底时被触发
*/
11
DECLARE
CONTINUE
HANDLER
FOR
NOT
FOUND
SET
done
=
1
;
12
13
/*
打开游标
*/
14
OPEN
CUR1;
15
OPEN
CUR2;
16
17
/*
循环插入数据，使用REPEAT...UNTIL语句
*/
18
REPEAT
19
FETCH
CUR1
INTO
a,b;
--
读取游标中的数据，并移向下一行
20
FETCH
CUR2
INTO
c;
21
IF
NOT
done
THEN
--
当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
;
--
当游标触底，句柄将被触发，done值被设为1，然后从这里继续执行主程序
28
UNTIL done
--
句柄触发后，done为1,执行UNTIL
29
END
REPEAT;
30
31
/*
使用完毕，关闭游标
*/
32
CLOSE
CUR1;
33
CLOSE
CUR2;
34
END
//
35
36
delimiter ;
其中，FETCH语句的基本语法如下：
FETCH
cursor_name
INTO
var_name
[
, var_name
]
...
该语句每次都会返回SELECT结果中的下一行（如果有的话）。
3.4、循环语句：
循环涉及到的语句有：1、 LOOP、ITERATE和LEAVE ；2、 REPEAT ；3、 WHILE 。
下面直接给出对应的循环例子：
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;
--
回到标签开始处
END
IF
;
LEAVE label1;
--
离开标签，退出流控制结构
END
LOOP label1;
--
结束循环
END
;
delimiter ;
2、REPEAT：
先给出REPEAT语法定义：
1
[
begin_label:] REPEAT
2
statement_list
3
UNTIL search_condition
4
END
REPEAT
[
end_label
]
可见，同样是可以在代码开始处插入label标签，不过REPEAT循环是自己有控制条件的，最好能直接使用UNTIL来进行条件判断。
比如下面这个例子：
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循环语法定义如下：
[
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
....
--
循环内书写具体需要处理的事务
SET
param
=
param
+
100
;
END
WHILE
;
END
;
delimiter ;
四、顺带提一下触发器TRIGGER：
触发器都是和某个特定的表相关联的，对该表设定触发器以后，一旦对这个表进行了某个特定操作（诸如INSERT，UPDATE，DELETE），触发器就会被触发。
先给出CREATE TRIGGER语法定义：
CREATE
[
DEFINER = { user | CURRENT_USER }
]
TRIGGER
trigger_name trigger_time trigger_event
ON
tbl_name
FOR
EACH ROW trigger_body
同样的，可以通过DEFINER自行指定触发器的适用对象。
在trigger_time中可以指定触发时间（诸如：BEFORE，AFTER），trigger_event前面已经提到过了，另外，DROP TABLE或TRUNCATE TABLE这种操作是不会触发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进行INSERT操作的时候（之前，这里设置的是BEFORE），BEGIN...END内的内容就会被执行。
