设计mysql存储过程_mysql存储过程设计

当一个存储过程过大时，就会不便于维护与调试。那么就可以把一个存储过程拆分成多个。

可以把拆分出来的存储过程存到一张表里面，然后取出表中的存储过程并在主存储过程中调用。

调用的语句如下：

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