Unit - V

**************************************************************************
Introduction to PL/SQL: SQL v/s PL/SQL, PL/SQL Block Structure, Language
construct of PL/SQL (Variable, Basic Composite Data Type, Conditions, Looping etc.),
% TYPE and % ROWTYPE, Using Cursor (Implicit, explicit).
**************************************************************************

 Several database systems support their own procedural languages, such as PL/SQL in Oracle and
TransactSQL in Microsoft SQLServer.
 Oracle has two main procedural languages, PL/SQL and Java.
 PL/SQL was Oracle’s original language for stored procedures and it has syntax similar to that used
in the Ada language.
 Java is supported through a Java virtual machine inside the database engine. Oracle provides a
package to encapsulate related procedures, functions, and variables into single units.
 Oracle supports SQLJ (SQL embedded in Java) and JDBC, and provides a tool to generate Java
class definitions corresponding to user-defined database types.

Introduction to PL/SQL:

 PL/SQL stands for Procedural Language extension of SQL. It was developed by Oracle Corporation
in the late 1980s to enhance the capabilities of SQL. It is the procedural extension language for
SQL.

PL/SQL block structure:

DECLARE
Declaration statements;
BEGIN
Execution statements;
EXCEPTION
Exception handling statements;
END;
/

PL/SQL Block sections:

1. Declaration section (optional).

2. Execution section (mandatory).
3. Exception handling section (optional).

Declaration section:

 It is an optional section and starts with DECLARE keyword. It is used to declare the variables,
constants, records and cursors etc.

Gajendra Chourey Page 95

Execution section:

 Execution section starts with BEGIN keyword and ends with END keyword. It is a mandatory
section. It is used to write the program logic code.
Note: Execution section must have one statement.

Exception handling section:

 Exception section starts with EXCEPTION keyword. It is an optional section. It is used to handle the
exceptions occurred in execution section.

Note:
1. Every PL/SQL statement will be followed by semicolon (;).
2. PL/SQL blocks can be nested.

Advantages of PL/SQL:

1. PL/SQL is a procedural language.

2. PL/SQL is a block structure language.
3. PL/SQL handles the exceptions.
4. PL/SQL engine can process the multiple SQL statements simultaneously as a single block hence
reduce network traffic and provides better performance.

SQL v/s PL/SQL

1. The basic difference between two languages is that SQL executes the single query at a time whereas,
Pl/SQL executes the block of code at once. This results in reduced network traffic.
2. SQL is a Structured Query Language or SQL is a declarative language, it only specifies what data is needed
whereas, PL/SQL is a Procedural Language/ Structured Query Language it specifies both what data is
needed and how it can be retrieved.
3. SQL act as a source to retrieve data that will be displayed by the application created using PL/SQL.
4. SQL queries and commands are written using DDL (Data Definition Language), DML (Data Manipulation
Language). However, using PL/SQL you can write programming block which has procedures, functions,
triggers, packages, variables within its syntax.
5. SQL queries are used to retrieve data from the database; you can also add or delete or modify the data in
the database. On the other hands, PL/SQL is used to create applications that can display the information
retrieved by the SQL.
6. You can embed SQL queries within the syntax of PL/SQL. However, the opposite is not possible.
7. Unlike PL/SQL, there is direct interaction between SQL and the database server.
8. PL/SQL offers high processing speed while performing manipulation of large volumes of data. This can’t be
achieved with SQL.
9. Due to the detail-oriented nature of SQL and that it can directly interact with the databases), SQL
statements are a great option for creating analytical reports. Since it writes DML statements, it also finds
use in supporting applications where there is a need for simple updating. Essentially, it is designed for data
manipulation and does just that.
PL/SQL is application-based and is primarily used to design applications such as building user screens or
creating back-end logic for web pages. SQL is responsible for providing data for these PL/SQL based
applications. PL/SQL can be integrated with Java and PHP to create complex logic.

Gajendra Chourey Page 96

PL/SQL Datatypes

 Every constant, variable, and parameter has a datatype (or type), which specifies a
storage format, constraints, and valid range of values. PL/SQL provides many predefined
datatypes. For instance, you can choose from integer, floating point, character, BOOLEAN,
date, collection, reference, and large object (LOB) types. PL/SQL also lets you define your
own subtypes.

Overview of Predefined PL/SQL Datatypes

 Predefined PL/SQL datatypes are grouped into composite, LOB, reference, and scalar
type categories.

[Link] Category & Description

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

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

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

4
Reference
Pointers to other data items.

PL/SQL Scalar Data Types and Subtypes

PL/SQL Scalar Data Types and Subtypes come under the following categories −
[Link] Date Type & Description

