Oracle PL/SQL嵌套表处理比较

Question

我正在使用Oracle 11GR2 PL/SQL表（嵌套表），但是只是对以下两种方法是否达到相同的结果感到有些困惑，即：

假设我已经定义了以下类型：

type member_nos_type is table of varchar2(32000);

和以下示例表定义/数据：

table: my_members
id        number not null,
membernos varchar2(30) not null,
first_nm  varchar2(255),
surname   varchar2(255)
Sample data in table my_members:
1  111111  Joe   Smith
2  222222  Sam   Smith
3  333333  Jane  Smith
4  444444  Jason Smith
5  555555  Joel  Smith

然后在一个过程中，我有以下代码：

    procedure a1 (p_param1 in varchar2) is
      v_member_list        member_nos_type;
    begin
      if p_param1 = 'BASKETBALL' then
        v_member_list := member_nos_type ('222222','444444');
      end if;
    end;    
----------------------------    
    procedure b2 (p_param1 in varchar2) is
      v_member_list        member_nos_type;
    begin
      if p_param1 = 'BASKETBALL' then
        v_member_list := member_nos_type (
          SELECT LISTAGG(membernos, ',') WITHIN GROUP (ORDER BY membernos)
          FROM my_members
          WHERE id in (2,4));
      end if;
    end;

基于上面的procedure a1和procedure b2，最终结果是varchar2类型的成员表吗？

更新

如何根据我的my_members表中的MemberNos列数据返回真正的嵌套表？

Answer 1

我怀疑您是否可以填充嵌套表。您可以如下;

procedure b2 (p_param1 in varchar2) is
      v_member_list        member_nos_type;
    begin
      if p_param1 = 'BASKETBALL' then
      for rec in (Select membernos
                  from my_members
                  WHERE id in (2,4))
      loop
       v_member_list.extend();     
       v_member_list(rec):= rec.membernos;    
      End loop;   
      end if;
      For i in 1..v_member_list.count
      loop
       dbms_output.put_line(v_member_list(i));
      end loop;
    end;

ps：未测试。

Answer 2

您应该能够

select cast(collect(membernos) as member_nos_type)
into v_member_list 
FROM my_members
WHERE id in (2,4);

，除非它实际上是作为收集类型存储在数据库中（我不建议），否则我不会真正称其为嵌套表。