Relational Database Management System

PL/SQL Introduction
PL/SQL is a block structured language that enables developers to combine the power of
SQL with procedural statements. All the statements of a block are passed to oracle engine
all at once which increases processing speed and decreases the traffic.

Basics of PL/SQL

● PL/SQL stands for Procedural Language extensions to the Structured Query

Language (SQL).
● PL/SQL is a combination of SQL along with the procedural features of
programming languages.
● Oracle uses a PL/SQL engine to processes the PL/SQL statements.
● PL/SQL includes procedural language elements like conditions and loops. It
allows declaration of constants and variables, procedures and functions, types
and variable of those types and triggers.

Features of PL/SQL
PL/SQL has the following features −

● PL/SQL is tightly integrated with SQL.

● It offers extensive error checking mechanisms.
● It supports numerous data types for flexible data handling.
● Includes a variety of programming structures, such as loops and conditionals.
● It supports structured programming through functions and procedures.
● It supports object-oriented programming, enabling more complex data handling
and manipulation.
● It supports the web application development and server pages.

Miss. Hemangi Panchal Page 1

 Relational Database Management System

Differences between SQL and PL/SQL:

SQL PL/SQL

PL/SQL is a block of codes that used

SQL is a single query that is used to
to write the entire program blocks/
perform DML and DDL operations.
procedure/ function, etc.

It is declarative, that defines what needs

PL/SQL is procedural that defines
to be done, rather than how things need to
how the things needs to be done.
be done.

Execute as a single statement. Execute as a whole block.

Mainly used to manipulate data. Mainly used to create an application.

It is an extension of SQL, so it can

Cannot contain PL/SQL code in it.
contain SQL inside it.

Miss. Hemangi Panchal Page 2

 Relational Database Management System

PL\SQL-Introduction
PL-SQL (Procedural - SQL)
➔ Function
➔ Procedure
➔ Trigger
➔ Cursor
The sections of a PL/SQL block are:
1. The Declare section
2. The master Begin and End section that also (optionally) contains as Exception
section.

PL/SQL block of code

Declare
Procedural Statements;
Begin
Procedural Statements;
SQL statements;
Exceptions
SQL statements;
END;

PL/SQL Data Types

In this chapter, we will discuss the Data Types in PL/SQL. The PL/SQL variables,
constants and parameters must have a valid data type, which specifies a storage format,
constraints, and a valid range of values. We will focus on the SCALAR and the LOB data
types in this chapter. The other two data types will be covered in other chapters.

S.No Category & Description

Scalar
1 Single values with no internal components, such as a NUMBER, DATE, or
BOOLEAN.

Miss. Hemangi Panchal Page 3

 Relational Database Management System

Large Object (LOB)

Pointers to large objects that are stored separately from other data items,
2 such as text, graphic images, video clips, and sound waveforms.

Composite
3 Data items that have internal components that can be accessed individually.
For example, collections and records.

Reference
4 Pointers to other data items.

PL/SQL Scalar Data Types and Subtypes

PL/SQL Scalar Data Types and Subtypes come under the following categories −

S.No Date Type & Description

Numeric
1 Numeric values on which arithmetic operations are performed.

Character
2 Alphanumeric values that represent single characters or strings of characters.

Boolean
3 Logical values on which logical operations are performed.

Datetime
4 Dates and times.

PL/SQL provides subtypes of data types. For example, the data type NUMBER has a
subtype called INTEGER. You can use the subtypes in your PL/SQL program to make
the data types compatible with data types in other programs while embedding the PL/SQL
code in another program, such as a Java program.

Miss. Hemangi Panchal Page 4

 Relational Database Management System

PL/SQL Numeric Data Types and Subtypes

Following table lists out the PL/SQL pre-defined numeric data types and their sub-types

S.No Data Type & Description

PLS_INTEGER
1 Signed integer in range -2,147,483,648 through 2,147,483,647, represented
in 32 bits

BINARY_INTEGER
2 Signed integer in range -2,147,483,648 through 2,147,483,647, represented
in 32 bits

BINARY_FLOAT
3 Single-precision IEEE 754-format floating-point number

BINARY_DOUBLE
4 Double-precision IEEE 754-format floating-point number

NUMBER(prec, scale)
Fixed-point or floating-point number with absolute value in range 1E-130 to
5 (but not including) 1.0E126. A NUMBER variable can also represent 0

DEC(prec, scale)
6 ANSI specific fixed-point type with maximum precision of 38 decimal digits

DECIMAL(prec, scale)
7 IBM specific fixed-point type with maximum precision of 38 decimal digits

NUMERIC(pre, secale)
8 Floating type with maximum precision of 38 decimal digits

Miss. Hemangi Panchal Page 5

 Relational Database Management System

DOUBLE PRECISION
9 ANSI specific floating-point type with maximum precision of 126 binary digits
(approximately 38 decimal digits)

FLOAT
10 ANSI and IBM specific floating-point type with maximum precision of 126
binary digits (approximately 38 decimal digits)