1
Numeric
Numeric values on which arithmetic operations are performed.

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

3
Boolean
Logical values on which logical operations are performed.

4
Datetime
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.

Gajendra Chourey Page 97

PL/SQL Numeric Data Types and Subtypes
Following table lists out the PL/SQL pre-defined numeric data types and their sub-types −
[Link] Data Type & Description

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Gajendra Chourey Page 98

PL/SQL Character Data Types and Subtypes
Following is the detail of PL/SQL pre-defined character data types and their sub-types −
[Link] Data Type & Description

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

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

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

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

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

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

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

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

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

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.

Gajendra Chourey Page 99

 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

YEAR -4712 to 9999 (excluding year 0) Any nonzero integer

MONTH 01 to 12 0 to 11

DAY 01 to 31 (limited by the values of MONTH Any nonzero integer

and YEAR, according to the rules of the
calendar for the locale)

HOUR 00 to 23 0 to 23

MINUTE 00 to 59 0 to 59

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

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

TIMEZONE_HOUR -12 to 14 (range accommodates daylight Not applicable

savings time changes)

TIMEZONE_MINUTE 00 to 59 Not applicable

TIMEZONE_REGION Found in the dynamic performance view Not applicable

V$TIMEZONE_NAMES

TIMEZONE_ABBR Found in the dynamic performance view Not applicable

V$TIMEZONE_NAMES

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

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

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

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

Gajendra Chourey Page 100

 database.

CLOB Used to store large blocks of character 8 to 128 TB

data in the database.

NCLOB Used to store large blocks of NCHAR data 8 to 128 TB

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);
END;
/
Output:
Hello Reader Welcome to the World of PL/SQL

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.

Gajendra Chourey Page 101

Variable:
 Variable is the name of reserved memory location.
 Each variable has a specific data type which determines the range of values and
set of operations for that variable.

PL/SQL variables naming rules:

 A variable name can’t contain more than 30 characters.

 A variable name must start with an ASCII letter followed by any number,
underscore (_) or dollar sign ($).
 PL/SQL is case-insensitive i.e. var and VAR refer to the same variable.

How to declare variable in PL/SQL:

 We have to declare a PL/SQL variable in the declaration section or in a package as

a global variable.
 After declaration PL/SQL allocates memory for the variable and variable name is
used to identify the storage location.

Syntax:

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

Where:

variable_name is a valid identifier name.

datatype is a valid PL/SQL datatype.

Initializing Variables in PL/SQL:

 When we declare a variable PL/SQL assigns it NULL as default value.

 If we want to initialize a variable with a non-NULL value, we can do it during
the declaration.
 We can use any one of the following methods:

1. The DEFAULT keyword

Num1 binary_integer := 0;

2. The assignment operator

siteName varchar2(20) DEFAULT ‘ABVU’;

Gajendra Chourey Page 102

 Example:

DECLARE
var1 integer := 20;
var2 integer := 40;
var3 integer;
var4 real;
BEGIN
var3 := var1 + var2;
dbms_output.put_line('Value of var3: ' || var3);
var4 := 50.0/3.0;
dbms_output.put_line('Value of var4: ' || var4);
END;
/
Output
Value of var3: 60
Value of var4: 16.66666666666666666666666666666666666667

Variable Scope in PL/SQL:

 As we discussed that PL/SQL allows the nesting of blocks i.e. blocks with
blocks.
 Based on the nesting structure PL/SQL variables can be divide into following
categories:

Local variables – Those variables which are declared in an inner block and not
accessible to outer blocks are known as local variables.

Global variables – Those variables which are declared in the outer block or a
package and accessible to itself and inner blocks are known as global variables.

Example:

DECLARE
-- Global variables
num1 number := 10;
num2 number := 20;
BEGIN
dbms_output.put_line('Outer Variable num1: ' || num1);
dbms_output.put_line('Outer Variable num2: ' || num2);
DECLARE
-- Local variables
num3 number := 30;
num4 number := 40;
BEGIN
dbms_output.put_line('Outer variable in inner block num1: ' || num1);

Gajendra Chourey Page 103

 dbms_output.put_line('Outer variable in inner block num2: ' || num2);
dbms_output.put_line('Inner Variable num3: ' || num3);
dbms_output.put_line('Inner Variable num4: ' || num4);
END;
END;
/
Output:
Outer Variable num1: 10
Outer Variable num2: 20
Outer variable in inner block num1: 10
Outer variable in inner block num2: 20
Inner Variable num3: 30
Inner Variable num4: 40

PL/SQL Constants:

 A constant holds a value used in a PL/SQL block that does not change
throughout the program.
 It is a user-defined literal value.

Syntax to declare a constant:

constant_name CONSTANT datatype := VALUE;

Where:

constant_name – is a valid identifier name.

CONSTANT – is a keyword.

VALUE – is a value which must be assigned to a constant when it is

declared. You cannot assign a value later.

Example:

DECLARE
-- constant declaration
pi constant number := 3.141592654;
-- other declarations
radius number(5,2);
dia number(5,2);
circumference number(7, 2);
area number (10, 2);
BEGIN
-- processing
radius := 10.5;
dia := radius * 2;
circumference := 2.0 * pi * radius;

Gajendra Chourey Page 104

 area := pi * radius * radius;
-- output
dbms_output.put_line('Radius: ' || radius);
dbms_output.put_line('Diameter: ' || dia);
dbms_output.put_line('Circumference: ' || circumference);
dbms_output.put_line('Area: ' || area);
END;
/
Output:
Radius: 10.5
Diameter: 21
Circumference: 65.97
Area: 346.36

PL/SQL Literals:

 Literals is an explicit numeric, character, string or Boolean values which are

not represented by identifiers i.e. TRUE, NULL, abvu etc.

Note: PL/SQL literals are case-sensitive.

Types of literals in PL/SQL:

1. Numeric Literals (765, 23.56 etc.).

2. Character Literals (‘A’ ‘%’ ‘9’ ‘ ‘ ‘z’ etc.).

3. String Literals ([Link] etc.).

4. BOOLEAN Literals (TRUE, FALSE and NULL).

5. Date and Time Literals (‘2016-12-25’ ‘2016-02-03 12:10:01’ etc.).

Hello World program:

DECLARE
-- variable declaration
message varchar2(20):= 'Hello World!';
BEGIN
--output
dbms_output.put_line(message);
END;
/
Output: Hello World!

Gajendra Chourey Page 105

Conditional Statements:

PL/SQL If statement:

 If statement is used to execute a block of statements if specified condition is

true.

Commonly used PL/SQL If statement:

IF-THEN statement:

Syntax:

IF condition

THEN

//Block of statements1

END IF;

Block of statements1 executes when the specified condition is true.

IF-THEN-ELSE statement:

Syntax:

IF condition

THEN

//Block of statements1

ELSE

//Block of statements2

END IF;

Block of statements1 executes when the specified condition is true otherwise

Block of statements2 executes.

IF-THEN-ELSIF statement:
Syntax:

IF condition1

THEN

//Block of statements1

Gajendra Chourey Page 106

 ELSIF condition2

//Block of statements2

ELSE

//Block of statements3

END IF;

Block of statements1 executes when condition1 is true if false codition2 is

checked and Block of statements2 executes if condition2 is true and so on.
Block of statements in ELSE block executes when no condition is true.

Example:

DECLARE
var number(3) := 50;
BEGIN
IF (var = 10) THEN
dbms_output.put_line('Value of var is 10');
ELSIF (var = 20) THEN
dbms_output.put_line('Value of var is 20');
ELSIF (var = 30) THEN
dbms_output.put_line('Value of var is 30');
ELSE
dbms_output.put_line('None of the above condition is true.');
END IF;
dbms_output.put_line('Exact value of var is: '|| var);
END;
/
Output:
None of the above condition is true.
Exact value of var is: 50

PL/SQL Switch Case Statement:

 Switch statement is used to execute a block of statement based on the switch

expression value.
 An expression must be of type int, short, byte or char.
 A case value should be a constant literal value and cannot be duplicated.
 Expression value is compared with each case value. If a match found
corresponding block of statements will be executed.
 A break statement is used to terminate the execution of statements.
 If no case value matches with expression value then default block of statements
will be executed.
 If break statement is not used within case, all matching cases will be executed.

Gajendra Chourey Page 107

 Syntax:

CASE [expression]

WHEN condition1 THEN Block of statements1

WHEN condition2 THEN Block of statements2

...

WHEN conditionn THEN Block of statementsn

ELSE Block of statements

END

Example:

DECLARE
nameChar char(1) := 'J';
BEGIN
CASE nameChar
when 'B' then dbms_output.put_line('Bharat');
when 'R' then dbms_output.put_line('Richi');
when 'S' then dbms_output.put_line('Sahdev');
when 'V' then dbms_output.put_line('Vinod');
when 'H' then dbms_output.put_line('Harish');
when 'M' then dbms_output.put_line('Mahesh');
when 'V' then dbms_output.put_line('Vivek');
when 'A' then dbms_output.put_line('Anil');
when 'J' then dbms_output.put_line('Jai');
else dbms_output.put_line('No such name');
END CASE;
END;
/
Output: Jai

Looping Statements:

