Oracle学习笔记（十三）

用游标编程实现
1.对所有员工,如果该员工职位是MANAGER，并且在DALLAS工作那么就给他薪金加15％；
如果该员工职位是CLERK，并且在NEW YORK工作那么就给他薪金扣除5％;其他情况不作处理
declare
  cursor c1 is select * from emp;
  c1rec c1%rowtype;
  v_loc varchar2(20);
begin
  for c1rec in c1 loop
    select loc into v_loc from dept where deptno = c1rec.deptno;
    if c1rec.job = 'MANAGER' and v_loc = 'DALLAS' then
       update emp set sal = sal * 1.15 where empno  = c1rec.empno;
    elsif c1rec.job='CLERK' and v_loc = 'NEW YORK' then
       update emp set sal = sal * 0.95 where empno  = c1rec.empno;
    else
  null;
 
   end if;
    
  end loop;  
end; 
2.对直接上级是'BLAKE'的所有员工，按照参加工作的时间加薪：
    81年6月以前的加薪10％
    81年6月以后的加薪5％
declare
  cursor c1 is select * from emp where mgr = (select
      empno from emp where ename='BLAKE'); --直接上级是'BLAKE'的所有员工
  c1rec c1%rowtype;
begin
  for c1rec in c1 loop
    if c1rec.hiredate < '01-6月-81' then
       update emp set sal = sal * 1.1 where empno = c1rec.empno;
    else
       update emp set sal = sal * 1.05 where empno = c1rec.empno;    
    end if;
  end loop;
end;
3.根据员工在各自部门中的工资高低排出在部门中的名次(允许并列).
<1>  一条SQL语句
   select deptno,ename,sal,(select count(*) + 1
from emp where deptno = a.deptno
and sal > a.sal) as ord
from emp a
order by deptno,sal desc;
<2> PL/SQL块
declare 
  cursor cc is
    select * from dept;
  ccrec cc%rowtype;
  curs