如何将查询结果存入PL/SQL嵌套记录（Record of Record）？

用户代码示例 #

declare
     type a is record(a1 number, a2 varchar2(10));
     type b is record(b1 number, b2 varchar2(10));
     type c is record(a2 a, b2 b);

      ra a;
      rc c; 
 begin  
      with pa as(   select 1,'a'  from dual)   select pa.* into ra from pa;

  dbms_output.put_line(ra.a2); --works

   with pa as( select 1, 'a' from dual)-- expression 'RC' in the INTO list is of wrong type       
    select pa.*,pa.* into rc from pa;

    with pa as( select 1, 'a' from dual) --invalid user.table.column, table.column, or column specification
    select a(pa.*),b(pa.*) into rc from pa;

    with pa as( select 1, 'a' from dual) --invalid user.table.column, table.column, or column specification    
    select c(a(pa.*),b(pa.*)) into rc from pa;
     
      end;

用户问题 #

我定义了嵌套记录类型c，该类型包含record类型的a2和b2字段。我希望将查询结果存入该嵌套记录rc中，尝试了三种实现方式但均报错。请问此需求是否可实现？若可行，正确的实现方法是什么？

解答 #

该需求完全可实现，你之前的写法错误在于没有正确匹配嵌套记录的字段结构，PL/SQL无法自动将查询列映射到嵌套记录，且自定义record类型没有默认构造函数，不能直接用类似a(pa.*)的方式构造。下面提供两种正确实现方式：

方式一：直接为嵌套字段的子项赋值 #

通过明确指定嵌套记录的层级字段，将查询结果对应赋值：

declare
     type a is record(a1 number, a2 varchar2(10));
     type b is record(b1 number, b2 varchar2(10));
     type c is record(a2 a, b2 b);

      ra a;
      rc c; 
 begin  
      with pa as(select 1,'a' from dual)
      select pa.* into ra from pa;

      dbms_output.put_line(ra.a2); --works

      -- 正确赋值嵌套记录
      with pa as(select 1, 'a' from dual)
      select pa.*, pa.* into rc.a2.a1, rc.a2.a2, rc.b2.b1, rc.b2.b2 from pa;

      dbms_output.put_line(rc.a2.a2); -- 输出 'a'
      dbms_output.put_line(rc.b2.b2); -- 输出 'a'
 end;

方式二：先赋值子记录，再传递给嵌套记录 #

先将查询结果存入子记录变量，再将子记录赋值给嵌套记录的对应字段：

declare
     type a is record(a1 number, a2 varchar2(10));
     type b is record(b1 number, b2 varchar2(10));
     type c is record(a2 a, b2 b);

      ra a;
      rb b;
      rc c; 
 begin  
      with pa as(select 1,'a' from dual)
      select pa.* into ra from pa;

      dbms_output.put_line(ra.a2); --works

      -- 先赋值子记录，再赋值给嵌套记录
      with pa as(select 1, 'a' from dual)
      select pa.* into ra, rb from pa;

      rc.a2 := ra;
      rc.b2 := rb;

      dbms_output.put_line(rc.a2.a2); -- 输出 'a'
      dbms_output.put_line(rc.b2.b2); -- 输出 'a'
 end;

错误原因说明 #

• 第一种错误写法：select pa.*,pa.* into rc from pa，PL/SQL无法自动将多列值映射到嵌套记录的层级结构，导致类型不匹配。
• 第二、三种错误写法：a(pa.*)、c(a(pa.*),b(pa.*))，自定义record类型不是对象类型，没有默认构造函数，无法通过这种方式构造实例。

内容的提问来源于stack exchange，提问作者Pierre-olivier Gendraud