EXPLORE SYSTEMS

HISTORY OF DATABASE
Databases have been in use since the earliest days of electronic computing.
Originally DBMSs were found only in large organizations with the computer
hardware needed to support large data sets.
The different types of database management systems are developed in different
years are listed below.
Year DBMS Name
1960s
1970s

Year DBMS name

1960s Navigational DBMS
Relational DBMS
End 1970s SQL DBMS
DBMS FEATURES AND CAPABILITIES
1) Query ability
2) Backup and replication
3) Rule enforcement
4) Security
5) Computation
META – DATA
Meta-data is data describing data. For example a listing that describes what
attributes are allowed to be in data sets is called “meta-information”. The meta-
data is also known as data about data.
EXAMPLES OF DATA BASE MANAGEMENT SYSTEM
====================================================================
Oracle data base IBM DB2 Ingres
Informix Microsoft SQL server Microsoft access
Microsoft visual fox pro My SQL SQL
====================================================================
SQL FORMATING COMMANDS
These are used to format the output of SQL commands these are of 5 types.
1) Column commands
2) Compute commands
3) Title commands
4) Set page commands
5) Storage and printing commands.

1)Column commands:-

Syntax:-
SQL>column <column name> heading <new name>;
SQL>column ename heading employee name;
SQL>select * from employee name;
EMPNO employeena JOB MGR HIREDATE SAL COMM DEPTNO

st
25-1-79, LN Puram – 1 Street, Podalakur Road, Nellore – 524003, Mob:0-9441217879 Page[1]
 EXPLORE SYSTEMS
7369 SMITH CLERK 7982 17-DEC-80 800 20
syntax:-
SQL>column <column name> format mode;
Ex:-SQL>column sal format $9999.99
SQL>select * from emp;
EMPNO employeena JOB MGR HIREDATE SAL COMM DEPTNO
7369 SMITH CLERK 7982 17-DEC-80 $800.00 20
SQL>select * from itnm;
SQL>column itnm format a4
SQL>select * from item;
ITNO ITNM QTY RATE
10 rin 300 10
11 lux 350 15
12 santoor 350 14
13 vim 400 13
14 liril 400 16
15 xxx 350 13
16 pens 100 10
7 rows selected
2)Compute commands:-
Syntax:-
SQL>break on<column name>[skip n] compute <function> of<column name> on
<break column name>;
SQL>break on itno
SQL>compute sum of qty on itno
SQL>select * from orders;
ITNO QTY ORDDATE DELDATE
10 10 08-JUN-87 08-JUN-87
********-----------
Sum 10
11 11 08-JUN-87 08-JUN-87
********-----------
Sum 11
12 10 01-JUN-87 01-JUN-87
********-----------
Sum 10
3 rows selected
Title commands:-
i)T title => Top title
Syntax:-SQL>T title location <title> [skip n]
ii)B title => Bottom title
Syntax:-SQL>B title location <title> [skip n]
SQL>t title center “item details” skip2
SQL>select * from item;
Item details
ITNO ITNM QTY RATE
10 rin 300 10

st
25-1-79, LN Puram – 1 Street, Podalakur Road, Nellore – 524003, Mob:0-9441217879 Page[2]
 EXPLORE SYSTEMS
***********----------
Sum 300
11 lux 350 15
***********----------
Sum 350
2 rows selected
SQL>t title center “student details” skip2
SQL>select * from student;
Student details
SNO SNAME ADDRESS
1 aa kmm
2 bb ktdm
SQL>btitle center “the end”
SQL>select * from student;
Student details
SNO SNAME ADDRESS
1 aa kmm
2 bb ktdm
To remove titles:-
1)t title off
2)b title off
SQL>b title off
SQL>select * from student;
To remove break:-
Clear break
To remove compute:-
Clear compute
To remove new column names:-
Clear columns
btitle off:-
SQL>btitle off;
SQL>select * from student;
Student details
SNO SNAME ADDRESS
1 aa kmm
2 bb ktdm
SQL>ttitle off;
SQL>select * from student;

SNO ENAME ADDRESS

1 aa kmm
2 bb ktdm
SQL> clear break;
Breaks cleared
SQL>select * from item;
ITNO ITNM QTY RATE
10 RIN 300 10

st
25-1-79, LN Puram – 1 Street, Podalakur Road, Nellore – 524003, Mob:0-9441217879 Page[3]
 EXPLORE SYSTEMS
11 LUX 350 15
12 SANTOOR 350 13
13 VIM 400 13
14 LIRIL 400 16
SETTING PAGE DIMENSIONS:-
1) Set new page<number of lines> sets the space in between beginning of the
new page and bottom title.
2) Set line size<number of characters> setting for the line to allow the given
number of characters.
3) Set page size<number of lines> sets the number of lines in a page.
SQL>set new page 3;
SQL>select * from orders;
ITNO QTY ORDDATE DELDATE
10 10 08-JUN-07 08-JUN-07
11 11 08-JUN-07 08-JUN-07
12 10 01-JUL-07 01-JUL-07
13 15 19-AUG-07 19-AUG-07
14 19 25-AUG-07 25-AUG-07
6 ROWS SELECTED.
SQL>set linesize 12
SQL>select * from orders;
SQL>set pagesize 5
SQL>select * from orders;
STORAGE AND PRINTING COMMANDS:-
SPOOL:-
This command is used to store the data in a file and we can give the file name with
.lst extension.
TO STORE IN A FILE:-
SYNTAX:-spool<filename>
TO STOP STORING IN A FILE:-
SQL> spool off
TO RUN THE FILE:-
Spool out
@<filename>
SQL>spool [Link]
SQL>select * from orders;
SQL>select * from emp;
SQL>spool off
SQL>spool out
SQL>@[Link]
PL/SQL

 PL/SQL stands for procedural language extensions to SQL.

 PL/SQL extends SQL by adding programming structures and subroutines
available in any high level language.
 PL/SQL can be used for both server-side and client-side development.

st
25-1-79, LN Puram – 1 Street, Podalakur Road, Nellore – 524003, Mob:0-9441217879 Page[4]
 EXPLORE SYSTEMS
 PL/SQL has syntax and rules that determine how programming statements
work together.
 PL/SQL is not a stand alone programming language.
 PL/SQL is a part of the ORACLE RDBMS, and hence can reside in two
environments, the client and the server.
 Any module that is developed using PL/SQL can be moved easily between
sever-side and client-side applications.
 Either in client/server environments any PL/SQL engine processes
subroutine.
 PL/SQL engine is a special component that processes and executes any
