在我的后台系统中，今天需要使用到存储过程。存储过程还真没写过，今天就写了个存储过程。使用在后台中。
其实这个接口功能 是涉及几张表的修改，删除，新增的。就写个一个存储过程。
存储过程：
1 CREATE PROCEDURE `sp_combine_admin_group`(IN `group_id1` int(11), IN `group_id2` int(11),IN `admin_user_id` int(11),IN `group_name` varchar(64),IN `description` varchar(1024), out `t_error` INT(11)) 2 BEGIN 3 DECLARE new_group_id int(11); 4 DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET t_error=0; 5 SET t_error = 1; 6 START TRANSACTION; 7 INSERT INTO admin_group (`group_name`,`description`) values ( group_name,description); 8 SET new_group_id =LAST_INSERT_ID(); 9 UPDATE group_special_power SET group_id= new_group_id where group_id =group_id1 or group_id= group_id2; 10 UPDATE group_menu_power SET group_id= new_group_id where group_id =group_id1 or group_id= group_id2; 11 UPDATE group_hospital_power SET group_id= new_group_id where group_id =group_id1 or group_id= group_id2; 12 UPDATE admin_user SET is_admin=0 , admin_group_id=new_group_id where admin_group_id =group_id1 or admin_group_id= group_id2; 13 UPDATE admin_user SET is_admin=1 where id=admin_user_id; 14 DELETE FROM admin_group where id =group_id1 or id= group_id2; 15 IF t_error = 0 THEN 16 ROLLBACK; 17 ELSE 18 COMMIT; 19 END IF; 20 END;
--Mapper.xml文件调用存储过程
1 <insert id="insertAdminGroupCombine" parameterType="org.system.entity.admin.AdminGroup" useGeneratedKeys="true" keyColumn="id" statementType="CALLABLE" > 2 {call sp_combine_admin_group( 3 #{group_id1,jdbcType=INTEGER,mode=IN}, 4 #{group_id2,jdbcType=INTEGER,mode=IN}, 5 #{admin_user_id,jdbcType=INTEGER,mode=IN}, 6 #{group_name,jdbcType=VARCHAR,mode=IN}, 7 #{description,jdbcType=VARCHAR,mode=IN}, 8 #{t_error,jdbcType=INTEGER,mode=OUT})} 9 </insert>
