一. 游标
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;