hoeadual Longuage Suelined uuy LLarguage

PL/sqL Con btnalton ale

Aedunnl paona mmung

PLIsf ua kisch muanung

PLIs AuhA ms dt'olrd and wm'Utn
Lege'al Mocks code

6LOCKK 3 seelions

Decdanaions Ee cul
Commands Excapuon
lenal (ma ndalo ry) Hanodun
ndosed
opTioned
KaweAls w
DECLARE
haniakun
BEGIN and Karwe
subp END Ex CEPTIO
RAmy
mtany *uplin
re laLA
hat hundl
PLJsL Stalimtnls
AOAms
AOa m

[Link] otteasl-

os, ma
..
a VL
Cmmand inol'eal
sheldbe
nattung
 nlh a e n cato
E y PLSL Stemint ends

Of A PLSQL BLOCk
ASIC STRUCTURE

Stucti

DECLARE
ouclanatuni Cectun
BEGIN
Kexecutabe mma nds)
vaicharl(2o) DEfmnT
DEFAULT
ExCEPTION
or oleulk kaypsan
ttytusi hardlirg > ichaur H a
( t o )

&4oody

END
nay rullo

DECLARE koneld
Hl
musaas
vtha2 (2.:
9 ssigmment ofsualm
BEGIN
un (mesage)
tiat olbyns -Buwhut. puk. va
END
USER DEPINED SUBTY PES
PL/SQL anohu daalypa
Subljpe2 subu
Called

bauy F)

PL/s ltui various ublyps

athage STANDARD Q2lbtyle))
a m p l

r u b p a Bato
SyeTYPE CHARA CTER S CHAR

SvBTYPE INTEGER Is NUMBER (38, 0).

To DEPINE DwN SUBTYfEES.
EXAMPLE
Subyrs
DECLARE
SUBTYPE
Mam IS char (20
IS aruharl (100)
SuBTYPE mesasa

b a n
y r s

Salukatm ami
musaL

BEGIN
SalulalLon := Radu'
L/s9
Aings: walm to hu weuld
kalubithos 1
ne Hello
lms- ukput. put.
autng) CPncabenalon

END onalar
 SUBPROG RAM
SubpADam u a rigram hat oms o paiviculay
ask
Thut ubpora ms a Cmbinud6. om longi
u Celtd amodulay olun'.
a w .TKis
anolhiv
ubnaam Com be tnuohed

PL/sQL Subproams

Paoudu
unelLons

t h u ubproams a
heliwn a do-ne
au
nalu dosetty
wed Mauy
anly
Cnmyule r mluun alion
uau.

ASukpugam an be usali

nsre o ahage m a PLsgL

t ltd a blok
acke Subpaoam
Radalow beiam.
slo n
Jaiabasu l can
Dmh CREATE
PRoEDURE oY

hn
CREATE PUNCTION
& oklld
Stalu nuw
a
u lovad un DROP PA CkA 6£ Ltalemu!.

Edatobon eon
dili wnth DROP

PRoCEPURE

PROPFUNCTIO
 AoLdul -nan

