Oracle 存储过程：返回多行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24673492/
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
Oracle Stored Procedure: Returning a Multiple Rows
提问by Jemru
In my stored procedure it needs to get inputs and return multiple rows to a front-end application.
在我的存储过程中，它需要获取输入并将多行返回给前端应用程序。
However the stored Procedure produces an errors:
但是存储过程会产生错误：
- SQL Statement ignored
- not enough values
- 忽略 SQL 语句
- 没有足够的价值
How to resolve?
如何解决？
Object type
对象类型
CREATE TYPE org_rspnsble_prsns_type
AS OBJECT (
"appId" varchar2,
"orgId" varchar2,
"domainId" varchar2,
"leadName" varchar2,
"personId" number
);
Table type
表型
CREATE TYPE org_rspnsble_prsns_table
AS TABLE OF org_rspnsble_prsns_type;
Stored Procedure
存储过程
CREATE OR REPLACE PROCEDURE GetNames( appIdInput IN varchar2, orgIdInput IN varchar2, p_arr OUT org_rspnsble_prsns_table )
AS
BEGIN
SELECT "appId", "orgId", "domainId", "leadName", "personId"
BULK COLLECT INTO p_arr
FROM (
select "appId", "orgId", "domainId", "leadName", "personId"
from tableA
UNION
select "appId", "orgId", "domainId", "leadName", "personId"
from tableB
)
WHERE "appId" = appIdInput
AND "orgId" = orgIdInput;
END;
回答by Jemru
I found a good way to do it from: https://asktom.oracle.com/pls/apex/ASKTOM.download_file?p_file=6551171813078805685
我找到了一个很好的方法：https: //asktom.oracle.com/pls/apex/ASKTOM.download_file?p_file=6551171813078805685
create or replace package types
as
type cursorType is ref cursor;
end;
/
create or replace function sp_ListEmp return types.cursortype
as
l_cursor types.cursorType;
begin
open l_cursor for select ename, empno from emp order by ename;
return l_cursor;
end;
/
create or replace procedure getemps( p_cursor in out types.cursorType )
as
begin
open p_cursor for select ename, empno from emp order by ename;
end;
/
Thank you to all who answered.
感谢所有回答的人。
回答by Pavel Gatnar
Your SELECT select 5 columns into 1 variable. You have to either
A) create object in your select:
您的 SELECT 将 5 列选择为 1 个变量。您必须
A) 在您的选择中创建对象：
SELECT org_rspnsble_prsns_type("appId", "orgId", "domainId", "leadName", "personId")
BULK COLLECT INTO p_arr
...
or B) create the type as record:
或 B) 创建类型作为记录：
CREATE TYPE org_rspnsble_prsns_type
AS RECORD (...
