游标属性
%found     布尔型，当最近一次读记录成功返回，则值为TRUE；
%notfound  布尔型，与%found相反；
%isopen    布尔型，当游标打开时返回TRUE；
%rowcount  数字型，返回已从游标中读取的记录数。

if not emp_cursor%isopen then
   open emp_cursor;
end if;

Cursor 游标名称 is select 语句;
Open 游标名称;
fetch 游标名称 into 变量列表;
close 游标名称;

1、使用标量变量接受游表数据
declare
  cursor emp_cursor is select ename,job,sal from scott.emp where deptno=&dno;
 
  v_ename scott.emp.ename%type;
  v_sal scott.emp.sal%type;
  v_job scott.emp.job%type;

begin
     open emp_cursor;
     loop
         fetch emp_cursor into v_ename,v_job,v_sal;
     exit when emp_cursor%notfound;
         dbms_output.put_line(v_ename||','||v_job||','||v_sal);
     end loop;
     close emp_cursor;
end;

2、使用记录变量接受游表数据
declare
  cursor emp_cursor is select ename,job,sal from scott.emp order by sal desc;
  emp_record emp_cursor%rowtype;
begin
     open emp_cursor;
     loop
         fetch emp_cursor into emp_record;
     exit when emp_cursor%notfound or emp_cursor%rowcount>&n;
         dbms_output.put_line(emp_record.ename||','||emp_record.sal);
     end loop;
     close emp_cursor;
end;

3、使用PL/SQL集合变量接收游标数据
declare
  cursor emp_cursor is select ename,sal from scott.emp where lower(job)=lower('&job');
  type emp_table_type is table of emp_cursor%rowtype index by binary_integer;
  emp_table emp_table_type;
  i int;
begin
     open emp_cursor;
     loop
         i:=emp_cursor%rowcount+1;
         fetch emp_cursor into emp_table(i);
     exit when emp_cursor%notfound;
          dbms_output.put_line(emp_table(i).ename||','||emp_table(i).sal);
     end loop;
     close emp_cursor;
 end;

4、游标FOR循环
declare
  cursor emp_cursor is select ename,hiredate from scott.emp order by hiredate desc;
begin
     for emp_record in emp_cursor loop
         dbms_output.put_line(emp_record.ename||','||emp_record.hiredate);
     exit when emp_cursor%rowcount=&n;
     end loop;
 end;

5、在游标FOR循环中直接引用子查询
begin
  for emp_record in (select ename,hiredate,rownum from scott.emp order by hiredate) loop
      dbms_output.put_line(emp_record.ename||','||emp_record.hiredate);
  exit when sql%notfound;
  end loop;
end;

6、参数游标
declare
  cursor emp_cursor(dno number) is select ename,job from scott.emp where deptno=dno;
begin
     for emp_record in emp_cursor(&dno) loop
         dbms_output.put_line(emp_record.ename||','||emp_record.job);
     end loop;
end;

7、更新游标行
declare
  cursor emp_cursor is select ename,sal,deptno from scott.emp for update;
  dno int:=&no;
begin
     for emp_record in emp_cursor loop
         if emp_record.deptno=dno then
            dbms_output.put_line(emp_record.ename||','||emp_record.sal);
            update scott.emp set sal=sal*1.2 where current of emp_cursor;
         end if;
     end loop;
end;

8、删除游标行
declare
  cursor emp_cursor is select ename from scott.emp for update;
  name varchar2(10):=lower('&name');
begin
     for emp_record in emp_cursor loop
         if lower(emp_record.ename)=name then
            delete from scott.emp where current of emp_cursor;
         else
             dbms_output.put_line(emp_record.ename);
         end if;
     end loop;
end;

9、使用OF子句在特定表上加共享锁
declare
  cursor emp_cursor is select a.dname,b.ename from scott.dept a join scott.emp b
  on a.deptno=b.deptno for update of b.deptno;
  name varchar2(10):=lower('&name');
begin
     for emp_record in emp_cursor loop
         if lower(emp_record.dname)=name then
            dbms_output.put_line(emp_record.ename);
            delete from scott.emp where current of emp_cursor;
         end if;
     end loop;