INT
11 ANSI specific integer type with maximum precision of 38 decimal digits

INTEGER
12 ANSI and IBM specific integer type with maximum precision of 38 decimal
digits

SMALLINT
13 ANSI and IBM specific integer type with maximum precision of 38 decimal
digits

REAL
14 Floating-point type with maximum precision of 63 binary digits (approximately
18 decimal digits)

Following is a valid declaration −

DECLARE
num1 INTEGER;
num2 REAL;
num3 DOUBLE PRECISION;
BEGIN
null;
END;
/

When the above code is compiled and executed, it produces the following result −

PL/SQL procedure successfully completed

Miss. Hemangi Panchal Page 6

 Relational Database Management System

PL/SQL Character Data Types and Subtypes

Following is the detail of PL/SQL pre-defined character data types and their sub-types −

S.No Data Type & Description

CHAR
1 Fixed-length character string with maximum size of 32,767 bytes

VARCHAR2
2 Variable-length character string with maximum size of 32,767 bytes

RAW
3 Variable-length binary or byte string with maximum size of 32,767 bytes, not
interpreted by PL/SQL

NCHAR
4 Fixed-length national character string with maximum size of 32,767 bytes

NVARCHAR2
5 Variable-length national character string with maximum size of 32,767 bytes

LONG
6 Variable-length character string with maximum size of 32,760 bytes

LONG RAW
7 Variable-length binary or byte string with maximum size of 32,760 bytes, not
interpreted by PL/SQL

ROWID
8 Physical row identifier, the address of a row in an ordinary table

UROWID
9 Universal row identifier (physical, logical, or foreign row identifier)

Miss. Hemangi Panchal Page 7

 Relational Database Management System

PL/SQL Boolean Data Types

The BOOLEAN data type stores logical values that are used in logical operations. The
logical values are the Boolean values TRUE and FALSE and the value NULL.

However, SQL has no data type equivalent to BOOLEAN. Therefore, Boolean values

cannot be used in −

SQL statements
Built-in SQL functions (such as TO_CHAR)
PL/SQL functions invoked from SQL statements

PL/SQL Datetime and Interval Types

The DATE datatype is used to store fixed-length datetimes, which include the time of day
in seconds since midnight. Valid dates range from January 1, 4712 BC to December 31,
9999 AD.

The default date format is set by the Oracle initialization parameter

NLS_DATE_FORMAT. For example, the default might be 'DD-MON-YY', which includes
a two-digit number for the day of the month, an abbreviation of the month name, and the
last two digits of the year. For example, 01-OCT-12.

Each DATE includes the century, year, month, day, hour, minute, and second. The

following table shows the valid values for each field −

Field Name Valid Datetime Values Valid Interval

Values

-4712 to 9999 (excluding year 0)

YEAR Any nonzero integer

MONTH 01 to 12 0 to 11

Miss. Hemangi Panchal Page 8

 Relational Database Management System

01 to 31 (limited by the values of

MONTH and YEAR, according to the
DAY rules of the calendar for the locale) Any nonzero integer

HOUR 00 to 23 0 to 23

MINUTE 00 to 59 0 to 59

00 to 59.9(n), where 9(n) is the 0 to 59.9(n), where

precision of time fractional seconds 9(n) is the precision
SECOND of interval fractional
seconds

-12 to 14 (range accommodates

TIMEZONE_HOUR daylight savings time changes) Not applicable

TIMEZONE_MINUTE 00 to 59 Not applicable

TIMEZONE_REGIO Found in the dynamic performance

view V$TIMEZONE_NAMES Not applicable
N

Found in the dynamic performance

TIMEZONE_ABBR view V$TIMEZONE_NAMES Not applicable

PL/SQL Large Object (LOB) Data Types

Large Object (LOB) data types refer to large data items such as text, graphic images,

video clips, and sound waveforms. LOB data types allow efficient, random, piecewise

access to this data. Following are the predefined PL/SQL LOB data types −

Data Type Description Size

Miss. Hemangi Panchal Page 9

 Relational Database Management System

Used to store large binary objects in System-dependent. Cannot

BFILE operating system files outside the exceed 4 gigabytes (GB).
database.

Used to store large binary objects in 8 to 128 terabytes (TB)

BLOB the database.

Used to store large blocks of 8 to 128 TB

CLOB character data in the database.

Used to store large blocks of NCHAR 8 to 128 TB

NCLOB data in the database.

PL/SQL User-Defined Subtypes

A subtype is a subset of another data type, which is called its base type. A subtype has
the same valid operations as its base type, but only a subset of its valid values.

PL/SQL predefines several subtypes in package STANDARD. For example, PL/SQL

predefines the subtypes CHARACTER and INTEGER as follows −

SUBTYPE CHARACTER IS CHAR;

SUBTYPE INTEGER IS NUMBER(38,0);

You can define and use your own subtypes. The following program illustrates defining
and using a user-defined subtype −

