java中调用oracle array

来源：互联网发布：交通仿真软件市场分析编辑：程序博客网时间：2024/06/05 04:30

在数据库scott用户下创建集合类型并创建存储函数返回集合类型的值:

create or replace type emparray is table of varchar2(32);

create or replace function getEmpArray return emparray
AS
  v_data emparray := emparray();
  cursor emp_lists is select ename from emp;
  begin
open emp_lists;
fetch c_table bulk collect into  v_data;
close emp_lists;
return v_data;
  end;
/

java 应用程序调用它并在应用程序中获得数组数据。oracle官方网站的例子在:
http://www.oracle.com/technology ... rayAccess.java.html

package com.greatsky;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

import oracle.jdbc.OracleCallableStatement;
import oracle.jdbc.OracleTypes;
import oracle.sql.ARRAY;

public class OracleArray {

   final static String sConnStr = "jdbc:oracle:thin:@//192.168.0.104:1521/sdb.greatsky.com";

      /**
      * @param args
      */
      public static void main(String[] args) {

            Connection conn = null;
            OracleCallableStatement stmt = null;
            try {
                     Class.forName("oracle.jdbc.OracleDriver");

                     conn = DriverManager.getConnection(sConnStr, "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 the PL/SQL Stored Function
                     stmt.registerOutParameter(1, OracleTypes.ARRAY, "EMPARRAY");
                     stmt.executeUpdate();

                     // oracle.sql.ARRAY;
                     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
                     String[] values = (String[]) simpleArray.getArray();

                     for (int i = 0; i < values.length; i++)
                              System.out.println("row " + i + " = '" + values + "'");
            } catch (Exception e) {

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

      }

}