PL/SQL statements and sends any SQL statement to the SQL statement processor.
 The SQL statement processes are always located on the oracle server.
 As per the necessity the PL/SQL engine can be located either at
o SERVER
o CLIENT
 When PL/SQL engine is located upon the server, the whole PL/SQL block is
passed to the PL/SQL engine on the oracle server.
 When the PL/SQL engine is located upon the client, the PL/SQL processing
is done on the client side. All SQL statements that are embedded within the
PL/SQL block, are sent to the oracle server for further processing.
o If the PL/SQL block does not contain any SQL statements, the entire block is
executed on the client side.

ADVANTAGES OF PL/SQL:-
 Completely portable.
 High performance transaction processing language.
 Support for SQL
 Support for object oriented programming.
 Better performance.
 Higher productivity.
 Tight integration with oracle.
 Tight security.
MAIN FEATURES OF PL/SQL:-
 It combines the data manipulating power of SQL with the processing power
of procedural language.
 Program flow can be controlled using the statements like IF and LOOP.
 We can declare variables, define procedures and functions and trap run time
errors.
 Can be used to break complex problems down into easily understandable
procedural code.
 The code can be reused for multiple applications.
 SQL commands can be directly embedded inside the PL/SQL without
learning new API’S.
 The PL/SQL data types correspond with SQL’S column types making
learning process easier.
BLOCK STRUCTURE APPROACH:-
 The basic units that make up a PL/SQL program are logical blocks.

st
25-1-79, LN Puram – 1 Street, Podalakur Road, Nellore – 524003, Mob:0-9441217879 Page[5]
 EXPLORE SYSTEMS
 The blocks in PL/SQL can be nested with one another.
 A block groups related declarations and statements.
 The declarations are local to the block and cease to exist when the block
completes.
 Block structure approach avoids cluttered name spaces for variables and
procedures.
 The basic parts of a block are
o Declarative part (optional)
o Executable part (mandatory)
o Exception handling part (optional)
 DECLARATIVE PART: - it is used to define type’s variables and similar items.
 EXECUTABLE PART:- it contains the optional code for the program. The
items in declarative part are manipulated here.
 EXCEPTION HANDLING PART:- any exceptions that are raised during the
program execution are handled here.
 Blocks can be nested in the executable and exception handling parts of a
PL/SQL block, a sub program.
 Local sub programs can be defined declarative part of any block.
 Local sub programs can be called from the block in which they are declared.
THE PSEUDO STRUCTURE OF A BLOCK:-
SQL>
DECLARE
Variable, cursors, user defined exceptions
BEGIN
SQL statements
PL/SQL statements
EXCEPTION
Actions to perform when errors occur
END;
 Only begin and end keywords are mandatory
EXECUTING STATEMENTS AND PL/SQL BLOCKS FROM SQL * PLUS:-
 Place a semicolon (;) at the end of the SQL statement or PL/SQL control
statement.
 Use a slash (/) to run the anonymous PL/SQL block in SQL*PLUS buffer.
 Place a period (.) to close a SQL*PLUS buffer.
 A PL/SQL block is treated as one continuous statement in the buffer.
 Semicolons within the block do not close or run the buffer.
 In PL/SQL an error is called an exception.
 Sections keywords declare, begin and exception should not contain a
semicolon.
 End and all other PL/SQL statements should be placed with a semicolon.

TYPES OF BLOCKS IN PL/SQL:-

 In PL/SQL there are various types of blocks.
o ANONYMOUS BLOCKS:-
 They are unnamed blocks.

st
25-1-79, LN Puram – 1 Street, Podalakur Road, Nellore – 524003, Mob:0-9441217879 Page[6]
 EXPLORE SYSTEMS
 They are declared at the point in an application, where they are to be
executed and are passed to the PL/SQL engine for execution at run time.
 An anonymous block can be embedded within a pre-compiler program and
within PL*SQL or sever manager.
o NAMED BLOCKS:-
 They have all the features as specified for the anonymous blocks, but the
only difference is that each block can be named if necessary.
o SUB PROGRAMMED BLOCKS:-
 These are named PL/SQL blocks that can take parameters and can be
invoked.
 These blocks are either declared as procedures or functions.
 A procedural block is used for performing an action, and a functional block
is used for performing computations.
PL/SQL supports four data type categories.
o SCALAR DATA TYPES:-
 They hold a single value.
 Main data types are those that correspond to column types in oracle server
tables.
 Supports Boolean variables.
o COMPOSITE DATATYPES:-
 Records allows group of fields to be defined and manipulated in PL/SQL
blocks.
o REFERANCE DATATYPES:-
 They hold values, called pointers, which designate other program items.

o LOB DATATYPES:-
 They hold values called locations, specifying the location of large objects that
are stored out of line.
DECLARING PL/SQL VARIABLES:-
Syntax:-
Identifier [constant] data type [not null] [:=|default expr];
 Identifiers -> specifies the name of the variables.
 Constant -> constants the variable such that its value cannot change.
Constants must be initialized.
 Data type -> it is a scalar, composite, reference or LOB data type.
 Not null -> constraints a variable such that it must contain a value should
be initialized.
 Expr -> it is any PL/SQL expression that can be a literal, another variable or
an expression involving operations and functions.
DETAILED ASPECTS OF PL/SQL LANGUAGE:-
CHARACTER SET:-
 PL/SQL programs are written as lines of text using a specific set of
characters.
 The characters that can be used are
 A-Z or a–z
 0–9
 () + - * / < > = ! ~ ^ : . ‘ @ % , “ # & { } ? [ ] | $

st
25-1-79, LN Puram – 1 Street, Podalakur Road, Nellore – 524003, Mob:0-9441217879 Page[7]
 EXPLORE SYSTEMS
 Tabs, spaces and carriage returns.
 PL/SQL keywords are not case sensitive, hence lower case letters are
equivalent to corresponding upper case letters.
LEXICAL UNITS:-
 A group of characters that are contained within a line of PL/SQL statement
are called as lexical units.
 The types of lexical units are
 Delimiters
 Identifiers
 Literals
 Comments
DELIMITERS:-
 It is a simple or compound syntax that has special meaning to PL/SQL.
 The delimiters are used to represent arithmetic operations etc.
IDENTIFIERS:-
 They are used to name PL/SQL program items and units.
 Identifiers can include
 Constants
 Variables
 Exception
 Cursors variables
 Subprograms
 Package
 An identifiers consists of a letter followed by a set of letters, numerals, dollar
signs etc.,
*& *- */ *”
 VALID IDENTIFIERS INVALID IDENTIFIERS

X THIS AND THAT

