ORACLE中使用VARRAY解决字符串数组问题

如何从 PL/SQL 存储函数中返回数组类型

 

思路：在 Oracle 数据库中创建 VARRAY ，在Java Application中使用 oracle.sql.ARRAY 类 访问 VARRAY

 

VARRAY 是大小可变的数组。它具有数据元素的排列集，并且所有元素属于同一数据类型。每个元素都具有索引， VARRAY 中元素的数量是 VARRAY 的“大小”。在声明 VARRAY 类型时，必须指定其最大值。

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

 

打开PL/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 应用程序代码：

 

import java.sql.Connection;
import java.sql.DriverManager;
import oracle.jdbc.OracleCallableStatement;

import oracle.jdbc.OracleTypes;
import oracle.sql.ARRAY;
import java.sql.SQLException;

public class SPArrayAccess {
  public static void main(String[] args){

    Connection conn = null;
    OracleCallableStatement stmt = null;
    try {
      DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());

      conn = DriverManager.getConnection(
        "jdbc:oracle:thin:@insn104a:1522:ora9idb", "scott","tiger");


      stmt =(OracleCallableStatement)conn.prepareCall
                  ( "begin ? := getEMpArray; end;" );

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


      //Get the ARRAY object and print some meta data about 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] +"'" );

    } catch (SQLException se) {
      System.out.println(se.toString());
    } catch (Exception e) {

      System.out.println(e.toString());
    } finally {
      try {
        stmt.close();
        conn.close();
      } catch (SQLException se) {
        System.out.println(se.toString());
      }
    }
  }
}

 

需要JDBC 驱动，Version 9i以上就可以了。

OracleCallableSatatement 用于调用 PL/SQL 存储函数。在执行 PL/SQL 存储函数前，将返回的数据类型注册为 OracleTypes.ARRAY， 并且指定了在数据库中定义的数据类型名称 (EMPARRAY )。执行 PL/SQL 存储函数后获得 oracle.sql.ARRAY 类型的返回值。 oracle.sql.ARRAY 类拥有的方法可以获得一系列关于数组的详细信息，如数组类型TYPE、数组长度LENGTH等。这里我们使用 oracle.sql.ARRAY 的 getArray() 方法获得数组并将其打印出来。