从PL/SQL存储函数返回数组

完成此方法指南后，您应该能够：

• 在 Oracle 数据库中创建 VARRAY

• 使用 oracle.sql.ARRAY 类

• 从 Java 访问 VARRAY

简介

本文档演示如何从 PL/SQL 函数返回数组并从 java 应用程序访问它。数组是一组有序的数据元素。 VARRAY 是大小可变的数组。它具有数据元素的排列集，并且所有元素属于同一数据类型。每个元素都具有索引，它是与元素在 VARRAY 中的位置相对应的一个数字。 VARRAY 中元素的数量是 VARRAY 的“大小”。在声明 VARRAY 类型时，必须指定其最大值。

在此方法指南中，PL/SQL 存储函数从 SCOTT 模式的 EMP 表中取出所有雇员的姓名，以这些姓名创建一个数组并将其返回。从 Java 应用程序调用此 PL/SQL 存储函数，向用户显示雇员的姓名。

软件需求

• Oracle9i Database version 9.0.1 或更新版本。您可从 Oracle 技术网下载 Oracle9i 数据库。

• JDK1.2.x 或更高版本。可从此处下载。

• Oracle9i JDBC 驱动程序。JDBC 驱动程序可从 ORACLE_HOME/jdbc/lib 处获得。也可从此处下载。

在数据库中创建一个 SQLVARRAY 类型，在本例中，它是 VARCHAR2 类型。 作为 scott/tiger 用户连接到数据库，并在 SQL 提示符处执行以下命令。

SQL>CREATE OR REPLACE TYPE EMPARRAY is VARRAY(20) OF VARCHAR2(30)
SQL>/

然后创建下面的函数，它返回一个 VARRAY。

CREATE OR REPLACE FUNCTION getEmpArray RETURN EMPARRAY
AS
  l_data EmpArray := EmpArray();
  CURSOR c_emp IS SELECT ename FROM EMP;
  BEGIN
    FOR emp_rec IN c_emp LOOP
      l_data.extend;
      l_data(l_data.count) := emp_rec.ename;
    END LOOP;
    RETURN l_data;
  END;

 

在数据库中创建函数后，可以从 java 应用程序调用它并在应用程序中获得数组数据。下面给出代码段，从 Java 应用程序执行 PL/SQL 存储函数。单击此处查看完整的应用程序源代码。

public static void main( ) {
.........
......... 
  OracleCallableStatement stmt =(OracleCallableStatement)conn.prepareCall
                ( "begin ?:= getEMpArray; end;" );

    // The name we use below, EMPARRAY, has to match the name of the 
    // type defined in the PL/SQL Stored Function
    stmt.registerOutParameter( 1, OracleTypes.ARRAY,"EMPARRAY" );
    stmt.executeUpdate();

    // Get the ARRAY object and print the meta data assosiated with it
    ARRAY simpleArray = stmt.getARRAY(1);

    System.out.println("Array is of type " +  simpleArray.getSQLTypeName());

    System.out.println("Array element is of type code "+simpleArray.getBaseType());

    System.out.println("Array is of length " + simpleArray.length());

    // Print the contents of the array
    String[] values = (String[])simpleArray.getArray();

    for( int i = 0; i < values.length; i++ )
      System.out.println( "row " + i + " = '" + values[i] +"'" );

...........
...........

 

在上面的代码段中，可以看到 OracleCallableSatatement 用于调用 PL/SQL 存储函数。在执行 PL/SQL 存储函数前，将返回的数据类型注册为 OracleTypes.ARRAY，并且指定在数据库中定义的类型名称 (EMPARRAY)。然后执行 PL/SQL 存储函数并获得 oracle.sql.ARRAY 形式的返回值。 oracle.sql.ARRAY 类拥有的方法可以获得关于数组的详细信息，如数组类型、数组长度等。使用 oracle.sql.ARRAY 的 getArray() 方法获得数组的内容并将内容打印出来。

总结

本文档说明了如何创建 VARRAY 并从 PL/SQL 存储函数返回数组，以及如何从 java 应用程序访问它

 

参考：http://www.oracle.com/technology/global/cn/sample_code/tech/java/codesnippet/jdbc/varray/index.html