DECLARE
SUBTYPE name IS char(20);
SUBTYPE message IS varchar2(100);
salutation name;
greetings message;
BEGIN
salutation := 'Reader ';
greetings := 'Welcome to the World of PL/SQL';
dbms_output.put_line('Hello ' || salutation || greetings);

Miss. Hemangi Panchal Page 10

 Relational Database Management System

END;
/

When the above code is executed at the SQL prompt, it produces the following result −

Hello Reader Welcome to the World of PL/SQL

PL/SQL procedure successfully completed.

NULLs in PL/SQL
PL/SQL NULL values represent missing or unknown data and they are not an integer,
a character, or any other specific data type. Note that NULL is not the same as an empty
data string or the null character value '\0'. A null can be assigned but it cannot be equated
with anything, including itself.

PL/SQL - Basic Syntax

Basic Syntax of PL/SQL which is a block-structured language; this means that the

PL/SQL programs are divided and written in logical blocks of code. Each block consists

of three sub-parts −

S.N Sections & Description

o

Declarations
This section starts with the keyword DECLARE. It is an optional
1
section and defines all variables, cursors, subprograms, and other
elements to be used in the program.

Miss. Hemangi Panchal Page 11

 Relational Database Management System

Executable Commands
This section is enclosed between the keywords BEGIN and END
and it is a mandatory section. It consists of the executable PL/SQL
2
statements of the program. It should have at least one executable
line of code, which may be just a NULL command to indicate that
nothing should be executed.

Exception Handling
3 This section starts with the keyword EXCEPTION. This optional
section contains exception(s) that handle errors in the program.

Every PL/SQL statement ends with a semicolon (;). PL/SQL blocks can be
nested within other PL/SQL blocks using BEGIN and END. Following is the basic

structure of a PL/SQL block −

DECLARE
<declarations section>
BEGIN
<executable command(s)>
EXCEPTION
<exception handling>
END;

The 'Hello World' Example

SET SERVEROUTPUT ON;
DECLARE
message varchar2(20):= 'Hello, World!';
BEGIN
dbms_output.put_line(message);
END;
/

Miss. Hemangi Panchal Page 12

 Relational Database Management System

The end; line signals the end of the PL/SQL block. To run the code from the SQL

command line, you may need to type / at the beginning of the first blank line after the last

line of the code. When the above code is executed at the SQL prompt, it produces the

following result −

Hello World

PL/SQL procedure successfully completed.

The PL/SQL Identifiers

PL/SQL identifiers are constants, variables, exceptions, procedures, cursors,
and reserved words. The identifiers consist of a letter optionally followed by
more letters, numerals, dollar signs, underscores, and number signs and
should not exceed 30 characters.

By default, identifiers are not case-sensitive. So you can use integer or

INTEGER to represent a numeric value. You cannot use a reserved keyword
as an identifier.

The PL/SQL Delimiters

A delimiter is a symbol with a special meaning. Following is the list of delimiters in PL/SQL

Delimiter Description

+, -, *, / Addition, subtraction/negation, multiplication, division

% Attribute indicator

Miss. Hemangi Panchal Page 13

 Relational Database Management System

' Character string delimiter

. Component selector

(,) Expression or list delimiter

: Host variable indicator

, Item separator

" Quoted identifier delimiter

= Relational operator

@ Remote access indicator

; Statement terminator

:= Assignment operator

=> Association operator

|| Concatenation operator

** Exponentiation operator

<<, >> Label delimiter (begin and end)

/*, */ Multi-line comment delimiter (begin and end)

-- Single-line comment indicator

Miss. Hemangi Panchal Page 14

 Relational Database Management System

.. Range operator

<, >, <=, >= Relational operators

<>, '=, ~=, ^= Different versions of NOT EQUAL

The PL/SQL Comments

Program comments are explanatory statements that can be included in the
PL/SQL code that you write and helps anyone reading its source code. All
programming languages allow some form of comments.

The PL/SQL supports single-line and multi-line comments. All characters

available inside any comment are ignored by the PL/SQL compiler. The PL/SQL
single-line comments start with the delimiter -- (double hyphen) and multi-
line comments are enclosed by /* and */.

DECLARE
-- variable declaration
message varchar2(20):= 'Hello, World!';
BEGIN
/*
* PL/SQL executable statement(s)
*/
dbms_output.put_line(message);
END;
/

When the above code is executed at the SQL prompt, it produces the following result −

Hello World

PL/SQL procedure successfully completed.

Miss. Hemangi Panchal Page 15

 Relational Database Management System

Block Structure

PL/SQL block structure consists of three main sections: declarative, executable, and
exception-handling sections. The structure can be divided into:

1. Declarative Section: This section is optional. Here, variables, constants,

cursors, and types are declared.
2. Executable Section: This is the mandatory section. It contains the executable
code and logic.
3. Exception-Handling Section: This section is optional. It handles exceptions
(runtime errors).

Basic PL/SQL Block Structure:

