oracle 将记录数组转换为refcursor
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3635879/
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
提示：将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间：2020-09-18 21:26:53 来源:igfitidea点击:
Convert array of records to refcursor
提问by K Ratnajyothi
The question is how to return the l_array as refcursor, Since the interface i am using can handle cursor easily rather than an array of record.
问题是如何将 l_array 作为 refcursor 返回，因为我使用的接口可以轻松处理游标而不是记录数组。
Plz help
请帮忙
create or replace package sample
TYPE r_type is record( code number; description varchar2(50));
TYPE tr_type IS TABLE OF r_type; l_rarray tr_type ; ind number:=0;
PROCEDURE getdata() IS
CURSOR cur IS
SELECT empid, empname, place, location FROM emp;
TYPE epmid_aat IS TABLE OF emp.empid%TYPE INDEX BY BINARY_INTEGER;
l_empid empid_aat;
BEGIN
k := 1;
FOR j IN (SELECT DISTINCT empid FROM emp)
LOOP
l_empid(k) := j.empid;
k := k + 1;
END LOOP;
FOR i IN cur
LOOP
FOR k IN l_empid.first .. l_empid.last
LOOP
IF l_empid(k) = i.empid THEN
procedure2(i.emp_id);
END IF;
END LOOP;
END LOOP;
END getdata();
PROCEDURE procedure2
(
empid_in IN NUMBER,
description_in IN VARCHAR2(20)
) IS
BEGIN
lrec.code := empid_in;
lrec.description := description_in;
l_rarray(ind) := lrec;
ind := ind + 1;
END procedure2;
end;
回答by Ismail
I think it should be like this :
我认为应该是这样的：
OPEN YourRefCursor
FOR SELECT * FROM TABLE (Cast(l_rarray AS tr_type));
回答by Michael Pakhantsov
Something like this.
像这样的东西。
TYPE r_type is record ( code number;
description varchar2(50)
);
TYPE tr_type IS TABLE OF r_type;
l_rarray tr_type ;
SELECT r_type(empid, empname)
BULK COLLECT INTO l_rarray
FROM emp;
OPEN YourRefCursor
SELECT *
FROM TABLE (Cast(l_rarray AS r_type));
