oracle - 如何使用 RECORD TYPE 作为 OUT 参数执行存储过程

Question

这是包规范:

create or replace PACKAGE EMPLOYEE_DETAILS AS

        TYPE DETAILS IS RECORD( 
        EMPLOYEE_ID NUMBER(6,0),
          EMPLOYEE_FIRST_NAME VARCHAR2(20),
          EMPLOYEE_LAST_NAME VARCHAR2(25)
          );

        TYPE TABLE_EMPLOYEES IS TABLE OF DETAILS;

        PROCEDURE GET_EMPLOYEES(
        EMP_DEPT_ID EMPLOYEES.DEPARTMENT_ID%TYPE,
        EMP_SALARY employees.salary%TYPE,
        TBL_EMPLOYEES OUT TABLE_EMPLOYEES
        );

        END EMPLOYEE_DETAILS;

这是包体。我能够编译该包，但在执行存储过程时需要一些帮助来验证结果。

create or replace PACKAGE BODY EMPLOYEE_DETAILS AS

    PROCEDURE GET_EMPLOYEES(
    EMP_DEPT_ID EMPLOYEES.DEPARTMENT_ID%TYPE, 
    EMP_SALARY employees.salary%TYPE,
    TBL_EMPLOYEES OUT TABLE_EMPLOYEES
)

IS
    LC_SELECT SYS_REFCURSOR;
    LR_DETAILS DETAILS;
    TBL_EMPLOYEE TABLE_EMPLOYEES;

BEGIN
    OPEN LC_SELECT FOR 
        SELECT EMPLOYEE_ID,FIRST_NAME,LAST_NAME 
        FROM EMPLOYEES
        WHERE DEPARTMENT_ID=EMP_DEPT_ID
        AND EMPLOYEES.SALARY>EMP_SALARY;

    LOOP 
        FETCH LC_SELECT INTO LR_DETAILS;
        EXIT WHEN LC_SELECT%NOTFOUND;

        IF IS_EMPLOYEE(LR_DETAILS.EMPLOYEE_ID) THEN
            TBL_EMPLOYEE.extend();
            TBL_EMPLOYEE(TBL_EMPLOYEE.count()) := LR_DETAILS;
        END IF;
    END LOOP;
    CLOSE LC_SELECT;
    TBL_EMPLOYEES := TBL_EMPLOYEE;

END GET_EMPLOYEES;
END EMPLOYEE_DETAILS;

到目前为止我所拥有的是:

set serveroutput on
declare 
tbl_employees table_employees;
begin
employee_details.get_employees(30,1000,tbl_employees);

For i IN tbl_employees.First .. tbl_employees.Last Loop
dbms_output.put_line(tbl_employees(i).employee_id || ' ' ||
                                tbl_employees(i).first_name|| ' ' ||
                                tbl_employees(i).last_name);
End Loop;
end;

但是当我执行这个时，它给我错误提示

table_employees must be declared

还有一个是

PLS-00320: the declaration of the type of this expression is incomplete or malformed.

有人可以帮我解决这个问题吗？

Answer 1

您引用的记录类型不正确。

tbl_employees table_employees;

table_employees must be declared

您使用的是记录类型，而不是对象类型。因此，您不能将记录类型引用为数据库对象。您需要将其作为您创建的包对象进行引用。

您需要将记录类型引用为:

l_table_rec_type employee_details.table_employees

让我们看一个完整的测试用例:

SQL> CREATE OR REPLACE
  2  PACKAGE employee_details
  3  AS
  4  TYPE details
  5  IS
  6    RECORD
  7    (
  8      p_name   VARCHAR2(40),
  9      p_emp_id NUMBER );
 10  TYPE table_employees
 11  IS
 12    TABLE OF details;
 13    PROCEDURE get_employees(
 14        p_deptno IN emp.deptno%TYPE,
 15        p_sal IN emp.sal%TYPE,
 16        emp_rec OUT table_employees );
 17  END employee_details;
 18  /

Package created.

SQL>

包已创建，

SQL> CREATE OR REPLACE
  2  PACKAGE BODY employee_details
  3  AS
  4  PROCEDURE get_employees(
  5        p_deptno IN emp.deptno%TYPE,
  6        p_sal IN emp.sal%TYPE,
  7        emp_rec OUT table_employees )
  8  IS
  9  BEGIN
 10    SELECT ename, empno BULK COLLECT INTO emp_rec FROM scott.emp where deptno = p_deptno and sal > p_sal;
 11  END get_employees;
 12  END employee_details ;
 13  /

Package body created.

SQL>

包体也被创建。现在让我们调用该过程。

SQL> SET SERVEROUTPUT ON
SQL> DECLARE
  2    l_table_rec_type employee_details.table_employees;
  3  BEGIN
  4    dbms_output.put_line(' calling get_employees ');
  5    employee_details.get_employees(30, 1000, l_table_rec_type);
  6    FOR l_rec IN 1..l_table_rec_type.count
  7    LOOP
  8      dbms_output.put_line('employee details ' || l_table_rec_type(l_rec).p_name ||' '||l_table_rec_type(l_rec).p_emp_id);
  9    END LOOP;
 10  END;
 11  /
calling get_employees
employee details ALLEN 7499
employee details WARD 7521
employee details MARTIN 7654
employee details BLAKE 7698
employee details TURNER 7844

PL/SQL procedure successfully completed.

SQL>

您已获得所需的输出。