创建带游标的存储过程 以及在JDBC中的调用

//先建立一个包

create or replace package pkg_emp
as
       TYPE cur IS REF CURSOR;
//动态SQL语句 要用到ref游标
       procedure up_emp(var_cur out cur);
end pkg_emp;

create or replace package body pkg_emp
as
       procedure up_emp(var_cur out cur)
       as
          var_sql varchar2(100) := 'select * from emp';
       begin
           open var_cur for var_sql;
       exception
          when others then dbms_output.put_line('ERROR!');
       end up_emp;

end pkg_emp;


//调用过程

package com.icss.test;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;

import oracle.jdbc.internal.OracleTypes;

public class ProcedureDao {

	public void select() {

		DbUtil db = new DbUtil();
		try {
			Connection conn = db.conn();
			CallableStatement proc = conn
					.prepareCall("{call pkg_emp.up_emp(?)}");
//带输出参数的
			proc.registerOutParameter(1, OracleTypes.CURSOR);

			proc.execute();
			ResultSet rs = (ResultSet) proc.getObject(1);
			while (rs.next()) {
				System.out.println(rs.getString(2));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
		}
	}

	public static void main(String[] args) {
		ProcedureDao dao = new ProcedureDao();
		dao.select();
	}
}