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;