MySQL 存储过程编程

MySQL 存储过程编程

1、语法

create
[definer=user] – DEFINER模式下，默认DEFINER=CURRENT_USER，在存储过程执行时，mysql会检查DEFINER定义的用户’user_name’@'host_name’的权限
procedure sp_name([proc_parameter[,…]])
[characteristic…]
routine_body
说明
proc_parameter:
in param_name data_type：-- 输入类型 data_type：mysql数据类型，例如：int,varchar
out param_name data_type： – 输出类型
inout param_name data_type： – 混合类型,即可以输入，在存储过程里面也可以修改，最后输出

characteristic: {
COMMENT ‘string’ – 存储过程的注释性信息写在COMMENT里面
| LANGUAGE SQL – 存储过程语言，默认是sql，说明存储过程中使用的是sql语言编写的，暂时只支持sql，后续可能会支持其他语言
| [NOT] DETERMINISTIC – 是否确定性的输入就是确定性的输出，默认是NOT DETERMINISTIC，只对于同样的输入，输出也是一样的，当前这个值还没有使用
| { CONTAINS SQL | – 表示子程序不包含读或者写数据的语句
NO SQL | – 表示子程序不包含sql
READS SQL DATA | – 表示子程序包含读数据的语句，但是不包含写数据的语句
MODIFIES SQL DATA – 表示子程序包含写数据的语句
}
| SQL SECURITY { DEFINER | INVOKER } – 用来指定存储过程是使用创建者的许可来执行，还是执行者的许可来执行，默认值是DEFINER
}

routine_body:
Valid SQL routine statement

2、案例

用户表
create table user_info(
id int not null auto_increment,
name varchar(200),
id_number varchar(50),
primary key(id)
);
insert into user_info values (1,‘张三’,‘420123199001011234’);
insert into user_info values (2,‘李四’,‘420123199001011235’);
insert into user_info values (3,‘王五’,‘420123199001011236’);

用户历史数据表
create table user_info_history(
id int not null auto_increment,
type varchar(50),
user_id int,
name varchar(200),
id_number varchar(50),
create_time datetime,
primary key(id)
);

用户记录删除审计表
create table audit_log(
id int not null auto_increment ,
type varchar(50),
login_user varchar(50),
audit_date datetime,
user_id int,
primary key(id)
);

需求：对于删除的数据，需要保留用户数据到历史表，并保留一天删除信息到审计表

-- 未添加事务，有数据缺失的风险
create procedure p_del_user(in p_user_id int,
                            in p_login_user varchar(50))
begin
	-- 第一步：将删除的数据保存在用户历史表
	insert into user_info_history(type,user_id,name,id_number,create_time)
	select 'delete',
				 id,
				 name,
				 id_number,
				 now()
	from user_info
	where id=p_user_id;
	

-- 第二步：删除用户数据
delete from user_info where id=p_user_id;

-- 第三步：增加审计日志
insert into audit_log(type,login_user,audit_date,user_id)
values('delete',p_login_user,now(),p_user_id);

end;

call p_del_user(1,"xian");
select * from user_info;
select * from user_info_history;
select * from audit_log;

-- 添加事务，保证数据的一致性

create procedure p_del_user_with_trans(in p_user_id int,
                                       in p_login_user varchar(50))
begin
	-- 开启事务
	start transaction;
	

-- 第一步：将删除的数据保存在用户历史表
insert into user_info_history(type,user_id,name,id_number,create_time)
select 'delete',
			 id,
			 name,
			 id_number,
			 now()
from user_info
where id=p_user_id;

-- 第二步：删除用户数据
delete from user_info where id=p_user_id;

-- 第三步：增加审计日志
insert into audit_log(type,login_user,audit_date,user_id)
values('delete',p_login_user,now(),p_user_id);

-- 开启事务后，需要手动提交
commit;

end;

-- 防止保存，成功时commit，失败时rollback，返回一个标识
create procedure p_del_user_with_trans_result(in p_user_id int,
																				 in p_login_user varchar(50),
																				 out result varchar(200)
																				 )
begin
	-- 声明变量
	declare var_error int default 0;
	-- 当脚本出现异常时，设置var_error=1
	declare continue handler for sqlexception set var_error=1;
	

set result = concat('删除',p_user_id,'成功！');
-- 开启事务
start transaction;

-- 第一步：将删除的数据保存在用户历史表
insert into user_info_history(type,user_id,name,id_number,create_time)
select 'delete',
			 id,
			 name,
			 id_number,
			 now()
from user_info
where id=p_user_id;

-- 第二步：删除用户数据
delete from user_info where id=p_user_id;

-- 第三步：增加审计日志
insert into audit_log(type,login_user,audit_date,user_id)
values('delete',p_login_user,now(),p_user_id);

-- 判断成功
if var_error = 0 then 
	commit;
else 
	rollback;
	-- 输出，失败标识
	set result = concat('删除',p_user_id,'失败！');
end if;

end;