, , ,

Subprograms

Subprograms are named PL/SQL blocks that can take parameters and be invoked. PL/SQL has two types of subprograms called procedures and functions. Generally, you use a procedure to perform an action and a function to compute a value.


 

Uses of Procedures/Functions
Procedures are excellent for defining a PL/SQL code block that you know you will need to call more than once, and whose work may produce results largely seen in the database or perhaps some module, like an Oracle Form, or a client-side form, as opposed to work whose result is some single answer; that would probably be more appropriate for a function.

In addition, an anonymous PL/SQL block is parsed each time it is submitted for execution. But if that same anonymous block is assigned a name and created as a procedure, then Oracle will parse the procedure once, at the time it is created. Each subsequent call to that procedure will not require reparsing; it will simply execute, saving time over an anonymous block.
A PL/SQL procedure can be invoked from a single executable statement in another PL/SQL statement. These other PL/SQL statements could be in an anonymous PL/SQL block or in a named program unit, such as another procedure. A PL/SQL procedure can also be invoked from a single command-line executable statement in a SQL*Plus session.

A function‘s main purpose is to return a single value of some sort, as opposed to a procedure, whose main purpose is to perform some particular business process. Like a procedure, a function is a PL/SQL block that’s been assigned a name; but unlike a procedure, the function will always return one—and only one—value of some sort. This returned value is embodied in the function call in such a way that the function becomes, in essence, a variable.
When you create a function, you must consider how you intend to use the function. There are two major categories of functions you can create:

  • Functions that are called from expressions in other PL/SQL program units. Any function can be used this way.
  • Functions that are called from within SQL statements, whether the SQL statement is part of a PL/SQL program unit or not. Some functions you create in PL/SQL can be used in this way.

It’s possible to create functions that can be invoked in both manners. However, if you intend to make a function that can be called from a valid SQL statement, there are some restrictions you have to consider. For example, a function that returns a BOOLEAN datatype, which is perfectly acceptable in PL/SQL, cannot be invoked from a SQL statement, where BOOLEAN datatypes are not recognized.

Functions versus Procedures
Functions can be used in places where procedures cannot be used. Whereas a procedure call is a statement unto itself, a call to a function is not; a function call is part of an expression. This means that functions can be used as a part, or all, of the right side of the assignment statement. Functions can be used as part, or perhaps all, of the Boolean expression in an IF statement. In short, wherever you might use a variable, you can use a function. Functions always return a single value, embodied in the function call itself. In other words, contrary to the optional OUT parameter feature in procedures, which you may or may not use to return multiple values from a procedure, a function must always return one—and only one—value through the very call to the function itself. This value is not returned in the form of an OUT parameter, but instead it is returned in the body of the function itself, so that the function call behaves like a variable. Technically, functions can use IN, OUT, and IN OUT parameters. In practice, functions are generally only given IN parameters.
Where Can You Store Procedures?
Procedures can be stored in the database, alongside tables and other database objects. Once a procedure is stored in the database, it can be invoked from any process with database access. If a process, such as a SQL*Plus window, Java program, Oracle Form, or another PL/SQL procedure, has access to the database, it can execute the procedure, provided that the proper privileges have been granted on the procedure (more on this later) to the schema under which the process is running.

/by
, , ,

Invoking Packaged Constructs

Packages themselves are never directly invoked or executed. Instead, the constructs contained within the package are invoked. For example, procedures and functions within the package are executed. Other constructs in the package, such as constants and other declared constructs, can be referenced from within other PL/SQL programs, in the same manner that those program units could refer to their own locally declared PL/SQL program constructs.
Referencing Packaged Constructs
To call upon any construct of a package, simply use the package name as a prefix to the construct name. For example, to reference a procedure with no parameters called increase_wages that is stored in a package called hr, use this reference:
hr.increase_wages;
All packaged constructs are invoked in the same fashion: by including the package name as a prefix, with a period separating the package name and the construct name. This notation is often referred to as dot notation. It’s the same format used to refer to database objects that are owned by a schema.
For example, if you have a package called assumptions that defines a public constant called tax_rate, you could use it this way in an expression:
v_full_price := v_pre_tax * (1 + assumptions.tax_rate);
Dot notation is required for references to any packaged constructs from outside of the package. However, although references to constructs from within the same package will accept dot notation, they do not require it.
Using Packaged Constructs
For the purpose of reviewing the rules of using packaged constructs, let’s consider all constructs to be in either of two categories: packaged program units, meaning procedures and functions, and global constructs, meaning packaged variables, constants, cursors, exceptions, and types.
Packaged Program Units
The same rules apply to the use of packaged procedures and functions that apply to stand-alone procedures and functions. In other words, packaged procedures must be called from a single statement, whereas packaged functions must be called from within an expression, just like stand-alone PL/SQL stored functions. Parameters may be passed by positional notation or by named notation.
For example, the following PL/SQL block executes a packaged procedure:
BEGIN
  pack_finance.proc_reconcile_invoice(p_status => ‘ACTIVE’);
