Oracle学习笔记6

一． 游标

1） --do while 循环

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;

2） --while 循环

Declare

Cursor c is select * from emp;

V_emp c%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;

3） --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;

4） -- 带参数的游标

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;

              Begin

                     For v_temp in c(30, ‘clerk’) loop

                            Dbms_output.put_line(v_temp.ename);

                     End loop;

              End;

5） -- 可更新的游标

Declare

         Cursor c is

                Select * from emp for update ;

Begin

         For v_temp in c loop

                If(v_temp.sal < 2000) then

                       Update emp set sal = sal * 2 where current of c ;

                Elsif (v_temp.sal = 5000) then

                       Delete from emp where current of c;

                End if;

         End loop;

         Commit;

End;