Report_报表中Ref Cursor数据源的概念和用法（案例）

/*定义ref cursor package头*/
CREATE OR REPLACE PACKAGE bxj_ref_cursor_pkg IS

  TYPE employee_rec IS RECORD(
    user_id      FND_USER.USER_ID%TYPE,
    employee_num HR_EMPLOYEES.EMPLOYEE_NUM%TYPE,
    user_name    FND_USER.USER_NAME%TYPE,
    party_type   HZ_PARTIES.PARTY_TYPE%TYPE,
    prefix       HR_EMPLOYEES.PREFIX%TYPE,
    full_name    HR_EMPLOYEES.FULL_NAME%TYPE);

  TYPE employee_refcursor IS REF CURSOR RETURN employee_rec;

  FUNCTION query_employee(user_id NUMBER) RETURN bxj_ref_cursor_pkg.employee_refcursor;

END;

 1 /*定义ref cursor package 体*/
 2 CREATE OR REPLACE PACKAGE BODY bxj_ref_cursor_pkg IS
 3 
 4   FUNCTION query_employee(user_id number)
 5     RETURN bxj_ref_cursor_pkg.employee_refcursor IS
 6     temp_employee_cursor bxj_ref_cursor_pkg.employee_refcursor;
 7   BEGIN
 8 
 9     OPEN temp_employee_cursor FOR
10       SELECT user_id, employee_num, user_name, party_type, prefix, full_name
11         FROM fnd_user fu, hz_parties hp, hr_employees he
12        WHERE fu.person_party_id = hp.party_id
13          AND hp.person_identifier = he.employee_id
14          AND fu.user_name = 'GAVIN';
15 
16     RETURN temp_employee_cursor;
17 
18   END;
19 
20 END;