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
????--初始化值
????count1?:=?0;
????count2?:=?0;
????count3?:=?0;
????saltotal?:=?0;
????--取得每個部門的部門號
????fetch?cdept?into?pdeptno;
????exit?when?cdept%notfound;
????--打開員工薪水光標
????open?cemp(pdeptno);
??????loop
????????fetch?cemp?into?psal;
????????exit?when?cemp%notfound;
????????saltotal?:=?saltotal+psal;
????????--判斷員工薪水的字段
????????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,saltotal);
??end?loop;
??--關閉部門光標
??close?cdept;
??dbms_output.put_line('統(tǒng)計結束');
end;
/