Java调用ORACLE存储过程，对返回的结果集处理（字符，游标，数组（表变量））

数据库ORACLE 10g：

1.创建Type

CREATE OR REPLACE TYPE P_TYPE_TEST AS OBJECT(
  P_ID       VARCHAR2(50),
  P_NAME     VARCHAR2(50),
  P_DES      VARCHAR2(1000)
)

CREATE OR REPLACE TYPE P_TYPE_TEST_LIST AS TABLE OF P_TYPE_TEST

 2.创建存储过程

CREATE OR REPLACE PROCEDURE P_TEST
(
I_PARAM IN VARCHAR2,
O_STR OUT VARCHAR2,
O_CURSOR OUT SYS_REFCURSOR,
O_TYPE OUT P_TYPE_TEST_LIST
)
AS
BEGIN
     SELECT COUNT(*) INTO O_STR FROM STA_REQ_DETAIL;
		 
     OPEN O_CURSOR FOR
     SELECT ROWNUM ,A.* FROM STA_REQ_DETAIL A WHERE ROWNUM<=10;
		 
		 O_TYPE := P_TYPE_TEST_LIST();
		 FOR O IN (SELECT M.INSTR_REF_NO,M.BEARER_REG_FLAG,M.INSTR_GROUP FROM STA_APP_INSTR_MAST M WHERE ROWNUM <=10) LOOP
			 DBMS_OUTPUT.PUT_LINE(O.INSTR_REF_NO);
			 O_TYPE.EXTEND;
        O_TYPE(O_TYPE.COUNT) := P_TYPE_TEST
        (
          O.INSTR_REF_NO,
          O.BEARER_REG_FLAG,
          O.INSTR_GROUP
        );
		 END LOOP;
END;

 3.Java 代码调用，对字符，游标，Array的处理

package com;

import java.sql.Array;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import oracle.jdbc.driver.OracleTypes;

public class Test {
	private Connection conn;
	private Statement statement;
	public static final String ORACLE_DRIVER="oracle.jdbc.driver.OracleDriver";
	public static final String ORACLE_URL="jdbc:oracle:thin:@127.0.0.1:1521:testdb";
	public static final String ORACLE_USERNAME="username";
	public static final String ORACLE_PASSWORD="password";
	public Test(){
		try{
			Class.forName(ORACLE_DRIVER);
			conn =DriverManager.getConnection(ORACLE_URL,ORACLE_USERNAME,ORACLE_PASSWORD);
			conn.setAutoCommit(false);
			statement = conn.createStatement();
		}catch (Exception e) {
			System.err.println("ERROR-ConnectDB:");
			e.printStackTrace();
		}
	}
	public void close(){
		try{
			if(statement!=null){
				statement.close();
			}
			if(conn!=null){
				conn.close();
			}
		}catch(SQLException e){
			System.err.println("ERROR-close:");
			e.printStackTrace();
		}
	}
	
	public void callPrepare(){
		CallableStatement callstmt = null;
		try {
			callstmt = conn.prepareCall("{ call P_TEST(?, ?, ?, ?) }");	
			callstmt.setString(1, "test");
			callstmt.registerOutParameter(2, OracleTypes.VARCHAR);
			callstmt.registerOutParameter(3, OracleTypes.CURSOR);
			callstmt.registerOutParameter(4, OracleTypes.ARRAY,"P_TYPE_TEST_LIST");
			callstmt.execute();	
			//获取字符
			String c = callstmt.getString(2);
			System.out.println(c);
			//获取游标
			ResultSet res = (ResultSet)callstmt.getObject(3);
			while(res.next()){
				System.err.println(res.getString(2)+"  "+res.getString(3));
			}
			//获取数组
			Array ary = callstmt.getArray(4);
			Object[] os = (Object[])ary.getArray();
			for(int i=0;i<os.length;i++){
				oracle.sql.STRUCT struct = (oracle.sql.STRUCT)os[i];
				Object obj[] = struct.getAttributes();
		        for(Object attr : obj){   
		        	System.out.print(attr+"   ");
		        }
		        System.out.println();
			}
		} catch (Exception e) {
			System.err.println("ERROR-callPrepare:");
			e.printStackTrace();
		}
	}
	
	public static void main(String[] args) {
		Test test = new Test();
		try{
			System.err.println("**********************");
			test.callPrepare();
			test.close();
			System.err.println("**********************");
		}catch (Exception e) {
			e.printStackTrace();
		}
		
		
	}
}

 

ORACLE jdbc驱动包