CHAPTER 5

PL/SQL INTRADUCTION
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.

Features of PL/SQL:
1. PL/SQL is basically a procedural language, which provides the functionality of decision
making, iteration and many more features of procedural programming languages.
2. PL/SQL can execute a number of queries in one block using single command.
3. One can create a PL/SQL unit such as procedures, functions, packages, triggers, and types,
which are stored in the database for reuse by applications.
4. PL/SQL provides a feature to handle the exception which occurs in PL/SQL block known as
exception handling block.
5. Applications written in PL/SQL are portable to computer hardware or operating system where
Oracle is operational.
6. PL/SQL Offers extensive error checking.

Structure of PL/SQL Block:

PL/SQL extends SQL by adding constructs found in procedural languages, resulting in a structural
language that is more powerful than SQL. The basic unit in PL/SQL is a block. All PL/SQL
programs are made up of blocks, which can be nested
within each

other.
Typically, each block performs a logical action in the program. A block has the following structure:
DECLARE
declaration statements;

BEGIN
 executable statements

EXCEPTIONS
exception handling statements

END;
• Declare section starts with DECLARE keyword in which variables, constants, records as
cursors can be declared which stores data temporarily. It basically consists definition of
PL/SQL identifiers. This part of the code is optional.
• Execution section starts with BEGIN and ends with END keyword.This is a mandatory section
and here the program logic is written to perform any task like loops and conditional statements.
It supports all DML commands, DDL commands and SQL*PLUS built-in functions as well.
• Exception section starts with EXCEPTION keyword.This section is optional which contains
statements that are executed when a run-time error occurs. Any exceptions can be handled in
this section.

Variable in PL/SQL

You must declare the PL/SQL variable in the declaration section or in a package as a global
variable. After the declaration, PL/SQL allocates memory for the variable's value and the storage
location is identified by the variable name.

Syntax variable_name [CONSTANT] datatype [NOT NULL] [:= | DEFAULT

initial_value] .

Example:

Radius Number := 5;

Date_of_birth date;

Naming rules for PL/SQL variables

The variable in PL/SQL must follow some naming rules like other programming languages.

o The variable_name should not exceed 30 characters. o Variable name should not be the
same as the table table's column of that block.
o The name of the variable must begin with ASCII letter. The PL/SQL is not case sensitive
so it could be either lowercase or uppercase. For example: v_data and V_DATA refer to
the same variables.
 o You should make your variable easy to read and understand, after the first character, it may
be any number, underscore (_) or dollar sign ($). o NOT NULL is an optional specification
on the variable.

Variable Scope in PL/SQL:

PL/SQL allows nesting of blocks. A program block can contain another inner block. If you declare
a variable within an inner block, it is not accessible to an outer block. There are two types of
variable scope:

o Local Variable: Local variables are the inner block variables which are not accessible to
outer blocks.
o Global Variable: Global variables are declared in outermost block.

Example of Local and Global variables

Let's take an example to show the usage of Local and Global variables in its simple form:

1. DECLARE
2. -- Global variables
3. num1 number := 95;
4. num2 number := 85;
5. BEGIN
6. dbms_output.put_line('Outer Variable num1: ' || num1);
7. dbms_output.put_line('Outer Variable num2: ' || num2);
8. DECLARE
9. -- Local variables
10. num1 number := 195;
11. num2 number := 185;
12. BEGIN
13. dbms_output.put_line('Inner Variable num1: ' || num1);
14. dbms_output.put_line('Inner Variable num2: ' || num2);
15. END;
16. END;
17. /

After the execution, this will produce the following result:

 Outer Variable num1: 95
Outer Variable num2: 85
Inner Variable num1: 195
Inner Variable num2: 185

PL/SQL procedure successfully completed.

PL/SQL Constants

A constant is a value used in a PL/SQL block that remains unchanged throughout the program. It
is a user-defined literal value. It can be declared and used instead of actual values.

1. constant_name CONSTANT datatype := VALUE; o Constant_name:it is the name of constant

just like variable name. The constant word is a reserved word and its value does not change.
o VALUE: it is a value which is assigned to a constant when it is declared. It can not be
assigned later.
PL/SQL Literals

Literals are the explicit numeric, character, string or boolean values which are not represented by
an identifier. For example: TRUE, NULL, etc. are all literals of type boolean. PL/SQL literals are
case-sensitive. There are following kinds of literals in PL/SQL:

