循环遍历游标,实现每个业务场景,其中突然在一个记录之后,游标强行退出了,调试了3个小时,才发现有bug的sql块如下:
select row_num+0 into v_style8_row from csf.tmp_excel_data ted
where cmp_id = v_cmp_id and rpt_cd = v_rpt_cd and col_num = 2 and data_value = 'ACCOUNTING & DISCLOSURE STANDARDS' ;
select count(1) into v_yes_flag from csf.tmp_excel_data ted
where cmp_id = v_cmp_id and rpt_cd = v_rpt_cd and row_num > (v_style8_row + 2) and col_num > 1 and data_value !='None';
select count(1) into v_yes_flag from csf.tmp_excel_data ted
where cmp_id = v_cmp_id and rpt_cd = v_rpt_cd and row_num = (v_style8_row + 2) and col_num > 1 and data_value !='None';
后来修改为如下的,加了一个判断,问题ok,close了!
select count(1) into v_yes_flag from csf.tmp_excel_data ted
where cmp_id = v_cmp_id and rpt_cd = v_rpt_cd and col_num = 2 and data_value = 'ACCOUNTING & DISCLOSURE STANDARDS' ;
if v_yes_flag>0 then
select row_num+0 into v_style8_row from csf.tmp_excel_data ted
where cmp_id = v_cmp_id and rpt_cd = v_rpt_cd and col_num = 2 and data_value = 'ACCOUNTING & DISCLOSURE STANDARDS' ;
select count(1) into v_yes_flag from csf.tmp_excel_data ted
where cmp_id = v_cmp_id and rpt_cd = v_rpt_cd and row_num > (v_style8_row + 2) and col_num > 1 and data_value !='None';
select count(1) into v_yes_flag from csf.tmp_excel_data ted
where cmp_id = v_cmp_id and rpt_cd = v_rpt_cd and row_num = (v_style8_row + 2) and col_num > 1 and data_value !='None';
else
set v_yes_flag=0;
end if;
这是去年刚开始搞mysql的时候写的存储过程里面遍历游标的某些sql语句,伤不起啊,当时赶时间,没有考虑仔细,大家以后千万不要范这样的小错误啊!
[
本帖最后由 mysqldbd 于 2011-8-2 11:09 编辑 ]
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/25267658/viewspace-703797/,如需转载,请注明出处,否则将追究法律责任。