Java调用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_objas 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)isBEGIN 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();}}}