Multiple IF conditions is not executing procedure in Oracle

Question

I have a stored procedure where I want to check multiple IF condtions. SO I wrote the below query. But it's not compiling and giving error as

Error(97,5): PLS-00103: Encountered the symbol "IF" when expecting one of the following: ; The symbol "IF" was ignored.

PROCEDURE GET_SPAN_BY_MZ
(
  PUSERTYPE IN NVARCHAR2,
  POPERATIONTYPE IN NVARCHAR2,
  PSPANTYPE IN NVARCHAR2,
  PMAINTZONECODE IN VARCHAR2,  
  PSPANDATA OUT SYS_REFCURSOR
)

AS
--SQLSTM VARCHAR2 (2000);

BEGIN

IF PUSERTYPE = 'Construction_Engineer_OL' OR PUSERTYPE = 'NHQ-PMO' THEN
      BEGIN       
       IF POPERATIONTYPE = 'NEW' THEN
         BEGIN
         IF PSPANTYPE = 'INTERCITY' THEN
              BEGIN
OPEN PSPANDATA FOR
                 
SELECT  TO_CHAR(TRIM(RJ_SPAN_ID)) AS SPAN_ID,
        TO_CHAR(RJ_MAINTENANCE_ZONE_CODE) AS MAINT_ZONE_CODE 
FROM NE.MV_SPAN@NE
WHERE LENGTH(TRIM(RJ_SPAN_ID)) = 21
  AND REGEXP_LIKE(TRIM(RJ_SPAN_ID), 
      'SP(N|Q|R|S).*+_(MP|AC|BN|BS|DN|DP|ID|LA|MS|MT|MU|OG|OL|PG|RC|RG|RT|VF|VT|YH)$','i')                                  
  AND NOT REGEXP_LIKE (NVL(RJ_INTRACITY_LINK_ID,'-'),'_(9)','i') 
  AND INVENTORY_STATUS_CODE = 'IPL'
  AND RJ_MAINTENANCE_ZONE_CODE = PMAINTZONECODE

INTERSECT

select rj_span_id, rj_maintenance_zone_code 
from ne.mv_transmedia@ne
where inventory_Status_code = 'IPL'
  and LENGTH(TRIM(RJ_SPAN_ID)) = 21
  AND REGEXP_LIKE(TRIM(RJ_SPAN_ID), 
      'SP(N|Q|R|S).*+_(MP|AC|BN|BS|DN|DP|ID|LA|MS|MT|MU|OG|OL|PG|RC|RG|RT|VF|VT|YH)$','i')
  AND NOT REGEXP_LIKE (NVL(RJ_INTRACITY_LINK_ID,'-'),'_(9)','i')
  AND RJ_MAINTENANCE_ZONE_CODE = PMAINTZONECODE;   
  
END IF;

END IF;

END GET_SPAN_BY_MZ;

Please let me know where I am wrong.

Answer 1

Use AND (rather than multiple IFs):

PROCEDURE GET_SPAN_BY_MZ
(
  PUSERTYPE      IN NVARCHAR2,
  POPERATIONTYPE IN NVARCHAR2,
  PSPANTYPE      IN NVARCHAR2,
  PMAINTZONECODE IN VARCHAR2,  
  PSPANDATA      OUT SYS_REFCURSOR
)
AS
BEGIN
  IF     PUSERTYPE IN ('Construction_Engineer_OL', 'NHQ-PMO')
     AND POPERATIONTYPE = 'NEW'
     AND PSPANTYPE = 'INTERCITY'
  THEN
    OPEN PSPANDATA FOR
    SELECT  TO_CHAR(TRIM(RJ_SPAN_ID)) AS SPAN_ID,
            TO_CHAR(RJ_MAINTENANCE_ZONE_CODE) AS MAINT_ZONE_CODE 
    FROM    NE.MV_SPAN@NE
    WHERE   LENGTH(TRIM(RJ_SPAN_ID)) = 21
    AND     REGEXP_LIKE(
              TRIM(RJ_SPAN_ID), 
              'SP(N|Q|R|S).*+_(MP|AC|BN|BS|DN|DP|ID|LA|MS|MT|MU|OG|OL|PG|RC|RG|RT|VF|VT|YH)$',
              'i'
            )                                  
    AND     NOT REGEXP_LIKE (NVL(RJ_INTRACITY_LINK_ID,'-'),'_(9)','i') 
    AND     INVENTORY_STATUS_CODE = 'IPL'
    AND     RJ_MAINTENANCE_ZONE_CODE = PMAINTZONECODE
  INTERSECT
    select  rj_span_id, rj_maintenance_zone_code 
    from    ne.mv_transmedia@ne
    where   inventory_Status_code = 'IPL'
    and     LENGTH(TRIM(RJ_SPAN_ID)) = 21
    AND     REGEXP_LIKE(
              TRIM(RJ_SPAN_ID), 
              'SP(N|Q|R|S).*+_(MP|AC|BN|BS|DN|DP|ID|LA|MS|MT|MU|OG|OL|PG|RC|RG|RT|VF|VT|YH)$',
              'i'
            )
    AND     NOT REGEXP_LIKE (NVL(RJ_INTRACITY_LINK_ID,'-'),'_(9)','i')
    AND     RJ_MAINTENANCE_ZONE_CODE = PMAINTZONECODE;   
  END IF;
END GET_SPAN_BY_MZ;

If you do want to use multiple IFs then make sure there is a matching END IF; for every IF and a matching END; for each BEGIN.