PL/SQL-01
create or replace function change_sal (p_job varchar2)return number
as v_result number(7,2) ;
begin
ifp_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);
end;
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 ;
begin
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('没有找到员工信息！');
end;
页:
[1]