CREATE OR REPLACE
FUNCTION MYFUNCSET(dept_id varchar2) RETURN SYS_REFCURSOR
is
ref_cursor SYS_REFCURSOR;
BEGIN
OPEN ref_cursor FOR
select HR.EMPLOYEES.EMPLOYEE_ID, HR.EMPLOYEES.FIRST_NAME from HR.EMPLOYEES where HR.EMPLOYEES.DEPARTMENT_ID = dept_id ;
RETURN (ref_cursor);
END MYFUNCSET;
sqlplus 测试
select HR.MyFuncSet(90) from dual;
java 测试
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import oracle.jdbc.OracleCallableStatement;
import oracle.jdbc.OracleTypes;
public class MyFuncSet {
/**
* @param args
* @throws Exception
public static void main(String[] args) throws Exception {
// TODO Auto-generated method stub
Connection conn = null;
CallableStatement stmt = null;
ResultSet rs = null;
String DBDriver = "oracle.jdbc.driver.OracleDriver";
String ConnStr = "jdbc:oracle:thin:@localhost:1521:ORCL";
try {
conn = DriverManager.getConnection(ConnStr, "system", "firewings");
/**
*/
// stmt = conn.prepareCall("{ call HR.MyProcSet(?,?) }");
stmt = conn.prepareCall("{? = call HR.MyFuncSet(?)}");
stmt.setString(2, "90");
stmt.registerOutParameter(1, OracleTypes.CURSOR);
stmt.execute();
rs = (ResultSet) stmt.getObject(1);
while (rs.next()) {
System.out.println(rs.getString(1) + " " + rs.getString(2));
} catch (Exception e) {
e.printStackTrace();
} finally {
rs.close();
stmt.close();
conn.close();
}
}
}
