oracle存储过程之返回列表

干邵美

一. 创建表并插入数据

SQL> create table tb_do_only(
  2  id number primary key,
  3  name varchar2(10)
  4  );

Table created.

SQL> commit;

Commit complete.

SQL> insert into tb_do_only values(1001, 'tom');

1 row created.

SQL> insert into tb_do_only values(1002, 'tony');

1 row created.

SQL> insert into tb_do_only values(1003, 'david');

1 row created.

SQL> insert into tb_do_only values(1004, 'jack');

1 row created.

SQL> commit;

Commit complete.



二. 创建返回列表存储
SQL> create or replace package pkg_return_list as    -- 创建包pkg_return_list及包的游标list_cursor, 该游标等下做pro_return_list的返回参数类型.
  2  type list_cursor is ref cursor;
  3  end pkg_return_list;
  4  /

Package created.

SQL> create or replace procedure pro_return_list(p_cursor out pkg_return_list.list_cursor) is    -- 创建存储过程pro_return_list, 并把p_cursor的类型定义为pkg_return_list.list_cursor.
  2  begin
  3  open p_cursor for select * from scott.tb_do_only;
  4  end pro_return_list;
  5  /

Procedure created.

SQL> commit;

Commit complete.






三.创建返回列表及相应测试Java类
package return_list;

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

public class Query_list {

/**
  * @param args
  */
public static void main(String[] args) {
  String driver = "oracle.jdbc.driver.OracleDriver";
  String strUrl = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
  ResultSet rs = null;
  Connection conn = null;

  try {
   Class.forName(driver);
   conn = DriverManager.getConnection(strUrl, "scott", "tiger");

   CallableStatement proc = null;
   proc = conn.prepareCall("{ call scott.pro_return_list(?) }");
   proc.registerOutParameter(1, oracle.jdbc.OracleTypes.CURSOR);
   proc.execute();
   rs = (ResultSet) proc.getObject(1);

   while (rs.next()) {
    System.out.println(rs.getString(1) + " :  " + rs.getString(2));
   }
  } catch (SQLException ex2) {
   ex2.printStackTrace();
  } catch (Exception ex2) {
   ex2.printStackTrace();
  } finally {
if(rs!=null){
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(proc!=null){
try {
proc.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(con!=null){
try {
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
  }
}
}

文章出自：http://www.blogjava.net/javaex/articles/217474.html