oracle游标总结

1.利用游标显示职工号、来厂日期、职位、部门号、部门名、部门所在地、工资所处的等级。

declare
   outempno int;
   outhiredate date;
   outjob varchar(10);
   outdeptno int;
   outloc varchar(10);
   outgrade int;
   cursor list_emp_dept_salgrade is select empno,hiredate,job,deptno,loc,grade from emp natural join dept,salgrade where sal between losal and hisal;
begin
   open list_emp_dept_salgrade;
       loop
             fetch list_emp_dept_salgrade into outempno,outhiredate,outjob,outdeptno,outloc,outgrade;
             exit when list_emp_dept_salgrade%notfound;
             dbms_output.put_line(outempno||'    '||outhiredate||'    '||outjob||'    '||outdeptno||'    '||outloc||'    '||outgrade);
       end loop;
   close list_emp_dept_salgrade;
   commit;
end;

2、为所有的经理涨工资，每人提升10%，如果工作超过5年的话，再加3000元。
 declare
    outempno int;
    outename varchar(10);
    outjob varchar(10);
    outhiredate date;
    outsal int;
    cursor list_emp is select empno,ename,job,hiredate,sal from emp where job='MANAGER';
 begin
    open list_emp;
        loop
            fetch list_emp into outempno,outename,outjob,outhiredate,outsal;
            exit when list_emp%notfound;
              update emp set sal=(1+0.1)*outsal where empno=outempno;
            if months_between(sysdate,outhiredate)>=60 then
                update emp set sal=sal+3000 where empno=outempno;   
            end if;
          
        end loop;
      close list_emp;
    commit;
 end;

 

第二种方法：
declare
    cursor cur_emp is select * from emp where job='MANAGER' for update of sal;
    semp emp%rowtype;
begin
    open cur_emp;
       loop
           fetch cur_emp into semp;
           exit when cur_emp%notfound;
           update emp set sal=(1+0.1)*sal where current of cur_emp;
           if months_between(sysdate,outhiredate)>=60 then
           update emp set sal=sal+3000 where current of cur_emp;//释放当前行的锁
           end if;
       end loop;
    close cur_emp;
    commit;
end;

 

 
3、将emp表中工作年限最长的两个人提升为经理。
declare
   outhiredate date;
   outjob varchar(10);
   outempno number;
   cursor list_emp is select job,hiredate,empno from emp  order by hiredate asc;
begin
   for i in 1..2 loop
      fetch list_emp into outjob,outhiredate,outempno;
      exit when list_emp%notfound;
      update emp set job='MANAGER' where empno=outempno;
      end loop;
   commit;
end;

 

第二种方法：
declare
   cursor cur_emp is select * from emp where job not like 'MANAGER'  order by hiredate asc for update of job;
   semp emp%rowtype;
begin
   open cur_emp;
       loop
           fetch cur_emp into semp;
           exit when cur_emp%rowcount>=2;
           update emp set job='MANAGER' where current of cur_emp;
       end loop;
   close cur_emp;
   commit;
end;

 

4、将emp表中姓名以A或S开始的员工工资提升10%。
declare
    cursor cur_emp is select empno from emp where ename like 'A%' or ename like 'S%';
    outempno int;
begin
    open cur_emp;
        loop
            fetch cur_emp into outempno;
            exit when cur_emp%notfound;
            update emp set sal=sal*1.1 where empno=outempno;
        end loop;
    close cur_emp;
    commit;
end;

 

第二种方法：
declare
   cursor cur_emp is select * from emp where ename like 'A%' or ename like 'S%' for update of sal;
   semp emp%rowtype;
begin
   open cur_emp;
       loop
          fetch cur_emp into semp;
          exit when cur_emp%notfound;
          update emp set sal=sal*1.1 where current of cur_emp;
       end loop;
   close cur_emp;
    commit;
end;

5.将员工的工资进行修改，少于1000 改为1000，在1001-2000 之间改为2500，2001-3000 之间改为3500。
declare
    outempno int;
    outsal int;
    cursor list_emp is select empno,sal from emp;
begin
    open list_emp;
    loop
      fetch list_emp into outempno,outsal;
      exit when list_emp%notfound;
      if outsal<1500 then
      outsal:=1500;
      elsif outsal<3000 then
      outsal:=3000;
      else
      outsal:=5000;
      end if;
         update emp set sal=outsal where empno=outempno;
    end loop;
      dbms_output.put_line('更新成功');
    close list_emp;
    commit;
end;

declare
   cursor cur_emp is select * from emp for update of sal;
   semp emp%rowtype;
begin
   open cur_emp;
      loop
         fetch cur_emp into semp;
         exit when cur_emp%notfound;
         if semp.sal<1500 then
      semp.sal:=1500;
      elsif semp.sal<3000 then
      semp.sal:=3000;
      else
      semp.sal:=5000;
      end if;
         update emp set sal=semp.sal where current of sal;
      end loop;
   close cur_emp;
   commit;
end;