筆記竟然沒實現(xiàn)插入代碼功能。

--創(chuàng)建表
/*create?table?msg
(deptno?number,
count1?number,
count2?number,
count3?number,
saltotal?number);

*/

set?serveroutput?on;

declare
??--定義光標1:查詢所有部門號
??cursor?cemp1?is?select?deptno?from?dept?group?by?deptno;
??pdeptno?dept.deptno%type;--部門號
??--定義光標2(部門號)：帶參光標，查詢相應(yīng)部門的所有員工工資
??cursor?cemp2(deptno2?dept.deptno%type)?is?select?sal?from?emp?where?deptno=deptno2;
??psal?number;
??psaltotal?number;--相應(yīng)部門工資總額
??count1?number;?--記錄工資3000以下的員工人數(shù)
??count2?number;?--記錄工資3000以上，6000以下的員工人數(shù)
??count3?number;?--記錄工資6000以上的員工人數(shù)
??
??begin
??
????--打開光標
????open?cemp1;
????
????--外層循環(huán)，得到所有部門號
????loop?
????
???????--得到一條部門號數(shù)據(jù)
??????fetch?cemp1?into?pdeptno;
??????exit?when?cemp1%notfound;
??????--重制所有臨時變量
??????psaltotal:=0;
??????count1?:=0;
??????count2?:=0;
??????count3?:=0;
??????
??????--如果cemp2未打開，則打開cemp2
??????if?cemp2%isopen=false?then
????????open?cemp2(pdeptno);
??????end?if;
??????
??????--內(nèi)層循環(huán)，得到相應(yīng)部門員工的工資
??????loop
??????
????????--得到一條相應(yīng)部門號的員工數(shù)據(jù)
????????fetch?cemp2?into?psal;
????????exit?when?cemp2%notfound;
????????--判斷工資級別
????????if?psal<=3000?then?count1?:=?count1+1;
????????elsif?(psal>3000?and?psal?<=6000)?then?count2?:=count2+1;
????????else?count3:=count3+1;
????????end?if;
????????--計算工資總額??（比操作數(shù)據(jù)庫更好）
????????psaltotal?:=?psaltotal?+?psal;
????????
??????end?loop?;
??????
??????--關(guān)閉cemp2
??????close?cemp2;
??????
??????--向msg中插入一條數(shù)據(jù)
??????insert?into?msg?values(pdeptno,count1,count2,count3,psaltotal);
??????dbms_output.put_line('測試，插入成功');
??????
????end?loop?;

????--關(guān)閉光標
????close?cemp1;
????
??end;
/

select?*?from?msg;