游标的使用

–无参显游标，%type接收
declare
cursor dept_cursor is select deptno,dname from dept;
v_deptno dept.deptno%type;
v_dname dept.dname%type;
begin
open dept_cursor;
loop
fetch dept_cursor into v_deptno,v_dname;
exit when dept_cursor%notfound;
dbms_output.put_line(‘部门号为：’||v_deptno||’，部门名为：’||v_dname);
end loop;
close dept_cursor;
end;

–无参显游标，%rowtype接收
declare
cursor dept_cursor is select deptno,dname,loc from dept;
v_record dept%rowtype;
begin
open dept_cursor;
loop
fetch dept_cursor into v_record;
exit when dept_cursor%notfound;
dbms_output.put_line(‘部门号为：’||v_record.deptno||’，部门名为：’||v_record.dname||’,部门地址为：’||v_record.loc);
end loop;
close dept_cursor;
end;

–无参显游标，集合接收
declare
cursor dept_cursor is select deptno,dname,loc from dept;
type dept_table_type is table of dept_cursor%rowtype index by binary_integer;
dept_table dept_table_type;
idx number;
begin
open dept_cursor;
loop
idx:=dept_cursor%rowcount+1;
fetch dept_cursor into dept_table(idx);
exit when dept_cursor%notfound or dept_cursor%rowcount>3;
dbms_output.put_line(‘部门号为：’||dept_table(idx).deptno||’，部门名为：’||dept_table(idx).dname||’,部门地址为：’||dept_table(idx).loc);
end loop;
close dept_cursor;
end;

–游标for循环
declare
cursor dept_cursor is select deptno,dname,loc from dept;

begin

for v_record in dept_cursor loop

exit when dept_cursor%notfound or dept_cursor%rowcount=#
dbms_output.put_line(‘部门号为：’||v_record.deptno||’，部门名为：’||v_record.dname||’,部门地址为：’||v_record.loc);
end loop;
end;

–修改游标所在位置
declare
v_deptno emp.deptno%type:=&deptno;
cursor emp_cursor is select empno,sal from emp where deptno=v_deptno for update nowait;

begin
for emp_record in emp_cursor loop
if emp_record.sal<1500 then
dbms_output.put_line(‘雇员号为：’||emp_record.empno||’，工资为：’||emp_record.sal);
update emp set sal=1500 where current of emp_cursor;
end if;
end loop;
commit;
end;

–删除游标所在位置
declare
v_deptno emp.deptno%type:=&deptno;
cursor emp_cursor is select empno,sal,deptno from emp for update nowait;

begin
for emp_record in emp_cursor loop
if emp_record.sal<1500 and emp_record.deptno=v_deptno then
dbms_output.put_line(‘雇员号为：’||emp_record.empno||’，工资为：’||emp_record.sal);
delete from emp where current of emp_cursor;
end if;
end loop;
commit;
end;