Creating PL/SQL Functions Within a Procedure or Code Block

Светлана Комарова

Светлана Комарова

Автор статьи. Системный администратор, Oracle DBA. Информационные технологии, интернет, телеком. Подробнее.

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.

 

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.

 

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

Describe the Basic PL/SQL Prog...
Describe the Basic PL/SQL Prog... 1726 views Андрей Волков Wed, 12 Sep 2018, 15:23:39
PL/SQL package: Collecting Rel...
PL/SQL package: Collecting Rel... 1377 views sepia Sat, 01 Dec 2018, 10:54:57
Why Oracle DBAs learn PL/SQL a...
Why Oracle DBAs learn PL/SQL a... 1630 views Андрей Волков Wed, 12 Sep 2018, 14:43:12
Creating a PL/SQL Stored Funct...
Creating a PL/SQL Stored Funct... 1458 views Игорь Воронов Sun, 09 Sep 2018, 06:09:21
Comments (0)
There are no comments posted here yet
Leave your comments
Posting as Guest
×
Suggested Locations