声明:本文整理自网络,加上个人实际实验结果
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