Java调用oracle存储过程通过游标返回临时表 博客分类： JDBC，存储过程数据库，oracle

CREATE TABLE SFZ_TEST_MANAGER_XG(
       yxgh VARCHAR2(100),
       ygxm VARCHAR2(100),
       position_name VARCHAR2(100)
);

insert into SFZ_TEST_MANAGER_XG values ('abc1','bcd1','cde1');
insert into SFZ_TEST_MANAGER_XG values ('abc2','bcd2','cde2');
insert into SFZ_TEST_MANAGER_XG values ('abc3','bcd3','cde3');
insert into SFZ_TEST_MANAGER_XG values ('abc4','bcd4','cde4');

DROP TABLE SFZ_TEST_MANAGER_XG;

CREATE GLOBAL TEMPORARY TABLE SFZ_TEMP_MANAGER_XG(
       yxgh VARCHAR2(100),
       ygxm VARCHAR2(100),
       position_name VARCHAR2(100)
)ON COMMIT DELETE ROWS;

DROP TABLE SFZ_TEMP_MANAGER_XG;

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

create or replace package sfz_obj
as
 type sfz_cursor is ref cursor;
 procedure proc_sfz_proc_test(user_yxgh IN VARCHAR2,v_table out sfz_cursor);
end sfz_obj;

DROP package sfz_obj;

create or replace package body sfz_obj as 
procedure proc_sfz_proc_test(user_yxgh IN VARCHAR2,v_table out sfz_cursor)
is
BEGIN
   INSERT INTO SFZ_TEMP_MANAGER_XG(YXGH,YGXM,POSITION_NAME) SELECT YXGH,YGXM,POSITION_NAME FROM SFZ_TEST_MANAGER_XG;
   --insert into SFZ_TEMP_MANAGER_XG values ('abc','bcd','cde');
   --insert into SFZ_TEMP_MANAGER_XG values ('abc','bcd','cde');
   --insert into SFZ_TEMP_MANAGER_XG values ('abc','bcd','cde');
   --insert into SFZ_TEMP_MANAGER_XG values ('abc','bcd','cde');
   open v_table for select * from SFZ_TEMP_MANAGER_XG;
end proc_sfz_proc_test;
end sfz_obj;

select * from product_component_version;

package com.zjhcsoft.test.utl;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;

import oracle.jdbc.OracleCallableStatement;
import oracle.jdbc.driver.OracleTypes;

public class TestOracleProc3 {

	/**
	 * @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.98.8.168:1521:ora817";
		try {
			conn = DriverManager.getConnection(DBurl, "sfz2", "sfz2_123");
			System.out.println("Getting Connection...");
			conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		try {
			conn = DriverManager.getConnection(DBurl, "sfz2", "sfz2_123");
			long start = System.currentTimeMillis();
			//最关键一步
			conn.setAutoCommit(false);
			OracleCallableStatement cstmt = (OracleCallableStatement)conn.prepareCall("{call sfz_obj.proc_sfz_proc_test(?,?)}");
			cstmt.setString(1, "");
			cstmt.registerOutParameter(2,OracleTypes.CURSOR);
			cstmt.execute();
			long end = System.currentTimeMillis();
			System.out.println("this procedure consumes "+((end-start)/1000)+" excute time.");
			start = System.currentTimeMillis();
			int i=0;
			ResultSet rs = (ResultSet)cstmt.getObject(2);
			   while (rs.next()) {
				   System.out.println("column"+(i+1)+":"+rs.getString(1)+",  "+rs.getString(2)+",  "+rs.getString(3));
				   i++;
			   } 
			   System.out.println("this procedure has "+(i-1)+" data.");
			end = System.currentTimeMillis();
			System.out.println("show this procedure data consumes "+((end-start)/1000)+" excute time.");
			conn.commit();
			cstmt.close();
			conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

}