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错误

解决方案 »

  1.   

    因为没有符合条件的数据,所以才会no data found.
      

  2.   

    类似declare
    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;
      

  3.   

    CLOSE COMPARE_CURSOR;
    exception when no_data_found then
    ....
    end COMPARE;
      

  4.   

    想用CONTINUE,并且总是有语法错误  在出现EXCEPTION 需要下列这一。
      

  5.   

    谁有这个全部的源代码---《Java整合开发与项目实战》分享下啊
      

  6.   

    continue好像需要版本支持.老版本的oracle是不支持continue的,一般是用goto模拟.
      

  7.   


    ---为什么不用这个,这个不行嘛
    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;
      

  8.   

    ORACLE 里没有CONTINU
    是里循环里的select into 变量 有空值是吧---这样就可以继续 规避no_data_found 的异常 继续进行循环
    CLOSE COMPARE_CURSOR;
    exception when no_data_found then
    null;
    end COMPARE;
      

  9.   

    select a.NCODE,a.WCODE,b.nFYARD,b.nGYARD,b.nJYARD,b.wFYARD,b.wGYARD,b.wJYARD中 b.nFYARD,b.nGYARD,b.nJYARD,b.wFYARD,b.wGYARD,b.wJYARD 提示无效标识符
      

  10.   

    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
      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;
      

  11.   


    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;
      

  12.   

    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)) 这个语句查询出来的A.NCODE A.WCODE是对的 但是 NFYARD WFYARD、NGYARD WGYARD、NJYARD WJYARD是没有区别的  是一个值