英文:
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 :='SUCCESS|Record inserted successfully'
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 > 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 > 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 :='SUCCESS|Record inserted successfully';
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 > 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('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 :='SUCCESS|Record updated successfully';
COMMIT;
END;
END;
END;
BEGIN
POUTMSG := 'EXISTS|Record already exists';
END;
END IF;
EXCEPTION
WHEN OTHERS THEN
ERROR_CODE := SQLCODE;
ERROR_MESSAGE := SUBSTR(SQLERRM, 1, 200);
ROLLBACK;
POUTMSG := 'ERROR|Error ocurred on record creation';
PKG_FIBER_HOTO_COMP_NEW.INS_ERRORLOG(PCREATED_BY, PFSA_ID, 'DB : INS_WORKFLOW_FIP_FTTX',ERROR_CODE||' : '||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 > 0
THEN
BEGIN
-- ...
FOR SPVENDORINFO IN (...)
LOOP
-- ...
END LOOP;
END;
END IF;
END IF;
ELSIF // here I am getting error
-- ..
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;
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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论