“批量收集到"和“立即执行";在甲骨文
是否可以在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