oracle - 使用具有多个 %rowtype 字段的 Oracle PL/SQL 记录表

Question

如何使用批量收集填充具有多个 %rowtype 字段的记录表？

我的代码:

      drop table child_table;
      drop table parent_table;
      /
      create table parent_table(pk number primary key);
      create table child_table(pk number primary key, fk REFERENCES parent_table(pk));
      /
      insert into parent_table (pk) values (1);
      insert into parent_table (pk) values (2);
      
      insert into child_table (pk, fk) values (11, 1);
      insert into child_table (pk, fk) values (21, 1);
      insert into child_table (pk, fk) values (32, 2);
      /
      declare
        
        type rec is record
        (
          parent parent_table%rowtype,
          child child_table%rowtype
        );
        type tbl is table of rec;
        v_table tbl := tbl();
        
        -- this works
        type tbl_parent is table of parent_table%rowtype;
        v_parent_table tbl_parent := tbl_parent();

      begin
        -- this works
        select * bulk collect into v_parent_table from parent_table;
        
        -- this doesn't work
        select * bulk collect into v_table from parent_table parent 
        inner join child_table child on parent.pk = child.fk;
        
      end;

此代码不起作用，但会抛出以下错误消息:

ORA-06550: line 13, column 30:
PLS-00597: expression 'V_TABLE' in the INTO list is of wrong type
ORA-06550: line 13, column 30:
PLS-00597: expression 'V_TABLE' in the INTO list is of wrong type
ORA-06550: line 13, column 38:
PL/SQL: ORA-00904: : invalid identifier
ORA-06550: line 13, column 3:
PL/SQL: SQL Statement ignored

好吧，oracle 说我使用了错误的数据类型，我同意。但如何解决呢？

Answer 1

您收到错误是因为联接 SELECT * 的列与表的列不同。

使用CURSOR%rowtype:

SQL> DECLARE
  2     CURSOR cc IS
  3        SELECT p.pk, c.pk cpk, c.fk
  4          FROM parent_table p
  5          JOIN child_table c ON p.pk = c.fk;
  6     TYPE tbl_join IS TABLE OF cc%ROWTYPE;
  7     l_table tbl_join;
  8  BEGIN
  9     OPEN cc;
 10     FETCH cc BULK COLLECT INTO l_table;
 11     CLOSE cc;
 12  END;
 13  /

PL/SQL procedure successfully completed

或者不使用SELECT *:

SQL> DECLARE
  2     TYPE tbl_child IS TABLE OF child_table%ROWTYPE;
  3     l_table tbl_child;
  4  BEGIN
  5     SELECT c.* BULK COLLECT INTO l_table
  6       FROM parent_table p
  7       JOIN child_table c ON p.pk = c.fk;
  8  END;
  9  /

PL/SQL procedure successfully completed