Oracle 存储过程返回数据集（游标）

琪华9250

　　声明：本文整理自网络，加上个人实际实验结果
　　
　　Oracle存储过程返回数据集，其实就是返回游标，这里需要了解这样几个概念，游标、存储过程的out参数、引用游标类型、Oracle的程序包，这里只说明具体怎么做，如果想了解以上概念请自行在网上搜索或关注我的博客。
　　首先说一下思路：
　　定义一个包（这相当于一个容器）（也可以理解为Java中的类）
　　包中包含一个存储过程来返回一个游标引用
　　包中包含一个自定义类型 游标引用（这个类型也可以应用到其它地方，这在调用存储过程时用到）
　　定义包分两部分：包头、包体
　　包头部分定义包中应该包含哪些内容（方法，存储过程等只做声明不做实现）（相当于Java中的接口）
　　包头部分定义：

create or replace package pkg_alen
as
type cursorRef is ref cursor; --定义游标引用类型
procedure query(u_id number,cursor_ref out cursorRef); --定义存储过程声明
end pkg_alen;
　　包体部分定义包头中的方法，存储过程等的实现（相当于Java中实现接口的类），注意必须实现包头中声明的所有方法和存储过程。
　　包体部分定义：

create or replace package body pkg_alen --注意这里的包名要和定义包头的包名一致
as
procedure query(u_id number,cursor_ref out cursorRef) --这里的存储过程要和包头中的一致参数列表中的参数名也要一致
is
begin
if u_id=0 then --如果传入的id为0则查询所有数据
open cursor_ref for select id,ck_name from dm_pac_ck;
else   --如果传入的id为非0则查询指定id的数据
open cursor_ref for select id,ck_name from dm_pac_ck where id=u_id; --根据查询打开游标
end if;
end query;
end pkg_alen;
　　Ok，到些返回数据集的存储过程已经编写完成，这里需要注意的是：1、包头定义部分要先执行然后执行包体部分；2、cursorRef 类型为自定义类型 其原型为 ref coursor。
　　网上的资料很多都到此为止，但是有好多新手（也包括我在内）并不知道怎么调用，下面我就来和大家说一下怎么在SQL中调用（亲测可用）和怎么使用Java调用（未亲测）
　　使用SQL调用上面定义的存储过程，这里需要注意了，调用存储过程的参数和参数类型（这个困扰了我好长时间），第一个参数没什么可说的就一数字型，主要是第二个参数，这个参数的类型是pkg_alen.cursorRef，没错就是这个自定义的类型（之前我尝试了 ref cursor 和定义一个新的ref cursor类型但是都报错）
　　调用部分内容：

set serveroutput on;
declare
return_cursor pkg_alen.cursorRef; --定义接收返回数据集的变量
v_id number(4);   --定义接收id的变量
uname varchar2(50);  --定义接收name的变量
begin
pkg_alen.query(0,return_cursor);  --调用包中的存储过程返回游标引用
loop fetch return_cursor into v_id,uname; --循环获取游标中每一行的数据
exit when return_cursor%notfound;          --游标结束时退出
dbms_output.put_line('id:'||v_id||' name:'||uname); --将获取到的值打印出来
end loop;    --循环结束
close return_cursor;  --关闭游标
end;
　　这里注意游标我们并没有打开只有关闭，因为游标的打开是在存储过程中完成的。
　　使用Java调用上面定义的存储过程：

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import oracle.jdbc.OracleTypes;
publicclass Dao {
String driver="oracle.jdbc.driver.OracleDriver";
String url="jdbc:oracle:thin:@127.0.0.1:1521:orcl";
Connection conn=null;
CallableStatement cs=null;
ResultSet rs;
/**
*获取数据连接的方法
*应该首先调用些方法来获得数据库连接
*/
publicvoid getConn(){
try {
Class.forName(driver);
conn = DriverManager.getConnection(url, "scott", "tiger");
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
}
/**
*调用存储过程返回数据集
*/
publicvoid callProcForResult(){
try {
cs = conn.prepareCall("{call pkg_alen.query(?)}");
cs.registerOutParameter(1, OracleTypes.CURSOR);
cs.execute();
ResultSet rs = (ResultSet)cs.getObject(1);
while(rs!=null&& rs.next()){
System.out.println(new StringBuilder("ID:").append(rs.getInt(1)).append("\t Name:").append(rs.getString(2)).toString());
}
} catch (SQLException e) {
e.printStackTrace();
}
}
publicvoid closeConn(){
try {
if (cs!=null) cs.close();
if(conn!=null) conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
publicstaticvoid main(String[] args) {
Dao dao =new Dao();
dao.getConn(); //得到连接
dao.callProcForResult(); //调用返回结果集的存储过程
dao.closeConn(); //关闭连接
}
}
　　未亲测上面的Java代码，代码来源：http://www.cnblogs.com/icerainsoft/archive/2011/08/24/2152381.html