o Numeric Literals

o Character Literals

o String Literals

o BOOLEAN Literals

o Date and Time Literals

Example of these different types of Literals:

Literals Examples

Numeric 75125, 3568, 33.3333333 etc.

Character 'A' '%' '9' ' ' 'z' '('

String Hello JavaTpoint!

Boolean TRUE, FALSE, NULL etc.

Date and Time '26-11-2002' , '2012-10-29 12:01:01'

Datatypes in PL/SQL

Datatype defines the type of data being used 4 broader categories of datatypes and they are:

1. Scalar Types: These are basic datatypes which generally holds a single value like a number
or a string of characters. Scalar types have 4 different categories which are listed in the
diagram above, namely Number Types, Character and String, Boolean Types and Date and
Time etc.

2. LOB Types: This datatype deals with large objects and is used to specify location of these
large objects like text files, images etc which are generally not stored outside the database.

3. Reference Types: This datatype is used to hold pointer values which generally stores
address of other program items.

4. Composite Types: Last but not the least, as the name suggests this type of data is a
composition of individual data which can be manipulated/processed separatel as well.

We won't be covering all these different datatypes below, but we will be covering the ones which
are most widely used.
NUMBER(p,s)

Range: p= 1 to 38 s= -84 to 127

This datatype is used to store numeric data. Here, p is precision s is scale. Example:

1. Age NUMBER(2); where , Age is a variable that can store 2 digits

2. percentage NUMBER(4,2); where, percentage is a variable that can store 4 (p) digits before
decimal and 2 (s) digits after decimal.

CHAR(size)

Range: 1 to 2000 bytes

• This datatype is used to store alphabetical string of fixed length.

• Its value is quoted in single quotes.
• Occupies the whole declared size of memory even if the space is not utilized by the data.

Example:

1. rank CHAR(10); where, rank is a variable that can store upto 10 characters. If the length of
data(charcaters) stored in rank is 5 then it will still occupy all the 10 spaces. 5 space in the
memory will get used and the rest blank memory spaces will be wasted.

VARCHAR(size)

Range: 1 to 2000 bytes

• This datatype is used to store alphanumeric string of variable length.

• Its value is quoted in single quotes.

• Occupies the whole declared size of memory even if the space is not utilized by the data.

1. address VARCHAR(10); where, address is a variable that can occupy maximum 10 bytes of
memory space and can store alphanumeric value in it. Unused spaces are wasted.
VARCHAR2(size)

Range: 1 to 4000 bytes

• This datatype is used to store alphanumeric string of variable length.

• Its value is quoted in single quotes.

• It releases the unused space in memory, hence saving the unused space.

Example:

1. name VARCHAR2(10); where, name is a variable that can occupy maximum 10 bytes of
memory to store an alphanumeric value. The unused memory space is released.

DATE

Range: 01-Jan-4712 BC to 31-DEC-9999

• It stores the data in date format DD-MON-YYYY

• The value for this datatype is written in single quotes.

Example:

1. DOB DATE; where, DOB is a variable that stores date of birth in defined format
(i.e,’13FEB-1991’)

%TYPE

• It stores value of that variable whose datatype is unknown and when we want the variable
to inherit the datatype of the table column.

• Also, its value is generally being retrieved from an existing table in the database, hence it
takes the datatype of the column for which it is used.

Example:

1. Student sno %TYPE;, where Student is the name of the table created in database and sno is
variable whose datatype is unknown and %TYPE is used to store its value.
BOOLEAN

• This datatype is used in conditional statements.

• It stores logical values.

• It can be either TRUE or FALSE

Example:

1. isAdmin BOOLEAN; where, isAdmin is a variable whose value can be TRUE or FALSE
depending upon the condition being checked

PL/SQL - Operators

we will discuss operators in PL/SQL. An operator is a symbol that tells the compiler to perform
specific mathematical or logical manipulation. PL/SQL language is rich in built-in operators and
provides the following types of operators −

• Arithmetic operators
• Relational operators
• Comparison operators
• Logical operators
• String operators Arithmetic Operators
Following table shows all the arithmetic operators supported by PL/SQL. Let us assume variable
A holds 10 and variable B holds 5, then −
Show Examples

Operator Description Example

