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 msgset serveroutput ondeclare --部门光标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