如何在PL/SQL中对多列集合使用MEMBER OF操作符？

原代码错误原因 #

• MEMBER OF 仅支持判断值是否为整个集合元素的成员，不支持直接批量访问集合所有元素的属性（v_ref_by_email.email 属于非法写法）
• MEMBER OF 仅支持嵌套表、VARRAY类型，不支持带INDEX BY的关联数组类型

实现方案 #

方案1：构造单列email集合，直接使用MEMBER OF #

适合需要频繁判断邮箱是否存在的场景，抽离单列集合后可以直接用原生操作符：

declare
   -- 定义多列记录类型
   TYPE t_ref_by_email_rec IS RECORD (
           email varchar(100)
         , ref   number
   );
   -- 定义多列嵌套表类型（去掉INDEX BY改为嵌套表）
   TYPE t_ref_by_email IS TABLE OF t_ref_by_email_rec;
   -- 定义单列email嵌套表类型，用于MEMBER OF判断
   TYPE t_email_list IS TABLE OF varchar(100);

   v_ref_by_email t_ref_by_email := t_ref_by_email();
   v_email_list t_email_list := t_email_list();
begin
   -- 模拟给多列集合赋值
   v_ref_by_email.extend(2);
   v_ref_by_email(1) := t_ref_by_email_rec('email@domain.com', 1);
   v_ref_by_email(2) := t_ref_by_email_rec('test@domain.com', 2);

   -- 抽离所有email到单列集合
   for i in 1..v_ref_by_email.count loop
       v_email_list.extend;
       v_email_list(i) := v_ref_by_email(i).email;
   end loop;

   -- 直接用MEMBER OF判断
   if 'email@domain.com' member of v_email_list then
      dbms_output.put_line('邮箱存在，执行对应逻辑');
   end if;
end;
/

方案2：遍历集合直接判断 #

适合小数据量场景，不需要额外定义新集合类型，逻辑简单直接：

declare
   TYPE t_ref_by_email_rec IS RECORD (
           email varchar(100)
         , ref   number
   );
   TYPE t_ref_by_email IS TABLE OF t_ref_by_email_rec INDEX BY BINARY_INTEGER;
   v_ref_by_email t_ref_by_email;
   v_exists boolean := false;
begin
   -- 模拟给集合赋值
   v_ref_by_email(1) := t_ref_by_email_rec('email@domain.com', 1);
   v_ref_by_email(2) := t_ref_by_email_rec('test@domain.com', 2);

   -- 遍历判断匹配项
   for i in 1..v_ref_by_email.count loop
       if v_ref_by_email(i).email = 'email@domain.com' then
           v_exists := true;
           exit; -- 匹配到直接退出循环，提升性能
       end if;
   end loop;

   if v_exists then
      dbms_output.put_line('邮箱存在，执行对应逻辑');
   end if;
end;
/

方案3：定义带MAP方法的对象类型，直接对多列集合使用MEMBER OF #

适合需要判断整个记录是否为集合成员的场景，需要先在Schema级别定义对象类型：

-- 1. 定义对象类型（不可在PL/SQL块内定义）
CREATE OR REPLACE TYPE t_ref_by_email_obj AS OBJECT (
    email varchar(100),
    ref number,
    MAP MEMBER FUNCTION match_key RETURN varchar2
);
/
-- 2. 实现对象的MAP方法，指定按email字段做匹配判断
CREATE OR REPLACE TYPE BODY t_ref_by_email_obj AS
    MAP MEMBER FUNCTION match_key RETURN varchar2 IS
    BEGIN
        RETURN self.email;
    END;
END;
/
-- 3. 定义对象嵌套表类型
CREATE OR REPLACE TYPE t_ref_by_email_tab IS TABLE OF t_ref_by_email_obj;
/

-- PL/SQL块中直接使用MEMBER OF
declare
   v_ref_by_email t_ref_by_email_tab := t_ref_by_email_tab();
begin
   v_ref_by_email.extend(2);
   v_ref_by_email(1) := t_ref_by_email_obj('email@domain.com', 1);
   v_ref_by_email(2) := t_ref_by_email_obj('test@domain.com', 2);

   -- 构造同email的对象直接判断是否为集合成员
   if t_ref_by_email_obj('email@domain.com', null) member of v_ref_by_email then
      dbms_output.put_line('邮箱存在，执行对应逻辑');
   end if;
end;
/

内容的提问来源于stack exchange，提问作者user5507535