数据库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();
}
}
}