PL/SQL is Oracle's procedural programming language. It allows you to use
various control structures and conditional arguments to construct more
complicated logical structures than are available through pure SQL.
All of the standard Oracle SQL functions are available for use, and some have
been extended increasing their functionality or range. Furthermore, some SQL
functions are available as stand alone functions within PL/SQL.
Conditional Statements
IF - THEN - ELSE - END IF
There is a time when you need to test something for its validity. The IF
statement allows you to test something and depending on the result (either true
or false) proceed through the logic of your program.
IF ( 1 = 1 ) THEN
.... proceed with program
END IF;
This can be further extended to allow for processing if the condition is not
met...
IF ( 1 = 1 ) THEN
.... proceed with program
ELSE
.... 1 does not = 1 wow!
END IF;
And even further extended to allow for alternate nested processing if the condition is not
met...and another conditionis to be tested.
IF ( 1 = 1 ) THEN
.... proceed with program
ELSIF ( 1 = 2 ) THEN
.... 1 = 2 wow!
END IF;
If statements can be nested one inside the other - as long as the nested
statement is fully enclosed within one of the processing blocks of the IF or
ELSE sections. Be careful when nesting IF statements as they can be difficult to
read, even if you develop them. If you need more than 3 or 4 levels of nesting
then you should probably rework the code to eliminate this.
An IF statement always evaluates to TRUE or FALSE and several evaluations can
be strung together in a single IF statement using AND or OR.
IF ( 1= 1 ) AND ( 2 = 2 ) THEN
... proceed with program
END IF;
Taking a fairly typical scenario, where we need to test a VARCHAR2 to see if
it contains all numbers, we could attempt to cast the VARCHAR2 to a number using
the TO_NUMBER function and then trap any error in the EXCEPTION section (more
about exception handling later in this course), but that defeats the purpose of
this exercise where we want control to remain within the IF statement. Breaking
down the problem we notice several things:
- We start with a VARCHAR2
- It may (or may not) be all numbers.
Applying several SQL functions against the VARCHAR2 we approach the problem
in a different way. Why not attempt to remove all the number characters and see
if anything is left - such that
IF NVL(LENGTH(LTRIM('12345a','0123456789')),0) = 0 THEN
... we have a number
ELSE
... not a number
END IF;
As can be seen from the above example there may be more than one way to
approach a problem, and invariably there usually is. So explaining above in more
detail gives the following LTRIM the string of any numbers '0123456789'. Then
see if anything is left in the string, hence the length function. The NVL is
there because once the contents of a varchar have been removed it comtains NULL
and its LENGTH is also NULL.
CASE - WHEN - END CASE
Looping
LOOP - END LOOP
WHILE LOOP - END WHILE
Cursors
Program Units
Functions
Procedures
Packages
Triggers
Overview of
PL/SQL
PL/SQL is
the best method available for writing and managing stored procedures that work
with Oracle data. PL/SQL code consists of three subblocks-the declaration
section, the executable section, and the exception handler. In addition, PL/SQL
can be used in four different programming constructs. The types are procedures
and functions, packages, and triggers. Procedures and functions are similar in
that they both contain a series of instructions that PL/SQL will execute.
However, the main difference is that a function will always return one and only
one value. Procedures can return more than that number as output parameters.
Packages are collected libraries of PL/SQL procedures and functions that have an
interface to tell others what procedures and functions are available as well as
their parameters, and the body contains the actual code executed by those
procedures and functions. Triggers are special PL/SQL blocks that execute when a
triggering event occurs. Events that fire triggers include any SQL
statement.
Declaring
and Using Variables
The
declaration section allows for the declaration of variables and constants. A
variable can have either a simple or "scalar" datatype, such as NUMBER or
VARCHAR2. Alternately, a variable can have a referential datatype that uses
reference to a table column to derive its datatype. Constants can be declared in
the declaration section in the same way as variables, but with the addition of a
constant keyword and with a value assigned. If a value is not assigned to a
constant in the declaration section, an
error will occur. In the executable section, a variable can have a value
assigned to it at any point using the assignment expression (:=).
Using
Implicit Cursor Attributes
Using
PL/SQL allows the developer to produce code that integrates seamlessly with
access to the Oracle database. There are no special characters or keywords
required for "embedding" SQL statements into PL/SQL, because SQL is an extension
of PL/SQL. As such, there really is no embedding at all. Every SQL statement
executes in a cursor. When a cursor is not named, it is called an implicit
cursor. PL/SQL allows the developer to investigate certain return status
features in conjunction with the implicit cursors that run.
These
implicit cursor attributes include
%notfound and
%found to identify if records were
found or not found by the SQL statement;
%notfound, which tells the developer
how many rows were processed by the statement; and
%isopen, which determines if the cursor
is open and active in the database.
Conditional
Statements and Process Flow
Conditional
process control is made possible in PL/SQL with the use of
if-then-else statements. The
if statement uses a Boolean logic
comparison to evaluate whether to execute the series of statements after the
then clause. If the comparison
evaluates to TRUE, the
then clause
is executed. If it evaluates to FALSE, then the code in the
else statement is executed. Nested
if statements can be placed in the
else clause of an
if statement, allowing for the
development of code blocks that handle a number of different cases or
situations.
Using
Loops
Process
flow can be controlled in PL/SQL with the use of loops as well. There are
several different types of loops, from simple
loop-exit statements to
loop-exit when statements,
while loop statements, and
for loop statements. A simple
loop-exit statement consists of the
loop and
end loop keywords enclosing the
statements that will be executed repeatedly, with a special
if-then statement designed to identify
if an
exit condition has been
reached. The
if-then statement can
be eliminated by using an
exit when
statement to identify the
exit
condition. The entire process of identifying the
exit condition as part of the steps
executed in the loop can be eliminated with the use of a
while loop statement. The
exit condition is identified in the
while clause of the statement. Finally,
the
for loop statement can be used
in cases where the developer wants the code executing repeatedly for a specified
number of times.
Explicit
Cursor Handling
Cursor
manipulation is useful for situations where a certain operation must be
performed on each row returned from a query. A cursor is simply an address in
memory where a SQL statement executes. A cursor can be explicitly named with the
use of the
cursor cursor_name is statement, followed by the SQL
statement that will comprise the cursor. The cursor cursor_name is statement is
used to define the cursor in the declaration section only. Once declared, the
cursor must be opened, parsed, and executed before its rows can be manipulated.
This process is executed with the
open statement. Once the cursor is
declared and opened, rows from the resultant dataset can be obtained if the SQL
statement defining the cursor was a
select using the
fetch statement. Both loose variables
for each column's value or a PL/SQL record may be used to store fetched values
from a cursor for manipulation in the statement.
CURSOR FOR
Loops
Executing
each of the operations associated with cursor manipulation can be simplified in
situations where the user will be looping through the cursor results using the
cursor for loop statement. The
cursor for loops handle many aspects of
cursor manipulation explicitly. These steps include including opening, parsing,
and executing the cursor statement, fetching the value from the statement,
handling the exit when data not found condition, and even implicitly declaring
the appropriate record type for a variable identified by the loop in which to
store the fetched values from the query.
Error
Handling
The
exception handler is arguably the finest feature PL/SQL offers. In it, the
developer can handle certain types of predefined exceptions without explicitly
coding error-handling routines. The developer can also associate user-defined
exceptions with standard Oracle errors, thereby eliminating the coding of an
error check in the executable section. This step requires defining the exception
using the
exception_init pragma and
coding a routine that handles the error when it occurs in the exception
handler.
For completely user-defined errors that do not raise Oracle errors, the user can
declare an exception and code a programmatic check in the execution section of
the PL/SQL block, followed by some routine to execute when the error occurs in
the exception handler. A special predefined exception called
others can be coded into the exception
handler as well to function as a catchall for any exception that occurs that has
no exception-handling process defined. Once an exception is raised, control
passes from the execution section of the block to the exception handler. Once
the exception handler has completed, control is passed to the process that
called the PL/SQL block.