nlay
CREATING A PROCERURE
an txisty Jocas
Qllews modyying
non
fkocEDUREoudua.
CREATE [o REPLACEJ
namt CN oUTI 0UTJ p, 7 c
(pua mul J
ohonal

BEGqIN
K Aouduu body
nàm
ENQ bADeduu.

xanp
CREATE PROCEAURE m g
. AS
BEGIN [ Hllo wald")
dbms buhput. put-
END
_ocduut
Exeun
BEGIN 7
END,
lulng _oduus
.

BAO? ?ROCERURE 4g

6EhN
t DAOP PRDLEQURE ak
ENO
 PROGRAMS
PARA METER
MOR ES IN PL/SL

OUT IN OUTT

IN
t d u s ualus to
olu t
Collns
t ( h e auisarameler alue
pdnl!

hut b i nauabli t Callu

mad- oml Arclei jaamuliv
aluu
dad

fnanuelirs

OUT MOR E
OF IN A two nalues ouolunu
EXAMPE mod lund
i n u m u m

ds IN
a m

taku 2 umbas
Unn OUT þanamlurs

un'un m

DECLARE
numben

bnumba ; N
IN
nmbes
numbe
numbea, y
(x N IS
AndMen nummbea)
PROCEQURE 2 bUT .

8EGIN THEN
If

ELSE

END IF
END
/
 8FGIN
a23
b:45
ridrin a, b, t)
olbs. uhuk. Aut. Lns ( "Mui of (:3, 45);: '|l e).2

END
9uhpu
Huntmu (23, 4):13
ExAMPLE 2

DECLARE
a. umb
Ctaltt
PRoceERURE sguaneNum ( 0UT number) (s
6EGIN

FNO.
SBEGN
a 23
Squa Numla,
[Link]. uis ('Sgpau c23): '|la)

Iqunut (23): 5>-7

 PUNTIONNS
unetin am aß aa [Link] tRupk thak
t ln a alu

SyNTAY
CREATE OR REPLACE] FoweTtON euneturn- name
aamutir namu [IM|oUT | w our7 typ l : D

RETURN tndakalyjp
irslAS
6EG H
netunm- boely
END Ceunetim - nami
EXAMPLE dug 4callng a hmduni
kuins ta tetal numbt npya
puncti
e
poy

ceatoafnelen
CREATE PUNCTIQN ttal Bmp /
RETURNnumbu IS
tota numbea()i=0., .

BEGIN tnt totsl PROM

ROM emp
SEECT Count
RETURN t u
ENO
 /Call auneluh
&ECLARE
C umberl)

BEGIN
C'totalbrpl)
enyluryu "ll ct
þut- lini ["Total
no.
dbns-put.

ENR

otal o%*gluys:
 CURSORS

memey Rnorwn S

ntixt xocexvmg an SQ2 ghaltmunl

niadsd
onkat's
all t nalton
which
tht Stalmuus u mbtr
o ucehg
Cesed
eke .

PAO
a polni o h i ontut area
eunsor
he Conlent area through
PLIS L Cmtols
t

CLoyY.

holols u owS (ons or mu).

A eutoY
gtatinns
rulunnnd y a sL
YOS Bhe cusser hotds elerrzd
a dhue 8
to as
o ha could be
An nam ekeh ana

onnd .
nd
proes tme.
a a
Staimen, bnt .

of CURSORSS
TYPES 7

IMPLIeuT CURSORS
EXPLICI1T CURSORS
IMPLICT CUR$ORS

Otate wheusaur a
Arutomaliially
ial by
sttumnt u x eu
Sq Conliel put tursors qnd
no
Cannet
maton i t
UPDA TE
hetur a DML s tatenmen wS¬RT, an ImpltUF cursoy

s issud
Qnd DELE TE) wh h stattmunt
as&dela hotd ak e dals
dal hha
t asl
tunsoY hotd
for INSERT e
hds to be tmsulis iduntfu
1denlfu
h e
he.
&vDr
C
DATE 4 DELETE
UP wpuld e offeetd.
heus Jat
Curso
SQL un sov
M o s mun mpliu
athubul a

T fellowtn
TRUE Aa n INSERT
INSERT
-
hulians
PoUN Sab munt
OY DELE TE
VPDATE
the dali u hesent
( lduuhio
nod SELECT tNTostatumen Alinn
aelinns

yeus
me
etuans FALSE
0eswn's,

7 FouNND
2) NOTFOUND
an INsERT , UPDATE
TRUE
l i n n s

temun
or DEuTE Salimsa
Arlened
SELECT INTO

nOY0WS
heluns FASE
 hetuuns FALSE for Implt'ut
ISOPOf EN was hi carsoy
CLNYOYY
clnoes
uinsers brtause Oatle aSgoualed
exeun9
Aurematuealy ali
sQL Sattnmes

RowcoUNT Kelans umbtr wS

4)
INSERT, UPDATE DELETE
SEECT INTO
Strlimn or u l n e s a

Stalinmens

sQL Cusor att subrid wrl ke accessed

as S97 aibulu namu

EXAM PLE

s scluut * Rom m,
dalr
table and
Ths hoann
salas y tath Cuetonnil by 500
St ROWtoVNTatubu
amd Use

lleamn? h mimber .
*

DECLARE
total ou umber(2)
BEGIN
UPDATE emp ET salary Salasy +5oo
IA S4%notoundy THEN
selachd).
dbis- eupuk. put. Lsl'no edtoshers
GLSIF ELSGIF s47,fnd THEN
total-wws 9 o w o u n Lmploy
dbms - eihut. put. uns (totalons i Cu ).
seleetis)
END IF
END
EXPLICIT CURSDRS

u dspned
Cmtex
Cnhot ou
qaunng
heuld tlud
okcelaralon eton
th DL/sqL stock.
statt mens wsth
SELE CT
u eacali
ont how
mel han
hahuans
Cunsor u
SYNTA for aling cxplueak
S alece- skatimunt
CuRSOR CUAsornam
uvsdus
cuwnsdy
an tzkliut
Weatir mh

stips
Relaung te u r sor
for tnlialur
aloalng mmimey
mey
he wsr foY
dali
wsor for mtweveng
altocali
allocali
3 erhn cwior
t ulase
4) Closu

