Oracle中执行存储过程时出现错误。

huangapple go评论54阅读模式
英文:

Error while executing stored procedure in Oracle

问题

我有一个存储过程,在这里我遇到了错误

> 错误(5668,11):PLS-00103:遇到符号“IF”,期望以下之一: ; <标识符> <双引号限定的标识符>

以下是存储过程。

<!-- 开始片段:js 隐藏:false 控制台:true babel:false -->

<!-- 语言:lang-html -->

PROCEDURE INS_WORKFLOW_FIP_FTTX
(
PFSA_ID IN TBL_FIBER_INV_JOBS.FSA_ID%TYPE,
PUG_LENGTH IN TBL_FIBER_INV_JOBS.FSA_UG%TYPE,
PAR_LENGTH IN TBL_FIBER_INV_JOBS.FSA_AERIAL%TYPE,
PCREATED_BY IN TBL_FIBER_INV_JOBS.CREATED_BY%TYPE,
PMAINTENANCEZONECODE IN TBL_FIBER_INV_JOBS.MAINTENANCEZONECODE%TYPE,
PMAINTENANCEZONENAME IN TBL_FIBER_INV_JOBS.MAINTENANCEZONENAME%TYPE,
PNE_LENGTH IN TBL_FIBER_INV_JOBS.MAINT_ZONE_NE_SPAN_LENGTH%TYPE,
PSTATUS_ID IN TBL_FIBER_INV_JOB_PROGRESS.STATUS_ID%TYPE,
PSPAN_TYPE IN TBL_FIBER_INV_JOBS.SPAN_TYPE%TYPE,
PUMS_GROUP_ASS_BY_ID IN TBL_FIBER_INV_JOB_PROGRESS.UMS_GROUP_ASS_BY_ID%TYPE,
PUMS_GROUP_ASS_BY_NAME IN TBL_FIBER_INV_JOB_PROGRESS.UMS_GROUP_ASS_BY_NAME%TYPE,
PUMS_GROUP_ASS_TO_ID IN TBL_FIBER_INV_JOB_PROGRESS.UMS_GROUP_ASS_TO_ID%TYPE,
PUMS_GROUP_ASS_TO_NAME IN TBL_FIBER_INV_JOB_PROGRESS.UMS_GROUP_ASS_TO_NAME%TYPE,
PHOTO_OFFERED_LENGTH IN TBL_FIBER_INV_JOB_PROGRESS.HOTO_OFFERED_LENGTH%TYPE,
PHOTO_ACCEPTANCE_DATE IN TBL_FIBER_INV_JOB_PROGRESS.HOTO_ACCEPTENCE_DATE%TYPE,
PSPVENDORXML IN XMLTYPE,
POUTMSG OUT NVARCHAR2
)

AS
PJOB_PROGRESS_ID NUMBER:=0;
PJOB_ID NUMBER :=0;
PCNT_JOBID NUMBER := -1;
BEGIN

SELECT COUNT(JOB_ID) INTO PCNT_JOBID
FROM TBL_FIBER_INV_JOBS
WHERE FSA_ID = PFSA_ID
AND MAINTENANCEZONECODE = PMAINTENANCEZONECODE;
-- END;
-- END IF;

IF PCNT_JOBID = 0 THEN
BEGIN

INSERT
INTO TBL_FIBER_INV_JOBS
(
FSA_ID,
FSA_UG,
FSA_AERIAL,
CREATED_BY,
MAINTENANCEZONECODE,
MAINTENANCEZONENAME,
SPAN_TYPE,
MAINT_ZONE_NE_SPAN_LENGTH
)
VALUES
(
PFSA_ID,
PUG_LENGTH,
PAR_LENGTH,
PCREATED_BY,
PMAINTENANCEZONECODE,
PMAINTENANCEZONENAME,
PSPAN_TYPE,
PNE_LENGTH
)RETURNING JOB_ID INTO PJOB_ID;

IF PJOB_ID > 0 THEN
BEGIN