P2 YOU – ME
V-PHONE # SHOW/OFF
V-PHONE NUMBER EMPNAME
 Identifiers are not case sensitive, until they are not declared within single
quotes.
 An identifiers length cannot exceed 30 characters.
RESERVED WORDS:-
 Reserved words have special syntactic meaning to PL/SQL.
 Reserved words can be used in combination with other words for
declaration.
 It is better to represent reserved words in upper casing.

PREDEFINED IDENTIFIERS:-
 The identifiers globally declared in package STANDARD can be re-declared.
 Declaring predefined identifiers again is error prone as the local declaration
overrides the global declaration.
QUOTED IDENTIFIERS:-

st
25-1-79, LN Puram – 1 Street, Podalakur Road, Nellore – 524003, Mob:0-9441217879 Page[8]
 EXPLORE SYSTEMS
 Identifiers declared in double quotes are called quoted identifiers.
 The maximum size of a quoted identifier cannot exceed 30 characters.
 Using PL/SQL reserved words as quoted identifiers is not advisable.
Example: - “employee(s)”
“Status on/off”

LITERALS:-
 A literal is an explicit numeric, character, string or Boolean value not
represented by an
Identifiers.
NUMERIC LITERALS:-
 They are of two types: integers & reals.
 An integer literal is an optionally signed whole number without a decimal
point.
Example: - 030, 8, -180, +56798.
 A real literal is optionally signed whole or fractional number with a decimal
point.
Example: - 6.6667, 0.0, -12.0, 3.14, 5, 25.
CHARACTER LITERALS:-
 It is an individual character enclosed by single quotes.
 It includes all printable characters in the PL/SQL character set.
Example: - ‘A’, ’c’, ‘a’, ‘7’.
 Character literals are case sensitive.
 Character literals are in the form of number is not equivalent to integer.
 Even though pure character literals declared only with numbers are simply
converted, it is better to use explicit conversion.
STRING LITERALS:-
 A string literal is a sequence of zero or more characters enclosed by single
quotes.
 All string literals except the NULL string
“Have data type CHAR.
 To represent an apostrophe within a string declare two single quotes.
Example: - ‘I am in, oracle!’
’02-10-83’
‘It is “so sunny outside.”’
‘Your”s association president.”’
 To implement single quotation marks with easiness, select any character
that is not part of the string and declare the style.
Ex: - Q ‘! ------------!’
Example: - Q ‘! We are a group, in your’s group!’
BOOLEAN LITERALS:-
 They are predefined values TRUE, FALSE and NULL.
 BOOLEAN LITERALS are values, but not strings.
DATE TIME LITERALS:-
 A date time literal can have various formats depending on the data type.

st
25-1-79, LN Puram – 1 Street, Podalakur Road, Nellore – 524003, Mob:0-9441217879 Page[9]
 EXPLORE SYSTEMS
Example: - DATE ‘1998-12-25’;
TIMES TAMP ‘1997-10-22 01:05:21’;
COMMENTS IN PL/SQL:-
 PL/SQL compiler ignores comments.
 Comments promote readability and ids understanding.
 PL/SQL supports two styles.
 Single line comments ( - -)
 Multiline comments (/* -----*/)
 Comments should appear within a statement at the end of a line.
 Comments cannot be nested.
 It is must preferable to use multiline comments rather than
single line comments.
DETAILED SPECIFICATION OF PL/SQL DATA TYPES:-
 They let the programmer to store numeric data, represent
quantities and apply calculations.
NUMBER:-
 This data type is used to store fixed=point or floating=point
numbers.
 The magnitude range is 1*10-130-----------10*10125, above this range
numeric overflow or under flow error is generated.
 Precision can be specified, for total number of digits and scale.
Syntax: - NUMBER (P, S);
NUMBER SUBTYPES:-
 These subtypes are used for more descriptive name.
 The NUMBER subtypes are used for compatibility with ANSI/ISO
and IBM types.
 The sub types available are
 Dec
 Decimal
 Double precision
 Float
 Integer
 Int
 Number
 Real
 Smallint
 The subtypes dec, decimal and number are used to declare fixed point
numbers with a maximum precision of 38 decimal digits.
 The sub types double precision and float are used to declare floating point
numbers with a maximum precision of 126 binary digits.
 The real sub type is used to declare floating point numbers with a maximum
precision of 63 binary digits, roughly equivalent to 18 decimal digits.
 The sub types integer, int and smallint are used to declare integers with a
maximum precision of 38 decimal digits.
PL/SQL CHARACTER AND STRING TYPES:-
 Character types let you store alphanumeric data, represent words and test,
and also manipulate character strings.

st
25-1-79, LN Puram – 1 Street, Podalakur Road, Nellore – 524003, Mob:0-9441217879 Page[10]
 EXPLORE SYSTEMS

CHAR:-
 It is used to store fixed length character data.
 The maximum size is 32767 bytes.
Syntax:-char (max_size [char/byte])
 If the maximum size is not specified then it defaults to 1.
CHAR SUBTYPE (CHARACTER):-
 It has the same range of values as its base type.
 Character is just another name for char, having compatibility with ANSI/ISO
and IBM types.
LONG AND LONG RAW:-
 It is used to store variable length character strings.
 The maximum size of long is 32760 bytes.
 Long raw is used to store binary data or byte strings; it is not interpreted by
PL/SQL.
RAW:-
 It is used to store binary data or byte strings.
 It can store a sequence of graphics characters or a digitized picture.
 It cannot be interpreted by PL/SQL.
 The maximum size is 32767 bytes.
Syntax:-raw (max_size);
VARCHAR2:-
 It is used to store variable lengths character data.
 The maximum size is up to 32767 bytes.
Syntax:-varchar2 (max size [char/byte]);
VARCHAR2 SUBTYPES:-
 The sub types available are
 String
 Varchar
 Used for compatibility with ANSI/ISO and IBM types.
BOOLEAN TYPES:-
 It is used to represent Boolean values true and false.
 It is used to store high logical values true, false and null.
 It takes no parameters, inserts & selects not accepted.
DATE:-
 It is used to store fixed length date times.
 The date portion defaults to the first day of the current month, and time
portion defaults to mid night.
 Valid range January 1 4712 BC to December 31 9999A.D.
DECLARATIONS IN PL/SQL:-
 The program stores values in variables.
 As the program executes, the values of variables can change but the values
of constants cannot change.
 Declarations allocate storage space for a value, hence should be specified by
a data type, and name for further reference.
 Any variable can be declared only in the declarative part of any PL/SQL
block, sub program or package.

st
25-1-79, LN Puram – 1 Street, Podalakur Road, Nellore – 524003, Mob:0-9441217879 Page[11]
 EXPLORE SYSTEMS
