Mysql 存储过程的使用

最近发现有张用户信息表中的性别字段 由于之前的问题 没有正确维护进数据库中 现在需要维护下 本想直接写个程序跑一下

想想用Mysql来实现应该会很有意思 所以决定用存储过程来完成字段的维护,顺便复习下存储过程的使用


什么是存储过程

存储过程(Stored Procedure) 是一组为了完成特定功能的 SQL 语句集合

它主要用来完成 CURD 操作 其中可以使用流程控制语句 来完成复杂的判断和 复杂的运算等

在经过编译后可以存储在数据库中 用户可以通过调用存储过程(传入相关参数[并不是必须]) 来调用它

存储过程和函数的区别

1.函数必须要有返回值, 而存储过程没有这个限制
2.函数只能返回一个变量, 而存储过程可以返回多个
3.函数的参数只有 in类的 就是输出参数, 而存储过程可以有 In Out Input 3种类型参数
4.函数中不能进行修改全局数据库状态的操作,而存储过程可以
5.函数可以做为查询语句的一部分来使用,而存储过程不可以

创建存储过程

先上个例子

1
2
3
4
5
6
DELIMITER //
CREATE PROCEDURE proc1(int s varchar(20),out ss int,input sss double)
BEGIN
SELECT COUNT(*) INTO s FROM user;
END //
DELIMITER ;

讲解:

(1)这里需要注意的是DELIMITER//和DELIMITER;两句,DELIMITER是分割符的意思,因为MySQL默认以”;”为分隔 符,如果我们没有声明分割符,那么编译器会把存储过程当成SQL语句进行处理,则存储过程的编译过程会报错,所以要事先用DELIMITER关键字申明当 前段分隔符,这样MySQL才会将”;”当做存储过程中的代码,不会执行这些代码,用完了之后要把分隔符还原。
(2)存储过程根据需要可能会有输入、输出、输入输出参数,这里有3个参数 第一个参数s 是输入参数 varchar类型, 第二个参数是ss 是输出参数 第三个参数sss 类型是输入输出参数, 你可以随便定义自己需要的参数 不要按照上面的顺序 上面例子参数不是必须的

存储过程的调用

1
call procedure_name(parame1,parame2,parame3);

存储过程完成表信息字段维护

回到开局问题 下面用存储过程来完成员工表中性别字段的维护
包含了mysql 的循环 条件判断 字符串函数截取等

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
58
59
60
61
62
63
64
65
66
67
# 删除存储过程
drop procedure if exists emp_change;
delimiter //
# 创建一个存储过程
create procedure emp_change()
begin
# 当前的记录id
declare this_id int;
# 声明待判断的身份证值
declare curren_check int;
# 当前的gender
declare this_gender tinyint;
# 当前的身份证号
declare this_card_no varchar(20);
# 声明一个是否执行完的标识
declare done int default 0;
# 把查询结果设置到c1中 先简单的拿2条数据测试下
declare c1 cursor for select id,card_no from employees where gender = 0;
# 声明一个异常 如果结果集遍历完 就把done标识设置为1 标识结束
declare continue handler for sqlstate '02000' set done = 1;
open c1;
# 开启循环
repeat
# 先设置一个默认值
set this_gender = 0;
set curren_check = 0;
# 取出一条结果集
fetch c1 into this_id, this_card_no;
# 如果done标识可以继续执行
if not done then
# 如果是15位身份证号码
if CHAR_LENGTH(this_card_no) = 15 then
# 截取第15位值
set curren_check = right(this_card_no, 1);
if mod(curren_check,2) = 0 then
# 整除了 就是 女
set this_gender = 2;
else
set this_gender = 1;
end if;
# 如果是18位身份证号码
elseif CHAR_LENGTH(this_card_no) = 18 then
# 截取第17位
set curren_check = substring(this_card_no, -2, 1);
if mod(curren_check,2) = 0 then
# 整除了 就是 女
set this_gender = 2;
else
set this_gender = 1;
end if;
end if;
# 设置好gender了 然后开始变更sql
update employees set gender = this_gender where id = this_id;
end if;
until done end repeat;
# 关闭
close c1;
end //
delimiter ;
# 执行
# call emp_change();

关于存储过程的使用基本就这样了

参考文档:

https://www.jianshu.com/p/7b2d74701ccd
https://www.jianshu.com/p/53a12af46836
http://www.cnblogs.com/chenmh/p/5201473.html

感谢大家的分享


-------------The End-------------