+ Adds two operands A + B will give 15

- Subtracts second operand from the first A - B will give 5

* Multiplies both operands A * B will give 50

/ Divides numerator by de-numerator A / B will give 2

** Exponentiation operator, raises one operand to the power of A ** B will give

other 100000

2.Relational Operators
Relational operators compare two expressions or values and return a Boolean result. Following
table shows all the relational operators supported by PL/SQL. Let us assume variable A holds 10
and variable B holds 20, then −

Operator Description Example

= Checks if the values of two operands are equal or not, if yes then (A = B) is not
condition becomes true. true.

Checks if the values of two operands are equal or not, if values are (A != B) is
!= not equal then condition becomes true. true.
<>
~=

> Checks if the value of left operand is greater than the value of right (A > B) is not
operand, if yes then condition becomes true. true.

< Checks if the value of left operand is less than the value of right (A < B) is true.
operand, if yes then condition becomes true.

>= Checks if the value of left operand is greater than or equal to the value (A >= B) is not
of right operand, if yes then condition becomes true. true.

<= Checks if the value of left operand is less than or equal to the value (A <= B) is
of right operand, if yes then condition becomes true. true
3.Comparison Operators
Comparison operators are used for comparing one expression to another. The result is always either
TRUE, FALSE or NULL.

Operator Description Example

LIKE The LIKE operator compares a character, string, If 'Zara Ali' like 'Z% A_i' returns
or CLOB value to a pattern and returns TRUE a Boolean true, whereas, 'Nuha
if the value matches the pattern and FALSE if it Ali' like 'Z% A_i' returns a
does not. Boolean false.

BETWEEN The BETWEEN operator tests whether a value If x = 10 then, x between 5 and
lies in a specified range. x BETWEEN a AND 20 returns true, x between 5 and
b means that x >= a and x <= b. 10 returns true, but x between 11
and 20 returns false.

IN The IN operator tests set membership. x IN (set) If x = 'm' then, x in ('a', 'b', 'c')
means that x is equal to any member of set. returns Boolean false but x in
('m', 'n', 'o') returns Boolean true.

IS NULL The IS NULL operator returns the BOOLEAN If x = 'm', then 'x is null' returns
value TRUE if its operand is NULL or FALSE Boolean false.
if it is not NULL. Comparisons involving
NULL values always yield NULL.

4.Logical Operators
Following table shows the Logical operators supported by PL/SQL. All these operators work on
Boolean operands and produce Boolean results. Let us assume variable A holds true and variable
B holds false, then −

Operator Description Examples

 And Called the logical AND operator. If both the operands are true then (A and B) is
condition becomes true. false.

Or Called the logical OR Operator. If any of the two operands is true (A or B) is

then condition becomes true. true.

Not Called the logical NOT Operator. Used to reverse the logical state not (A and B)
of its operand. If a condition is true then Logical NOT operator will is true.
make it false.

5.String
PL/SQL strings could be either variables or literals. A string literal is enclosed within quotation
marks. For example,
'This is a string literal.' Or 'hello world'

Conditional control statement

1)PL/SQL If

PL/SQL supports the programming language features like conditional statements and iterative
statements. Its programming constructs are similar to how you use in programming languages like
Java and C++.

IF-THEN

Syntax for IF Statement:

There are different syntaxes for the IF-THEN-ELSE statement.

Syntax: (IF-THEN statement):

1. Statement: {It is executed when condition is true}

2. END IF;

IF-THEN-ELSE

This syntax is used when you want to execute statements only when condition is TRUE.

Syntax: (IF-THEN-ELSE statement):

1. IF condition
2. THEN
3. {...statements to execute when condition is TRUE...}
4. ELSE
5. {...statements to execute when condition is FALSE...}
6. END IF;

IF-THEN-ELSIF

This syntax is used when you want to execute one set of statements when condition is TRUE or a
different set of statements when condition is FALSE.

Syntax: (IF-THEN-ELSIF statement):

1. IF condition1
2. THEN
3. {...statements to execute when condition1 is TRUE...}
4. ELSIF condition2
5. THEN
6. {...statements to execute when condition2 is TRUE...}
7. END IF;

IF-THEN-ELSIF-ELSE statement

This syntax is used when you want to execute one set of statements when condition1 is TRUE or
a different set of statements when condition2 is TRUE.

