Java调用oracle存储过程输出自定义对象或二维表 博客分类： JDBC，存储过程数据库，oracle

CREATE TABLE SFZ_TEST_MANAGER_XG(
       yxgh VARCHAR2(100),
       ygxm VARCHAR2(100),
       position_name VARCHAR2(100)
);
CREATE TABLE SFZ_TEST_MANAGER_XG AS select T.CCUST_ID as yxgh,T.ACCS_NBR as ygxm,T.CCUST_NAME as position_name from HEMS_FILTER_CUST_LIST t where t.ccust_id is not null and t.accs_nbr is not null and t.ccust_name is not null;

DROP TABLE SFZ_TEST_MANAGER_XG;

select * from SFZ_TEST_MANAGER_XG;
select count(*) from SFZ_TEST_MANAGER_XG;

create or replace TYPE SFZ_MANAG_M_OBJ_TYPE AS OBJECT(
      yxgh VARCHAR2(100),
       ygxm VARCHAR2(100),
       position_name VARCHAR2(100));
      
DROP TYPE SFZ_MANAG_M_OBJ_TYPE;

select * from user_objects t where t.object_name not like '%SFZ_MANAG_M_OBJ_TYPE%' and t.object_type = 'TYPE';

CREATE OR REPLACE TYPE SFZ_MANAG_M_TAB_TYPE IS TABLE OF SFZ_MANAG_M_OBJ_TYPE;

DROP TYPE SFZ_MANAG_M_TAB_TYPE;

create or replace procedure proc_sfz_proc_test(user_yxgh IN VARCHAR2,v_table out SFZ_MANAG_M_TAB_TYPE)
is
  cursor cursor_cur_type is select * from SFZ_TEST_MANAGER_XG; --可以增加条件
  i NUMBER(8) :=1;
BEGIN
   v_table:=SFZ_MANAG_M_TAB_TYPE();
   for cursor_record in cursor_cur_type loop
        v_table.EXTEND;
        v_table(i) := NEW SFZ_MANAG_M_OBJ_TYPE(cursor_record.yxgh,cursor_record.ygxm,cursor_record.position_name);
        --v_table(i) := NEW SFZ_MANAG_M_OBJ_TYPE('123','124','125');
        i:=i+1;
   end loop;
end proc_sfz_proc_test;

DROP PROCEDURE proc_sfz_proc_test;

package com.zjhcsoft.test.utl;

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

import oracle.jdbc.OracleCallableStatement;
import oracle.jdbc.driver.OracleTypes;
import oracle.sql.ARRAY;
import oracle.sql.Datum;
import oracle.sql.STRUCT;

public class TestOracleProc {

	/**
	 * @param args
	 */
	public static void main(String[] args) {
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
		} catch (Exception e) {
			e.printStackTrace();
		}

		Connection conn = null;
		String DBurl = "jdbc:oracle:thin:@134.224.40.73:1521:orcl";
		try {
			conn = DriverManager.getConnection(DBurl, "hems", "hems");
			System.out.println("Getting Connection...");
			conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		try {
			conn = DriverManager.getConnection(DBurl, "hems", "hems");
			long start = System.currentTimeMillis();
			OracleCallableStatement  cs = (OracleCallableStatement)conn.prepareCall("{call proc_sfz_proc_test(?,?)}");
			cs.setString(1, "fruitking");
			cs.registerOutParameter(2, OracleTypes.ARRAY,"SFZ_MANAG_M_TAB_TYPE");
			cs.execute();
			long end = System.currentTimeMillis();
			System.out.println("this procedure consumes "+((end-start)/1000)+" excute time.");
			start = System.currentTimeMillis();
			ARRAY array = (ARRAY)cs.getArray(2);
			Datum[] datas = array.getOracleArray();
			if(datas.length>0){
				System.out.println("this procedure has "+datas.length+" data.");
				for (int i = 0; i < datas.length; i++){
					if(datas[i]!=null&&((STRUCT) datas[i])!=null){
					Datum[] personAttributes = ((STRUCT) datas[i]).getOracleAttributes();
					System.out.println("column"+(i+1)+":"+new String(personAttributes[0].getBytes())+",  "+new String(personAttributes[1].getBytes())+",  "+new String(personAttributes[2].getBytes()));
					}else{
						System.out.println("datas["+i+"] is null.");
					}
				}
			}else{
				System.out.println("this procedure is not result data...");
			}
			end = System.currentTimeMillis();
			System.out.println("show this procedure data consumes "+((end-start)/1000)+" excute time.");
			cs.close();
			conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

}