Mysql 存储过程的使用

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

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

什么是存储过程

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

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

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

存储过程和函数的区别

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

创建存储过程

先上个例子

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 的循环条件判断字符串函数截取等

# 删除存储过程
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();