Syntax: (IF-THEN-ELSIF-ELSE statement):

1. IF condition1
2. THEN
3. {...statements to execute when condition1 is TRUE...}
4. ELSIF condition2
5. THEN
6. {...statements to execute when condition2 is TRUE...}
7. ELSE
8. {...statements to execute when both condition1 and condition2 are FALSE...}
9. END IF;
It is the most advance syntax and used if you want to execute one set of statements when condition1
is TRUE, a different set of statement when condition2 is TRUE or a different set of statements
when both the condition1 and condition2 are FALSE.

Example of PL/SQL If Statement

Let's take an example to see the whole concept:

1. DECLARE
2. a number(3) := 500;
3. BEGIN
4. -- check the boolean condition using if statement
5. IF( a < 20 ) THEN
6. -- if condition is true then print the following
7. dbms_output.put_line('a is less than 20 ' );
8. ELSE
9. dbms_output.put_line('a is not less than 20 ' );
10. END IF;
11. dbms_output.put_line('value of a is : ' || a);
12. END;

After the execution of the above code in SQL prompt, you will get the following result:

The PL/SQL CASE statement facilitates you to execute a sequence of satatements based on a
selector. A selector can be anything such as variable, function or an expression that the CASE
statement checks to a boolean value.

The CASE statement works like the IF statement, only using the keyword WHEN. A CASE
statement is evaluated from top to bottom. If it get the condition TRUE, then the corresponding
THEN calause is executed and the execution goes to the END CASE clause.

Syntax for the CASE Statement:

1. CASE [ expression ]
2. WHEN condition_1 THEN result_1 3. WHEN condition_2 THEN
result_2
4. ...
5. WHEN condition_n THEN result_n
6. ELSE result
7. END

Example of PL/SQL case statement

Let's take an example to make it clear:

1. DECLARE
2. grade char(1) := 'A';
3. BEGIN
4. CASE grade
5. when 'A' then dbms_output.put_line('Excellent');
6. when 'B' then dbms_output.put_line('Very good');
7. when 'C' then dbms_output.put_line('Good');
8. when 'D' then dbms_output.put_line('Average');
9. when 'F' then dbms_output.put_line('Passed with Grace');
10. else dbms_output.put_line('Failed');
11. END CASE;
12. END;

After the execution of above code, you will get the following result:

PL/SQL while loop is used when a set of statements has to be executed as long as a condition is
true, the While loop is used. The condition is decided at the beginning of each iteration and
continues until the condition becomes false.

Syntax of while loop:

1. WHILE <condition>
2. LOOP statements;
3. END LOOP;
 PL/SQL WHILE Loop Example
1. DECLARE
2. VAR1 NUMBER;
3. VAR2 NUMBER;
4. BEGIN
5. VAR1:=200;
6. VAR2:=1;
7. WHILE (VAR2<=10)
8. LOOP
9. DBMS_OUTPUT.PUT_LINE (VAR1*VAR2);
10. VAR2:=VAR2+1;
11. END LOOP; 12. END;

200
400
600
800
1000
1200
1400
1600
1800
2000

BEGIN
FOR indx IN REVERSE 1 .. 5
LOOP
DBMS_OUTPUT.put_line (indx);
END LOOP;
END;
5
4
3
2
1
Pl sql loop label syntax:
 PL/SQL FOR Loop
PL/SQL for loop is used when when you want to execute a set of statements for a
predetermined number of times. The loop is iterated between the start and end integer values.
The counter is always incremented by 1 and once the counter reaches the value of end integer,
the loop ends.

Syntax of for loop:

1. FOR counter IN initial_value .. final_value LOOP

2. LOOP statements;
3. END LOOP; initial_value : Start integer value
o final_value : End integer value

PL/SQL For Loop Example 1

Let's see a simple example of PL/SQL FOR loop.

1. BEGIN
2. FOR k IN 1..10 LOOP
3. -- note that k was not declared
4. DBMS_OUTPUT.PUT_LINE(k);
5. END LOOP;
6. END;

Exception Handling in PL/SQL