Ex: - declare
Admn date date;
Patient name varchar2 (30);
Bpcount smallint=0;
ASSIGNING VALUES TO A VARIABLE:-
 In PL/SQL a variable can be assigned in three ways.
1. By using assignment operator(:=)
2. By selecting or fetching database values.
3. By passing it as an out or inout parameter to a sub program.

PRESENTING DATA ON TO THE SCREEN:-

 For producing outputs on the video device we need the assistance of DBMS-
OUTPUT package.
 The package enables to display output from PL/SQL blocks and sub
programs.
 The procedure PUT_LINE outputs information to a buffer in the SGA.
 The information can be displayed by calling the procedure GET_LINE or by
setting server output on in SQL*PLUS.
SYNTAX: -DBMS_OUTPUT.PUT_LINE (MANAGE);
 Has limitation for maximum of 255 characters per line.
 The DBMS_OUTPUT package is owned by the oracle user sys.
 It writes information to the BUFFER for storage.
 The size of the BUFFER can be set between 2000 to 1,000,000 bytes.
 The specifications to set buffers are
o SET SERVEROUTPUT ON;
o SET SERVEROUTPUT ON SIZE 5000;
o SET SERVEROUTPUT OFF;
APPLYING SUBSTITUTION VARIABLES:-
 PL/SQL is not a stand alone programming language, it only exists as a tool
within the oracle programming environment.
 Because of the alone mentioned standard it does not really have capabilities
to accept input from user.
 For accepting input the substitutions variables can be used, which can be
either ‘&’ or ‘&&’
 Substitutions variables cannot be used to output the values.
 The substitutions of the value is actually done by the SQL * PLUS
environment, before the PL/SQL block is sent to the database.

1) Write a PL/SQL program to find addition of two numbers.

declare
a number (10);
b number (10);
c number (10);
begin
a:=&a;

st
25-1-79, LN Puram – 1 Street, Podalakur Road, Nellore – 524003, Mob:0-9441217879 Page[12]
 EXPLORE SYSTEMS
b:=&b;
c:=a+b;
dbms_output.put_line (‘the addition result is :’ ||c);
end;
/
TO EXECUTE A PROGRAM (RUNNING):-
@ Program name
Ex: - @ p1
To open a program
ed program name
Ex: - ed p1
FOR OUTPUT
Options->environment->serveroutput->custom
->on->ok
Or
AT SQL PROMPT
SQL> set severoutput on;
2) Write a PL/SQL program to print our name and age by entering date of
birth.
declare
name varchar2 (20):=’&name’;
dob date:=’&date’;
begin
dbms_output.put_line (‘u r name is:’||name);
dbms_ output.put_line (‘u r dob is:’||dob);
dbms_output.put_line (‘u r age is:’||(sysdate-dob)/365);
end;
/
OPERATORS IN PL/SQL:-
 LOGICAL : AND, OR, NOT
 ARITHMETIC : +, -,*, /
 CONCATENATION : ||
 PRENTHESIS TO CONTROL ORDER OF OPERATIONS
 EXPONENTIATION : **
 COMPARISION : =,! =,<, >, <=,>=
 SQL* PLUS : IS NULL, LIKE,
BETWEEN, IN
PRECEDENCE:-
1. **, NOT
2. +, - (IDENTITY,NEGATION)
3. *,/
4. +, -
5. =, !=, <, >, <=, >=, IS NULL, LIKE, BETWEEN, IN
6. AND
7. OR
OPERATORS IN PL/SQL, THINGS TO NOTE:-
 Comparison involving null’s always yield null.

st
25-1-79, LN Puram – 1 Street, Podalakur Road, Nellore – 524003, Mob:0-9441217879 Page[13]
 EXPLORE SYSTEMS
 Applying logical operator not to a null yields null.
 In conditional control statements, if the condition yields null, its associated
sequence of statements are not executed.
SCOPE AND VISIBILITY OF PL/SQL IDENTIFIERS:-
 References to identifiers are resolved according to the scope and visibility.
 SCOPE:- scope of an identifier is that region of a program unit, from which
we can reference the identifier.
 VISIBLE:- it is the region from which we can reference the identifier using an
unqualified name.
 Identifiers declared in a PL/SQL block are considered LOCAL to that block
and GLOBAL to all its sub-blocks.
 If a GLOBAL IDENTIFIER is re-declared in a sub-block, both identifiers
remain in scope.
 Within the sub-block, only the local identifier is visible.
 A qualified name should be used to reference the GLOBAL IDENTIFIER.
 The same identifier can be declared in two different blocks, the change in
one does not affect the other.
NAME RESOLUTION STANDARDS:-
 The names of database columns take precedence over the names of local
variables and formal parameters.
 To avoid the ambiguity of name resolution, add a prefix to the names of the
local variables and formal parameters, or a BLOCK LABEL can be used to quality
references.

SCOPE AND VISIBILITY DIAGRAM:-

SQL> DECLARE
X BINARY_INTEGER;
BEGIN
---------
---------
DECLARE
Y NUMBER;
BEGIN
----------
-----------
END OF ‘Y’
END;
END;
END;
NESTED BLOCKS AND VARIABLE SCOPE:-
 The variable ‘y’ can reference the variable named ‘x’.
 The variable ‘x’ cannot reference variable ‘y’.

st
25-1-79, LN Puram – 1 Street, Podalakur Road, Nellore – 524003, Mob:0-9441217879 Page[14]
 EXPLORE SYSTEMS
 If the variable named y in the nested block is given the same name as the
variable name x in the outer block its value is valid only for the duration of the
nested block.

3) WRITE A PL/SQL PROGRAM.

declare
a varchar2(20):=’hello guys’;
b real:=0;
begin
dbms_output.put_line(‘the value of outer string a:’||a);
dbms_output.put_line(‘the value of outer real b:’||b);
declare
a integer:=200;
b real:=400;
begin
dbms_output.put_line(‘the value of inner integer a is : ‘||a);
dbms_output.put_line(‘the value of inner real b is:’||b);
end;
declare
b real:=500;
a varchar2(30):=’hello girls’;
begin
dbms_output.put_line(‘the value of last b is:’||b);
dbms_output.put_line(‘the value of last a is:’||a);
end;
dbms_output.put_line(‘the value of outer a is:’||a);
dbms_output.put_line(‘the value of outer real b is:’||b);
end;
/

PROGRAMMING GUIDELINES:-
Code maintenance can be made easier by
 Documenting code convention for the code.
 Developing naming conventions for identifiers and other objects.
 Enhancing readability by indenting
 SQL statements, PL/SQL keywords and data types are kept in uppercasing.

