UNI

T-V

Dat
aTy
pes

PL/SQLvar
iabl
es,const
antsandpar ameter
smusthav eav ali
ddat
aty
pewhi
ch
speci
fi
esastor
ageformat,
const
rai
nts,andvali
drangeofv
alues.

Cat
egor
y Descr
ipt
ion

Si
ngl
eval
ueswithnoi
nter
nalcomponent
s,suchas
aNUMBER,DATE,
Scal
ar
orBOOLEAN.

Point
erstolargeobject
sthatarest
oredseparatel
y
Lar ect f
ge Obj r
om otherdata
(LOB) i
tems,suchast ext
,graphi
cimages,
videocl
ips,and
soundwav ef
orms.

Dat
aitemst hathav
eint
ernalcomponent
sthatcan
beaccessed
Composi
te
i
ndi
vi
dual
l
y.Forexampl
e,col
l
ect
ionsandr
ecor
ds.

Ref
erence Poi
nter
stoot
herdat
ait
ems.

PL/
SQLScal
arDat
aTy
pesandSubt
ypes
PL/
SQLScal
arDat
aTy
pesandSubt
ypescomeundert
hef
oll
owi
ngcat
egor
ies:

Dat
eTy
pe Descr
ipt
ion

Numeri
cval
uesonwhi
char
it
hmet
icoper
ati
onsar
e
Numer
ic per
for
med.

Alphanumeri
c v al
ues t
hat r
epr
esent si
ngl
e
charact
ersorst
ri
ngsof
Char
act
er
char
act
ers.

Logi
calval
ues on whi
ch l
ogi
caloper
ati
ons ar
e
Bool
ean perf
ormed.

Dat
eti
me Dat
esandt
imes.
PL/SQLprovi
dessubt
ypesofdat
aty
pes.Forexampl
e,t
hedatat
ypeNUMBERhasa
subty
pecall
edINTEGER.Youcanusesubtypesiny
ourPL/SQLprogr
am t
omake
thedatat
ypes

compati
blewithdat
atypesinotherprogr
amswhi
l
eembeddi
ngPL/
SQLcodei
n
anot
herprogr
am,suchasaJavaprogr
am.

PL/
SQLNumer
icDat
aTy
pesandSubt
ypes
Fol
l
owi
ngi
sthedet
ail
ofPL/
SQLpr
e-def
inednumer
icdat
aty
pesandt
hei
rsub-
types:

Dat
aTy
pe Descr
ipt
ion

Si
gned integer i
nrange -
2,147,
483,
648 t
hrough
2,
147,
483,647,
PLS_
INTEGER
r
epr
esent
edi
n32bi
ts

Si
gned integer i
nrange -
2,147,
483,
648 t
hrough
2,
147,
483,647,
BI
NARY_
INTEGER
r
epr
esent
edi
n32bi
ts

Singl
e-pr
eci
sion I
EEE 754-
for
mat f
loat
ing-
poi
nt
BI
NARY_
FLOAT number

Doubl
e-pr
eci
si
on I
EEE 754-
for
mat f
loat
ing-
poi
nt
BI
NARY_
DOUBLE number

Fixed-
poi
ntorf l
oati
ng-
poi
ntnumberwi
thabsol
ute
NUMBER(
prec, valuei
nrange1E-130
scal
e) to(butnoti
ncl
uding)1.
0E126.ANUMBERv
ari
abl
e
canalsor
epr
esent0.

ANSI speci
fi
c fixed-
poi
nt t
ype wi
th maxi
mum
pr
eci
sionof38decimal
DEC(
prec,
scal
e)
di
git
s.

DECIMAL(
prec, I
BM speci f
ic fi
xed-
point ty
pe wi
th maxi
mum
scal
e) pr
eci
sionof38deci
mal di
git
s.

NUMERIC(
pre, Fl
oatingtypewi t
hmax i
mum pr
ecisionof38deci
mal
scal
e) di
git
s.
ANSIspeci f
icf l
oati
ng-poi
nttype wi t
h maxi
mum
DOUBLE pr
ecisionof126bi nary
PRECI
SION di
git
s( approximatel
y38decimaldigit
s)
 ANSIand I
BM speci
fi
cf l
oat
ing-
poi
nt t
ype wi
th
maxi
mum pr
eci
si
onof126
FLOAT
bi
nar
ydi
git
s(appr
oxi
mat
ely38deci
mal
digi
ts)

ANSIspeci
fi
cintegert
ypewi
thmaxi
mum pr
eci
sion
I
NT of38deci
maldigi
ts

ANSIandIBM speci
fi
cint
egert
ypewi
thmaxi
mum
pr
eci
sionof38
I
NTEGER
deci
mal
digi
ts

ANSIandIBM speci
fi
cint
egert
ypewi
thmaxi
mum
pr
eci
sionof38
SMALLI
NT
deci
mal
digi
ts

Fl
oat
ing-
poi
ntt
ypewi
thmaxi
mum pr
eci
sionof63
bi
nar
ydigi
ts
REAL
(
appr
oxi
mat
ely18deci
mal
digi
ts)

Fol
l
owi
ngi
sav
ali
ddecl
arat
ion:
DECLARE
num1INTEGER;
num2REAL;
num3DOUBLEPRECI
SION;BEGI
N
nul
l;END;
/

Whent
heabov
ecodei
scompi
l
edandexecut
ed,
itpr
oducest
hef
oll
owi
ngr
esul
t:
PL/
SQLpr
ocedur
esuccessf
ull
ycompl
eted
PL/
SQLChar acterDataTypesandSubty
pesFol l
owi
ngi
sthedet
ailofPL/
SQLpr
e-
def
inedchar
acterdat
atypesandt
hei
rsub-
types:

Dat
aTy
pe Descr
ipt
ion

Fi
xed-
lengt
hcharact
erst
ri
ngwi
thmaxi
mum si
ze
CHAR of32,
767bytes

Var
iabl
e-l
ength char
act
erst
ri
ng wi
th maxi
mum
VARCHAR2 si
zeof32,767bytes

Var
iabl
e-l
engt
h binar
y or by
te st
ri
ng wi
th
maximum si
zeof32,
767
RAW
by
tes,
noti
nter
pret
edbyPL/
SQL

Fi
xed-l
engt
h national char
act
er st
ri
ng wi
th
maximum si
zeof32,767
NCHAR
by
tes

Var
iabl
e-l
engt
h nati
onal char
act
er st
ri
ng wi
th
maximum si
zeof32,
767
NVARCHAR2
by
tes

Var
iabl
e-l
ength char
act
erst
ri
ng wi
th maxi
mum
LONG si
zeof32,760bytes

Var
iabl
e-l
engt
h binar
y or by
te st
ri
ng wi
th
maximum si
zeof32,
760
LONGRAW
by
tes,
noti
nter
pret
edbyPL/
SQL

Physi
calr
owident
if
ier
,theaddr
essofar
owi
nan
ROWI
D ordi
nar
ytabl
e

Univer
sal r
ow i denti
fi
er (
phy
sical
,logi
cal
,or
UROWI
D for
eignrowident
ifi
er)
 Val
i
d Dat
eti
me
Fi
eldName Val
ues Val
i
dInt
erv
alVal
ues

-
4712t
o9999
YEAR (excl
udi ear Anynonzer
ng y oint
eger
0)

MONTH 01t
o12 0t
o11

01t o31( l
imit
ed
byt he
values of
MONTHand
YEAR,accor di
ng
DAY tothe Anynonzer
oint
eger
rul
es of t he
calendar
forthelocal
e)

HOUR 00t
o23 0t
o23

MI
NUTE 00t
o59 0t
o59

00 t o 59.9(n),
where 0t o 59.9(n)
,where 9(n)i
sthe
9(n) i s t he precisionof
SECOND preci
sionof
ti
me f ract
ional i
nter
v alf
racti
onal
seconds
seconds

-12to14(range
TI
MEZONE_
HO accommodat es
UR dayl
ight Notappl
i
cabl
e
savi
ngs ti
me
changes)

TI
MEZONE_
MINUTE 00t
o59 Notappl
i
cabl
e

Found i n the
dynamic
TI
MEZONE_
RE perf
ormance
GI
ON vi
ew Notappl
i
cabl
e
V$TIMEZONE_N
AMES
 Found i n the
dynamic
TI
MEZONE_
AB perf
ormance
BR vi
ew Notappl
i
cabl
e
V$TIMEZONE_N
AMES

PL/
SQLBool
eanDat
aTy
pes

TheBOOLEANdataty
pestor
eslogi
calv
aluest
hatar
eusedinl
ogi
caloper
ati
ons.The
l
ogical
val
uesar
etheBool
eanval
uesTRUEandFALSEandtheval
ueNULL.

Howev er
,SQLhasnodat
aty
peequi
val
entt
oBOOLEAN.Ther
efor
e,Bool
eanv
alues
cannotbeusedi
n:

SQLst
atement
s

Bui
l
t-i
nSQLf
unct
ions(
suchasTO_
CHAR)

