“批量收集到"和“立即执行";在甲骨文 [英] "Bulk Collect Into" and "Execute Immediate" in Oracle
问题描述
是否可以在oracle中使用"execute immediate"
命令执行"bulk Collect into"
?所有这些都将成为函数的一部分,该函数将返回管道表作为结果.
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.
推荐答案
是的,从技术上讲,您可以:
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
Oracle在文档中明确指出了这一点:
And Oracle clearly states this in the documentation:
http://docs.oracle.com/cd/B19306_01/appdev.102 /b14261/executeimmediate_statement.htm
但是,如果您确实需要执行Dynamic SQL-弱引用游标,则可以使用更有用的方法事件.您将可以使用LIMIT等强大功能,并且可以使用记录集合.
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> 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
这篇关于“批量收集到"和“立即执行";在甲骨文的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!