开发技术:关于变量、移动以及含义
添加时间: 2008-4-23 22:52:28 作者: Oracle指导 阅读次数:43 来源: http://www.d9soft.com
Lines 12-17. Construct the dynamic query to retrieve all titles or text with a matching title.
Line 19. Associate the result set with the cursor variable, for the specified title filter.
Lines 21-23. Compute the number of rows identified by the query.
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.
Code Listing 3: From results to collection to FORALL
1 DECLARE
2 l_count PLS_INTEGER;
3 l_jokes sys_refcursor;
4
5 TYPE jokes_tt IS TABLE OF jokes.text%TYPE;
6
7 l_joke_array jokes_tt := jokes_tt ();
8 BEGIN
9 get_title_or_text (title_like_in => '%insect%'
10 ,return_title_in => FALSE
11 ,joke_count_out => l_count
12 ,jokes_out => l_jokes
13 );
14 DBMS_OUTPUT.put_line ('Number of jokes found = ' || l_count);
15
16 FETCH l_jokes
17 BULK COLLECT INTO l_joke_array;
18
19 CLOSE l_jokes;
20
21 FORALL indx IN l_joke_array.FIRST .. l_joke_array.LAST
22 INSERT INTO joke_archive
23 VALUES (SYSDATE, l_joke_array (indx));
24 END;
Here is an explanation of the interesting parts of the anonymous block in Listing 3.
Lines 2 and 3. Declare variables to retrieve the values returned by the get_title_or_text procedure.
Lines 5-7. Declare a nested table to hold the data identified by the cursor variable.
Lines 8-13. Call the stored procedure to return the cursor variable and the count of rows in the result set.
Lines 16-19. Use BULK COLLECT to fetch all the rows in the result set into the nested table. Then close the cursor variable. Note that I use BULK COLLECT with an implicit SELECT INTO to retrieve all the rows identified by the cursor. If you are querying a large volume of data, this approach can consume an unacceptable amount of memory. Under such circumstances, you may want to switch to using BULK COLLECT with a LIMIT clause.
Lines 21-23. Use a FORALL statement to push the data into the joke archive.
Listing 4 contains a modified version of the previous anonymous block in Listing 3, showing how to use the LIMIT clause to restrict the number of rows fetched with a BULK COLLECT query, thus reducing the amount of memory needed to populate the collection.
Code Listing 4: From results to collection to FORALL plus LIMIT
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
get_title_or_text (title_like_in => '%insect%'
,return_title_in => FALSE
,joke_count_out => l_count
,jokes_out => l_jokes
);
DBMS_OUTPUT.put_line ('Number of jokes found = ' || l_count);
开发技术:关于变量、移动以及含义(2) 第 [1] [2] [3] [4] [5] 下一页
上一篇文章: 入门基础:连接常见错误及解决方法 下一篇文章: 开发技术:调查您的应用程序需求