mysql存储过程使用总结

最新推荐文章于 2024-07-25 11:20:42 发布

I_love_truth

最新推荐文章于 2024-07-25 11:20:42 发布

阅读量187

点赞数

分类专栏：存储过程文章标签： mysql

本文链接：https://blog.csdn.net/I_love_truth/article/details/111596348

版权

存储过程专栏收录该内容

1 篇文章 0 订阅

订阅专栏

文章目录

**mysql-存储过程使用总结**

mysql-存储过程使用总结

一.场景介绍

1.需求说明

在XXX项目开发过程中,遇到了这样一个需求.根据管理表的ID,将多张临时表的相关数据移动到正式表中,并且将管理表的状态字段进行一下更新已解析待确认-->已完成.

2.表结构说明

序号	类别	表名
1	正式表	def_entity_type
2	正式表	def_entity_type_attrs
3	正式表	def_relation_type
4	正式表	def_relation_type_attrs
5	临时表	def_entity_type_temp
6	临时表	def_entity_type_attrs_temp
7	临时表	def_relation_type_attrs
8	临时表	def_relation_type_attrs_temp
9	管理表	def_schema_sql_import

临时表与正式表一一对应,临时表命名规则为正式表表名+"_temp";
相较于正式表,临时表多了sql_import_id字段,sql_import_id引用管理表的ID作为外键;
管理表的字段信息如下所示

管理表字段-state状态的值为已解析待确认,已完成.

二.编写存储过程

1.编写基本的SQL语句

假定要删出的ID=111:

-- 将临时表数据移动到正式表中
insert into def_entity_type(id,name,source,remark,create_by,create_time) select id,name,source,remark,create_by,create_time from def_entity_type_temp where sql_import_id=1111;

insert into def_entity_type_attrs(id,name,attr_type,det_id,remark) select id,name,attr_type,det_id,remark from def_entity_type_attrs_temp where det_id in (select id from def_entity_type_temp where sql_import_id=1111);

insert into def_relation_type(id,name,det_from_id,det_from_name,det_to_id,det_to_name,source,remark,create_by,create_time) select id,name,det_from_id,det_from_name,det_to_id,det_to_name,source,remark,create_by,create_time from def_relation_type_temp where sql_import_id=1111;

insert into def_relation_type_attrs(id,name,attr_type,drt_id,remark) select id,name,attr_type,drt_id,remark from def_relation_type_attrs_temp where drt_id in (select id from def_relation_type_temp where sql_import_id=1111);

-- 删除临时表数据
delete from def_entity_type_temp where sql_import_id=1111;
delete from def_relation_type_temp where sql_import_id=1111;

-- 更新sql导入记录表的状态
UPDATE def_schema_sql_import SET state='已完成'  WHERE id=1111;

2.创建存储过程

编写存储过程脚本,并在Navicat中执行.

delimiter$$
DROP PROCEDURE IF EXISTS `proc_move_data`;
CREATE PROCEDURE `proc_move_data`()
BEGIN
	-- 将临时表数据移动到正式表中
	insert into def_entity_type(id,name,source,remark,create_by,create_time) select id,name,source,remark,create_by,create_time from def_entity_type_temp where sql_import_id=111;
	
	insert into def_entity_type_attrs(id,name,attr_type,det_id,remark) select id,name,attr_type,det_id,remark from def_entity_type_attrs_temp where det_id in (select id from def_entity_type_temp where sql_import_id=111);
	
	insert into def_relation_type(id,name,det_from_id,det_from_name,det_to_id,det_to_name,source,remark,create_by,create_time) select id,name,det_from_id,det_from_name,det_to_id,det_to_name,source,remark,create_by,create_time from def_relation_type_temp where sql_import_id=111;
	
	insert into def_relation_type_attrs(id,name,attr_type,drt_id,remark) select id,name,attr_type,drt_id,remark from def_relation_type_attrs_temp where drt_id in (select id from def_relation_type_temp where sql_import_id=111);
	
	-- 删除临时表数据
	delete from def_entity_type_temp where sql_import_id=111;
	delete from def_relation_type_temp where sql_import_id=111;
	
	-- 更新sql导入记录表的状态
	UPDATE def_schema_sql_import SET state='已完成'  WHERE id=111;
END$$

3.添加输入参数

因为需求中的ID为不确定值,所以需要添加输入参数.

IN $sqlId bigint:声明输入参数

IN用来表示输入参数
$sqlId表示变量名称
bigint 表示变量类型,变量类型值与字段类型值相同.

下面将 2.创建存储过程中的内容进行修改,添加输入参数,将111替换成变量名称

delimiter$$
DROP PROCEDURE IF EXISTS `proc_move_data`;
CREATE PROCEDURE `proc_move_data`(IN `$sqlId` bigint)
BEGIN
	-- 将临时表数据移动到正式表中
	insert into def_entity_type(id,name,source,remark,create_by,create_time) select id,name,source,remark,create_by,create_time from def_entity_type_temp where sql_import_id=$sqlId;
	

	insert into def_entity_type_attrs(id,name,attr_type,det_id,remark) select id,name,attr_type,det_id,remark from def_entity_type_attrs_temp where det_id in (select id from def_entity_type_temp where sql_import_id=$sqlId);
	
	insert into def_relation_type(id,name,det_from_id,det_from_name,det_to_id,det_to_name,source,remark,create_by,create_time) select id,name,det_from_id,det_from_name,det_to_id,det_to_name,source,remark,create_by,create_time from def_relation_type_temp where sql_import_id=$sqlId;
	
	insert into def_relation_type_attrs(id,name,attr_type,drt_id,remark) select id,name,attr_type,drt_id,remark from def_relation_type_attrs_temp where drt_id in (select id from def_relation_type_temp where sql_import_id=$sqlId);
	
	-- 删除临时表数据
	delete from def_entity_type_temp where sql_import_id=$sqlId;
	delete from def_relation_type_temp where sql_import_id=$sqlId;
	
	-- 更新sql导入记录表的状态
	UPDATE def_schema_sql_import SET state='已完成'  WHERE id=$sqlId;

END$$

4.添加事务

整个存储过程既有insert语句,也有delete,update语句;因此需要开启事务,保证这些语句要么一起成功,要么一起失败.

事务管理相关语法

语句	备注
start transaction;	开启事务操作
commit;	提交事务
rollback;	回滚

异常检测