THE CwRS DR
DECLARING he Cta

e usor olfnis
asoelala sELECT
Dalaung and he
nami

r namu
ttmunk
addsuss
SEECT mptd, ename,
CuRSOR(CLmp) IS

FROM wP
 THE wRSOR
OPENIN

DPEN C- tmp,

THE CURSOR
FETCH ING
c-add
C-ep
NTO Ctd C-na mu
FETCH

CLOSIN THE CURSDR

CLOSE C Uanabla
e adnd
mia ha
ExAMPE h a b t nami
J , n
bEtARE asbha babtu

/Cd tmp epA,y tablu

oumnnan
bECLARE
-

4-Mäm mp. enaYy

/ Sup1 Dackaur9
CURSORCemp S r s o v

SELECT - empno, Unanm, ob PROM e , for

tnh7
BEGIN
OPEN i p 2 :opaug cursor
LOoP:
PETCH C- emp i C-a, cnamu, c-os / a
c u r s o

dsms [Link]-int (e-td 1 *lc-na me l

Ex hwHEN Cemp% netjound

END Loo uurtoy
CLOSE C- tmp, aph: ctosn thi
END
 TRIGGERS
stod bspgoms hich ae
uagns a

auomatiealy tytub Pid shin Som

thant bu.
Tauags can be olpnd on a b , Vw,shima
erdatabosu wlh wwwen us en b asoudalGA,

SYNTAX FoR CREA TIN G TAIG4ERS

an iWstng tuyg
Oa Y laus
t a námu
CREATE DR AEPLALE TRLGGER teg-na ma
i8EFORE | AFTER| INSTEAD OF . t e oFJ
uwd t
2INSERT CORJ | UPDATE COR bELETE ali

DML epenatioms
Of cod nam cdumnam ha woud sm te
pdalc

ON Ta6 Le nam name tablk assouos

LRGPERENCUNG0LD AS D NEN AS n3
n.

FOR ECH RowT wausfor vaudouw DML

WHEN (umdiiin) quilo L mins

a nonl h e y ie h u wouud
DECLARE
enddhond daratuon -ghalimnth

BeGIN
xeata
-
Nahm ns
n a 39L
ExcEPTIOoN Baumin
kekd, shieh is
Staliunb
Exupon -handlung
-

ENP
EXAMPLE 2
Selsc k hom Lm
a Aow en hu'g Po
wat
AoAn
Cmployu kas ho
would f for
or UPDA TE
DEETE S p u a l i o n s rormud
NSERT
This u w di splay
dsplay
bt
tn
n
4mploy
mploy
the old valus
Salay ofounee w
hew valuits

CREATE TRIER dasplay salaay changea -

BEFORE DELETE OR INSERT OR PDATE oN

mp.02
OR. ECH ROW

wHEN LNGW, mpno >o)

