Creating PL/SQL Functions Within a Procedure or Code Block

Svetlana Komarova

Svetlana Komarova

The author. System Administrator, Oracle DBA. Information technology, internet, telecom. More details.

PL/SQL Functions Within a ProcedureProblem

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.



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 sal *  .08;


-- function for federal tax

 FUNCTION calc_federal (sal IN NUMBER) 



    RETURN sal *  .12;



  DBMS_OUTPUT.PUT_LINE('Calculating paycheck with taxes');

  INTO emp_rec
  FROM employees
  WHERE employee_id = emp_id;

  paycheck_total := emp_rec.salary - calc_state(emp_rec.salary) -

  DBMS_OUTPUT.PUT_LINE('The paycheck total for ' || emp_rec.last_name ||
    ' is ' || paycheck_total);


      'No matching employee for the given ID');



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.


Вас заинтересует / Intresting for you:

Describe the Basic PL/SQL Prog...
Describe the Basic PL/SQL Prog... 889 views Андрей Волков Wed, 12 Sep 2018, 15:23:39
PL/SQL package: Collecting Rel...
PL/SQL package: Collecting Rel... 648 views sepia Sat, 01 Dec 2018, 10:54:57
Why Oracle DBAs learn PL/SQL a...
Why Oracle DBAs learn PL/SQL a... 866 views Андрей Волков Wed, 12 Sep 2018, 14:43:12
Creating a PL/SQL Stored Funct...
Creating a PL/SQL Stored Funct... 747 views Игорь Воронов Sun, 09 Sep 2018, 06:09:21