存储过程就是一条或者多条SQL语句的集合，可视为批文件，但是其作用不仅限于批处理。
9.1、创建存储过程和函数
存储程序可以分为存储过程和函数，MySQL中创建存储过程和函数使用的语句分别是CREATE PROCEDURE和CREATE FUNCTION。使用CALL语句来调用存储过程，只能用输出变量返回值。函数可以从语句外调用( 即通过引用函数名)，也能返回标量值。存储过程也能调用其他存储过程。
创建存储过程
创建存储过程，需要使用CREATE PROCEDURE语句，基本语法为：
|
1
2
|
CREATE
PROCEDURE
sp_name ([proc_parameter])
[characteristic ...] routine_body
|
| CREATE PROCEDURE：创建存储过程的关键字 |
| sp_name：存储过程的名称 |
| proc_parameter：参数列表，列表形式为 [IN|OUT|INOUT] param_name type IN表示输入参数；OUT表示输出参数；INOUT表示既可输入又可输出； param_name参数名称；type参数的类型，该类型可以是MySQL中的任意类型 |
| characteristic：指定存储过程的特性 LANGUAGE SQL：说明routine_body部分是由SQL语句组成，SQL是LANGUAGE特性的唯一值 [NOT] DETERMINISTIC：指明存储过程执行的结果是否确定，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表明子程序包含写数据的语句。 SQL SECURITY { DEFINER | INVOKER }：指明谁有权限执行。DEFINER表示只有定义者才能执行；INVOKER表示拥有权限的调用者可以执行。 COMMENT 'string'：注释信息。 |
| routine_body：SQL代码的内容，可以用BEGIN...END来表示代码的开始和结束。 |
创建查看fruits表的存储过程
|
1
2
3
4
5
6
7
8
9
|
mysql> DELIMITER //
mysql>
CREATE
PROCEDURE
Proc()
->
BEGIN
->
SELECT
*
FROM
fruits;
->
END
//
Query OK, 0
rows
affected (0.00 sec)
mysql> DELIMITER ;
|
创建名称为CountProc的存储过程
|
1
2
3
4
5
6
7
8
9
|
mysql> DELIMITER //
mysql>
CREATE
PROCEDURE
CountProc(
OUT
param1
INT
)
->
BEGIN
->
SELECT
COUNT
(*)
INTO
param1
FROM
fruits;
->
END
//
Query OK, 0
rows
affected (0.00 sec)
mysql> DELIMITER ;
|
'DELIMITER //'作用是将MySQL的结束符设置为//，当使用DELIMITER命令时，应该避免使用反斜杠（’\’）字符，因为反斜线是MySQL的转义字符。
创建存储函数
创建存储函数，需要使用CREATE FUNCTION语句，基本语法为：
|
1
2
3
|
CREATE
FUNCTION
func_name ([func_parameter])
RETURNS
type
[characteristic ...] routine_body
|
| CREATE FUNCTION：创建存储函数的关键字 |
| func_name表示存储函数的名称 |
| func_parameter存储函数的参数列表，形式为 [IN|OUT|INOUT] param_name type IN表示输入参数；OUT表示输出参数；INOUT表示既可输入又可输出； param_name参数名称；type参数的类型，该类型可以是MySQL中的任意类型 |
| RETURNS type表示函数返回数据类型 |
| characteristic指定存储函数的特性，取值和与创建存储过程相同 |
创建存储函数，名称为NameByZip，该函数返回SELECT语句的查询结果，数值类型为字符串型
|
1
2
3
4
5
6
7
8
9
|
mysql> DELIMITER //
mysql>
CREATE
FUNCTION
NameByZip()
->
RETURNS
CHAR
(50)
->
RETURN
(
SELECT
s_name
FROM
suppliers
WHERE
s_call=
'48075'
);
-> //
Query OK, 0
rows
affected (0.00 sec)
mysql> DELIMITER ;
|
变量的使用
在存储过程中使用DECLARE语句定义变量，语法格式为：
|
1
|
DECLARE
var_name[,varname]... date_type [
DEFAULT
value];
|
定义名称为myparam的变量，类型为INT类型，默认值为100
|
1
|
DECLARE
myparam
INT
DEFAULT
100;
|
定义变量后，为变量赋值可以改变变量的默认信息，MySQL使用SET语句为变量赋值，语法格式为：
|
1
|
SET
var_name = expr [,var_name=expr]...
|
声明3个变量，分别为var1、var2和var3，数据类型为INT，使用SET为变量赋值
|
1
2
3
|
DECLARE
var1, var2, var3
INT
;
SET
var1 = 10, var2 = 20;
SET
var3 = var1 + var2;
|
MySQLhankeyishiyong SELECT...INTO为一个或多个变量赋值，语法为：
|
1
|
SELECT
col_name[,...]
INTO
var_name[,...] table_expr;
|
声明变量fruitname和fruitprice，通过SELECT ... INTO语句查询指定记录并为变量赋值
|
1
2
3
4
5
|
DECLARE
fruitname
CHAR
(50);
DECLARE
fruitprice
DECIMAL
(8,2);
SELECT
f_name,f_price
INTO
fruitname, fruitprice
FROM
fruits
WHERE
f_id =
'a1'
;
|
定义条件和处理程序
特定条件需要特定处理。这些条件可以联系到错误，以及子程序的一般流程控制。定义条件是事先定义程序执行过程中遇到的问题，处理程序定义了在遇到这些问题时应当采取的处理方式，并且保证存储过程或函数在遇到警告或错误时能继续执行。使用DECLARE关键字来定义条件和处理程序。定义条件的语法格式为：
|
1
2
3
|
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为数值类型错误代码 |
定义"ERROR 1148(42000)"错误，名称为command_not_allowed
|
1
2
3
4
5
|
//方法一：使用sqlstate_value
DECLARE
command_not_allowed CONDITION
FOR
SQLSTATE
'42000'
;
//方法二：使用mysql_error_code
DECLARE
command_not_allowed CONDITION
FOR
1148
|
定义处理程序时，使用DECLARE语句的语法为：
|
1
2
3
4
5
6
7
8
9
10
11
12
|
DECLARE
handler_action HANDLER
FOR
condition_value statement
handler_action:
CONTINUE
| EXIT| UNDO
condition_value:
mysql_error_code
| SQLSTATE [VALUE] sqlstate_value
| condition_name
| SQLWARNING
|
NOT
FOUND
| SQLEXCEPTION
|
| handler_action:处理错误方式，参数有3个取值：CONTINUE，EXIT，UNDO。 CONTINUE表示遇到错误不处理，继续执行 EXIT遇到错误马上退出 UNDO遇到错误后撤回之前的操作 |
| condition_value表示错误类型 SQLSTATE [VALUE] sqlstate_value：包含5个字符的字符串错误值 condition_name：DECLARE CONDITION定义的错误条件名称 SQLWARNING：匹配所有01开头的SQLSTATE错误代码 NOT FOUND：匹配所有02开头的SQLSTATE错误代码 SQLEXCEPTION：匹配所有没有被SQLWARNING或NOT FOUND捕获的SQLSTATE错误代码 mysql_error_code：匹配数值类型错误代码 |
| statement参数为程序语句段，表示遇到定义的错误时，需要执行的存储过程或函数 |
定义处理程序的几种方式
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
//方法一：捕获sqlstate_value
DECLARE
CONTINUE
HANDLER
FOR
SQLSTATE
'42S02'
SET
@info=
'NO_SUCH_TABLE'
;
//方法二：捕获mysql_error_code
DECLARE
CONTINUE
HANDLER
FOR
1146
SET
@info=
' NO_SUCH_TABLE '
;
//方法三：先定义条件，然后调用
DECLARE
no_such_table CONDITION
FOR
1146;
DECLARE
CONTINUE
HANDLER
FOR
NO_SUCH_TABLE
SET
@info=
' NO_SUCH_TABLE '
;
//方法四：使用SQLWARNING
DECLARE
EXIT HANDLER
FOR
SQLWARNING
SET
@info=
'ERROR'
;
//方法五：使用
NOT
FOUND
DECLARE
EXIT HANDLER
FOR
NOT
FOUND
SET
@info=
' NO_SUCH_TABLE '
;
//方法六：使用SQLEXCEPTION
DECLARE
EXIT HANDLER
FOR
SQLEXCEPTION
SET
@info=
'ERROR'
;
|
定义条件和处理程序
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
|
mysql>
CREATE
TABLE
test.t (s1
int
,
primary
key
(s1));
Query OK, 0
rows
affected (0.05 sec)
mysql> DELIMITER //
mysql>
CREATE
PROCEDURE
handlerdemo ()
->
BEGIN
->
DECLARE
CONTINUE
HANDLER
FOR
SQLSTATE
'23000'
SET
@x2 = 1;
->
SET
@x = 1;
->
INSERT
INTO
test.t
VALUES
(1);
->
SET
@x = 2;
->
INSERT
INTO
test.t
VALUES
(1);
->
SET
@x = 3;
->
END
;
-> //
Query OK, 0
rows
affected (0.00 sec)
mysql> DELIMITER ;
mysql> CALL handlerdemo(); /*调用存储过程*/
Query OK, 0
rows
affected, 1 warning (0.02 sec)
mysql>
SELECT
@x; /*查看调用过程结果*/
+
------+
| @x |
+
------+
| 3 |
+
------+
1 row
in
set
(0.00 sec)
|
'@var_name'表示用户变量，使用SET语句为其赋值，用户与连接有关，一个客户端定义的变量不能被其他客户端看到或使用。客户端退出时，该客户端连接的所有变量自动释放。
光标的使用
查询语句可能返回多条记录，如果数据非常大，需要在存储过程和存储函数中使用光标来逐条读取查询结果集中的记录。光标必须在声明处理程序之前被声明，并且变量和条件还必须在声明光标或处理程序之前声明。MySQL中光标只能在存储过程和函数中使用。
MySQL中使用DECLARE关键字声明光标，语法基本形式为：
|
1
|
DECLARE
cursor_name
CURSOR
FOR
select_statement
|
| cursor_name表示光标名称 select_statement表示SELECT语句的内容 返回一个用户创建光标的结果集 |
声明名称为cursor_fruit的光标
|
1
|
DECLARE
cursor_fruit
CURSOR
FOR
SELECT
f_name, f_price
FROM
fruits ;
|
打开光标的语法为：
|
1
|
OPEN
cursor_name [光标名称]
|
打开名称为cursor_fruit的光标
|
1
|
OPEN
cursor_fruit
|
使用光标的语法为：
|
1
|
FETCH
cursor_name
INTO
var_name [,var_name] ...[参数名称]
|
使用名称为cursor_fruit的光标。将查询出来的数据存入fruit_name和fruit_price这两个变量
|
1
|
FETCH
cursor_fruit
INTO
fruit_name, fruit_price ;
|
关闭名称为cursor_fruit的光标
|
1
|
CLOSE
cursor_fruit;
|
流程控制的使用
流程控制语句用来控制条件语句的执行。MySQL终于来控制流程的于具有IF、CASE、LOOP、LEAVE、ITERATE、REPEAT和WHERE语句。
-
IF
IF语句包含多个条件判断，根据判断的结果为TRUE或FALSE执行相应的语句，语法格式为：
|
1
2
3
4
|
IF search_condition
THEN
statement_list
[ELSEIF search_condition
THEN
statement_list] ...
[
ELSE
statement_list]
END
IF
|
IF语句的示例
|
1
2
3
4
|
IF val
IS
NULL
THEN
SELECT
'val is NULL'
;
ELSE
SELECT
'val is not NULL'
;
END
IF;
|
-
CASE
CASE是另一个进行条件判断的语句，该语句有两种格式：
|
1
2
3
4
5
6
7
8
9
10
11
12
|
CASE
case_value
WHEN
when_value
THEN
statement_list
[
WHEN
when_value
THEN
statement_list] ...
[
ELSE
statement_list]
END
CASE
Or
:
CASE
WHEN
search_condition
THEN
statement_list
[
WHEN
search_condition
THEN
statement_list] ...
[
ELSE
statement_list]
END
CASE
|
使用CASE流程控制语句第1种格式，判断val值等于1、等于2，或者两者都不等
|
1
2
3
4
5
6
7
|
CASE
val
WHEN
1
THEN
SELECT
'val is 1'
;
WHEN
2
THEN
SELECT
'val is 2'
;
ELSE
SELECT
'val is not 1 or 2'
;
END
CASE
;
当val值为1时，输出字符串“val
is
1”；当val值为2时，输出字符串“val
is
2”；否则输出字符
串“val
is
not
1
or
2”。
|
使用CASE流程控制语句的第2种格式，判断val是否为空、小于0、大于0或者等于0
|
1
2
3
4
5
6
7
8
|
CASE
WHEN
val
IS
NULL
THEN
SELECT
'val is NULL'
;
WHEN
val < 0
THEN
SELECT
'val is less than 0'
;
WHEN
val > 0
THEN
SELECT
'val is greater than 0'
;
ELSE
SELECT
'val is 0'
;
END
CASE
;
当val值为空，输出字符串“val
is
NULL
”；当val值小于0时，输出字符串“val
is
less than 0”；
当val值大于0时，输出字符串“val
is
greater than 0”；否则输出字符串“val
is
0”。
|
-
LOOP
LOOP循环语句用来重复执行某些语句，与IF和CASE相比，LOOP只是创建一个循环的过程，并不进行条件判断。LOOP内的语句一直重复只i系那个知道循环被退出，跳出循环过程使用LEAVE子句，LOOP语法格式为：
|
1
2
3
|
[begin_label:] LOOP
statement_list
END
LOOP [end_label]
|
使用LOOP语句进行循环操作，id值小于等于10之前，将重复执行循环过程
|
1
2
3
4
5
6
|
DECLARE
id
INT
DEFAULT
0;
add_loop: LOOP
SET
id = id + 1;
IF id >= 10
THEN
LEAVE add_loop;
END
IF;
END
LOOP add_ loop;
|
-
LEAVE
LEAVE语句用来退出任何被标注的流程控制构造，其语法格式为：
|
1
|
LEAVE label
|
使用LEAVE语句退出循环
|
1
2
3
4
|
add_num: LOOP
SET
@
count
=@
count
+1;
IF @
count
=50
THEN
LEAVE add_num ;
END
LOOP add_num ;
|
-
ITERATE
ITERATE语句将执行顺序转到语句段开头处，语法格式为：
|
1
|
ITERATE label
|
ITERATE只可以出现在LOOP、REPEAT、WHERE语句内。
ITERATE语句示例
|
1
2
3
4
5
6
7
8
9
10
11
|
CREATE
PROCEDURE
doiterate()
BEGIN
DECLARE
p1
INT
DEFAULT
0;
my_loop: LOOP
SET
p1= p1 + 1;
IF p1 < 10
THEN
ITERATE my_loop;
ELSEIF p1 > 20
THEN
LEAVE my_loop;
END
IF;
SELECT
'p1 is between 10 and 20'
;
END
LOOP my_loop;
END
|
-
REPEAT
REPEAT语句创建一个带条件判断的循环过程，每次语句执行完毕之后，会对条件表达式进行判断，如果为真，则结束循环，其语法格式为：
|
1
2
3
4
|
[begin_label:] REPEAT
statement_list
UNTIL search_condition
END
REPEAT [end_label]
|
REPEAT语句示例，id值小于等于10之前，将重复执行循环过程
|
1
2
3
4
5
|
DECLARE
id
INT
DEFAULT
0;
REPEAT
SET
id = id + 1;
UNTIL id >= 10
END
REPEAT;
|
-
WHILE
WHILE语句创建一个带条件判断的循环过程，与REPEAT不同，WHERE在执行语句时，先对指定表达式进行判断，如果为真，则执行循环的语句，其语法格式为：
|
1
2
3
|
[begin_label:] WHILE search_condition DO
statement_list
END
WHILE [end_label]
|
WHILE语句示例，id值小于等于10之前，将重复执行循环过程
|
1
2
3
4
|
DECLARE
i
INT
DEFAULT
0;
WHILE i < 10 DO
SET
i = i + 1;
END
WHILE;
|
9.2、调用存储过程和函数
调用存储过程
存储过程通过CALL语句进行调用，语法格式为：
|
1
|
CALL sp_name([parameter[,...]])
|
定义名为CountProc1的存储过程，然后调用这个存储过程
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
mysql> DELIMITER //
mysql>
CREATE
PROCEDURE
CountProc1 (
IN
sid
INT
,
OUT
num
INT
)
->
BEGIN
->
SELECT
COUNT
(*)
INTO
num
FROM
fruits
WHERE
s_id = sid;
->
END
//
Query OK, 0
rows
affected (0.00 sec)
mysql> DELIMITER ;
mysql> CALL CountProc1 (101, @num);
Query OK, 1 row affected (0.00 sec)
mysql>
select
@num;
+
------+
| @num |
+
------+
| 3 |
+
------+
1 row
in
set
(0.02 sec)
|
调用存储函数
定义存储函数CountProc2，然后调用这个函数
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
mysql> DELIMITER //
mysql>
CREATE
FUNCTION
CountProc2 (sid
INT
)
->
RETURNS
INT
->
BEGIN
->
RETURN
(
SELECT
COUNT
(*)
FROM
fruits
WHERE
s_id = sid);
->
END
//
Query OK, 0
rows
affected (0.00 sec)
mysql> DELIMITER ;
mysql>
SELECT
CountProc2(101);
+
--------------------+
| Countproc(101) |
+
--------------------+
| 3 |
+
-------------------+
|
9.3、查看存储过程和函数
MySQL存储了存储过程和函数的状态信息，用户可以使用SHOW STATUS语句或SHOW CREATE语句查看，也可以直接从系统information_schema数据库中查看。
使用SHOW STATUS语句可以查看存储过程和函数状态，基本语法为：
|
1
|
SHOW [
PROCEDURE
|
FUNCTION
] STATUS [
LIKE
'pattern'
]
|
SHOW STATUS语句示例
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
mysql> SHOW
PROCEDURE
STATUS
LIKE
'C%'
\G
*************************** 1. row ***************************
Db: test
Name
: CountProc
Type:
PROCEDURE
Definer: root@localhost
Modified: 2017-08-04 11:32:08
Created: 2017-08-04 11:32:08
Security_type: DEFINER
Comment:
character_set_client: utf8
collation_connection: utf8_general_ci
Database
Collation: utf8_general_ci
1 row
in
set
(0.00 sec)
|
使用SHOW CREATE语句可以查看存储过程和函数状态，基本语法为：
|
1
|
SHOW
CREATE
[
PROCEDURE
|
FUNCTION
] sp_name
|
SHOW CREATE语句示例
|
1
2
3
4
5
6
7
8
9
10
11
12
|
mysql> SHOW
CREATE
PROCEDURE
test.CountProc \G
*************************** 1. row ***************************
Procedure
: CountProc
sql_mode:
Create
Procedure
:
CREATE
DEFINER=`root`@`localhost`
PROCEDURE
`CountProc`(
OUT
param1
INT
)
BEGIN
SELECT
COUNT
(*)
INTO
param1
FROM
fruits;
END
character_set_client: utf8
collation_connection: utf8_general_ci
Database
Collation: utf8_general_ci
1 row
in
set
(0.00 sec)
|
MySQL中存储过程和函数的信息存储在information_schema数据库下的Routines表中，可以通过查询该表的记录来查询存储过程和函数，其基本语法为：
|
1
2
|
SELECT
*
FROM
information_schema.Routines
WHERE
ROUTINE_NAME=
'sp_name'
;
|
从Routines表中查询名称为CountProc的存储过程的信息
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
|
mysql>
SELECT
*
FROM
information_schema.Routines
->
WHERE
ROUTINE_NAME=
'CountProc'
AND
ROUTINE_TYPE =
'PROCEDURE'
\G
*************************** 1. row ***************************
SPECIFIC_NAME: CountProc
ROUTINE_CATALOG: def
ROUTINE_SCHEMA: test
ROUTINE_NAME: CountProc
ROUTINE_TYPE:
PROCEDURE
DATA_TYPE:
CHARACTER_MAXIMUM_LENGTH:
NULL
CHARACTER_OCTET_LENGTH:
NULL
NUMERIC_PRECISION:
NULL
NUMERIC_SCALE:
NULL
CHARACTER_SET_NAME:
NULL
COLLATION_NAME:
NULL
DTD_IDENTIFIER:
NULL
ROUTINE_BODY: SQL
ROUTINE_DEFINITION:
BEGIN
SELECT
COUNT
(*)
INTO
param1
FROM
fruits;
END
EXTERNAL_NAME:
NULL
EXTERNAL_LANGUAGE:
NULL
PARAMETER_STYLE: SQL
IS_DETERMINISTIC:
NO
SQL_DATA_ACCESS:
CONTAINS
SQL
SQL_PATH:
NULL
SECURITY_TYPE: DEFINER
CREATED: 2017-08-04 11:32:08
LAST_ALTERED: 2017-08-04 11:32:08
SQL_MODE:
ROUTINE_COMMENT:
DEFINER: root@localhost
CHARACTER_SET_CLIENT: utf8
COLLATION_CONNECTION: utf8_general_ci
DATABASE_COLLATION: utf8_general_ci
1 row
in
set
(0.00 sec)
|
9.4、修改存储过程和函数
使用ALTER语句可以修改存储过程或函数的特性，基本语法为：
|
1
|
ALTER
[
PROCEDURE
|
FUNCTION
] sp_name [characteristic ...]
|
修改存储过程CountProc的定义。将读写权限改为MODIFIES SQL DATA，并指明调用者可以执行
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
mysql>
ALTER
PROCEDURE
CountProc
-> MODIFIES SQL DATA
-> SQL SECURITY INVOKER ;
Query OK, 0
rows
affected (0.00 sec)
mysql>
SELECT
SPECIFIC_NAME,SQL_DATA_ACCESS,SECURITY_TYPE
->
FROM
information_schema.Routines
->
WHERE
ROUTINE_NAME=
'CountProc'
AND
ROUTINE_TYPE=
'PROCEDURE'
;
+
---------------+-------------------+---------------+
| SPECIFIC_NAME | SQL_DATA_ACCESS | SECURITY_TYPE |
+
---------------+-------------------+---------------+
| CountProc | MODIFIES SQL DATA | INVOKER |
+
---------------+-------------------+---------------+
1 row
in
set
(0.00 sec)
|
9.5、删除存储过程和函数
删除存储过程和函数，可以使用DROP语句，其语法格式为：
|
1
|
DROP
[
PROCEDURE
|
FUNCTION
] [IF EXISTS] sp_name
|
删除存储过程和存储函数
|
1
2
3
4
5
|
mysql>
DROP
PROCEDURE
CountProc;
Query OK, 0
rows
affected (0.00 sec)
mysql>
DROP
FUNCTION
CountProc;
Query OK, 0
rows
affected (0.00 sec)
|