st
25-1-79, LN Puram – 1 Street, Podalakur Road, Nellore – 524003, Mob:0-9441217879 Page[15]
 EXPLORE SYSTEMS
 Identifiers and parameters, database tables and columns are kept in lower
casing and initcap casing.
 The names of local variables are formal parameters take precedence over the
names of database tables.
 The names of columns take precedence over the names of local variables.
CONTROL STRUCTURES IN PL/SQL:-
 To write programs that reflects the real time requirement, we need:
1. Branching
2. Selection
3. Looping
BRANCHING STATEMENTS:-
 In PL/SQL branching is implemented using the IF statements.
 PL/SQL provides three types of conditional control
o Simple if
o If-than-Elsif
o Else if
 The branching standards can be implemented either as
i. Simple if
ii. Nested if
iii. Else if ladders

GENERAL SYNTAX:-
If condition1 then
Statement1;
Statement2;
----------------
----------------
End if;
 If is a reserved word and marks the beginning of the IF statement.
 The ENDIF is a reserved phrase that indicates the end of the IF….THEN
construct.
 When IF….THEN is executed, a condition is evaluated to either TRUE or
FALSE.

4) WRITE A PL/SQL PROGRAM TO FIND THE GIVEN NUMBER IS EVEN OR

NOT
declare
n number(10);
begin
n:=&n;
if mod(n,2)=0 then
dbms_output.put_line(n||’is even’);
end if;
end;
/

st
25-1-79, LN Puram – 1 Street, Podalakur Road, Nellore – 524003, Mob:0-9441217879 Page[16]
 EXPLORE SYSTEMS
5) WRITE A PL/SQL PROGRAM TO FIND THE GIVEN NUMBER IS POSITIVE
OR NOT
IF-THEN-ELSE STATEMENT:-
 IF-THEN-ELSE statement enables to specify two groups of statements.
 One group is evaluated when the condition evaluates to TRUE, the next
group is evaluated when the condition evaluates to FALSE.

Syntax: - IF condition THEN

Statement1;
ELSE
Statement2;
END IF;
 This concept should be used when trying to choose between two mutually
exclusive actions.
ELSIF STATEMENTS:-
Syntax:-
IF condition1 THEN
Statement1;
ELSIF condition2 THEN
Statement2;
ELSIF condition3 THEN
Statement3;
-----------------------------
------------------------------
ELSE
Statement n;
END IF;
 In this construct for every false state of an IF condition another IF follows.
 This construct is also specified as ELSIF ladders.
 The applicational syntax can also be produced with ELSE if condition
structure.
 For clarity purpose rather than ELSIF, ELSE IF construct is suggested.
6) WRITE A PL/SQL BLOCK TO FIND THE CURRENT BILL WHEN NUMBER OF
UNITS GREATER THAN HUNDRED (>100) PER UNIT COST IS 2.5 OTHER WISE
PER UNIT COST IS 1.75.
declare
n number(10);
c number(10);
p number(10);
begin
c:=&c;
p:=&p;
n:=c-p;
dbms_output.put_line(‘no of units:’||n);
if n>100 then

st
25-1-79, LN Puram – 1 Street, Podalakur Road, Nellore – 524003, Mob:0-9441217879 Page[17]
 EXPLORE SYSTEMS
dbms_output.put_line(‘the current bill:’||n*2.5);
else
dbms_output.put_line(‘the current bill:’||n*1.75);
end if;
end;
/
NESTED IF STATEMENTS:-
 The IF statements can be nested into one another as per requirements.
 NESTED IF is a situation in which an IF follows another IF immediately for
every true state of an IF.
 Each IF is considered as an individual block of IF and needs proper nesting.

Syntax:-
IF condition THEN
IF condition THEN
Statement1;
ELSE
IF condition THEN
Statement2;
ELSE
Statement2;
END IF;
ELSE
Statement3;
END IF;
WRITE A PL/SQL BLOCK FIND BIGGEST OF THREE NUMBERS.
declare
a number(10);
b number(10);
c number(10);
begin
a:=&a;
b:=&b;
c:=&c;
if a>c then
if a>b then
dbms_output.put_line(‘a=’||a||’is big’);
else
dbms_output.put_line(‘b=’||b||’is big’);
end if;
else if c>b then
dbms_output.put_line(‘c=’||c||’is big’);
end if;
end;

st
25-1-79, LN Puram – 1 Street, Podalakur Road, Nellore – 524003, Mob:0-9441217879 Page[18]
 EXPLORE SYSTEMS
/
ELSE IF LADDER:-
 This is situation where a condition is followed for every FALSE state of an IF
condition.
 Each FALSE state is immediately associated with a condition of its own.
Syntax:-
IF condition THEN
Statement1;
ELSIF condition THEN
statement2;
ELSIF condition THEN
Statement3;
ELSE
Statement4;
END IF;

WRITE A PL/SQL BLOCK TO FIND THE GIVEN NUMBER IS ZERO OR EVEN OR

ODD
declare
n number(10);
begin
n:=&n;
if n=0 then
dbms_output.put_line(‘the given number is zero’);
elsif mod(n,2)=0 then
dbms_output.put_line(‘the given number is even’);
else
dbms_output.put_line(‘the given number is odd’);
end if;
end;
/

ITERATIONS IN PL/SQL:-
 LOOPs facilitate to repeat a statement or sequence of statements multiple
times.
 The different types of PL/SQL Loops are
o Basic or simple loop
o For loops
o WHILE Loops.
BASIC OR SIMPLE LOOP:-
 It is the simplest form of the LOOP construct in PL/SQL
 It encloses a sequence of statements between the keywords LOOP and END
LOOP.
 It allows execution of its statements at least once.
 The keep the LOOP in finite state the EXIT statement is used.

st
25-1-79, LN Puram – 1 Street, Podalakur Road, Nellore – 524003, Mob:0-9441217879 Page[19]
 EXPLORE SYSTEMS

Syntax:-
LOOP
Statement1;
---------------
EXIT [WHEN condition];
END LOOP;
EXIT STATEMENT:-
 EXIT statement is used to terminate a Loop.
 Once the Loop is terminated, the control passes to the next statement after
the END LOOP.
 EXIT can be issued either as an action within an IF statement or as a stand
alone statement with LOOP.
 The EXIT statement should always be placed inside a LOOP.
 EXIT can be associated with a WHEN clause to allow conditional termination
of the LOOP.
 A basic loop can contain multiple EXIT statements.
 The EXIT condition can be at the top of the LOOP or at the end of the LOOP
