PL/SQL编程

网络整理 - 08-18

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