package com.jckb.procedure;

import java.sql.CallableStatement;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import oracle.jdbc.OracleCallableStatement;

public class MainTest3  {

	/**

	 * 使用jdbc调用带游标参数的存储过程

		create or replace PACKAGE PACKAGE1 AS

		  --定义游标类型

		  type empCursor is ref cursor;

		  --定义存储过程使用游标

		  procedure findEmpInfoByDept(mydeptno in number,empInfo out empCursor);

		END PACKAGE1;

		-------------------

		create or replace PACKAGE BODY PACKAGE1 AS

		  procedure findEmpInfoByDept(mydeptno in number,empInfo out empCursor) AS

		  BEGIN

		    -- 根据指定的部门编号员工信息赋值隔日输出型变量游标

		   open empInfo for select * from emp where deptno=mydeptno;

		    --此处不能关闭游标

		  END findEmpInfoByDept;

		END PACKAGE1;

	 * */

	public static void main(String[] args) throws Exception{

		Class.forName("oracle.jdbc.OracleDriver");

		Connection connection = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl", "scott", "tiger");

		CallableStatement callableStatement = connection.prepareCall("{call PACKAGE1.findEmpInfoByDept(?,?)}");

		callableStatement.setInt(1, 10);

		callableStatement.registerOutParameter(2, oracle.jdbc.OracleTypes.CURSOR);

		callableStatement.execute();

		//获取值

		ResultSet resultSet = ((OracleCallableStatement)callableStatement).getCursor(2);

		while(resultSet.next()){

			int empno =	resultSet.getInt(1);

			String ename = resultSet.getString(2);

			String job = resultSet.getString(3);

			System.out.println("编号：" + empno + " 用户：" + ename + " 工作：" + job);

		}

		resultSet.close();

		callableStatement.close();

		connection.close();

	}

}