as per Logical convenience.
WRITE A PL/SQL BLOCK TO PRINT 10 20 30 40 50 60 70 80 90 100
declare
I number:=0;
begin
loop
i:=i+10;
dbms_output.put_line(i);
exit when i=100;
end loop;
end;
/
WRITE A PL/SQL PROGRAM TO PRINT EVEN NUMBERS
declare
I number:=0;
begin
loop
i:=i+2;
dbms_output.put_line(i);
exit when i=50;
end loop;
end;
/
WHILE LOOP:-
 It can be used to repeat a sequence of statements until the controlling
condition is no longer TRUE.
 The condition is evaluated at the start of each iteration.
 The Loop terminates when the condition is FALSE.
Syntax:- WHILE condition LOOP

st
25-1-79, LN Puram – 1 Street, Podalakur Road, Nellore – 524003, Mob:0-9441217879 Page[20]
 EXPLORE SYSTEMS
Statement1;
Statement2;
---------------
----------------
END LOOP;
 If the condition yields NULL, the Loop is by passed and control passes to the
next statement.

WRITE A PL/SQL PROGRAM TO FIND THE GIVEN NUMBER IS PRIME OR NOT

declare
n number(10);
I number:=1;
C number:=0;
begin
n:=&n;
while i<=n
loop
if mod(n,i)=0 then
c:=c+1;
enf if;
i:=i+1;
end loop;
if c=2 then
dbms_output.put_line(n||’is prime’);
end if;
end;
/
WRITE A PL/SQL PROGRAM TO FIND SUM OF DIGITS OF A GIVEN NUMBER
declare
n number(10);
s number:=0;
r number(10);
begin
n:=&n;
while n>0
loop
r:=mod(n,10);
s:=s+r;
n:=floor(n/10);
end loop;
dbms_output.put_line(‘sum of digits of a given number is:’||s);
end;
/
FOR LOOP:-
 It has the same general structure as the basic loop.
 FOR loop contains a control statement at the front of the LOOP keyword, to
determine the number of iterations that PL/SQL performs.

st
25-1-79, LN Puram – 1 Street, Podalakur Road, Nellore – 524003, Mob:0-9441217879 Page[21]
 EXPLORE SYSTEMS
Syntax:- FOR counter IN[RESERVE]
Lower Bound---upper bound loop
Statement1;
Statement2;
----------------
----------------
END LOOP;
 COUNTER:-
o It is an implicitly declared integer whose value automatically increased or
decreased by 1 on each iteration of the loop until the upper bound or lower bound
is reached.
 REVERSE:-
o It is a keyword, and causes the counter to decrement with each iteration
from the upper bound to the lower bound.
 The counter need not be declared, as it is implicitly declared as an integer.
 The lower bound and the upper bound of the loop can be Literals, variables,
and expressions, but they should be evaluated to integers.
 The lower and upper bounds of a Loop statement need not be numeric
literals, they can be expressions that covert to numeric values.
FACTORIAL OF A GIVEN NUMBER
declare
n number:=&n;
f number:=1;
begin
for i in 1..n
loop
f:=f*1;
end loop;
dbms_output.put_line(‘the factorial is:’||f);
end;
/

USING SQL WITH PL/SQL:-

DATA RETRIEVAL STANDARDS:-
 Data retrieval options range from basic SELECT statements to pattern
matching with regular expressions.
SQL>SELECT statement
Syntax:-
SELECT select List
[INTO variable list]
FROM table list
[WHERE where clause]
[ORDER by column list]
 The select list can be columns, strings, built in functions or * to retrieve
data.
 Arithmetic operations are allowed in the select list.

st
25-1-79, LN Puram – 1 Street, Podalakur Road, Nellore – 524003, Mob:0-9441217879 Page[22]
 EXPLORE SYSTEMS
 Variables can be declared as a single data type, or anchored data type or
entire record types.
 The table list can be one or more tables, views or inline views.
 The WHERE clause restricts the result set.
 When selecting a value into a variable, be sure to return one and only one
variable.
 Only one record can be returned into a SELECT statement in PL/SQL.
 The common errors encountered are:
o ORA-01403-no data found.
o ORA-01422-exact fetch returns more than requested number of rows.
declare
empno1 number:=&empno1;
ename1 varchar(10);
job1 varchar(10);
sal1 number(10);
begin
select ename,job,sal into ename1,job1,sal1 from emp where empno=empno1;
dbms_output.put_line(‘name:’||ename1||’job:’||job1||’sal:’||sal1);
end;
/
%TYPE VARIABLE DECLARATION:-
 The % type variable is used to anchor PL/SQL variable to the database type
columns.
 This methodology is more suitable where the variable that is declared in the
program is mapping directly to a column in the database table.
Syntax:-
Variable name table name. Column name%type;
 This method keeps the PL/SQL program to be unaffected even when the data
types within the database are changing.
 The methodology provides structural independency for the PL/SQL program.

Write a PL/SQL block to update order table to find out amount for the ordered
quantity.
r [Link]%type;
I [Link]%type;
begin
i:=&i;
select rate into r from item where itno=I;
update orders set amt=qty*r where itno=I;
end;
/
Output:-SQL>@ program name
SQL>select * from item;
SQL>select * from orders;
SQL>alter table orders add(amt number(10));
Table altered.
SQL>select * from orders;

st
25-1-79, LN Puram – 1 Street, Podalakur Road, Nellore – 524003, Mob:0-9441217879 Page[23]
 EXPLORE SYSTEMS
Write a PL/SQL block to modify the quantity in orders table.
declare
q [Link]%type;
q1 [Link]%type;
i [Link]%type;
begin
q1:=&q1;
i:=&i;
select qty into q from item where itno=i;
ifq>=q1 then
insert into orders(itno,qty,orddate) values(I,q1,sysdate());
update item set qty=qty-q1 where itno=i;
else
dbms_output.put_line(‘qty not available’)’
end if;
end;
/
SQL>select *from item;
SQL>@ program name
Enter value forq1:100
Old 6:q1:=&q1;
New 6:q1:=100;
Enter value for i:13
Old 7 : i:=&i;
New 7 : i:=13;
%ROW TYPE ATTRIBUTE:-
 To declare a record based on a collection of columns in a database table or
view, we can use the %ROW TYPE attribute.
 The fields in the record take their names and data types from the columns of
the table or view.
 The record can also store an entire row of data fetched from cursor or cursor
variable.
 Prefix %ROWTYPE with the database table.
Syntax:-
Variable name table name %ROWTYPE;
ADVANTAGES:-
 The number and data types of the underlying database columns may not be
