Advantage/Usage of Functions, Procedures and Packages

Subprogram - Procedure and Function

A subprogram is a program unit/module that performs a particular task. These subprograms are combined to form larger programs. This is basically called the 'Modular design'. A subprogram can be invoked by another subprogram or program, which is called the calling program.

A subprogram can be created:

• At schema level
• Inside a package
• Inside a PL/SQL block

A schema level subprogram is a standalone subprogram. It is created with the CREATE PROCEDURE or CREATE FUNCTION statement. It is stored in the database and can be deleted with the DROP PROCEDURE or DROP FUNCTION statement.

A subprogram created inside a package is a packaged subprogram. It is stored in the database and can be deleted only when the package is deleted with the DROP PACKAGE statement. 

To create a function in your own schema, you must have the CREATE PROCEDURE system privilege. To create a function in another user's schema, you must have the CREATE ANY PROCEDURE system privilege. To replace a function in another user's schema, you must have the ALTER ANY PROCEDURE system privilege.

To invoke a call specification, you may need additional privileges, for example, EXECUTE privileges on a C library for a C call specification.

To embed a CREATE FUNCTION statement inside an Oracle precompiler program, you must terminate the statement with the keyword END-EXEC followed by the embedded SQL statement terminator for the specific language.

PL/SQL Package

PL/SQL packages are schema objects that groups logically related PL/SQL types, variables and subprograms.

A package will have two mandatory parts:

• Package specification
• Package body or definition

Package Specification

The specification is the interface to the package. It just DECLARES the types, variables, constants, exceptions, cursors, and subprograms that can be referenced from outside the package. In other words, it contains all information about the content of the package, but excludes the code for the subprograms.

All objects placed in the specification are called public objects. Any subprogram not in the package specification but coded in the package body is called a private object.

Package Body

The package body has the codes for various methods declared in the package specification and other private declarations, which are hidden from code outside the package.

The CREATE PACKAGE BODY Statement is used for creating the package body. The following code snippet shows the package body declaration for the cust_sal package created above. I assumed that we already have CUSTOMERS table created in our database as mentioned in PL/SQL - Variableschapter.

Advantages of Packages:

Packages have a long history in software engineering, offering important features for reliable, maintainable, reusable code, often in team development efforts for large systems.

Modularity

Packages let you encapsulate logically related types, items, and subprograms in a named PL/SQL module. Each package is easy to understand, and the interfaces between packages are simple, clear, and well defined. This aids application development.

Easier Application Design

When designing an application, all you need initially is the interface information in the package specs. You can code and compile a spec without its body. Then, stored subprograms that reference the package can be compiled as well. You need not define the package bodies fully until you are ready to complete the application.

Information Hiding

With packages, you can specify which types, items, and subprograms are public (visible and accessible) or private (hidden and inaccessible). For example, if a package contains four subprograms, three might be public and one private. The package hides the implementation of the private subprogram so that only the package (not your application) is affected if the implementation changes. This simplifies maintenance and enhancement. Also, by hiding implementation details from users, you protect the integrity of the package.

Added Functionality

Packaged public variables and cursors persist for the duration of a session. They can be shared by all subprograms that execute in the environment. They let you maintain data across transactions without storing it in the database.

Better Performance

When you call a packaged subprogram for the first time, the whole package is loaded into memory. Later calls to related subprograms in the package require no disk I/O.

Packages stop cascading dependencies and avoid unnecessary recompiling. For example, if you change the body of a packaged function, Oracle does not recompile other subprograms that call the function; these subprograms only depend on the parameters and return value that are declared in the spec, so they are only recompiled if the spec changes

Advantages of Subprogram:

• Subprograms let you extend the PL/SQL language. Procedures act like new statements. Functions act like new expressions and operators.
• Subprograms let you break a program down into manageable, well-defined modules. You can use top-down design and the stepwise refinement approach to problem solving.
• Subprograms promote reusability. Once tested, a subprogram can be reused in any number of applications. You can call PL/SQL subprograms from many different environments, so that you do not have to reinvent the wheel each time you use a new language or API to access the database.
• Subprograms promote maintainability. You can change the internals of a subprogram without changing other subprograms that call it. Subprograms play a big part in other maintainability features, such as packages and object types.
• Dummy subprograms (stubs) let you defer the definition of procedures and functions until after testing the main program. You can design applications from the top down, thinking abstractly, without worrying about implementation details.

Usage of Functions:

Restrictions on User-Defined Functions

• User-defined functions cannot be used in situations that require an unchanging definition. Thus, you cannot use user-defined functions:
  • In a CHECK constraint clause of a CREATE TABLE or ALTER TABLE statement
  • In a DEFAULT clause of a CREATE TABLE or ALTER TABLE statement
• In addition, when a function is called from within a query or DML statement, the function cannot:
  • Have OUT or IN OUT parameters
  • Commit or roll back the current transaction, create a savepoint or roll back to a savepoint, or alter the session or the system. DDL statements implicitly commit the current transaction, so a user-defined function cannot execute any DDL statements.
  • Write to the database, if the function is being called from a SELECT statement. However, a function called from a subquery in a DML statement can write to the database.
  • Write to the same table that is being modified by the statement from which the function is called, if the function is called from a DML statement.

Here are some particularly noteworthy advantages of placing SQL within Oracle stored procedures and packages:

• High productivity:  PL/SQL is a language common to all Oracle environments. Developer productivity is increased when applications are designed to use PL/SQL procedures and packages because it avoids the need to rewrite code.  Also, the migration complexity to different programming environments and front-end tools will be greatly reduced because Oracle process logic code is maintained inside the database with the data, where it belongs.  The application code becomes a simple “shell” consisting of calls to stored procedures and functions.

• Improved Security:  Making use of the “grant execute” construct, it is possible to restrict access to Oracle, enabling the user to run only the commands that are inside the procedures. For example, it allows an end user to access one procedure that has a command delete in one particular table instead of granting the delete privilege directly to the end user. The security of the database is further improved since you can define which variables, procedures and cursors will be public and which will be private, thereby completely limiting access to those objects inside the PL/SQL package.  With the “grant” security model, back doors like SQL*Plus can lead to problems; with “grant execute” you force the end-user to play by your rules.

• Application portability:  Every application written in PL/SQL can be transferred to any other environment that has the Oracle Database installed regardless of the platform.  Systems that consist without any embedded PL/SQL or SQL become “database agnostic” and can be moved to other platforms without changing a single line of code.

• Code Encapsulation: Placing all related stored procedures and functions into packages allows for the encapsulation of storage procedures, variables and datatypes in one single program unit in the database, making packages perfect for code organization in your applications.

• Global variables and cursors:  Packages can have global variables and cursors that are available to all the procedures and functions inside the package.