oracle java resource_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;