known.
 The number and data types of the underlying database columns may change
at run time.
 It is most useful when retrieving a row with the SELECT statement.
CURSOR TYPES:-
 CURSORS are broadly recognized as two types.
o Implicit cursors
o Explicit cursors
 IMPLICIT CURSORS:-

st
25-1-79, LN Puram – 1 Street, Podalakur Road, Nellore – 524003, Mob:0-9441217879 Page[24]
 EXPLORE SYSTEMS
 It is a cursor that is automatically declared by oracle every time an SQL
statement is executed.
 The programmer cannot control or process the information is an implicit
cursors.
 EXPLICIT CURSORS:-
 It is a cursor that is defined by the programmer within the program for any
query that returns more than one row of data.
 This CURSOR is declared within the PL/SQL block, allows sequential
process of each row of the returned data.

IMPLICIT CURSORS:-
 The process flow of an implicit cursor is ….
[Link] given PL/SQL block issued an implicit cursor whenever an SQL
statement is executed, as long as explicit cursors does not exist.
2. A cursor is automatically associated with ever DML statement.
3. All UPDATE and DELETE statements have CURSORS that identify the set
of rows that will be affected by the operations.
4. An INSERT statement needs a place to receive the data that is to be
inserted in the database, which is handled by the implicit cursor.
5. the most recently opened cursor is called ‘SQL%’ cursor.
6. During processing of an implicit cursor, oracle automatically performs….
*OPEN *FETCH *CLOSE
CURSOR ATTRIBUTES:-
*%ISOPEN *%NOTFOUND *%FOUND
*%ROWCOUNT
 The attribute return information about the execution of a data manipulation
statement.
o %ISOPEN->BOOLEAN->evaluates to TRUE if the CURSOR is open.
o %NOTFOUND->BOOLEAN->evaluates to TRUE if the most recent fetch does
not return a row.
o %FOUND->BOOLEAN->evaluates to TRUE if the most recent fetch returns a
row.
o %ROWCOUNT->NUMBER->evaluates the total number of row returned so
far.
EXPLICIT CURSORS:-
 An EXPLICIT CURSOR is generated using a name with association of a
SELECT statement in the DECLARE section.
ADVANTAGES:-
 Explicit cursors provide more programmatic control for programmers.
 Explicit cursors are more efficient in implementation, hence easy to trap
errors.
 Explicit cursors are designed to work with SELECT statements that return
more than one record at a time.
 Explicit cursors require additional steps to operate than implicit cursors.
 There are four steps that should be performed by explicit cursors.
o CURSOR declaration

st
25-1-79, LN Puram – 1 Street, Podalakur Road, Nellore – 524003, Mob:0-9441217879 Page[25]
 EXPLORE SYSTEMS
o CURSOR opening
o CURSOR fetching
o CURSOR closing

THE PSEUDO STRUCTURE:-

SQL> DECLARE
1. CURSOR declaration
BEGIN
2. OPENING OF CURSOR….
……………………………………
……………………………………
3. FETCHING THE ROWS FROM CURSOR
……………………………………..
……………………………………...
4. CLOSE CURSOR
END;
CURSOR DECLARATION:-
 The CURSOR is declared in the declarative block and is provided with a
name and a SELECT statement.
SYNTAX:-
CURSOR Cursor_Name [Parameter List]
[RETURN ReturnType]
IS Query
[FOR UPDATE [OF (column_List)][No WAIT]];
 The Cursor_Name can be any valid identifier.
 Parameter list is optional and can be any valid parameter used for query
execution.
 The RETURN clause is optional and specifies the type of data to be returned.
 The QUERY can be any SELECT statement
 The FOR UPDATE clause locks the records when the CURSOR is opened,
and maintains the status of the CURSOR as READ ONLY.
 If NOWAIT is specified, the program will exit immediately on open if an
exclusive lock cannot be obtained.
OPENING THE CURSOR:-
 CURSOR is opened in the EXECUTION SELECTION of the block.
SYNTAX:-
OPEN CursorName [(parameter values)];
 The OPEN command prepares the CURSOR for use.
EXAMPLE:
OPEN EmpCursor;
OPEN EmpCursor(20);
 The things that should be noted are
o The query is passed, when executed.
o The bind values are evaluated.
o The rows are recorded in the context area.

st
25-1-79, LN Puram – 1 Street, Podalakur Road, Nellore – 524003, Mob:0-9441217879 Page[26]
 EXPLORE SYSTEMS
o The result set is made ready.
 There can be only one active record in a CURSOR at a time.
 ON OPEN, the active record is the first one returned by the CURSOR’s query.
FETCHING RECORDS FROM THE CURSOR:-
 FETCH retrieves records from the CONTEXT AREA into a variable such that
the variable can be used.
 The FETCH command operates on the current record only and proceeds
through the result set one record at a time.
SYNTAX:-
FETCH CursorName INTO VariableName(s)|PL/SQL Record;
 The variablename(s) can be one or more comma delimited variables that
match the number and type of columns included in the result set.
Example:-
FETCH EmpCursor INTO V_EmpRecord;
CLOSING THE CURSOR:-
 The explicit cursors should be closed, else it may lead to memory leak;
 Until the CURSOR is closed, the memory is not released.
Syntax:-
CLOSE CursorName;
 CLOSE if unnecessarily used raised an exception.
CURSOR LOOPS:-
 CURSORS are generally very active with LOOPS to provide a way to navigate
through the ACTIVE RECORD SET.
CURSORS WITH SIMPLE LOOPS:-
 It can be used with the same syntax as discussed in simple loops or basic
loops.
 Within the loop each record in the active set is retrieved and used.
 Each loop iteration advances the pointer by one record in the active set.
 The EXIT WHEN statement is mandatory to be part of the loop.
CURSOR WITH FOR LOOPS:-
 Most applications are implemented in real time using the FOR LOOP
concept.
 While using a FOR LOOP, the cursor does not require explicit OPEN, FETCH,
or CLOSE.
 The processing of the CURSOR is implicitly handled by the PL/SQL.
 The INDEX variable within the FOR LOOP need not be explicitly declared.
 The CURSOR FOR LOOPs make the program more compact.

CURSOR FOR LOOPS USING SUB QUERIES:-

 If a sub query is instituted into a PL/SQL block then a CURSOR
DECLARATION is not necessary.
 In this case the sub query is embedded into the FOR LOOP.
Write a PL/SQL block to calculate the total quantity in order1 table
declare
q [Link]%type;
cursor cur is select qty from orders where itno=101;

