ORACLE 游标

Question

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中。用游标做可以吗？怎么来做

Answer 1

CURSOR COMPARE_CURSOR IS
       SELECT NCODE,WCODE FROM A;
       NEICODE VARCHAR2(6);
       WAICODE VARCHAR2(6);
begin
  
  OPEN COMPARE_CURSOR;
  LOOP
  FETCH COMPARE_CURSOR INTO NEICODE,WAICODE;
  EXIT WHEN COMPARE_CURSOR %NOTFOUND;
  
  ……
  
  END LOOP;
  CLOSE COMPARE_CURSOR;关键部分不知道怎么写啊 。。请大家帮忙

Answer 2

若NCODE 和 WCODE 对应的 FYARD、GYARD、JYARD有一个差大于2  ???那个差

Answer 3

SELECT FYARD,GYARD,JYARD INTO FM,GM,JM  FOMR B WHERE CODE=USECODE;当B中没有CODE=USECODE 时，就会有ORA-01403: no data found  怎么抛出这个异常呢 ？exception when no_data_found then  这个要放在什么地方呢

Answer 4

declare
....
begin
...
SELECT FYARD,GYARD,JYARD INTO FM,GM,JM FOMR B WHERE CODE=USECODE;
....
exception 
when no_date_found then
...
end;

Answer 5

--就用隐身游标
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(b.nFYARD-b.wFYARD>2 or b.nGYARD-b.wGYARD>2 or 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;
end;

Answer 6

SELECT FYARD,GYARD,JYARD INTO NFM,NGM,NJM FOMR B WHERE CODE=USENCODE;
SELECT FYARD,GYARD,JYARD INTO WFM,WGM,WJM FOMR B WHERE CODE=USEWCODE;EXCEPTION WHEN NO_DATE_FOUND THEN COMTINUE
END;IF ABS(NFM-WFM)>2  OR ABS(NGM-WGM)>2  OR ABS(NJM-WJM)>2 THEN 
INSERT ……;
END IF;
END LOP;
CLOSE COMPARE_CURSOR;这里会报ORA00013错误   语法上哪里出错了呢

Answer 7

---就用这个  
--你的全部贴出来看看
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;
end;

Answer 8

create or replace procedure T_COMPARE(comdate in char) isCURSOR COMPARE_CURSOR IS
       SELECT NBCODE,WBCODE FROM A;
       NEICODE VARCHAR2(6);
       WAICODE VARCHAR2(6);
       
       NFYARD NUMBER;
       NGYARD NUMBER;
       NJYARD  NUMBER;
       WFYARD NUMBER;
       WGYARD NUMBER;
       WJYARD  NUMBER;
begin
  
  OPEN COMPARE_CURSOR;
  LOOP
  FETCH COMPARE_CURSOR INTO NEICODE,WAICODE;
  EXIT WHEN COMPARE_CURSOR %NOTFOUND;
  
  SELECT FYARD,GYARD,JYARD 
  INTO   NFYARD,NGYARD,NJYARD 
  FROM B WHERE CODE=NEICODE;
  
  SELECT FYARD,GYARD,JYARD 
  INTO   WFYARD,WGYARD,WJYARD 
  FROM B WHERE CODE=WAICODE;
  
  EXCEPTIONS  
  WHEN NO_DATA_FOUND THEN 
  CONTINUE
  END;
  
  IF ABS(NFYARD-WFYARD)>2 OR ABS(NGYARD-WGYARD)>2 OR ABS(NJYARD-WJYARD)>2 THEN
     INSERT INTO C(NCODE,WCODE,NFYARD,NGYARD,NJYARD,WFYARD,WGYARD,WJYARD)
      VALUES(NEICODE,WAICODE,NFYARD,NGYARD,NJYARD,WFYARD,WGYARD,WJYARD);
  END IF;
END LOOP;
  CLOSE COMPARE_CURSOR;
  commit;
end ELC_COMPARE;

Answer 9

end ELC_COMPARE改成 T_COMPARE

Answer 10

---comdate  这个参数根本就没看到下面引用--你的修改 你的异常位置问题 以及其他地方修改了下 
create or replace procedure T_COMPARE(comdate in varchar2) 
is
CURSOR COMPARE_CURSOR IS SELECT NBCODE,WBCODE FROM A;
  NEICODE VARCHAR2(6);
  WAICODE VARCHAR2(6);
    
  NFYARD NUMBER;
  NGYARD NUMBER;
  NJYARD NUMBER;
  WFYARD NUMBER;
  WGYARD NUMBER;
  WJYARD NUMBER;
begin
   
  OPEN COMPARE_CURSOR;
  LOOP
  FETCH COMPARE_CURSOR INTO NEICODE,WAICODE;
  EXIT WHEN COMPARE_CURSOR %NOTFOUND;
   
  SELECT FYARD,GYARD,JYARD  
  INTO NFYARD,NGYARD,NJYARD  
  FROM B WHERE CODE=NEICODE and rownum=1;
   
  SELECT FYARD,GYARD,JYARD  
  INTO WFYARD,WGYARD,WJYARD  
  FROM B WHERE CODE=WAICODE and rownum=1;
  IF ABS(NFYARD-WFYARD)>2 OR ABS(NGYARD-WGYARD)>2 OR ABS(NJYARD-WJYARD)>2 THEN
  INSERT INTO C(NCODE,WCODE,NFYARD,NGYARD,NJYARD,WFYARD,WGYARD,WJYARD)
  VALUES(NEICODE,WAICODE,NFYARD,NGYARD,NJYARD,WFYARD,WGYARD,WJYARD);
  commit;
  END IF;
END LOOP;
  CLOSE COMPARE_CURSOR;  EXCEPTIONS   
  WHEN NO_DATA_FOUND THEN  
dbms_output.put_line(sqlcode||'  '||sqlerrm);
end;

Answer 11

--我的
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;  EXCEPTIONS   
  WHEN NO_DATA_FOUND THEN  
dbms_output.put_line(sqlcode||'  '||sqlerrm);
end;

Answer 12

还是会有这个错误  Compilation errors for PROCEDURE ELC_COMPAREError: PLS-00103: 出现符号 "WHEN"在需要下列之一时：
        := . ( @ % ;
Line: 54
Text: WHEN NO_DATA_FOUND THENError: PLS-00103: 出现符号 "COMMIT"在需要下列之一时：
        end not pragma final
          instantiable order overriding static member constructor map
Line: 57
Text: commit;
这个错误很恶心啊  

Answer 13

--没环境 没去仔细看 
EXCEPTION ---这里 没有s
  WHEN NO_DATA_FOUND THEN  

Answer 14

insert时，要插入主键 主键是char(32)的   
怎么插入啊 好比C的主键ID insert into c(id,ncode,wcode,....)