开发技术:关于变量、移动以及含义
添加时间: 2008-4-23 22:52:28 作者: Oracle指导 阅读次数:25 来源: http://www.d9soft.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?
A cursor variable is a PL/SQL variable that points to a result set. You can 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).
Listing 2 contains the implementation of the get_title_or_text procedure.
Code Listing 2: get_title_or_text procedure
1 CREATE OR REPLACE PROCEDURE get_title_or_text (
2 title_like_in IN VARCHAR2
3 ,return_title_in IN BOOLEAN
4 ,joke_count_out OUT PLS_INTEGER
5 ,jokes_out OUT SYS_REFCURSOR
6 )
7 IS
8 c_from_where VARCHAR2 (100) := ' FROM jokes WHERE title LIKE :your_title';
9 l_colname all_tab_columns.column_name%TYPE := 'TEXT';
10 l_query VARCHAR2 (32767);
11 BEGIN
12 IF return_title_in
13 THEN
14 l_colname := 'TITLE';
15 END IF;
16
17 l_query := 'SELECT ' || l_colname || c_from_where;
18
19 OPEN jokes_out FOR l_query USING title_like_in;
20
21 EXECUTE IMMEDIATE 'SELECT COUNT(*)' || c_from_where
22 INTO joke_count_out
23 USING title_like_in;
24 END get_title_or_text;
Here is an explanation of the interesting parts of the get_title_or_text procedure in Listing 2.
开发技术:关于变量、移动以及含义(1) 第 [1] [2] [3] [4] [5] 下一页
上一篇文章: 入门基础:连接常见错误及解决方法 下一篇文章: 开发技术:调查您的应用程序需求
相关软件: