Here's an example of a PL/SQL procedure that takes an employee_id as input from the employees table, calculates a 10% tax on each employee's salary using a local function, and returns the before tax and after tax amounts using a VARRAY type:
Example:
CREATE OR REPLACE PROCEDURE CalculateTax(p_employee_id IN employees.employee_id%TYPE)
AS
v_before_tax NUMBER;
v_after_tax NUMBER;
--use of local data type as varray--
TYPE AmountsType is VARRAY(2) OF NUMBER;
--use of local function to return type as varray--
FUNCTION CalculateTaxAmount(p_salary IN NUMBER) RETURN AmountsType
IS
v_amounts AmountsType := AmountsType();
BEGIN
v_amounts.EXTEND(2);
v_amounts(1) := p_salary;
v_amounts(2) := p_salary * 0.9; -- 10% tax
RETURN v_amounts;
END CalculateTaxAmount;
BEGIN
SELECT salary INTO v_before_tax FROM employees
WHERE employee_id = p_employee_id;
--calling function inside procedure--
v_before_tax:=CalculateTaxAmount(v_before_tax)(1);
v_after_tax := CalculateTaxAmount(v_before_tax)(2); -- Get the after-tax amount
DBMS_OUTPUT.PUT_LINE('Before Tax Amount: ' || v_before_tax);
DBMS_OUTPUT.PUT_LINE('After Tax Amount: ' || v_after_tax);
END CalculateTax;
--call the procedure:
begin
CalculateTax(100); --employee_id=100 and salary is 24000
end;
--output:
Before Tax Amount: 24000
After Tax Amount: 21600
