Oracle对像和类型

Oracle创建一个对象:

CREATE TYPE Test_Type AS OBJECT ( --对象名
      gid NUMBER(10),             --对像字段.
      gname VARCHAR2(20)
);

创建对象数组:

CREATE OR REPLACE TYPE test_TABLE AS TABLE OF Test_Type;	--test_TABLE为:你的数组名

testArray test_TABLE := test_TABLE();         --初始化. 等于New.

testArray.extend(5);      --分配五个空间.

testArray(1) := Test_Type(1, '小天__');       --赋值时,每个字段必须有值.

--便利Table
FOR i IN 1..testArray.COUNT LOOP
   dbms_output.put_line(testArray(i).gid);
END LOOP;

完整过程:

CREATE TYPE TEST_TYPE AS OBJECT( --创建对象. 
  GID   NUMBER(10),
  GNAME VARCHAR2(20)
);

--建立数组
CREATE OR REPLACE TYPE TESTTABLE AS TABLE OF TEST_TYPE; 

--过程使用.
CREATE OR REPLACE PROCEDURE 

  TEST_TEST IS TESTARRAY TESTTABLE := TESTTABLE();
  
BEGIN 
   TESTARRAY.EXTEND(5);
   TESTARRAY(1) := TEST_TYPE(1, 'xxxx');
    FOR i IN 1 .. TESTARRAY.COUNT LOOP
       DBMS_OUTPUT.PUT_LINE(TESTARRAY(I).GID);
    END LOOP;
END;

JAVA里通过JDBC传参数,传入数组:

	public void call(){
		Connection connection = null;
		OracleCallableStatement statement = null;
		int[] dataArray = new int[1];
		dataArray[0] = 1;
		try {
//			connection = DataBase.getConnection();	//连接池获得的连接. 在这里不可以用.
			Class.forName("oracle.jdbc.driver.OracleDriver");	//只能自己新建一个连接.
   		    connection = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:orcl", "oracleUserName","orclePassword");
			
			//test_TABLE :为Oracle对像数组
			ArrayDescriptor arrayDesc = ArrayDescriptor.createDescriptor("test_TABLE ",connection); 
			ARRAY array_data = new ARRAY(arrayDesc, connection, dataArray);
			statement = (OracleCallableStatement) connection.prepareCall("call GET_USER_NAME(?)");
			statement.setArray(1, array_data);
			statement.execute();
		} catch (SQLException e) {
			Global.log.error(e);
		}finally{
			DataBase.close(connection, statement);
		}
	}