PL/
SQLf
unct
ionsi
nvokedf
rom SQLst
atement
s

PL/
SQLDat
e,t
imeandI
nter
val
Types

TheDATEdataty
petostor
efi
xed-
lengt
hdateti
mes,whichi
ncludet
heti
meofday
i
nsecondssi
ncemidni
ght
.Val
i
ddat esr
angefr
om Januar
y1,4712BCtoDecember
31,
9999AD.

The default date format is set by t he Or acl

e ini
ti
ali
zat
ion parameter
NLS_DATE_FORMAT.Forexampl e,t
hedefaul
tmightbe'DD-MON-YY'
,whi
chincludes
atwo-di
gitnumberfort
hedayoft hemonth,anabbrevi
ati
onofthemonthname, and
thel
asttwodigi
tsoftheyear
,forexampl
e,01-OCT-
12.

EachDATEincl
udest
hecentur
y,year
,month,day,hour
,mi
nut
e,andsecond.The
fol
lowi
ngt
abl
eshowsthev
ali
dvaluesf
oreachfi
eld:

PL/
SQLUser
-Def
inedSubt
ypes

Asubty
pei
sasubsetofanotherdat
atype,whichi
scall
edi
tsbasetype.Asubtype
hast
hesamev
ali
doperat
ionsasitsbasetype,
butonl
yasubsetofi
tsval
idval
ues.

PL/SQLpr edef
inessev
eralsubt
ypesi
npackageSTANDARD.Forexampl
e,PL/
SQL
predef
inesthesubty
pesCHARACTERandINTEGERasfol
lows:
SUBTYPECHARACTERI
SCHAR;
SUBTYPEI
NTEGERISNUMBER(
38,
0);

Youcandefineandusey ourownsubtypes.Thef
oll
owi
ngpr
ogr
am i
l
lust
rat
es
def
ini
ngandusi
ngauser
-defi
nedsubt
ype:
DECLARE
SUBTYPEnameI Schar(20);
SUBTYPEmessageI Svarchar2(
100)
;sal
utat
ionname;
greeti
ngsmessage;BEGIN
sal
utati
on:='
Reader'
;
 greet
ings:
='Welcometot
heWor
ldofPL/
SQL'
;dbms_
out
put
.put
_li
ne(
'Hel
l
o '|
|
salut
ati
on|
|gr
eet
ings)
;
END;
/

Whent
heabov
ecodei
sexecut
edatSQLpr
ompt
,itpr
oducest
hef
oll
owi
ngr
esul
t:

Hel
l
oReaderWel
comet
otheWor
ldofPL/
SQL

PL/
SQLpr
ocedur
esuccessf
ull
ycompl
eted.

NULLsi
nPL/
SQL

PL/SQLNULLv al
uesrepresentmissi
ngorunknowndat aandtheyar
enotanint
eger
,a
charact
er,oranyotherspecif
icdataty
pe.NotethatNULLisnotthesameasanempty
datastri
ngort henullcharacterval
ue'\0'
.Anul lcanbeassignedbuti
tcannotbe
equatedwithanyt
hing,i
ncludi
ngitsel
f.

Var
iabl
es
Av ar
iableisnothi
ngbutanamegi ventoast orageareat hatourprogramscan
manipulate.Eachvari
abl
einPL/SQLhasaspecifi
cdatat ype,whichdet
erminesthe
sizeandlayoutofthevar
iabl
e'
smemory;t
herangeofvaluesthatcanbestoredwit
hin
thatmemor yandthesetofoper
ati
onst
hatcanbeappli
edtot hevar
iabl
e.

ThenameofaPL/ SQLv ari

abl
econsi stsofal
ett
eropti
onall
yfol
lowedbymor el
ett
ers,
numer als,dol
l
arsigns,underscores,andnumbersignsandshoul dnotexceed30
characters.Bydefault
,vari
ablenamesar enotcase-sensi
ti
ve.You cannotusea
reserv
edPL/ SQLkeywordasav ar
iabl
ename.

PL/SQLprogr
ammi ngl
anguageall
owstodefi
nevar
ioustypesofvari
abl
es,swhichwe
wil
lcoveri
nsubsequentchapt
ersli
kedat
et i
medatatypes,recor
ds,col
lect
ions,et
c.
Forthi
schapt
er,
letusst
udyonlybasi
cvar
iabl
ety
pes.

Var
iabl
eDecl
arat
ioni
nPL/
SQL

PL/SQLv ari
ablesmustbedeclar edint
hedeclarat
ionsectionorinapackageasa
globalvari
able.When you declareav ar
iabl
e,PL/ SQL al
locat
es memor
yf ort
he
vari
able'
sval
ueandthestor
agel ocati
oni
sidenti
fi
edbyt hevari
abl
ename.

Thesy
ntaxf
ordecl
ari
ngav
ari
abl
eis:
v
ari
abl
e_name[
CONSTANT]dat
aty
pe[
NOTNULL][
:=|
DEFAULTi
nit
ial
_val
ue]

Where,var
iabl
e_nameisaval
i
dident
ifi
erinPL/SQL,datat
ypemustbeavali
dPL/SQL
datatypeoranyuserdefi
neddatat ypewhichweal r
eadyhavedi
scussedi
nl ast
chapt
er.Somev al
idv
ari
abl
edecl
arat
ionsalongwit
ht hei
rdef
ini
ti
onar
eshownbelow:

sal
esnumber(
10,2);
piCONSTANTdoublepreci
si
on:
=3.
1415;
namev
archar
2(25)
;
addr
essvar
char2(
100);
 heny oupr ov
ideasi ze,scal
eorpreci
sionl
imitwiththedat
atype,i
tiscal
l
eda
constr
ained declarati
on. Const
rai
ned decl
arat
ions r
equi
reless memorythan
unconstrai
neddeclarati
ons.

Forexampl
e:

sal
esnumber
(10,
2);
namev
archar
2(25)
;addr
essv
archar
2(100)
;

I
nit
ial
i
zingVar
iabl
esi
nPL/
SQL

Wheneveryoudeclareav ar
iable,PL/SQLassi
gnsitadef
aul
tval
ueofNULL.I
fyou
wanttoini
ti
ali
zeav ariablewithav al
ueotherthant
heNULLval
ue,y
oucandoso
dur
ingthedecl
arat
ion,usingeitherofthefol
l
owing:

TheDEFAULTkey
wor
d Theassi
gnmentoper
ator
Forexampl e:
counter
binary_
int
eger :
=0;
greeti
ngs
varchar2(
20) DEFAULT'
Hav
eaGoodDay
';

You can al
so a var
iabl
e shoul
d not hav
e aNULLv
alue
speci
fyt
hat usi
ngtheNOT
NULLconst
rai
nt.Ify
ouuset
heNOTNULLconst
rai
nt,
youmustexpl
i
cit
lyassi
gnani
nit
ial
val
uef
orthatv
ariabl
e.

