我有一个存储过程，在那里我得到错误作为
错误（5668，11）：PLS-00103：预期出现以下情况之一时，遇到符号“IF”：;
下面是SP。
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;
1条答案
按热度按时间ktca8awb1#
整齐地格式化代码并使用适当的缩进：
当你这样做的时候，你可以清楚地看到你已经使用
END IF关闭了一个BEGIN块（就在你的ELSIF语句之前，这是错误不是ELSIF语句的地方），然后你使用END关闭了一个IF语句，甚至更晚的END IF关闭了最外面的BEGIN语句。