如何将数据从引用游标批量插入到 PL/SQL 中的临时表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2430466/
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 bulk insert data from ref cursor to a temporary table in PL/SQL
提问by Sambath Prum
Could anyone tell me how to bulk insert data from a ref cursor to a temporary table in PL/SQL? I have a procedure that one of its parameters stores a result set, this result set will be inserted to a temporary table in another stored procedure.
谁能告诉我如何将数据从引用游标批量插入到 PL/SQL 中的临时表？我有一个过程，它的一个参数存储了一个结果集，这个结果集将被插入到另一个存储过程中的临时表中。
This is my sample code.
这是我的示例代码。
CREATE OR REPLACE PROCEDURE get_account_list
(
type_id in account_type.account_type_id%type,
acc_list out sys_refcursor
)
is
begin
open acc_list for
select account_id, account_name, balance
from account
where account_type_id = type_id;
end get_account_list;
CREATE OR REPLACE PROCEDURE proc1
(
...
)
is
accounts sys_refcursor;
begin
get_account_list(1, accounts);
--How to bulk insert data in accounts to a temporary table?
end proc1;
In SQL Server, I can write as code below
在 SQL Server 中，我可以编写如下代码
CREATE PROCEDURE get_account_list
type_id int
as
select account_id, account_name, balance
from account
where account_type_id = type_id;
CREATE PROCEDURE proc1
(
...
)
as
...
insert into #tmp_data(account_id, account_name, balance)
exec get_account_list 1
How can I write similar to the code in SQL Server? Thanks.
如何编写类似于 SQL Server 中的代码？谢谢。
采纳答案by Vincent Malgrat
you can use BULK operations on REF CURSOR:
您可以在 REF CURSOR 上使用 BULK 操作：
SQL> CREATE GLOBAL TEMPORARY TABLE gt (ID NUMBER);
Table crúúe.
SQL> DECLARE
2 l_refcursor SYS_REFCURSOR;
3 TYPE tab_number IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
4 l_data tab_number;
5 BEGIN
6 OPEN l_refcursor FOR
7 SELECT ROWNUM FROM dual CONNECT BY LEVEL <= 1e6;
8 LOOP
9 FETCH l_refcursor BULK COLLECT
10 INTO l_data LIMIT 100;
11
12 FORALL i IN 1..l_data.count
13 INSERT INTO gt VALUES (l_data(i));
14
15 EXIT WHEN l_refcursor%NOTFOUND;
16
17 END LOOP;
18 CLOSE l_refcursor;
19 END;
20 /
Procúdure PL/SQL terminúe avec succTs.
Oracle 10g already implements this optimization for regular loop though, so you may not see much improvement from a simple LOOP...INSERT.
不过，Oracle 10g 已经为常规循环实现了这种优化，因此您可能不会从简单的 LOOP...INSERT 中看到太多改进。
回答by Padmarag
How about
怎么样
procedure insert_rec(in_type_id in number) is
begin
insert into temp_table
select account_id, account_name, balance
from account
where account_type_id = in_type_id;
end insert_rec;
