PL/SQL package: Collecting Related Routines into a Single Unit

Collecting Related Routines into a Single PL/SQL packageProblem

You have a number of procedures and functions that formulate an entire application when used together. Rather than defining each subprogram individually, you prefer to combine all of them into a single, logically related entity.


Create a PL/SQL package that in turn declares and defines each of the procedures together as an organized entity. You declare each of the subprograms in the package specification (otherwise known as a header) and define them in the package body.

The following example shows the creation of a PL/SQL package containing two procedures and a variable.

First, you create the package specification:

CREATE OR REPLACE PACKAGE process_employee_time IS

  total_employee_salary              NUMBER;

  PROCEDURE grant_raises(pct_increase IN NUMBER,
                         upper_bound IN NUMBER);

  PROCEDURE increase_wage (empno_in IN NUMBER,
                           pct_increase IN NUMBER,
                           upper_bound IN NUMBER) ;


The specification lists the procedures, functions, and variables that you want to be visible from outside the package. Think of the specification as the external interface to your package.

Next, create the package body:


  PROCEDURE grant_raises (pct_increase IN NUMBER,
                          upper_bound IN NUMBER) as

  CURSOR emp_cur is
     SELECT employee_id, first_name, last_name
     FROM employees;


  -- loop through each record in the employees table
  FOR emp_rec IN emp_cur LOOP

      DBMS_OUTPUT.PUT_LINE(emp_rec.first_name || ' ' || emp_rec.last_name);
      increase_wage(emp_rec.employee_id, pct_increase, upper_bound);




                          pct_increase IN NUMBER,
                          upper_bound IN NUMBER) AS
  emp_count    NUMBER := 0;
  emp_sal      employees.salary%TYPE;  
  Results   VARCHAR2(50);


  SELECT count(*)
  INTO emp_count
  FROM employees
  WHERE employee_id = empno_in;

  IF emp_count > 0 THEN

    SELECT salary
    INTO emp_sal
    FROM employees
    WHERE employee_id = empno_in;   

    IF emp_sal < upper_bound AND round(emp_sal + 
      (emp_sal * pct_increase), 2) <upper_bound 


        UPDATE employees
        SET salary = round(salary + (salary * pct_increase),2)
        WHERE employee_id = empno_in; 
        results := 'SUCCESSFUL INCREASE';
    END IF;

    Results := 'NO EMPLOYEE FOUND';




The package in this example declares a global variable and two procedures within the package specification. The package body then defines both of the procedures and assigns a value to the variable that was declared in the specification. Procedures defined within the package body are defined in the same manner as they would be if they were stand-alone procedures. The difference is that now these two procedures are contained in a single package entity and are therefore related to each other and can share variables declared globally within the package.


How It Works

A PL/SQL package can be useful for organizing code into a single construct. Usually the code consists of a grouping of variables, types, cursors, functions, and procedures that perform actions that are logically related to one another. Packages consist of a specification and a body, both of which are stored separately in the data dictionary. The specification contains the declarations for each of the variables, types, subprograms, and so on, that are defined in the package. The body contains the implementations for each of the subprograms and cursors that are included in the specification, and it can also include implementations for other functions and procedures that are not in the specification. You'll learn more about this in other recipes.

Most packages contain both a specification and a body, and in these cases the specification acts as the interface to the constructs implemented within the body. The items that are included in the specification are available to the public and can be used outside the package. Not all packages contain a body. If there are only declarations of variables or constants in the package, then there is no need for a body to implement anything. Other PL/SQL objects outside the package can reference any variables that are declared in the specification. In other words, declaring a variable within a PL/SQL package specification essentially creates a global variable.

Note Global variables should be used wisely. The use of global variables can complicate matters when tracking down problems or debugging your code. If global variables are used, then it can be hard to determine where values have been set and where initialization of such variables occurs. Following the rules of encapsulation and using local variables where possible can make your life easier.

Procedures and functions defined within the package body may call each other, and they can be defined in any order as long as they have been declared within the package specification. If any of the procedures or functions have not been declared in the specification, then they must be defined in the package body prior to being called by any of the other procedures or functions.

You can change any implementations within a package body without recompiling the specification. This becomes very important when you have other objects in the database that depend on a particular package because it is probably not a good idea to change a package specification during normal business hours when a package is in use by others. Doing so may result in unusable objects, and the package users could begin to see errors. However, if changes need to be made to the code within the package body, then you can change that code without affecting public-facing constructs of a package.

Packages are one of the most important constructs that you can create in PL/SQL. You will use packages to combine common code objects for almost any significant application that you write. It is possible to create entire applications without the use of a package, but doing so can create a maintenance nightmare because you will begin to see a pool of procedures and functions being created within your database, and it will be difficult to remember which constructs are used for different tasks. Packages are especially handy when writing PL/SQL web applications.


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

Describe the Basic PL/SQL Prog...
Describe the Basic PL/SQL Prog... 717 views Андрей Волков Wed, 12 Sep 2018, 15:23:39
Why Oracle DBAs learn PL/SQL a...
Why Oracle DBAs learn PL/SQL a... 703 views Андрей Волков Wed, 12 Sep 2018, 14:43:12
Introduction to PL/SQL
Introduction to PL/SQL 988 views Antoniy Wed, 12 Sep 2018, 15:18:13
Translating PL/SQL to JavaScri...
Translating PL/SQL to JavaScri... 908 views Гвен Sun, 03 Jun 2018, 11:54:40