4-6. Creating Functions Within a Procedure or Code Block
Problem
You want to create some functions within a stored procedure. You want the functions to be local to the procedure, available only from the procedure's code block.
Solution
Create a stored procedure, and then create functions within the declaration section. The internal functions will accept parameters and return values just as an ordinary stored function would, except that the scope of the functions will be constrained to the outer code block or to the procedure. The procedure that is demonstrated in this solution embodies two functions. One of the functions is used to calculate the federal tax for an employee paycheck, while the other calculates the state tax.
CREATE OR REPLACE PROCEDURE calc_employee_paycheck(emp_id IN NUMBER) as
emp_rec employees%ROWTYPE;
paycheck_total NUMBER;
-- function for state tax
FUNCTION calc_state (sal IN NUMBER)
RETURN NUMBER IS
BEGIN
RETURN sal * .08;
END;
-- function for federal tax
FUNCTION calc_federal (sal IN NUMBER)
RETURN NUMBER IS
BEGIN
RETURN sal * .12;
END;
BEGIN
DBMS_OUTPUT.PUT_LINE('Calculating paycheck with taxes'),
SELECT *
INTO emp_rec
FROM employees
WHERE employee_id = emp_id;
paycheck_total := emp_rec.salary - calc_state(emp_rec.salary) -
calc_federal(emp_rec.salary);
DBMS_OUTPUT.PUT_LINE('The paycheck total for ' || emp_rec.last_name ||
' is ' || paycheck_total);
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR(-20001,
'No matching employee for the given ID'),
END;
How It Works
Functions—and procedures too—can be contained within other bodies of code. Creating a function within a declaration section will make the function accessible to the block that contains it. The declaration of the function is the same as when you are creating a stored function, with the exception of the CREATE OR REPLACE keywords. Any variables that are declared inside the function will be accessible only to that function, not to the containing object.
Creating a function or procedure inside a PL/SQL code block can be useful when you want to make a function that is only to be used by the containing object. However, if you find that the body of the embedded function may change frequently, then coding a separate stored function may prove to be more efficient.