一个父sys_refcursor记录包含一个具有多条记录的子sys_refcursor类型

Question

数据库: Oracle 11gR2

你好,

我有两个表，它们之间有父子关系(一对多)。现在，我需要从一个存储过程返回一个引用游标，它将从父表中返回与输入参数对应的一行，并从子表中返回相应的记录(可以是一个数组)。注意，我们不想重复来自父对象的结果集。因此，基本上我们只想返回一条记录，其中详细信息列是一个记录数组，或者说父sys递归器中的一个子sys refcursor。问题是我该怎么做呢？

下面是示例表、数据和通常的过程(返回多行--这不是预期的)：

create table parent 
(
    class_id        varchar2(2),
    class_name      varchar2(10),
    subject_name    varchar2(10),
    constraint pk_id primary key (class_id)
);

create table child
(
    class_id      varchar2(10),
    student_name    varchar2(10),
    result          varchar2(10),
    constraint fk_id Foreign key (class_id) references parent(class_id)
);

insert into parent values('1', 'class_1', 'subject_1');
insert into parent values('2', 'class_2', 'subject_2');
insert into parent values('3', 'class_3', 'subject_3');
insert into parent values('4', 'class_4', 'subject_4');

insert into child values ('1', 'student_1', 'pass');
insert into child values ('1', 'student_2', 'fail');
insert into child values ('1', 'student_3', 'pass');
insert into child values ('2', 'student_1', 'pass');
insert into child values ('2', 'student_4', 'fail');

create or replace PROCEDURE get_data (
    class_id_in        IN      parent.class_id%TYPE,
    student_list_cur   OUT     SYS_REFCURSOR)
AS
BEGIN
    OPEN student_list_cur FOR
    SELECT p.class_name,
           p.subject_name,
           c.student_name,
           c.result
    FROM parent p, child c
    WHERE p.class_id = c.class_id
    AND p.class_id = class_id_in;

END get_data;

再次注意，sys-refcursor返回：

class_name     subject_name     student_name     result
-------------------------------------------------------
class_1         subject_1       student_1        pass
class_1         subject_1       student_2        fail
class_1         subject_1       student_3        pass

相反，我想要的是这样的一条记录：

parent.class_name,
parent.subject_name,
child.array_of_records SYS_REFCURSOR

其中child.array_of_records包含child.student_name的多个记录，child.result对应于相同的class_id。

谢谢。

Answer 1

您正在寻找CURSOR SUBQUERY

OPEN student_list_cur FOR
SELECT p.class_name,
       p.subject_name,
       CURSOR(SELECT c.student_name,
                     c.result
              FROM
                 child c
              WHERE p.class_id = c.class_id)
FROM parent p
WHERE p.class_id = class_id_in;