1.静态游标
a.隐式游标
当用户update,delete,insert,select into时,自动产生隐式游标。
游标名字为sql。
相关属性:%found,%notfound,%isopen,%rowcount。
begin
update emp set sal = sal + 1 where empno > &emp_no;
if sql%found then
dbms_output.put_line('更新了'||sql%rowcount||'行');
else
dbms_output.put_line('没有记录');
end if;
end;
/
declare
emp_row emp%rowtype;
begin
select * into emp_row from emp where ename like '&e_name';
if sql%found then
dbms_output.put_line(emp_row.empno || emp_row.ename);
end if;
exception
when no_data_found then
dbms_output.put_line('没有记录');
when too_many_rows then
dbms_output.put_line('Too many rows');
when others then
dbms_output.put_line('未知错误');
end;
b.显式游标
声明游标,打开游标,提取记录,关闭游标。
declare
cur_name scott.emp.ename%type;
cur_sal scott.emp.sal%type;
cursor cur_emp is select ename,sal from scott.emp where deptno=&dno;
begin
open cur_emp;
loop
fetch cur_emp into cur_name,cur_sal;
exit when cur_emp%notfound;
dbms_output.put_line(cur_name||','||cur_sal);
end loop;
end;
/
declare
cur_name scott.emp.ename%type;
cur_sal scott.emp.sal%type;
cursor cur_emp is select ename,sal from scott.emp where deptno=&dno;
begin
open cur_emp;
loop
fetch cur_emp into cur_name,cur_sal;
if cur_emp%found then
dbms_output.put_line(cur_name||','||cur_sal);
else
exit;
end if;
end loop;
end;
/
declare
emp_row emp%rowtype;
cursor emp_cur is
select * from emp where empno > &emp_no for update;
begin
open emp_cur;
loop
fetch emp_cur
into emp_row;
exit when emp_cur%notfound;
update emp set sal = sal + 100 where current of emp_cur;
dbms_output.put_line('更新了' || emp_row.ename || '工资为' ||emp_row.sal);
end loop;
close emp_cur;
commit;
end;
c.带参数的游标
declare
dept_code emp.deptno%type;
emp_code emp.empno%type;
emp_name emp.ename%type;
cursor emp_cur(dept_no number) is select empno,ename from emp where deptno=dept_no;
begin
dept_code := &dno;
open emp_cur(dept_code);
loop
fetch emp_cur into emp_code,emp_name;
exit when emp_cur%notfound;
dbms_output.put_line(emp_code||','||emp_name);
end loop;
close emp_cur;
end;
2.ref游标:运行时才确定select语句。
弱类型:不带return type。
declare
type emp_cur_type is ref cursor;
emp_cur emp_cur_type;
emp_row emp%rowtype;
begin
open emp_cur for 'select * from emp'; --可使用字符串&sql
loop
fetch emp_cur
into emp_row;
exit when emp_cur%notfound;
dbms_output.put_line(emp_row.empno);
end loop;
close emp_cur;
end;
强类型:带return type。
declare
type ref_emp_type is ref cursor return scott.emp%rowtype;
ref_emp ref_emp_type;
emp_row scott.emp%rowtype;
begin
open ref_emp for select * from scott.emp where deptno=&dno;
loop
fetch ref_emp into emp_row;
exit when ref_emp%notfound;
dbms_output.put_line(emp_row.empno||','||emp_row.ename);
end loop;
end;
/
declare
type emp_cur_type is ref cursor;
emp_cur emp_cur_type;
emp_row emp%rowtype;
e_deptno emp.deptno%type default '&e_deptno';
e_sal emp.sal%type default '&e_sal';
begin
open emp_cur for 'select * from emp where deptno = :1 and sal > :2'
using e_deptno,e_sal;
loop
fetch emp_cur
into emp_row;
exit when emp_cur%notfound;
dbms_output.put_line(emp_row.empno);
end loop;
close emp_cur;
end;
3.循环游标:
在游标中取完记录后自动停止,不需要exit when。
自动提取记录,不用fetch into。
自动关闭游标,不用close。
declare
cursor emp_cur is
select * from emp;
begin
for emp_row in emp_cur
loop
dbms_output.put_line(emp_row.empno);
end loop;
end;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/17012874/viewspace-693785/,如需转载,请注明出处,否则将追究法律责任。