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) ; END;
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:
CREATE OR REPLACE PACKAGE BODY process_employee_time IS 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; BEGIN -- 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); END LOOP; END; PROCEDURE INCREASE_WAGE (empno_in IN NUMBER, pct_increase IN NUMBER, upper_bound IN NUMBER) AS emp_count NUMBER := 0; emp_sal employees.salary%TYPE; Results VARCHAR2(50); BEGIN SELECT count(*) INTO emp_count FROM employees WHERE employee_id = empno_in; IF emp_count > 0 THEN -- IF EMPLOYEE FOUND, THEN OBTAIN RECORD 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 THEN UPDATE employees SET salary = round(salary + (salary * pct_increase),2) WHERE employee_id = empno_in; results := 'SUCCESSFUL INCREASE'; ELSE results := 'EMPLOYEE MAKES TOO MUCH, DECREASE RAISE PERCENTAGE'; END IF; ELSE Results := 'NO EMPLOYEE FOUND'; END IF; DBMS_OUTPUT.PUT_LINE(results); END; END;
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.