ORACLE程序错误 [英] ORACLE PROCEDURE ERROR

问题描述

您好，我编写了以下程序:

SET SERVEROUTPUT ON

创建或替换过程Proc_Mail_escalation为
m_SAP_ID tbl_salesuser_mast.SAP_ID％TYPE;


光标csrSAPID为
从tbl_salesuser_mast中选择tbl_salesuser_mast.SAP_ID，其中tbl_salesuser_mast.levelid ="L6"且tbl_salesuser_mast.SAP_ID不在(从tbl_mail_escalation中选择tbl_mail_escalation.sm_id)；

Hi, I have written a procedure as below:

SET SERVEROUTPUT ON

Create or replace procedure Proc_Mail_escalation as
m_SAP_ID tbl_salesuser_mast.SAP_ID%TYPE;


cursor csrSAPID is
select tbl_salesuser_mast.SAP_ID from tbl_salesuser_mast where tbl_salesuser_mast.levelid=''L6'' and tbl_salesuser_mast.SAP_ID not in (select tbl_mail_escalation.sm_id from tbl_mail_escalation);

BEGIN
  OPEN csrSAPID
       loop fetch csrSAPID in m_SAP_ID EXIT WHEN csrSAPID%NOTFOUND
            begin
             IF csrSAPID%FOUND then
                   insert into tbl_mail_escalation (SM_ID,SM_NAME,SM_EMAIL,SM_LOCATION,USER2_ID,USER2_NAME,USER2_EMAIL,USER2_LOCATION,BH_ID,BH_NAME,BH_EMAIL,BH_LOCATION)select distinct a.SAP_ID,a.NAME,a.EMAIL,a.AREA_ID,b.SAP_ID,b.NAME,b.EMAIL,b.AREA_ID,c.SAP_ID,c.NAME,c.EMAIL,c.AREA_ID from TBL_SALESUSER_MAST a,TBL_SALESUSER_MAST b,TBL_SALESUSER_MAST c,TBL_SALES_HIERARCHY h where h.LEVEL6_CODE=a.SAP_ID and h.LEVEL5_CODE=b.SAP_ID and h.LEVEL1_CODE=c.SAP_ID and a.SAP_ID=m_SAP_ID;
             END IF
         end
       end loop;
  CLOSE csrSAPID
   commit;
END

但这给了我以下错误.请让我知道此
的任何解决方案
线路/颜色错误
-------- ------------------------------------------ -----------------------
5/3 PLS-00103:期望其中一个
时遇到符号"CURSOR" 以下:
:=(;不为null范围内的默认字符

but it is giving me the error as follows .Please let me know any solution for this

LINE/COL ERROR
-------- -----------------------------------------------------------------
5/3 PLS-00103: Encountered the symbol "CURSOR" when expecting one of
the following:
:= ( ; not null range default character

推荐答案

当我查看您的代码时，如下所示:

Hi,

As I review your code as follows:

CLOSE csrSAPID
    commit;

为什么关闭您的 csRSAPID 光标然后又提交

我认为您应该 commit 在关闭游标之前...


希望对您有所帮助.

问候，

代数

Why you close your csRSAPID cursor then commit

I thing you should commit before closing the cursor...


Hope this could help.

Regards,

Algem

这篇关于ORACLE程序错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！