存储过程的输出参数为游标，PL/SQL中如何调用 Java代码如何调用

为什么80%的码农都做不了架构师？>>>   

--查询某个部门中所有员工的所有信息

--包头
CREATE OR REPLACE PACKAGE MYPACKAGE AS
       type empcursor is ref cursor;
       PROCEDURE queryEmpList(dno IN NUMBER ,empList OUT empcursor);
END MYPACKAGE;
--包体
CREATE OR REPLACE PACKAGE BODY MYPACKAGE AS
       PROCEDURE queryEmpList(dno IN NUMBER , empList OUT empcursor) AS
       BEGIN
           OPEN empList FOR SELECT * FROM emp WHERE deptno=dno;
       END queryEmpList;
END MYPACKAGE;  

--PL/SQL调用

declare
  type type_cur_data is ref cursor return emp%rowtype;
  v_cur_data type_cur_data;
  v_tab_data emp%rowtype;
begin
  MYPACKAGE.queryEmpList(10, v_cur_data);
  while (1=1) loop
    fetch v_cur_data into v_tab_data;
    exit when v_cur_data%notfound;
    dbms_output.put_line(v_tab_data.deptno || v_tab_data.ename);
  end loop;
end;

/*
    CREATE OR REPLACE PACKAGE MYPACKAGE AS
        type empcursor is ref cursor;
        procedure queryEmpList(dno in number,empList out empcursor);
    END MYPACKAGE;
*/

--java调用

@Test
public void testCursor(){
    String sql = "{call MYPACKAGE.queryEmpList(?,?)}";
    Connection conn = null;
    CallableStatement call = null;
    ResultSet rs = null;
    try {
        conn = JDBCUtils.getConnection();
        call = conn.prepareCall(sql);
        // 对于 in  参数，赋值
        call.setInt(1, 20);
        // 对于 out  参数，申明
        call.registerOutParameter(2, OracleTypes.CURSOR);
        // 执行
        call.execute();
        // 取出结果
        rs = ((OracleCallableStatement)call).getCursor(2);
        while(rs.next()){
        String name = rs.getString("ename");
        double sal = rs.getDouble("sal");
        String job = rs.getString("empjob");
        System.out.println(name+"\t"+sal+"\t"+job);
    }
} catch (Exception e) {
e.printStackTrace();
}finally{
JDBCUtils.release(conn, call, rs);
}
}
}

 

转载于:https://my.oschina.net/hfzj/blog/707882