-- --------------------------------------------------------------------------------
-- Routine DDL
-- --------------------------------------------------------------------------------
DELIMITER $$
USE `csf`$$
DROP procedure IF EXISTS `proc_pub_style1_split`$$
USE `csf`$$
CREATE PROCEDURE `proc_pub_style1_split`()
begin
declare v_log_id INT;
declare _done int default 0;
declare v_rs_code varchar(1);
declare v_cmp_id int;
declare cur_cmp cursor for select distinct cmp_id from csf_pub.rpt_style1 where cmp_id<4 order by cmp_id;
declare continue handler for not found
begin
set _done=1;
INSERT INTO csf.sys_log_pub_detail (log_id, log_type, log_cmp_id, log_search_msg, log_msg)
VALUE (v_log_id, 2, p_cmp_id, "proc_pub_style1_split ","split style1!");
commit;
end;
declare exit handler for sqlexception
begin
select v_cmp_id, 'Sqlexception';
end;
SELECT LAST_INSERT_ID() INTO v_log_id; open cur_cmp;
allcodes:loop
fetch cur_cmp into v_cmp_id;
if not _done then
select md5(v_cmp_id) into v_rs_code;
set @v_Sql=concat('delete from csf_pub.rpt_style1_',v_rs_code,' where cmp_id=', v_cmp_id,'; insert into csf_pub.rpt_style1_', v_rs_code,' select * from csf_pub.rpt_style1 where cmp_id=', v_cmp_id, ';');
select v_cmp_id,@v_Sql;
prepare stmt from @v_Sql;
select 1;
execute stmt;
else
leave allcodes;
end if;
end loop allcodes;
close cur_cmp;
END在打开游标之后,第一次遍历,当v_cmp_id=1的时候,执行到 prepare stmt from @v_Sql; 的时候报错了,直接 就跳到了下面的异常处理过程
declare exit handler for sqlexception
begin
select v_cmp_id, 'Sqlexception';
end;
然后就跳出来了。大家帮我看下,如何改正,难道遍历游标的时候,不能执行动态sql吗?
-- Routine DDL
-- --------------------------------------------------------------------------------
DELIMITER $$
USE `csf`$$
DROP procedure IF EXISTS `proc_pub_style1_split`$$
USE `csf`$$
CREATE PROCEDURE `proc_pub_style1_split`()
begin
declare v_log_id INT;
declare _done int default 0;
declare v_rs_code varchar(1);
declare v_cmp_id int;
declare cur_cmp cursor for select distinct cmp_id from csf_pub.rpt_style1 where cmp_id<4 order by cmp_id;
declare continue handler for not found
begin
set _done=1;
INSERT INTO csf.sys_log_pub_detail (log_id, log_type, log_cmp_id, log_search_msg, log_msg)
VALUE (v_log_id, 2, p_cmp_id, "proc_pub_style1_split ","split style1!");
commit;
end;
declare exit handler for sqlexception
begin
select v_cmp_id, 'Sqlexception';
end;
SELECT LAST_INSERT_ID() INTO v_log_id; open cur_cmp;
allcodes:loop
fetch cur_cmp into v_cmp_id;
if not _done then
select md5(v_cmp_id) into v_rs_code;
set @v_Sql=concat('delete from csf_pub.rpt_style1_',v_rs_code,' where cmp_id=', v_cmp_id,'; insert into csf_pub.rpt_style1_', v_rs_code,' select * from csf_pub.rpt_style1 where cmp_id=', v_cmp_id, ';');
select v_cmp_id,@v_Sql;
prepare stmt from @v_Sql;
select 1;
execute stmt;
else
leave allcodes;
end if;
end loop allcodes;
close cur_cmp;
END在打开游标之后,第一次遍历,当v_cmp_id=1的时候,执行到 prepare stmt from @v_Sql; 的时候报错了,直接 就跳到了下面的异常处理过程
declare exit handler for sqlexception
begin
select v_cmp_id, 'Sqlexception';
end;
然后就跳出来了。大家帮我看下,如何改正,难道遍历游标的时候,不能执行动态sql吗?
解决方案 »
- 求助MySQL自动删除过期数据(不用event scheduler)
- mysql语句的写法
- 连接mysql时出现错误,在线等!!急急急!!
- 求一mysql语句,题目如下:(补充上午的)
- mysql获取前10条数据
- postgresql有没有判断游标打开或关闭的属性?
- PostgreSQL中执行查询的问题
- 这个功能能不能用一条SQL语句搞定?(在线等,超急!)
- mysql error:Access denied for user root@localhost using password yes
- 远程或者本地连接MySQL时报错1130 host is not allowed to connect to this mysql server
- C++和MYSQL的多SQL语句问题
- postgresql 通配符转译问题
delete from csf_pub.rpt_style1_c where cmp_id=1; insert into csf_pub.rpt_style1_c select * from csf_pub.rpt_style1 where cmp_id=1;
单独执行,没有任何问题的。是ok的。
mysql存储过程里面的sqlexception不捕捉具体的错误信息,只告诉用户错误了。
delete from csf_pub.rpt_style1_c where cmp_id=1;
再执行
insert into csf_pub.rpt_style1_c select * from csf_pub.rpt_style1 where cmp_id=1;
那就是要设立2个动态sql了,我去试试看吧。
delete from csf_pub.rpt_style1_c where cmp_id=1;
insert into csf_pub.rpt_style1_c select * from csf_pub.rpt_style1 where cmp_id=1
是否有满足条件的记录
SET @asql=CONCAT('select * from aa;','select * from cmp;');
PREPARE stml FROM @asql;
EXECUTE stml
提示出错