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

PL/SQL编程,布布扣,bubuko.com

PL/SQL编程