DECLARE
-- Declarative section (optional)
-- Variable, constant, cursor declarations, etc.
variable_name datatype [NOT NULL] := value;
constant_name CONSTANT datatype := value;

BEGIN
-- Executable section (mandatory)
-- Code to be executed
DBMS_OUTPUT.PUT_LINE('Executable code goes here');

-- Variable manipulation and processing logic

variable_name := some_value;

EXCEPTION
-- Exception-handling section (optional)
-- Code to handle errors
WHEN exception_name THEN
-- Actions to perform when the exception is raised
DBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM);

-- You can handle multiple exceptions

WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('A general error occurred');
END;
/

Miss. Hemangi Panchal Page 16

 Relational Database Management System

Breakdown of Each Section:

1. DECLARE (optional): This is where variables and constants are declared. If

there are no declarations, you can omit this section entirely.

DECLARE
counter NUMBER := 0;
greeting VARCHAR2(20) := 'Hello, PL/SQL!';

2. BEGIN (mandatory): The main executable code goes here. This is where the
logic and operations are performed.

BEGIN
DBMS_OUTPUT.PUT_LINE(greeting);
counter := counter + 1;

3. EXCEPTION (optional): This section is used to handle errors. PL/SQL exceptions can
be predefined or user-defined.
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No data found.');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('An unexpected error occurred.');

Example of a Complete PL/SQL Block:

DECLARE
v_num NUMBER := 5;
BEGIN
IF v_num > 0 THEN
DBMS_OUTPUT.PUT_LINE('The number is positive.');
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('An error occurred.');
END;
/

When the above code is executed at the SQL prompt, it produces the following result −

Miss. Hemangi Panchal Page 17

 Relational Database Management System

The number is positive.

PL/SQL procedure successfully completed.

In this example:

● The declarative section declares a variable v_num.

● The executable section checks if v_num is positive.
● The exception section handles any potential errors.

Conditional Statements in PL / SQL

In many case we would be in a state whether we have to choose Yes or No. These types
of questions are also called Decision Statements. Here, the user is compelled to choose
one answer. In this case we choose the answer based on the condition in which we are
present. In the same way, Every Program has its moments like choosing if this condition
is positive then we choose Yes statement else if condition is false then we choose No
statement while coding.

The Every Programming Language has a few Conditional statements. These Conditional
statements are also known as Decision Statements. There are a few Conditional

Miss. Hemangi Panchal Page 18

 Relational Database Management System

Statements or Decision Statements in PL / SQL (Procedure Language / Structured Query

Language).

The Conditional Statements can only be used in BEGIN State.

Types of Conditional Statements

1. if condition then statement

2. if condition then else statement
3. nested if condition then statements
4. if condition then elseif condition then else statements

1.) if Condition then Statement

This is one of the first important PL / SQL Conditional Statement. In this statement only
if the condition is true, then the output is going to be shown. Else in any other cases there
is no opportunity for outputs.

Syntax :

IF <condition> then

Statement 1

Statement 2

Statement N

END IF;

Example :
SQL> DECLARE

N1 INTEGER;

BEGIN

N1 := 10;

IF N1 >= 10 THEN

Miss. Hemangi Panchal Page 19

 Relational Database Management System

dbms_output.put_line('The Value is ' || N1);

END IF;

EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE('ERROR');

END;

OUTPUT:
The Value is 10

PL/SQL procedure successfully completed.

2.) if Condition then else Statement

This is also one of the first important PL / SQL Conditional Statement. In this statement
only if the condition is true, then the output is going to be shown. Else in any other cases
there is opportunity for outputs to seen. If the if condition fails then the else statements
are executed.

This means if condition is true If statements are executed. If not possible else statements
are executed.

This kind of conditional statements are required for finding few real life problems like
finding odd or even number, Checking the number is divisible by two, three, etc., Finding
Largest Number, Finding Smallest Number, etc. many programs like this.

Syntax

IF >CONDITION> THEN

Statement 1

Statement 2

Miss. Hemangi Panchal Page 20

 Relational Database Management System

Statement N

ELSE

Statement 1

Statement 2

Statement N

END IF;

Example:

SQL> DECLARE

N1 INTEGER;

N2 INTEGER;

BEGIN

N1:=10;

N2:=5;

IF N1 > N2 THEN

dbms_output.put_line('The Values of N1 is greater than N2');

ELSE

dbms_output.put_line('The Values of N2 is greater than N1');

END IF;

END;

Miss. Hemangi Panchal Page 21

 Relational Database Management System

OUTPUT :

The Values of N1 is greater than N2

PL/SQL procedure successfully completed.

3.) if condition elsif condition else statements

This is also one of the first important PL / SQL Conditional Statement. In this statement
only if the condition is true, then the output is going to be shown. Else in any other cases
there is opportunity for outputs to seen. If the if condition fails then the else if statements
with a condition are executed. Even if else if condition is False, then the else statements
are executed.

Syntax

IF <CONDITION> THEN

Statement 1

Statement 2

Statement N

ELSEIF <CONDITION> THEN

