当一个存储过程过大时,就会不便于维护与调试。那么就可以把一个存储过程拆分成多个。
可以把拆分出来的存储过程存到一张表里面,然后取出表中的存储过程并在主存储过程中调用。
调用的语句如下:
WHILE(@num IS NOT NULL) DO
-- 获取所需的要执行的存储过程的名字
SET @proc_name = (SELECT `name` FROM `t_biz_dict_exec_proc` WHERE `sequence`= @num);
-- select @proc_name;
-- 执行被调用的存储过程
SET error_info = concat('调用存储过程失败:',@proc_name);
PREPARE proc FROM @proc_name;
EXECUTE proc USING @ias_id,@info_id,@res_id,@biz_type_id,@biz_sys_id,@time_stamp
,@ad_event,@ext_info,@client_ip,@client_language
,@client_resolution,@os_type,@app_name,@client_model,@browser_type
,@user_agent,@cookies_id,@cookies_date,@tr_status,@error_info;
DEALLOCATE PREPARE proc;
-- 按照顺序获取下一个存储过程
SET error_info = concat('获取顺序失败:',@num);
SET @num = (
SELECT MIN(`sequence`) FROM `t_biz_dict_exec_proc`
WHERE `biz_tab` = biz_tab AND `sequence` > @num
AND `main_proc_name` = masterProcName
AND `executable` = 1
);
-- 判断返回值的情况,并记录日志
IF @tr_status = 1 THEN
INSERT INTO `t_biz_statistics_log`(`proc_name`,`status`,`error_info`) VALUES(@proc_name,1,@error_info) ;
ELSE IF @tr_status = 2 THEN
SET @seqno = (SELECT max(`seqno`) FROM `t_info_access`);
INSERT INTO `t_biz_statistics_log`(`proc_name`,`data_seqno`,`error_info`) VALUES(@proc_name,@seqno,@error_info);
END IF;
END IF;
-- 恢复状态值的初始状态
SET @tr_status = 0;
END WHILE;
在执行存储过程时,要把错误信息记录到一张表里,方便接下来的调试。
在动态执行存储过程时,有两种方法拼接参数
1.
set @sqlstr=concat('call
pro_main',WhereCondition);prepare stmt from @sqlstr;
execute stmt;
使用concat将存储过程的调用语句和参数列表拼接起来,然后执行。
2.像上面代码一样,利用USING把参数一个个的传进去,但是USING要求传的参数一定要是用户变量。
这里会有一个隐患,就是用户变量是在一次连接内全局有效,就是如果在一次连接内,其他的存储过程中也定义了该变量,
那么就会把它的值改掉,所以在用户变量内的值有时可能不是你想要的,最好取名时进行分开。
-------------------------------------------------------------------------------------------------------------------------
存储过程的事务:
存储过程需要加事务控制:START TRANSACTION;然后通过commit或rollback来提交事务。
使用SET AUTOCOMMIT = 0 ;和START TRANSACTION等价。
如果没有对存储过程加上事务处理,那么当执行两条insert语句时:
insert into test.t_b values(100,'aadww',90);
insert into test.t_a values(22,'dede',23);
如果第二条执行失败,第一条正常,那么数据库中会将第一条语句提交并修改数据库。(默认insert会自动提交commit)
但是存储过程强调整体性,那么就需要对事务进行控制,以防止数据不一致的情况。
一般会设置一个状态值,来观察状态值的变化实现对事务的控制:
DELIMITER $$
CREATE PROCEDURE `test`.`add_row` ()
BEGIN
declare t_status int default 0;
DECLARE continue HANDLER FOR SQLSTATE '23000' SET t_status = 1;
DECLARE continue HANDLER FOR SQLEXCEPTION SET t_status = 2;
START TRANSACTION;
insert into test.t_b values(100,'aadww',90);
select t_status;
insert into test.t_a values(22,'dede',23);
select t_status;
if t_status <> 0 then
rollback;
else
commit;
end if;
END