PL SQL EXIT LOOP:

 The loop repeatedly executes a block of statements until it reaches a loop exit.
The EXIT and EXIT WHEN statements are used to terminate a loop.

Where:

EXIT: The EXIT statement is used to terminate the loop unconditionally

and normally used with IF statement.

Gajendra Chourey Page 108

 EXIT WHEN: The EXIT WHEN statement is used to terminate the loop
conditionally. It terminates the loop when the specified condition is true.

Syntax with EXIT: Example with EXIT: Output:

LOOP DECLARE 1
num NUMBER := 1; 2
//block of statements
BEGIN 3
EXIT; LOOP 4
DBMS_OUTPUT.PUT_LINE(num); 5
END LOOP;
IF num = 10 THEN 6
EXIT; 7
END IF; 8
num := num+1; 9
END LOOP; 10
END;

Syntax with EXIT WHEN: Example with EXIT WHEN: Output:

LOOP DECLARE 1
num NUMBER := 1; 2
//block of statements
BEGIN 3
EXIT WHEN condition; 4
LOOP
5
END LOOP; DBMS_OUTPUT.PUT_LINE(num);
6
EXIT WHEN num = 10; 7
num := num+1; 8
END LOOP; 9
END; 10

PL SQL WHILE LOOP:

 The while loop repeatedly executes a block of statements until a particular

condition is true.
 It first check the condition and executes a block of statements if condition is
true.

Syntax: Example: Output:

WHILE condition DECLARE 1
num NUMBER := 1; 2
LOOP
BEGIN 3
//block of statements; WHILE num <= 10 4
LOOP 5
END LOOP;
DBMS_OUTPUT.PUT_LINE(num); 6
num := num+1; 7
END LOOP; 8
END; 9
10

Gajendra Chourey Page 109

PL SQL FOR IN LOOP:

 The for in loop repeatedly executes a block of statements for a fixed number of
times.
 The loop iteration occurs between the start and end integer values.
 The counter is always incremented by 1 and loop terminates when the counter
reaches the value of the end integer.

Syntax:

FOR loop_counter IN [REVERSE] start_value .. end_value

LOOP

//block of statements.

END LOOP;

Note:
1. The double dot (..) specifies the range operator.
2. By default iteration is from start_value to end_value but we can reverse
the iteration process by using REVERSE keyword.
3. No need to declare the counter variable explicitly because it is declared
implicitly in the declaration section.
4. The counter variable is incremented by 1 and does not need to be
incremented explicitly.
5. The EXIT and EXIT WHEN statements can be used.

Example: (1 to 10) Output: Example: (10 to 1) Output:

DECLARE 1 DECLARE 10
BEGIN 2 BEGIN 9
3 FOR var IN REVERSE 1..10 8
FOR var IN 1..10 4 7
LOOP
LOOP 5 6
DBMS_OUTPUT.PUT_LINE(var); 6 5
7
DBMS_OUTPUT.PUT_LINE(var); 4
END LOOP; END LOOP;
8 3
END; 9 END; 2
10 1

PL SQL CONTINUE STATEMENT:

 The continue statement is a control statement which is used to skip the

following statement in the body of the loop and continue with the next
iteration of the loop.

Syntax: Example: Output:

continue; DECLARE 1
num NUMBER := 0; 2
BEGIN 3

Gajendra Chourey Page 110

 WHILE num < 10 4
LOOP 6
num := num +1; 7
8
IF num = 5 THEN
9
CONTINUE;
10
END IF;
DBMS_OUTPUT.PUT_LINE(num);
END LOOP;
END;

PL SQL LOOP LABEL:

 The loop can be labeled by loop labels.

Syntax: Example: Output:

DECLARE i is: 1 and j is: 1
i is: 1 and j is: 2
<< label >> i number(1);
i is: 1 and j is: 3
j number(1); i is: 1 and j is: 4
BEGIN i is: 1 and j is: 5
<< outer_loop >> i is: 2 and j is: 1
FOR i IN 1..5 LOOP i is: 2 and j is: 2
i is: 2 and j is: 3
<< inner_loop >> i is: 2 and j is: 4
FOR j IN 1..5 LOOP i is: 2 and j is: 5
dbms_output.put_line('i is: '|| i || ' and j is: ' || j); i is: 3 and j is: 1
END loop inner_loop; i is: 3 and j is: 2
i is: 3 and j is: 3
END loop outer_loop;
i is: 3 and j is: 4
END; i is: 3 and j is: 5
/ i is: 4 and j is: 1
i is: 4 and j is: 2
i is: 4 and j is: 3
i is: 4 and j is: 4
i is: 4 and j is: 5
i is: 5 and j is: 1
i is: 5 and j is: 2
i is: 5 and j is: 3
i is: 5 and j is: 4
i is: 5 and j is: 5