Statement 1

Statement 2

Statement N

ELSE

Statement 1

Statement 2

Miss. Hemangi Panchal Page 22

 Relational Database Management System

Statement N

END IF;
Example
SQL> SET SERVEROUTPUT ON;

SQL> DECLARE

num1 INTEGER:= 7;

num2 INTEGER:= 8;

BEGIN

IF num1 > num2 THEN

dbms_output.put_line('NUM1 SMALLER THAN NUM2');

ELSIF num1 = num2 THEN

dbms_output.put_line('EQUAL');

ELSE

dbms_output.put_line('NUM2 SMALLER THAN NUM1');

END IF;

END;

Output:

NUM2 SMALLER THAN NUM1

PL/SQL procedure successfully completed.

Miss. Hemangi Panchal Page 23

 Relational Database Management System

4.) Nested if condition then statements

This is also one of the conditional statements used in PL / SQL. Here, we are going to
write the same if else statements. But in place of if statements we are going to write
another if condition else statements. The workings of these statements are same and
similar to the previous if else statements.

Syntax

Syntax 1

IF <CONDITION> THEN

IF <CONDITION> THEN

Statement 1

Statement 2

Statement N

ELSE

Statement 1

Statement 2

Statement N

END IF;

ELSE

Statement 1

Statement 2

Statement N

END IF;

Syntax 2:

Miss. Hemangi Panchal Page 24

 Relational Database Management System

IF <CONDITION> THEN

Statement 1

Statement 2

Statement N

ELSE

IF <CONDITION> THEN

Statement 1

Statement 2

Statement N

ELSE

Statement 1

Statement 2

Statement N

END IF;

END IF;

Example

SQL> DECLARE

num1 INTEGER;

num2 INTEGER;

Miss. Hemangi Panchal Page 25

 Relational Database Management System

num3 INTEGER;

BEGIN

num1:= 17;

num2:= 12;

num3:= 10;

IF num1 > num2 THEN

IF num1 > num3 THEN

dbms_output.put_line('The greatest number is '|| num1);

END IF;

ELSIF num2 > num3 THEN

dbms_output.put_line('The greatest number is '|| num2);

ELSE

dbms_output.put_line('The greatest number is '|| num3);

END IF;

END;

Output:

The greatest number is 17

PL/SQL procedure successfully completed.

Miss. Hemangi Panchal Page 26

 Relational Database Management System

PL/SQL Loops
There may be a situation when you need to execute a block of code several number of
times. In general, statements are executed sequentially: The first statement in a function
is executed first, followed by the second, and so on.

Programming languages provide various control structures that allow for more
complicated execution paths.

A loop statement allows us to execute a statement or group of statements multiple times

and following is the general form of a loop statement in most of the programming

languages −

PL/SQL provides the following types of loop to handle the looping requirements.

1. Basic Loop

2. While Loop

3. For Loop

Miss. Hemangi Panchal Page 27

 Relational Database Management System

We will learn about all three of them while covering the concept of nexted loops which
means loop inside a loop. So let's get started.

PL/SQL Basic Loop

Basic loop or simple loop is preferred in PL/SQL code when there is no surety about how
many times the block of code is to be repeated. When we use the basic loop the code
block will be executed at least once.

While using it, following two things must be considered:

● Simple loop always begins with the keyword LOOP and ends with a keyword
END LOOP.

● A basic/simple loop can be terminated at any given point by using the exit
statement or by specifying certain condition by using the statement exit when.

Syntax:

LOOP

sequence of statements

END LOOP;

Let's see the simple loop in action in the code example below.

Example

We have used the loop to print counting from 1 to 10 on the console and have used the
exit statement to break out of the loop.

SQL> set serveroutput on;

SQL> DECLARE

i int;

Miss. Hemangi Panchal Page 28

 Relational Database Management System

BEGIN

i := 1;

LOOP

if i>10 then

exit;

end if;

dbms_output.put_line(i);

i := i+1;

END LOOP;

END;

Output:

10

PL/SQL procedure successfully completed

Let's take one more example where we will be using the exit when statement to break out
of the loop.

SQL> set serveroutput on;

Miss. Hemangi Panchal Page 29

 Relational Database Management System

SQL> DECLARE

i int;

BEGIN

i := 0;

LOOP

i := i+2;

dbms_output.put_line(i);

exit WHEN i > 10;

END LOOP;

END;

Output :

10

PL/SQL procedure successfully completed

PL/SQL: While Loop

It is an entry controlled loop which means that before entering in a while loop first the
condition is tested, if the condition is TRUE the statement or a group of statements get
executed and if the condition is FALSE the control will move out of the while loop.

Syntax:

WHILE <test_condition> LOOP

<action>

END LOOP;

Miss. Hemangi Panchal Page 30

 Relational Database Management System

Let's see the while loop in action in the code example below.

Example :

Below we have a simple program to print the odd numbers between 1 to 10 using the
while loop.

SQL> set serveroutput on;

