PL/SQL編程

 

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; /