1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 /*by lby 為員工長工資。從最低工資調起每人長10％，但工資總額不能超過5萬元, 請計算長工資的人數和長工資後的工資總額，並輸出輸出長工資人數及工資總額。 用到的sql: select empno,sal from emp order by sal; select sum(sal) from emp; update emp set sal = sal + sal*0.1 where empno = ?; */ set serveroutput on declare cursor cemp is select empno,sal from emp order by sal; --相對應的變量 pempno emp.empno% TYPE; psal emp.sal% TYPE; --保存工資總額 ptotalSal NUMBER; --計算器保存漲工資的人數 pcount number; begin --初始化工資總額 select sum(sal) into ptotalSal from emp; pcount := 0; open cemp; loop exit when ptotalSal> 60000; fetch cemp into pempno,psal; exit when cemp%notfound; ptotalSal := ptotalSal + psal*0.1; if ptotalSal>60000 then ptotalSal := ptotalSal- psal*0.1; exit ; else update emp set sal=sal*1.1 where empno=pempno; pcount := pcount + 1; end if; end loop; close cemp; commit; dbms_output.put_line('工資總額:'||ptotalSal); dbms_output.put_line('漲工資的人數'||pcount); end; /