INSERT
INTO TBL_FIBER_INV_JOB_PROGRESS
(
JOB_ID,
FSA_UG,
FSA_AERIAL,
CREATED_BY,
CREATED_DATE,
STATUS_ID,
UMS_GROUP_ASS_BY_ID,
UMS_GROUP_ASS_BY_NAME,
UMS_GROUP_ASS_TO_ID,
UMS_GROUP_ASS_TO_NAME,
UMS_GROUP_ASS_TO_DATE,
HOTO_OFFERED_LENGTH,
HOTO_ACCEPTENCE_DATE,
NE_SPAN_LENGTH,
MODIFIED_BY,
MODIFIED_DATE
)
VALUES
(
PJOB_ID,
PUG_LENGTH,
PAR_LENGTH,
PCREATED_BY,
SYSDATE,
PSTATUS_ID,
PUMS_GROUP_ASS_BY_ID,
PUMS_GROUP_ASS_BY_NAME,
PUMS_GROUP_ASS_TO_ID,
PUMS_GROUP_ASS_TO_NAME,
SYSDATE,
PHOTO_OFFERED_LENGTH,
PHOTO_ACCEPTANCE_DATE,
PNE_LENGTH,
PCREATED_BY,
SYSDATE
)RETURNING JOB_PROGRESS_ID INTO PJOB_PROGRESS_ID;

DELETE FROM TBL_FIBER_INV_VENDORINFO
WHERE JOB_ID = PJOB_ID;

FOR SPVENDORINFO IN
(
SELECT ASPVENDORDETAILS.EXTRACT('ROW/VendorID/text()').GETSTRINGVAL() AS ASP_VENDOR_ID,
ASPVENDORDETAILS.EXTRACT('ROW/VendorName/text()').GETSTRINGVAL() AS ASP_VENDOR_NAME,
ASPVENDORDETAILS.EXTRACT('ROW/VendorCode/text()').GETSTRINGVAL() AS ASP_VENDOR_CODE,
ASPVENDORDETAILS.EXTRACT('ROW/FromDate/text()').GETSTRINGVAL() AS ASP_VENDOR_START_DATE,
ASPVENDORDETAILS.EXTRACT('ROW/ToDate/text()').GETSTRINGVAL() AS ASP_VENDOR_END_DATE
FROM TABLE(XMLSEQUENCE(PSPVENDORXML.EXTRACT('SPVENDORDETAILS/ROW'))) ASPVENDORDETAILS
)
LOOP
INSERT INTO TBL_FIBER_INV_VENDORINFO
(
SP_VENDOR_CODE,
SP_VENDOR_START_DATE,
SP_VENDOR_END_DATE,
JOB_ID
)
VALUES
(
SPVENDORINFO.ASP_VENDOR_CODE,
TO_DATE(SPVENDORINFO.ASP_VENDOR_START_DATE,'DD/MM/YYYY'),
TO_DATE(SPVENDORINFO.ASP_VENDOR_END_DATE,'DD/MM/YYYY'),
PJOB_ID
);
END LOOP;

        POUTMSG :=&#39;SUCCESS|Record inserted successfully&#39;

COMMIT;

      END;
      END IF;

  END IF;
  ELSIF // 这里我遇到错误

  SELECT COUNT(JOB_ID) INTO PCNT_JOBID
        FROM TBL_FIBER_INV_JOBS
        WHERE FSA_ID = PFSA_ID
        AND MAINTENANCEZONECODE = PMAINTENANCEZONECODE;

 IF PCNT_JOBID &gt; 0 THEN
BEGIN

UPDATE TBL_FIBER_INV_JOB_PROGRESS
SET
            JOB_ID = PJOB_ID,
            FSA_UG = PUG_LENGTH,
            FSA_AERIAL = PAR_LENGTH,
            CREATED_BY = PCREATED_BY,
            CREATED_DATE = SYSDATE,
            STATUS_ID = PSTATUS_ID,
            UMS_GROUP_ASS_BY_ID = PUMS_GROUP_ASS_BY_ID,
            UMS_GROUP_ASS_BY_NAME =  PUMS_GROUP_ASS_BY_NAME,
            UMS_GROUP_ASS_TO_ID = PUMS
英文:

I have a stored procedure where I am getting error as

>Error(5668,11): PLS-00103: Encountered the symbol "IF" when expecting one of the following: ; <an identifier> <a double-quoted delimited-identifier>

Below is the SP.

<!-- begin snippet: js hide: false console: true babel: false -->

<!-- language: lang-html -->

