MySQL存储过程

1.1 CREATE PROCEDURE (创建)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
CREATE PROCEDURE 存储过程名 ( 参数列表 )
BEGIN
SQL语句代码块
END
# 注意:
# 由括号包围的参数列必须总是存在。如果没有参数,也该使用一个空参数列()。每个参数默认都是一个IN参数。要指定为其它参数,可在参数名之前使用关键词 OUTINOUT
# 在mysql客户端定义存储过程的时候使用delimiter命令来把语句定界符从;变为//
# 当使用delimiter命令时,你应该避免使用反斜杠(‘"’)字符,因为那是MySQL的转义字符。
# 如:
delimiter //
CREATE PROCEDURE simpleproc (OUT param1 INT)
BEGIN
SELECT COUNT(\*) INTO param1 FROM t;
END
//
# Query OK, 0 rows affected (0.00 sec)

1.2 ALTER PROCEDURE (修改)

1
2
ALTER PROCEDURE 存储过程名 SQL语句代码块
# 这个语句可以被用来改变一个存储程序的特征。

1.3 DROP PROCEDURE (删除)

1
2
3
4
5
DROP PROCEDURE IF EXISTS 存储过程名 
# 例如:
DROP PROCEDURE IF EXISTS proc_employee
# (proc_employee 存储过程名 )
# 这个语句被用来移除一个存储程序。不能在一个存储过程中删除另一个存储过程,只能调用另一个存储过程

1.4 SHOW CREATE PROCEDURE(类似于SHOW CREATE TABLE,查看一个已存在的存储过程)

1
SHOW CREATE PROCEDURE 存储过程名

1.5 SHOW PROCEDURE STATUS (列出所有的存储过程)

1
SHOW  PROCEDURE  STATUS

1.6 CALL语句(存储过程的调用)

1
2
3
4
CALL 存储过程名 (参数列表)
# CALL语句调用一个先前用CREATE PROCEDURE创建的程序。
# CALL语句可以用声明为OUT或的INOUT参数的参数给它的调用者传回值。
# 存储过程名称后面必须加括号,哪怕该存储过程没有参数传递

1.7 BEGIN … END(复合语句)

1
2
3
4
5
6
7
8
BEGIN 
[statement_list]
END
# 存储子程序可以使用BEGIN ... END复合语句来包含多个语句。

# statement_list 代表一个或多个语句的列表。statement_list之内每个语句都必须用分号(;)来结尾。

# 复合语句可以被标记。除非begin_label存在,否则end_label不能被给出,并且如果二者都存在,他们必须是同样的。

1.8 DECLARE语句(用来声明局部变量)

1
2
# DECLARE语句被用来把不同项目局域到一个子程序:局部变量
# DECLARE仅被用在BEGIN ... END复合语句里,并且必须在复合语句的开头,在任何其它语句之前。

1.9 存储程序中的变量

1.9.1 DECLARE局部变量

1
2
3
4
5
DECLARE var_name[,...] type [DEFAULT value]
# 这个语句被用来声明局部变量。要给变量提供一个默认值,请包含一个DEFAULT子句。
# 值可以被指定为一个表达式,不需要为一个常数。如果没有DEFAULT子句,初始值为NULL
# 局部变量的作用范围在它被声明的BEGIN ... END块内。
# 它可以被用在嵌套的块中,除了那些用相同名字声明变量的块。

1.9.2 变量SET语句

1
2
3
4
5
6
7
SET var_name = expr [, var_name = expr]
# 在存储程序中的SET语句是一般SET语句的扩展版本。
# 被参考变量可能是子程序内声明的变量,或者是全局服务器变量。
# 在存储程序中的SET语句作为预先存在的SET语法的一部分来实现。
# 这允许SET a=x, b=y, ...这样的扩展语法。
# 其中不同的变量类型(局域声明变量及全局和集体变量)可以被混合起来。
# 这也允许把局部变量和一些只对系统变量有意义的选项合并起来。

1.9.3 SELECT … INTO语句

1
2
3
4
5
SELECT col_name[,...] INTO var_name[,...] table_expr
# 这个SELECT语法把选定的列直接存储到变量。
# 因此,只有单一的行可以被取回。
SELECT id,data INTO x,y FROM test.t1 LIMIT 1;
# 注意,用户变量名在MySQL 5.1中是对大小写不敏感的。

重要: SQL变量名不能和列名一样。如果SELECT … INTO这样的SQL语句包含一个对列的参考,并包含一个与列相同名字的局部变量,MySQL当前把参考解释为一个变量的名字。

1.10 MySQL 存储过程参数类型(in、out、inout)

1.10.1 MySQL存储过程参数(in)

MySQL 存储过程 “in” 参数:跟 C 语言的函数参数的值传递类似, MySQL 存储过程内部可能会修改此参数,但对 in 类型参数的修改,对调用者(caller)来说是不可见的(not visible)。

1.10.2 MySQL存储过程参数(out)