I
ti sagoodprogr
ammi ngpract
icetoinit
ial
i
zevari
abl
esproper
lyot
her
wise,somet
imes
progr
am woul
dproduceunexpectedresul
t.Tryt
hefol
lowi
ngexamplewhichmakesuse
ofvari
oust
ypesofvar
iabl
es:
DECLARE
ainteger:=10;bint
eger:=20;
cinteger;
freal
;BEGIN
c:=a+b;
dbms_ output
.put
_li
ne(
'Val
ue of c: ' | | c)
; f :
= 70.
0/3.
0;
dbms_ output
.put
_li
ne(
'Val
ueoff:'
||f)
;
END;
/

Whent
heabov
ecodei
sexecut
ed,
itpr
oducest
hef
oll
owi
ngr
esul
t:
Val
ueofc:
30
Val
ueoff
:23.
333333333333333333

PL/
SQLpr
ocedur
esuccessf
ull
ycompl
eted.

Var
iabl
eScopei
nPL/
SQL

PL/SQLallowst henest
ingofBlocks,i
.e.,eachprogram bl
ockmaycontain
anotherinnerblock.Ifa var
iabl
eis decl
ared wit
hin an i
nnerbl
ock,i
tis not
accessi
blet
otheout
erbl
ock.Howev
er,i
f
v
ari
ablei
sdeclaredandaccessi
blet
oanout erBl
ock,i
tisal
soaccessi
blet
oal
lnest
ed
i
nnerBl
ocks.Therear
etwotypesofvar
iabl
escope:

Localvari
ables-var
iablesdecl
aredinaninnerbl
ockandnotaccessibl
etoout
er
blocks.
Globalvar
iabl
es-v
ari
ablesdecl
aredi
ntheout
ermostbl
ockorapackage.
Fol
l
owi
ngexampl
eshowst
heusageofLocal
andGl
obal
var
iabl
esi
nit
ssi
mpl
efor
m:
DECLARE
-
-Globalvar
iablesnum1number: =95;num2number:=85;
BEGIN
dbms_output.
put_l
ine(
'Out
er Var iable num1: ' | | num1)
;
dbms_output.
put_l
ine(
'Out
erVari
abl
enum2: '
||num2)
;DECLARE
-
-Localvari
ablesnum1number: =195;num2number:=185;
BEGIN
dbms_output.
put_l
ine(
'I
nner Var iabl
e num1: ' | | num1)
;
dbms_output.
put_l
ine(
'I
nnerVar
iabl
enum2:'|
|num2);
END;END;
/

Whent
heabov
ecodei
sexecut
ed,
itpr
oducest
hef
oll
owi
ngr
esul
t:
Out
erVari
abl
enum1:95
Out
erVari
abl
enum2:85
I
nnerVar
iabl
enum1:
195
I
nnerVar
iabl
enum2:
185

PL/
SQLpr
ocedur
esuccessf
ull
ycompl
eted.

Const
ant
s
Aconst
antholds a value t
hatonce decl
ared,does notchange i nthe pr
ogram.
Aconst
antdecl
arat
ionspeci
fi
esi
tsname,datatype,andvalue,andal
l
ocat
esstor
agefor
i
t.Thedecl
arat
ioncanal
soimposet
heNOTNULLconst rai
nt.

Decl
ari
ngaConst
ant

Aconstanti
sdeclar
edusi
ngtheCONSTANTkey
wor
d.I
trequi
resani
nit
ial
val
ueanddoes
notal
l
owt hatv
aluetobechanged.

Forexampl
e:

PICONSTANTNUMBER: =3.141592654;DECLARE
-
-constantdecl
arat
ion
piconst
antnumber: =3.
141592654;--ot
herdecl
arat
ions
 radi
usnumber (
5,2);di
anumber (5,2)
;
cir
cumfer
encenumber (7,2);
areanumber( 10,2)
;
BEGIN
- pr ocessi
ngr adius:=9.5;
dia:=r
adi
us*2;
cir
cumfer
ence:=2. 0*pi *radius; ar
ea:=pi
*radi
us*r adius;
- output
dbms_output
.put_l
ine('
Radius: ' |
| r
adius)
;
dbms_output
.put_l
ine('
Diameter : ' || dia)
;
dbms_output
.put_l
ine('
Circumference: ' || circumf
erence)
;
dbms_output
.put_l
ine('
Area:'|
| area);
END;
/

When t
heabovecodei
sexecut
ed atSQLpr
ompt
,itpr
oducest
he
f
oll
owi
ngresul
t:
Radius:9.
5
Diameter:
19

Ci
rcumfer
ence:
59.
69
Ar
ea:283.
53

Pl
/SQLpr
ocedur
esuccessf
ull
ycompl
eted.

Oper
ator
s

Anoper at
ori sasymbolthatt
ellsthecompil
ertoperf
orm speci
fi
cmathematicalor
l
ogicalmani pul
ati
on.PL/
SQLl anguageisr
ichinbuil
t-
inoperat
orsandprov
idest he
fol
l
owi ngtypesofoperat
ors:

Ari
thmet icoperator
s
Relati
onal operat
ors
Compar isonoper at
ors
Logicaloperators
Str
ingoper ators

Ar
it
hmet
icOper
ator
s

Fol
lowi
ngtableshowsallthear
it
hmeti
coperat
orssuppor
tedbyPL/
SQL.Assume
var
iabl
eAholds10andvari
abl
eBholds5,t
hen:

Oper
at
or Descr
ipt
ion Exampl
e

A +B wi
l
l
+ Addst
wooper
ands gi
ve15
 A -B wi
l
l
- Subt
ract
ssecondoper
andf
rom t
hef
ir
st gi
ve5

A *B wi
l
l
* Mul
ti
pli
esbot
hoper
ands gi
ve50

A /B wi
l
l
/ Di
vi
desnumer
atorbyde-
numer
ator gi
ve2

A**Bwi
l
l
Exponent
iat
ionoperat
or,r
aisesoneoper
andt
o gi
ve
*
* thepowerofother
100000

Exampl
e:
BEGI
N
dbms_out
put
.put
_li
ne( 10 + 5)
; dbms_out
put
.put
_li
ne( 10 - 5)
;
dbms_out
put
.put
_li
ne( 10 * 5)
; dbms_
output
.put
_li
ne( 10 / 5)
;
dbms_out
put
.put
_li
ne(10**5)
;
END;
/

When t
heabovecodei
sexecut
ed atSQLpr
ompt
,itpr
oducest
he
f
oll
owi
ngresul
t:
15
5
50
2
100000

PL/
SQLpr
ocedur
esuccessf
ull
ycompl
eted.

Rel
ati
onal
Oper
ator
s

Rel
ationaloperator
scomparetwoexpressi
onsorv al
uesandret
urnaBool
eanresul
t.
Fol
lowingt ableshowsalltherel
ati
onaloper at
orssuppor
tedbyPL/SQL.Assume
var
iableAhol ds10andvar
iabl
eBholds20,then:
 Oper Exampl
at
or Descr
ipt
ion e

Checksift
heval
uesoft
wooper
andsar
eequalor (
A = B)
not
,ifyest
hen i
snot
=
condi
ti
onbecomest
rue. t
rue.

!
= Checksift
heval
uesoft
wooper
andsar
eequalor (
A!=B)
not
,ifval
uesar
e i
s
<>
notequal
thencondi
ti
onbecomest
rue. t
rue.
~=

Checksift
heval
ueofl
eftoper
andi
sgr
eat
ert
han (
A > B)
theval
ueofri
ght i
snot
>
oper
and,
ify
est
hencondi
ti
onbecomest
rue. t
rue.

Checksift
hev al
ueofl
eftoper
andi
slesst
han
theval
ueofri
ght (
A < B)
< i
str
ue.
oper
and,
ify
est
hencondi
ti
onbecomest
rue.

Checksi
ftheval
ueofl
eftoper
andi
sgr
eat
ert
han (
A>=B)
orequal
tothe i
snot
>= val
ue ofri
ghtoper
and,i
fyes t
hen condi
ti
on
becomest
rue. t
rue.

Checksiftheval
ueofl
eftoper
andi
slesst
hanor (
A<=B)
equalt
ot heval
ue i
s
<= ofri
ghtoper
and,i
fyest
hencondi
ti
onbecomes
tr
ue. t
rue.

Exampl
e:
DECLARE
anumber(
2):=21;
bnumber(2):
=10;BEGIN
I
F(a=b)then
dbms_out
put.
put
_li
ne('
Li
ne1-ai
sequalt
ob');
ELSE
dbms_out
put.
put
_li
ne('
Li
ne1-ai
snotequal
tob')
;ENDI
F;

I
F(a<b)then
dbms_
output
.put
_li
ne(
'Li
ne2-ai
slessthanb')
;ELSE
dbms_
output
.put
_li
ne(
'Li
ne2-ai
snotlessthanb')
;ENDI
F;
I
F(a>b)THEN
 dbms_
out
put
.put
_li
ne(
'Li
ne3-ai
sgreat
erthanb')
;ELSE
dbms_
out
put
.put
_li
ne(
'Li
ne3-ai
snotgreat
erthanb')
;ENDI
F;

-
-Letschangevalueofaandba:=5;
b:=20;
I
F(a<=b)THEN
dbms_output
.put_
li
ne(
'Li
ne4-aisei
therequalorl
esst
hanb'
);END
I
F;
I
F(b>=a)THEN
dbms_out
put
.put
_li
ne(
'Li
ne5-bi
sei
therequalorgr
eat
ert
hana'
);
ENDIF;
I
F(a<>b)THEN
dbms_
out
put
.put
_li
ne(
'Li
ne6-ai
snotequal
tob')
;ELSE
dbms_
out
put
.put
_li
ne(
'Li
ne6-ai
sequalt
ob');
ENDIF;
END;
/

When t
heabovecodei
sexecut
ed atSQLpr
ompt
,itpr
oducest
he
f
oll
owi
ngresul
t:
Li
ne1-ai
snotequal t
ob
Li
ne2-ai
snotlessthanb
Li
ne3-ai
sgreatert
hanb
Li
ne4-ai
sei
therequal orl
esst
hanb
Li
ne5-bi
sei
therequal orgr
eat
ert
hana
Li
ne6-ai
snotequal t
ob

PL/
SQLpr
ocedur
esuccessf
ull
ycompl
eted

Compar
isonOper
ator
s

Compar
ison oper
ator
s ar
e used forcompar
ing one expr
essi
on t
o
anot
her
.Theresul
tisal
waysei
therTRUE,
FALSEORNULL.

Oper
ator Descr
ipt
ion Exampl
e

I
f' Zar a Ali
'
l
ike'Z%
The LI KE operator compares a A_ i
'returnsa
character
,st
ri
ng,orCLOB
val
uet oapatter
nandr et
urnsTRUEif Booleant r
ue,
LI
KE thevaluematches whereas,
'
NuhaAl i'
thepatter
nandFALSEi fi
tdoesnot
. l
ike 'Z% A_ i
'
ret
urnsa
Boolean
fal
se.

BETWEE The BETWEEN operat

or t
est
s I
fx=10t
hen,
N whet
herav
aluel
i
esi
na x
 speci
fi
edrange.xBETWEENaANDb bet
ween 5
meansthatx>=a and20

ret
urns tr
ue,
andx<=b. x
between 5
and10
ret
urns tr
ue,
butx
between 11
and20
ret
urnsfal
se.

I
fx=' m't
hen,
xin
(
'a'
,' b'
,' c'
)
TheINoper
atortest
ssetmember
shi
p.xI
N r
eturns
(set
)meansthat bool
eanf
alse
I
N butxi
n
xi
sequal
toanymemberofset
. ('
m',' n'
,' o'
)
ret
urns
Booleantrue.

The IS NULL oper ator retur

ns the I
fx='m',then
BOOLEANv al
ueTRUEif '
xis
I
S i
tsoperandisNULLorFALSEi fiti
snot nul
l'returns
NULL NULL. Bool
ean
Compari
sonsinv
olv
ingNULLvaluesal
ways
yi
eldNULL. f
alse.

LI
KEOper
ator:
Thisprogram t
eststheLIKEoper
ator
,thoughy
ouwil
llear
nhowt owr
it
e
procedureinPL/SQL,butI'
m goi
ngtouseasmallprocedur
e()t
oshow
thefuncti
onal
it
yofLI KEoper
ator
:
DECLARE
PROCEDUREcompar e(val
uevarchar2,pat
ter
nvarchar
2)i
sBEGI
N
I
FvalueLIKEpatt
ernTHENdbms_ output.
put_
li
ne('
True'
);
ELSE
dbms_out
put.
put
_li
ne('
Fal
se')
;ENDI F;
END;
BEGI
N
compar
e('
Zar
aAl
i
','
Z%A_
i'
);
compar
e('
NuhaAl
i
','
Z%A_
i'
);
END;
 /

When t
heabovecodei
sexecut
ed atSQLpr
ompt
,itpr
oducest
he
f
oll
owi
ngresul
t:
True
False

PL/
SQLpr
ocedur
esuccessf
ull
ycompl
eted.

BETWEENOper
ator
:
Thef
oll
owi
ngpr
ogr
am showst
heusageoft
heBETWEENoper
ator
:
DECLARE
x number (
2):=10;BEGIN
I
F( xbet
ween5and20)THENdbms_ out
put
.put
_li
ne(
'Tr
ue'
);
ELSEdbms_ out
put.put
_li
ne(
'Fal
se'
);
ENDI F;

I
F(xBETWEEN5AND10)THENdbms_ out
put
.put
_li
ne(
'Tr
ue'
);
ELSEdbms_
out
put
.put
_li
ne(
'Fal
se'
);
ENDIF;

I
F( xBETWEEN11AND20)THENdbms_ out
put
.put
_li
ne(
'Tr
ue'
);
ELSEdbms_ out
put
.put
_li
ne(
'Fal
se'
);
ENDIF;END;
/

When t
heabovecodei
sexecut
ed atSQLpr
ompt
,itpr
oducest
he
f
oll
owi
ngresul
t:
True
True
False

PL/
SQLpr
ocedur
esuccessf
ull
ycompl
eted.

I
NandI
SNULLOper
ator
s:
Thef
oll
owi
ngpr
ogr
am showst
heusageofI
NandI
SNULLoper
ator
s:
DECLARE
l
etterv
archar2(1):
='m';
BEGI N
I
F( l
ett
erin('
a'
,'
b','
c')
)THENdbms_ out
put
.put
_li
ne(
'Tr
ue'
);
ELSEdbms_ output
.put_l
i
ne('
False'
);
ENDI F;
I
F(l
ett
eri
n('
m','
n'
,'
o'
))THENdbms_ out
put
.put
_li
ne(
'Tr
ue'
);
ELSEdbms_out
put.
put_
li
ne(
'Fal
se'
);
ENDIF;

I
F(let
teri
snul
l)THENdbms_ output.
put_
li
ne(
'Tr
ue'
);
ELSE
dbms_out
put.put
_li
ne(
'Fal
se'
);ENDIF;
END;
/

When t
heabovecodei
sexecut
ed atSQLpr
ompt
,itpr
oducest
he
f
oll
owi
ngresul
t:
False
True
False

PL/SQLproceduresuccessf
ull
ycompl
eted.
Logical
Operator
s

Foll
owingtableshowst heLogi
caloperat
orssupportedbyPL/ SQL.All
these operat
ors workon Boolean oper
ands and produces Boolean
resul
ts.Assumev ar
iabl
eAholdstrueandvari
abl
eBhol dsfalse,
then:

Oper
at
or Descr
ipt
ion Exampl
e

Call
ed logi
cal AND oper
ator
.If bot
h t
he (
AandB)
operandsar
etr
uethen i
s
and
condi
ti
onbecomest
rue. f
alse.

Call
edl ogi
calOR Oper
ator
.Ifanyoft
het
wo
operandsistr
uet
hen (
A orB)
or i
str
ue.
condi
ti
onbecomest
rue.

Call
edl
ogicalNOT Oper
ator
.Usedt
orev
erse
thel
ogi
cal
st at
eofi
ts not (A
oper
and.Ifacondi
ti
oni
str
uet
henLogi
calNOT andB)i
s
not oper
atorwil
lmake
t
rue.
i
tfal
se.

Exampl
e:
DECLARE
abool
ean:=t
rue;
bbool
ean:
=fal
se;
BEGI
N
I
F(aANDb)THEN
 dbms_
output
.put
_li
ne(
'Li
ne1-Condi
tionistr
ue');
ENDI
F;
I
F(aORb)THEN
dbms_
output
.put
_li
ne(
'Li
ne2-Condi
tionistr
ue');
ENDI
F;
I
F(NOTa)THEN
dbms_
output
.put
_li
ne(
'Li
ne3-aisnottrue'
);
ELSE
dbms_
output
.put
_li
ne(
'Li
ne3-aistr
ue')
; ENDIF;
I
F(NOTb)THEN
dbms_
output
.put
_li
ne(
'Li
ne4-bisnottrue'
);ELSE
dbms_
output
.put
_li
ne(
'Li
ne4-bistr
ue')
; ENDIF;
END;
/

When t
heabovecodei
sexecut
ed atSQLpr
ompt
,itpr
oducest
he
f
oll
owi
ngresul
t:
Li
ne2-Condi
tionistr
ue
Li
ne3-aistr
ue
Li
ne4-bisnottrue

PL/
SQLpr
ocedur
esuccessf
ull
ycompl
eted.

PL/
SQLOper
atorPr
ecedence

Oper
atorprecedencedetermi
nest hegroupi
ngoft er
msinanexpr essi
on.Thisaf
fects
how anexpressi
onisevaluat
ed.Certai
noperat
orshavehi
gherprecedencethanothers;
forexample,the mult
ipli
cat
ion operat
orhas higherpr
ecedence than t
he addit
ion
oper
ator
:

Forexampl ex=7+3*2;her e,xi

sassigned13,not20becauseoperat
or*hashi
gher
precedencethan+,
soi
tfi
rstget
smult
ipl
i
edwi t
h3*2andthenaddsint
o7.

Here,oper
ator
swi
tht
hehighestprecedenceappearatthetopofthet abl
e,t
hosewi
ththe
l
owestappearatt
hebot
tom.Wi thinanexpressi
on,higherprecedenceoperat
orswi
llbe
eval
uatedfi
rst
.

Oper
ator Oper
ati
on

*
* Exponent
iat
ion

+,
- i
dent
it
y,negat
ion

*
,/ mul
ti
pli
cat
ion,
div
isi
on

addi
tion, subt
ract
ion,
+,
-,|
| concatenat
ion
 =,
<,>,
<=,
>=,
<>,
!=,
~=,
^=,
Compar
ison
I
SNULL,
LIKE,
BETWEEN,
IN

NOT l
ogi
cal
negat
ion

AND Conj
unct
ion

OR I
ncl
usi
on

Exampl
e:
Tr
yt he f
oll
owi
ng exampl
eto under
stand t
he oper
atorpr
ecedence
av
ail
abl
einPL/SQL:
DECLARE
a number
(2):
=20;
b number
(2):
=10;
c number
(2):
=15;
d number
(2):
=5;
e number
(2);
BE
GI
N
e:=(
a+b)*c/d; --(30*15)/5
dbms_out
put
.put
_li
ne(
'Val
ueof(a+b)*c/dis:
'|
|e)
;
e :=(
(a+b)*c)/d; -
-(30*15)/5
dbms_
output
.put
_li
ne(
'Val
ueof((
a+b)*c)/dis :'
|| e)
;
e :=(
a+b)*( c/d) ; -
-(30)*(
15/
5)
dbms_
output
.put
_li
ne(
'Valueof(a+b)*(c/d)i
s :
'|
| e)
;
e :=a+(b*c)/d; --20+(150/
5)
dbms_
output
.put
_li
ne(
'Val
ueofa+(b*c)/dis :'
|| e)
;
EN
D;
/

Whentheabovecodei
sexecut
edatSQLpr
ompt
,itpr
oduces
t
hefol
l
owingresul
t:

Val
ueof(
a+b)*c/di s:90
Val
ueof(
(a+b)*c)/dis :
90
Val
ueof(
a+b)*(c/d)is :
90
Val
ueofa+(b*c)/dis :
50

PL/
SQLpr
ocedur
esuccessf
ull
ycompl
eted.
 8
Condi
ti
ons
Decisi
on-makingstr
uct ur
esrequir
et hattheprogr
ammerspecif
yoneormorecondi
ti
ons
tobeev al
uatedortestedbythepr ogram, al
ongwithastat
ementorst
atement
stobeexecut
edif
theconditi
oni sdeter
mi nedtobet rue,andopti
onal
ly,ot
herstat
ementstobeexecut
edifthe
condit
ionisdeter
minedt obefalse.

Fol
l
owingist
hegener
alfr
om ofat y
picalcondi
ti
onal(
i.
e.,deci
sionmaki
ng)st
ruct
uref
oundi
n
mostoft
heprogr
ammingl
anguages:

PL/SQLpr
ogr
amminglanguagepr
ovi
desf
oll
owi
ngt
ypesofdeci
sion-
maki
ngst
atement
s.Cl
i
ck
thefol
l
owi
ngl
inkst
ocheckthei
rdet
ail
.

St
atement Descr
ipt
ion

The I
F st
atementassoci
ates a condi
ti
on
wit
hasequenceof
st
atement
s encl
osed by t
he key
wor
ds
THENandENDIF.I
f
I
F-THENst
atement theconditi
on i
st r
ue,thest
atement
sget
executedandi
fthe
condit
ion i
sf al
se orNULL then t
he I
F
stat
ementdoesnothing.
 IF statement adds t he keyword ELSE
foll
owedbyan
alt
ernati
ve sequence ofstatement.Ifthe
conditi
onisfal
seor
IF-
THEN-ELSE NULL, thenonlythealt
ernat
ivesequenceof
stat
ement statementsget
executed.Itensur es thateitheroft he
sequenceofst at
ements
i
sexecut ed.

IF-
THEN-ELSI
F Itall
owsy ou t
o choosebet
ween sev
eral
stat
ement alt
ernat
ives.

Li
ket heIFst atement ,theCASEstatement
sel
ectsone
sequence of st atements to execut e.
Howev er,
tosel ectthe
sequence,t he CASE st atement uses a
Casest
atement sel
ectorratherthan
multi
pleBool eanexpr essi
ons.Aselectoris
anexpression
whosev alueisusedt osel
ectoneofsev er
al
al
ternati
ves.

The sear ched CASE st

atement has no
selector
,andit
's
Searched CASE WHEN cl ausescontai
n sear
ch condi
ti
ons
st
atement thatyiel
dBoolean
values.

YoucanuseoneI
F-THENorI
F-THEN-
ELSI
F
st
atement
nest
edI
F-THEN-
ELSE i
nside anotherI
F-THEN orI
F-THEN-
ELSI
F
st
atement(s).

I
F-THENst
atement
Iti
st hesimplestfor
m ofI
Fcontr
olst
atement,fr
equentlyusedindecisionmaking
andchangi ngthecontr
olf
lowoft
heprogram executi
on.
TheI Fstat
ementassociat
esacondit
ionwit
hasequenceofst atement sencl
osed
bythekey wor dsTHENandENDI F.I
fthecondi t
ionisTRUE,thest atementsget
executed,andi fthecondit
ioni
sFALSEorNULL,t hentheI Fstatementdoes
nothing.
Syntax:
Sy
ntaxf
orI
F-THENst
atementi
s:
I
Fcondi
ti
onTHEN
S;
 ENDI
F;

Where condi
ti
on is a Bool
ean orr
elat
ionalcondi
ton and S i
i s a si
mpl
e or
compoundstatement.Exampl
eofanI
F-THENstatementi
s:
I
F(a<=20)THENc:
=c+1;
ENDIF;

I
ftheBool eanexpr on condi
essi t
ion evaluat
est otr
ue,thent heblockofcode
i
nsi
dethei fstat
ementwillbeexecuted.IfBooleanexpr
essionev al
uatestofal
se,
t
henthefir
stsetofcodeaf t
ertheendoft heifstat
ement(aftertheclosi
ngendif)
wi
llbeexecuted.

Fl
owDi
agr
am:

Exampl
e1:
Letust
ryacompl
eteexampl
ethatwoul
dil
l
ust
rat
etheconcept
:
DECLARE
anumber(2):=10;BEGIN
a:=10;
-
-checkthebooleancondi
ti
onusingi
fst
atementIF(a<20)THEN
--i
fconditi
onist r
uethenprintt
hef
oll
owingdbms_ out
put
.put
_li
ne(
'ai
slesst
han
20' )
;
ENDI F;
dbms_output.
put_
li
ne('
val
ueofai
s:'||a)
;END;
/

Whent
heabov
ecodei
sexecut
edatSQLpr
ompt
,itpr
oducest
hef
oll
owi
ngr
esul
t:
 ai
slesst
han20v
alueofai
s:10
PL/
SQLpr
ocedur
esuccessf
ull
ycompl
eted.

Exampl
e2:
Considerwehav eat ableandf ew recordsinthet ableaswehadcr
eat
edi
n
PL/SQLVar i
ableTy pes
DECLARE
c_i
dcustomer s.i
d%type: =1;c_salcust
omer s.
sal
ary
%t ype;
BEGIN
SELECTsalaryI NTOc_ salFROM customersWHEREi d=c_ i
d;
I
F(c_sal<=2000)THENUPDATEcust omers
SETsalary=sal
ar y+1000WHEREi d=c_ i
d;
dbms_output.put
_li
ne( '
Salar
yupdated')
;ENDIF;
END;
/

Whent
heabov
ecodei
sexecut
edatSQLpr
ompt
,itpr
oducest
hef
oll
owi
ngr
esul
t:

Sal
aryupdat
ed

PL/
SQLpr
ocedur
esuccessf
ull
ycompl
eted.

I
F-THEN-
ELSEst
atement
AsequenceofI
F-THENstatement
scanbefoll
owedbyanopti
onalsequenceof
ELSEst
atement
s,whi
chexecutewhent
hecondi
ti
oni
sFALSE.
Synt
ax:
Sy
ntaxf
ort
heI
F-THEN-
ELSEst
atementi
s:
I
Fcondit
ionTHEN
S1;
ELSE
S2;
ENDIF;

Where,S1 and S2 ar
edi f
fer
entsequenceofstat
ements.IntheI F-
THEN-ELSE
st
atements,whenthetestcondi
ti
onisTRUE,t
hest ementS1i
at sexecutedandS2
i
sskipped;whenthetestcondit
ioni
sFALSE,thenS1isbypassedandst at
ement
S2isexecut
ed.Forexample:

I
Fcol
or=redTHEN
dbms_
output
.put
_li
ne(
'Youhavechosenar
edcar
')ELSE
dbms_
output
.put
_li
ne(
'Pl
easechooseacol
orf
oryourcar
')
;ENDI
F;
IftheBool
eanexpr oncondi
essi tioneval
uatest
ot r
ue,t
henthei f-
thenbl
ockof
codewil
lbeexecut
ed,ot
her
wisetheel
seblockofcodewi
llbeexecuted.

Exampl
e:
Letust
ryacompl
eteexampl
ethatwoul
dil
l
ust
rat
etheconcept
:
DECLARE
anumber (3):
=100;BEGIN
-
-checkthebooleancondi
ti
onusingifstat
ement
I
F(a<20)THEN
-
-ifcondi
tionistr
uethenprintt
hef ol
l
owingdbms_output
.put
_li
ne(
'ai
slesst
han
20');
ELSE
dbms_ out
put.
put_
li
ne('
aisnotlessthan20')
;ENDIF;
dbms_ out
put.
put_
li
ne('
val
ueofai s:'|
|a);
END;
/

Whent
heabov
ecodei
sexecut
edatSQLpr
ompt
,itpr
oducest
hef
oll
owi
ngr
esul
t:

ai
snotl
esst
han20v
alueofai
s:100

PL/
SQLpr
ocedur
esuccessf
ull
ycompl
eted.

I
F-THEN-
ELSI
Fst
atement
TheIF-THEN-ELSIFstatementall
owsy outochoosebet weenseveralal
ternati
ves.
AnIF-THEN statementcanbef oll
owedbyanopt ionalELSIF.
..
ELSEst atement.
TheELSIFclauselet
sy ouaddadditi
onalcondit
ions.
Whenusi ngI
F-THEN-ELSIFstatements,t
herearefewpointstokeepinmi nd.
I
t'
sELSI
F,notELSEI
F

AnIF-
THENst
atementcanhav
ezer
ooroneELSE'
sandi
tmustcomeaf
terany
ELSI
F'
s.

AnIF-THENst
atementcanhav
ezer
otomanyELSI
F'
sandt
heymustcomebef
ore
theELSE.
OnceanELSI
Fsucceeds,
noneoft
her
emai
ningELSI
F'
sorELSE'
swi
l
lbet
est
ed.

Sy
ntax:
Thesy
ntaxofanI
F-THEN-
ELSI
FSt
atementi
nPL/
SQLpr
ogr
ammi
ngl
anguagei
s:
I
F(bool
ean_expr
essi
on1)THEN
S1; - - Executes when t he bool
ean expr
essi
on 1 i
s t
rue
ELSI
F(boolean_
expr
essi
on2)THEN
 S2; -- Execut es when t he bool ean expression 2 is t
rue
ELSI
F(bool
ean_expr
essi
on3)THEN
S3;-
-Execut
eswhenthebool
eanexpressi
on3i st
rueELSE
S4;-
-execut
eswhenthenoneoftheabovecondit
ioni
strueENDI
F;

Exampl
e:
DECLARE
anumber(3):=100;BEGIN
I
F(a=10)THEN
dbms_output.
put_
li
ne('
Valueofais10'
);ELSIF(a=20)THEN
dbms_output.
put_
li
ne('
Valueofais20'
);ELSIF(a=30)THEN
dbms_output.
put_
li
ne('
Valueofais30'
);ELSE
dbms_output
.put
_li
ne(
'Noneoftheval
uesismat chi
ng'
);
ENDI
F;
dbms_output.
put_
li
ne('
Exactval
ueofais:'
||
a) ;
END;
/

Whent
heabov
ecodei
sexecut
edatSQLpr
ompt
,itpr
oducest
hef
oll
owi
ngr
esul
t:

Noneoftheval
uesismat
chi
ng
Exactv
alueofais:
100
PL/
SQLpr
ocedur
esuccessf
ull
ycompl
eted.

Casest
atement
:

LiketheIFstat
ement ,t
heCASEst atementsel
ectsonesequenceofst
atementsto
execute.However,toselectthesequence,theCASEst at
ementusesasel ect
or
ratherthanmul t
ipl
eBool eanexpressi
ons.A select
orisanexpressi
on,whose
valueisusedtoselectoneofseveralal
ter
nati
ves.
Sy
ntax:
Thesy
ntaxf
orcasest
atementi
nPL/
SQLi
s:
CASEselector
WHEN' v
alue1'THENS1;WHEN'v
alue2'
THENS2;
WHEN'
val
ue3'
THENS3;
.
..
ELSESn; -
-def
aul
tcaseENDCASE;

Fl
owDi
agr
am:
Exampl
e:
DECLARE
gradechar
(1):
='A'
;

BEGIN
CASEgrade
when ' A' t hen dbms_ output.
put_l
i
ne( '
Excel
lent
')
; when ' B' t hen
dbms_out
put.
put_ l
i
ne('
Ver
y good');when ' C't hen dbms_out
put.
put
_li
ne(
'Wel
l
done'
);
when'D'thendbms_ out
put
.put_l
ine(
'Youpassed');
when '
F' t hen dbms_output.
put_l
ine('
Bett
er t r
y agai
n'
); el se
dbms_out
put.
put_ l
i
ne('
Nosuchgrade')
;
ENDCASE; END;
/

Whent
heabov
ecodei
sexecut
edatSQLpr
ompt
,itpr
oducest
hef
oll
owi
ngr
esul
t:

Excel
l
ent
PL/
SQLpr
ocedur
esuccessf
ull
ycompl
eted.

Sear
chedCASEst
atement
ThesearchedCASEst at
ementhasnosel ect
orandi
tsWHEN cl
ausescont
ain
sear
chcondit
ionst
hatgiv
eBooleanv
alues.
Synt
ax:
Thesy
ntaxf
orsear
chedcasest
atementi
nPL/
SQLi
s:
CASE
WHEN sel ect
or=' val
ue1'THEN S1;WHEN sel
ect
or='
val
ue2'THEN S2;WHEN
selector='
val
ue3'THENS3;
..
.
ELSESn;--def
aultcaseENDCASE;
Fl
owDi
agr
am:
 Exampl
e:
DECLARE
gr
adechar(1):
='B';
BEGI N
case
whengr ade=' A'thendbms_ output
.put
_li
ne('
Excel
lent')
;whengrade='B't
hen
dbms_ out
put
.put_
li
ne('Very good'
); when grade = '
C' t hen
dbms_ out
put
.put_
li
ne('Well done' ); when grade = '
D' t hen
dbms_ out
put
.put_
li
ne('Youpassed'
);
when gr ade = ' F' then dbms_ out
put.put
_li
ne('Bett
er t
ry agai
n'
); el
se
dbms_ out
put
.put_
li
ne('Nosuchgrade'
);
endcase;

END;
/

Whent
heabov
ecodei
sexecut
edatSQLpr
ompt
,itpr
oducest
hef
oll
owi
ngr
esul
t:
Ver
ygood
PL/
SQLpr
ocedur
esuccessf
ull
ycompl
eted.

Nest
edI
F-THEN-
ELSE

Itisal
wayslegali
nPL/SQLprogr
ammingtonestIF-ELSEstat
ements,whi
ch
meansy oucanuseoneIForELSEIFstat
ementi
nsideanotherIForELSEIF
stat
ement(
s).
Sy
ntax:
I
F(boolean_expr
ession1)THEN
-
-executeswhent hebooleanexpression1istr
ueIF(bool
ean_expressi
on2)THEN
-
-executeswhent hebooleanexpression2istr
uesequence-of-
statements;
ENDI F;ELSE
-
-executeswhent hebooleanexpression1isnott
rueelse-
statements;
ENDIF;

Exampl
e:
DECLARE
anumber (3):
=100;
bnumber (3):
=200;
BEGI
N
-
-checkthebooleancondi
ti
onIF(a=100)THEN
-
-ifcondit
ionistr
uethencheckthefol
lowi
ngIF(b=200)THEN
-i fconditi
onistr
uethenpri
ntthefol
l
owing
 dbms_out
put.
put_l
i
ne('Val
ueofai
s100andbi
s200')
;ENDIF;
ENDIF;
dbms_out
put
.put_
li
ne('
Exactval
ueofais:'||a);dbms_out
put.
put
_li
ne(
'Exact
val
ueofbis:'|
|b);
END;
/

Whent
heabov
ecodei
sexecut
edatSQLpr
ompt
,itpr
oducest
hef
oll
owi
ngr
esul
t:
Valueofais100andbis200
Exactval
ueofais:100
Exactval
ueofbis:200

PL/
SQLpr
ocedur
esuccessf
ull
ycompl
eted.

Loops
Theremaybeasi tuati
onwheny ouneedtoexecuteablockofcodesev er
al
numberof
ti
mes.Ingeneral
,stat
ementsareexecut
edsequent
ial
l
y:Thefi
rstst
atementi
na
funct
ioni
sexecutedfi
rst
,fol
l
owedbythesecond,
andsoon.

Progr
amminglanguagesprov
idev
ari
ouscont
rolst
ruct
urest
hatal
l
ow f
ormor
e
compli
cat
edexecuti
onpat
hs.

Aloopstatementall
owsust o execut
east at
ementorgr
oupofst at
ement
s
mul
ti
pleti
mesandf ol
lowingi
sthegeneralf
orm ofal
oopst
atementi
nmostof
t
heprogr
ammi ngl
anguages:

PL/SQLprov
idesthefol
l
owi
ngt y
pesoflooptohandl
ethel
oopi
ngr
equi
rement
s.
Cl
ickthef
oll
owingli
nkst
ocheckthei
rdet
ail
.

LoopTy
pe Descr
ipt
ion

Inthisloopstr
uct
ure,sequenceofst
atement
sis
enclosedbetween
 theLOOPandEND LOOPst at
ements.Ateach
PL/SQL c i
Basi terat
ion,t
he
LOOP sequenceofst at
ementsi
sexecut
edandt hen
contr
olresumesatthe
topoftheloop.

Repeat
s a st
atementorgr
oup ofst
atement
s
PL/SQL WHI
LE unt
ilagi
vencondi
ti
onis
LOOP t
rue.I
ttest
sthecondi
ti
onbef
oreexecut
ingt
he
l
oopbody.

Execut
e a sequence of st
atement
s mul
ti
ple
PL/SQL FOR ti
mesandabbrevi
ates
LOOP
t
hecodet
hatmanagest
hel
oopv
ari
abl
e.

Youcanuseoneormor
eloopi
nsi
deanyanot
her
Nested l
oops i
n basi
cloop,
whi
leor
PL/SQL
f
orl
oop.

PL/
SQLBasi
cLOOP
Basicl
oopstr
uctureenclosessequenceofst at
ementsinbetweentheLOOPand
END LOOP statements.Wi th each i
ter
ati
on,the sequence ofstat
ement
sis
execut
edandthencontrolresumesatthetopofthel
oop.
Synt
ax:
Thesynt
axofabasi
cloopi
nPL/
SQLpr
ogr
ammi
ngl
anguagei
s:
LOOP
Sequenceofst
atement
s;
ENDLOOP;

Here,sequence ofst
atement
(s)may be a si
ngl
e st
atementora block of
stat
ements.AnEXITst
atementoranEXI
TWHENst atementi
srequi
redt
obreak
theloop.

Exampl
e:
DECLARE
xnumber:=10;BEGIN
LOOPdbms_output
.put
_li
ne(
x);
x:=x+10;
IFx>50THENexi t
;
ENDIF;ENDLOOP;
 -
-aft
erexi
t,cont
rol
resumesher
edbms_
out
put
.put
_li
ne(
'Af
terExi
txi
s:'
||x)
;
END;
/

Whent
heabov
ecodei
sexecut
edatSQLpr
ompt
,itpr
oducest
hef
oll
owi
ngr
esul
t:
10
20
30
40
50
Af
terExi
txi
s:60

PL/
SQLpr
ocedur
esuccessf
ull
ycompl
eted.

Youcanuset
heEXI
TWHENst
atementi
nst
eadoft
heEXI
Tst
atement
:
DECLARE
xnumber:=10;BEGIN
LOOPdbms_ output
.put
_li
ne(
x);
x:=x+10;
exi
tWHENx>50; ENDLOOP;
--aft
erexi
t,cont
rolr
esumesheredbms_
out
put
.put
_li
ne(
'Af
terExi
txi
s:'
||x)
;
END;
/

Whent
heabov
ecodei
sexecut
edatSQLpr
ompt
,itpr
oducest
hef
oll
owi
ngr
esul
t:
10
20
30
40
50
Af
terExi
txi
s:60

PL/
SQLpr
ocedur
esuccessf
ull
ycompl
eted.

PL/
SQLWHI
LELOOP
AWHI LELOOPstatementi
nPL/SQLprogr
amminglanguager
epeat
edl
yexecut
es
at
argetstat
ementaslongasagi
vencondi
ti
oni
strue.
Sy
ntax:
WHILEcondi
ti
onLOOPsequence_
of_
stat
ement
s
ENDLOOP;

Exampl
e:
DECLARE
 anumber
(2):
=10;
BEGIN
WHI
LEa<20LOOPdbms_ out
put
.put
_li
ne(
'v
alueofa:
'||
a);
a:=a+1;
ENDLOOP;
END;
/

Whent
heabov
ecodei
sexecut
edatSQLpr
ompt
,itpr
oducest
hef
oll
owi
ngr
esul
t:
v
alueofa:
10

val
ueofa:11val
ueofa:12val
ueofa:13val
ueofa:14v
alueofa:15v
alueofa:
16val
ueofa:17val
ueofa:18v
alueofa:
19
PL/
SQLpr
ocedur
esuccessf
ull
ycompl
eted.

PL/
SQLFORLOOP

AFORLOOPi sarepet
it
ioncont
rolst
ruct
urethatal
l
owsy
out
oef
fi
cient
lywr
it
ea
l
oopt
hatneedst
oexecuteaspecif
icnumberofti
mes.

Sy
ntax:
FORcount
erI
Nini
ti
al_
val
ue.
.fi
nal
_val
ueLOOPsequence_
of_
stat
ement
s;
ENDLOOP;

Her
eist
hef
lowofcont
rol
inaf
orl
oop:

Thei
nit
ialstepisexecutedfi
rst
,andonlyonce.Thi
sst
epal
l
owsy
out
odecl
are
andi
nit
ial
izeanyloopcontr
olvar
iabl
es.

Next,thecondi
ti
on,i.
e.,i
nit
ial_v
alue..fi
nal
_val
ueisev al
uated.I
fiti
sTRUE,t he
bodyoftheloopisexecut
ed.I fi
tisFALSE,t
hebodyoft heloopdoesnotexecute
andflowofcontr
olj
umpst ot henextstat
ementjustaf
tertheforl
oop.

Aft
ert hebodyoft heforl
oopexecut
es,t
hev
al he count
ueoft erv
ari
abl
eis
i
ncreasedordecr
eased.

Theconditi
onisnow ev al
uatedagai
n.Ifiti
sTRUE,theloopexecut
esandt he
process r
epeatsitsel
f(body ofloop,then i
ncr
ementstep,and then agai
n
condit
ion)
.Aft
erthecondit
ionbecomesFALSE,t
heFOR-LOOPter
minat
es.

Fol
l
owi
ngar
esomespeci
alchar
act
eri
sti
csofPL/
SQLf
orl
oop:

Thei ni
ti
al_
val
ueandf i
nal
_val
ueoftheloopvar
i eorcount
abl ercanbeli
ter
als,
vari
ables,
orexpressi
onsbutmusteval
uatet
onumbers.Ot
her
wise,PL/
SQLrai
ses
thepredefi
nedexcepti
onVALUE_ERROR.

The i
nit
ial
_val
ue need nott
o be1;howev
er,t
hel
oop count
eri
ncr
ement(
or
decr
ement)mustbe1.
PL/
SQLal
l
owsdet
ermi
net
hel
oopr
angedy
nami
cal
l
yatr
unt
ime.

Exampl
e:
 DECLARE
anumber
(2);
BEGI
N
FOR ain10..20LOOPdbms_
out
put
.put
_li
ne(
'v
alueofa:
'||
a);
ENDLOOP;
END;
/

Whent
heabov
ecodei
sexecut
edatSQLpr
ompt
,itpr
oducest
hef
oll
owi
ngr
esul
t:

val
ueofa:10val
ueofa:11val
ueofa:12val
ueofa:13val
ueofa:14val
ueofa:
15val
ueofa:16val
ueofa:17v
alueofa:
18valueofa:
19val
ueofa:20

PL/
SQLpr
ocedur
esuccessf
ull
ycompl
eted.

Rev
erseFORLOOPSt
atement
Bydefault,iter
ationproceedsfrom t
hei ni
ti
alvaluetot hefi
nalvalue,gener
all
y
upwar
df rom t helowerboundt othehigherbound.Youcanr ever
set hi
sorderby
usi
ngtheREVERSEkey word.Insuchcase,it
erat
ionproceedstheotherway.Aft
er
eachi
teration,theloopcounteri
sdecremented.

However,y
oumustwr
it
et herangeboundsi
nascendi
ng(
notdescendi
ng)or
der
.
Thef
ollowi
ngpr
ogr
am i
ll
ustr
atesthi
s:
DECLARE
anumber(
2); BEGIN
FORaINREVERSE10. .20LOOPdbms_
out
put
.put
_li
ne(
'v
alueofa:
'||
a);
ENDLOOP; END;
/

Whent
heabov
ecodei
sexecut
edatSQLpr
ompt
,itpr
oducest
hef
oll
owi
ngr
esul
t:

val
ueofa:20val
ueofa:19val
ueofa:18val
ueofa:17val
ueofa:16val
ueofa:
15val
ueofa:14val
ueofa:13v
alueofa:
12valueofa:
11val
ueofa:10

PL/
SQLpr
ocedur
esuccessf
ull
ycompl
eted.

Nest
edl
oopsi
nPL/
SQL

PL/SQLal
l
owsusingoneloopinsi
deanot
herl
oop.Fol
l
owi
ngsect
ionshowsf
ew
exampl
estoi
l
lust
rat
etheconcept
.

Thesy
ntaxf
oranest
edbasi
cLOOPst
atementi
nPL/
SQLi
sasf
oll
ows:
LOOP
Sequenceofst
atement
s1
LOOP
Sequenceofst
atement
s2
ENDLOOP;
 ENDLOOP;

Thesy
ntaxf
oranest
edFORLOOPst
atementi
nPL/
SQLi
sasf
oll
ows:

FORcount
er1I
Nini
ti
al_
val
ue1.
.fi
nal
_val
ue1LOOPsequence_
of_
stat
ement
s1
FORcount
er2I
Nini
ti
al_
val
ue2.
.fi
nal
_val
ue2LOOPsequence_
of_
stat
ement
s2
ENDLOOP;ENDLOOP;

Thesy
ntaxf
oranest
edWHI
LELOOPst
atementi
nPascal
isasf
oll
ows:

WHILEcondi
tion1LOOPsequence_
of_
stat
ement
s1WHI
LEcondi
ti
on2LOOP
sequence_of
_st
atement
s2ENDLOOP;
ENDLOOP;

Exampl
e:
Thefol
lowi
ngpr
ogr
am usesanest
edbasi
cloopt
ofi
ndt
hepr
imenumber
sfr
om 2
to100:
DECLARE
inumber(3);
jnumber(3);
BEGIN
i :=2; LOOP
j
:=2;LOOP
exi
tWHEN( (mod(i,
j)=0)or(j=i
))
;j:
=j+1;
ENDLOOP;
IF(
j=i )THEN
dbms_ output
.put_l
i
ne(i
||'
ispri
me'
);ENDIF;
i:
=i +1;
exi
tWHENi =50; ENDLOOP;
END;
/

Whent
heabov
ecodei
sexecut
edatSQLpr
ompt
,itpr
oducest
hef
oll
owi
ngr
esul
t:

2ispri
me
3ispri
me
5ispri
me
7ispri
me
11ispri
me
13ispri
me
17ispri
me
19ispri
me
23ispri
me
29ispri
me
31ispri
me
37ispri
me
41ispri
me
43ispri
me
47i
spr
ime
PL/
SQLpr
ocedur
esuccessf
ull
ycompl
eted.

TheLoopCont
rol
Stat
ement
s

Loop contr
olstat
ement
schangeexecut
ion f
rom i
tsnormalsequence.When
execut
ionleav
esascope,al
laut
omati
cobject
sthatwer
ecreat
edint hatscope
aredestr
oyed.

PL/SQLsuppor
tsthefol
lowingcontr
olstatements.Label
i
ngloopsal
sohelpsi
n
taki
ngt
hecontr
olout
sidealoop.Cl
ickthefoll
owingli
nkstocheckt
hei
rdet
ail
.

Contr
ol
St
atement Descr
ipt
ion

The Exitstatementcompl
etes t
he l
oop and
cont
rolpassestothe
EXI
Tst
atement
st
atementi
mmedi
atel
yaf
terENDLOOP

Causest
helooptoski
pther
emai
nderofi
tsbody
CONTINUE andimmedi
atel
y
st
atement
r
etesti
tscondi
ti
onpr
iort
orei
ter
ati
ng.

Transf
ers cont
rol t
o t
he l
abel
ed st
atement
.
GOTO Thoughiti
snotadvi
sedt
o
st
atement
useGOTOst
atementi
nyourpr
ogr
am.

EXI
Tst
atement

TheEXITstat
ementinPL/SQLprogrammingl
anguagehasf ol
lowi
ngtwousages:
Whent heEXITst
atementisencounter
edinsi
deal oop,t
hel oopisimmediat
ely
ter
minatedandprogram cont
rolresumesatthenextst atementfol
lowi
ngthe
l
oop.

Ifyou are usi

ng nest
ed loops (
i.
e.one loop i
nsi
de anot
herl
oop)
,the EXIT
stat
ementwi l
lstoptheexecuti
onoftheinnermostl
oopandstar
texecut
ingthe
nextli
neofcodeaftert
heblock.
Sy
ntax:
Thesy
ntaxf
oranEXI
Tst
atementi
nPL/
SQLi
sasf
oll
ows:
EXI
T;
Fl
owDi
agr
am:

Exampl
e:
DECLARE
anumber(2):
=10; BEGI
N
-
-whil
eloopexecuti
onWHI LEa<20LOOP
dbms_out
put.
put_l
i
ne( '
val
ueofa:'|
|a)
;a:
=a+1;
I
Fa>15THEN
-
-ter
minatet
heloopusingtheexi
tstat
ementEXI
T;
ENDIF;ENDLOOP;
END;
/

Whent
heabov
ecodei
sexecut
edatSQLpr
ompt
,itpr
oducest
hef
oll
owi
ngr
esul
t:

val
ueofa:10v
alueofa:11v
alueofa:12v
alueofa:13v
alueofa:14v
alueofa:
15
PL/
SQLpr
ocedur
esuccessf
ull
ycompl
eted.

TheEXI
TWHENSt
atement

The EXIT-WHEN st
atementallows t
he condi
ti
on i
nthe WHEN clause t
o be
eval
uated.Ift
hecondit
ionist
rue,t
heloopcomplet
esandcont
rolpassestothe
st
atementimmediat
elyaft
erENDLOOP.
Fol
l
owi
ngar
etwoi
mpor
tantaspect
sfort
heEXI
TWHENst
atement
:

Unti
lthecondi
ti
onist
rue,t
heEXIT-
WHENst at
ementact
sli
keaNULLst
atement
,
exceptf
oreval
uati
ngt
hecondi
ti
on,anddoesnott
ermi
natet
hel
oop.

Ast
atementi
nsi
det
hel
oopmustchanget
hev
alueoft
hecondi
ti
on.

Sy
ntax:
Thesy
ntaxf
oranEXI
TWHENst
atementi
nPL/
SQLi
sasf
oll
ows:
EXI
TWHENcondi
ti
on;

TheEXI
TWHENst
atementr
epl
acesacondi
ti
onalst
atementl
i
kei
f-
thenusedwi
th
t
heEXI
Tst
atement
.
Exampl
e:
DECLARE
anumber(2):
=10;BEGI
N
-
-whil
eloopexecut
ionWHI
LEa<20LOOP
dbms_out
put
.put_l
i
ne( '
val
ueofa:
'||
a);
a:=a+1;
--t
ermi
nat
et heloopusi
ngtheexi
twhenst
atementEXI
TWHENa>15;
ENDLOOP; END;
/

Whent
heabov
ecodei
sexecut
edatSQLpr
ompt
,itpr
oducest
hef
oll
owi
ngr
esul
t:

val
ueofa:10v
alueofa:11v
alueofa:12v
alueofa:13v
alueofa:14v
alueofa:
15

PL/
SQLpr
ocedur
esuccessf
ull
ycompl
eted.

CONTI
NUEst
atement

TheCONTI NUEst at
ementcausestheloopt oskipther
emainderofi
tsbodyand
i
mmedi atelyr
etesti
tscondi
ti
onpr i
ortor ei
ter
ati
ng.Inot
herwords,i
tfor
cesthe
nexti
terati
onoftheloopt
otakeplace,
skippinganycodei
nbetween.

Sy
ntax:
Thesy
ntaxf
oraCONTI
NUEst
atementi
sasf
oll
ows:
CONTI
NUE;

Fl
owDi
agr
am:
Exampl
e:
DECLARE
anumber(2):
=10; BEGI
N
-
-whil
eloopexecuti
onWHI LEa<20LOOP
dbms_out
put.
put_l
ine('
val
ueofa:'
||a)
;a:=a+1;
I
Fa=15THEN
-
-ski
ptheloopusingtheCONTINUEstatementa:
=a+1;
CONTINUE;ENDI F;
ENDLOOP; END;
/

Whent
heabov
ecodei
sexecut
edatSQLpr
ompt
,itpr
oducest
hef
oll
owi
ngr
esul
t:

val
ueofa:10val
ueofa:11val
ueofa:12val
ueofa:13v
alueofa:14v
alueofa:
16val
ueofa:17val
ueofa:18v
alueofa:
19

PL/
SQLpr
ocedur
esuccessf
ull
ycompl
eted.

GOTOst
atement

AGOTOstatementi
nPL/
SQLprogramminglanguagepr
ovidesanuncondi
ti
onal
j
umpfr
om theGOTOtoal
abel
edstat
ementinthesamesubprogr
am.

NOTE:Use ofGOTO st atementi s hi

ghly di
scouraged i
n any pr
ogramming
l
anguagebecauseitmakesdiffi
cultt
otracethecontr
ol f
lowofaprogr
am, maki
ng
t
hepr ogr
am hardtounder st
andandhar dtomodi f
y.Anypr ogr
am thatusesa
GOTOcanber ewri
ttensothatitdoesn'
tneedtheGOTO.
Sy
ntax:
Thesy
ntaxf
oraGOTOst
atementi
nPL/
SQLi
sasf
oll
ows:
GOTOl abel
;
..
..
<<label>>st
atement
;

Fl
owDi
agr
am:
 Exampl
e:
DECLARE
anumber (
2):=10; BEGIN
<<loopst
art>>
--whil
eloopexecutionWHI LEa<20LOOP
dbms_ out
put.put_
li
ne( '
valueofa:'|
|a);
a:=a+1;
I
Fa=15THENa: =a+1;
GOTOl oopstar
t;ENDI F;
ENDLOOP; END;
/
Whent heabov ecodei sexecutedatSQLprompt
,itpr
oducest
hef
oll
owi
ngr
esul
t:

val
ueofa:10val
ueofa:11val
ueofa:12val
ueofa:13v
alueofa:14v
alueofa:
16val
ueofa:17val
ueofa:18v
alueofa:
19
PL/SQLpr oceduresuccessf
ull
ycompl
eted.
Restr
ict
ionswi t
hGOTOSt at
ement
GOTOSt
atementi
nPL/
SQLi
mposest
hef
oll
owi
ngr
est
ri
cti
ons:

AGOTOst atementcannotbr
anchi
ntoanI
Fst
atement
,CASEst
atement
,LOOP
st
atementorsub-
block.

AGOTO statementcannotbr
anchfrom oneI
Fstat
ementcl
auset
oanot
heror
f
rom oneCASEstat
ementWHENcl auset
oanot
her
.

AGOTOstatementcannotbr
anchf
rom anout
erbl
ocki
ntoasub-
block(
thati
s,an
i
nnerBEGI
N-ENDblock)
.

AGOTO st at
ementcannotbr
anchoutofasubpr
ogr
am.Toendasubprogr
am
ear
ly,ei
theruset
heRETURN stat
ementorhaveGOTO br
ancht
oaplaceri
ght
bef
oretheendofthesubpr
ogr
am.

AGOTOst at
ementcannotbranchf
rom anexcept
ionhandl
erbackint
othecurr
ent
BEGIN-
END block.However,aGOTO stat
ementcanbr anchfrom anexcept
ion
handl
eri
ntoanenclosi
ngblock.