END;
Packaged Global Constructs
Packaged global constructs generally behave the same as their locally defined counterparts, with one dramatic difference: Their declaration is global to the user sessions.
Consider the following code sample:
PACKAGE rates AS
  bonus NUMBER(3,2);
END rates;
/by
, , ,

Create, Alter and Drop – Packages

The statements used to create, alter, and drop packages are rather straightforward. However, this process is a little more involved than merely creating a procedure or function. The first point to understand is that a package consists of two parts: the package specification and the package body. The two parts are created separately. Any package must have a specification.
A package may optionally include a package body but is not necessarily required to. The requirement for a package to have a body will be determined by what you declare in a package specification; you may simply declare a package specification and no body. However, most packages often include both the package specification and the package body.
You can declare a package specification without the package body and successfully store it in the database as a valid object. Furthermore, with only a package specification, it’s possible to create other PL/SQL programs that call on the constructs of your package, even procedures or functions, whose code isn’t written yet—the actual code can only be defined in the package body. However, the existence of the package specification enables other outside PL/SQL program units to reference the constructs of this package.
It’s recommended that you create the package specification first, before the package body. The package specification, as we have seen, will successfully store, compile, and support the successful compilation of outside program units. A package body, on the other hand, cannot be compiled successfully without a corresponding package specification. However, the package body can be submitted and stored in the data dictionary without a package specification. The package body will simply be flagged with a status of INVALID in the USER_OBJECTS data dictionary view. After the package specification is successfully created, you need to either issue the ALTER PACKAGE … COMPILE statement, or simply reference a packaged construct and let the database automatically compile the package at that time.

Creating a Package Specification
The following is an example of a statement that creates a stored PL/SQL package specification:
CREATE OR REPLACE PACKAGE pack_booking AS
  c_tax_rate NUMBER(3,2) := 0.05;
  CURSOR cur_cruises IS
    SELECT CRUISE_ID, CRUISE_NAME
    FROM   CRUISES;
  rec_cruises cur_cruises%ROWTYPE;
  FUNCTION func_get_start_date
    (p_cruise_id IN CRUISES.CRUISE_ID%TYPE)
  RETURN DATE;
END pack_booking;
The syntax of the statement is the reserved word CREATE, followed by the optional OR REPLACE words, the reserved word PACKAGE, the name you choose for the package, and the reserved word AS. (The word IS is also accepted here.) Next is a series of declared constructs. Eventually, the closing END is included, followed by the package name, optionally repeated for clarity, and then the semicolon.
This package specification declares a constant c_tax_rate, a cursor cur_cruises, a record variable rec_cruises, and the function func_get_start_date. Notice that the function’s actual code isn’t included here, only the function header.
The package specification is the part of a package that declares the constructs of the package. These declared constructs may include any of the following:

  • Variables and/or constants
  • Compound datatypes, such as PL/SQL tables and TYPEs
  • Cursors
  • Exceptions
  • Procedure headers and function headers
  • Comments

The package specification contains no other code. In other words, the actual source code of procedures and functions is never included in the package specification. The specification merely includes enough information to enable anyone who wants to use these constructs to understand their names, parameters, and their datatypes, and in the case of functions, their return datatypes, so that developers who want to write calls to these constructs may do so. In other words, if any developer wants to create new programs that invoke your package constructs, all the developer needs to see is the package specification. That’s enough information to create programs that employ the procedures and functions of your package. The developer does not need to have full access to the source code itself, provided that he or she understands the intent of the program unit.
Once the package specification has been successfully stored in the database, it will be given a status of VALID, even if the associated package body, containing the actual source code of any and all functions and/or procedures, has yet to be stored in the database.
Creating a Package Body
The following is a sample of a package body that correlates to the package specification we saw earlier:
CREATE OR REPLACE PACKAGE BODY pack_booking AS
  FUNCTION func_get_start_date
    (p_cruise_id IN CRUISES.CRUISE_ID%TYPE)
  RETURN DATE
  IS
     v_start_date CRUISES.START_DATE%TYPE;
  BEGIN
     SELECT START_DATE
     INTO   v_start_date
     FROM   CRUISES
     WHERE  CRUISE_ID = p_cruise_id;
     RETURN v_start_date;
  END func_get_start_date;
