Oracle JavaResource传数组例子

一、创建对象

CREATE OR REPLACE TYPE emp_obj AS OBJECT
(
        empno NUMBER(4),
        ename VARCHAR2(10)
);
/
CREATE TYPE typ_emp_obj IS TABLE OF emp_obj;
/

二、Java程序：

create or replace and compile java source named arrayprotest as
import java.sql.*;
import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;
import oracle.jdbc.OracleResultSet;
import oracle.sql.STRUCT;
import oracle.sql.StructDescriptor;
import java.util.ArrayList;
import java.util.List;
public class ArrayPrpTest{
  /*public static ARRAY getArrays() throws Exception {
    System.out.println("--11---");
    String[] str = new String[5];
    for (int i = 0; i < str.length; i++) {
      str[i] = String.valueOf(i);
        }
        ArrayDescriptor varchar2Desc = ArrayDescriptor.createDescriptor(
                "TYP_CA_VARCHAR2", getConn());
        ARRAY array = new ARRAY(varchar2Desc, getConn(), str);
        return array;
    }*/
 
  public static ARRAY getArrays(int num) throws Exception {
    connection = getConn();
    System.out.println("--11---");
    /*String[] str = new String[5];
        for (int i = 0; i < str.length; i++) {
            str[i] = String.valueOf(i);
        }*/
        StructDescriptor empDescriptor = StructDescriptor.createDescriptor("EMP_OBJ", connection);
    List structs = new ArrayList();
    for (int i = 0; i < num; i++) {
            Object[] objs = new Object[2];
            objs[0] = "0000"+i;
            objs[1] = "Jack"+i;
      STRUCT struct = new STRUCT(empDescriptor, connection, objs);
      structs.add(struct);
    }
    ArrayDescriptor arrayDesc = ArrayDescriptor.createDescriptor(
        "TYP_EMP_OBJ", connection);
    ARRAY array = new ARRAY(arrayDesc, getConn(), structs.toArray());
    return array;
  }

  private static Connection getConn() throws Exception {
    if (connection == null) {
      String driver = "oracle.jdbc.driver.OracleDriver";
      String strUrl = "jdbc:oracle:thin:@localhost:1521:sgpmdb";
      Class.forName(driver);
      connection = DriverManager.getConnection(strUrl, "sgpm", "sgpm");
    }
    return connection;
  }

  public static String testString(){
    return "abc";
  }
  private static Connection connection;
}

三、创建函数

CREATE OR REPLACE FUNCTION fun_test(i_num IN NUMBER) RETURN typ_emp_obj AS
        LANGUAGE JAVA NAME 'ArrayPrpTest.getArrays(int) return oracle.sql.ARRAY';

四、测试过程：

DECLARE
    typ_test typ_emp_obj;
    i_num    number(12) := 5;
BEGIN
    typ_test := fun_test(i_num);
    FOR i IN 1 .. typ_test.count
    LOOP
        dbms_output.put_line('EMP_NO:' || typ_test(i).empno);
        dbms_output.put_line('EMP_NAME:' || typ_test(i).ename);
    END LOOP;
END;