pl/sql综合

--每年入职人数
declare
  cursor cemp is
    select to_char(hiredate, 'yyyy') from emp;
  phiredate varchar2(4);
  count80   number := 0;
  count81   number := 0;
  count82   number := 0;
  count87   number := 0;
begin
  open cemp;
  loop
    fetch cemp
      into phiredate;
    exit when cemp%notfound;
    if phiredate = '1980' then
      count80 := count80 + 1;
    elsif phiredate = '1981' then
      count81 := count81 + 1;
    elsif phiredate = '1982' then
      count82 := count82 + 1;
    else
      count87 := count87 + 1;
    end if;
  end loop;
  close cemp;
  dbms_output.put_line(count80 + count81 + count82 + count87);
  dbms_output.put_line(count80);
  dbms_output.put_line(count81);
  dbms_output.put_line(count82);
  dbms_output.put_line(count87);
end;


--给员工涨工资
declare
  cursor cemp is
    select empno, sal from emp order by sal;
  pempno   emp.empno%type;
  psal     emp.sal%type;
  countEmp number := 0; --涨工资人数；
  salTotal number:= 0; --涨后工资总额；
begin
  open cemp;
  loop
    exit when salTotal > 50000; --退出条件：1.工资总额>50000;
    fetch cemp
      into pempno, psal;
    exit when cemp%notfound; --退出条件：2.notfound
    update emp set sal = sal * 1.1 where empno = pempno;
    countEmp := countEmp + 1;
    salTotal := salTotal + psal * 0.1;
  end loop;
  close cemp;
  commit;
  dbms_output.put_line('人数：'||countEmp||'  涨后工资总额：'||salTotal);
end;
  
rollback;
select * from emp;

/*实现按部门分段（6000以上、（6000、3000）、3000以下）统计各部门工资段的职工人数、
以及各部门的工资总额（工资总额中不包括奖金）*/

create table msg(deptno number,
count1 number,
count2 number,
count3 number,
saltotal number
);
select * from msg

set serveroutput on
declare 
--部门光标
cursor cdept is select deptno from dept;
pdeptno dept.deptno%type;
--部门员工中的薪水
cursor cemp (dno number) is select sal from emp where deptno=dno;
psal emp.sal%type;
count1 number;
count2 number;
count3 number;
saltotal number;
begin
  open cdept;
  loop 
    --取出一个部门
    fetch cdept into pdeptno;
    exit when cdept%notfound;
    --初始化工作
    count1:=0;count2 :=0;count3 :=0;saltotal:=0;
    --得到部门工资总额
    select sum(sal) into saltotal from emp where deptno=pdeptno;
    --取出部门中的员工薪水
        open cemp(pdeptno);
    loop
      --取一个员工的薪水
      fetch cemp into psal;
      exit when cemp%notfound;
      --判断薪水范围
      if psal <3000 then count1:=count1+1;
      elsif psal >=3000 and psal <=6000 then count2:=count2+1;
      else count3:=count3+1;
      end if;   
      end loop;
    close cemp;
     --保存当前部门结果  
      insert into msg values (pdeptno,count1,count2,count3,nvl(saltotal,0));
    end loop;
  close cdept;
  commit;
  dbms_output.put_line('ok');
  end;
truncate table msg