DECLARE
Sal -dt numbn,.
bEhiN
Sal-di, NEN,. gal - . :OLD. Sal
dbms -Bwput putne [ 0d salay:| ol). sal)-
Jms-ouhput. put- ne ("laus Salay : "I|:NEw, sal)
Jrnsutpuk. put. Line ("Salay dffenene : '.I| [Link])ys
END

nsu a new Mord tabt

ntloyas
inse t mp valnss34, dman, 'Managpr', 1i
I-od -81no0, ,2o
 Ms Saloy 1200,
Salony unu

Salany lank becaum tt nt auallast

o ur a nw Mlt ming
null

upelatur aw
xing Mcord.
pdaz Lmp Se Sal a
sal +So whuu

Unpro 1234

odsalauy z oo

Alw Salany I7 oo
SO
Salay ou'un

To DELETE A TklGGER
TAt3anNam2
PRoP TRIGER

ndi'enlo ha y ahd
AFTER
isguAg Staumink
e x euhng

BEPORE gndtealb ttat

Axe'g i stalimens
 SYNTAX
tumu n an Cxiutung abl

ln lable Ttabtnamu) add (coummnamu ) Kdataliyp),

aCelun xistung tasle

AL tab (ablemam) dop Cotumn Kstumnnamu?

CONS TRA INTS

imay ky
2)No Mull

3 thutk

anl
eren key

Add/ Daep ame

A i 1ab Ktallna miy add conshaunl- (myonsttwnty

aimaay0 kuy (cotumnama )

Ai tab <latlenama) drep may
Aad /0ot Net Null conaint
Alu Taba talename)mody tumna me )
datalype > net null

At tab ble nanu) mod1 colu mnnaml

dokatype null
du/Dep chech emskain
Alu Table <takenäme add Consba nl
Keolunnn nama/ twnahaintnamt > cheek
Ctondstion);
 table taki namu) dup ouhtaun KColumnnama/
A
tonsbiaunnamu y

Rafeutt add daop .=

ablename) modtY AuounMnnanau)

Kulunmnna nnl
tble
Ali
datalyps dtault ('E),
Condillm

modi!
sumnna mt
Ktabluna nu)
Alli tabH
Kdahalpe depalt (null)
table
7 pdat
a elumnnami! = Kip4),
fablname ) &el
hsher
condstiim >,
cotumn.namu2 ) <Exp7
 INQEXES .

SELECT) Sklenmus u tnd b earh for

hen a
AALOAD!
H
a p a r d lu l a

i s t loals6a).
Mana k
Mads
Daell

kpamaen
Slct oealubn
nd. om
tue
alable's
NeBd
sloaac
eas
a efu
pfoumn
olefred
V alennd
hakrh
natrh ustr
Modh to
to localr SE SELECT
uuhdt
Chliua ceLAs SELaly

l e s r o l y

sel&(sease

a
t
A pheuu hi
npho

usealal
t
a
pnoderue &uiuwdom the lte.
wh wttch

Conlinls

l o a l k

o n d u n d _ u t

cstumn
hnday an
b
c o l u m
o n
m n

a a
 Jnoixn9
nwu m 2p Matuy
* unskpendt
Cuals

t ndr be
wls

mau'
ou ma
2D onla

kom tabl
CBlumn
xauts

CLal
a dohues
Callud

Loal
hao u t r eoun

dalabare
(hi h (abe

odals nsuls r a i l
the
Jouhu
whn auonmaliallyy
Lngn
tue
'Aa' u o l u

v a l u l

dala
lou u t a t dala
dala i n d i c a l i s

Kos 18
Mcond
unngpn

L n e r l s

whers tabe

.
eyatly
Ostanid

N&EX unpu02
Pon
nD

Salra
 VIEWS
eLald
a tabu
lur
a l
all
dals nwens
to
alleumns
nwssary a last
_ b
b t om
may

anMS s
dala lale.
Lenal
nal

Lalug
Mian

would apprefprua numbe Csumas

oumns
t h i o

haung tne Lacl

a 7
Augn e m i n l ,

F l u n l y

uo anowt olala
olala

to
wu
ul heuy
y
l l
Asdtundan dals
dalabase.
ek
tne
la