过程如下,在执行过程中发现, CURSOR C_UPDDATA IS
SELECT SERIAL_NO,CUST_CODE,IMG_CLSES from STK_IMG_LOG
WHERE STKEX = '0' AND SUBMIT_STATUS = '2' AND CUST_CODE != 0
AND BIZ_DATE BETWEEN 20110101 AND 20110201 order by SERIAL_NO;
这个游标的查询结果明明有值,可是缺不进入循环。经过检查发现,在实际运行中,发现游标的结果是空的。请高手帮忙解决!在线等CREATE OR REPLACE PROCEDURE IMG_LOSS_XZ(is_bgndate in integer,
is_enddate in integer) AS
vs_o_SERIAL_NO NUMERIC(19,0);
vs_o_CUST_CODE NUMERIC(10, 0);
vs_o_IMG_CLSES VARCHAR(200); CURSOR C_UPDDATA IS
SELECT SERIAL_NO,CUST_CODE,IMG_CLSES from STK_IMG_LOG
WHERE STKEX = '0' AND SUBMIT_STATUS = '2' AND CUST_CODE != 0
AND BIZ_DATE BETWEEN 20110101 AND 20110201 order by SERIAL_NO;
TYPE UPDDATA IS RECORD(
vs_o_SERIAL_NO STK_IMG_LOG.SERIAL_NO%TYPE,
vs_o_CUST_CODE STK_IMG_LOG.CUST_CODE%TYPE,
vs_o_IMG_CLSES STK_IMG_LOG.IMG_CLSES%TYPE
);
r_update UPDDATA;
vs_r_IMG_SNES VARCHAR(200);
vs_r_Submit_Status CHAR(1);
vs_r_SERIAL_NO NUMERIC(19,0);
vs_m_IMG_CLS varchar(3);
vs_r_IMG_SN varchar(17);
vs_i NUMERIC(10,0);
vs_TotalLength NUMERIC(10,0);
vs_rec_cnt NUMERIC(10,0);
vs_Sql_Str varchar(8000); type t_sor is ref cursor;
C_SYSTAB t_sor;
c_sqlStr varchar(250);
BEGIN
delete from Upd_Sql;
commit; open C_UPDDATA;
Loop
fetch C_UPDDATA into r_update;
vs_i := 1;
vs_TotalLength := length(trim(r_update.vs_o_IMG_CLSES));
vs_r_Submit_Status := '2';
vs_r_IMG_SNES := '';
while vs_i < vs_TotalLength Loop
vs_m_IMG_CLS := SubStr(r_update.vs_o_IMG_CLSES,vs_i,3);
if COALESCE(vs_m_IMG_CLS,'0') <> '0' then
select max(img_sn) into vs_r_IMG_SN from user_img_info
where img_status = '0'
and user_code = r_update.vs_o_CUST_CODE
and img_cls = vs_m_IMG_CLS;
select count(*),b.serial_no into vs_rec_cnt,vs_r_SERIAL_NO from frt_biz_img a,frt_biz_log b
where a.serial_no = b.serial_no
and a.img_sn = vs_r_IMG_SN
group by b.serial_no;
if vs_rec_cnt > 0 then
vs_r_IMG_SNES := vs_r_IMG_SNES || vs_r_IMG_SN||',';
end if;
end if;
vs_i:=vs_i +4;
end Loop; if COALESCE(vs_r_IMG_SNES,'0') != '0' then
vs_r_IMG_SNES := substr(vs_r_IMG_SNES,1,length(vs_r_IMG_SNES)-1);
end if;
if length(COALESCE(vs_r_IMG_SNES,'0')) = ((trunc((vs_i - 1)/4))*17 - 1) then
vs_r_Submit_Status := '0';
end if ;
if vs_TotalLength > 0 then
vs_Sql_Str := 'UPDATE STK_IMG_LOG SET SERIAL_NO = ''' || vs_r_SERIAL_NO||''','
||' IMG_SNES = ''' ||vs_r_IMG_SNES||''','
||' SUBMIT_STATUS = '''|| vs_r_Submit_Status ||''' '
||' WHERE SERIAL_NO = ''' || r_update.vs_o_SERIAL_NO || ''';';
insert into Upd_Sql values (vs_Sql_Str);
end if;
exit when C_UPDDATA%notfound;
end Loop;
close C_UPDDATA;
END;
SELECT SERIAL_NO,CUST_CODE,IMG_CLSES from STK_IMG_LOG
WHERE STKEX = '0' AND SUBMIT_STATUS = '2' AND CUST_CODE != 0
AND BIZ_DATE BETWEEN 20110101 AND 20110201 order by SERIAL_NO;
这个游标的查询结果明明有值,可是缺不进入循环。经过检查发现,在实际运行中,发现游标的结果是空的。请高手帮忙解决!在线等CREATE OR REPLACE PROCEDURE IMG_LOSS_XZ(is_bgndate in integer,
is_enddate in integer) AS
vs_o_SERIAL_NO NUMERIC(19,0);
vs_o_CUST_CODE NUMERIC(10, 0);
vs_o_IMG_CLSES VARCHAR(200); CURSOR C_UPDDATA IS
SELECT SERIAL_NO,CUST_CODE,IMG_CLSES from STK_IMG_LOG
WHERE STKEX = '0' AND SUBMIT_STATUS = '2' AND CUST_CODE != 0
AND BIZ_DATE BETWEEN 20110101 AND 20110201 order by SERIAL_NO;
TYPE UPDDATA IS RECORD(
vs_o_SERIAL_NO STK_IMG_LOG.SERIAL_NO%TYPE,
vs_o_CUST_CODE STK_IMG_LOG.CUST_CODE%TYPE,
vs_o_IMG_CLSES STK_IMG_LOG.IMG_CLSES%TYPE
);
r_update UPDDATA;
vs_r_IMG_SNES VARCHAR(200);
vs_r_Submit_Status CHAR(1);
vs_r_SERIAL_NO NUMERIC(19,0);
vs_m_IMG_CLS varchar(3);
vs_r_IMG_SN varchar(17);
vs_i NUMERIC(10,0);
vs_TotalLength NUMERIC(10,0);
vs_rec_cnt NUMERIC(10,0);
vs_Sql_Str varchar(8000); type t_sor is ref cursor;
C_SYSTAB t_sor;
c_sqlStr varchar(250);
BEGIN
delete from Upd_Sql;
commit; open C_UPDDATA;
Loop
fetch C_UPDDATA into r_update;
vs_i := 1;
vs_TotalLength := length(trim(r_update.vs_o_IMG_CLSES));
vs_r_Submit_Status := '2';
vs_r_IMG_SNES := '';
while vs_i < vs_TotalLength Loop
vs_m_IMG_CLS := SubStr(r_update.vs_o_IMG_CLSES,vs_i,3);
if COALESCE(vs_m_IMG_CLS,'0') <> '0' then
select max(img_sn) into vs_r_IMG_SN from user_img_info
where img_status = '0'
and user_code = r_update.vs_o_CUST_CODE
and img_cls = vs_m_IMG_CLS;
select count(*),b.serial_no into vs_rec_cnt,vs_r_SERIAL_NO from frt_biz_img a,frt_biz_log b
where a.serial_no = b.serial_no
and a.img_sn = vs_r_IMG_SN
group by b.serial_no;
if vs_rec_cnt > 0 then
vs_r_IMG_SNES := vs_r_IMG_SNES || vs_r_IMG_SN||',';
end if;
end if;
vs_i:=vs_i +4;
end Loop; if COALESCE(vs_r_IMG_SNES,'0') != '0' then
vs_r_IMG_SNES := substr(vs_r_IMG_SNES,1,length(vs_r_IMG_SNES)-1);
end if;
if length(COALESCE(vs_r_IMG_SNES,'0')) = ((trunc((vs_i - 1)/4))*17 - 1) then
vs_r_Submit_Status := '0';
end if ;
if vs_TotalLength > 0 then
vs_Sql_Str := 'UPDATE STK_IMG_LOG SET SERIAL_NO = ''' || vs_r_SERIAL_NO||''','
||' IMG_SNES = ''' ||vs_r_IMG_SNES||''','
||' SUBMIT_STATUS = '''|| vs_r_Submit_Status ||''' '
||' WHERE SERIAL_NO = ''' || r_update.vs_o_SERIAL_NO || ''';';
insert into Upd_Sql values (vs_Sql_Str);
end if;
exit when C_UPDDATA%notfound;
end Loop;
close C_UPDDATA;
END;
end if;
exit when C_UPDDATA%notfound;
end Loop;
没有commit,
EXIT WHEN C_UPDDATA%NOTFOUND;
放到fetch C_UPDDATA into r_update;的下一条语句再调试一下
while vs_i < vs_TotalLength Loop
vs_m_IMG_CLS := SubStr(r_update.vs_o_IMG_CLSES,vs_i,3);
if COALESCE(vs_m_IMG_CLS,'0') <> '0' then
select max(img_sn) into vs_r_IMG_SN from user_img_info
where img_status = '0'
and user_code = r_update.vs_o_CUST_CODE
and img_cls = vs_m_IMG_CLS;
select count(*),b.serial_no into vs_rec_cnt,vs_r_SERIAL_NO from frt_biz_img a,frt_biz_log b
where a.serial_no = b.serial_no
and a.img_sn = vs_r_IMG_SN
group by b.serial_no;
if vs_rec_cnt > 0 then
vs_r_IMG_SNES := vs_r_IMG_SNES || vs_r_IMG_SN||',';
end if;
end if;
vs_i:=vs_i +4;
end Loop;