Stored Procedures, Functions and Packages

Stored Procedures and Functions

Procedures and functions are schema objects that logically group a set of SQL and other PL/SQL programming language statements together to perform a specific task. Procedures and functions are created in a user's schema and stored in a database for continued use.

Packages

A package is a group of related procedures and/or functions, together with the cursors and variables they use, stored together in the database for continued use as a unit. Similar to standalone procedures and functions, packaged procedures and functions can be called explicitly by applications or users.

Benefits of Procedures
Procedures provide advantages in the following areas.
Security

Stored procedures can help enforce data security. You can restrict the database operations that users can perform by allowing them to access data only through procedures and functions that execute with the definer's privileges. When a user invokes the procedure, the procedure executes with the privileges of the procedure's owner. Users who have only the privilege to execute the procedure (but not the privileges to query, update, or delete from the underlying tables) can invoke the procedure, but they cannot manipulate table data in any other way.

Performance

Stored procedures can improve database performance in several ways:

• The amount of information that must be sent over a network is small compared to issuing individual SQL statements or sending the text of an entire PL/SQL block to Oracle, because the information is sent only once and thereafter invoked when it is used.
• A procedure's compiled form is readily available in the database, so no compilation is required at execution time.
• If the procedure is already present in the shared pool of the SGA, retrieval from disk is not required, and execution can begin immediately.
• Memory Allocation

Because stored procedures take advantage of the shared memory capabilities of Oracle, only a single copy of the procedure needs to be loaded into memory for execution by multiple users. Sharing the same code among many users results in a substantial reduction in Oracle memory requirements for applications.

Productivity

Stored procedures increase development productivity. By designing applications around a common set of procedures, you can avoid redundant coding and increase your productivity.

Integrity

Stored procedures improve the integrity and consistency of your applications. By developing all of your applications around a common group of procedures, you can reduce the likelihood of committing coding errors.

Standalone Procedures

Stored procedures not defined within the context of a package are called standalone procedures. Procedures defined within a package are considered a part of the package.

Benefits of Packages

Packages are used to define related procedures, variables, and cursors and are often implemented to provide advantages in the following areas:

• Encapsulation of related procedures and variables

Stored packages allow you to encapsulate (group) related stored procedures, variables, datatypes, and so forth in a single named, stored unit in the database. This provides for better organization during the development process.
Encapsulation of procedural constructs in a package also makes privilege management easier. Granting the privilege to use a package makes all constructs of the package accessible to the grantee.

• Declaration of public and private procedures, variables, constants, and cursors

The methods of package definition allow you to specify which variables, cursors, and procedures are
Public:  Directly accessible to the user of a package.
Private: Hidden from the user of a package.

• Better performance

An entire package is loaded into memory when a procedure within the package is called for the first time. This load is completed in one operation, as opposed to the separate loads required for standalone procedures. Therefore, when calls to related packaged procedures occur, no disk I/O is necessary to execute the compiled code already in memory.

• Encapsulation

A package body can be replaced and recompiled without affecting the specification. As a result, schema objects that reference a package's constructs (always via the specification) need not be recompiled unless the package specification is also replaced. By using packages, unnecessary recompilation can be minimized, resulting in less impact on overall database performance.

References:

http://asktom.oracle.com/
http://www.csee.umbc.edu/
http://www.tutorialspoint.com/plsql/