--演示隱式游標(biāo),系統(tǒng)自動(dòng)聲明，自動(dòng)打開(kāi)，自動(dòng)使用并且自動(dòng)關(guān)閉

begin

     update emp set sal = 1000;

     dbms_output.put_line('影響的行數(shù)：' || sql%rowcount);

end;

rollback;

/*游標(biāo)的使用方法：

第一步：聲明游標(biāo)

第二步：打開(kāi)游標(biāo)

第三步：使用游標(biāo)進(jìn)行循環(huán)操作

第四步：關(guān)閉游標(biāo)*/

--普通游標(biāo)，游標(biāo)本身就是一個(gè)變量

declare     

     --下面的這行代碼聲明了一個(gè)游標(biāo)

     cursor mycur is select * from emp where deptno = 20;

     emprow emp%rowtype;

begin

     open mycur; --打開(kāi)游標(biāo)

     loop

         fetch mycur into emprow; --把游標(biāo)所指的紀(jì)錄放到變量中

         exit  when (mycur%notfound); --當(dāng)游標(biāo)沒(méi)有指向行時(shí)退出循環(huán)

         dbms_output.put_line('名字：' || emprow.ename || '薪水：' || emprow.sal);

     end loop;

     close mycur;  --關(guān)閉游標(biāo)

end;

--簡(jiǎn)單游標(biāo)，列操作

declare

       empname emp.ename%type;

       empsal emp.sal%type;

       cursor mycur is select ename,sal from emp where deptno = 30;

begin

     open mycur;

     loop

         fetch mycur into empname,empsal;

         exit when mycur%notfound;

         dbms_output.put_line('姓名:' || empname || '工資' || empsal);

     end loop;

end;

--簡(jiǎn)單游標(biāo)，列操作

declare

  cursor c 

  is

  select * from dept;

  vDept_row_record c%rowtype;

begin

  open c;

  fetch c into vDept_row_record;

  dbms_output.put_line(vDept_row_record.dname);

  close c;

end;

--when循環(huán)游標(biāo)

declare

  cursor c 

  is

  select * from dept;

  vDept_row_record c%rowtype;

begin

  open c;

  loop

       fetch c into vDept_row_record;

       exit when(c%notfound);

       dbms_output.put_line(vDept_row_record.dname);

  end loop;

  close c;

end;

--while循環(huán)游標(biāo)

declare

  cursor c

  is

  select * from dept;

  vDept_row_record c%rowtype;

begin

  open c;

  fetch c into vDept_row_record;

  while (c%found) loop

    dbms_output.put_line(vDept_row_record.dname);

    fetch c into vDept_row_record;

  end loop;

  close c;

end;

--for循環(huán)游標(biāo)

declare

  cursor c

  is

  select * from dept;

  vDept_row_record c%rowtype;

begin

  for vDept_row_record in c loop

    dbms_output.put_line(vDept_row_record.dname);

  end loop;

end;

--帶參游標(biāo)

declare

  cursor c(sSal emp.sal%type, sEmpno emp.empno%type)

  is

  select * from emp where sal >= sSal and empno > sEmpno;

begin

  for record_data in c(2500, 6666) loop

      dbms_output.put_line(record_data.ename);

  end loop;

end;

--update游標(biāo)

declare

  cursor c(sSal emp2.sal%type)

  is

  select * from emp2 where sal >= sSal for update;

begin

  for record_data in c(2500) loop

    if (record_data.sal < 3000) then

      update emp2 set sal = sal   3 where current of c;

      dbms_output.put_line(record_data.ename);

    elsif (record_data.sal = 5000) then

      update emp2 set sal = sal - 3 where current of c;

      dbms_output.put_line(record_data.ename);

    end if;

  end loop;

end;

--引用游標(biāo)不能使用循環(huán)游標(biāo)的語(yǔ)法

--引用游標(biāo)不能進(jìn)行刪除和修改

--引用游標(biāo)是一個(gè)數(shù)據(jù)類型，使用該類型必須聲明變量

--弱類型引用游標(biāo)，就是不指定游標(biāo)將要提取的數(shù)據(jù)行的類型

declare

       type my_cur_type is ref cursor;

       mycur my_cur_type;--聲明變量

       which varchar2(10);

       deptrow dept%rowtype;

       emprow emp%rowtype;

begin

     which := '&請(qǐng)選擇dept還是emp';

     if (which = 'dept') then

        open mycur for select * from dept;

        loop

            fetch mycur into deptrow;

            exit when (mycur%notfound);

            dbms_output.put_line(deptrow.deptno || '  ' || deptrow.dname);

        end loop;

     elsif (which = 'emp') then

        open mycur for select * from emp;

        loop

            fetch mycur into emprow;

            exit when (mycur%notfound);

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

        end loop;

     end if;

     close mycur;

end;

--強(qiáng)類型引用游標(biāo)，就是指定游標(biāo)將要提取的數(shù)據(jù)行的類型 ，只能是record或%rowtype類型

--比如：return number是錯(cuò)的，return emp.ename%type也是錯(cuò)的

declare

       type mycurtype is ref cursor return emp%rowtype;

       mycur mycurtype;--聲明變量

       emprow emp%rowtype;

begin

        open mycur for select * from emp;

        loop

            fetch mycur into emprow;

            exit when mycur%notfound;

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

        end loop;

        close mycur;