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 orderby sal;
select sum(sal) fromemp;
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;
psalemp.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;
/