PL SQL GOTO STATEMENT:

 The goto statement provides an unconditional jump from the GOTO to a labeled
statement in the same subprogram.
 A label can be declare with the << label >> syntax.

Syntax: Example: Output:

DECLARE Value of num: 1
GOTO label_name; Value of num: 2
num number := 1;
Value of num: 3
//Other statements BEGIN Value of num: 4
<<label_name>> Value of num: 6
<<loop1>>
Value of num: 7
Statement; -- while loop execution Value of num: 8
Value of num: 9
WHILE num <= 10 LOOP
Value of num: 10

Gajendra Chourey Page 111

 dbms_output.put_line ('Value of num: ' || num);
num := num + 1;
IF num = 5 THEN
num := num + 1;
GOTO loop1;
END IF;
END LOOP;
END;
/

PL SQL GOTO STATEMENT RESTRICTIONS:

1. GOTO statement cannot transfer control into an IF statement, CASE statement,

LOOP statement or sub-block.
2. GOTO statement cannot transfer control from one IF statement clause to
another or from one CASE statement WHEN clause to another.
3. GOTO statement cannot transfer control from an outer block into a sub-block.
4. GOTO statement cannot transfer control out of a subprogram.
5. GOTO statement cannot transfer control into an exception handler.

PL SQL STORED PROCEDURE:

 The stored procedure is a named PL/SQL block which performs one or more
specific tasks.
 A stored procedure can be divided into two parts: Header and Body part.

Header: The header part contains the name of the procedure and the
parameters passed to the procedure.

Body: The body part contains declaration section, execution section and
exception section.

Note: A stored procedure do not return a value directly.

 How to pass parameter in a procedure?

We can use the below modes to pass the parameters in a procedure:

IN-parameters: These parameters are the read-only parameters. Procedure

cannot change the value of IN parameters.

Gajendra Chourey Page 112

 OUT-parameters: These parameters are the write-only parameters and used to
return values back to the calling program. Procedure can change the value of
OUT parameters.

IN OUT-parameters: These parameters are read and write parameters i.e. a

procedure can reads and change the IN OUT parameter value and return it back
to the calling program.

Syntax of stored procedure:

CREATE [OR REPLACE] PROCEDURE proc_name [list of parameters]

IS | AS
//Declaration block
BEGIN
//Execution block
EXCEPTION
//Exception block
END;

 How to create a procedure?

Example without parameters:

CREATE OR REPLACE PROCEDURE hello_world

AS
BEGIN
dbms_output.put_line('Hello World!');
END;
/
Example with parameters:

CREATE OR REPLACE PROCEDURE add_student(rollNo IN NUMBER, name IN

VARCHAR2)
IS
BEGIN
insert into students values(rollNo,name);
END;
/

 How to execute stored procedure?

A procedure can be executed by using EXEC or EXECUTE statement.

EXEC procedure_name();

EXEC procedure_name;
Gajendra Chourey Page 113
 Note: Execute procedure with parameters:

EXEC procedure_name(param1,param2…paramN);

 A procedure can also be invoked from other PL SQL block.

BEGIN
procedure_name;
END;
/

 How to drop stored procedure?

DROP PROCEDURE procedure_name;

PL SQL FUNCTION:

 The function is a named PL/SQL block which performs one or more specific
tasks and must returns a value.

 How to pass parameter in a function?

 We can use the below modes to pass the parameters in a function:

IN-parameters: These parameters are the read-only parameters. Function

cannot change the value of IN parameters.

OUT-parameters: These parameters are the write-only parameters and used to

return values back to the calling program. Function can change the value of
OUT parameters.

IN OUT-parameters: These parameters are read and write parameters i.e. a

function can reads and change the IN OUT parameter value and return it back
to the calling program.

Syntax:

CREATE [OR REPLACE] FUNCTION function_name [parameters]

RETURN return_datatype;

IS|AS

//Declaration block

BEGIN

Gajendra Chourey Page 114

 //Execution_block

Return return_variable;

EXCEPTION

//Exception block

Return return_variable;

END;

 How to create a function?

create or replace function getMultiple(num1 in number, num2 in number)

return number

is

num3 number(8);

begin

num3 :=num1*num2;

return num3;

end;

 How to execute a function?

A functions return value can be assign to a variable.

result := getMultiple(4, 5);

As a part of a SELECT statement:

SELECT getMultiple(4, 5) FROM dual;

In a PL/SQL Statement:

dbms_output.put_line(getMultiple(4, 5));

 How to drop a function?

DROP FUNCTION function_name;

Gajendra Chourey Page 115

% TYPE and % ROWTYPE

