Oracle 存储过程错误 SQL

标签: sql Oracle
发布时间: 2017/4/15 18:58:09
注意事项: 本文中文内容可能为机器翻译,如要查看英文原文请点击上面连接.

我有下面的存储过程的编译就好︰

CREATE OR REPLACE PROCEDURE SSACHDEV.SyncTeleappWithClientinfo
as
mine varchar2(1);
v_teleCaseNbr      number;
v_cashwithappyn varchar2(1);

CURSOR TeleAppCursor
is
    Select 
        distinct casenbr, cashwithappyn
    from TeleApp;

BEGIN
dbms_output.put_line('begin');
open TeleAppCursor;

LOOP
    fetch TeleAppCursor into v_teleCaseNbr, v_cashwithappyn;
    EXIT when TeleAppCursor%NOTFOUND; -- this one has the problem

    Select cashwithappyn into mine from ClientInfo where casenbr = v_teleCaseNbr and trim(cashwithappyn) is null;

END LOOP;
dbms_output.put_line('end');
END;

但当我尝试使用以下运行它︰

BEGIN 
  SSACHDEV.SYNCTELEAPPWITHCLIENTINFO;
END;

我得到以下 errrors:

ORA-01403: no data found
ORA-06512: at "SSACHDEV.SYNCTELEAPPWITHCLIENTINFO", line 21
ORA-06512: at line 2

谁知道为什么?或者我可以做什么来避免这些问题呢?

解决方法 1:

它的发生因为第二个 select 声明 ( Select cashwithappyn into mine from .. ) 过程中不返回任何数据。添加 EXCEPTION 部分在你的程序来处理该异常。

CREATE OR REPLACE PROCEDURE SSACHDEV.SyncTeleappWithClientinfo
as
mine varchar2(1);
v_teleCaseNbr      number;
v_cashwithappyn varchar2(1);

CURSOR TeleAppCursor
is
    Select 
        distinct casenbr, cashwithappyn
    from TeleApp;

BEGIN
dbms_output.put_line('begin');
open TeleAppCursor;

LOOP
    fetch TeleAppCursor into v_teleCaseNbr, v_cashwithappyn;
    EXIT when TeleAppCursor%NOTFOUND;

    Select cashwithappyn into mine from ClientInfo where casenbr = v_teleCaseNbr and trim(cashwithappyn) is null;

END LOOP;
dbms_output.put_line('end');

EXCEPTION
  WHEN NO_DATA_FOUND 
  THEN  dbms_output.put_line(' no data found'); -- for example
END;
赞助商