oracle 如何使用 RECORD TYPE 作为 OUT 参数执行存储过程
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/29203376/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to execute a Stored Procedure with RECORD TYPE as OUT parameter
提问by kris
This is the package specification:
这是包装规格：
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;
And this is the package body. I was able to compile the package but need some help on executing the stored procedure to verify the results.
这是包体。我能够编译包，但在执行存储过程以验证结果方面需要一些帮助。
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;
What I've have so far is:
到目前为止我所拥有的是：
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;
But when I execute this it gives me error saying
但是当我执行这个时它给了我错误说
table_employeesmust be declared
table_employees必须声明
and the other one is
另一个是
PLS-00320: the declaration of the type of this expression is incomplete or malformed.
PLS-00320：此表达式的类型声明不完整或格式错误。
Can somebody please help me with this?
有人可以帮我解决这个问题吗？
回答by Lalit Kumar B
You are referring the record type incorrectly.
您错误地引用了记录类型。
tbl_employees table_employees;
table_employees must be declared
tbl_employees table_employees;
必须声明 table_employees
You are using a record typeand not an object type. Thus, you cannot refer the record type as a database object. You need to refer it as the package object you created.
您使用的是记录类型而不是对象类型。因此，您不能将记录类型称为数据库对象。您需要将其称为您创建的包对象。
You need to refer the record type as:
您需要将记录类型称为：
l_table_rec_type employee_details.table_employees
l_table_rec_type employee_details.table_employees
Let's look at a complete test case:
让我们看一个完整的测试用例：
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>
Package is created,
包已创建，
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>
Package body is also created. Now let,s call the procedure.
包体也被创建。现在让我们调用程序。
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>
You have the required output.
您有所需的输出。
回答by psaraj12
Since table_employees is defined inside the package employee_details hence use
由于 table_employees 是在包 employee_details 中定义的，因此使用
tbl_employees employee_details.table_employees := employee_details.table_employees();
Also do the same change for initializing collection in package body
也对包体中的初始化集合进行相同的更改
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:= table_employees();
