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

声明：本文整理自网络，加上个人实际实验结果

 

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