PROCEDURE INS_WORKFLOW_FIP_FTTX
(
PFSA_ID IN TBL_FIBER_INV_JOBS.FSA_ID%TYPE,   
PUG_LENGTH IN TBL_FIBER_INV_JOBS.FSA_UG%TYPE,
PAR_LENGTH IN TBL_FIBER_INV_JOBS.FSA_AERIAL%TYPE,
PCREATED_BY IN TBL_FIBER_INV_JOBS.CREATED_BY%TYPE, 
PMAINTENANCEZONECODE IN TBL_FIBER_INV_JOBS.MAINTENANCEZONECODE%TYPE, 
PMAINTENANCEZONENAME IN TBL_FIBER_INV_JOBS.MAINTENANCEZONENAME%TYPE, 
PNE_LENGTH IN TBL_FIBER_INV_JOBS.MAINT_ZONE_NE_SPAN_LENGTH%TYPE,
PSTATUS_ID IN TBL_FIBER_INV_JOB_PROGRESS.STATUS_ID%TYPE,
PSPAN_TYPE IN TBL_FIBER_INV_JOBS.SPAN_TYPE%TYPE,  
PUMS_GROUP_ASS_BY_ID IN TBL_FIBER_INV_JOB_PROGRESS.UMS_GROUP_ASS_BY_ID%TYPE,
PUMS_GROUP_ASS_BY_NAME IN TBL_FIBER_INV_JOB_PROGRESS.UMS_GROUP_ASS_BY_NAME%TYPE,
PUMS_GROUP_ASS_TO_ID IN TBL_FIBER_INV_JOB_PROGRESS.UMS_GROUP_ASS_TO_ID%TYPE,
PUMS_GROUP_ASS_TO_NAME IN TBL_FIBER_INV_JOB_PROGRESS.UMS_GROUP_ASS_TO_NAME%TYPE,
PHOTO_OFFERED_LENGTH IN TBL_FIBER_INV_JOB_PROGRESS.HOTO_OFFERED_LENGTH%TYPE,  
PHOTO_ACCEPTANCE_DATE IN TBL_FIBER_INV_JOB_PROGRESS.HOTO_ACCEPTENCE_DATE%TYPE,
PSPVENDORXML IN XMLTYPE,
POUTMSG OUT NVARCHAR2
)   
AS
PJOB_PROGRESS_ID NUMBER:=0;
PJOB_ID NUMBER :=0;
PCNT_JOBID NUMBER := -1;
BEGIN   
SELECT COUNT(JOB_ID) INTO PCNT_JOBID 
FROM TBL_FIBER_INV_JOBS
WHERE FSA_ID = PFSA_ID
AND MAINTENANCEZONECODE = PMAINTENANCEZONECODE;           
--   END;
--  END IF; 
IF PCNT_JOBID = 0 THEN
BEGIN        
INSERT
INTO TBL_FIBER_INV_JOBS
(
FSA_ID,
FSA_UG,
FSA_AERIAL,
CREATED_BY,
MAINTENANCEZONECODE,
MAINTENANCEZONENAME,
SPAN_TYPE,
MAINT_ZONE_NE_SPAN_LENGTH
)
VALUES
(
PFSA_ID,
PUG_LENGTH,
PAR_LENGTH,
PCREATED_BY,
PMAINTENANCEZONECODE,
PMAINTENANCEZONENAME,
PSPAN_TYPE,
PNE_LENGTH
)RETURNING JOB_ID INTO PJOB_ID;
IF PJOB_ID &gt; 0 THEN
BEGIN
INSERT
INTO TBL_FIBER_INV_JOB_PROGRESS
(
JOB_ID,
FSA_UG,
FSA_AERIAL,
CREATED_BY,
CREATED_DATE,
STATUS_ID,
UMS_GROUP_ASS_BY_ID,
UMS_GROUP_ASS_BY_NAME,
UMS_GROUP_ASS_TO_ID,
UMS_GROUP_ASS_TO_NAME,
UMS_GROUP_ASS_TO_DATE,
HOTO_OFFERED_LENGTH,
HOTO_ACCEPTENCE_DATE,
NE_SPAN_LENGTH,
MODIFIED_BY,
MODIFIED_DATE
)
VALUES
(
PJOB_ID,
PUG_LENGTH,
PAR_LENGTH,
PCREATED_BY,
SYSDATE,
PSTATUS_ID,
PUMS_GROUP_ASS_BY_ID,
PUMS_GROUP_ASS_BY_NAME,
PUMS_GROUP_ASS_TO_ID,
PUMS_GROUP_ASS_TO_NAME,
SYSDATE,
PHOTO_OFFERED_LENGTH,
PHOTO_ACCEPTANCE_DATE,
PNE_LENGTH,
PCREATED_BY,
SYSDATE
)RETURNING JOB_PROGRESS_ID INTO PJOB_PROGRESS_ID;           
DELETE FROM TBL_FIBER_INV_VENDORINFO 
WHERE JOB_ID = PJOB_ID;    
FOR SPVENDORINFO IN
(        
SELECT ASPVENDORDETAILS.EXTRACT(&#39;ROW/VendorID/text()&#39;).GETSTRINGVAL() AS ASP_VENDOR_ID, 
ASPVENDORDETAILS.EXTRACT(&#39;ROW/VendorName/text()&#39;).GETSTRINGVAL() AS ASP_VENDOR_NAME, 
ASPVENDORDETAILS.EXTRACT(&#39;ROW/VendorCode/text()&#39;).GETSTRINGVAL() AS ASP_VENDOR_CODE,
ASPVENDORDETAILS.EXTRACT(&#39;ROW/FromDate/text()&#39;).GETSTRINGVAL() AS ASP_VENDOR_START_DATE,
ASPVENDORDETAILS.EXTRACT(&#39;ROW/ToDate/text()&#39;).GETSTRINGVAL() AS ASP_VENDOR_END_DATE
FROM TABLE(XMLSEQUENCE(PSPVENDORXML.EXTRACT(&#39;SPVENDORDETAILS/ROW&#39;))) ASPVENDORDETAILS
)
LOOP
INSERT INTO TBL_FIBER_INV_VENDORINFO
(
SP_VENDOR_CODE,
SP_VENDOR_START_DATE,
SP_VENDOR_END_DATE,
JOB_ID
)
VALUES
(
SPVENDORINFO.ASP_VENDOR_CODE,
TO_DATE(SPVENDORINFO.ASP_VENDOR_START_DATE,&#39;DD/MM/YYYY&#39;),
TO_DATE(SPVENDORINFO.ASP_VENDOR_END_DATE,&#39;DD/MM/YYYY&#39;),
PJOB_ID                
);
END LOOP;               
POUTMSG :=&#39;SUCCESS|Record inserted successfully&#39;;
COMMIT;
END;
END IF;       
END IF;
ELSIF // here I am getting error
SELECT COUNT(JOB_ID) INTO PCNT_JOBID 
FROM TBL_FIBER_INV_JOBS
WHERE FSA_ID = PFSA_ID
AND MAINTENANCEZONECODE = PMAINTENANCEZONECODE;
IF PCNT_JOBID &gt; 0 THEN
BEGIN
UPDATE TBL_FIBER_INV_JOB_PROGRESS
SET
JOB_ID = PJOB_ID,
FSA_UG = PUG_LENGTH,
FSA_AERIAL = PAR_LENGTH,
CREATED_BY = PCREATED_BY,
CREATED_DATE = SYSDATE,
STATUS_ID = PSTATUS_ID,
UMS_GROUP_ASS_BY_ID = PUMS_GROUP_ASS_BY_ID,
UMS_GROUP_ASS_BY_NAME =  PUMS_GROUP_ASS_BY_NAME,
UMS_GROUP_ASS_TO_ID = PUMS_GROUP_ASS_TO_ID,
UMS_GROUP_ASS_TO_NAME = PUMS_GROUP_ASS_TO_NAME,
UMS_GROUP_ASS_TO_DATE = SYSDATE,
HOTO_OFFERED_LENGTH = PHOTO_OFFERED_LENGTH,
HOTO_ACCEPTENCE_DATE = PHOTO_ACCEPTANCE_DATE,
NE_SPAN_LENGTH = PNE_LENGTH,
MODIFIED_BY = PCREATED_BY,
MODIFIED_DATE = SYSDATE
) RETURNING JOB_PROGRESS_ID INTO PJOB_PROGRESS_ID;
DELETE FROM TBL_FIBER_INV_VENDORINFO 
WHERE JOB_ID = PJOB_ID;    
FOR SPVENDORINFO IN
(        
SELECT ASPVENDORDETAILS.EXTRACT(&#39;ROW/VendorID/text()&#39;).GETSTRINGVAL() AS ASP_VENDOR_ID, 
ASPVENDORDETAILS.EXTRACT(&#39;ROW/VendorName/text()&#39;).GETSTRINGVAL() AS ASP_VENDOR_NAME, 
ASPVENDORDETAILS.EXTRACT(&#39;ROW/VendorCode/text()&#39;).GETSTRINGVAL() AS ASP_VENDOR_CODE,
ASPVENDORDETAILS.EXTRACT(&#39;ROW/FromDate/text()&#39;).GETSTRINGVAL() AS ASP_VENDOR_START_DATE,
ASPVENDORDETAILS.EXTRACT(&#39;ROW/ToDate/text()&#39;).GETSTRINGVAL() AS ASP_VENDOR_END_DATE
FROM TABLE(XMLSEQUENCE(PSPVENDORXML.EXTRACT(&#39;SPVENDORDETAILS/ROW&#39;))) ASPVENDORDETAILS
)
LOOP
INSERT INTO TBL_FIBER_INV_VENDORINFO
(
SP_VENDOR_CODE,
SP_VENDOR_START_DATE,
SP_VENDOR_END_DATE,
JOB_ID
)
VALUES
(
SPVENDORINFO.ASP_VENDOR_CODE,
TO_DATE(SPVENDORINFO.ASP_VENDOR_START_DATE,&#39;DD/MM/YYYY&#39;),
TO_DATE(SPVENDORINFO.ASP_VENDOR_END_DATE,&#39;DD/MM/YYYY&#39;),
PJOB_ID                
);
END LOOP;               
POUTMSG :=&#39;SUCCESS|Record updated successfully&#39;;
COMMIT;
END;
END;
END;
BEGIN
POUTMSG := &#39;EXISTS|Record already exists&#39;;
END;
END IF;
EXCEPTION  
WHEN OTHERS THEN
ERROR_CODE := SQLCODE;
ERROR_MESSAGE := SUBSTR(SQLERRM, 1, 200);
ROLLBACK;
POUTMSG := &#39;ERROR|Error ocurred on record creation&#39;;
PKG_FIBER_HOTO_COMP_NEW.INS_ERRORLOG(PCREATED_BY, PFSA_ID, &#39;DB : INS_WORKFLOW_FIP_FTTX&#39;,ERROR_CODE||&#39; : &#39;||ERROR_MESSAGE);
END INS_WORKFLOW_FIP_FTTX;

<!-- end snippet -->

答案1

得分: 1

PROCEDURE INS_WORKFLOW_FIP_FTTX
(
...
)
AS
PJOB_PROGRESS_ID NUMBER:=0;
PJOB_ID NUMBER :=0;
PCNT_JOBID NUMBER := -1;
BEGIN
-- ...
IF PCNT_JOBID = 0
THEN
BEGIN
-- ...
IF PJOB_ID > 0
THEN
BEGIN
-- ...
FOR SPVENDORINFO IN (...)
LOOP
-- ...
END LOOP;
END;
END IF;
END;
ELSIF // 这里出错
-- ..
IF PCNT_JOBID > 0
THEN
BEGIN
-- ...
FOR SPVENDORINFO IN (...)
LOOP
-- ...
END LOOP;
-- ...
END;
END;
END;

BEGIN
-- ...
END;
END IF;
EXCEPTION
WHEN OTHERS THEN
-- ...
END INS_WORKFLOW_FIP_FTTX;

英文:

Format your code neatly and use appropriate indentation:

PROCEDURE INS_WORKFLOW_FIP_FTTX
(
...
)   
AS
PJOB_PROGRESS_ID NUMBER:=0;
PJOB_ID NUMBER :=0;
PCNT_JOBID NUMBER := -1;
BEGIN
-- ...
IF PCNT_JOBID = 0
THEN
BEGIN
-- ...
IF PJOB_ID &gt; 0
THEN
BEGIN
-- ...
FOR SPVENDORINFO IN (...)
LOOP
-- ...
END LOOP;               
END;
END IF;       
END IF;
ELSIF // here I am getting error
-- ..      
IF PCNT_JOBID &gt; 0
THEN
BEGIN
-- ...
FOR SPVENDORINFO IN (...)
LOOP
-- ...
END LOOP;
-- ...
END;
END;
END;
BEGIN
-- ...
END;
END IF;
EXCEPTION  
WHEN OTHERS THEN
-- ...
END INS_WORKFLOW_FIP_FTTX;

When you do that then you can clearly see that you have use END IF to close a BEGIN block (just before your ELSIF statement, which is where the error is not the ELSIF statement) and then later you have used END to close an IF statement and even later END IF to close the outer-most BEGIN statement.

huangapple
  • 本文由 发表于 2023年3月31日 18:50:17
  • 转载请务必保留本文链接:https://go.coder-hub.com/75897682.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定