如何正确使用Oracle 11g中的MEMBER OF功能

从Oracle的Collection for Collection数据类型页面:

的MEMBER [OF]或NOT MEMBER [OF]条件测试的一个元素是否是嵌套表的成员,返回结果为布尔值.

在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;