如何判断一个事务应该提交还是回滚?mysql提供了异常检测机制,用于检测sql执行过程中发生的异常情况.
```
DECLARE handler_action HANDLER  
        FOR condition_value [, 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:检测到异常后程序的处理措施.continue表示继续,exit表示退出,UNDO-?
- condition_value:这个的话说明可以包括多种情况(方括弧表示可选的)，也就是一个handler可以定义成针对多种情况进行相应的操作；另外condition_value可以包括的值有上面列出来的6种.
  1. mysql_error_code，这个表示mysql的错误代码，错误代码是一个数字，完成是由mysql自己定义的。
  2. SQLSTATE [VALUE] sqlstate_value，这个同错误代码类似形成一一对应的关系，它是一个5个字符组成的字符串，关键的地方是它从ANSI SQL和ODBC这些标准中引用过来的，因此更加标准化，而不像上面的error_code完全是mysql自己定义给自己用的。
  3. condtion_name，这个是条件名称，它使用DECLARE…CONDITION语句来定义，这个后面我们会介绍如何定义自己的condition_name。
  4. SQLWARNING，表示SQLTATE中的字符串以‘01’起始的那些错误，比如Error: 1311 SQLSTATE: 01000 (ER_SP_UNINIT_VAR)
  5. NOT FOUND，表示SQLTATE中的字符串以‘02’起始的那些错误，比如Error: 1329 SQLSTATE: 02000 (ER_SP_FETCH_NO_DATA)
  6. SQLEXCEPTION，表示SQLSTATE中的字符串不是以’00’、‘01’、‘02’ 起始的那些错误，这里’00’起始的SQLSTATE其实表示的是成功执行而不是错误，另外两个就是上面的4和5的两种情况。
mysql存储过程事务中不支持的语法:

drop database…;

其他的没有进行测试过,最好不要在存储过程中使用DDL语句.
存储过程添加事务+异常检测

定义code变量,当发生异常时,设置code=‘1’;

在最后添加if else可控制语句:

if code != '0' THEN
	ROLLBACK; -- 回滚
  else
	commit;   -- 提交
 end if;

完整脚本如下所示

delimiter$$
DROP PROCEDURE IF EXISTS `proc_move_data`;
CREATE PROCEDURE `proc_move_data`(IN `$sqlId` bigint)
BEGIN
  DECLARE code varchar(50) DEFAULT '0';
  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION set code = '1';
  START TRANSACTION;
	-- 将临时表数据移动到正式表中
	insert into def_entity_type(id,name,source,remark,create_by,create_time) select id,name,source,remark,create_by,create_time from def_entity_type_temp where sql_import_id=$sqlId;

insert into def_entity_type_attrs(id,name,attr_type,det_id,remark) select id,name,attr_type,det_id,remark from def_entity_type_attrs_temp where det_id in (select id from def_entity_type_temp where sql_import_id=$sqlId);

insert into def_relation_type(id,name,det_from_id,det_from_name,det_to_id,det_to_name,source,remark,create_by,create_time) select id,name,det_from_id,det_from_name,det_to_id,det_to_name,source,remark,create_by,create_time from def_relation_type_temp where sql_import_id=$sqlId;

insert into def_relation_type_attrs(id,name,attr_type,drt_id,remark) select id,name,attr_type,drt_id,remark from def_relation_type_attrs_temp where drt_id in (select id from def_relation_type_temp where sql_import_id=$sqlId);

-- 删除临时表数据
delete from def_entity_type_temp where sql_import_id=$sqlId;
delete from def_relation_type_temp where sql_import_id=$sqlId;
-- 更新sql导入记录表的状态
UPDATE def_schema_sql_import SET state='已完成'  WHERE id=$sqlId;
  if code != '0' THEN
		ROLLBACK;
  else
		commit;
 end if;

END$$

4.返回异常信息

mysql5.6及以上提供GET DIAGNOSTICS CONDITION语法用于获取错误码以及错误详细信息.

添加输出参数

CREATE PROCEDURE `proc_move_data`(IN `$sqlId` bigint,OUT `$errorCode` varchar(50),OUT `$errorMsg` TEXT)

添加GET DIAGNOSTICS CONDITION

  DECLARE code varchar(50) DEFAULT '0';
  DECLARE msg TEXT;
  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
		  GET DIAGNOSTICS CONDITION 1
			code=RETURNED_SQLSTATE,msg = MESSAGE_TEXT;
-- 赋值输出参数		
  if code != '0' THEN
  	ROLLBACK;
    set $errorCode=code;
  	set $errorMsg=msg;
  else
  	commit;
  	set $errorCode=code;
  	set $errorMsg='success';

完整脚本如下所示

delimiter$$
DROP PROCEDURE IF EXISTS `proc_move_data`;
CREATE PROCEDURE `proc_move_data`(IN `$sqlId` bigint,OUT `$errorCode` varchar(50),OUT `$errorMsg` TEXT)
BEGIN
  DECLARE code varchar(50) DEFAULT '0';
  DECLARE msg TEXT;
  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
		  GET DIAGNOSTICS CONDITION 1
			code=RETURNED_SQLSTATE,msg = MESSAGE_TEXT;
		  START TRANSACTION;
			-- 将临时表数据移动到正式表中
			insert into def_entity_type(id,name,source,remark,create_by,create_time) select id,name,source,remark,create_by,create_time from def_entity_type_temp where sql_import_id=$sqlId;
			insert into def_entity_type_attrs(id,name,attr_type,det_id,remark) select id,name,attr_type,det_id,remark from def_entity_type_attrs_temp where det_id in (select id from def_entity_type_temp where sql_import_id=$sqlId);
			insert into def_relation_type(id,name,det_from_id,det_from_name,det_to_id,det_to_name,source,remark,create_by,create_time) select id,name,det_from_id,det_from_name,det_to_id,det_to_name,source,remark,create_by,create_time from def_relation_type_temp where sql_import_id=$sqlId;
			insert into def_relation_type_attrs(id,name,attr_type,drt_id,remark) select id,name,attr_type,drt_id,remark from def_relation_type_attrs_temp where drt_id in (select id from def_relation_type_temp where sql_import_id=$sqlId);
			-- 删除临时表数据
			delete from def_entity_type_temp where sql_import_id=$sqlId;
			delete from def_relation_type_temp where sql_import_id=$sqlId;
			-- 更新sql导入记录表的状态
			UPDATE def_schema_sql_import SET state='已完成'  WHERE id=$sqlId;
      if code != '0' THEN
			ROLLBACK;
            set $errorCode=code;
            set $errorMsg=msg;
	  else
			commit;
            set $errorCode=code;
            set $errorMsg='success';
	 end if;
END$$

三.java调用存储过程

原先使用mybatis调用存储过程,但是当存储过程中有输出参数时,死活取不到返回结果,一致提示返回结果值设置错误,无奈放弃了这种方式.下面使用jdbctemplate方式进行存储过程的调用

1.jdbctemplate调用存储过程

/**
	 * jdbcTemplate调用存储过程
	 */
	@Test
	public void jdbc2produre() {
		List<String> error = jdbcTemplate.execute(new CallableStatementCreator() {
			@Override
			public CallableStatement createCallableStatement(Connection con) throws SQLException {
				String proc = "{call proc_move_data(?,?,?)}";
				CallableStatement cs = con.prepareCall(proc);
				cs.setLong(1, 10000410000L);
				cs.registerOutParameter(2, Types.VARCHAR);
				cs.registerOutParameter(3, Types.VARCHAR);
				return cs;
			}
		}, new CallableStatementCallback<List<String>>() {
			@Override
			public List<String> doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
				List<String> list = new ArrayList<String>();
				cs.execute();
				list.add(cs.getString(2));
				list.add(cs.getString(3));
				return list;
			}
		});
		System.err.println("errorCode->[" + error.get(0) + "],errorMsg->[" + error.get(1) + "]");
	}

四.储过程编写时注意事项**

spring事务无法管理存储过程的事务,因此不要讲存储过程和其他更新插入语句放在一个方法里,这样是无法回滚的.
存储过程定义时一定要声明delimiter$$,否则会出现很多莫名其妙的编译错误

I_love_truth

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql存储过程使用总结

文章目录**mysql-存储过程使用总结**一.**场景介绍**1.需求说明2.表结构说明**二.编写存储过程**1.编写基本的SQL语句2.创建存储过程3.添加输入参数4.添加事务4.返回异常信息**三.java调用存储过程**1.jdbctemplate调用存储过程四.储过程编写时注意事项****mysql-存储过程使用总结**一.**场景介绍**1.需求说明2.表结构说明**二.编写存储过程**1.编写基本的SQL语句2.创建存储过程3.添加输入参数4.添加事务4.返回异常信息**三.java调用存储
复制链接

扫一扫