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.
A SQL user typically constructs a query to perform an operation on data in the database. Although several queries can be executed
in sequence, the fundamental programming unit in SQL is a single statement. A PL/SQL user typically creates a program that includes
variable declaration and assignment, conditional statements (e.g. IF/ELSE statements), and loops (e.g. FOR or WHILE statements). The program can
contain many SQL statements, including statements that are dynamically generated. In addition, PL/SQL provides mechanisms to seamlessly integrate
SQL and so effectively extends the capabilities present in SQL alone.
Many languages can be used with SQL embedded in them, but PL/SQL executes within the database itself. Although there are other languages
that can execute within the Oracle database (Java for instance), they are simply general purposes languages that are available in this environment.
PL/SQL was specifically designed for to interact closely with SQL within the Oracle 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 stored procedure or simply procedure is essentially an encapsulated named block of code
stored within the database. This distinguishes it from an anonymous block which is an unnamed block
of PL/SQL code which is not stored in the Oracle database. Although an anonymous block is not stored
within the database, it is executed within the database.
A function is distinguished from a procedure in that it is able to return a value.
Both functions and procedures can take parameters and are called interactively. A procedure
cannot be called directly from SQL. A function (in most cases) can be called from a SQL
A trigger cannot take parameters and is called automatically when a database
event occurs. Examples of triggering events include the execution of DML statements (an insert, update
or delete) or DDL statements (create, alter, drop). A Package serves as a container for
functions, procedures and PL/SQL types.
One or more parameters can be specified for stored procedures or functions. A parameter is created
with a name, data type, and mode. As PL/SQL is tightly integrated with SQL, PL/SQL data types are a
superset of SQL datatypes. The parameter mode determines a parameter's behavior. It can be
input only (IN),
output only (OUT), or
input and output (IN OUT). Therefore,
even though a procedure cannot directly return a value, a similar effect is possible using an output
The following table summarizes distinguishing characteristics of PL/SQL anonymous blocks and subprograms.
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.
In this lesson, you will learn about database triggers.
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.