oracle返回临时表,Java调用oracle存储过程透过游标返回临时表

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_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();

}

}

}

我的异常网推荐解决方案：oracle存储过程,http://www.myexceptions.net/oracle-develop/177537.html