how to use bulk collect instead of a loop in Oracle
I have a sample query like below in my procedure:
result_rec mypkg.mytype;
OPEN CUR1 FOR
select col1, col2, col3 from table1 where something = 'a'; --rows will always be 50
LOOP
FETCH CUR1
INTO myrectype;
EXIT WHEN CUR1%NOTFOUND;
result_rec.col1 := myrectype.col1;
result_rec.col2 := myrectype.col2;
result_rec.col3 := myrectype.col3;
PIP开发者_Go百科E ROW (result_rec);
END LOOP;
As you can see, every time I am looping 50 times. Is there a better way to do this? something like BULK COLLECT INTO? how would I implement that?
In Oracle 10g (possibly 9i), Oracle will automatically bulk collect implicit cursors. So code like:
DECLARE
result_rec mypkg.mytype;
BEGIN
for i in (select col1, co2, col3 from table1 where something = 'a')
loop
result_rec.col1 := i.col1;
result_rec.col2 := i.col2;
result_rec.col3 := i.col3;
pipe_row (result_rec);
end loop;
END;
Will only make the context switch from the PL/SQL engine to the SQL engine for fetching records once every 100 rows. Run it under a SQL trace (dbms_monitor.session_trace_enable()
) and see!
You can try the following.
DECLARE
type tab_result_rec IS TABLE OF mypkg.mytype INDEX BY PLS_INTEGER;
t_result_rec tab_result_rec;
BEGIN
select col1, col2, col3 bulk collect into t_result_rec
from table1 where something = 'a'; --rows will always be 50
--
for i in 1..t_result_rec.count LOOP
PIPE ROW (t_result_rec(i));
end loop;
END;
精彩评论