PL/SQL Cursor:

Context area:

 When processing an SQL statement, Oracle creates a temporary work area in the
system memory which contains all the information needed for processing the
statement known as context area.

 A cursor is a pointer to context area i.e. Context area is controlled by the cursor. It is
used to fetch and manipulate the data returned by the SQL statement.

Note:

 Databases such as ORACLE have a memory area, where processing of instructions

and fetched data takes place. A cursor is a pointer which is pointing to this area.
 The data contained in this memory area is also known as Active Set. (The set of rows
the cursor holds is known as active set.)
 Cursors can be broadly classified into Implicit Cursors and Explicit Cursors.

Gajendra Chourey Page 116

 Types of cursors:

1. Implicit cursors.

2. Explicit cursors.

Implicit cursors:

 Implicit cursors are automatically generated by Oracle while processing an SQL

statement when no explicit cursor for the statement is used.
 They are created by default when DML statements like DELETE, INSERT, UPDATE and
SELECT are executed.
 Oracle provides implicit cursor attributes to check the status of DML operations. When
DML statements like INSERT, UPDATE, or DELETE are executed the cursor attributes
tell us whether any rows are affected or not and how many have been affected. When
a SELECT… INTO statement is executed in a PL/SQL Block, implicit cursor attributes
can be used to find out whether any row has been returned by the SELECT statement
or not and returns an error when no data is selected.

Implicit cursor attributes:

%FOUND

 It returns TRUE if an INSERT, UPDATE or DELETE statement affected one or

more rows or a SELECT INTO statement returned one or more rows. Otherwise
returns FALSE.
Example: SQL%FOUND

%NOTFOUND

 It returns TRUE if an INSERT, UPDATE, or DELETE statement affected no rows,

or a SELECT INTO statement returned no rows. Otherwise returns FALSE.
Example: SQL%NOTFOUND

%ISOPEN

 Always returns FALSE for implicit cursors, because Oracle closes the SQL
cursor automatically after executing its associated SQL statement.
Example: SQL%ISOPEN

%ROWCOUNT

 It returns the number of rows affected by an INSERT, UPDATE or DELETE

statement or returned by a SELECT INTO statement.
Example: SQL%ROWCOUNT

 Example:

Gajendra Chourey Page 117

 DECLARE var_rows number(2);
BEGIN
UPDATE employees
SET salary = salary + 2000;
IF SQL%NOTFOUND THEN
dbms_output.put_line('No record updated.');
ELSIF SQL%FOUND THEN
var_rows := SQL%ROWCOUNT;
dbms_output.put_line(var_rows || ' records are updated.');
END IF;
END;
/
Output: 10 records are updated.

Explicit cursors:

 Explicit cursors are the user defined cursors to gain more control over the
context area.
 These are defined in the declaration section of the PL/SQL block.
 An explicit cursor is created on a SELECT Statement which returns more than
one row.

Syntax for creating an explicit cursor:

CURSOR cursor_name IS select_statement;

 How to use explicit cursor?

1. DECLARE the cursor for initialization in the declaration section.

2. OPEN the cursor for memory allocation in the execution section.

3. FETCH the cursor for retrieving data in the execution section.

4. CLOSE the cursor to release allocated memory in the execution section.

 Declaring the cursor:

CURSOR cur_students IS

SELECT rollNo, name, address FROM students;

Gajendra Chourey Page 118

  Fetching the cursor:

FETCH cur_students INTO s_rollNo, s_name, s_address;

 Closing the cursor:

CLOSE cur_students;

Example:

DECLARE
s_rollNo [Link]%type;
s_name [Link]%type;
s_address [Link]%type;
CURSOR cur_students is
SELECT rollNo, name, address FROM students;
BEGIN
OPEN cur_students;
LOOP
FETCH cur_students into s_rollNo, s_name, s_address;
EXIT WHEN cur_students%notfound;
dbms_output.put_line(s_rollNo || ' ' || s_name || ' ' || s_address);
END LOOP;
CLOSE cur_students;
END;
/

Output:
1 Vivek UK
2 Anil Delhi
3 Mahesh Rajasthan
4 Vishal Delhi
5 Binod UP
6 Sunil UP

Implicit vs. Explicit Cursor

Implicit Cursors Explicit Cursors

Implicit cursors are automatically created Explicit cursors needs to be defined explicitly
when select statements are executed. by the user by providing a name.
They are capable of fetching a single row at a
time. Explicit cursors can fetch multiple rows.
They are more vulnerable to errors such as They are less vulnerable to errors(Data
Data errors, etc. errors etc.)
Provides less programmatic control to the User/Programmer has the entire control.

Gajendra Chourey Page 119

