PL/SQL is Oracle's fourth-generation language and supports rapid development of RDBMS code. PL/SQL stands for "Procedural Language extensions to SQL" and provides a procedural wrapper for SQL statements. A procedural language is one in which programmers specify step-by-step statements to be carried out or evaluated. In contrast, SQL statements describe what data to get not how to get it. PL/SQL was initially developed for automating SQL transactions but has since been transformed into a substantial development platform for complex applications.
It is a robust language that supports a wide rage of data types, strong data-typing enforcement, subroutines, conditional and iterative logic, and sophisticated error handling. Almost any SQL statement can be used in PL/SQL program without any special preprocessing, The one exception is the SQL's data definition statement such as CREATE TABLE. These are not allowed in PL/SQL, because PL/SQL code is compiled and it cannot refer to objects that that do not yet exist at compile time. You can work around this restriction by using the dynamic SQL package dbms_sql, which allows for data definition statements to be created dynamically at run time and have them executed.
Advantages of PL/SQL
PL/SQL can be used to automate and repeat complex data manipulation.
PL/SQL can break down complex or unmanageable SQL statements into
simpler steps. PL/SQL code can be shared, thus reducing development
time. PL/SQL can offer performance improvements by reducing network
traffic. PL/SQL adds programming constraints that are not native
to SQL. PL/SQL can often add to program functionality and program
efficiency, and there are certainly many cases where the use of
a PL/SQL is essential for delivering required functionality. PL/SQL
is a valid alternative to SQL when there is little or no requirement
to return large quantities of data. UPDATE, INSERT, or DELETE
statements are often good candidates for conversion to PL/SQL.
However PL/SQL can be difficult to debug
and does not support normal input/output operations.
Structure of PL/SQL
A PL/SQL program is made up of a series of statements. A statement
is terminated with a semicolon (;). PL/SQL is a block-oriented
language: all code is organized into one or more blocks demarked
by BEGIN and END statements. These blocks provide a degree of
structure to PL/SQL-based programs, making it easier to both develop
and maintain the code. PL/SQL version 2.0 and above offers both
unnamed blocks (also called anonymous blocks) and named blocks.
Every PL/SQL program is structured as a block containing up to
four sections (some are optional under certain circumstances):
Header
Relevant for named blocks only. The Header determines the way the name block or program must be called.Declaration Section
The Declaration Section is the part of the block that declares variables, cursors, and sub-blocks that are referenced in the execution and exception sections. When you declare variables, you must specify a legal name, a data type, and (optionally) an initial value or constraint. Variables can be specified by direct reference to a datatype or anchored to the datatype of previously existing variable or data structure. PL/SQL allows a variable's data type to be based on an existing variable, database table or database column. This is accomplished by using %TYPE for scalar variables and %ROWTYPE for record variables. The anchoring data structure can be a column in a database table, the entire table itself, a programmer-defined record, or a local PL/SQL variable. Whenever you declare a variable that has anything to do with a database element, use the %TYPE and %ROWTYPE declaration attributes to define the datatype to those structures. If those database elements change, your compiled code is discarded. When recompiled, the changes are automatically applied to your code. The benefit of using %TYPE and %ROWTYPE is highly maintainable code.Execution Section
The part of the PL/SQL block containing the executable statements. Executable statements are executed by the PL/SQL runtime engine. All executable statements are found within the block body.Exception Section
The section that handles exceptions to normal processing (warnings and error conditions). PL/SQL allows developers to raise and handle errors in a very flexible and powerful way, using the exception handler architecture. As opposed to a linear code model, the exception-handler mechanism allows you to cleanly separate your error processing code from your executable statements. When an exception occurs (is raised) the executing section immediately terminates. Control is passed to the exception section. In other words, no matter how a particular exception is raised, it is handled by the same exception handler in the exception section. Some exceptions have been predefined by Oracle in the STANDARD package. You can also declare your own exceptions.
Anonymous Block
An anonymous block has three parts (1) Declaration Section --
optional, (2) Executable Section, and (3) Exception Section --
optional. An anonymous block is different from stored procedure
and packages, which are compiled once and can then be executed
repetitively. In contrast, an anonymous block is compiled and
executed every time it is submitted to the server. When submitted,
anonymous blocks go through much the same parsing process as standalone
SQL statements. A search of the shared pool is performed for a
matching PL/SQL block and if not found, the block is parsed and
stored in the shared pool. Parsing PL/SQL blocks is typically
a more expensive operation that parsing an SQL statement, since
SQL statements contained within PL/SQL must also be parsed. Parsing
PL/SQL blocks is a time-consuming process. Because stored procedures
are stored in the database in compiled form, they normally do
not need to be compiled when executed, only when created.
Stored Procedures
Stored procedures are callable PL/SQL program units that exist
as objects in an Oracle database schema. Stored procedures are
a way of storing software within the Oracle database. Anything
that you can develop in a PL/SQL script can be saved as a stored
procedure. The Declaration syntax differs from an anonymous blocks,
in that stored procedures have a CREATE keyword at the beginning
and the AS keyword instead of the IS keyword. The types of stored
procedures are procedures, functions, triggers, and packages.
Procedures, functions and triggers are also called subprograms.
A collection of stored procedures can be saved as a package.
Procedures
A procedure is a named PL/SQL block that can take parameter
and be invoked. You use a procedure anywhere you want to encapsulate
a set of actions and optional return one or more values in the
parameter list. Procedures are key building blocks of modular
code, allowing you to both consolidate and reuse your programming
logic. You can execute a procedure from a SQL*Plus session or
from any interface that can execute SQL statements. You can identify
a procedure by the CREATE PROCEDURE statement. The syntax for
the CREATE OR REPLACE PROCEDURE statement is:
CREATE [OR REPLACE] PROCEDURE procedure_name
[(argument [{IN | OUT \ IN OUT}] type,
…
[(argument [{IN | OUT \ IN OUT}] type)] { IS | AS}
procedure_body
Functions
A function is similar to a procedure except that a function must
have a RETURN clause in the header, and at least one RETURN statement
in the executable section. A function without a RETURN statement
will compile with errors. You can identify a function by the CREATE
FUNCTION statement. The syntax for creating a function is very
similar to the syntax for a procedure. It is:
CREATE [[OR REPLACE] FUNCTION function_name
[(argument [{IN | OUT | IN OUT }] type,
…
[(argument [{IN | OUT | IN OUT }] type)]
RETURN return_type { IS | AS}
Procedure_body
You can call functions anywhere in a SQL statement where an expression is allowed - in the SELECT, WHERE, START WITH, GROUP BY, HAVING, ORDER BY, SET, and VALUES clauses. A function can also be used in place of an expression in a PL/SQL statement. You can use one of your own functions or the built-in SQL functions such as TO_DATE, SUBSTR, or LENGTH.
Procedures Versus Functions
The significant difference between procedures and functions is
simply the types of output the two objects generate. Used procedures
to perform complicated processing when you want a substantial
amount of information back. Use a function to compute a value.
Packages
A package consists of a specification and a body. Packages provide
a mechanism to logically group smaller program units together.
Packages encapsulate procedures and functions. The first step
to creating a package is to create its specification. The specification
is the interface to your applications. You declare all public
constructs within the package specification. The specification
publicly declares the schema objects that are continued in the
body of the package. It contains the types, variables, constants,
exceptions, cursors, and subprograms available for use. You must
compile the package specification before the body specification.
When you grant EXECUTE authority on a package, you are giving
access only to the specification; the body remains hidden. The
syntax for the package specification is:
CREATE [ OR REPLACE ] PACKAGE package_name
IS | AS
Public type and item declareations
Subprogram specification
END package_name;
After the specification is created, you create the body of the package. A package body contains the procedure and function logic identified in the package specification. Any procedure or function identified in the package specification must be created in the package body and must match the package specification definition (name, parameter name, type, and order). The body fully defines cursors and subprograms, and so implements the specification. The order in which constructs are defined within the package body makes a difference, you must define a construct before referencing it. The elements and program units defined in the package body are considered PRIVATE and are only known to the package body components. PRIVATE components cannot be called directly from PL/SQL outside their package. The package body is treated as a separate object in the database and is compiled separately from the package specification. The package body compilation relies on the package specification for successful compilation. The syntax for the package body is:
CREATE [OR REPLACE] PACKAGE BODY package_name
IS|AS
Private type and item declarations
Subprogram bodies
END package_name;
Oracle offers many built-in packages. These packages of functions,
procedures and data structures greatly expand the scope of PL/SQL
language. New packages become available with each new release
of Oracle.
Package Hints
Keep packages simple and general to promote their reuse in future
applications. Design your package body after you design the application.
Don't write packages that replicate existing Oracle functionality.
To avoid recompiling, don't place too many items in the package
specification. Changes to the package body may not require Oracle
to recompile.
Trigger
A trigger is a stored subprogram that executes implicitly whenever
a particular event takes place. A trigger can be either an application
trigger or a database trigger. Application triggers are used extensively
in Developer/2000 Forms. Database triggers execute implicitly
when an INSERT, UPDATE, or DELETE statement (triggering statement)
is issued against the associated table. Database triggers can
be defined only on tables, not on views. You can identify a trigger
by the CREATE TRIGGER statement. The syntax for creating a trigger
is:
CREATE [OR REPLACE] TRIGGER trigger_name
{BEFORE | AFTER } trigger_event ON table_name
[[REFERENCING correlation_name ] FOR EACH ROW [ WHEN trigger_condition
]
trigger_body;
Trigger_Name and Table_Name
The trigger_name is the name you want to give the trigger. By
default, Oracle will create the trigger in your schema, but you
can specify another schema by using the standard schema_name.trigger_name
notation. The table_name is the table on which the trigger is
defined. As with trigger name, you can qualify this with a schema
name.
Trigger_Event
The trigger event determines the type of trigger. Triggers can
be defined for INSERT, UPDATE, or DELETE operations. They can
be fired on row or statement operations. The trigger can have
more than one event. For example, you could write an INSERT OR
UPDATE trigger. The syntax for trigger_event is as follows:
{INSERT| DELETE | UPDATE [OF column_list] } OR trigger_event
Row-Level Versus Statement-Level
FOR EACH ROW defines the trigger to be a row-level trigger. Row-level
triggers fire once for each row affected. The default is a statement-level
trigger, which fires only once for each triggering statement.
Within the trigger, you will be able to reference the old and
new values involved in the transaction.
Correlation_Name
Inside a row-level trigger, you can access the row that is currently
being processed by using :old and :new. The :old and : new are
implemented as bind variables. Like other bind variables, the
colon delimits them from regular PL/SQL variables. Correlation_name
allows you to specify an alias for both old and new values. There
is no colon perfix if the old and new are referenced in the WHEN
trigger_condition. The WHEN clause is optional and only valid
for row-level triggers. The WHEN trigger_condition specifies the
conditions that must be met for the trigger to fire. This allows
you to restrict the trigger firing to certain conditions.
Before and After Triggers
Triggers can fire BEFORE or AFTER the triggering event. AFTER
data triggers are slightly more efficient than BEFORE triggers.
Use a BEFORE trigger when you need to derive column values before
completing a triggering INSERT or UPDATE or when the trigger action
should determine weather that triggering statement should be allowed
to complete. Use an AFTER trigger when you want the triggering
statement to complete before executing the triggering action or
if a BEFORE triggering statement is already being used.
Oracle 8 Triggers
Oracle 8.1 introduces eight new trigger types expanding the database
trigger concept beyond database table triggers. The syntax for
INSTEAD OF is not shown here. The INSTEAD OF trigger is valid
on only Oracle8 views. The INSTEAD OF trigger is used to provide
a transparent way of modifying views that cannot be modified directly.
Oracle8i must create a trigger on a nested table column.
Restrictions on Triggers
A trigger may not issue any transaction control statements - COMMIT,
ROLLBACK, or SAVEPOINT. Triggers should not change data in the
primary key, foreign key, or unique key columns of a constraint
table. Triggers may not read from or modify any mutating tables.
A mutating table is a table that is currently being modified by
an UPDATE, DELETE, or INSERT statement. A table is not considered
mutating for STATEMENT triggers.
Efficient Triggers
Only use database triggers for centralized, global operations
that should be fired for the triggering statement, regardless
of which user or application issued that statement. Make use of
the UPDATE OF column_list and WHEN clauses of CREATE TRIGGER statement
to ensure that your triggers only fire when necessary. The UPDATE
OF and WHEN clause can be used to prevent the unnecessary execution
of the trigger and improve performance of DML operations. The
UPDATE OF clause lets you specify the column for update and the
WHEN clause lets you specify the conditions for the update. Triggers
that do not specify the column in the UPDATE OF clause fire every
time any field in the table is changed. Filtering data using the
WHEN clause also prevents the trigger from firing unnecessarily.
Advantages of Stored Procedures
Remember stored procedures are procedures, functions, triggers,
and packages. Stored procedures promote reusability and maintainability.
If the definition changes, only the procedure is affected, this
greatly simplifies maintenance. Stored procedures are held within
the database in a compiled form. This means that all the SQL within
the stored procedure has already been parsed. When the SQL statement
is executed, there is no need to check the statement for syntax,
object references or determine the execution plan. The overhead
of parsing can be very significant. Another advantage is when
you execute a stored procedure, you execute it as if you were
the person who created it and not with the privileges that you
would normally have. Stored procedures can be used to perform
write operations thus limiting the number of users with write
permissions.
Disadvantages of Stored Procedures
All stored procedures are stored in the system tablespace. Usually
the DBA views the system tablespace as theirs and do not want
non-DBA software placed in it. Stored procedures also require
the DBA to grant special system privileges for their creation.
CURSORS
One of the most important features of PL/SQL is the ability to
handle data one row at a time. You cannot selectively examine
or modify a single row from a SELECT statement's result set. Starting
with PL/SQL Version 2.0's cursors, you can attain much finer control
over manipulation of information in the database. Cursors in PL/SQL
can be opened, fetched from, and closed. PL/SQL has two types
of cursors: implicit and explicit.
Implicit CURSORS
An implicit cursor is called that because you do not explicitly
declare a cursor. If you use an implicit cursor, Oracle performs
the open, fetch, and close for you automatically; these actions
are outside of your programmatic control. PL/SQL employs an implicit
cursor for UPDATE, DELETE, or INSERT statement. You cannot execute
these statements within an explicit cursor. You could use an implicit
cursor if your query returns only a single row. If you use an
implicit cursor that returns more than one row, Oracle returns
the exception TOO_MANY_ROWS. Embedded SQL statements declare implicit
cursors that have the identifier name of "SQL." An implicit
SELECT statement has the following form:
SELECT column_list
INTO variable_list
WHERE were_clause ….;
Explicit CURSORS
An explicit cursor is a SELECT statement that is explicitly defined
and assigned a name in the declaration section of your code. You
would use an explicit cursor when you need to retrieve more than
one row of data at a time. For PL/SQL programs, an explicit cursor
is always more efficient, gives greater program control, and is
less vulnerable to data errors than an implicit cursor.
The first step in using an explicit cursor is to define it in the declaration section. The <cursor_name> is the name of the cursor, [( [parameter [parameter …] )] are optional parameter, return_specification is an optional RETURN clause for the cursor, and SELECT_statement is any valid SQL SELECT statement.
CURSOR cursor_name [( [parameter [parameter …] )]
[RETURN return_specifiaction]
IS SELECT_statement
The next step you must perform before you try to extract or fetch records from a cursor is to open that cursor. The <cursor_name> is the name of the cursor you declared and the arguments are the values to be passed if the cursor was declared with a parameter list.
OPEN <cursor_name> [ ( argument [, argument … ] ) ];
The FETCH retrieves the rows. The <cursor_name> is the same name used in the DECLARE and OPEN. The <record_or_variable_list> is the PL/SQL data structure that the records are copied into. You can FETCH into a record structure or into one or more variables. When you FETCH into a list of variables, the number of variables must match the number of expressions in the SELECT list of the cursor. When there are no more records left to FETCH use %FOUND or %NOTFOUND to terminate the loop. %FOUND, %NOTFOUND, %ISOPEN, and %ROWCOUNT are methods for testing explicit cursor attributes.
FETCH <cursor_name> INTO <record_or_variable_list>;
CLOSE closes the named cursor, and releases its resources. When execution of the block terminates, PL/SQL will automatically close any local cursors that were left open without raising an exception. Do not depend on the runtime engine to do your cleaning up for you. When you are done with a cursor, close it.
CLOSE <cursor_name>
CURSOR Attributes
Both explicit and implicit cursors have the following four attributes:
%FOUND - Returns TRUE if record was fetched successfully.
%NOTFOUND - Returns TRUE if record was not fetched successfully.
%ROWCOUNT - Returns number of records fetched from cursor at that
point in time.
%ISOPEN - Returns TRUE if cursor is open.
The SELECT FOR UPDATE clause
By default, Oracle RDBMS locks rows as they are changed. To lock
all rows in a result set, use the FOR UPDATE clause in your SELECT
statement when you OPEN the cursor, instead of when you change
the data. Using the FOR UPDATE clause does not require you to
actually make changes to the data; it only locks the rows when
opening the cursor.
Control Statements
Similar to any other high-level programming language, PL/SQL provides
for null statements, assignment statements, conditional statements,
and loops.
NULL Statement
The Null statement has nothing to do with NULL values. Its main
purpose is to make code more readable by saying, in effect "do
nothing". It is usually used as the statement following one
(usually the last) of a series of condition tests. The syntax
is NULL;
Assignment Statement
The assignment statement is ":=". The assignment operator
is used to assign values and initialize variables and constants.
The syntax is:
<variable> := <expression>;
i:= i + 1;
Conditional Statement
Conditional control is the ability to direct the flow of execution
through your program based on a condition. The three basic forms
for conditional statements are IF, IF ELSE, nested IF. The first
and simplest form executes one or more statements if an expression
is true or skips them if it is false.
IF (expression) THEN
statements;
END IF;
The second form executes one or more statements if an expression
is true or another set if false.
Only one or the other set of statements is executed; they are
mutually exclusive.
IF (expression) THEN
statements;
ELSE
statements;
END IF;
The third form is the nested IF statement. You can nest any IF statement within any other IF statement. Nested IF statements are often necessary to implement complex logic rules, but you should use them carefully. Nested IF statements, like nested loops, can be very difficult to understand and debug. Good comments are essential. An advantage to the nested IF structure is that it defers evaluation of inner conditions. An inner IF condition is evaluated only when the outer condition is true. If the evaluation of conditions uses a lot of memory or CPU time, you may want to use nested IF and defer processing to an inner IF statement.
IF (a > b or c < d) THEN
IF (a > d) THEN
…. some statements
ELSE
… some other statements
END IF;
ELSE (expression)
END IF;
Sequential Statements
Rather than nest logic to test mutually exclusive expressions,
you can use special sequential logic syntax. Notice the unusual
spelling ELSIF (not ELSE IF). The entire compound statement is
terminated by a single END. The conditions in the ELSIF are always
evaluated in the order of first to last.
IF (expression) THEN
statements;
ELSIF (expression) THEN
statements;
ELSIF (expression) THEN
statements;
END IF;
Sequential Statements Using Looping
In general, loops allow programmers to repeat a set of executable
statements a finite number of times until some condition is met.
The four kinds of PL/SQL loops are simple LOOP, WHILE LOOP, numeric
FOR LOOP and cursor FOR LOOP.
Simple LOOP
Simple loops are unconditional loops. The structure consists of
the LOOP keyword, the body of executable code, and the END LOOP
keyword. Be careful when you use simple loops. Make sure they
always have a way to stop. You will find yourself using %NOTFOUND
attribute with simple forms of looping. Simple LOOPs are most
typically used with explicit cursors.
LOOP
<executable statements>
END LOOP;
WHILE LOOP
The WHILE statement has the exit condition at the top of the loop.
The WHILE loop continues to execute the statements as long as
the condition remains true. The loop terminates when the condition
is evaluated to false or null. A danger of the all looping code
is that it could be an infinite loop if the loop never executes
an EXIT statement. A poorly coded conditional statement can result
in an infinite loop. Never exit from a WHILE loop with an EXIT
or RETURN statement. Rely solely on the WHILE loop condition to
terminate the loop.
WHILE <some Boolean condition is TRUE> LOOP
<executable statements>
END LOOP;
Numeric FOR LOOP
You use the numeric FOR LOOP when you need to loop a precise number
of times. If the number of times the loop body is to be executed
is known, the numeric FOR LOOP statement can be used.
FOR <loop-counter> IN [REVERSE]
<lower> .. <higher> LOOP
<statements>;
END LOOP;
The <loop-counter> is the loop control variable that is implicitly declared by PL/SQL. It should not be declared in the program. The lower and upper bounds for the loop control variable are specified in <lower> and <upper> respectively. These must be integer expressions. The REVERSE keyword is optional; if not used, the loop starts with the loop control variable set at the lower bound and increases by one each time. If REVERSE keyword is used, the loop starts at the upper bound and decreases by one each time.
Cursor FOR LOOP
You can use the cursor FOR LOOP to access all the records in a
cursor quickly and easily. The cursor FOR LOOP can be used to
implicitly open a cursor, fetches its results set into the loop
control variable, and then implicitly closes the cursor. Whenever
you need to read through every record fetched by a cursor, the
cursor FOR LOOP will save you lots of typing over the "manual"
approach of explicitly opening , fetching, and closing the cursor.
The cursor FOR LOOP reduces the volume of code you need to write
to fetch data from a cursor. It greatly lessens the chance of
introducing loop errors.
FOR loop_control_variable IN cursor_or_query LOOP
<executable statements>
END LOOP;
In many cases, you will have a choice to code in SQL or PL/SQL. If PL/SQL offers no benefits over SQL, then coding in SQL can be faster and easier. You may want to evaluate the benefits of programming in PL/SQL over SQL on a case by case basis. The following is an example where PL/SQL is a valid alternative to SQL.
In SQL, when your correlated update references the table in both the WHERE and the SET clause, duplicate processing can occur. There is often no alternative to this format for the SQL UPDATE. Better performance can be achieved by using PL/SQL to perform the task. As an alternative to a SQL statement with both the WHERE and the SET clause, the following is an example PL/SQL cursor FOR LOOP:
DECLARE
CURSOR cust_cursor is
SELECT c.rowid crowid, c.rowid, e.manager_id
FROM customers c,
employees e
WHERE e.surname=c.contract_surname
And e.firstname =c.contract_firstname
BEGIN
FOR cust_row in cust_cursor LOOP
UPDATE customers
SET sales_rep_id = cust_row.manager_id
WHERE rowid =cust_row.crowid;
END LOOP.
END;
GOTO Statement
The GOTO statement allows you to perform unconditional branching
to another executable statement in the same execution section
of a PL/SQL block. When a GOTO statement is evaluated, control
immediately passes to the statement identified by the label. It
is illegal to branch into an inner block, loop, or IF statement.
The syntax is:
GOTO label;
Summary
This discussion of PL/SQL describes the structure of PL/SQL and
advantages of using it. You are introduced to the kinds of PL/SQL:
anonymous block, procedures, functions, triggers, and packages.
The implicit CURSORS and explicit CURSORS are described. The programming
fundamentals of directing the flow of execution through a program
using conditional control statements and sequential control statements
are discussed.