SQL> DECLARE

num int:=1;

BEGIN

while(num <= 10) LOOP

dbms_output.put_line(''|| num);

num := num+2;

END LOOP;

END;

Output :

PL/SQL procedure successfully completed.

NOTE: You must write the code to increment the value of the variable that you put in the
condition otherwise the value of the variable will remain the same and the condition will
always remain true.

PL/SQL: For Loop

Miss. Hemangi Panchal Page 31

 Relational Database Management System

This loop is used when some statements in PL/SQL code block are to be repeated for a
fixed number of times.

When we use the for loop we are supposed to define a counter variable which decides
how many time the loop will be executed based on a starting and ending value provided
at the beginning of the loop.

The for loop automatically increments the value of the counter variable by 1 at the end of
each loop cycle.

The programmer need not have to write any instruction for incrementing or decrementing
value.

Syntax:

FOR counter_variable IN start_value..end_value LOOP

statement to be executed

END LOOP;

Example :

In the example below we have used a the for loop to print numbers from 1 to 10.

SQL> set serveroutput on;

SQL> DECLARE

i number(2);

BEGIN

FOR i IN 1..10 LOOP

dbms_output.put_line(i);

END LOOP;

END;

Output:

Miss. Hemangi Panchal Page 32

 Relational Database Management System

10

PL/SQL procedure successfully completed.

Cursors

When an SQL statement is processed, Oracle creates a memory area known as context
area. A cursor is a pointer to this context area. It contains all information needed for
processing the statement. In PL/SQL, the context area is controlled by Cursor. A cursor
contains information on a select statement and the rows of data accessed by it.

A cursor is used to referred to a program to fetch and process the rows returned by the
SQL statement, one at a time. There are two types of cursors:

● Implicit Cursors

● Explicit Cursors

1) PL/SQL Implicit Cursors

The implicit cursors are automatically generated by Oracle while an SQL statement is
executed, if you don't use an explicit cursor for the statement.

Miss. Hemangi Panchal Page 33

 Relational Database Management System

These are created by default to process the statements when DML statements like
INSERT, UPDATE, DELETE etc. are executed.

Oracle provides some attributes known as Implicit cursor's attributes to check the status
of DML operations. Some of them are: %FOUND, %NOTFOUND, %ROWCOUNT and
%ISOPEN.

For example: When you execute the SQL statements like INSERT, UPDATE, DELETE
then the cursor attributes tell whether any rows are affected and how many have been
affected. If you run a SELECT INTO statement in PL/SQL block, the implicit cursor
attribute can be used to find out whether any row has been returned by the SELECT
statement. It will return an error if there no data is selected.

The following table specifies the status of the cursor with each of its attribute.

Attribute Description

%FOUND Its return value is TRUE if DML statements like INSERT, DELETE
and UPDATE affect at least one row or more rows or a SELECT
INTO statement returned one or more rows. Otherwise it returns
FALSE.

%NOTFOUND Its return value is TRUE if DML statements like INSERT, DELETE
and UPDATE affect no row, or a SELECT INTO statement return no
rows. Otherwise it returns FALSE. It is a just opposite of %FOUND.

%ISOPEN It always returns FALSE for implicit cursors, because the SQL
cursor is automatically closed after executing its associated SQL
statements.

Miss. Hemangi Panchal Page 34

 Relational Database Management System

%ROWCOUNT It returns the number of rows affected by DML statements like

INSERT, DELETE, and UPDATE or returned by a SELECT INTO
statement.

PL/SQL Implicit Cursor Example

Create customers table and have records:

ID NAME AGE ADDRESS SALARY

1 Ramesh 23 Allahabad 20000

2 Suresh 22 Kanpur 22000

3 Mahesh 24 Ghaziabad 24000

4 Chandan 25 Noida 26000

5 Alex 21 Paris 28000

6 Sunita 20 Delhi 30000

Let's execute the following program to update the table and increase salary of each
customer by 5000. Here, SQL%ROWCOUNT attribute is used to determine the number
of rows affected:

Create procedure:

Miss. Hemangi Panchal Page 35

 Relational Database Management System

DECLARE

total_rows number(2);

BEGIN

UPDATE customers

SET salary = salary + 5000;

IF sql%notfound THEN

dbms_output.put_line('no customers updated');

ELSIF sql%found THEN

total_rows := sql%rowcount;

dbms_output.put_line( total_rows || ' customers updated ');

END IF;

END;

Output:

6 customers updated

PL/SQL procedure successfully completed.

Now, if you check the records in customer table, you will find that the rows are updated.

select * from customers;

Miss. Hemangi Panchal Page 36

 Relational Database Management System

ID NAME AGE ADDRESS SALARY

1 Ramesh 23 Allahabad 25000

2 Suresh 22 Kanpur 27000

3 Mahesh 24 Ghaziabad 29000

4 Chandan 25 Noida 31000

5 Alex 21 Paris 33000

6 Sunita 20 Delhi 35000

2) PL/SQL Explicit Cursors

