create or replace procedure COMPARE(comdate in char) isCURSOR COMPARE_CURSOR IS
SELECT NBCODE,WBCODE,GDLY FROM B;
NEICODE VARCHAR2(6);
WAICODE VARCHAR2(6);
GOUDLY VARCHAR2(30);
USE_DATE DATE;
NYGM NUMBER;
NWGM NUMBER;
NFM NUMBER;
NPM NUMBER;
NGM NUMBER;
NJM NUMBER;
WYGM NUMBER;
WWGM NUMBER;
WFM NUMBER;
WPM NUMBER;
WGM NUMBER;
WJM NUMBER;
begin
SELECT TO_DATE(comdate,'yyyyMM') INTO USE_DATE FROM DUAL; DELETE FROM DIFFER WHERE COM_DATE=comdate;
COMMIT;
OPEN COMPARE_CURSOR;
LOOP
FETCH COMPARE_CURSOR INTO NEICODE,WAICODE,GOUDLY;
EXIT WHEN COMPARE_CURSOR %NOTFOUND;
SELECT YZM,FZM,PZM,GZM,JZM,WZM
INTO NYGM,NFM,NPM,NGM,NJM,NWGM
FROM C WHERE C_DATE=USE_DATE AND CODE=NEICODE AND ROWNUM=1;
SELECT YZM,FZM,PZM,GZM,JZM,WZM
INTO WYGM,WFM,WPM,WGM,WJM,WWGM
FROM C WHERE C_DATE=USE_DATE AND CODE=WAICODE AND ROWNUM=1;
IF ABS(NYGM-WYGM)>2 OR ABS(NFM-WFM)>2 OR ABS(NPM-WPM)>2 OR ABS(NGM-WGM)>2 OR ABS(NJM-WJM)>2 OR ABS(NWGM-WWGM)>2 THEN
INSERT INTO DIFFER(DIFFER_ID,GDLY,COM_DATE,
NBCODE,NBYGM,NBFM,NBPM,NBGM,NBJM,NBWGM,
WBCODE,WBYGM,WBFM,WBPM,WBGM,WBJM,WBWGM)
VALUES(
substr(to_char(dbms_random.value*10),3,32),GOUDLY,comdate,
NEICODE,NYGM,NFM,NPM,NGM,NJM,NWGM,
WAICODE,WYGM,WFM,WPM,WGM,WJM,WWGM
);
COMMIT;
END IF;
END LOOP;
CLOSE COMPARE_CURSOR;
end COMPARE;
在 SELECT YZM,FZM,PZM,GZM,JZM,WZM
INTO NYGM,NFM,NPM,NGM,NJM,NWGM
FROM C WHERE C_DATE=USE_DATE AND CODE=NEICODE AND ROWNUM=1
SELECT YZM,FZM,PZM,GZM,JZM,WZM
INTO NYGM,NFM,NPM,NGM,NJM,NWGM
FROM C WHERE C_DATE=USE_DATE AND CODE=WAICODE AND ROWNUM=1
会有 NO_DATA_FOUND EXCEPTION 我想去掉这个异常 用 EXCEPTION WHEN NO_DATA_FOUND THEN CONTINUE;END;不知道加在哪里,总是报 PLS-00103错误
SELECT NBCODE,WBCODE,GDLY FROM B;
NEICODE VARCHAR2(6);
WAICODE VARCHAR2(6);
GOUDLY VARCHAR2(30);
USE_DATE DATE;
NYGM NUMBER;
NWGM NUMBER;
NFM NUMBER;
NPM NUMBER;
NGM NUMBER;
NJM NUMBER;
WYGM NUMBER;
WWGM NUMBER;
WFM NUMBER;
WPM NUMBER;
WGM NUMBER;
WJM NUMBER;
begin
SELECT TO_DATE(comdate,'yyyyMM') INTO USE_DATE FROM DUAL; DELETE FROM DIFFER WHERE COM_DATE=comdate;
COMMIT;
OPEN COMPARE_CURSOR;
LOOP
FETCH COMPARE_CURSOR INTO NEICODE,WAICODE,GOUDLY;
EXIT WHEN COMPARE_CURSOR %NOTFOUND;
SELECT YZM,FZM,PZM,GZM,JZM,WZM
INTO NYGM,NFM,NPM,NGM,NJM,NWGM
FROM C WHERE C_DATE=USE_DATE AND CODE=NEICODE AND ROWNUM=1;
SELECT YZM,FZM,PZM,GZM,JZM,WZM
INTO WYGM,WFM,WPM,WGM,WJM,WWGM
FROM C WHERE C_DATE=USE_DATE AND CODE=WAICODE AND ROWNUM=1;
IF ABS(NYGM-WYGM)>2 OR ABS(NFM-WFM)>2 OR ABS(NPM-WPM)>2 OR ABS(NGM-WGM)>2 OR ABS(NJM-WJM)>2 OR ABS(NWGM-WWGM)>2 THEN
INSERT INTO DIFFER(DIFFER_ID,GDLY,COM_DATE,
NBCODE,NBYGM,NBFM,NBPM,NBGM,NBJM,NBWGM,
WBCODE,WBYGM,WBFM,WBPM,WBGM,WBJM,WBWGM)
VALUES(
substr(to_char(dbms_random.value*10),3,32),GOUDLY,comdate,
NEICODE,NYGM,NFM,NPM,NGM,NJM,NWGM,
WAICODE,WYGM,WFM,WPM,WGM,WJM,WWGM
);
COMMIT;
END IF;
END LOOP;
CLOSE COMPARE_CURSOR;
end COMPARE;
在 SELECT YZM,FZM,PZM,GZM,JZM,WZM
INTO NYGM,NFM,NPM,NGM,NJM,NWGM
FROM C WHERE C_DATE=USE_DATE AND CODE=NEICODE AND ROWNUM=1
SELECT YZM,FZM,PZM,GZM,JZM,WZM
INTO NYGM,NFM,NPM,NGM,NJM,NWGM
FROM C WHERE C_DATE=USE_DATE AND CODE=WAICODE AND ROWNUM=1
会有 NO_DATA_FOUND EXCEPTION 我想去掉这个异常 用 EXCEPTION WHEN NO_DATA_FOUND THEN CONTINUE;END;不知道加在哪里,总是报 PLS-00103错误
v_num number;
begin
null;
begin
select 1 into v_num from dual where 1=0;
exception
when no_data_found then
null;
end;null;
end;
exception when no_data_found then
....
end COMPARE;
---为什么不用这个,这个不行嘛
create or replace procedure T_COMPARE(comdate in varchar2)
is
begin
for i in
(select a.NCODE,a.WCODE,b.nFYARD,b.nGYARD,b.nJYARD,b.wFYARD,b.wGYARD,b.wJYARD
from a,b where a.NCODE=b.CODE or a.WCODE=b.CODE
and(abs(b.nFYARD-b.wFYARD)>2 or abs(b.nGYARD-b.wGYARD)>2 or abs(b.nJYARD-b.wJYARD)>2))
loop
insert into c(NCODE,WCODE,NFYARD,NGYARD,NJYARD,WFYARD,WGYARD,WJYARD)
values(i.NCODE,i.WCODE,i.nFYARD,i.nGYARD,i.nJYARD,i.wFYARD,i.wGYARD,i.wJYARD);
commit;
end loop; EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line(sqlcode||' '||sqlerrm);
end;
是里循环里的select into 变量 有空值是吧---这样就可以继续 规避no_data_found 的异常 继续进行循环
CLOSE COMPARE_CURSOR;
exception when no_data_found then
null;
end COMPARE;
SELECT NBCODE,WBCODE,GDLY FROM B;
NEICODE VARCHAR2(6);
WAICODE VARCHAR2(6);
GOUDLY VARCHAR2(30);
USE_DATE DATE;
NYGM NUMBER;
NWGM NUMBER;
NFM NUMBER;
NPM NUMBER;
NGM NUMBER;
NJM NUMBER;
WYGM NUMBER;
WWGM NUMBER;
WFM NUMBER;
WPM NUMBER;
WGM NUMBER;
WJM NUMBER;
begin
SELECT TO_DATE(comdate,'yyyyMM') INTO USE_DATE FROM DUAL; DELETE FROM DIFFER WHERE COM_DATE=comdate;
COMMIT;
OPEN COMPARE_CURSOR;
LOOP
begin --new added
FETCH COMPARE_CURSOR INTO NEICODE,WAICODE,GOUDLY;
EXIT WHEN COMPARE_CURSOR %NOTFOUND;
SELECT YZM,FZM,PZM,GZM,JZM,WZM
INTO NYGM,NFM,NPM,NGM,NJM,NWGM
FROM C WHERE C_DATE=USE_DATE AND CODE=NEICODE AND ROWNUM=1;
SELECT YZM,FZM,PZM,GZM,JZM,WZM
INTO WYGM,WFM,WPM,WGM,WJM,WWGM
FROM C WHERE C_DATE=USE_DATE AND CODE=WAICODE AND ROWNUM=1;
IF ABS(NYGM-WYGM)>2 OR ABS(NFM-WFM)>2 OR ABS(NPM-WPM)>2 OR ABS(NGM-WGM)>2 OR ABS(NJM-WJM)>2 OR ABS(NWGM-WWGM)>2 THEN
INSERT INTO DIFFER(DIFFER_ID,GDLY,COM_DATE,
NBCODE,NBYGM,NBFM,NBPM,NBGM,NBJM,NBWGM,
WBCODE,WBYGM,WBFM,WBPM,WBGM,WBJM,WBWGM)
VALUES(
substr(to_char(dbms_random.value*10),3,32),GOUDLY,comdate,
NEICODE,NYGM,NFM,NPM,NGM,NJM,NWGM,
WAICODE,WYGM,WFM,WPM,WGM,WJM,WWGM
);
COMMIT;
END IF;
exception
when no_data_found then
null;
end;
END LOOP;
CLOSE COMPARE_CURSOR;
end COMPARE;
TABLE A: NCODE、WCODETABLE B:CODE、FYARD、GYARD、JYARDTABLE C:NCODE、WCODE、NFYARD、NGYARD、NJYARD、WFYARD、WGYARD、WJYARD
A中的NCODE 和 WCODE对应B中的CODE 要比较A中的NCODE 和 WCODE 对应的 FYARD、GYARD、JYARD,若NCODE 和 WCODE 对应的 FYARD、GYARD、JYARD有一个差大于2就将 NCODE 和 WCODE 及对应的 FYARD、GYARD、JYARD 插入到C中 比如:A:001、002
B:001、20、30、40
002、20、30、43 这时就要将B中的两条数据插入到C中。用游标做可以吗?怎么来做--这个
create or replace procedure T_COMPARE(comdate in varchar2)
is
begin
for i in
(select a.NCODE,a.WCODE,b.FYARD nFYARD,b.GYARD nGYARD,b.JYARD nJYARD,b.FYARD wFYARD,b.GYARD wGYARD,b.JYARD wJYARD
from a,b where a.NCODE=b.CODE or a.WCODE=b.CODE
and(abs(b.FYARD-b.FYARD)>2 or abs(b.GYARD-b.GYARD)>2 or abs(b.JYARD-b.JYARD)>2))
loop
insert into c(NCODE,WCODE,NFYARD,NGYARD,NJYARD,WFYARD,WGYARD,WJYARD)
values(i.NCODE,i.WCODE,i.nFYARD,i.nGYARD,i.nJYARD,i.wFYARD,i.wGYARD,i.wJYARD);
commit;
end loop; EXCEPTIONS
WHEN NO_DATA_FOUND THEN
null;
end;
from a,b where a.NCODE=b.CODE or a.WCODE=b.CODE
and(abs(b.FYARD-b.FYARD)>2 or abs(b.GYARD-b.GYARD)>2 or abs(b.JYARD-b.JYARD)>2)) 这个语句查询出来的A.NCODE A.WCODE是对的 但是 NFYARD WFYARD、NGYARD WGYARD、NJYARD WJYARD是没有区别的 是一个值