Oracle and PL/SQL Recipes: A Problem-Solution Approach

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 ...