users
Comparitive to Implicit cursors, explicit
Implicit cursors are less efficient. cursors are more efficient.
Implicit Cursors are defined as: Explicit cursors are defined as:

BEGIN DECLARE
SELECT attr_name from table_name CURSOR cur_name IS

where CONDITION; SELECT attr_name from table_name

END where CONDITION;

BEGIN
...
implicit cursors requires anonymous buffer Explicit cursors use user-defined memory
memory for storage purpose. space for storage purpose
Cursor attributes use prefix “SQL”. Structure for explicit cursors:
cur_name%attr_name
Structure for implicit cursors:
SQL%attr_name Few explicit cursors are:
cur_name%FOUND,
Few implicit cursors attributes are: cur_name%NOTFOUND,
SQL%FOUND, SQL%NOTFOUND, cur_name%ROWCOUNT
SQL%ROWCOUNT

Gajendra Chourey Page 120

Exception Handling Plsql

Exception:

Exception refers to an exceptional event. Exception is an event that disrupts the

normal flow of the program, during program execution.

Pl sql exception handling:

PL/SQL provides a mechanism to handle such exceptions so that normal flow of the
program can be maintained.

Types of exceptions:

1. System-defined exceptions.

2. User-defined exceptions.

Syntax for exception handling:

DECLARE

//Declaration section

BEGIN

//Exception section

EXCEPTION

WHEN ex_name1 THEN

//Error handling statements

WHEN ex_name2 THEN

-Error handling statements

WHEN Others THEN

//Error handling statements

END;

Example:

DECLARE
Gajendra Chourey Page 121
 s_rollNo [Link]%type := 10;

s_name [Link]%type;

s_address [Link]%type;

BEGIN

SELECT rollNo, name, address FROM students WHERE rollNo = s_rollNo;

dbms_output.put_line(s_rollNo || ' ' || s_name || ' ' || s_address);

EXCEPTION

WHEN no_data_found THEN

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

WHEN others THEN

dbms_output.put_line('Error!');

END;

Output:

No such student!

Pl sql raise exception:

Database server automatically raised the exceptions in case of any internal database
error. But database exceptions can also be raised explicitly by using RAISE command.

Syntax of raising an exception:

DECLARE

exception_name EXCEPTION;

BEGIN

IF condition THEN

RAISE exception_name;

END IF;

EXCEPTION

WHEN exception_name THEN

Gajendra Chourey Page 122

 statement;

END;

Pl sql user defined exception:

The PL/SQL provides the facility to define the custom or user-defined exceptions
according to the need of 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.

Syntax for declaring a user defined exception:

DECLARE custom-exception EXCEPTION;

Example:

DECLARE

s_rollNo [Link]%type := &ss_rollNo;

s_name [Link]%type;

s_address [Link]%type;

-- user defined exception

ex_invalid_rollNo EXCEPTION;

BEGIN

IF c_id <= 0 THEN

RAISE ex_invalid_rollNo;

ELSE

SELECT rollNo, name, address FROM students WHERE rollNo = s_rollNo;

dbms_output.put_line(s_rollNo || ' ' || s_name || ' ' || s_address);

END IF;

EXCEPTION

WHEN ex_invalid_rollNo THEN

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

WHEN no_data_found THEN

Gajendra Chourey Page 123

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

WHEN others THEN

dbms_output.put_line('Error!');

END;

Output:

(Enter a value less than 0 for rollNo)

rollNo must be greater than zero!

Pl sql predefined exceptions list:

Oracle
Exception SQLCODE Description
Error

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 the WHEN clauses of a
CASE_NOT_FOUND 06592 -6592
CASE statement 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 or
COLLECTION_IS_NULL 06531 -6531
varray, or the program attempts
to assign values to the elements
of an uninitialized nested table or
varray.

It is raised when duplicate values

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

It is raised when attempts are

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

Gajendra Chourey Page 124

 It is raised when the conversion
of a character string into a
INVALID_NUMBER 01722 -1722 number fails because the string
does not represent a valid
number.

It is raised when s program

attempts to log on to the
LOGIN_DENIED 01017 -1017
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

NOT_LOGGED_ON 01012 -1012 is 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

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

It is raised when a member

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

It is raised when PL/SQL ran out

STORAGE_ERROR 06500 -6500 of memory or memory was
corrupted.

It is raised when s 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 size-
constraint error occurs.

It is raised when an attempt is

ZERO_DIVIDE 01476 1476
made to divide a number by zero.

Gajendra Chourey Page 125

Triggers Plsql

Oracle pl sql triggers:

A database trigger is a stored program which is automatically fired or executed when

some events occur. A trigger can execute in response to any of the following events:

