关于游标变量的使用(Cursor Variables)

  

关于游标变量 (Cursor Variables)

如何将存储过程返回的REF CURSOR类型的结果集插入到一个数据库表中？

游标变量(cursor variable)是一种指向结果集的PL/SQL变量。我们可以从游标变量的结果集中取得游标行(fetch)，就像是从显式游标中取得游标行一样。特别是使用针对游标变量的BULK COLLECT语法将结果集中的所有行存放到一个集合中，那么就可以在一个FORALL INSERT语句中引用这个集合。

 

下面举例说明这种使用方法。

CREATE TABLE jokes (

   joke_id INTEGER,

   title VARCHAR2(100),

   text VARCHAR2(4000)

)

/

CREATE TABLE joke_archive (

   archived_on DATE,

   old_stuff VARCHAR2(4000)

)

/

在jokes表中存放两条记录：

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;

 

建立get_title_or_text过程来来获取jokes表的数据，并返回REF CURSOR结果集：

 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 )

--参数解释：

--title_like_in：区别那些需要转移到joke_archive表的数据行的过滤条件。

return_title_in：是想得到标题(TRUE)还是得到文本(FALSE)。

--joke_count_out：返回所得到的行数。

--joke_out：返回所得到的结果集。

--SYS_REFCURSOR：系统定义的REF CURSOR类型，在Oracle9i Database Release 2及以上版本可用。

 7 IS

 8      c_from_where      VARCHAR2 (100) := ' FROM jokes WHERE title LIKE :your_title';

--第8行：由于get_title_or_text过程要执行两个动态查询语句，而仅仅是SELECT子句不同，因此将查询的其他部分（FROM和WHERE子句）存储在一个可重用的字符串中。

 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;

--第12-17行：构造动态查询来获得标题匹配输入条件的所有标题或文本。

18

19      OPEN jokes_out FOR l_query USING title_like_in;

--第19行：对指定的标题过滤条件，通过游标变量得到结果集。

20

21      EXECUTE IMMEDIATE 'SELECT COUNT(*)' || c_from_where

22                   INTO joke_count_out

23                  USING title_like_in;

--第21-23行：计算查询应返回的行数。

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.

 

现在我们可以调用这个过程来返回结果集，并将数据保存在集合中，然后在FORALL语句中使用这个集合来达成目的。

下面完成这项任务的匿名块：

1 DECLARE

 2      l_count         PLS_INTEGER;

 3      l_jokes          sys_refcursor;

--第2-3行：声明变量来获得get_title_or_text过程的返回值。

 4

 5      TYPE jokes_tt IS TABLE OF jokes.text%TYPE;

 6

 7      l_joke_array   jokes_tt      := jokes_tt ();

--第5-7行：声明一个嵌套表（nested table）来保存游标变量的数据。

 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                    );

--第8-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;

--第16-19行：使用BULK COLLECT语法将结果集中的所有行放置在嵌套表中，然后关闭游标变量。注意，这里使用了带有隐式SELECT INTO语句的BULK COLLECT来获取所有行数据。假如是查询大量数据，这种方案会消耗无法接受的内存。在那种情况下，可能应当使用带LIMIT子句的BULK COLLECT。

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));

--第21-23行：使用FORALL语句将数据插入joke_archive表。

24 END;

 

下面示范如何使用LIMIT子句来限制一个BULK COLLECT查询所获得的行数，这样可以减少生成集合时所耗费的内存。

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);

   LOOP

      --每次仅获取100行数据。

      FETCH l_jokes

      BULK COLLECT INTO l_joke_array LIMIT 100;

       --循环获取数据，直到取完既退出循环。

       EXIT WHEN l_joke_array.COUNT = 0;

   

      --将其保存进joke_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;

上述例子示范了在灵活的利用游标变量的同时，如何兼顾BULK COLLECT和FORALL的性能来构造查询。