An exception is an error which disrupts the normal flow of program instructions. PL/SQL provides
us the exception block which raises the exception thus helping the programmer to find out the fault
and resolve it.
There are two types of exceptions defined in PL/SQL
1. User defined exception.
2. System defined exceptions. Syntax to write an exception
WHEN exception THEN
statement;
DECLARE
declarations section;
BEGIN
executable command(s);
EXCEPTION
WHEN exception1 THEN
statement1;
WHEN exception2 THEN
statement2;
[WHEN others THEN]
/* default exception handling code */
END;
1. System defined exceptions:
System-defined exceptions are further divided into two categories:
1. Named system exceptions.
2. Unnamed system exceptions.
Named system exceptions: They have a predefined name by the system like
ACCESS_INTO_NULL, DUP_VAL_ON_INDEX, LOGIN_DENIED etc. the list is quite
big.
So we will discuss some of the most commonly used exceptions:
Lets create a table geeks.
create table geeks(g_id int , g_name varchar(20), marks
int); insert into geeks values(1, 'Suraj',100); insert into
geeks values(2, 'Praveen',97);
insert into geeks values(3, 'Jessie', 99);

1. NO_DATA_FOUND: It is raised WHEN a SELECT

INTO statement returns no rows. For eg:

DECLARE

temp varchar(20);
 BEGIN

SELECT g_id into temp from geeks where g_name='GeeksforGeeks';

exception

WHEN no_data_found THEN

dbms_output.put_line('ERROR');

dbms_output.put_line('there is no name as');

dbms_output.put_line('GeeksforGeeks in geeks table');

end;

Output:
ERROR
there is no name as GeeksforGeeks in geeks table
TOO_MANY_ROWS:It is raised WHEN a SELECT INTO statement returns more than one
row.

DECLARE

temp varchar(20);

BEGIN
 -- raises an exception as SELECT

-- into trying to return too many rows

SELECT g_name into temp from geeks;

dbms_output.put_line(temp);

EXCEPTION

WHEN too_many_rows THEN

dbms_output.put_line('error trying to SELECT too many rows');

end;

Output:
error trying to SELECT too many rows
VALUE_ERROR:This error is raised WHEN a statement is executed that resulted in an
arithmetic, numeric, string, conversion, or constraint error. This error mainly results from
programmer error or invalid data input.

DECLARE

temp number;
 BEGIN

SELECT g_name into temp from geeks where g_name='Suraj';

dbms_output.put_line('the g_name is '||temp);

EXCEPTION

WHEN value_error THEN

dbms_output.put_line('Error');

dbms_output.put_line('Change data type of temp to varchar(20)');

END;

Output:
Error
Change data type of temp to varchar(20)
ZERO_DIVIDE = raises exception WHEN dividing with zero.

DECLARE

a int:=10;

b int:=0;

answer int;
 BEGIN

answer:=a/b;

dbms_output.put_line('the result after division is'||answer);

exception

WHEN zero_divide THEN

dbms_output.put_line('dividing by zero please check the values again');

dbms_output.put_line('the value of a is '||a);

dbms_output.put_line('the value of b is '||b);

END;

Output:
dividing by zero please check the values again the
value of a is 10
the value of b is 0

• Unnamed system exceptions:Oracle doesn’t provide name for some system exceptions
called unnamed system exceptions.These exceptions don’t occur frequently.These
exceptions have two parts code and an associated message.
The way to handle to these exceptions is to assign name to them using Pragma
EXCEPTION_INIT
Syntax:
• PRAGMA EXCEPTION_INIT(exception_name, -error_number);
error_number are pre-defined and have negative integer range from -20000 to -20999.
Example:

DECLARE

exp exception;

pragma exception_init (exp, -20015);

n int:=10;

BEGIN

FOR i IN 1..n LOOP

dbms_output.put_line(i*i);

IF i*i=36 THEN

RAISE exp;

END IF;

END LOOP;

EXCEPTION

WHEN exp

THEN

dbms_output.put_line('Welcome to GeeksforGeeks');
 1
4
9
16
25
36
Welcome to GeeksforGeeks
1.

2. User defined exceptions: This type of users can create their own exceptions
according to the need and to raise these exceptions explicitly raise command is used.
Example:
• Divide non-negative integer x by y such that the result is greater than or equal
to 1. From the given question we can conclude that there exist two exceptions
• Division be zero.
• If result is greater than or equal to 1 means y is less than or equal to x.

DECLARE

x int:=&x; /*taking value at run time*/

y int:=&y; div_r float;

