使用ref游标返回结果集

一、

PL/SQL可以返回结果集，这是Oracle中自数据库版本7.2以来可用的一个特性（1995年引入）。

一般使用ref游标是返回结果给客户机的最适合的方法。理由如下：

。容易编程

。灵活  客户端应用程序看选择一次取多少行。不是在一个数组中送回10000条，而是送回客户端可一次从其中取10条的一个结果集。

。性能 不需要让PL/SQL从游标取数据、填充数组（在服务器上分配内存）并发送该数组到客户端（在客户端机器上分配内存），使客户端在取得第一行前等待处理完最后一行。ref游标将立即返回数据给客户端而不用做上述任何事情。

 

二、

作为何处适合于使用ref游标的例子，我们使用一个从all_objects的副本取数据的Java客户端应用程序。我们将一次用PL/SQL表类型编码，一次用ref游标编码。

SQL> create table t as select * from all_objects;
 
Table created

 

SQL>

create or replace package demo_pkg
as
       type varchar2_array is table of varchar2(30)
            index by binary_integer;
       type rc is ref cursor;
       procedure index_by(p_owner in varchar2,
                          p_object_name out varchar2_array,
                          p_object_type out varchar2_array,
                          p_timestamp out varchar2_array);
       procedure ref_cursor(p_owner in varchar2,
                            p_cursor in out rc);
end demo_pkg;

SQL>

create or replace package body demo_pkg is
       procedure index_by(p_owner in varchar2,
                          p_object_name out varchar2_array,
                          p_object_type out varchar2_array,
                          p_timestamp out varchar2_array)
       is
       begin
         select object_name,object_type,timestamp
           bulk collect into
                p_object_name,p_object_type,p_timestamp
           from t
          where wner=p_owner;
        end index_by;
                         
       procedure ref_cursor(p_owner in varchar2,
                            p_cursor in out rc)
       is
       begin
         open p_cursor for
         select object_name,object_type,timestamp
           from t
          where wner=p_owner;
       end ref_cursor;
end demo_pkg;

其中，INDEX_BY例程使用BULK COLLECT取所有数据。REF_CURSOR例程只进行一个OPEN。

 

三、调用INDEX_BY例程的Java客户端应用程序如下。从一个非常简单的计时例程开始，此例程将打出调用之间经历的时间（以毫秒计时）。

import java.sql.*;
import java.util.Date;
import oracle.jdbc.driver.*;
import oracle.sql.*;
public class indexby {
 static long start = new Date().getTime();
 public static void showElapsed(String msg)
 {
  long end=new Date().getTime();
  System.out.println(msg+ " " + (end - start) + " ms");
  start=end;
 }
}

每次调用该例程时，它都要打出最后一次调用它以来经历的时间，然后记住最后的这次新时间。下面来看看主例程。

从连接Oracle开始。

public static void main(String args[]) throws Exception
 {
  DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
  Connection conn = DriverManager.getConnection("jdbc:oracle:thin:hr/hr@192.168.1.100:1551:orcl");
  showElapsed("Connected,going to prepare");
  OracleCallableStatement cstmt = (OracleCallableStatement)conn.prepareCall("begin demo_pkg.index_by(?,?,?,?); end;");
  showElapsed("Connected,going to bind");
  int maxl=15000;
  int elemSqlType=OracleTypes.VARCHAR;
  int elemMaxLen=30;
  cstmt.setString(1, "SYS");
  cstmt.registerIndexTableOutParameter(2, maxl, elemSqlType, elemMaxLen);
  cstmt.registerIndexTableOutParameter(3, maxl, elemSqlType, elemMaxLen);
  cstmt.registerIndexTableOutParameter(4, maxl, elemSqlType, elemMaxLen);  

然后在DEMO_PKG中准备一个对INDEX_BY的调用。将绑定SYS到第一个输入参数，然后一个接一个的定义输出PL/SQL索引表。设置了3个列，分别为：MAXL,它是准备处理的“行”最大数目，数组的最大尺寸；ELEMSQLTYPE,每个输出数组的数据类型；ELEMMAXLEN,预期的每个数组元素的最大宽度。

 

接着，执行语句。在执行语句之后，检索代表结果集的3个数据数组。

  showElapsed("Connected,going to execute");
  cstmt.execute();
  Datum[] object_name=cstmt.getOraclePlsqlIndexTable(2);
  Datum[] object_type=cstmt.getOraclePlsqlIndexTable(3);
  Datum[] timestamp=cstmt.getOraclePlsqlIndexTable(4);

然后依次访问每个数组，以显示相应的结果集中从第一行到最后一行需要多长时间。

  showElapsed("First Row "+object_name.length);
  String data;
  int i;
  for(i = 0;i < object_name.length; i++){
   data=object_name[i].stringValue();
   data=object_type[i].stringValue();
   data=timestamp[i].stringValue();
  }
  showElapsed("Last Row "+i);  
}

}

