Oracle对象-包头，包体返回游标 多参数值

前言

    在编写存储过程或存储函数的过程中，你可能会发现，当需要的返回值过多，就需要更多的变量来接受它。是否可能用一个光标来接受返回值？答案当然是可以的。在Oracle数据库中，可以定义包头和包体。包头相当于声明，而包体则是逻辑实现。

实例

包头声明如下：

CREATE OR REPLACE PACKAGE MYPACKAGE AS 


  type empcursor is ref cursor;
  procedure queryEmpList(dno in number,empList out empcursor);--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;

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("job");
				System.out.println(name+"\t"+sal+"\t"+job);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			JDBCUtils.release(conn, call, rs);
		}		
		
	}
}

package demo;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class JDBCUtils {

	private static String driver = "oracle.jdbc.OracleDriver";
	private static String url = "jdbc:oracle:thin:@192.168.137.129:1521/orcl";
	private static String user = "scott";
	private static String password = "tiger";
	
	static{
		//注册驱动
		try {
			Class.forName(driver);
			//DriverManager.registerDriver(driver);
		} catch (ClassNotFoundException e) {
			throw new ExceptionInInitializerError(e);
		}
	}

	public static Connection getConnection(){
		try {
			return DriverManager.getConnection(url, user, password);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	/*
	 * 运行Java程序：
	 * java -Xms100M -Xmx200M HelloWorld
	 * 
	 * 技术方向：
	 * 1. 性能优化
	 * 2. 故障诊断:死锁 ThreadDump
	 */
	public static void release(Connection conn,Statement st,ResultSet rs){
		if(rs != null){
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}finally{
				rs = null;//-----> Java GC
			}
		}
		if(st != null){
			try {
				st.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}finally{
				st = null;
			}
		}
		if(conn != null){
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}finally{
				conn = null;
			}
		}		
	}
}