在Oracle嵌套表中查找特定的varchar

Question

我是 PL-SQL 新手，正在努力寻找嵌套表操作的清晰文档。请更正任何误用的术语等。

我有一个嵌套表类型，用作存储过程的参数。

CREATE OR REPLACE TYPE "STRARRAY" AS TABLE OF VARCHAR2 (255)

在我的存储过程中，该表被初始化并填充。假设我有一个 VARCHAR2 变量，我想知道该 varchar 是否存在于嵌套表中。

我尝试过

strarray.exists('somevarchar')

，但收到 ORA-6502

除了迭代之外还有更简单的方法吗？

FOR i IN strarray.FIRST..strarray.LAST
    LOOP
        IF strarray(i) = value THEN
            return 1;--found
        END IF;
    END LOOP;

Answer 1

对于单值检查，我更喜欢“成员”运算符。

zep@dev> declare
      2      enames     strarray;
      3      wordToFind varchar2(255) := 'King';
      4  begin
      5      select emp.last_name bulk collect
      6      into   enames
      7      from   employees emp;
      8      if wordToFind member of enames then
      9          dbms_output.put_line('Found King');
     10      end if;
     11  end;
     12  /

Found King

PL/SQL procedure successfully completed

zep@dev> 

Answer 2

您可以使用 MULTISET INTERSECT 运算符来确定集合中是否存在您感兴趣的字符串。例如

declare
  l_enames strarray;
  l_interesting_enames strarray := new strarray( 'KING' );
begin
  select ename
    bulk collect into l_enames
    from emp;
  if( l_interesting_enames = l_interesting_enames MULTISET INTERSECT l_enames )
  then
    dbms_output.put_line( 'Found King' );
  end if;
end;

，如果字符串“KING”是 l_enames 集合的元素，则会打印出“Found King”。

Answer 3

当嵌套表被声明为架构级别类型时（正如您所做的那样），它可以在任何 SQL 查询中作为表使用。所以你可以编写一个简单的函数，如下所示：

CREATE OR REPLACE FUNCTION exists_in( str VARCHAR2, tab stararray)
  RETURN BOOLEAN
  AS
    c  INTEGER;
  BEGIN
    SELECT COUNT(*)
      INTO c
      FROM TABLE(CAST(tab AS strarray))
      WHERE column_value = str;
    RETURN (c > 0);
  END exists_in;

Answer 4

如果您想确定集合中是否存在该元素，则应该将数组索引而不是数组值传递给 exists。嵌套表按整数索引，因此无法通过字符串引用它们。

但是，如果您希望通过字符串索引引用数组元素，您可能需要查看关联数组而不是集合。这看起来像这样：

DECLARE
  TYPE assocArray IS TABLE OF VARCHAR2(100) INDEX BY VARCHAR2(100);
  myArray assocArray;
BEGIN

  myArray('foo') := 'bar';

  IF myArray.exists('baz') THEN
    dbms_output.put_line(myArray('baz'));

  ELSIF myArray.exists('foo') THEN
    dbms_output.put_line(myArray('foo'));

  END IF;

END;

基本上，如果您的数组值不同，您可以创建相互引用的配对数组，例如，
arr('b') := 'a'; arr('a') := 'b';

这种技术可以帮助您轻松查找任何元素及其索引。