MySQL 存储过程 “out” 参数:从存储过程内部传值给调用者。在存储过程内部,该参数初始值为 null,无论调用者是否给存储过程参数设置值

1.10.3 MySQL存储过程参数(inout)

MySQL 存储过程 inout 参数跟 out 类似,都可以从存储过程内部传值给调用者。不同的是:调用者还可以通过 inout 参数传递值给存储过程。

1.10.4 总结

如果仅仅想把数据传给 MySQL 存储过程,那就使用“in” 类型参数;如果仅仅从 MySQL 存储过程返回值,那就使用“out” 类型参数;如果需要把数据传给 MySQL 存储过程,还要经过一些计算后再传回给我们,此时,要使用“inout” 类型参数。

1.11 例子:

1.11.1 创建存储过程

带(输出参数)返回值的存储过程:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
-- 删除存储过程
DROP PROCEDURE IF EXISTS proc_employee_getCount
-- 创建存储过程
CREATE PROCEDURE proc_employee_getCount(out n int)
BEGIN
SELECT COUNT(\*) FROM employee ;
END
--MYSQL调用存储过程
CALL proc_employee_getCount(@n);

-- 带输入参数的存储过程:

-- 删除存储过程
DROP PROCEDURE IF EXISTS proc_employee_findById;
-- 创建存储过程
CREATE PROCEDURE proc_employee_findById(in n int)
BEGIN
SELECT \* FROM employee where id=n;
END
-- 定义变量
SET @n=1;
-- 调用存储过程
CALL proc_employee_findById(@n);

操作存储过程时应注意:

  1. 删除存储过程时只需要指定存储过程名即可,不带括号;

  2. 创建存储过程时,不管该存储过程有无参数,都需要带括号;

  3. 在使用SET定义变量时应遵循SET的语法规则;SET @变量名=初始值;

  4. 在定义存储过程参数列表时,应注意参数名与数据库中字段名区别开来,否则将出现无法预期的结果;

1.12 Java代码调用存储过程(JDBC)

相关 API:java.sql.CallableStatement

使用到java.sql.CallableStatement接口,该接口专门用来调用存储过程;

该对象的获得依赖于java.sql.Connection;

通过Connection实例的prepareCall()方法返回CallableStatement对象

prepareCall()内部为一固定写法{call 存储过程名(参数列表1,参数列表2)}可用?占位

eg: connection.prepareCall(“{call proc_employee(?)}”);

存储过程中参数处理:

输入参数:通过java.sql.CallableStatement实例的setXXX()方法赋值,用法等同于java.sql.PreparedStatement

输出参数:通过java.sql.CallableStatement实例的registerOutParameter(参数位置, 参数类型)方法赋值,其中参数类型主要使用java.sql.Types中定义的类型

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
37
38
39
40
41
//Java代码调用带输入参数的存储过程 (根据输入ID查询雇员信息)
public void executeProcedure() {
try{
/**
*callableStatementjava.sql.CallableStatement
*connectionjava.sql.Connection
*jdbc调用存储过程原型
*{call存储过程名(参数列表1,参数列表2)}可用?代替
*/
callableStatement=connection.prepareCall("{call proc_employee_findById(?)}");
callableStatement.setInt(1, 1); //设置输入参数
resultSet=callableStatement.executeQuery();//执行存储过程
if(resultSet.next()){
System.out.println(resultSet.getInt(1)+"\\t"+resultSet.getString(2));
}
} catch (SQLException e) {
e.printStackTrace();
}
}

//Java代码调用带输出参数的存储过程 (返回数据库中的记录数)
public void executeProcedure() {
try {
/**
*callableStatementjava.sql.CallableStatement
*connectionjava.sql.Connection
*jdbc调用存储过程原型
*{call存储过程名(参数列表1,参数列表2)}可用?代替
*/
callableStatement=connection.prepareCall("{call proc_employee_getCount(?)}");
//设置输出参数
callableStatement.registerOutParameter(1, Types.*INTEGER*);
//执行存储过程
resultSet=callableStatement.executeQuery();
if(resultSet.next()) {
System.out.println(resultSet.getInt(1));
}
} catch (SQLException e) {
e.printStackTrace();
}
}

1.14 场景案例:

根据疫苗接种信息,定时更新社区疫苗接种总数与比例:

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
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
-- 创建存储过程之前需判断该存储过程是否已存在,若存在则删除
DROP PROCEDURE IF EXISTS pro_upd_z_vac_sta_num;
delimiter //
-- 创建存储过程
CREATE PROCEDURE pro_upd_z_vac_sta_num()
BEGIN
-- 定义变量
DECLARE p INT DEFAULT 0;
DECLARE cur_id_num VARCHAR(20);
DECLARE cur_batch VARCHAR(20);
DECLARE cur_com_id INT;
DECLARE cur_com_nam VARCHAR(30);
DECLARE sel_sta_num INT;
-- 定义游标,并将sql结果集赋值到游标中
DECLARE cur_vac_inf CURSOR FOR SELECT id_number,batch FROM u_vaccination_info WHERE has_statis=0;
-- 声明当游标遍历完后将标志变量置成某个值
DECLARE CONTINUE HANDLER FOR NOT FOUND SET p=1;
-- 打开游标
OPEN cur_vac_inf;
-- 将游标中的值赋值给变量,注意:变量名不要和返回的列名同名,变量顺序要和sql结果列的顺序一致
FETCH cur_vac_inf INTO cur_id_num,cur_batch;
-- 当s不等于1,也就是未遍历完时,会一直循环
WHILE p<>1 DO
loop_lable: LOOP
-- 获取社区名称
SELECT `community` INTO cur_com_nam FROM e_resident_info WHERE id_num=cur_id_num;
-- 获取社区编号
SELECT `id` INTO cur_com_id FROM e_community_info WHERE `name`=cur_com_nam;
IF NOT ISNULL(cur_com_id) THEN
LEAVE loop_lable;
END IF;
FETCH cur_vac_inf INTO cur_id_num,cur_batch;
END LOOP loop_lable;
-- 获取查询社区统计信息是否存在
SELECT COUNT(*) INTO sel_sta_num FROM z_vaccination_statistics WHERE `id`=cur_com_id;
IF sel_sta_num<1 THEN
INSERT INTO z_vaccination_statistics(`id`,`first_dose_num`,`first_dose_ratio`,`second_dose_num`,`second_dose_ratio`,`third_dose_num`,`third_dose_ratio`,`statis_date`) VALUES(cur_com_id,0,0,0,0,0,0,NOW());
END IF;
IF cur_batch LIKE '%第一剂次%' THEN
UPDATE z_vaccination_statistics SET `first_dose_num`=`first_dose_num`+1 where `id`=cur_com_id;
UPDATE u_vaccination_info SET `has_statis`=1 where `id_number`=cur_id_num;
ELSEIF cur_batch LIKE '%第二剂次%' THEN
UPDATE z_vaccination_statistics SET `second_dose_num`=`second_dose_num`+1 where `id`=cur_com_id;
UPDATE u_vaccination_info SET `has_statis`=1 where `id_number`=cur_id_num;
ELSE
UPDATE z_vaccination_statistics SET `third_dose_num`=`third_dose_num`+1 where `id`=cur_com_id;
UPDATE u_vaccination_info SET `has_statis`=1 where `id_number`=cur_id_num;
END IF;
-- 将游标中的值再赋值给变量,供下次循环使用
FETCH cur_vac_inf INTO cur_id_num,cur_batch;
-- 当s等于1时表明遍历以完成,退出循环
END WHILE;
-- 关闭游标
CLOSE cur_vac_inf;
END
//
delimiter ;
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
-- 创建存储过程之前需判断该存储过程是否已存在,若存在则删除
DROP PROCEDURE IF EXISTS pro_upd_z_vac_sta_rat;
delimiter //
-- 创建存储过程
CREATE PROCEDURE pro_upd_z_vac_sta_rat()
BEGIN
-- 定义变量
DECLARE p INT DEFAULT 0;
DECLARE cur_com_id INT;
DECLARE cur_fir_num INT;
DECLARE cur_sec_num INT;
DECLARE cur_thi_num INT;
DECLARE cur_com_per_num INT;
-- 定义游标,并将sql结果集赋值到游标中
DECLARE cur_vac_sta CURSOR FOR SELECT id,first_dose_num,second_dose_num,third_dose_num FROM z_vaccination_statistics;
-- 声明当游标遍历完后将标志变量置成某个值
DECLARE CONTINUE HANDLER FOR NOT FOUND SET p=1;
-- 打开游标
OPEN cur_vac_sta;
-- 将游标中的值赋值给变量,注意:变量名不要和返回的列名同名,变量顺序要和sql结果列的顺序一致
FETCH cur_vac_sta INTO cur_com_id,cur_fir_num,cur_sec_num,cur_thi_num;
-- 当s不等于1,也就是未遍历完时,会一直循环
while_lable:WHILE p<>1 DO
SELECT per_num INTO cur_com_per_num FROM e_community_info WHERE id=cur_com_id;
UPDATE z_vaccination_statistics SET `first_dose_ratio`=cur_fir_num/cur_com_per_num,`second_dose_ratio`=cur_sec_num/cur_com_per_num,`third_dose_ratio`=cur_thi_num/cur_com_per_num,`statis_date`=NOW() where `id`=cur_com_id;
-- 将游标中的值再赋值给变量,供下次循环使用
FETCH cur_vac_sta INTO cur_com_id,cur_fir_num,cur_sec_num,cur_thi_num;
-- 当s等于1时表明遍历以完成,退出循环
END WHILE while_lable;
-- 关闭游标
CLOSE cur_vac_sta;
END
//
delimiter ;
1
2
CALL pro_upd_z_vac_sta_num();
CALL pro_upd_z_vac_sta_rat();