package demo.oracle;

import java.sql.CallableStatement;

import java.sql.Connection;

import java.sql.ResultSet;

import oracle.jdbc.OracleCallableStatement;

import oracle.jdbc.OracleTypes;

import org.junit.Test;

import demo.utils.JDBCUtils;

publicclassTestCursor{

/*

CREATE OR REPLACE PACKAGE MYPACKAGE AS

type empcursor is ref cursor;

procedure queryEmplist(dno in number,emplist out empcursor);

END MYPACKAGE;

* */

@Test

publicvoid testCursor(){

String sql="{call MYPACKAGE.queryEmplist(?,?)}";

Connection conn =null;

CallableStatement call =null;

ResultSet rs =null;

try{

//获取数据库的连接

conn =JDBCUtils.getConnection();

//创建statement

call = conn.prepareCall(sql);

//对于in参数，赋值

call.setInt(1,10);

//对于out参数，申明

call.registerOutParameter(2,OracleTypes.CURSOR);

//执行调用

call.execute();

//取出该部门中所有员工的信息

rs =((OracleCallableStatement)call).getCursor(2);

while(rs.next()){

//取出该员工的员工号，姓名，薪水和职位

int empno = rs.getInt("empno");

String name =rs.getString("ename");

double salay = rs.getDouble("sal");

String job = rs.getString("empjob");

System.out.println(empno+"\t"+name+"\t"+salay+"\t"+job);

}

}catch(Exception e){

e.printStackTrace();

}finally{

JDBCUtils.release(conn, call, rs);

}

}

}