oracle游标变量

 
--1
declare
type emp_cursor_type is ref cursor;
emp_cursor emp_cursor_type;
emp_record emp%rowtype;
begin
   open emp_cursor for select * from emp where deptno=10;
   loop
     fetch emp_cursor into emp_record;
     exit when emp_cursor%notfound;
     dbms_output.put_line('第'||emp_cursor%rowcount||'个雇员'||emp_record.ename);
   end loop;
   close emp_cursor;
end;

--定义 ref cursor类型时 ，指定return 子句
  declare
type emp_record_type is record(
name varchar2(10),
salary number(6,2)
);
type emp_cursor_type is ref cursor return emp_record_type;
emp_cursor emp_cursor_type;
emp_record emp_record_type;
begin

  open emp_cursor for select ename,sal from emp;
  loop
    fetch emp_cursor into emp_record;
    exit when emp_cursor%notfound;
    dbms_output.put_line('第'||emp_cursor%rowcount||'个雇员的名字：'||emp_record.name);
  end loop;
  close emp_cursor;
end;
  --使用cursor表达式,嵌套cursor
  declare

type refcursor is ref cursor;
cursor dept_cursor(no number) is
   select a.dname,cursor(select ename,sal from emp where deptno=a.deptno)
  from dept a where a.deptno=no;
empcur refcursor;
v_dname dept.dname%type;
v_ename emp.ename%type;
v_sal emp.sal%type;
begin
  open dept_cursor(&no);
  loop
    fetch dept_cursor into v_dname,empcur;
    exit when dept_cursor%notfound;
    dbms_output.put_line('部门名：'||v_dname);
    loop
fetch empcur into v_ename,v_sal;
exit when empcur%notfound;
dbms_output.put_line('雇员名：'||v_ename||',工资：'||v_sal);
    end loop;
  end loop;
  close dept_cursor;
end;


---The STRONG_REF_CURSOR and until Oracle 9i also the weak-type need to be declared in a package structure lik this:

create or replace package refcursor_pkg as
type weak8i_ref_cursor is ref cursor;
type strong_ref_cursor is ref cursor return emp%rowtype;
end refcursor_pkg;

--The pl/sql procedure that returns a ref-cursor looks like this:
create or replace procedure test(
p_deptno in number,
p_cursor out refcursor_pkg.weak8i_ref_cursor
)
is
begin
  open p_cursor for
   select * from emp where deptno=p_deptno;
end ;

--Since Oracle 9i you can use SYS_REFCURSOR as the type for the returning REF_CURSOR.
create or replace procedure test(
p_deptno in number,
p_cursor out sys_refcursor
)
is
begin
  open p_cursor for
   select * from emp where deptno=p_deptno;
end ;
/*
Selecting the ref_cursor from JDBC
To get the cursor from Java you can use the following JDBC-code:
*/
public void method() throws SQLException{
  Connection conn = getConnection();
  CallableStatement cstmt = null;
  ResultSet rs = null;
  int deptno = 10;
  Object temp;
  try{
      cstmt = conn.prepareCall("begin  test(?,?); end;");
      cstmt.setInt(1, deptno);
      cstmt.registerOutParameter(2, OracleTypes.CURSOR);
      cstmt.execute();
      rs = (ResultSet) cstmt.getObject(2);
      ResultSetMetaData rsm = rs.getMetaData();
      int columnCount = rsm.getColumnCount();
      while (rs.next()){
         for (int j=0;j< columnCount;j++){
            temp = rs.getObject(j+1);
         }
      }
  } finally {
      if (!rs==null){
        rs.close();
      }
      if (!stmt==null){
        stmt.close();
      }
      if (!conn==null){
        conn.close();
      } 
  }
}


--Calling ref-cursor from pl/sql

create or replace procedure test_call is
  c_cursor REFCURSOR_PKG.STRONG_REF_CURSOR;
  r_emp    emp%rowtype;
begin
  test(10,c_cursor);
  loop
    fetch c_cursor into r_emp;
    exit when c_cursor%notfound;
    dbms_output.put_line(r_emp.ename);
  end loop;
  close c_cursor;
end test_call;