主页 / coding / 问题 / 78208849
Accepted
hud
Asked: 2024-03-23 05:09:04 +0800 CST

Oracle中多个IF条件不执行过程

  • 772

我有一个存储过程，我想在其中检查多个IF条件。所以我写了下面的查询。但它没有编译并给出错误

错误（97,5）：PLS-00103：在期望以下其中一项时遇到符号“IF”：；符号“IF”被忽略。

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;

请让我知道我错在哪里。

oracle

1 个回答

  1. Best Answer

    使用AND（而不是多个IF）：

    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;

    如果您确实想使用多个IFs，请确保END IF;每个 s 都有一个匹配，每个 sIF也有一个匹配。END;BEGIN

    • 2

相关问题