立即执行批量收集限制 [英] BULK COLLECT LIMIT in EXECUTE IMMEDIATE
问题描述
是否可以使用立即执行将限制放入批量收集中?
Is it possible to put a limit in a bulk collect using execute immediate?
我有以下脚本,但是使用LIMIT时出现错误.
I have below script but I am getting error when using a LIMIT.
declare
v_query varchar2(3000);
begin
v_query := 'select 1 from dual' -- this is just a sample query.
execute immediate v_query
bulk collect into table1 --table type
end;
如果我的代码无法进行限制,是否可以解决?
If limit is not possible with my code, is there any work around?
谢谢!
推荐答案
似乎EXECUTE IMMEDIATE语法不允许在bulk collect子句中使用LIMIT
http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/executeimmediate_statement.htm#CJACGJJG
It seems that EXECUTE IMMEDIATE syntax doesn't allow for LIMIT in bulk collect clause
http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/executeimmediate_statement.htm#CJACGJJG
bulk_collect_into_clause
http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/returninginto_clause.htm#CJAIAGHJ
您可以通过以下方式将游标和FETCH .. LIMIT命令与OPEN..FOR命令一起使用,
:
You can use a cursor and FETCH .. LIMIT command together with OPEN..FOR command,
in this way:
DECLARE
....
cur sys_refcursor;
BEGIN
v_query := 'SELECT level AS x FROM dual CONNECT BY LEVEL <=10';
OPEN cur FOR v_query;
LOOP
FETCH cur BULK COLLECT INTO collection LIMIT 3;
EXIT WHEN collection.COUNT = 0;
/* Process data from `collection` */
END LOOP;
CLOSE cur;
END;
示例:
Example:
DECLARE
TYPE col_typ IS table of NUMBER;
collection col_typ;
v_query varchar2(3000);
cur sys_refcursor;
i int := 0;
x int;
BEGIN
v_query := 'SELECT level AS x FROM dual CONNECT BY LEVEL <=10';
OPEN cur FOR v_query;
LOOP
FETCH cur BULK COLLECT INTO collection LIMIT 3;
EXIT WHEN collection.COUNT = 0;
/* Process data from `collection` */
i := i + 1;
DBMS_OUTPUT.PUT_LINE('==== Batch nbr #' || i );
FOR x IN 1 .. collection.COUNT LOOP
DBMS_OUTPUT.PUT_LINE( collection( x ) );
END LOOP;
END LOOP;
CLOSE cur;
END;
/
结果:
==== Batch nbr #1
1
2
3
==== Batch nbr #2
4
5
6
==== Batch nbr #3
7
8
9
==== Batch nbr #4
10
这篇关于立即执行批量收集限制的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!