plsql中游标的应用

游标：cursor

declare 

         cursor c  is  --声明一个游标

                select * from emp;

        v_emp c%rowtype;

begin

             open c;--打开游标

             fetch c into v_emp; --拿出这条记录，存到v_emp,拿出后，游标自动向后移

            dbms_output.put_line(v_emp.ename);

            close c; --自动清除内存

end;

循环和游标一起使用：

declare

        cursor c is

            select * from emp;

        v_emp  c%rowtype;

begin

      open c;

     loop  --注意这三条不能变换顺序

        fetch c into v_emp;

         exit when (c%notfound);

        dbms_output.put_line(v_emp.ename);

     end loop;

     close c;

end;

while()循环与游标的使用：

declare

        cursor c is

            select * from emp;

        v_emp emp%rowtype;

begin

          open c;

        fetch c into v_emp;

         while(c%found)

          loop

            dbms_output.put_line(v_emp.ename);

            fetch c into v_emp;

        end loop;

        close c;

end;

          for（）循环,    是最简单的循环，最常用；

declare 

        cursor c is

             select * from emp;

begin

         for v_emp in c 

        loop

            dbms_output.put_line(v_emp.ename);

        end loop;

end;

带参数的游标：

declare  

            --这儿是形参

        cursor  c(v_deptno  emp.deptno%type,v_job emp.job%type)

        is 

            select ename ,sal from emp where deptno  =v_deptno and job=v_job;

            --v_temp c%rowtype;

begin

          for v_temp in c(30,'CLERK')

        loop

            dbms_output.put_line(v_temp.ename);

        end loop;

end;

     可更新的游标：

declare 

            cursor c 

            is

                select * from emp2 for update;

                --v_temp c%rowtype;

        begin

        for v_temp in c 

        loop

            if(v_temp.sal < 2000) then

                 update emp2 set sal = sal*2 where current of c;

            elsif(v_temp.sal = 5000) then

                 delete from emp2 where current of c;

            end if;

            end loop;

        commit;

end;