The Explicit cursors are defined by the programmers to gain more control over the context
area. These cursors should be defined in the declaration section of the PL/SQL block. It
is created on a SELECT statement which returns more than one row.

Following is the syntax to create an explicit cursor:

Syntax of explicit cursor

Following is the syntax to create an explicit cursor:

CURSOR cursor_name IS select_statement;

Steps:

Miss. Hemangi Panchal Page 37

 Relational Database Management System

You must follow these steps while working with an explicit cursor.

1. Declare the cursor to initialize in the memory.

2. Open the cursor to allocate memory.
3. Fetch the cursor to retrieve data.
4. Close the cursor to release allocated memory.

1) Declare the cursor:

It defines the cursor with a name and the associated SELECT statement.

Syntax for explicit cursor declaration

CURSOR name IS

SELECT statement;

2) Open the cursor:

It is used to allocate memory for the cursor and make it easy to fetch the rows returned
by the SQL statements into it.

Syntax for cursor open:

OPEN cursor_name;

3) Fetch the cursor:

It is used to access one row at a time. You can fetch rows from the above-opened cursor
as follows:

Syntax for cursor fetch:

FETCH cursor_name INTO variable_list;

4) Close the cursor:

Miss. Hemangi Panchal Page 38

 Relational Database Management System

It is used to release the allocated memory. The following syntax is used to close the
above-opened cursors.

Syntax for cursor close:

Close cursor_name;

PL/SQL Explicit Cursor Example

Explicit cursors are defined by programmers to gain more control over the context area.
It is defined in the declaration section of the PL/SQL block. It is created on a SELECT
statement which returns more than one row.

Let's take an example to demonstrate the use of explicit cursor. In this example, we are
using the already created CUSTOMERS table.

Create customers table and have records:

ID NAME AGE ADDRESS SALARY

1 Ramesh 23 Allahabad 20000

2 Suresh 22 Kanpur 22000

3 Mahesh 24 Ghaziabad 24000

4 Chandan 25 Noida 26000

Miss. Hemangi Panchal Page 39

 Relational Database Management System

5 Alex 21 Paris 28000

6 Sunita 20 Delhi 30000

Create procedure:

Execute the following program to retrieve the customer name and address.

DECLARE

c_id customers.id%type;

c_name customers.name%type;

c_addr customers.address%type;

CURSOR c_customers is

SELECT id, name, address FROM customers;

BEGIN

OPEN c_customers;

LOOP

FETCH c_customers into c_id, c_name, c_addr;

EXIT WHEN c_customers%notfound;

dbms_output.put_line(c_id || ' ' || c_name || ' ' || c_addr);

END LOOP;

CLOSE c_customers;

END;

Miss. Hemangi Panchal Page 40

 Relational Database Management System

Output:

1 Ramesh Allahabad

2 Suresh Kanpur

3 Mahesh Ghaziabad

4 Chandan Noida

5 Alex Paris

6 Sunita Delhi

PL/SQL procedure successfully completed.

PL/SQL - Exceptions

What is Exception?

An error occurs during the program execution is called Exception in PL/SQL.

PL/SQL facilitates programmers to catch such conditions using exception block in the
program and an appropriate action is taken against the error condition.

There are two type of exceptions:

● System-defined Exceptions

● User-defined Exceptions

Syntax for Exception Handling

The general syntax for exception handling is as follows. Here you can list down as many

exceptions as you can handle. The default exception will be handled using WHEN others

THEN −

Miss. Hemangi Panchal Page 41

 Relational Database Management System

DECLARE

<declarations section>

BEGIN

<executable command(s)>

EXCEPTION

<exception handling goes here >

WHEN exception1 THEN

exception1-handling-statements

WHEN exception2 THEN

exception2-handling-statements

WHEN exception3 THEN

exception3-handling-statements

........

WHEN others THEN

exception3-handling-statements

END;

Example

Let us write a code to illustrate the concept. We will be using the CUSTOMERS table we

had created and used in the previous chapters −

Miss. Hemangi Panchal Page 42

 Relational Database Management System

DECLARE

c_id customers.id%type := 8;

c_name customerS.Name%type;

c_addr customers.address%type;

BEGIN

SELECT name, address INTO c_name, c_addr

FROM customers

WHERE id = c_id;

DBMS_OUTPUT.PUT_LINE ('Name: '|| c_name);

DBMS_OUTPUT.PUT_LINE ('Address: ' || c_addr);

EXCEPTION

WHEN no_data_found THEN

dbms_output.put_line('No such customer!');

WHEN others THEN

dbms_output.put_line('Error!');

END;

When the above code is executed at the SQL prompt, it produces the following result −

Miss. Hemangi Panchal Page 43

 Relational Database Management System

No such customer!

PL/SQL procedure successfully completed.

The above program displays the name and address of a customer whose ID is given.
Since there is no customer with ID value 8 in our database, the program raises the run-
time exception NO_DATA_FOUND, which is captured in the EXCEPTION block.