1. A database manipulation (DML) statement like DELETE, INSERT or UPDATE.

2. A database definition (DDL) statement like CREATE, ALTER or DROP.

3. A database operation like SERVERERROR, LOGON, LOGOFF, STARTUP, or

SHUTDOWN.

Note: A trigger can be defined on the table, view, schema or database with which the
event is associated.

Types of PL SQL triggers:

1. Row level trigger – An event is triggered at row level i.e. for each row updated,
inserted or deleted.

2. Statement level trigger – An event is triggered at table level i.e. for each sql
statement executed.

Syntax for creating a trigger:

CREATE [OR REPLACE] TRIGGER trigger_name

{BEFORE | AFTER | INSTEAD OF }

{INSERT [OR] | UPDATE [OR] | DELETE}

[OF col_name]

ON table_name

[REFERENCING OLD AS o NEW AS n]

[FOR EACH ROW]

WHEN (condition)

BEGIN

--- sql statements

END;

/
Gajendra Chourey Page 126
Where:

CREATE [OR REPLACE ] TRIGGER trigger_name – It creates a trigger with the given
name or overwrites an existing trigger with the same name.

{BEFORE | AFTER | INSTEAD OF } – It specifies the trigger get fired. i.e before or
after updating a table. INSTEAD OF is used to create a trigger on a view.

{INSERT [OR] | UPDATE [OR] | DELETE} – It specifies the triggering event. The
trigger gets fired at all the specified triggering event.

[OF col_name] – It is used with update triggers. It is used when we want to trigger
an event only when a specific column is updated.

[ON table_name] – It specifies the name of the table or view to which the trigger is
associated.

[REFERENCING OLD AS o NEW AS n] – It is used to reference the old and new values
of the data being changed. By default, you reference the values as :old.column_name
or :new.column_name. The old values cannot be referenced when inserting a record
and new values cannot be referenced when deleting a record, because they do not
exist.

[FOR EACH ROW] – It is used to specify whether a trigger must fire when each row
being affected (Row Level Trigger) or just once when the sql statement is executed
(Table level Trigger).

WHEN (condition) – It is valid only for row level triggers. The trigger is fired only
for rows that satisfy the condition specified.

Example:

Existing data:

Select * from employees;

EMP_ID NAME AGE ADDRESS SALARY

1 Shveta 23 Delhi 50000
2 Bharti 22 Karnal 52000
3 Deepika 24 UP 54000
4 Richi 25 US 56000
5 Bharat 21 Paris 58000
6 Sahdev 26 Delhi 60000

Trigger:

Gajendra Chourey Page 127

CREATE OR REPLACE TRIGGER show_salary_difference

BEFORE DELETE OR INSERT OR UPDATE ON employees

FOR EACH ROW

WHEN (NEW.EMP_ID > 0)

DECLARE

sal_diff number;

BEGIN

sal_diff := :[Link] - :[Link];

dbms_output.put_line('Old salary: ' || :[Link]);

dbms_output.put_line('New salary: ' || :[Link]);

dbms_output.put_line('Salary difference: ' || sal_diff);

END;

Note: The above trigger will execute for every INSERT, UPDATE or DELETE
operations performed on the EMPLOYEES table.

Drop a trigger:

DROP TRIGGER trigger_name;

Package Plsql

Oracle pl sql package:

A package is a schema object that groups logically related PL/SQL types, variables
and subprograms.

Parts of a package:

1. Package specification

2. Package body or definition

Gajendra Chourey Page 128

Package specification:

The package specification is the package interface which declares the types,
variables, constants, exceptions, cursors and subprograms that can be referenced
from outside the package.

Note: All objects in the package specification are known as public objects.

Syntax of package specification:

CREATE PACKAGE package_name AS

PROCEDURE procedure_name;

END cust_sal;

Example:

CREATE PACKAGE emp_sal AS

PROCEDURE find_sal(e_id [Link]%type);

END emp_sal;

Package body or definition:

The package body or definition defines the queries for the cursors and the code for
the subprograms.

Note: All objects in the package body or definition are known as private objects.

Syntax of body or definition:

CREATE OR REPLACE PACKAGE BODY package_name AS

PROCEDURE procedure_name IS

//procedure body

END procedure_name;

END package_name;

Example:

Gajendra Chourey Page 129

CREATE OR REPLACE PACKAGE BODY emp_sal AS

PROCEDURE find_sal(e_id [Link]%TYPE) IS

e_sal [Link]%TYPE;

BEGIN

SELECT salary INTO e_sal

FROM employees

WHERE id = e_id;

dbms_output.put_line('Salary: '|| e_sal);

END find_sal;

END emp_sal;

Gajendra Chourey Page 130