Introduction to PL/SQL
Objectives
At the end of this session, you will be able to: State the need for a procedural language in Oracle Create PL/SQL blocks Write nested blocks Write control structures
Introduction to PL/SQL
Procedural Language/SQL (PL/SQL), is an extension of SQL. The need for PL/SQL arose: To centralize automated business tasks Due to its ability to handle errors
Benefits of PL/SQL
The reasons for using PL/SQL are as follows:
Integrating with the Oracle server and Oracle development
tools
Implementing performance enhancements in an
application
Modularizing the development of programs Implementing Portability Declaration of variables Programming with procedural language control structures Handling errors
Integration
PL/SQL integrates with:
The Oracle server because it supports the use of SQL
data types
The Oracle development tools because the tools have
their own PL/SQL engines
Performance Enhancements of Applications
PL/SQL enhances application performance by:
Reducing network traffic
Adding procedural processing power to Oracle
Development tools
Modularized Program Development
PL/SQL programs are made up of one or more blocks.
A diagrammatic representation of modularization:
Portability
The portability features of PL/SQL are as follows:
PL/SQL programs can be run anywhere the Oracle
server can run
PL/SQL code can also be moved between the Oracle
server and Oracle Developer applications
Variable Declaration
In PL/SQL, you can declare variables:
To use them in SQL and procedural statements Belonging to different data types Dynamically based on the structure of tables and
columns in the database
Programming with Procedural Language Control Structures
PL/SQL allows the usage of control structures to execute:
Sequence of statements conditionally Sequence of statements iteratively in a loop Individually the rows returned by multiple-row query
Error Handling
PL/SQL implements error handling functionality by:
Processing Oracle server errors with error handling
routines
Allowing users to declare their own error conditions
and processing them with error handling routines
PL/SQL Block Types
The types of PL/SQL Blocks are:
Anonymous, which are unnamed and not stored in the
database
Subprograms, which are named stored code stored in
the database.
PL/SQL Constructs
Constructs include:
Anonymous blocks Procedures Functions Packages Triggers
Invoking Stored Procedures and Functions
Stored procedures and functions can be invoked from environments such as:
iSQL*Plus
Oracle Development tools Another stored procedure
PL/SQL Block Structure
A block consists of:
A declarative section
An executable section An exception handler section
Guidelines to Writing PL/SQL Blocks
Some points that will help write a block are: A line of PL/SQL text can contain lexical units such as: Delimiters Identifiers Literals Comments Use a semicolon (;) at the end of all SQL and PL/SQL control statements and the END keyword
Guidelines to Writing PL/SQL Blocks (Cont.)
Some points that will help write a block are (Cont.):
Do not use semicolons after the keywords DECLARE,
BEGIN, and EXCEPTION
Increase the readability of the block by writing the
keywords in uppercase
Use a slash (/) to terminate a PL/SQL block on a line by
itself
Variables
A variable is a named memory location used to store data temporarily.
Variables are used for:
Temporary storage data
Manipulation of data
Reusability of data
Maintenance of data
Using Variables in PL/SQL
Points to remember while using variables in
PL/SQL:
Declare and initialize the variables in the declarative section
of a PL/SQL block
You can specify the initial value of the variable or put a NOT
NULL constraint on it
You can assign new values to variables in the executable
section
Use parameters to assign values to the variables Use output variables to display the results of a PL/SQL block
Types of variables
Variables are of two types:
PL/SQL variables Non-PL/SQL variables
PL/SQL Variables
The data types used to declare PL/SQL variables are: Scalar data types Composite data types Reference data types LOB data types The syntax for declaring PL/SQL variables is:
identifier [CONSTANT] datatype [NOT NULL] [:= | DEFAULT value];
Guidelines for declaring PL/SQL Variables
Some points to remember while declaring PL/SQL variables are:
Assign a value if you declare a variable using the NOT
NULL constraint
The keyword CONSTANT must precede the data type
specification in case of a constant variable declaration
Assign an expression to a variable using either the
assignment operator (:=) or the keyword DEFAULT
Operators in PL/SQL
The following operators can be used in PL/SQL:
Arithmetic
Logical Relational or comparison Concatenation Exponentiation Other miscellaneous symbols
Functions used in PL/SQL Blocks
The following SQL functions can be used in
PL/SQL blocks:
Single-row number and character functions Data type conversion functions
Date functions
GREATEST and LEAST functions
The functions that cannot be used are:
DECODE
Group functions
Declaring Variables using Scalar Data Types
The scalar data types include: CHAR VARCHAR2 LONG LONG RAW NUMBER BINARY_INTEGER PLS_INTEGER BOOLEAN DATE
Declaring Variables using Scalar Data Types (Cont.)
The scalar data types include (Cont.): TIMESTAMP TIMESTAMP WITH TIMEZONE TIMESTAMP WITH LOCAL TIME ZONE INTERVAL YEAR TO MONTH INTERVAL DAY TO SECOND
Variables of scalar data type are declared with the following syntax:
<variable_name> <datatype [(size)]>
%TYPE Attribute
Use the %TYPE to declare a variable to be of the same data type as that of: A previously declared variable
The syntax is:
<variable_name> variable_name%TYPE
A database column The syntax is: <variable_name> table.columnname%TYPE
iSQL*Plus Variables
The iSQL*Plus or non-PL/SQL variables that can be used within PL/SQL blocks are:
Substitution variables:Referenced in a block by prefixing it
with an ampersand (&)
Host variables: Referenced in a PL/SQL block by placing a
colon before the variable. The syntax to declare a bind variable is: VARIABLE <variable_name> data type The syntax to display the variable value is: PRINT <variable_name>
Nested Blocks
A block within a block is a nested block
Control Structures
Control structures are categorized into: Conditional constructs
IF - THEN - END IF IF - THEN - ELSE - END IF IF - THEN - ELSIF - END IF
CASE constructs
CASE expressions CASE statements
Simple CASE Searched CASE
Loop constructs
Basic Loop FOR Loop WHILE Loop
Summary
In this session you have learnt to:
State the need for a procedural language in Oracle Create PL/SQL blocks
Write nested blocks
Write control structures
Objectives
At the end of this session, you will be able to:
Raise and handle exceptions Describe the different types of exceptions
Raising and Handling Exceptions
The types of exceptions are System generated User defined
Exceptions are handled by: The exception handling section Passing it on to calling environment
Handling Exceptions
To implement exception handling:
Declare an exception Raise the exception Trap and handling the exception Propagate the exception
Trapping exceptions
Exceptions are raised by keyword RAISE EXCEPTION The syntax to handle them is:
EXCEPTION WHEN exception1 [or exceptionN. . .] THEN statements; [WHEN exception 2 [or exceptionN . . .] THEN statements; [WHEN OTHERS THEN statements]
Propagating exceptions
The following figure Illustrates how exceptions propagate.
Types of Exception
The three types of exceptions are:
Predefined exceptions User-defined exceptions
Internal or non-predefined exceptions
Predefined Exceptions
These exceptions are:
System defined exceptions Raised implicitly
User-Defined Exceptions
These exceptions are handled explicitly The syntax for declaring an exception name is:
<exception> EXCEPTION, where exception is the name
of the exception.
The syntax to raise an exception is:
RAISE exception.
exception is the name of the exception declared.
Summary
In this session you have learnt to:
Raise and handle exceptions Describe the different types of exceptions
Objectives
At the end of this session, you will be able to:
Use SELECT statements within PL/SQL blocks Perform data manipulations within PL/SQL blocks Describe the common SQL parser
Embedding SELECT Statements in PL/SQL
Use SELECT statements within PL/SQL blocks
to retrieve data from the database. The syntax to use the SELECT statement is:
SELECT <select_list>
INTO (variable_name[, variable_name] | record_name) FROM <table_name> WHERE condition;
Embedding SELECT Statements in PL/SQL (Cont.)
Points to remember while embedding SELECT statements are: Terminate each SQL statement with a semicolon Remember to use the INTO clause The number of output variables in the INTO clause must be the same as the number of columns selected in the SELECT clause The output variables in the INTO clause must correspond positionally to the selected columns
Embedding SELECT Statements in PL/SQL (Cont.)
Points to remember while embedding SELECT statements are:
Use the %TYPE attribute to ensure that the data types
of variables match that of the columns selected
Group functions can be used in the SELECT statement
but not in the PL/SQL syntax
Avoid ambiguity in the WHERE clause
DML statements in PL/SQL
The following DML statements can be
embedded in a PL/SQL block:
INSERT statements UPDATE statements DELETE statements
MERGE statements
Embedding Transaction Control Statements
The following transaction control options can be used within PL/SQL blocks:
COMMIT
ROLLBACK SAVEPOINT
Cursors
A cursor is an area of memory where the Oracle
server parses and executes SQL statements. Cursors are of the following two kinds:
Implicit Explicit
Implicit Cursor
Implicit cursor attributes are:
SQL%ROWCOUNT SQL%FOUND
SQL%NOTFOUND
SQL%ISOPEN
Common SQL Parser
The Common SQL parser is one of the PL/SQL
enhancements introduced in Oracle9i. The effects of having a common SQL parser are:
Earlier detection of static SQL errors
Elimination of bugs that arise due to differences in
SQL analysis between SQL and PL/SQL
Easy to upgrade from previous versions of Oracle to
Oracle9i
Summary
In this session you have learnt to:
Use SELECT statements within PL/SQL blocks Perform data manipulations within PL/SQL blocks Describe the common SQL parser
