1设计一个函数,给定员工的编号,返回该员工所应交纳的税。计算税的公式如下:
Sal<2000 tax=sal*10%
2000<=Sal<=4000 tax=sal*15%
Sa>4000 tax=sal*20%
写一个匿名块调用该函数。2设计一个过程,将给定部门名称的员工sal增加100元。写一个匿名块调用该过程。
在线等答案啊!!! 救命的啊 一题50分啊!!!
Sal<2000 tax=sal*10%
2000<=Sal<=4000 tax=sal*15%
Sa>4000 tax=sal*20%
写一个匿名块调用该函数。2设计一个过程,将给定部门名称的员工sal增加100元。写一个匿名块调用该过程。
在线等答案啊!!! 救命的啊 一题50分啊!!!
CREATE OR REPLACE FUNCTION get_tax(v_empno IN emp.empno%TYPE) RETURN NUMBER AS
v_sal emp.sal%TYPE;
v_tax NUMBER;
BEGIN
SELECT sal INTO v_sal FROM emp WHERE empno = v_empno;
IF v_sal < 2000 THEN
v_tax := v_sal * 10%;
ELSIF v_sal <= 4000 THEN
v_tax := v_sal%15%;
ELSE
v_tax := v_sal%20%;
END IF;
RETURN v_tax;
END get_tax;DECLARE
v_tax NUMBER;
BEGIN
v_tax := call get_tax(7369);
END;DECLARE
v_tax NUMBER;
BEGIN
v_tax := call get_tax(7369);
END;
-- create function to get tax back
create or repalce function getTax(v_in_empno varchar2) return number
is
result number(10,2);
begin
select sal into result from emp where empno = v_in_empno;
if (result < 2000 ) then
result := result * 0.1;
elsif (result <= 4000 ) then
result := result * 0.15;
else
result := result * 0.2;
end if;
return result;
exception
when no_data_found then
return 0;
end;
-- execute function
declare
v_tax number(10,2);
begin
select getTax(111) into v_tax from dual;
end;2.
-- create procedure to add salary of employee
create or replace procedure addTax(v_in_empno varchar2) as
begin
execute immediate 'update emp set sal = sal + 100 where empno = :1' using v_in_empno;
commit;
expception
when others then
rollback;
dbms_output.put_line(sqlcode);
end;-- execute procedure
begin
addTax(111);
end;