st
25-1-79, LN Puram – 1 Street, Podalakur Road, Nellore – 524003, Mob:0-9441217879 Page[27]
 EXPLORE SYSTEMS
tot number:=0;
begin
open cur;
loop
fetch cur into q;
if cur%found then
dbms_output.put_line(q);
tot:=tot+q;
end if;
exit when cur %not found;
end loop;
dbms_output.put_line(tot);
close cur;
end;
/
Write a PL/SQL block to calculate number of rows of a ordered item.
declare
q [Link]%type;
cursor cur is select qty fromorders where itno=101;
x number(10);
begin
open cur;
loop
fetch cur into q;
x:=cur%rowcount;
exit when cur %not found;
end loop;
dbms_output.put_line(x);
close cur;
end;
/

Implicit cursors:-
Write a PL/SQL block to modify orders table
declare
x number:=0;
begin
delete from order1 where itno=101;
x:=sql %rowcount;
dbms_output.put_line(x);
end;
/

st
25-1-79, LN Puram – 1 Street, Podalakur Road, Nellore – 524003, Mob:0-9441217879 Page[28]
 EXPLORE SYSTEMS
Output:-
SQL>select * from order1;
SQL>set serveroutput on;
SQL>@program name
SQL>select * from order1;
PL/SQL procedure successfully completed.
Write a PL/SQL block to update a quantity in item table where qty=0
declare
x number:=0;
begin
update item set qty=500 where qty=0;
x:=sql%rowcount;
if x>0 then
dbms_output.put_line(x||’rows updated’);
else
dbms_output.put_line(‘no rows updated’);
end if;
end;
/
SQL>select * from item;
SQL>update item set qty=0 where itno=104;
1 row updated
SQL>@programe name
1 row updated
PL/SQL procedure successfully completed.
SQL>select * from item;
Cursor for loop:-
Write a PL/SQL block to delete items from item table
declare
cursor c1 is select itname from item where itno=101;
rec c1%rowtype;
begin
for rec in c1
loop
delete from item where itname like ‘lux’;
exit when c1%not found;
end loop;
end;
/
Working with exceptions in PL/SQL:-
EXCEPTION:-
 It is an IDENTIFIER in PL/SQL raised during the execution of a block that
termination its main body of ACTIONS.
 A block terminates when PL/SQL raises an EXCEPTION, by specifying an
EXCEPTION HANDLER we can perform final action.
METHODS TO RAISE AN EXCEPTION:-

st
25-1-79, LN Puram – 1 Street, Podalakur Road, Nellore – 524003, Mob:0-9441217879 Page[29]
 EXPLORE SYSTEMS
 An ORCLE ERROR OCCURS and the associated exception is raised
automatically.
 Raise the EXCEPTION explicitly by issuing the RAISE statement within the
block.
 The RAISED EXCEPTION can be either USER DEFINED or PREDEFINED.
EXCEPTION HANDLING:-
 Exception handling can happier in two types
o Exception trapping
o Exception propagation
EXCEPTION TRAPPING:-
 If an exception is raised in the executable section of the block then process
the exception to the corresponding exception handling in the exception section of
the same block.
 If the exception is successfully handled then the exception does not
propagate to the enclosing block of environment.
 If exception is trapped and handled successfully then the PL/SQL block
terminated successfully.
EXCEPTION PROPAGATION:-
 If an EXPECTION is raised in the executable section of the block and there
is no corresponding Exception Handler then the PL/SQL Block terminates with
failure and the exception is propagated to the calling encampment.
EXCEPTION TRAPPING EXCEPTION PROTAGATION
DECLARE DECLARE
BEGIN BEGIN
Exception Raised; Exception Raised;
EXCEPTION EXCEPTION
Exception Trapped; END
END Exception Not Trapped
Exception Propagation to
calling Environment
TYPES OF EXCEPTIONS:
 As per PL/SQL Exception are considered to be of three types
o Predefined Oracle Server Errors
o Non-Predefined Oracle Server Errors
o User Defined Errors
 The PL/SQL programmer can program for exceptions to avoid disruption at
Runtime.
SPECIFICATIONS OF EXCEPTONS:
1) EXCEPTION TYPE: Predefined Oracle Server
Errors.
DESCRIPTON: These are on of appropriately
20 errors that occur most often
in PL/SQL code.
2) EXCEPTION TYPE: Non-predefined Oracle
Server Errors.
DESCRIPTON: It is any other standard Oracle
Server Error.

st
25-1-79, LN Puram – 1 Street, Podalakur Road, Nellore – 524003, Mob:0-9441217879 Page[30]
 EXPLORE SYSTEMS
HANDLING TIP: Declare with in the
declarative section and allow
the Oracle Server to Raise
them implicity.
3) EXCEPTION TYPE: User defined Errors
DESCRIPTON: It is any condition that the
develops determines, which is
abnormal.
HANDLING TIP: Declare within the
Declarative section and Raise
Explicity.
TRAPPING EXCEPTIONS:
 Including a corresponding routine within the exception handling section of
the PL/SQL block can trap any errors raised while running a PL/SQL Block.
 Each Exception Handler consists of a WHEN clause which specifies and
Exception.
 Each Exception is followed by a sequence of statements to be executed when
that exception is raised.
Syntax:
EXCEPTION
WHEN EXCEPTION 1[OR Exception 2…………..] THEN
Statement 1;
Statement 2;
.
.
.
WHEN OTHERS THEN
Statement 1;
Statement 2;
.
.
.
 OTHERS is an optional EXCEPTION Handling clause that traps unspecified
Exceptions.
WHEN OTHERS EXCEPTION HANDLER:
 The EXCEPTION Handling section taps only those Exceptions that are
specified any other Exceptions are not trapped unless the OTHERS EXCEPTION
handles is used.
 The OTHERS should be the last exceptions handles in the definition.
TRAPPING EXCEPTIONS GUIDELINES:
 Begin the exception handling section of the Block with the keyword
EXCEPTION.
 Define several Exception handlers, each with its own set of actions for the
block.
 When an Exception occurs OLSQL processes only one handles before leaving
the block
 Place the OTHERS clause after all other Exception Handling Clauses.

st
25-1-79, LN Puram – 1 Street, Podalakur Road, Nellore – 524003, Mob:0-9441217879 Page[31]
 EXPLORE SYSTEMS
 We can have at most one OTHERS clause.
 EXCEPTIONS cannot appear in argument statements or SQL statements.
HANDLING PREDEFINED EXCEPTIONS:
 A PREDEFINED

st
25-1-79, LN Puram – 1 Street, Podalakur Road, Nellore – 524003, Mob:0-9441217879 Page[32]