游标使用

declare
  type DeptRecord is record
       (deptno dept.deptno%type,
        dname dept.dname%type,
        loc dept.loc%type);
  v_deptrecord DeptRecord;
  v_dept_name dept.dname%type;
  v_dept_loc dept.loc%type;
  cursor c1                                --声明游标
  is
     select dname,loc from dept where deptno <= 10;
  cursor c2(v_dept_no number)              --有参数游标
  is
     select dname,loc from dept where deptno <= v_dept_no;
  cursor c3(v_dept_no number)              --有返回数据类型的游标
    return DeptRecord
  is
     select deptno,dname,loc from dept where deptno <= v_dept_no;
  cursor c4(v_dept_no number)              --普通的游标
  is
     select deptno,dname,loc from dept where deptno <= v_dept_no;
  v_dept_rec c4%rowtype;                   --基于游标定义记录变量，比DeptRecord方便
begin
  open c1;                                 --打开游标
  loop
    fetch c1 into v_dept_name,v_dept_loc;  --提取游标
    if c1%found then
      dbms_output.put_line('c1:'|| v_dept_name||' '||v_dept_loc);
    else
      dbms_output.put_line('c1:'||'已经处理完结果了');
      exit;
    end if;
  end loop;
  open c2(20);
  loop
    fetch c2 into v_dept_name,v_dept_loc;
    exit when c2%notfound;
      dbms_output.put_line('c2:'|| v_dept_name||' '||v_dept_loc);
  end loop;
  open c3(v_dept_no => 30);
  loop
    fetch c3 into v_deptrecord;
    exit when c3%notfound;
    dbms_output.put_line('c3:'|| v_deptrecord.deptno||' '||v_deptrecord.dname||' '||v_deptrecord.loc);
  end loop;
   open c4(40);
  loop
    fetch c4 into v_dept_rec;
    exit when c4%notfound;
    dbms_output.put_line('c4:'|| v_dept_rec.deptno||' '||v_dept_rec.dname||' '||v_dept_rec.loc);
  end loop;
  close c1;
  close c2;
  close c3;
  close c4;
end;








---for 循环的游标控制语句,不需要自己手动的打开，提取，关闭游标

declare
   cursor c1(v_dept_no number default 10)
   is
          select deptno,dname from dept where deptno <= v_dept_no;
   begin
     for c1_rec in c1(30) loop
       dbms_output.put_line('c1(30):'||c1_rec.deptno||' '||c1_rec.dname);
       end loop;
      for c1_rec in c1 loop
       dbms_output.put_line('c1(10):'||c1_rec.deptno||' '||c1_rec.dname);
       end loop;
   end;