/********************************************************************************
包中存储过程及函数 (database Oracle 10G)
********************************************************************************/
----包声明
CREATE OR REPLACE PACKAGE pkg_test
AS
TYPE myrctype IS REF CURSOR;
PROCEDURE proc_findResult(u_cursor OUT myrctype, u_id NUMBER);
FUNCTION fun_findResult(u_id NUMBER) return myrctype;
END pkg_test;
----包主体的声明
CREATE OR REPLACE PACKAGE BODY pkg_test
AS
PROCEDURE proc_findResult(u_cursor OUT myrctype ,u_id NUMBER)
IS
sqlString VARCHAR2 (500);
BEGIN
IF u_id = 0 THEN
OPEN u_cursor FOR SELECT userId, userName, password, groupName FROM user_account ORDER BY userId;
ELSE
sqlString :='SELECT userId, userName, password, groupName FROM user_account where userId=:w_id';
OPEN u_cursor FOR sqlString USING u_id;
END IF;
END proc_findResult;
function fun_findResult(u_id NUMBER) RETURN myrctype
IS
u_cursor myrctype;
sqlString VARCHAR2 (500);
BEGIN
IF u_id = 0 THEN
OPEN u_cursor FOR SELECT userId, userName, password, groupName FROM user_account ORDER BY userId;
ELSE
sqlString :='SELECT userId, userName, password, groupName FROM user_account where userId=:w_id';
OPEN u_cursor FOR sqlString USING u_id;
END IF;
return u_cursor;
END fun_findResult;
END pkg_test;
/********************************************************************************
java 中 test class
********************************************************************************/
package org.taink.test;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import oracle.jdbc.OracleCallableStatement;
import oracle.jdbc.OracleTypes;
public class MyTest {
public static void main(String args[]) throws Exception {
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
Connection conn = DriverManager.getConnection(
"jdbc:oracle:thin:@192.168.1.100:1521:TAINK", "orca", "orca");
/**
* call procedure
*
*/
OracleCallableStatement oracleCallableStatement = (OracleCallableStatement) conn
.prepareCall("begin pkg_test.proc_findResult(?,?); end;");
//设参数
oracleCallableStatement.registerOutParameter(1, OracleTypes.CURSOR);
oracleCallableStatement.setInt(2, 0);
oracleCallableStatement.execute();
//获得输出对象,因为是第一个传入的,所以使用:getObject(1);
ResultSet resultSet = (ResultSet) oracleCallableStatement.getObject(1);
while (resultSet.next()) {
System.out.println(resultSet.getString("userId"));
System.out.println(resultSet.getString("userName"));
System.out.println(resultSet.getString("password"));
System.out.println(resultSet.getString("groupName"));
}
/**
* call function
*
*/
// OracleCallableStatement oracleCallableStatement = (OracleCallableStatement) conn
// .prepareCall("{?=call pkg_test.fun_findResult(?)}");
// //设参数
// oracleCallableStatement.registerOutParameter(1, OracleTypes.CURSOR);
// oracleCallableStatement.setInt(2, 0);
//
// oracleCallableStatement.execute();
// //获得输出对象,因为是第一个传入的,所以使用:getObject(1);
// ResultSet resultSet = (ResultSet) oracleCallableStatement.getCursor(1);
// while (resultSet()) {
// System.out.println(resultSet.getString(1));
// System.out.println(resultSet.getString(2));
// System.out.println(resultSet.getString(3));
// System.out.println(resultSet.getString(4));
// }
}
}