exp1 EXCEPTION;
exp2 EXCEPTION;
BEGIN

IF y=0 then

raise exp1;

ELSEIF y > x then

raise exp2;

ELSE

div_r:= x / y; dbms_output.put_line('the

result is '||div_r);

END IF;

EXCEPTION

WHEN exp1 THEN

dbms_output.put_line('Error');

dbms_output.put_line('division by zero not allowed');

 Output: the result is 2 Input
2: x = 20
y=0

Output: Error
division by zero not allowed Input
3: x=20
y = 30

Output:<.em> Error
y is greater than x please check the input

STRING FUNCTION AND OPERATORS

The string in PL/SQL is actually a sequence of characters with an optional size specification.
The characters could be numeric, letters, blank, special characters or a combination of all.

Declaring String Variables

DECLARE
name varchar2(20);
company varchar2(30);

PL/SQL String Functions and Operators

PL/SQL offers the concatenation operator (||) for joining two strings. The following table provides
the string functions provided by PL/SQL −
S.No Function & Purpose

ASCII(x);
1
Returns the ASCII value of the character x.

CHR(x);
2
Returns the character with the ASCII value of x.

CONCAT(x, y);
3
Concatenates the strings x and y and returns the appended string.

INITCAP(x);
4
Converts the initial letter of each word in x to uppercase and returns that string.

INSTR(x, find_string [, start] [, occurrence]);

5
Searches for find_string in x and returns the position at which it occurs.

INSTRB(x);
6
Returns the location of a string within another string, but returns the value in bytes.

LENGTH(x);
7
Returns the number of characters in x.

LENGTHB(x);
8
Returns the length of a character string in bytes for single byte character set.

LOWER(x);
9
Converts the letters in x to lowercase and returns that string.

LPAD(x, width [, pad_string]) ;

10
Pads x with spaces to the left, to bring the total length of the string up to width characters.

LTRIM(x [, trim_string]);
11
Trims characters from the left of x.

NANVL(x, value);
12
Returns value if x matches the NaN special value (not a number), otherwise x is returned.

NLS_INITCAP(x);
13 Same as the INITCAP function except that it can use a different sort method as specified by
NLSSORT.

NLS_LOWER(x) ;
14 Same as the LOWER function except that it can use a different sort method as specified by
NLSSORT.

15 NLS_UPPER(x);
 Same as the UPPER function except that it can use a different sort method as specified by
NLSSORT.

NLSSORT(x);
16 Changes the method of sorting the characters. Must be specified before any NLS function;
otherwise, the default sort will be used.

NVL(x, value);
17
Returns value if x is null; otherwise, x is returned.

NVL2(x, value1, value2);

18
Returns value1 if x is not null; if x is null, value2 is returned.

REPLACE(x, search_string, replace_string);

19
Searches x for search_string and replaces it with replace_string.

RPAD(x, width [, pad_string]);

20
Pads x to the right.

RTRIM(x [, trim_string]);
21
Trims x from the right.

SOUNDEX(x) ;
22
Returns a string containing the phonetic representation of x.

SUBSTR(x, start [, length]);

23 Returns a substring of x that begins at the position specified by start. An optional length for the
substring may be supplied.

SUBSTRB(x);
24 Same as SUBSTR except that the parameters are expressed in bytes instead of characters for the
single-byte character systems.

TRIM([trim_char FROM) x);

25
Trims characters from the left and right of x.

UPPER(x);
26
Converts the letters in x to uppercase and returns that string.

Let us now work out on a few examples to understand the concept −

Example 1

DECLARE
greetings varchar2(11) := 'hello world';
BEGIN
dbms_output.put_line(UPPER(greetings));
 dbms_output.put_line(LOWER(greetings));

dbms_output.put_line(INITCAP(greetings));

/* retrieve the first character in the string */

dbms_output.put_line ( SUBSTR (greetings, 1, 1));

/* retrieve the last character in the string */

dbms_output.put_line ( SUBSTR (greetings, -1, 1));

/* retrieve five characters,

starting from the seventh position. */
dbms_output.put_line ( SUBSTR (greetings, 7, 5));

/* retrieve the remainder of the string,

starting from the second position. */
dbms_output.put_line ( SUBSTR (greetings, 2));

/* find the location of the first "e" */

dbms_output.put_line ( INSTR (greetings, 'e'));
END;
/

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

HELLO WORLD
hello world
Hello World
h
d
World
ello World
2
ARRAY IN PL SQL

. The PL/SQL programming language provides a data structure called the VARRAY, which can store
a fixed-size sequential collection of elements of the same type. A varray is used to store an ordered
collection of data, however it is often better to think of an array as a collection of variables of the
same type.

All varrays consist of contiguous memory locations. The lowest address corresponds to the first
element and the highest address to the last element.
Creating a Varray Type

A varray type is created with the CREATE TYPE statement. You must specify the maximum size
and the type of elements stored in the varray.

The basic syntax for creating a VARRAY type at the schema level is −

CREATE OR REPLACE TYPE varray_type_name IS VARRAY(n) of <element_type>

Where,

• varray_type_name is a valid attribute name,

• n is the number of elements (maximum) in the varray,
• element_type is the data type of the elements of the array.

Maximum size of a varray can be changed using the ALTER TYPE statement.

For example,

CREATE Or REPLACE TYPE namearray AS VARRAY(3) OF VARCHAR2(10);

/

Type created.

The basic syntax for creating a VARRAY type within a PL/SQL block is −

TYPE varray_type_name IS VARRAY(n) of <element_type>

For example −

TYPE namearray IS VARRAY(5) OF VARCHAR2(10);

Type grades IS VARRAY(5) OF INTEGER;

Let us now work out on a few examples to understand the concept −

Example 1

The following program illustrates the use of varrays −

DECLARE
type namesarray IS VARRAY(5) OF VARCHAR2(10);
type grades IS VARRAY(5) OF INTEGER;
names namesarray;
marks grades;
total integer;
BEGIN
names := namesarray('Kavita', 'Pritam', 'Ayan', 'Rishav', 'Aziz');
marks:= grades(98, 97, 78, 87, 92);
total := names.count;
dbms_output.put_line('Total '|| total || ' Students');
FOR i in 1 .. total LOOP
dbms_output.put_line('Student: ' || names(i) || '
Marks: ' || marks(i));
END LOOP;
END;
/

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

Total 5 Students
Student: Kavita Marks: 98
Student: Pritam Marks: 97
Student: Ayan Marks: 78
Student: Rishav Marks: 87
Student: Aziz Marks: 92
PL/SQL Cursor

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:

o Implicit Cursors
o 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.
These are created by default to process the statements when DML statements like INSERT, UPDATE,
DELETE etc. are executed.

Orcale 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.

CURSOR attribute.

Attribute Description

%FOUND Its return value is TRUE if DML statements like INSERT, DELETE and UPDATE affect at le
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,
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 close
associated SQL statements.

%ROWCOUNT It returns the number of rows affected by DML statements like INSERT, DELETE, and UPDA
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:

1. DECLARE
2. total_rows number(2);
3. BEGIN
4. UPDATE customers
5. SET salary = salary + 5000;
6. IF sql%notfound THEN
7. dbms_output.put_line('no customers updated');
8. ELSIF sql%found THEN
9. total_rows := sql%rowcount;
10. dbms_output.put_line( total_rows || ' customers updated ');
11. END IF;
12. END;
13. /

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.

1. select * from customers;

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:

1. CURSOR cursor_name IS select_statement;;

ADVERTISEMENT

Steps:

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 decleration

1. CURSOR name IS
2. 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:

1. 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:

1. FETCH cursor_name INTO variable_list;

4) Close the cursor:

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

Syntax for cursor close:

1. 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

5 Alex 21 Paris 28000

6 Sunita 20 Delhi 30000

Create procedure:
 Execute the following program to retrieve the customer name and address.

1. DECLARE
2. c_id customers.id%type;
3. c_name customers.name%type;
4. c_addr customers.address%type;
5. CURSOR c_customers is
6. SELECT id, name, address FROM customers;
7. BEGIN
8. OPEN c_customers;
9. LOOP
10. FETCH c_customers into c_id, c_name, c_addr;
11. EXIT WHEN c_customers%notfound;
12. dbms_output.put_line(c_id || ' ' || c_name || ' ' || c_addr);
13. END LOOP;
14. CLOSE c_customers;
15. END;
16. /

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 procedure successfully completed.

PL/SQL - Functions

In this chapter, we will discuss the functions in PL/SQL. A function is same as a procedure except
that it returns a value. Therefore, all the discussions of the previous chapter are true for functions too.

Creating a Function
A standalone function is created using the CREATE FUNCTION statement. The simplified syntax
for the CREATE OR REPLACE PROCEDURE statement is as follows −

CREATE [OR REPLACE] FUNCTION function_name

[(parameter_name [IN | OUT | IN OUT] type [, ...])]
RETURN return_datatype
{IS | AS}
BEGIN
< function_body >
END [function_name];

Where,

• function-name specifies the name of the function.

• [OR REPLACE] option allows the modification of an existing function.
• The optional parameter list contains name, mode and types of the parameters. IN represents
the value that will be passed from outside and OUT represents the parameter that will be used
to return a value outside of the procedure.
• The function must contain a return statement.
• The RETURN clause specifies the data type you are going to return from the function.
• function-body contains the executable part.
• The AS keyword is used instead of the IS keyword for creating a standalone function.

Example

The following example illustrates how to create and call a standalone function. This function returns
the total number of CUSTOMERS in the customers table.

We will use the CUSTOMERS table, which we had created in the PL/SQL Variables chapter −

Select * from customers;

+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
+----+----------+-----+-----------+----------+
CREATE OR REPLACE FUNCTION totalCustomers
RETURN number IS
total number(2) := 0;
BEGIN
SELECT count(*) into total
FROM customers;

RETURN total;
END;
/
PL/SQL - Procedures

• Procedures − These subprograms do not return a value directly; mainly used to perform an
action.

This chapter is going to cover important aspects of a PL/SQL procedure. We will discuss PL/SQL
function in the next chapter.

Parts of a PL/SQL Subprogram

Each PL/SQL subprogram has a name, and may also have a parameter list. Like anonymous PL/SQL
blocks, the named blocks will also have the following three parts −

S.No Parts & Description

Declarative Part
It is an optional part. However, the declarative part for a subprogram does not start with the
1 DECLARE keyword. It contains declarations of types, cursors, constants, variables, exceptions,
and nested subprograms. These items are local to the subprogram and cease to exist when the
subprogram completes execution.

Executable Part
2
This is a mandatory part and contains statements that perform the designated action.

Exception-handling
3
This is again an optional part. It contains the code that handles run-time errors.

Creating a Procedure

A procedure is created with the CREATE OR REPLACE PROCEDURE statement. The simplified
syntax for the CREATE OR REPLACE PROCEDURE statement is as follows −

CREATE [OR REPLACE] PROCEDURE procedure_name

[(parameter_name [IN | OUT | IN OUT] type [, ...])]
{IS | AS}
BEGIN
< procedure_body >
END procedure_name;

Where,

• procedure-name specifies the name of the procedure.

• [OR REPLACE] option allows the modification of an existing procedure.
• The optional parameter list contains name, mode and types of the parameters. IN represents
the value that will be passed from outside and OUT represents the parameter that will be used
to return a value outside of the procedure.
• procedure-body contains the executable part.
• The AS keyword is used instead of the IS keyword for creating a standalone procedure.

Example

The following example creates a simple procedure that displays the string 'Hello World!' on the
screen when executed.

CREATE OR REPLACE PROCEDURE greetings

AS
BEGIN
dbms_output.put_line('Hello World!');
END;
/

The following table lists out the parameter modes in PL/SQL subprograms −

S.No Parameter Mode & Description

IN
An IN parameter lets you pass a value to the subprogram. It is a read-only parameter. Inside the
subprogram, an IN parameter acts like a constant. It cannot be assigned a value. You can pass a
1
constant, literal, initialized variable, or expression as an IN parameter. You can also initialize it to
a default value; however, in that case, it is omitted from the subprogram call. It is the default
mode of parameter passing. Parameters are passed by reference.

OUT
An OUT parameter returns a value to the calling program. Inside the subprogram, an OUT
2
parameter acts like a variable. You can change its value and reference the value after assigning
it. The actual parameter must be variable and it is passed by value.

3 IN OUT
An IN OUT parameter passes an initial value to a subprogram and returns an updated value to the
caller. It can be assigned a value and the value can be read.
The actual parameter corresponding to an IN OUT formal parameter must be a variable, not a
constant or an expression. Formal parameter must be assigned a value. Actual parameter is
passed by value.