欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

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

程序员文章站 2024-03-22 19:04:52
...
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();
		}
	}

}