java 調(diào)用 oracle 存儲過程 返回 結(jié)果集 ,Re

1、Java調(diào)用oracle 存儲過程返回結(jié)果集(record首先創(chuàng)建2個type類型, ,我可以把record記錄集里的內(nèi)容賦給type對象類型,在給游標(biāo)返回!假如環(huán)境是在scott/tiger用戶下創(chuàng)建type定義你要返回的字段!drop type myScalarType;drop type myTableType;create or replace type myScalarType as object (rno number(18,rname number(18,rsal number(18;create or replace type myTableType as table of m

2、yScalarType;創(chuàng)建PACKAGECREATE OR REPLACE PACKAGE TESTPACKAGE AStype type_cursor is ref cursor;end TESTPACKAGE;創(chuàng)建PROCEDURECREATE OR REPLACE PROCEDURE test_items(MyTable outTESTPACKAGE.type_cursorISBEGINDeclareType RecType Is Record(rno number(18,rname number(18,rsal number(18;Type TabType Is Table Of R

3、ecType Index By Binary_Integer;MyTab TabType;obj_type_tab MYTABLETYPE := MYTABLETYPE(;vN Number;Begin-填充你要組裝的字段vN := 1;MyTab(vN.rno := '00000'MyTab(vN.rname := '11111'MyTab(vN.rsal := '22222'vN := vN + 1;MyTab(vN.rno := '33333'MyTab(vN.rname := '44444'MyTab(vN

4、.rsal := '55555'vN := MyTab.First;For varR In vN.MyTab.countLoopobj_type_tab.EXTEND;obj_type_tab(obj_type_tab.LAST := myscalartype(MyTab(vN.rno, MyTab(vN.rname,MyTab(vN.rsal;DBMS_OUTPUT.PUT_LINE(vN |'-'|MyTab(vN.rno|'-'|MyTab(vN.rname|'-'|MyTab(vN.rsal;vN := MyTab.Nex

5、t(vN;End Loop; -*/open MyTable for select rno ,rname,rsal from table(cast(obj_type_tab AS MYTABLETYPE;End;dbms_output.PUT_LINE('-dayi-'END test_items;Java 代碼package test.samo.owner;import java.sql.*;import java.sql.ResultSet;import oracle.jdbc.driver.*;public class TestProcedureTHREE /* para

6、m args*/public static void main(String args String driver="oracle.jdbc.driver.OracleDriver"String strUrl="jdbc:oracle:thin:192.168.1.1:1521:orcl"Statement stmt=null;ResultSet rs=null;Connection conn=null;tryClass.forName(driver;conn = DriverManager.getConnection(strUrl, "adm

7、in", "123456"CallableStatement proc = null;proc = conn.prepareCall(" call test_items(? "proc.registerOutParameter(1,/*oracle.jdbc.*/OracleTypes.CURSOR;proc.execute(;rs = (ResultSetproc.getObject(1;System.out.println("輸出結(jié)果:"while(rs.next(System.out.println(" " + rs.getString(1 + ""+rs.getString(2+" "+rs.getString(3;catch(SQLException ex2ex2.printStackTrace(;catch(Exception ex2ex2.printStackTrace(;finallytryif(rs != nullrs.