About cursor Variables
(Cursor variables)How can I insert the ref cursor type result set returned by a stored procedure into a database table? Cursor variable is a PL/SQL variable pointing to the result set. We can get the rows of the cursor (FETCH) from the result set of the cursor variable, just like getting the rows of the cursor from an explicit cursor. In particular, if you use the bulk collect syntax for the cursor variable to store all rows in a set, you can reference this set in a forall insert statement. The following is an example of this method. Create Table jokes (joke_id integer, title varchar2 (100), text varchar2 (4000)/create table joke_archive (archived_on date, old_stuff varchar2 (4000 )) /store two records in the jokes table: Begin insert into jokes values (100, 'Why does an elephant take a shower? ', 'Why does an elephant take a shower? '| 'Because it can' t fit into the bathtub! '); Insert into jokes values (101, 'How can you prevent diseases caused by biting insects? ', 'How can you prevent diseases caused by biting insects? '| 'Don' t bite any! '); Commit; end; create the get_title_or_text process to obtain the data of the jokes table, and return the ref cursor result set: 1 create or replace procedure get_title_or_text (2 title_like_in in varchar2 3, return_title_in Boolean 4, joke_count_out pls_integer 5, jokes_out sys_refcursor 6) -- parameter explanation: -- title_like_in: distinguishes filtering conditions for data rows that need to be transferred to the joke_archive table. Return_title_in: whether to get the title (true) or text (false ). -- Joke_count_out: returns the number of rows. -- Joke_out: returns the result set. -- Sys_refcursor: the ref cursor type defined by the system. It is available in Oracle9i Database Release 2 and later versions. 7 is 8 c_from_where varchar2 (100): = 'from jokes where title like: your_title '; -- Row 8th: Because the get_title_or_text process needs to execute two dynamic query statements, but the select clause is different, therefore, the rest of the query (from and where clauses) is stored in a reusable string. 9 l_colname all_tab_columns.column_name % Type: = 'text'; 10 l_query varchar2 (32767); 11 begin12 if return_title_in13 then14 l_colname: = 'title'; 15 end if; 1617 l_query: = 'select' | l_colname | c_from_where; -- Row 12-17: constructs a dynamic query to obtain all titles or texts whose titles match the input conditions. 1819 open jokes_out for l_query using title_like_in; -- Row 19th: filter the specified title by using the cursor variable to obtain the result set. 2021 execute immediate 'select count (*) '| c_from_where22 into joke_count_out23 using title_like_in; -- row 21-23: calculates the number of rows to be returned in the query. 24 end get_title_or_text; I can now call this procedure, return a result set, move that data into a collection, and then use the collection in a forall statement, as shown in listing 3. now we can call this process to return the result set, store the data in the Set, and use this set in the forall statement to achieve the goal. The following anonymous block for completing this task: 1 declare 2 l_count pls_integer; 3 l_jokes sys_refcursor; -- Row 2-3: declare the variable to get the return value of the get_title_or_text process. 4 5 type jokes_tt is table of jokes. text % type; 6 7 l_joke_array jokes_tt: = jokes_tt (); -- Row 5-7: declares a nested table to store cursor variable data. 8 begin 9 rows (title_like_in => '% insect %' 10, return_title_in => false11, joke_count_out => l_count12, jokes_out => l_jokes13); -- Row 8-13: call the stored procedure to return the number of rows in the cursor variable and result set. 14 dbms_output.put_line ('number of jokes found = '| l_count); 1516 fetch l_jokes17 bulk collect into l_joke_array; 1819 close l_jokes; -- Row 16-19: use the bulk collect syntax to place all rows in the result set in a nested table, and then close the cursor variable. Note that bulk collect with an implicit select into statement is used to obtain all row data. If it is to query a large amount of data, this solution will consume unacceptable memory. In that case, the bulk collect with the limit clause may be used. 2021 forall indx in l_joke_array.first .. ignore insert into joke_archive23 values (sysdate, l_joke_array (indx); -- Row 21-23: insert data into the joke_archive table using the forall statement. 24 end; the following shows how to use the limit clause to limit the number of rows obtained by a bulk collect query, which can reduce the memory consumption during collection generation. Declare l_count pls_integer; l_jokes sys_refcursor; Type jokes_tt is table of jokes. text % type; l_joke_array jokes_tt: = jokes_tt (); begin substring (rows => '% insect %', return_title_in => false, joke_count_out => l_count, jokes_out => l_jokes ); dbms_output.put_line ('number of jokes found = '| l_count); loop -- get only 100 rows of data at a time. Fetch l_jokes bulk collect into l_joke_array
The limit 100; -- Obtain data cyclically until the data is obtained and the loop is exited. Exit when l_joke_array.count = 0; -- save it to the joke_archive table forall indx in l_joke_array.first .. rochelle joke_array.last insert into joke_archive values (sysdate, l_joke_array (indx); End loop; close l_jokes; end; the preceding example demonstrates flexible use of cursor variables while, how to construct a query based on the performance of bulk collect and forall.
