pl/sql游标

       pl/sql游标操作的步骤：

     1：声明游标

           cursor c is

                select * from emp;

     2：打开游标

           open c;

     3：迭代抓取游标

            fetch c into 变量名

     4：关闭游标

            close c;

     利用for循环遍历游标，会自动打开和关闭游标

      

declare
	cursor c is
		select * from emp;
	v_emp emp%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;

     下面我们利用游标实现： 更新emp表，工资小于1200的，加5快，工资小于1800的，加3快，其他工资的加1快

     

declare
  cursor c 
      is select * from emp for update;
  v_emp emp%rowtype;
begin
    for v_emp in c loop
      if(v_emp.sal < 1200) then
           update emp set sal = sal +5 where current of c;
      elsif(v_emp.sal <1800) then
          update emp set sal = sal + 3 where current of c ;
      else 
         update emp set sal = sal +  1 where current of c ;
      end if;
    end loop;
end;

可更新的游标
declare  cursor c is  select * from emp2 for update;当后面加上for update时，我们就可以利用current of c 来获取当前的游标。

  游标有带参数的游标

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;