declare ?? cursor cemp is select empno ,sal from emp order by sal; ?? --定義參數(shù) ?? pempno emp.empno%type; ?? psal emp.sal%type; ?? countEmp number :=0; ?? salTotal number;
?? begin ???? --得到工資總額的初始值 ???? select? sum(sal) into salTotal from emp; ???? --打開光標(biāo) ???? open cemp; ????????? loop ????????????? -- 1.工資總額>5w ????????????? exit when salTotal>50000; ????????????? --取一個(gè)員工漲工資 ????????????? fetch? cemp into? pempno ,psal; ????????????? --2.%notfound ????????????? exit when cemp%notfound;
????????????? --漲工資 ????????????? if salTotal+psal*1.1<50000 then ????????????? update? emp set sal = sal*1.1 where empno=pempno; ????????????? --漲工資的人數(shù) ????????????? countEmp := countEmp+1; ????????????? --漲后的工資總額 ????????????? salTotal := salTotal+psal*0.1; ????????????? else exit; ????????????? end if; ? ????????? end loop; ???? --關(guān)閉光標(biāo) ???? close cemp; ???? dbms_output.put_line('漲工資人數(shù):'||countEmp||? '資總額:'||salTotal); ?? end;
--漲工資
if salTotal+psal*1.1<50000 then
這個(gè)漲工資條件，不應(yīng)該是漲后總工資嗎？
漲后總工資=漲前總工資+漲的工資數(shù)額
應(yīng)該是：if salTotal+psal*0.1<50000 then 把？
求解答。。
（小白一枚，個(gè)人見解）
2017-12-14
declare
?? cursor cemp is select empno ,sal from emp order by sal;
?? --定義參數(shù)
?? pempno emp.empno%type;
?? psal emp.sal%type;
?? countEmp number :=0;
?? salTotal number;
?? begin
???? --得到工資總額的初始值
???? select? sum(sal) into salTotal from emp;
???? --打開光標(biāo)
???? open cemp;
????????? loop
????????????? -- 1.工資總額>5w
????????????? exit when salTotal>50000;
????????????? --取一個(gè)員工漲工資
????????????? fetch? cemp into? pempno ,psal;
????????????? --2.%notfound
????????????? exit when cemp%notfound;
????????????? --漲工資
????????????? if salTotal+psal*1.1<50000 then
????????????? update? emp set sal = sal*1.1 where empno=pempno;
????????????? --漲工資的人數(shù)
????????????? countEmp := countEmp+1;
????????????? --漲后的工資總額
????????????? salTotal := salTotal+psal*0.1;
????????????? else exit;
????????????? end if; ?
????????? end loop;
???? --關(guān)閉光標(biāo)
???? close cemp;
???? dbms_output.put_line('漲工資人數(shù):'||countEmp||? '資總額:'||salTotal);
?? end;
2019-07-16
不需要用if 直接加個(gè)when就可以了