开发技术:关于变量、移动以及含义(1)

文章作者 100test 发表时间 2007:03:14 13:47:27
来源 100Test.Com百考试题网

　　apply best practices to cursor variables, bidirectional cursor access, and error messages.
i have a stored procedure that uses a ref cursor parameter to return a result set as a cursor variable. how can i call that procedure and then insert the rows identified by the cursor variable into a table, using forall?

　　 fetch the rows of a cursor variable’s result set just as you would fetch the rows identified by an explicitly declared cursor. you can in particular use bulk collect against a cursor variable to deposit into a collection all the rows identified by the result set. you can then reference that collection in a forall insert statement.
let’s take a look at the kind of code you would write to achieve your goal. i will use the jokes and joke_archive tables as my example data structures:
create table jokes (
joke_id integer,
title varchar2(100),
text varchar2(4000)
)
/
create table joke_archive (
archived_on date,
old_stuff varchar2(4000)
)
/

i will place two jokes in the jokes table, as shown in listing 1.
code listing 1: inserting jokes into 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.

i now need to write a procedure that will identify joke text or titles that need to be moved to the joke_archive table. here is the header of my procedure:
create or replace
procedure get_title_or_text (
title_like_in in varchar2
,return_title_in in boolean
,joke_count_out out pls_integer
,jokes_out out sys_refcursor
)

i pass in a string (title_like_in) that acts as a filter to identify the rows in the jokes table to be moved to the archive. i specify whether i want to retrieve titles (return_title_in => true) or text (return_title_in => false). i then return the total number of rows identified by the result set (joke_count_out), as well as the result set itself (joke_out). i use the system-defined weak ref cursor type, sys_refcursor (available in oracle9i database release 2 and later).