详解Oracle在out参数中访问光标

CREATE OR REPLACE

PACKAGE MYPACKAGE AS

  type empcursor isref 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 forselect* from emp where deptno = dno;

END queryEmplist;

END MYPACKAGE;

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);

}

}

}

7782  CLARK  6450.0  MANAGER

7839  KING  10100.0  PRESIDENT

7934  MILLER  3300.0  CLERK