set serveroutput on

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;
        --退出条件:not found
        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 loop;
     close cemp;

     dbms_output.put_line(‘Total:‘||(count80+count81+count82+count87))
     dbms_output.put_line(‘count81:‘||count81)
     dbms_output.put_line(‘count82:‘||count82)
     dbms_output.put_line(‘count87:‘||count87)
end;
/

set serveroutput on
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;
begin
    --得到初始的工资总额
    select sum(sal) into salTotal from emp;

    open cemp;
    loop
        --1.总额>5w
        exit  when salTotal>50000;
        --取一个员工
        fetch cemp into pempno,psal;
        --2.notfound
        exit when cemp%notfound
         
       --涨工资(涨工资之前进行判断一下,如果多于50000就不涨)

       update emp set sal =sal*1.1  where empno =pempno;
       --人数+1
       countEmp :=countEmp +1;
       --2.涨后=涨前+sal*0.1(当没有必要进行操作数据库时就不进行操作)
       salTotal :=salTotal+psal*0.1
    end loop;
    close cemp;
    
    commit;
    dbms_output.put_line(‘人数:‘||countEmp||‘总金额:‘salTotal)
end;
/

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
　 --部门的工资总额： 
    salTatal number :=0
begin
    open cdept;
    loop
        --取一个部门(取完就应该想到退出条件)
        fetch cdept into pdeptno;    
        exit when cdept%notfound;
        
        --初始化
        count1:=0;count2:=0;count3:=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(‘完成‘)
end;
/