create or replace function change_sal (p_job varchar2) return number as v_result number(7,2) ; begin if p_job = 'CLERK' then v_result := 0.1 ; elsif p_job = 'SALESMAN' then v_result := 0.15 ; elsif p_job = 'MANAGER' then v_result := 0.2 ; end if; return v_result ; end; declare result number(7,2) := 0 ; begin result := change_sal('CLERK'); DBMS_OUTPUT.put_line('0'||result); declare v_job varchar2 (100) ; v_empno varchar2(20); v_ename varchar2(60); r_ratio number(7,2); cursor c_emp is select job , empno,ename from scott.emp for update ; open c_emp; loop fetch c_emp into v_job,v_empno,v_ename ; exit when c_emp%notfound; r_ratio := change_sal(v_job); update scott.emp set sal = sal * ( 1 + r_ratio ) where current of c_emp; DBMS_OUTPUT.put_line('已經為員工 '|| v_empno||':'||v_ename||' 成功加薪!'); end loop; close c_emp; exception when others then DBMS_OUTPUT.put_line('沒有找到員工資訊!'); |