Answer to Question #268397 in Databases | SQL | Oracle | MS Access for tumi

Question

Question 6                                                                                                                                                         [15]

A.     Create a function called calc_tax that receives an employee’s salary as a parameter and then returns 15% of that amount as tax.                                                                       (5)                              

B.     Create a procedure called display_tax that receives a staff number as a parameter, computes the corresponding payable tax by making use of the function created in Question 7A, and then displays information as shown in the sample below. Execute the procedure using any staff number. Show how you would do this.                                                                                                                 (10)

READY works as a LIBRARIAN, earn          R24000 and pays          R3600 as

tax

PL/SQL procedure successfully completed.

 

Answer 1

A.

 CREATE FUNCTION calc_tax (empl_salary IN NUMBER)  
RETURN number IS 
   tax number(2) := 0; 
BEGIN 
   tax:=empl_salary*0.15
    
   RETURN tax; 
END; 

B.

CREATE OR REPLACE PROCEDURE display_tax (staff_num IN NUMBER) AS 
BEGIN
   SELECT name, occupation, salary
    FROM EMPLOYEES
       WHERE staff_num=n;
        tax:= calc_tax (salary);
 DBMS_OUTPUT.PUT_LINE (name 'works as a' occupation ', earn' salary
  'and pays' tax 'as tax. PL/SQL procedure successfully completed.');
    END;