在客户端执行程序时，收到如下提示：

Exception in thread "main" java.sql.SQLException: ORA-06513: PL/SQL: 主机语言数组的 PL/SQL 表索引超出范围
ORA-06512: 在 line 1

这是由于客户端提供了一个太小的数组尺寸，所以它接受一个错误而不是数据。使用这种方法，客户端需要知道行的最大数目以及每个列的最大宽度。

 

 

四、调用REF_CUR例程的Java客户端应用程序如下。从一个非常简单的计时例程开始，此例程将打出调用之间经历的时间（以毫秒计时）。

import java.sql.*;
import java.util.Date;
import oracle.jdbc.driver.*;
import oracle.sql.*;
public class refcur{
 static long start = new Date().getTime();
 public static void showElapsed(String msg)
 {
  long end=new Date().getTime();
  System.out.println(msg+ " " + (end - start) + " ms");
  start=end;
 }
}

每次调用该例程时，它都要打出最后一次调用它以来经历的时间，然后记住最后的这次新时间。下面来看看主例程。

从连接Oracle开始。

public static void main(String args[]) throws Exception
 {
  DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
  Connection conn = DriverManager.getConnection("jdbc:oracle:thin:hr/hr@192.168.1.100:1551:orcl");
  showElapsed("Connected,going to prepare");

下面是设置行的预取尺寸（数组尺寸）。它对JDBC默认为10，不过我一般用100

  ((OracleConnection)conn).setDefaultRowPrefetch(100);

现在就想PL/SQL表一样，准备和绑定语句，使用ref游标而不是按表索引的语法。

  OracleCallableStatement cstmt = (OracleCallableStatement)conn.prepareCall("begin demo_pkg.ref_cur(?,?); end;");
  showElapsed("Connected,going to bind");
  cstmt.setString(1, "SYS");
  cstmt.registerOutParameter(2, OracleTypes.CURSOR);

然后执行语句并取结果集。在达到之间每行的每列后再次打印取得第一行和最后一行之间的时间。

  showElapsed("Connected,going to execute");
  cstmt.execute();
  ResultSet rset=(ResultSet)cstmt.getObject(2);
  if (rset.next())
   showElapsed("First Row ");
  String data;
  int i;
  for(i = 0;rset.next(); i++){
   data=rset.getString(1);
   data=rset.getString(2);
   data=rset.getString(3);
  }
  showElapsed("Last Row "+i);  
}

}

 

下面列出执行这两个版本的结果汇总。

INDEXBY

Connected,going to prepare 397 ms
Connected,going to bind 169 ms
Connected,going to execute 2 ms
First Row 21023 227 ms
Last Row 21023 24 ms  总时间 251ms

 

REFCUR

Connected,going to prepare 366 ms
Connected,going to bind 152 ms
Connected,going to execute 2 ms
First Row  93 ms
Last Row 21022 411 ms 总时间504ms

 

 

现在进一步，增加一个表混合保存服务器中使用的PGA和UGA内存上的统计数据。

SQL> create table statistics (which varchar2(30),uga number,pga number);

 

在两个客户端应用程序的最后一条showElapsed后添加这条SQL语句

  Statement stmt = conn.createStatement();
  String insertsql="insert into statistics "+
  "select 'indexby', "+
  "max(decode(a.name,'session uga memory max',b.value,null)) uga, "+
  "max(decode(a.name,'session pga memory max',b.value,null)) pga "+
  "from v$statname a,v$mystat b "+
  "where a.name like '%memory%max' "+
  "and a.statistic#=b.statistic# ";
  stmt.execute(insertsql);

 

 

我们看到，与ref游标方法相比，INDEXBY方法要消耗大量的内存。

SQL> select which,trunc(avg(uga)),trunc(avg(pga)),count(*)
  2    from statistics
  3  group  by which;
 
WHICH                          TRUNC(AVG(UGA)) TRUNC(AVG(PGA))   COUNT(*)
------------------------------ --------------- --------------- ----------
indexby                                2978092         7659092          1
refcur                                 1216836         1695316          1

表明在1次运行后，INDEXBY方法消耗服务器上10.1MB内存。REF游标方法完成相同的事情只需2.8MB的内存

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/14359/viewspace-691762/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/14359/viewspace-691762/