Oracle 10g Tutorials - Identifying PL/SQL Objects

Oracle 10g Tutorials - Identifying PL/SQL Objects

In this tutorial you will learn about indentifying PL/SQL Objects like Packages, Procedures and Functions using SQL Plus based examples and illustrations.

Document Summary

This document is a simple guide to understanding PL/SQL objects such as stored procedures, functions, triggers etc. This guide provides insight into creating the above-mentioned objects using SQL Plus.

Identifying PL/SQL Objects

PL/SQL objects are named blocks of PL/SQL with declarative, executable and exception handling sections. The PL/SQL objects are stored in the data dictionary with a name and can be reused. PL/SQL objects include Packages, Stored Procedures, Functions and triggers.

Packages:

A Package is a PL/SQL construct that allows related objects to be stored together. Apart from grouping related objects, packages also provide many more advantages such as improved performance and ability to reference the PL/SQL objects contained within a package from other PL/SQL blocks.

A Package consists of two portions:

a) Package Specification

Package Specification consists of information about the contents of the package. Essentially, package specification contains ‘forward declarations’ of the procedures and functions in that package but does not contain the codes for any of these subprograms.

The syntax for creating package specification is as below:

CREATE [OR REPLACE] PACKAGE package_name {IS/AS}
type_definition/
procedure_specification/
function_specification/
variable_declaration/
exception_declaration/
cursor_declaration/
pragma_declaration
END [package_name];

b) Package Body

Package Body contains the actual code for the subprograms in the package. Package body is a separate data dictionary object from the package header. The package body cannot be compiled without the package specification being compiled successfully. The package body should compulsorily contain the definition for all sub program definitions contained in the package specification.

The syntax for creating package specification is as below:

CREATE [OR REPLACE] PACKAGE BODY package_name {IS/AS}
procedure_definitions
function_definitions
END [package_name];

Below is an example of creating a package:

Figure -1 Package Creation

Procedures:

A Procedure is a named PL/SQL block with declarative section, an executable section and an exception handling section. Procedures are stored in the data dictionary and are executed whenever they are invoked from another PL/SQL block. Procedures can be called from any PL/SQL block and arguments can be passed while calling. A procedure call is a PL/SQL statement and cannot be a part of an expression.

The syntax for creating a procedure is as below:

CREATE [OR REPLACE] procedure procedure_name
[(argument_1[{IN/OUT/IN OUT}] data_type,
…
…
…
…
argument_n[{IN/OUT/IN OUT}] data_type)] {IS/AS}
/*declarative section*/
BEGIN
/*executable section*/
EXCEPTION
/*exception section*/
END [procedure_name];

Below is an example of creating a procedure:

Figure -2 Procedure Creation

Functions:

A Function, just like a procedure, is a named PL/SQL block that has an executable section and an exception handling section. Functions also, are stored in the data dictionary and can be called from other PL/SQL blocks when required. Functions can also have parameters and the arguments can be passed while calling them.

There are some differences between procedures and functions though. Functions can only be called as a part of an expression (as opposed to procedure calls which is a PL/SQL statement by itself). Function body contains a ‘return’ statement that returns the result of function execution to the calling expression. So, the function has a return type that has to be declared in the function header.

The syntax for creating a function is as below:

CREATE [OR REPLACE] FUNCTION function_name
[( argument_1 [{IN/OUT/IN OUT}] data_type,
…
…
…
…
argument_n [{IN/OUT/IN OUT}] data_type,)]
RETURN return_type {IS/AS}
/*declarative section*/
BEGIN
/*executable section*/
RETURN expression;
EXCEPTION
/*exception section*/
END [function_name];

Below is an example of creating a function:

Figure -3 Function Creation