开发技术:关于变量、移动以及含义
添加时间: 2008-4-26 1:21:21 作者: Oracle指导 阅读次数:15 来源: http://www.d9soft.com
-- Fetch the next 100 rows.
FETCH l_jokes
BULK COLLECT INTO l_joke_array LIMIT 100;
EXIT WHEN l_joke_array.COUNT = 0;
-- Push them into the archive.
FORALL indx IN l_joke_array.FIRST .. l_joke_array.LAST
INSERT INTO joke_archive
VALUES (SYSDATE, l_joke_array (indx));
END LOOP;
CLOSE l_jokes;
END;
This answer demonstrates how you can take advantage of the flexibility of cursor variables to construct your queries at runtime without losing the performance benefits of the BULK COLLECT and FORALL features.
Moving Back and Forth
I need to be able to move back and forth within a cursor result set, comparing the contents of distinct rows. I also would like to be able to read from the end of the result set to the beginning. How can I do that inside a PL/SQL program?
I will assume in my answer that your question refers to server-side-only code that needs to traverse a cursor's result set. An entirely different set of considerations comes into play if you are talking about transferring data from the server to a user interface, such as a Web page, and allowing that front-end environment to flexibly move through result sets.
Oracle does not currently support bidirectional access to cursor result sets (aka scrollable cursors) through a PL/SQL interface. You might well find, however, that you can achieve the desired effect with a combination of the following:
Multiple queries (each with different ORDER BY clauses that correspond to the different ways you need to traverse the result set).
Analytic functions: As the Oracle Database SQL Reference states, "Analytic functions compute an aggregate value based on a group of rows. They differ from aggregate functions in that they return multiple rows for each group. The group of rows is called a window and is defined by the analytic_clause. For each row, a sliding window of rows is defined. The window determines the range of rows used to perform the calculations for the current row. . . ."
For tables with a relatively small number of rows, the use of multiple queries may yield a satisfactory implementation. If, on the other hand, your result set is very large, you may run into some performance issues. In addition, you may still not be able to reference arbitrary rows within the result set as desired.
Fortunately, you can achieve the desired effect of a bidirectional cursor rather easily by caching the result in a PL/SQL collection. Once the data has been moved into the cache, you can move back and forth through the result set, compare rows, and so on, with complete freedom and a high degree of efficiency. I will demonstrate how you can build and move through such a cache.
Recall that PL/SQL program data consumes program global area (PGA) memory, distinct from the system global area (SGA), and there is a separate PGA for each session connected to an Oracle instance. With large result sets, you are going to be manipulating lots of data and the PGA will require lots of memory for the collection.
This technique of building and moving through a PL/SQL collection cache will make the most sense under the following circumstances:
You are running this program for a small number of simultaneous sessions, or it is a single batch process. You must have sufficient memory to hold the cache(s) you will create to emulate bidirectional cursors.
The data in the result set is static (or you want to ignore any changes that occur once your program starts). Once you have copied your result set to your collection-based cache, any changes to the tables that contributed to your result set will not be reflected in the cache—even if those changes are committed in some other session. This is a "one-off," static copy of the table (or whatever result set you have defined with your query).
Listing 5 offers an example of bidirectional cursor processing built around a collection of records with the same structure (and data) as the jokes table defined below:
CREATE TABLE jokes (
joke_id INTEGER,
title VARCHAR2(100),
text VARCHAR2(4000)
)
/
Code Listing 5: Example of bidirectional cursor processing with collections
1 CREATE OR REPLACE PROCEDURE bidir_example
2 IS
3 TYPE joke_tt IS TABLE OF jokes%ROWTYPE
4 INDEX BY PLS_INTEGER;
5
6 joke_cache joke_tt;
7 l_row PLS_INTEGER;
8 BEGIN
9 SELECT *
10 BULK COLLECT INTO joke_cache
11 FROM jokes;
12
13 DBMS_OUTPUT.put_line ('From first to last...');
14 l_row := joke_cache.FIRST;
15
16 WHILE (l_row IS NOT NULL)
17 LOOP
18 DBMS_OUTPUT.put_line (' ' || joke_cache (l_row).title);
19 l_row := joke_cache.NEXT (l_row);
20 END LOOP;
21
22 DBMS_OUTPUT.put_line ('From last to first...');
23 l_row := joke_cache.LAST;
24
25 WHILE (l_row IS NOT NULL)
26 LOOP
27 DBMS_OUTPUT.put_line (' ' || joke_cache (l_row).title);
28 l_row := joke_cache.PRIOR (l_row);
29 END LOOP;
30
31 DBMS_OUTPUT.put_line ('Compare fifth row to twelfth row...');
32
33 IF LENGTH (joke_cache (5).title) > LENGTH (joke_cache (12).title)
34 THEN
35 DBMS_OUTPUT.put_line ('Fifth row title longer than twelfth.');
36 ELSE
37 DBMS_OUTPUT.put_line ('Fifth row title is not longer than twelfth.');
38 END IF;
39
40 joke_cache.DELETE;
41 END bidir_example;
Lines 3-6. Declare a collection type that mimics the structure of the jokes relational table, and then instantiate a variable based on that type (joke_cache).
开发技术:关于变量、移动以及含义(3) 第 [1] [2] [3] [4] [5] 下一页
上一篇文章: 入门基础:连接常见错误及解决方法 下一篇文章: 开发技术:调查您的应用程序需求