end;

10、使用无返回类型的游标变量
declare
  type ref_cursor_type is ref cursor;
  ref_cursor ref_cursor_type;
  v1 number(6);
  v2 varchar2(10);
begin
     open ref_cursor for select &col1 col1,&col2 col2 from scott.&table where &cond;
     loop
         fetch ref_cursor into v1,v2;
         exit when ref_cursor%notfound;
         dbms_output.put_line('col1='||v1||',col2='||v2);
     end loop;
     close ref_cursor;
end;

11、使用有返回类型的游标变量
declare
  type emp_cursor_type is ref cursor return scott.emp%rowtype;
  emp_cursor emp_cursor_type;
  emp_record scott.emp%rowtype;
begin
     open emp_cursor for select * from scott.emp where deptno=&dno;
     loop
         fetch emp_cursor into emp_record;
         exit when emp_cursor%notfound;
         dbms_output.put_line(emp_record.ename||','||emp_record.sal);
     end loop;
     close emp_cursor;
end;

12、使用FETCH...BULK COLLECT提取所有数据
declare
  cursor emp_cursor is select * from scott.emp where lower(job)=lower('&job');
  type emp_table_type is table of scott.emp%rowtype;
  emp_table emp_table_type;
begin
     open emp_cursor;
     fetch emp_cursor bulk collect into emp_table;
     close emp_cursor;
     for i in 1..emp_table.count loop
         dbms_output.put_line(emp_table(i).ename||','||emp_table(i).sal);
     end loop;
end;

13、使用LIMIT子句限制提取行数
declare
  cursor emp_cursor is select * from scott.emp;
  type emp_array_type is varray(20) of scott.emp%rowtype;
  emp_array emp_array_type;
begin
     open emp_cursor;
     fetch emp_cursor bulk collect into emp_array limit &rows;
     for i in 1..emp_array.count loop
         dbms_output.put_line(emp_array(i).ename||','||emp_array(i).sal);
     end loop;
     close emp_cursor;
end;

14、使用CURSOR表达式
declare
  cursor dept_cursor(no number) is select a.dname,cursor(select * from scott.emp where deptno=a.deptno)
  from scott.dept a where a.deptno=no;
  type ref_cursor_type is ref cursor;
  emp_cursor ref_cursor_type;
  emp_record scott.emp%rowtype;
  v_dname scott.dept.dname%type;
begin
     open dept_cursor(&dno);
     loop
         fetch dept_cursor into v_dname,emp_cursor;
         exit when dept_cursor%notfound;
         dbms_output.put_line('部门名:'||v_dname);
         loop
             fetch emp_cursor into emp_record;
             exit when emp_cursor%notfound;
             dbms_output.put_line('雇员名:'||emp_record.ename||','||emp_record.job);
         end loop;
     end loop;
     close dept_cursor;
end;

隐式游标
begin
update scott.emp set sal=sal+1 where empno=0001;
 if sql%notfound then
   insert into scott.emp
   values(0001,'abcd',null,null,null,801,null,20);
 end if;
end;

例
drop table scott.temp_hjp;
create table scott.temp_hjp
(art_no number(7),
 move_qty1 number(9,2),
 move_qty2 number(9,2)
);
commit;

declare
cursor c1 is select art_no,sum(move_qty)
from scott.art_movement
where to_char(move_date)='11-4月 -08'
group by art_no;
v1_no number(7);
v1_qty number(9,3);
v2_qty number(9,3);

begin
  open c1;
  loop
      fetch c1 into v1_no,v1_qty;
      if c1%found then
   select sum(move_qty) into v2_qty from scott.art_movement where to_char(move_date)='10-4月 -08' and art_no=v1_no;
          --dbms_output.put_line(v1_no||'  '||v1_qty);
          insert into scott.temp_hjp(art_no,move_qty1,move_qty2) values(v1_no,v1_qty,v2_qty);
      else
          exit;
      end if;
  end loop;
  close c1;
end;
/
select * from scott.temp_hjp;
drop table scott.temp_hjp;