Raising Exceptions
Exceptions are raised by the database server automatically whenever there is any internal

database error, but exceptions can be raised explicitly by the programmer by using the

command RAISE. Following is the simple syntax for raising an exception −

DECLARE

exception_name EXCEPTION;

BEGIN

IF condition THEN

RAISE exception_name;

END IF;

EXCEPTION

WHEN exception_name THEN

statement;

END;

Miss. Hemangi Panchal Page 44

 Relational Database Management System

You can use the above syntax in raising the Oracle standard exception or any user-
defined exception. In the next section, we will give you an example on raising a user-
defined exception. You can raise the Oracle standard exceptions in a similar way.

User-defined Exceptions
PL/SQL allows you to define your own exceptions according to the need of your program.
A user-defined exception must be declared and then raised explicitly, using either a
RAISE statement or the procedure
DBMS_STANDARD.RAISE_APPLICATION_ERROR.

The syntax for declaring an exception is −

DECLARE

my-exception EXCEPTION;

Example

The following example illustrates the concept. This program asks for a customer ID, when
the user enters an invalid ID, the exception invalid_id is raised.

DECLARE

c_id customers.id%type := &cc_id;

c_name customerS.Name%type;

c_addr customers.address%type;

-- user defined exception

ex_invalid_id EXCEPTION;

BEGIN

Miss. Hemangi Panchal Page 45

 Relational Database Management System

IF c_id <= 0 THEN

RAISE ex_invalid_id;

ELSE

SELECT name, address INTO c_name, c_addr

FROM customers

WHERE id = c_id;

DBMS_OUTPUT.PUT_LINE ('Name: '|| c_name);

DBMS_OUTPUT.PUT_LINE ('Address: ' || c_addr);

END IF;

EXCEPTION

WHEN ex_invalid_id THEN

dbms_output.put_line('ID must be greater than zero!');

WHEN no_data_found THEN

dbms_output.put_line('No such customer!');

WHEN others THEN

dbms_output.put_line('Error!');

END;

Miss. Hemangi Panchal Page 46

 Relational Database Management System

When the above code is executed at the SQL prompt, it produces the following result −

Enter value for cc_id: -6 (let's enter a value -6)

old 2: c_id customers.id%type := &cc_id;

new 2: c_id customers.id%type := -6;

ID must be greater than zero!

PL/SQL procedure successfully completed.

Pre-defined Exceptions

PL/SQL provides many pre-defined exceptions, which are executed when any database

rule is violated by a program. For example, the predefined exception NO_DATA_FOUND

is raised when a SELECT INTO statement returns no rows. The following table lists few

of the important pre-defined exceptions −

Oracle SQLC
Exception Description
Error ODE

It is raised when a null object is

ACCESS_INTO_NULL 06530 -6530
automatically assigned a value.

It is raised when none of the choices in

CASE_NOT_FOUND 06592 -6592
the WHEN clause of a CASE statement

Miss. Hemangi Panchal Page 47

 Relational Database Management System

is selected, and there is no ELSE

clause.

It is raised when a program attempts to

apply collection methods other than
EXISTS to an uninitialized nested table
COLLECTION_IS_NULL 06531 -6531
or varray, or the program attempts to
assign values to the elements of an
uninitialized nested table or varray.

It is raised when duplicate values are

DUP_VAL_ON_INDEX 00001 -1 attempted to be stored in a column with
unique index.

It is raised when attempts are made to

make a cursor operation that is not
INVALID_CURSOR 01001 -1001
allowed, such as closing an unopened
cursor.

It is raised when the conversion of a

character string into a number fails
INVALID_NUMBER 01722 -1722
because the string does not represent
a valid number.

Miss. Hemangi Panchal Page 48

 Relational Database Management System

It is raised when a program attempts to

LOGIN_DENIED 01017 -1017 log on to the database with an invalid
username or password.

It is raised when a SELECT INTO

NO_DATA_FOUND 01403 +100
statement returns no rows.

It is raised when a database call is

NOT_LOGGED_ON 01012 -1012 issued without being connected to the
database.

It is raised when PL/SQL has an

PROGRAM_ERROR 06501 -6501
internal problem.

It is raised when a cursor fetches value

ROWTYPE_MISMATCH 06504 -6504 in a variable having incompatible data
type.

It is raised when a member method is

SELF_IS_NULL 30625 -30625 invoked, but the instance of the object
type was not initialized.

It is raised when PL/SQL ran out of

STORAGE_ERROR 06500 -6500
memory or memory was corrupted.

Miss. Hemangi Panchal Page 49

 Relational Database Management System

It is raised when a SELECT INTO

TOO_MANY_ROWS 01422 -1422
statement returns more than one row.

It is raised when an arithmetic,

VALUE_ERROR 06502 -6502 conversion, truncation, or
sizeconstraint error occurs.

It is raised when an attempt is made to

ZERO_DIVIDE 01476 1476
divide a number by zero.

Miss. Hemangi Panchal Page 50