我可以在 PL/SQL 块中使用 REGEXP_LIKE 作为带 IF 的条件吗 [英] Can i use REGEXP_LIKE as a condition with IF in a PL/SQL block

问题描述

我正在尝试创建一个函数，旨在遍历组织单位树，根据它们在树结构中的级别和出现在我们的 Intranet 页面上的天气过滤掉一些组织单位.函数的输入是起始单元的 ORG_UNIT_ID，一个标志，表明我们是否应该关心内网标志和一个逗号分隔的级别列表.例如'2,3'.我正在尝试将 REGEXP_LIKE 与循环内的 ELSEIF 结合使用以向上运行树，直到我遇到第一个符合条件的父单元.

I'm trying to create a function designed to traverse a tree of organisational units filtering out some based on their level in the tree structure and weather they appear on our intranet page. The input to the function is the ORG_UNIT_ID of the starting unit, a flag to show if we should care about the intranet flag and a comma separated list of levels. For instance '2,3'. I'm trying to use REGEXP_LIKE in conjunction with an ELSEIF inside a loop to run up the tree until I hit the first eligible parent unit.

T_STOP 是循环的控制变量.R_ORG_UNIT_OVER 用于查询上述单元的元数据.在循环第一遍期间，这将是作为函数输入传递的单元之上的单元.

T_STOP is the control variable for the loop. R_ORG_UNIT_OVER is used to query meta-data on the above unit. During the loops first pass this will be the unit above the one passed as input to the function.

游标定义:

CURSOR C_ORG_UNIT_OVER(V_ORG_UNIT_ID ORG_UNIT.ORG_UNIT_ID%TYPE) IS
  SELECT  ORUI.ORG_UNIT_ID
  ,       ORUI.ORG_LEVEL
  ,       ORUI.SHOW_ON_INTRANET
FROM    ORG_UNIT ORUI
JOIN    ORG_UNIT_PARENT OUPA ON ORUI.ORG_UNIT_ID=OUPA.ORG_UNIT_ID_PARENT
WHERE   OUPA.ORG_UNIT_ID = V_ORG_UNIT_ID;

循环中失败的代码段:

IF R_ORG_UNIT_OVER.SHOW_ON_INTRANET = 'N' THEN
  T_ORG_UNIT_ID := R_ORG_UNIT_OVER.ORG_UNIT_ID;
ELSEIF REGEXP_LIKE (P_SKIP_LEVEL, '(^|,)' || R_ORG_UNIT_OVER.ORG_LEVEL || '($|,)') THEN
  T_ORG_UNIT_ID := R_ORG_UNIT_OVER.ORG_UNIT_ID;
ELSE
  T_STOP := 'Y';
END IF;

但是，此代码总是在 REGEXP_LIKE 符号上引发 PLS-00103 错误.当 REGEXP_LIKE 用作 PL/SQL IF/ELSEIF 块中的条件而不是常规查询时，是否存在某种限制或替代方式?

However this code always throws a PLS-00103 error on the REGEXP_LIKE symbol. Is there some sort of limitation or alternate way in which REGEXP_LIKE works when used as a condition in a PL/SQL IF/ELSEIF block as opposed to in a regular query?

推荐答案

PL/SQL 使用 ELSIF，而不是 ELSEIF.通过您的编辑，您的代码确实会出现您描述的错误；有了这个，它不会:

PL/SQL uses ELSIF, not ELSEIF. With your edit your code does get the error you described; with this it doesn't:

IF R_ORG_UNIT_OVER.SHOW_ON_INTRANET = 'N' THEN
  T_ORG_UNIT_ID := R_ORG_UNIT_OVER.ORG_UNIT_ID;
ELSIF REGEXP_LIKE (P_SKIP_LEVEL, '(^|,)' || R_ORG_UNIT_OVER.ORG_LEVEL || '($|,)') THEN
  T_ORG_UNIT_ID := R_ORG_UNIT_OVER.ORG_UNIT_ID;
ELSE
  T_STOP := 'Y';
END IF;

这篇关于我可以在 PL/SQL 块中使用 REGEXP_LIKE 作为带 IF 的条件吗的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！