Oracle存储过程二

Oracle存储过程返回游标例子
游标：用来查询数据库，获取记录集合（结果集）的指针，可以让开发者一次访问一行结果集，在每条结果集上作操作。
游标分为
1．静态游标：包括显式游标和隐式游标。
2．REF游标：是一种引用类型，类似于指针。

首先创建包

create or replace package pack_test is
       type cur_test is ref cursor;
end pack_test;

创建含有游标类型参数存储过程

create or replace procedure proc_cur(
       p_id in number,
       p_cur out pack_test.cur_test) 
is 
       v_sql varchar2(400); 
begin
       if p_id = 0 then 
          open p_cur for select * from users; 
       else 
          v_sql := 'select * from users where id =: p_id'; 
          open p_cur for v_sql using p_id; 
       end if; 
end proc_cur;

PL/SQL调用存储过程如下

-- Test statements here
declare 
  v_id number := 0;
  v_row users%rowtype;
  p_cur pack_test.cur_test;
begin 
  proc_cur(v_id, p_cur);
  loop
    fetch p_cur into v_row;
    exit when p_cur%notfound;
    DBMS_OUTPUT.PUT_LINE(v_row.username||'='||v_row.name);
  end loop;
  close p_cur;
end;

JAVA调用存储过程如下

package tf;

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

public class TestProc {
	private static String sDriver = "oracle.jdbc.driver.OracleDriver";
	private static String sUrl = "jdbc:oracle:thin:@tf:1521:orcl";
	private static Connection conn;
	private static ResultSet rs;
	private static CallableStatement cs;

	public static void main(String[] args) {
		try {
			Class.forName(sDriver);
			conn = DriverManager.getConnection(sUrl, "test", "123");
			cs = conn.prepareCall("{ call proc_cur(?,?) }");
			cs.setInt(1, 0);
			cs.registerOutParameter(2, oracle.jdbc.OracleTypes.CURSOR);
			cs.execute();
			rs = (ResultSet) cs.getObject(2);
			while (rs.next()) {
				System.out.println(rs.getString(1) + "=" + rs.getString(3));
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}