arrays - 如何在 Oracle 11g 中正确使用 MEMBER OF 函数

Question

我有以下代码，每次执行它时，都会收到错误 PLS-00306:调用“MEMBER OF”时参数的数量或类型错误。谁能告诉我我是如何错误地使用这个功能的？如果不是，正确的使用方法是什么？

如果我正确使用它，我还能如何检查数组的内容？

DECLARE
    TYPE type_cd IS VARRAY(12) OF CHAR(2);
    v_my_list  type_cd ;
    v_cd    CHAR(2) := 'AA';
BEGIN
    v_my_list   := type_cd (v_cd);

    IF v_cd MEMBER OF v_my_list
    THEN
        DBMS_OUTPUT.PUT_LINE(v_cd || ' is a member of v_my_list');
    ELSE
        DBMS_OUTPUT.PUT_LINE(v_cd || ' is NOT a member of v_my_list');
    END IF;
END;

Answer 1

来自 Oracle 的 Support for Collection Datatypes页:

The MEMBER [OF] or NOT MEMBER [OF] condition tests whether an element is a member of a nested table, returning the result as a Boolean value.

MEMBER 比较适用于嵌套表而不是 VARRAY。

将 VARRAY(12) 更改为 TABLE，它将起作用:

DECLARE
    TYPE type_cd IS TABLE OF CHAR(2);
    v_my_list  type_cd ;
    v_cd    CHAR(2) := 'AA';
BEGIN
    v_my_list   := type_cd (v_cd);

    IF v_cd MEMBER OF v_my_list
    THEN
        DBMS_OUTPUT.PUT_LINE(v_cd || ' is a member of v_my_list');
    ELSE
        DBMS_OUTPUT.PUT_LINE(v_cd || ' is NOT a member of v_my_list');
    END IF;
END;

编辑:

您可以使用一个简单的循环来检查一个成员是否存在于 VARRAY 中:

DECLARE
    TYPE type_cd IS VARRAY(12) OF CHAR(2);
    v_my_list  type_cd ;
    v_cd    CHAR(2) := 'AA';
    v_found BOOLEAN := false;
    v_index INTEGER;
BEGIN
    v_my_list   := type_cd (v_cd);

    v_index := v_my_list.FIRST;
    WHILE NOT v_found AND v_index IS NOT NULL LOOP
      IF v_my_list(v_index) = v_cd THEN
        v_found := true;
      ELSE
        v_index := v_my_list.NEXT( v_index );
      END IF;
    END LOOP;
    IF v_found THEN
        DBMS_OUTPUT.PUT_LINE(v_cd || ' is a member of v_my_list at ' || v_index );
    ELSE
        DBMS_OUTPUT.PUT_LINE(v_cd || ' is NOT a member of v_my_list');
    END IF;
END;