Oracle 中的“批量收集到”和“立即执行”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21117021/
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
"Bulk Collect Into" and "Execute Immediate" in Oracle
提问by Thiago Burgos
is it possible to execute the "bulk Collect into"with the "execute immediate"commands in oracle? All of that would be part of a function that returns a pipe lined table as a result.
是否可以"bulk Collect into"使用"execute immediate"oracle中的命令执行？所有这些都将成为返回管道内衬表作为结果的函数的一部分。
回答by Dmitry Nikiforov
Yes, technically you can:
是的，从技术上讲，您可以：
1 SQL> declare
2 type x is table of t.id%type index by pls_integer;
3 xx x;
4 begin
5 execute immediate
6 'select id from t' bulk collect into xx;
7 dbms_output.put_line(xx.count);
8 end;
9 /
426
And Oracle clearly states this in the documentation:
Oracle 在文档中明确说明了这一点：
http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/executeimmediate_statement.htm
http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/executeimmediate_statement.htm
But you can use more useful way event if you really NEED to execute Dynamic SQL - weak ref cursors. You will have the access to such powerful option as LIMIT and will be able to use collections of records.
但是如果你真的需要执行动态 SQL - 弱引用游标，你可以使用更有用的方式事件。您将可以访问诸如 LIMIT 之类的强大选项，并将能够使用记录集合。
SQL> declare
2 type x is table of t%rowtype index by pls_integer;
3 xx x;
4 c sys_refcursor;
5 begin
6 open c for 'select * from t';
7 loop
8 fetch c bulk collect into xx limit 100;
9 dbms_output.put_line(xx.count);
10 exit when c%notfound;
11 end loop;
12 close c;
13 end;
14 /
100
100
100
100
26
回答by Thiago Burgos
Following the idea proposed by Dmitry Nikiforov I solved the problem using cursors and here is the solution ;)
按照 Dmitry Nikiforov 提出的想法，我使用游标解决了这个问题，这是解决方案；)
FUNCTION myFunction ( parameter_p IN VARCHAR2) RETURN myTableType PIPELINED
IS
c sys_refcursor;
stmt varchar2(4000);
out_rec mYrowType;
rec mYrowType;
BEGIN
stmt:='Select * from ''' || parameter_p || '''';
open c for stmt;
LOOP
fetch c into rec;
EXIT WHEN c%NOTFOUND;
out_rec.field1 := rec.field1;
out_rec.field2 := rec.field2;
out_rec.field3 := rec.field3;
PIPE Row(out_rec);
END LOOP;
Close c;
Return;
END myFunction;