END pack_booking;
This package body defines the source code of the function func_get_start_date. Notice that the function header is completely represented here, even though it was already declared completely in the package specification. Also, notice that there are no provisions for the other declared constructs in the package specification. Only the functions and/or procedures that were declared in the package specification need to be defined in the package body. The package specification handles the full declaration of the other public constructs, such as variables, constants, cursors, types, and exceptions.
The package body is only required if the package specification declares any procedures and/or functions, or in some cases of declared cursors, depending on the cursor syntax that is used. The package body contains the complete source code of those declared procedures and/or functions, including the headers that were declared in the specification.
The package body can also include privately defined procedures and/or functions. These are program units that are recognized and callable only from within the package itself and are not callable from outside the package. They are not declared in the package specification, but are defined in the package body.
Altering a Package
Packages, like procedures and functions, should be recompiled with the ALTER command if their referenced constructs are changed for any reason. This includes any referenced database objects, such as tables, views, snapshots, synonyms, and other PL/SQL packages, procedures, and functions.
The syntax to recompile a package with the ALTER statement is
ALTER PACKAGE package_name COMPILE;
This statement will attempt to recompile the package specification and the package body.
The syntax to recompile just the package body is
ALTER PACKAGE package_name COMPILE BODY;
Note that the package is listed in the data dictionary with two records: one record for the PACKAGE and another for the PACKAGE BODY. Both have their individual status assignments. The PACKAGE, meaning the package specification, can be VALID, while the PACKAGE BODY is INVALID. If this is the case, then an ALTER PACKAGE package_name COMPILE statement will attempt to restore the entire package, including the body, to a status of VALID.
If a change is made to the package body and it is recompiled, then the package specification does not demand that the package be recompiled. Even if the recreated package body results in a change that is inconsistent with the package specification, the package specification will still show a status of VALID in the data dictionary (assuming it was VALID to begin with), and the package body will be flagged with a status of INVALID
Dropping a Package
You have two options when dropping a package. The following statement will remove the package body reservations from the database:
DROP PACKAGE BODY RESERVATIONS;
This statement will remove the package body, but will leave the package specification in the database. Furthermore, the package specification for reservations will still be VALID.
The following statement will remove the entire package:
DROP PACKAGE RESERVATIONS;
The result of issuing this statement to the database will be the complete removal of both the package specification and the package body from the database.
Dropping a package will cause any other program units that reference the package to be flagged with a status of INVALI
Public versus Private Constructs
Constructs that are declared in the package specification are considered public constructs. However, a package body can also include constructs that aren’t declared in the package specification. These are considered private constructs, which can be referenced from anywhere within its own package body but cannot be called from anywhere outside the particular package body. Furthermore, any developers with privileges to use the constructs of the package do not necessarily have to see the package body, which means that they do not necessarily know of the existence of the private constructs contained within the package body.
Global Constructs
Package constructs, such as variables, constants, cursors, types, and user-defined exceptions, are global to the user session that references them. Note that this dynamic is irrelevant for packaged procedures and packaged functions, but applies to all other packaged constructs. This is true for both public and private constructs in the package. In other words, the values for these constructs will be retained across multiple invocations within the user session.
For example, if an anonymous PL/SQL block references a public packaged variable and changes its value, the changed value can be identified by another PL/SQL block that executes afterwards. Neither block declares the variable because it’s declared as part of the package.
The user cannot directly access any private constructs, such as a variable, but imagine that a user invokes a packaged procedure, for example, that references its own private variable value and changes that value. If the user re-invokes that packaged procedure again within the same user session, the changed value will be recognized by the packaged procedure.
The value will be retained as long as the user session is still active. As soon as the user session terminates, the modified states of the packaged constructs are released, and the next time the user session starts up, the packaged constructs will be restored to their original state, until the user sessions modifies them again.

/by
, , ,

Packages in PL SQL

A PL/SQL package is a single program unit that contains one or more procedures and/or functions, as well as various other PL/SQL constructs such as cursors, variables, constants, and exceptions. Packages bring these various constructs together in a single program unit.
Most applications include several Procedural Language/Structured Query Language (PL/SQL) procedures and functions that are logically related together. These various procedures and functions could be left as stand-alone individual procedures and functions, stored in the database. However, they can also be collected in a package, where they can be more easily organized and where you will find certain performance improvements as well as access control and various other benefits.

A package is a collection of PL/SQL program constructs, including variables, constants, cursors, user-defined exceptions, and PL/SQL procedures and functions, as well as PL/SQL-declared types. A package groups all of these constructs under one name. More than that, the package owns these constructs, and in so doing, affords them powers and performance benefits that would not otherwise exist if these constructs were not packaged together.

/by