Oracle PL/SQL 返回行数组
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19854029/
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 PL/SQL return array of rows
提问by baliman
Dear Oracle Developers,
尊敬的 Oracle 开发人员：
I have searched and googled to find a solution for my problem but nothing helped me.
我已经搜索并用谷歌搜索为我的问题找到解决方案，但没有任何帮助。
Situation :
情况 ：
TABLE : CUSTOMER(....);
表：客户（....）；
My problem is : I want to create a stored procedure say get_customersto return an array of customer rows. I have no idea how to get this working.
我的问题是：我想创建一个存储过程，说get_customers要返回一个客户行数组。我不知道如何让这个工作。
I tried to create a type customer_rec and using a cursor to retrieve no more than maxRows
我尝试创建一个类型 customer_rec 并使用游标检索不超过 maxRows
create or replace procedure get_customers(maxRows IN NUMBER, ??? OUT ????)
How to define the OUT parameter ?
如何定义 OUT 参数？
How to retrieve the rows in the array using a cursor ?
如何使用游标检索数组中的行？
Thanks a lot
非常感谢
回答by Michael O'Neill
I would like to answer this in a way that discourages passing around arrays, when passing around cursors is a more sound approach. It doesn't exactly answer the question as posed, but it is an answer. Thinking cursors instead of thinking arrays is more efficient and thus more scalable. Also, it can be much easier code to maintain.
我想以一种不鼓励传递数组的方式来回答这个问题，因为传递游标是一种更合理的方法。它并没有完全回答所提出的问题，但它是一个答案。思考游标而不是思考数组更有效，因此更具可扩展性。此外，它可以更容易维护代码。
create table customer (
customer_id number(2) primary key,
customer_name varchar2(200) );
insert into customer values (1, 'Customer One');
insert into customer values (2, 'Customer Two');
insert into customer values (3, 'Customer Three');
insert into customer values (4, 'Customer Four');
insert into customer values (5, 'Customer Five');
insert into customer values (6, 'Customer Six');
insert into customer values (7, 'Customer Seven');
CREATE OR REPLACE PACKAGE cursor_not_array IS
FUNCTION get_customers(p_max_records INTEGER, p_id_start INTEGER, p_id_end INTEGER DEFAULT NULL) RETURN SYS_REFCURSOR;
END cursor_not_array;
CREATE OR REPLACE PACKAGE BODY cursor_not_array IS
c_max_customer_id CONSTANT NUMBER(2) := 99;
FUNCTION get_customers(p_max_records INTEGER, p_id_start INTEGER, p_id_end INTEGER DEFAULT NULL) RETURN SYS_REFCURSOR IS
v_result SYS_REFCURSOR;
BEGIN
OPEN v_result FOR
SELECT customer_id,
customer_name
FROM customer
WHERE customer_id BETWEEN p_id_start AND nvl(p_id_end, c_max_customer_id)
ORDER BY customer_id;
RETURN v_result;
END;
END cursor_not_array;
回答by pablomatico
You could create a package like this:
你可以创建一个这样的包：
create or replace package customers is
type customers_array is table of customer%rowtype index by binary_integer;
procedure get_customers(maxRows IN NUMBER, customer_array OUT customers_array);
end customers;
create or replace package body customers is
procedure get_customers(maxRows IN NUMBER, customer_array OUT customers_array) is
cursor c_customers is
select *
from customers;
where rownum <= maxRows;
i number := 1;
begin
for r in c_customers loop
customer_array(i) := r;
i := i + 1;
end loop;
end get_customers;
end customers;
And then call the get_customers procedure from wherever you want to...
然后从任何你想要的地方调用 get_customers 过程......
回答by Issam Ressani
You can, using the SYS_REFCURSORon your function output.
您可以在函数输出上使用SYS_REFCURSOR。
回答by Guillaume
First you have to define a collection:
首先你必须定义一个集合：
TYPE customers_array IS TABLE OF customer%ROWTYPE
INDEX BY BINARY_INTEGER;
Then your procedure simply have to fetchthe result into that collection. You're procedure could be written as follow:
然后您的过程只需要将结果提取到该集合中。你的程序可以写成如下：
CREATE OR REPLACE PACKAGE your_pkg
AS
TYPE customers_array IS TABLE OF customer%ROWTYPE
INDEX BY BINARY_INTEGER;
PROCEDURE get_customers(pn_max_rows IN NUMBER,
pt_coustomers OUT customers_array);
END your_pkg;
CREATE OR REPLACE PACKAGE BODY your_pkg
AS
PROCEDURE get_customers(pn_max_rows IN NUMBER,
pt_coustomers OUT customers_array)
IS
BEGIN
SELECT *
BULK COLLECT INTO pt_coustomers
FROM customers
WHERE rownum <= pn_max_rows;
END get_customers;
END your_pkg;
回答by user2956499
first time create VARRAY type.
第一次创建 VARRAY 类型。
'create TYPE CUSTARRAY is VARRAY(100) OF VARCHAR2(30);'
varray limit is depends on you.
varray 限制取决于你。
then create procedure that return CUSTARRAY type parameter.
然后创建返回 CUSTARRAY 类型参数的过程。
`create
procedure prc_get_arr(p_maxrow in number, p_customers out custarray)
as
my_cust custarray := custarray();
cursor c_cust is select name from CUSTOMER where rownum<p_maxrow;
v_customer varchar2(64);
begin
open c_cust;
loop
fetch c_cust into v_customer;
exit when c_cust%notfound;
my_cust.extend;
my_cust(my_cust.count) := v_customer;
end loop;
close c_cust;
p_customers:=my_cust;
end;`
Now call this procedure
现在调用这个程序
DECLARE
P_MAXROW NUMBER;
p_customers custarray;
v_cnt number:=0;
begin
P_MAXROW := 22;
prc_get_arr( p_maxrow => p_maxrow, p_customers => p_customers );
v_cnt:=p_customers.count;
for i in p_customers.first..p_customers.last loop
dbms_output.put_line('P_CUSTOMERS = ' || p_customers(i));
end loop;
end;
