作业sm_sal和提成.txt

来自「orale培训教材包括了所有的sql说明和实例」· 文本 代码 · 共 28 行

create or replace procedure sm_setsalary2 ( p_id in char) is
  v_totalsal number:=0; 
  ticheng number:=0;
begin 

  select nvl(sum(totalprice),0) into v_totalsal from sm_saleorderlist 
  where employid=p_id;
  
  -- 没有此雇员的销售业绩。v_totalsal为0.工资为sallary.
  
  if v_totalsal < 100 then 
    ticheng:=0;
  elsif v_totalsal < 200 then
    ticheng := 0.1;
  elsif v_totalsal < 300 then
    ticheng := 0.15;
  else
    ticheng := 0.25;
  end if;
  
  update sm_emp set salary =salary + v_totalsal*ticheng where empid=p_id;
  commit;
end;

call sm_setsalary2('0000000001');
--测试数据
select sum(totalprice) from sm_saleorderlist 
  where employid='0000000001';