Oracle PL/SQL is designed to extend and integrate with Oracle SQL. While SQL itself is powerful and can accomplish many tasks, it is a set-oriented language designed to obtain and manipulate data in a relational database system (RDBMS). Even though Oracle SQL includes additional functionality unavailable in ANSI standard SQL, it lacks the features available in procedural languages. A procedural language provides the ability to assign values to variables, execute statements in sequence, iterate, and conditionally process commands. Hence, PL/SQL (or Procedural Language extensions to the Structured Query Language) was designed by Oracle to provide such features within the database.
In PL/SQL code groups of commands are arranged within a block.
A block groups related declarations or statements.
A block can be contained within another block or in a larger programming unit called a subprogram.
A subprogram can be a stored procedure, function, trigger or package.
A PL/SQL block can contain an optional section where types and variables are defined.
These items are accessed and manipulated in the executable section of the block. This chapter describes
the types of variables available and how to create, access, and modify the values that variables contain.
PL/SQL - by definition - is a procedural language. It is specifically a procedural extension to SQL. A procedural (or imperative) language
is used to specify steps to be taken to reach a desired state. Often the steps involved differ based on the values of the variables in the program.
PL/SQL includes several control structures to vary the execution flow.
Conditional statements are used to differentiate the execution path taken (or steps taken to reach the desired state) based upon the value
of one or more variables. Iterative processing (or looping)
is used to represent repeated steps. Conditional statements and loops can be nested arbitrarily to produce many different possible results of running
the same program.
It is important to distinguish the various types of error conditions that might exist in PL/SQL.
A compile time error occurs when there is a problem with PL/SQL code at the time it is
saved to the database. A run time error occurs when a syntactically valid program
encounters a problem during program execution. Many run time errors can be dealt with in the EXCEPTION
section of a PL/SQL block. An exception handler is the special code implemented to handle a given
A cursor is a control structure used to traverse and process database records in a result set. Every SQL statement
involves the use of a cursor. Since PL/SQL is tightly integrated with SQL, the ability to reference and control cursors is
a significant feature of the language. Cursors can be explicit or implicit. An implicit cursor is used
when assigning a value to a variable using the SELECT INTO syntax. An implicit cursor is also created
when a DML statement (INSERT/UPDATE/DELETE) is executed. An explicit cursor must be declared for SQL statements that
return more than one row.
You have already been introduced to PL/SQL blocks. Blocks are the fundamental structure within which PL/SQL code is written.
They can be nested inside of each other to organize code, to limit variable scope or visibility,
or to affect exception handling. Scope is the context in which a given variable can be accessed within a program.
Scope is relevant when considering where a variable will be declared and where it will be referenced. Depending upon where it
is declared, a variable might be accessible using its name, accessible using a qualified name or not accessible at all.
Both traditional variables and exceptions are affected by scope considerations. In addition, the use of the EXCEPTION
section of a block can affect the way errors are handled by your program.
Up to this point, we have dealt primarily with anonymous blocks. These scripts are chunks of PL/SQL code
that can be used to accomplish a wide variety of tasks. However, since PL/SQL is tightly integrated with SQL,
it makes sense that code be available within the database itself. Subprograms provide all of the functionality
possible in anonymous blocks but are stored in the database.
The focus of this lesson is to describe the basic creation and maintenance of subprograms. All of the PL/SQL you
have learned up to this point can be used in the context of such programs, but the examples will be simple
demonstration programs that focus on the distinctive characteristics of Oracle functions and stored procedures.
Subprograms serve as independent programming units but can be coupled together to produce complex applications.
In order for subprograms to be useful and to interact with people and programs they need to be able to receive data, process it and send back results.
As an application grows in size and complexity it becomes increasingly important to structure
units of application code. Well organized code is easier to debug and maintain.
Procedures and functions are the basic building blocks of PL/SQL code
that are stored in the database. In this lesson, you will learn
ways to organize these building blocks into groups of related functionality called packages.
PL/SQL functions as an extension of SQL and operates within the database environment.
Oracle has provided a great deal of flexibility in the ways that PL/SQL can reference
and operate upon data. Various data retrieval techniques available are discussed and
demonstrated in this lesson.
The Oracle database goes far beyond providing the base functionality expected
in a standard RDBMS. Procedural programming is available through the use of PL/SQL. In addition,
Oracle includes a large number of packages that are included with a standard
database installation. Many other paid options available in Oracle
are implemented (in full or in part) as PL/SQL packages.
The features and options available in Oracle increase with every version. Some of these features
are invisible to the end user and are enhancements to the availability, recoverability and performance
of the database. However, many options provide application developers and data analysts new options
for application design and data analysis. A few of these options are mentioned below along with guidelines
to consider when creating applications that interact with the Oracle database.