java怎么得到oracle的游标_Java 开发中之六：Oracle中的游标

Oracle中的游标:是内存中的一块区域，存放sql 的查询结果

cursor

隐式游标：无约束的集合--可以存放任意表的结果

使用单条结构化查询语言，4个属性如下：

%rowcount记录的行数，整数.

%found:能得到记录true.

%notfound:是否为空 true.

%ispoen：是否打开，默认值，false.

declare

empr emp%rowtype;--游标一般配合行级变量使用

cursor cc is select * from emp where deptno=30;

--声明一个游标变量

ccrec cc%rowtype;

begin

for empr in cc loop

dbms_output.put_line(empr.empno||' '|| empr.ename);

end loop;

end;

declare

cursor cc is select * from emp where deptno=30;

ccrec cc%rowtype;

begin

open cc;

loop

fetch cc into ccrec;

exit when cc%notfound;

dbms_output.put_line(ccrec.empno||' '|| ccrec.ename);

end loop;

close cc;

end;

declare

cursor cc is select * from emp where deptno=30;

ccrec cc%rowtype;

begin

open cc;

fetch cc into ccrec;

while cc%found loop

dbms_output.put_line(ccrec.empno||' '|| ccrec.ename||' '||cc%rowcount);

fetch cc into ccrec;

end loop;

close cc;

end;

declare

cursor cc(no number) is select * from emp where deptno=no;--游标带参时最好使用for循环

ccrec cc%rowtype;

begin

for empr in cc(&no) loop

dbms_output.put_line(empr.empno||' '|| empr.ename||' '||cc%rowcount);

end loop;

end;

declare

cursor cc is select emp1.*,dept.loc from emp1,dept where emp1.deptno=dept.deptno

and emp1.job in(upper('manager'),upper('clerk'));

ccrec cc%rowtype;

begin

for ccrec in cc loop

if (ccrec.loc='DALLAS') then

update emp1 set emp1.sal=sal*1.15 where emp1.empno=ccrec.empno;

commit;

else

update emp1 set emp1.sal=sal*0.95 where emp1.empno=ccrec.empno;

commit;

end if;

end loop;

end;

显式游标:带泛型的集合--只能存放指定表结构的查询结果

declare

type cur is ref cursor; --弱类型，可以存任何东东

--type cur is ref cursor return emp%rowtype; --强类型，存值有限

mycur cur;-- 定义一个游标

r1 emp%rowtype;

r2 dept%rowtype;

tn varchar2(10);

begin

tn:='&tablename';

if upper(tn)='EMP' then

open mycur for select * from emp;

loop

fetch mycur into r1;

exit when mycur%notfound;

dbms_output.put_line(r1.empno||' '||r1.ename);

end loop;

else

open mycur for select * from dept;

loop

fetch mycur into r2;

exit when mycur%notfound;

dbms_output.put_line(r2.deptno);

end loop;

end if;

end;

*注解：游标只能向一个方向走

在包中调用一个返回的游标

declare

p_dist_values sys_refcursor;

ccrec varchar2(1000);

begin

PKG_HOUSEKEEP.get_combobox_distinct_values('gc_guarantee', p_dist_values); --这样这个游标就有值了

fetch p_dist_values

into ccrec;

--while p_dist_values%found loop

dbms_output.put_line(ccrec);

--end loop;

close p_dist_values;

end;