Pl SQL k Online Material
-
Upload
salai-sukumar -
Category
Documents
-
view
220 -
download
0
Transcript of Pl SQL k Online Material
7/25/2019 Pl SQL k Online Material
http://slidepdf.com/reader/full/pl-sql-k-online-material 1/98
•
• VIDEOS
• MATERIALS
• FAQ'S
• FORUM
•
Contact Us:
Landline: 040 – 65530333,
Mobile : 91 – 8143-900-333,Email : [email protected]
1. Mateials
!. "L#$%L
7/25/2019 Pl SQL k Online Material
http://slidepdf.com/reader/full/pl-sql-k-online-material 2/98
PL/SQLPL/SQL
• &t is a 'o(ammin( lan()a(e *+ic+ is )sed to define o) o*n lo(ics.
• &t is )sed eec)te block of statements at a time and incease t+e 'efomance.
• &t s)''ots aiables and conditional statements and loo's.
• &t s)''ots obect oiented 'o(ammin( and s)''ots com'osite data t/'es.
• &t s)''ots +andle t+e eo +andlin( mec+anism.
7/25/2019 Pl SQL k Online Material
http://slidepdf.com/reader/full/pl-sql-k-online-material 3/98
• Block
o &t is one of t+e aea *+ic+ is )sed to *ite a 'o(ammin( lo(ic.
o +is block is +ae 3 sections.
eclaation $ection
2ec)table $ection
2ce'tion $ection
o eclaation $ection
&t is one of t+e section *+ic+ is )sed declae aiables, c)sos and
ece'tions and so on.
&t is o'tional section.
o 2ec)table $ection
&t is one of t+e section *+ic+ is )sed to *ite a 'o(am codin(.
&t is mandato/ section.
o 2ce'tion $ection
&t is one of t+e section *+ic+ is )sed to +andle t+e eos at )ntime.
&t is o'tional section.
o +ee ae t*o t/'es of blocks ae s)''oted b/ 'l#sl.
non/mo)s lock
amed lock
Anonmo!s Block
+ese blocks does not +ae a name and also not stoed in database.
Example :
Declare
7/25/2019 Pl SQL k Online Material
http://slidepdf.com/reader/full/pl-sql-k-online-material 4/98
-------------
Begin
-------------
-------------
End;
Example 1:
Begin
Dbms_Output.Put_Line(‘elc!me t! E Business S!luti!ns" #;
End;
"am#$ Block
+ese blocks ae +ain( a name and also stoed in database.
E$amples % Pr!cedures & 'uncti!ns& Pacages and )riggers etc..
Va%ia&l#
&t is one of t+ememo/ location *+ic+ is )sed to
stoe t+e data.
7eneall/ *e aedeclae t+e aiables in declaation
section.
+ese ae s)''oteddefa)lt and not n)ll.Syntax :*ariable_+ame Datat,pe ( Sie #;
Example :
Declare
+umber ( #;
B +umber ( # n!t null %012;
7/25/2019 Pl SQL k Online Material
http://slidepdf.com/reader/full/pl-sql-k-online-material 5/98
3 +umber ( # de4ault 12;
Example 1:
Declare
*arc5ar6(62#;
Begin
%0 ‘7ell! EBS";
Dbms_Output.Put_Line( #;
End;
Sto%in a (al!# into (a%ia&l#
sin( assi(nment o'eato : ; *e stoin( a al)e
into aiable. Syntax :*ariable_+ame %0 8alue;
Example : a %02;
Dis)la M#ssa# * o% + Va%ai&l# Val!#
<e +ae one 'e defined 'acka(e *+ic+ is )seddis'la/ t+e messa(e o al)e in a 'o(am.Syntax : dbms_!utput.put_line ( ‘message" #;dbms_!utput.put_line ( 8ariable_name #;
S#l#ct ,,,,,, Into ,,,,,, Cla!s#
+is cla)se is )sed to etiee t+e data fom table =
stoin( into 'l#sl aiables.Syntax : select c!l1& c!l6 int! 8ar1& 8ar6;
o
DataT)#s
1. > /'e
!. > ?o*/'e
3. ?ecod/'e o ; "l#sl ?ecod
7/25/2019 Pl SQL k Online Material
http://slidepdf.com/reader/full/pl-sql-k-online-material 6/98
4. &nde/ able o ; "l#sl able
-T)#:
&t is one of t+edatat/'e *+ic+ is )sed to assi(n t+e col)mn datat/'e
to a aiable.
&t is )sed to stoeone al)e at a time.
&t is not 'ossibleto +old moe t+an one col)mn al)es o o*
al)es.Syntax : 8ariable_name table_name.c!lumn_name9t,pe;
Example 1:
Declare
*n! emp.empn!9t,pe%0:n;
*name emp.ename9t,pe;
Begin
Select
ename int! 8name 4r!m emp 5ere empn!08n!;
Dbms_!utput.put_line ( ‘ empl!,ee name is % ‘ ‘ ‘ 8name #;
End;
- Ro.T)#
&t is one of t+edatat/'e *+ic+ is )sed assi(n all t+e col)mn
datat/'es of table to a aiable.
&t +olds entieecod of t+e same table.
2ac+ of t+e time it oeide onl/ one ecod.
&t is not 'ossibleto ca't)e t+e moe t+an one table data.Syntax :8ariable_name table_name9r!t,pe;
Example 1:
7/25/2019 Pl SQL k Online Material
http://slidepdf.com/reader/full/pl-sql-k-online-material 7/98
Declare
*r! emp9r!t,pe;
*n! emp.empn!9t,pe%0:n;
Begin
Select < int! 8r! 4r!m emp 5ere empn!08n!;
Dbms_!utput.put_line ( 8r!.ename ‘ ‘ 8r!.sal #;
End;
R#co%$ T)# * o% + Pl/Sl R#co%$
&s is one of t+e )se defined tem'oa/ data t/'e *+ic+ is )sed to
stoe moe t+an one table data o ; to assi(n moe t+an one col)mn
datat/'es.
+e/ m)st at least contain one element.
"in'oint of data is not 'ossible.Syntax : ),pe ),pename is =ec!rd ( *al-1 Datat,pe& *al-6 Datat,pe&>..#;
*ar ),pename
Example :
Declare
),pe =ec is rec!rd ( 8name emp.ename9t,pe&
*sal emp.sal9t,pe&
*L!c dept.l!c9t,pe#;
*rec =ec;
*n! emp.empn!9t,pe%0:n;
Begin
7/25/2019 Pl SQL k Online Material
http://slidepdf.com/reader/full/pl-sql-k-online-material 8/98
Select ename&sal&l!c int! 8rec 4r!m emp&dept 5ere
emp.deptn!0dept.deptn! and emp.empn!08n!;
Dbms_!utput.put_line(8rec.8name"&"8rec.8sal"&"8rec.8l!c#;
End;
o Con$itional Stat#m#nts
1. &f ondition
!. &f 2lse ondition
3. 2lsif ondition
4. ase ondition
1. &f onditionSyntax :?4 c!nditi!n t5enStatements;End i4;
6. Example 1:
@. Declare
A. +umber ( A # %0:n;
. B 35ar ( 1 #;
. Begin
C. ?4 a62 t5en
. B%0"Fes";
G. End i4;
12. Dbms_!utput.put_line ( B #;
End;
11. &f 2lse onditionSyntax : ?4 c!nditi!n t5enStatements ;
7/25/2019 Pl SQL k Online Material
http://slidepdf.com/reader/full/pl-sql-k-online-material 9/98
ElseStatements ;End i4;
16. Example 1:
1@. Declare
1A. +umber ( A # %0:n;
1. B 35ar ( 12 #;
1.Begin
1C. ?4 a62 t5en
1. B%0")=HE";
1G.Else
62. B%0"'LSE";
61.End i4;
66. Dbms_!utput.put_line ( B #;
End;
!3. 2lsif onditionSyntax : ?4 c!nditi!n-1 t5enStatements;Elsi4 c!nditi!n-6 t5enStatements;Elsi4 c!nditi!n-@ t5enStatements;ElseStatements;End i4;
6A. Example 1:
6. Declare
6. +umber ( A # %0:n;
6C. B 35ar ( 1 #;
6.Begin
7/25/2019 Pl SQL k Online Material
http://slidepdf.com/reader/full/pl-sql-k-online-material 10/98
6G. ?4 a62 t5en
@2. B%0"L! *alue";
@1. Elsi4 aI62 and a122 t5en
@6. B%0"7ig5 *alue";
@@.Else
@A. B%0"?n8alid *alue";
@.End i4;
@. Dbms_!utput.put_line ( B #;
End;
3A. ase onditionSyntax : 3ase ( c!lumn name #J5en c!nditi!n t5enStatements;J5en c!nditi!n t5enStatements;ElseStatements;End 3ase;
@. Example 1:
@G.DE3L=E
A2. *SL +HKBE=(12#%0:+;
A1.BE?+
A6.3SE
A@. J7E+ *SL6222 )7E+
AA. DBKS_OH)PH).PH)_L?+E(M*SL ?SMM MMLOJM#;
A. J7E+ *SLI6222 )7E+
A. DBKS_OH)PH).PH)_L?+E(M*SL ?SMM MM7?7M#;
AC. ELSE
7/25/2019 Pl SQL k Online Material
http://slidepdf.com/reader/full/pl-sql-k-online-material 11/98
A. DBKS_OH)PH).PH)_L?+E(M*SL ?SMM MM?+*L?DM#;
AG. E+D 3SE;
E+D;
!. Loo)s
1. $im'le Loo'
!. <+ile Loo'
3. Bo Loo'
1. $im'le Loo'
Syntax :L!!pStatements;End l!!p;Syntax :L!!p3!de;E$it 5en c!nditi!n;End l!!p;
6. Example 1:
@. Begin
A. L!!p
. Dbms_!utput.put_line ( ‘Jelc!me t! -!nlines.c!mM #;
. End l!!p;
End;
Example 2 :
Declare
+ number(#%01;
Begin
7/25/2019 Pl SQL k Online Material
http://slidepdf.com/reader/full/pl-sql-k-online-material 12/98
L!!p
Dbms_!utput.put_line ( n #;
E$it 5en nI012;
+%0nN1;
End l!!p;
End;
Example 3 :
Declare
+ number(#%01;
Begin
L!!p
Dbms_!utput.put_line ( n #;
?4 nI012 t5en
E$it;
End i4;
+%0+N1;
End l!!p;
End;
A. <+ile Loo'Syntax : J5ile ( 3!nditi!n #L!!pStatements;
End l!!p;
. Example 1:
G. Declare
7/25/2019 Pl SQL k Online Material
http://slidepdf.com/reader/full/pl-sql-k-online-material 13/98
12. + +umber(A#%01;
11.Begin
16. J5ile nI012
1@. L!!p
1A. Dbms_!utput.put_line ( + #;
1.+%0+N1;
1. End l!!p;
End;
1A. Bo Loo'Syntax : '!r 8ariable_name in l!erb!und..!uterb!undL!!pStatements;End l!!p;
1. Example 1:
1G. Declare
62. + number(#;
61.Begin
66. '!r n in 1..12
6@. L!!p
6A. Dbms_!utput.put_line ( + #;
6. End l!!p;
End;
Example 2 :
Declare
+ number(#;
7/25/2019 Pl SQL k Online Material
http://slidepdf.com/reader/full/pl-sql-k-online-material 14/98
Begin
'!r n in re8erse 1..12
L!!p
Dbms_!utput.put_line ( + #;
End l!!p;
End;
!. Bin$ Va%ia&l#
1. +ese aiables ae session aiable.Syntax : 8ariable a number;
6. Example 1:
@. slI *ariable * +umber;
A. SlI Declare
. number(#%022;
. Begin
C. %8%0a/6;
. End;
G. SlI Print *;
o CURSORS
1. )so is a b)ffe aea *+ic+ is )sed to 'ocess m)lti'le ecods and also ecod b/ecod b/ 'ocess.
!. +ee ae t*o t/'es
&m'licit )sos
2'licit )sos
7/25/2019 Pl SQL k Online Material
http://slidepdf.com/reader/full/pl-sql-k-online-material 15/98
Im)licit C!%so%s
$l statements et)ns a sin(le ecod is called im'licit
c)sos
&m'licit c)so o'eations done b/ t+e s/stem.
C'en b/ t+e s/stem.
Betc+ t+e ecods b/ t+e s/stem
lose b/ t+e s/stem.
Example :
Declare
emp9r!t,pe;
Begin
Select < int! 4r!m emp 5ere empn!0C@G;
Dbms_!utput.put_line($.empn!"&"$.ename#;
End;
E0)licit C!%so%s
$l statements et)n a m)lti'le ecods is called e'licit
c)sos
2'licit c)so o'eations done b/ t+e )se.
eclae b/ t+e )se
C'en b/ t+e )se
Betc+ t+e ecods b/ t+e )se
lose b/ t+e )se
Example 1:
7/25/2019 Pl SQL k Online Material
http://slidepdf.com/reader/full/pl-sql-k-online-material 16/98
Declare
3urs!r c1 is select ename&sal 4r!m emp;
*_+ame 8arc5ar6(12#;
*_Sal number(12#;
Begin
Open 31;
'etc5 c1 int! 8_name&8_sal;
Dbms_!utput.put_line(8_name"&"8_sal#;
3l!se 31;
End;
Example 2 :
Declare
3urs!r c1 is select ename&!b 4r!m emp;
*8name 8arc5ar6(12#;
R!b 8arc5ar6(12#;
Begin
Open c1;
'etc5 c1 int! 8name&!b;
Dbms_!utput.put_line(8nameM&M!b#;
'etc5 c1 int! 8name&!b;
Dbms_!utput.put_line(8nameM&M!b#;
3l!se c1;
End;
Example 3 :
7/25/2019 Pl SQL k Online Material
http://slidepdf.com/reader/full/pl-sql-k-online-material 17/98
Declare
3curs!r c1 is select ename&!b 4r!m emp;
*name 8arc5ar6(12#;
*!b 8arc5ar6(12#;
Begin
Open c1;
L!!p
'etc5 c1 int! 8name&8!b;
Dbms_!utput.put_line(8nameM&M8!b#;
End l!!p;
3l!se c1;
End;
o CURSOR Att%i&!t#s
a. 2e/ e'licit c)so +ain( follo*in( fo) attib)tes
a. >otBo)nd
b. >Bo)nd
c. >&so'en
d. >?o*co)nt
!. ll t+ese c)so attib)tes )sin( alon( *it+ c)so name onl/ Syntax :curs!rname9 attributenameNote : E$cept 9r!c!unt all !t5er curs!r attribute rec!rds B!!lean 8alue return
eit5er true !r 4alse 5ere as 9r!c!unt return number datatupe.
1. >otBo)nd
a. ?et)ns &DL&E?$C? if c)so is declaed, b)t not o'en o if c)so
+as been closed.
b. ?et)ns LL if c)so is o'en, b)t fetc+ +as not been eec)ted.
7/25/2019 Pl SQL k Online Material
http://slidepdf.com/reader/full/pl-sql-k-online-material 18/98
c. ?et)ns BL$2 if a s)ccessf)l fetc+ +as been eec)ted.
d. ?et)ns ?2 if no o* *as et)ned.
6. Example 1:
@. Declare
A. 3curs!r c1 is select ename&!b 4r!m emp;
. *name 8arc5ar6(12#;
. *!b 8arc5ar6(12#;
C. Begin
. Open c1;
G. L!!p
12. 'etc5 c1 int! 8name&8!b;
11. E$it 5en c19n!t4!und;
16. Dbms_!utput.put_line(8nameM&M8!b#;
1@. End l!!p;
1A. 3l!se c1;
1.End;
1.
>Bo)nd
a. ?et)ns &DL&E?$C? if c)so is declaed, b)t not o'en o if c)so
+as been closed.
b. ?et)ns LL if c)so is o'en, b)t fetc+ +as not been eec)ted.
c. ?et)ns ?2 if a s)ccessf)l fetc+ +as been eec)ted.
d. ?et)ns BL$2 if no o* *as et)ned.
Example 1:
7/25/2019 Pl SQL k Online Material
http://slidepdf.com/reader/full/pl-sql-k-online-material 19/98
Declare
3urs!r c1 is select < 4r!m emp;
? emp9r!t,pe;
Begin
Open c1;
L!!p
'etc5 c1 int! i;
?4 c194!und t5en
Dbms_!utput.put_line(i.empn!M&Mi.ename#;
Else
E$it;
End i4;
End l!!p;
3l!se c1;
End;
>&sC'en
a. ?et)ns ?2 if t+e c)so is o'en,
b. ?et)ens BL$2 if t+e c)so is closed.
Example 1:
Declare
3urs!r c1 is select < 4r!m emp;
? emp9r!t,pe;
7/25/2019 Pl SQL k Online Material
http://slidepdf.com/reader/full/pl-sql-k-online-material 20/98
Begin
Open c1;
?4 c19is!pen t5en
Dbms_!utput.put_line(Mcurs!r is !penM#;
L!!p
'etc5 c1 int! i;
?4 c194!und t5en
Dbms_!utput.put_line(i.ename#;
Else
E$it;
End i4;
End l!!p;
3l!se c1;
?4 n!t c19is!pen t5en
Dbms_!utput.put_line(Mcurs!r is cl!sedM#;
End i4;
End i4;
End;
>?o*co)nt
a. ?et)ns &DL&E?$C? if c)so is declaed, b)t not o'en o if c)so
+as been closed.
b. ?et)ns t+e n)mbe of o*s fetc+ed b/ t+e c)so.
Example 1:
7/25/2019 Pl SQL k Online Material
http://slidepdf.com/reader/full/pl-sql-k-online-material 21/98
Declare
3urs!r c1 is select < 4r!m emp;
? emp9r!t,pe;
Begin
Open c1;
L!!p
'etc5 c1 int! i;
E$it 5en c19n!t4!und;
Dbms_!utput.put_line(i.empn!M&Mi.ename#;
End l!!p;
Dbms_!utput.put_line(M)!tal n! !4 empl!,ee% M c19r!c!unt#;
3l!se c1;
End;
b. PARAMETER CURSOR
"assin( a 'aamete in c)so is call it as a 'aamete c)so.
Syntax : 3urs!r curs!r_name ( parameter_name # is select < 4r!m table_name 5erec!lumn_name0parameter_name
Example 1:
Declare
3urs!r c1 (p_deptn! number# is select < 4r!m emp 5ere deptn!0p_deptn!;
? emp9r!t,pe;
Begin
Open c1(12#;
L!!p
7/25/2019 Pl SQL k Online Material
http://slidepdf.com/reader/full/pl-sql-k-online-material 22/98
'etc5 c1 int! i;
E$it 5en c19n!t4!und;
Dbms_!utput.put_line(i.ename#;
End l!!p;
3l!se c1;
End;
Example 2 :
Declare
3urs!r c1 ( p_!b 8arc5ar6# is select < 4r!m emp 5ere !b0p_!b;
? emp9r!t,pe;
Begin
Open c1(MK+E=M#;
L!!p
'etc5 c1 int! i;
E$it 5en c19n!t4!und;
Dbms_!utput.put_line(i.empn!M&Mi.enameM&Mi.!b#;
End l!!p;
3l!se c1;
Open c1(M3LE=M#;
L!!p
'etc5 c1 int! i;
E$it 5en c19n!t4!und;
Dbms_!utput.put_line(i.empn!M&Mi.enameM&Mi.!b#;
End l!!p;
7/25/2019 Pl SQL k Online Material
http://slidepdf.com/reader/full/pl-sql-k-online-material 23/98
3l!se c1;
End;
o CURSOR 1IT2 FOR Loo)
&n c)so fo loo' no need to o'en, fetc+, close t+e c)so. Bo loo' it self
a)tomaticall/ *ill 'efom t+ese f)nctionalities
o Example 1:
o Declare
o 3urs!r c1 is select < 4r!m emp;
o
? emp9r!t,pe;
o Begin
o '!r i in c1 l!!p
o Dbms_!utput.put_line(i.empn!M&Mi.ename#;
o End l!!p;
o End;
o "ESTED CURSOR 1IT2 FOR Loo)
o Example 2 :
o Declare
o 3urs!r c1 is select < 4r!m dept;
o 3urs!r c6(p_deptn! number# is select < 4r!m emp 5ere deptn!0p_deptn!;
o
Begin
o '!r i in c1
o L!!p
o Dbms_!utput.put_line(i.deptn!#;
7/25/2019 Pl SQL k Online Material
http://slidepdf.com/reader/full/pl-sql-k-online-material 24/98
o '!r in c6(i.deptn!#
o L!!p
o Dbms_!utput.put_line(.empn!M&M.enameM&M.sal#;
o End l!!p;
o End l!!p;
o End;
o CURSOR 1IT2 DML O)#%ations
o Example 1:
o Declare
o 3urs!r c1 is select < 4r!m emp;
o Begin
o '!r i in c1
o L!!p
o ?nsert int! t1 8alues (i.ename&i.sal#;
o End l!!p;
o End;
o Example 2 :
o Declare
o 3urs!r c1 is select < 4r!m t1;
o Begin
o '!r i in c1
o L!!p
o Delete 4r!m t1 5ere sal0@222;
o End l!!p;
7/25/2019 Pl SQL k Online Material
http://slidepdf.com/reader/full/pl-sql-k-online-material 25/98
o End;
o Example 3 :
o Declare
o 3urs!r c1 is select < 4r!m unc5am;
o Begin
o '!r i in c1
o L!!p
o ?4 i.!b0M3LE=M t5en
o Hpdate unc5am set sal0i.salN1111 5ere empn!0i.empn!;
o Elsi4 i.!b0MK+E=M t5en
o Hpdate unc5am set sal0i.salN6666 5ere empn!0i.empn!;
o End i4;
o End l!!p;
o End;
o
R#3 C!%so%
?ef )sos ae )se define t/'es *+ic+ is )sed to 'ocess m)lti'le
ecods and also t+is is ecod b/ ecod 'ocess
7eneall/ t+o)(+ t+e static c)sos *e ae )sin( onl/ one select statement
at a time fo sin(le actie set aea *+ee as in ef c)sos *e ae eec)tin( no of
select statements d/namicall/ fo sin(le actie set aea.
+ats *+/ t+ese t/'e of c)sos ae also called as d/namic c)sos.
/ )sin( ef c)sos *e et)n la(e amo)nt of data fom oacle database
into client a''lications.
+ee ae ! /'es
a. $ton( ?ef )so
7/25/2019 Pl SQL k Online Material
http://slidepdf.com/reader/full/pl-sql-k-online-material 26/98
b. <eak ?ef )so
$ton( ?ef )so
&t is one of t+e ef c)so *+ic+ is +ain( et)n t/'e.
<eak ?ef )so
&t is one of t+e ef c)so *+ic+ does not +ae a et)n t/'e.
"ot# : &n ef c)so *e ae eec)tin( select statements )sin( o'en .... fo statement.
Example 1 :
Declare
),pe t1 is re4 curs!r;
8_t t1;
i emp9r!t,pe;
begin
!pen 8_t 4!r select < 4r!m emp 5ere salI6222;
l!!p
4etc5 8_t int! i;
e$it 5en 8_t9n!t4!und;
dbms_!utput.put_line(i.enameM Mi.sal#;
end l!!p;
cl!se 8_t;
end;
Example 2 :
Declare
t,pe t1 is re4 curs!r;
8_t t1;
7/25/2019 Pl SQL k Online Material
http://slidepdf.com/reader/full/pl-sql-k-online-material 27/98
i emp9r!t,pe;
dept9r!t,pe;
8_n! number(#%0:n!;
begin
i4 8_n!01 t5en
!pen 8_t 4!r select < 4r!m emp;
l!!p
4etc5 8_t int! i;
e$it 5en 8_t9n!t4!und;
dbms_!utput.put_line(i.enameM Mi.deptn!#;
end l!!p;
cl!se 8_t;
elsi4 8_n!06 t5en
!pen 8_t 4!r select < 4r!m dept;
l!!p
4etc5 8_t int! ;
e$it 5en 8_t9n!t4!und;
dbms_!utput.put_line(.deptn!M M.dname#;
end l!!p;
cl!se 8_t;
end i4;
end;
Example 3 :
create !r replace pacage pg1
7/25/2019 Pl SQL k Online Material
http://slidepdf.com/reader/full/pl-sql-k-online-material 28/98
is
t,pe t1 is re4 curs!r return emp9r!t,pe;
t,pe t6 is re4 curs!r return dept9r!t,pe;
pr!cedure p1 (p_t1 !ut t1#;
pr!cedure p6 (p_t6 !ut t6#;
end;
create !r replace pacage b!d, pg1 is
pr!cedure p1 (p_t1 !ut t1#
is
begin
!pen p_t1 4!r select < 4r!m emp;
end p1;
pr!cedure p6 (p_t6 !ut t6#
is
begin
!pen p_t6 4!r select < 4r!m dept;
end p6;
end;
E$ecuti!n
8ariable a re4curs!r
8ariable b re4curs!r
7/25/2019 Pl SQL k Online Material
http://slidepdf.com/reader/full/pl-sql-k-online-material 29/98
e$ec pg1.p1(%a#;
e$ec pg1.p6(%b#;
print a b;
o 14#%# C!%%#nt o3 an$ Fo% U)$at# Cla!s#
7eneall/ *+en *e ae )sin( )'date, delete statements a)tomaticall/ locks
ae (eneated in t+e data base.
&f /o) *ant to (eneate locks befoe )'date, delete statements t+en *e ae
)sin( c)so lockin( mec+anism in all data base s/stems.
&n t+is case *e m)st s'ecif/ fo )'date cla)se in c)so definition.
Syntax : 3urs!r 3urs!r_+ame is select < 4r!m )able_+ame 5ere c!nditi!n 4!rupdate
&f /o) ae s'ecif/in( fo )'date cla)se also oacle see does not (eneate
t+e lock i.e *+enee *e ae o'enin( t+e c)so t+en onl/ oacle see intenall/
)ses ecl)sie locks.
fte 'ocessin( *e m)st elease t+e locks )sin( commit statement.
*+ee c)ent of cla)se )ni)el/ identif/in( a ecod in eac+ 'ocess
beca)se *+ee c)ent of cla)se intenall/ )ses ?C<&.
<+enee *e ae )sin( *+ee c)ent of cla)se *e m)st )se fo )'date
cla)se.
Example :
Declare
curs!r c1 is select < 4r!m 4!r update;
i emp9r!t,pe;
begin
!pen c1;
l!!p
7/25/2019 Pl SQL k Online Material
http://slidepdf.com/reader/full/pl-sql-k-online-material 30/98
4etc5 c1 int! i;
e$it 5en c19n!t4!und;
i4 i.!b0M3LE=M t5en
update set sal0i.salN1222 5ere current !4 c1;
end i4;
end l!!p;
c!mmit;
cl!se c1;
end;
o E5CEPTIO"S
2ce'tion is one of t+e actiit/ *+ic+ is )sed to +andle t+e eos at
)ntime.
+ee ae 3 t/'es of ece'tions
"edefined 2ce'tion
sedefined 2ce'tion
nnamed 2ce'tion
1. "edefined 2ce'tion
&t is one of t+e ece'tion *+ic+ ae defined b/ oacle.
+ee ae !0 ece'tions aailable.Syntax : 5en e$cepti!n1 t5enstatements;
5en e$cepti!n6 t5enstatements;5en !t5ers t5enstatements;
"edefined 2ce'tions ae
noEdataEfo)nd
7/25/2019 Pl SQL k Online Material
http://slidepdf.com/reader/full/pl-sql-k-online-material 31/98
tooEman/Eo*s
inalidEc)so
c)soEalead/Eo'en
inalidEn)mbe
al)eEeo
FeoEdeide
ot+es
etc.....
oEataEBo)nd
<+en a 'l#sl block contains select ------into cla)se
and also if e)ested data not aailable in a table
oacle see et)ns an eo.
2o is oa-01403 : no data fo)nd
o +andle t+is eo *e ae )sin( noEdataEfo)nd
ece'tion.
Example :
Declare
8_ename 8arc5ar6(62#;
8_sal number(12#;
begin
select ename&sal int! 8_ename&8_sal 4r!m 5ere
empn!0:n!;
dbms_!utput.put_line(8_enameM M8_sal#;
end;
7/25/2019 Pl SQL k Online Material
http://slidepdf.com/reader/full/pl-sql-k-online-material 32/98
Example :
Declare
8_ename 8arc5ar6(62#;
8_sal number(12#;
begin
select ename&sal int! 8_ename&8_sal 4r!m 5ere
empn!0:n!;
dbms_!utput.put_line(8_enameM M8_sal#;
e$cepti!n
5en n!_data_4!und t5en
dbms_!utput.put_line(Mempl!,ee d!es n!t e$itM#;
end;
ooEMan/E?o*s
<+en a select ------into cla)se t/ to et)n moe
t+an one ecod o moe t+an one al)e t+en oaclesee et)n an eo.
2o is oa-014!! : eact fetc+ et)ns moe t+an
e)ested n)mbe of o*s.
o +andle t+is eo *e ae )sin( tooEman/Eo*s
ece'tion
Example :
Declare
8_ename 8arc5ar6(62#;
7/25/2019 Pl SQL k Online Material
http://slidepdf.com/reader/full/pl-sql-k-online-material 33/98
8_sal number(12#;
begin
select ename&sal int! 8_ename&8_sal 4r!m ;
dbms_!utput.put_line(8_enameM M8_sal#;
end;
Example :
Declare
8_ename 8arc5ar6(62#;
8_sal number(12#;
begin
select ename&sal int! 8_ename&8_sal 4r!m ;
dbms_!utput.put_line(8_enameM M8_sal#;
e$cepti!n
5en t!!_man,_r!s t5en
dbms_!utput.put_line(Mpr!gram return m!re t5an !ne
r!M#;
end;
&nalidE)so
<+enee *e ae 'efomin( inalid o'eations on
t+e c)so see et)ns an eo i.e if /o) ae t/ toclose t+e c)so *it+ o)t o'enin( c)so t+en oacle
see et)ns an eo.
2o is oa-01001 : inalid c)so
o +andle t+is eo *e ae )sin( inalidEc)so
ece'tion.
7/25/2019 Pl SQL k Online Material
http://slidepdf.com/reader/full/pl-sql-k-online-material 34/98
Example :
Declare
curs!r c1 is select < 4r!m emp;
i emp9r!t,pe;
begin
l!!p
4etc5 c1 int! i;
e$it 5en c19n!t4!und;
dbms_!utput.put_line(i.enamei.sal#;
end l!!p;
cl!se c1;
end;
Example :
Declare
curs!r c1 is select < 4r!m emp;
i emp9r!t,pe;
begin
l!!p
4etc5 c1 int! i;
e$it 5en c19n!t4!und;
dbms_!utput.put_line(i.enamei.sal#;
end l!!p;
cl!se c1;
e$cepti!n
7/25/2019 Pl SQL k Online Material
http://slidepdf.com/reader/full/pl-sql-k-online-material 35/98
5en in8alid_curs!r t5en
dbms_!utput.put_line(MTrst ,!u !pen t5e curs!rM#;
end;
)soElead/EC'en
<+en *e ae t/ to eo'en t+e c)so *it+o)t
closin( t+e c)so oacle see et)ns an eo.
2o is oa-06511 : c)so alead/ o'en
o +andle t+is eo *e ae )sin(
c)soEalead/Eo'en ece'tion
Example :
curs!r c1 is select < 4r!m emp;
i emp9r!t,pe;
begin
!pen c1;
l!!p
!pen c1;
4etc5 c1 int! i;
e$it 5en c19n!t4!und;
dbms_!utput.put_line(i.enamei.sal#;
end l!!p;
cl!se c1;
end;
Example :
Declare
7/25/2019 Pl SQL k Online Material
http://slidepdf.com/reader/full/pl-sql-k-online-material 36/98
curs!r c1 is select < 4r!m emp;
i emp9r!t,pe;
begin
!pen c1;
l!!p
!pen c1;
4etc5 c1 int! i;
e$it 5en c19n!t4!und;
dbms_!utput.put_line(i.enamei.sal#;
end l!!p;
cl!se c1;
e$cepti!n
5en curs!r_alread,_!pen t5en
dbms_!utput.put_line(Mcurs!r alread, !penM#;
end;
&nalidE)mbe
<+enee *e ae t/ to conet stin( t/'e to
n)mbe t/'e oacle see et)n eo.
2o is oa-01A!! : inalid n)mbe
o +andle t+is eo *e ae )sin( inalidEeo
ece'tion
Example :
Begin
7/25/2019 Pl SQL k Online Material
http://slidepdf.com/reader/full/pl-sql-k-online-material 37/98
insert int! emp(empn!&sal# 8alues (111&MabcdM#;
end;
Example :
Begin
insert int! emp(empn!&sal# 8alues (111&MabcdM#;
e$cepti!n
5en in8alid_number t5en
dbms_!utput.put_line(Minsert pr!per data !nl,M#;
end;
Dal)eE2o
<+enee *e ae t/ to conet stin( t/'e to
n)mbe t/'e based on t+e condition t+en oacle seeet)ns an eo
<+enee *e ae t/ to stoe la(e amo)nt of data
t+an t+e s'ecified data t/'e siFe in aaible declaationt+en oacle see et)n same eo
2o is oa-0650! : n)meic o al)e eo:
c+aacte to n)mbe conesion eo
o +andle t+is eo *e ae )sin( al)eEeo
ece'tion
Example :
Declare
number(12#;
begin
%0M:$MNM:,M;
dbms_!utput.put_line(#;
7/25/2019 Pl SQL k Online Material
http://slidepdf.com/reader/full/pl-sql-k-online-material 38/98
end;
Example :
Declare
number(12#;
begin
%0M:$MNM:,M;
dbms_!utput.put_line(#;
e$cepti!n
5en 8alue_err!r t5en
dbms_!utput.put_line(MEnter t5e pr!per data !nl,M#;
end;
Example :
Declare
number(@#;
begin
%0MabcdM;
dbms_!utput.put_line(#;
end;
GeoEeide
<+enee *e ae t/ to diide b/ Feo t+en oacle
see et)n a eo
2o is oa-014A6 : diiso is e)al to Feo
o +andle t+is eo *e ae )sin( FeoEdiide
ece'tion
7/25/2019 Pl SQL k Online Material
http://slidepdf.com/reader/full/pl-sql-k-online-material 39/98
Example :
Declare
a number(12#;
b number(12#%0:b;
c number(12#%0:c;
begin
a%0b/c;
dbms_!utput.put_line(a#;
end;
Example :
Declare
a number(12#;
b number(12#%0:b;
c number(12#%0:c;
begin
a%0b/c;
dbms_!utput.put_line(a#;
e$cepti!n
5en er!_di8ide t5en
dbms_!utput.put_line(Mc d!es n!t c!ntain er!M#;
end;
2H2"&C "?C"7&C
2ce'tions ae also aised in
7/25/2019 Pl SQL k Online Material
http://slidepdf.com/reader/full/pl-sql-k-online-material 40/98
eclaation $ection
2ec)table $ection
2ce'tion $ection
&f t+e ece'tions ae aised in eec)table section t+ose
ece'tions ae +andled )sin( eit+e inne block o an o)te
block.
<+ee as if ece'tion ae aised in declaation section o in
ece'tion section t+ose ece'tions ae +andled )sin( o)te
blocks onl/.
Example :
Begin
declare
8arc5ar6(@#;--%0MabcdM;
begin
%0MabcdM;
dbms_!utput.put_line(#;
e$cepti!n
5en 8alue_err!r t5en
dbms_!utput.put_line(Min8alid string leng5tM#;
end;
e$cepti!n
5en 8alue_err!r t5en
dbms_!utput.put_line(Mt5e leng5t is m!reM#;
end;
7/25/2019 Pl SQL k Online Material
http://slidepdf.com/reader/full/pl-sql-k-online-material 41/98
sedefined 2ce'tion
<e can also ceate o) o*n ece'tion names and also aise
*+enee it is necessa/. t+ese t/'es of ece'tions ae called )se definedece'tions.
+ese ece'tions ae diided into 3 ste's
eclae 2ce'tion
?aise 2ce'tion
Iandle 2ce'tion
1. eclae 2ce'tion
&n declae section of t+e 'l#sl 'o(am *e ae definin( o) o*n ece'tion name )sin( ece'tion t/'e.
Syntax : userdeTnede$cepti!n_name e$cepti!n
Example :
Declare
a e$cepti!n;
?aise 2ce'tion
<+enee it is e)ied aise )se defined ece'tion eit+e
in eec)table section o in ece'tion section, in t+is case *e ae
)sin( aise ke/*od.
Syntax : raise userdeTnede$cepti!n_name
Example :
Declare
a e$cepti!n;
begin
raise a;
7/25/2019 Pl SQL k Online Material
http://slidepdf.com/reader/full/pl-sql-k-online-material 42/98
end;
Iandle 2ce'tion
<e can also +andle )se defined ece'tions as same as 'edefined ece'tion )sin( 'edefined +andle.
Syntax :5en userdeTnede$cepti!n_name1 t5enstatements;5en userdeTnede$cepti!n_name6 t5enstatements;--------------------5en !t5ers t5enstatements;
Example :
Declare
a e$cepti!n;
begin
i4 t!_c5ar(s,sdate&Md,M#0MsunM t5en
raise a;
end i4;
e$cepti!n
5en t5en
dbms_!utput.put_line(Mm, e$cepti!n raised t!da,M#;
end;
Ex:
Declare
8_sal number(12#;
a e$cepti!n;
7/25/2019 Pl SQL k Online Material
http://slidepdf.com/reader/full/pl-sql-k-online-material 43/98
begin
select sal int! 8_sal 4r!m 5ere empn!0CG26;
i4 8_salI6222 t5en
raise a;
else
update set sal0salN122 5ere empn!0CG26;
end i4;
e$cepti!n
5en a t5en
dbms_!utput.put_line(Msalar, alred, 5ig5M#;
end;
?&$&7 "edefined 2ce'tion
<e can also aise t+e ece'tion in ece'tion section
Example :
Ddeclare
a1 e$cepti!n;
a6 e$cepti!n;
begin
begin
raise a1;
e$cepti!n
5en a1 t5en
dbms_!utput.put_line(Ma1 5andledM#;
7/25/2019 Pl SQL k Online Material
http://slidepdf.com/reader/full/pl-sql-k-online-material 44/98
--raise a6;
end;
e$cepti!n
5en a6 t5en
dbms_!utput.put_line(Ma6 5andledM#;
end;
2??C? a''in( B)nctions
+ee ae t*o eo ta''in( f)nctions s)''oted b/ oacle.
$%L ode
$%L 2m
SQL Co$# : &t et)ns n)mbes
SQL E%%m : &t et)ns eo n)mbe *it+ eo messa(e.
Example :
Declare
8_sal number(12#;
begin
select sal int! 8_sal 4r!m emp 5ere empn!0C@G;
dbms_!utput.put_line(slc!de#;
dbms_!utput.put_line(slerrm#;
end;
?&$2 ""L&&C 2??C?
1. &f /o) *ant to dis'la/ /o) o*n )se defined ece'tion
n)mbe and ece'tion messa(e t+en *e can )se t+is aise a''licationeo
7/25/2019 Pl SQL k Online Material
http://slidepdf.com/reader/full/pl-sql-k-online-material 45/98
Syntax : raise_applicati!n_err!r ( err!r_number&err!r_message #;
E%%o%6"!m&#% : &t is )sed to (ie t+e eo n)mbes bet*een
-!0000 to -!0999
E%%o%6M#ssa# : &t is )sed to (ie t+e messa(e )'to 51! c+aactes.
Example :
Declare
8_sal number(12#;
a e$cepti!n;
begin
select sal int! 8_sal 4r!m 5ere empn!0C@G;
i4 8_sal 6222 t5en
raise a;
else
update set sal0salN122 5ere empn!0C@G;
end i4;
e$cepti!n
5en a t5en
raise_applicati!n_err!r ( -62GGG&Msalar, alreda, 5ig5M#;
end;
nnamed 2ce'tion
&f /o) *ant to +andle ot+e t+an oacle !0 'edefined eos *e ae
)sin( )nnamed met+od.
eca)se oacle define ece'tion names fo e()lal/ acc)ed eos
ot+e t+an !0 t+e/ ae not definin( ece'tion names.
&n t+is case *e ae 'oidin( ece'tion names and also associate
t+is ece'tion name *it+ a''o'iate eo no )sin( ece'tionEinit f)nction.
7/25/2019 Pl SQL k Online Material
http://slidepdf.com/reader/full/pl-sql-k-online-material 46/98
Syntax : pragma e$cepti!n_init ( userdeTned_e$cepti!n_name&err!r_number #;
Iee 'a(ma is a com'ile diectie i.e at t+e time of com'ilation
onl/ 'l#sl )ntime en(ine associate eo n)mbe *it+ ece'tion name.
+is f)nction is )sed in declae section of t+e 'l#sl block.
Example :
Declare
8_n! number(12#;
e e$cepti!n;
pragma e$cepti!n_init(e&-66G1#;
begin
select empn! int! 8_n! 4r!m emp 5ere empn!0:n!;
dbms_!utput.put_line(8_n!#;
e$cepti!n
5en e t5en
dbms_!utput.put_line(Mpragma err!rM#;
end;
!. SUB PRO7RAMS
$)b 'o(ams ae named 'l#sl blocks *+ic+ is )sed to sole 'atic)la
task.
+ee ae t*o t/'es of s)b 'o(ams s)''oted b/ oacle.
"oced)es
B)nctions
"oced)es
1. "oced)es ma/ o ma/ not et)n a al)e.
7/25/2019 Pl SQL k Online Material
http://slidepdf.com/reader/full/pl-sql-k-online-material 47/98
!. "oced)es et)n moe t+an one al)e *+ile )sin( t+e o)t
'aamete.
3. "oced)e can eec)te onl/ 3 *a/s
non/mo)s lock
2ec
all
b. "oced)e can not eec)te in select statement.
c. "oced)e intenall/ +ain( one time com'ilation 'ocess.
d. "oced)e ae )sed to im'oe t+e 'efomance of b)siness
a''lications
e. 2e/ 'oced)e is +ain( t*o 'ats
"oced)e $'ecification
&n 'oced)e s'ecification *e ae s'ecif/in( name
of t+e 'oced)e and t/'es of t+e 'aametes.
"oced)e od/
&n 'oced)e bod/ *e ae solin( t+e act)al task.
Example :
create !r replace pr!cedure p11(p_empn! number# is
8_ename 8arc5ar6(12#;
8_sal number(12#;
begin
select ename&sal int! 8_ename&8_sal 4r!m emp 5ere empn!0p_empn!;
dbms_!utput.put_line(8_enameM&M8_sal#;
end;
7/25/2019 Pl SQL k Online Material
http://slidepdf.com/reader/full/pl-sql-k-online-material 48/98
f. 2ec)te +e "oced)e in 3 *a/s
M#t4o$ : 8 - 2ec "11 A90! ;
M#t4o$ : 9 - e(in
"11 A90! ;JendJ
M#t4o$ : - all "11 A90! ;
Example :
create !r replace pr!cedure p111(p_deptn! number# is
curs!r c1 is select < 4r!m emp 5ere deptn!0p_deptn!;
i emp9r!t,pe;
begin
!pen c1;
l!!p
4etc5 c1 int! i;
e$it 5en c19n!t4!und;
dbms_!utput.put_line(i.enameM&Mi.salM&Mi.deptn!#;
end l!!p;
cl!se c1;
end;
"aametes in "oced)es
"aametes ae )sed to 'ass t+e al)e into 'oced)es andalso et)n al)es fom t+e 'oced)e.
&n t+is case *e m)st )se t*o t/'es of 'aametes
Bomal "aametes
ct)al "aametes
7/25/2019 Pl SQL k Online Material
http://slidepdf.com/reader/full/pl-sql-k-online-material 49/98
Fo%mal Pa%am#t#%s
Bomal "aametes ae defined in 'oced)e
s'ecification
&n Bomal "aametes *e ae definin(
'aamete name = mode of t+e 'aamete
+ee ae t+ee t/'es of modes s)''oted b/
oacle.
a. & Mode
b. C Mode
c. &C Mode
a. & Mode :
/ defa)lt 'oced)e
'aametes +ain( & mode.
& Mode is )sed to 'ass t+e
al)es into 'oced)e bod/.
+is mode be+aes like a
constant in 'oced)e bod/,
t+o)(+ t+is & Mode *e canalso 'ass defa)lt al)es )sin(
defa)lt o K:K o'eato
Example :
3reate !r replace pr!cedure
P1 ( p_deptn! in number&
p_dname in 8arc5ar6&
p_l!c in 8arc5ar6#
is
begin
7/25/2019 Pl SQL k Online Material
http://slidepdf.com/reader/full/pl-sql-k-online-material 50/98
insert int! dept 8alues
(p_deptn!&p_dname&p_l!c#;
dbms_!utput.put_line(Mrec!rd
is inserted t5r!ug5pr!cedureM#;
end;
+ee ae t+ee t/'es of
eec)tion met+ods s)''oted b/ in 'aamete.
1. "ositional otations
!. amed otations
3. Mied otations
1. "ositional otations
Example : e$ec p1( 1&
MaM&MbM#;
!. amed otations
Example : e$ec p1
( p_dname0IM$M&
p_l!c0IM,M&
p_deptn!0I6 #
3. Mied otations
Example : e$ec p1 ( 1&
p_dname0IMmM&
p_l!c0IMnM #;
7/25/2019 Pl SQL k Online Material
http://slidepdf.com/reader/full/pl-sql-k-online-material 51/98
b. C Mode :
+is mode is )sed to et)n
al)es fom 'oced)e bod/.
C Mode intenall/
be+aes like a )ninitialiFed
aiable in 'oced)e bod/
c. Example :
d. 3reate !r replace pr!cedure p1 (a in
number& b !ut number# is
e. begin
4. b%0a<a;
g. dbms_!utput.put_line(b#;
5. end;
Note : ?n !racle i4 a subpr!gramc!ntains OH)& ?+OH) Parameters t5!sesubpr!grams are e$ecuted using4!ll!ing t! met5!ds.
Method - 1 : Hsing Bind
*ariable
Method - 2 : Hsingnn!n,m!us Bl!c
ind Daiable:
• +ese aiables ae
session aiables.
•
+ese aiables aeceated at +ost
enionment t+ats *+/t+ese aiables ae also
called as +ost aiables.
• +ese aiables ae not
a 'l#sl aiables, b)t
7/25/2019 Pl SQL k Online Material
http://slidepdf.com/reader/full/pl-sql-k-online-material 52/98
*e can also )se t+ese
aiables in "L#$%L to
eec)te s)b'o(ams+ain( C?
"aametes.
M#t4o$ , 8 : ind Daiable
Example : *ariable b number;
e$ec p1 ( 12& %b#;
M#t4o$ , 9 : nnon/mo)s
lock
Example :Declare
b number(12#;
begin
p1( & b #
dbms_!utput.put_line( b #;
end;
Example :De8el!p a pr!gram
4!r passing empl!,ee name as
in parameter return salar, !4
t5at empl!,ee using !ut
parameter 4r!m emp tableU
Prog :3reate !r replace
pr!cedure p1 ( p_ename in
8arc5ar6& p_sal !ut number #
is
7/25/2019 Pl SQL k Online Material
http://slidepdf.com/reader/full/pl-sql-k-online-material 53/98
begin
select sal int! p_sal 4r!m
emp 5ere empn!0p_ename;
end;
M#t4o$ , 8 :ind aiable
8ariable a number;
e$ec p1 ( M?+M& %a#;
M#t4o$ , 9 : nnon/mo)s
lock
Declare
a number(12#;
begin
e$ec p1( M LLE+ M& a #;
dbms_!utput.put_line( a #;
end;
Example :
De8el!p a pr!gram 4!r passing
deptn! as
a parameter return 5! man,
empl!,ees are !ring in a
dept 4r!m emp tableU
Prog :
7/25/2019 Pl SQL k Online Material
http://slidepdf.com/reader/full/pl-sql-k-online-material 54/98
3reate !r replace pr!cedure
pe6 ( p_deptn! in number& p_t
!ut number# is
begin
select c!unt(<# int! p_t 4r!m
emp 5ere deptn!0p_deptn!;
dbms_!utput.put_line(p_t#;
end;
b. & C Mode
+is mode is )sed to 'ass t+eal)es into s)b 'o(am =
et)n t+e al)es fom s)b 'o(ams.
c. Example :
d. 3reate !r replace pr!cedure
p1 ( a in !ut number # is
e. begin
4. a %0 a<a;
g. dbms_!utput.put_line ( a #;
5. end;
• M#t4o$ , 8 :ind
Daiable
*ariable a number;
e$ec %a %012;
e$ec p1 ( %a #;
7/25/2019 Pl SQL k Online Material
http://slidepdf.com/reader/full/pl-sql-k-online-material 55/98
• M#t4o$ ,
9 : nnon/mo)s lock
Declare
a number(12# %0 :n;
begin
p1( a #;
dbms_!utput.put_line
( a #;
end;
Example :
3reate !r replace
pr!cedure peA ( a in
!ut number# is
begin
select sal int! a 4r!m
emp 5ere empn!0a;
dbms_!utput.put_line(
a #;
end;
b. "?7M CCMC$ ?$&C
1. )tonomo)s tansactions ae inde'endent tansactions
)sed in eit+e 'oced)es o in ti((es.
!. 7eneall/ a)tonomo)s tansactions ae )sed in c+ild
'oced)es, +ese 'oced)es ae not effected fom
7/25/2019 Pl SQL k Online Material
http://slidepdf.com/reader/full/pl-sql-k-online-material 56/98
t+e main tansactions *+en *e ae )sin( commit o
ollback.
c. Example : 3reate table test ( name 8arc5ar6(12##;
d. Pr!gram % 3reate !r replace pr!cedure P1 is
e. pragma aut!n!m!us_transacti!n;
4. begin
g. insert int! ptest 8alues (MindiaM#;
5. c!mmit;
i. end;
.
. E$ecute )5e Pr!gram% Begin
l. insert int! ptest 8alues (MusaM#;
m. insert int! ptest 8alues (MuM#;
n. P1;
!. r!llbac;
p. end;
.
r. Jit5 !ut ut!n!m!us )ransacti!n
s. Pr!gram % 3reate !r replace pr!cedure P1 is
t. begin
u. insert int! ptest 8alues (MindiaM#;
8. c!mmit;
. end;
$. E$ecute )5e Pr!gram% Begin
,. insert int! ptest 8alues (MusaM#;
7/25/2019 Pl SQL k Online Material
http://slidepdf.com/reader/full/pl-sql-k-online-material 57/98
. insert int! ptest 8alues (MuM#;
aa. P1;
ab. r!llbac;
ac. end;
B)nctions
B)nction is a named 'l#sl block *+ic+ is )sed to sole
'atic)la task and b/ defa)lt f)nctions et)n a sin(le al)e.
B)nction is allo* to *ite m)lti'le et)n statements b)t it
eec)te onl/ fist et)n statement.
B)nction can eec)te in 4 *a/s
nnon/mo)s lock
$elect $tatement
ind Daiable
2ec
!. B)nction also +ain( t*o 'ats
B)nction $'ecification
B)nction od/
3. &n B)nction $'ecification *e ae s'ecif/in( name of t+ef)nction and t/'e of t+e 'aametes *+ee as in f)nction bod/ *e ae
solin( t+e act)al task.
Example : 3reate !r replace 4uncti!n 4un1( a 8arc5ar6#
return 8arc5ar6
is
7/25/2019 Pl SQL k Online Material
http://slidepdf.com/reader/full/pl-sql-k-online-material 58/98
begin
return a;
end;
Method - 1 : Select 3lause
Select 4un1(M5iM# 4r!m dual
Method - 2 :nn!n,m!us Bl!c
Declare
a 8arc5ar6(12#;
begin
a %04un1(M5iM#;
dbms_!utput.put_line(a#;
end;
Method - 3 : Bind *ariable
*ariable * *arc5ar6(62#;
Begin
%a%04un1(M5iM#;
end;
Method - 4 : E$ec
E$ec Dbms_!utput.put_line(4un1(M5iM##;
Example : 3reate !r replace 4uncti!n 4un6 (a number#
7/25/2019 Pl SQL k Online Material
http://slidepdf.com/reader/full/pl-sql-k-online-material 59/98
return 8arc5ar6
is
begin
i4 m!d(a&6#02 t5en
return Me8en numberM;
else
return M!dd numberM;
end i4;
end;
Note : Je can als! use user deTned 4uncti!n in insert statement.
Example : 3reate table t1(sn! number(12#& msg
8arc5ar6(12##;
?nsert int! t1 8alues ( 1& 4un6(##;
Select < 4r!m t1;
Example : Jrite a pl/sl st!red 4uncti!n 4!r passing empn! as
parameter return
gr!ss salar, 4r!m emp table based !n 4!ll!ing c!nditi!nU
3!nditi!n 0I gr!ss%0basicN5raNdaNp4;
5ra 0I 129 !4 Sal
da 0I 629 !4 Sal
p4 0I 129 !4 Sal
7/25/2019 Pl SQL k Online Material
http://slidepdf.com/reader/full/pl-sql-k-online-material 60/98
Prog : 3reate !r replace 4uncti!n 4un@ (p_empn! number#
return number
is
8sal number(12#;
gr!ss number(12#;
5ra number(12#;
da number(12#;
p4 number(12#;
begin
select sal int! 8sal 4r!m emp 5ere empn!0p_empn!;
5ra%08sal<2.1;
da%08sal<2.6;
p4%08sal<2.1;
gr!ss%08salN5raNdaNp4;
return gr!ss;
end;
Note : Je can als! use predeTned 4uncti!ns in user deTned
4uncti!ns and als! t5is user deTned
4uncti!ns in same table !r diVerent table.
Example : 3reate !r replace 4uncti!n 4m
return number
7/25/2019 Pl SQL k Online Material
http://slidepdf.com/reader/full/pl-sql-k-online-material 61/98
is
8sal number(12#;
begin
select ma$(sal# int! 8sal 4r!m emp;
return 8sal;
end;
Note : ?4 e ant t! return m!re number !4 8alues 4r!m 4uncti!n
e are using OH) Parameter.
Example : 3reate !r replace 4uncti!n 4unA
(p_deptn! in number
&p_dname !ut 8arc5ar6
&p_l!c !ut 8arc5ar6#
return 8arc5ar6
is
begin
select dname&l!c int! p_dname&p_l!c 4r!m dept 5ere
deptn!0p_deptn!;
return p_dname;
end;
*ariable a 8arc5ar6(12#;
*ariable b 8arc5ar6(12#;
*ariable c 8arc5ar6(12#;
7/25/2019 Pl SQL k Online Material
http://slidepdf.com/reader/full/pl-sql-k-online-material 62/98
Begin
%a%04unA ( 12& %b& %c#;
end;
Print b c;
Example : Jrite a pl/sl st!red 4uncti!n 4!r passing
empn!&date as
parameter return number !4 ,ears t5at empl!,ee is !ring
based !n date 4r!m emp tableU
Prog : 3reate !r replace 4uncti!n 4un(p_empn!
number&p_date date#
return number
is
a number(12#;
begin
select m!nt5s_beteen(p_date&5iredate#/16 int! a 4r!m emp
5ere empn!0p_empn!;
return (r!und(a##;
end;
Execution : Select empn!&ename&5iredate&
4un(empn!&s,sdate#MFearsM E$p
4r!m emp 5ere empn!0CG26
7/25/2019 Pl SQL k Online Material
http://slidepdf.com/reader/full/pl-sql-k-online-material 63/98
Example : Jrite a pl/sl st!red 4uncti!n 4!r passing empn! as
parameter&
calculate ta$ based !n 4!ll!ing c!nditi!ns b, using emp table.
3!nditi!ns%
1# i4 annual salar, I12222 t5en ta$0129
6# i4 annual salar, I62222 t5en ta$0629
@# i4 annual salar, I2222 t5en ta$0@29
Prog : 3reate !r replace 4uncti!n 4unC (p_empn! number#
return number
is
8sal number(12#;
asal number(12#;
ita$ number(12#;
begin
select sal int! 8sal 4r!m emp 5ere empn!0p_empn!;
asal%08sal<16;
i4 asalI12222 and asal01222 t5en
ita$%0asal<2.1;
elsi4 asalI1222 and asal06222 t5en
ita$%0asal<2.6;
elsi4 asalI62222 t5en
ita$%0asal<2.@;
else
7/25/2019 Pl SQL k Online Material
http://slidepdf.com/reader/full/pl-sql-k-online-material 64/98
ita$%02;
end i4;
return ita$;
end;
o Packa#s
1. "acka(e is a database obect *+ic+ is )sed enca's)late aiables,constants, 'oced)es, c)sos, f)nctions, t/'es in to sin(le )nit.
!. "acka(es does not acce'ts 'aametes, can not be nested, can not be
inoked.
3. 7eneall/ 'acka(es ae )sed to im'oe 'efomance of t+e a''lication beca)se *+en *e ae callin( 'acka(ed s)b 'o(am fist time total 'acka(e
a)tomaticall/ loaded into memo/ aea.
4. <+enee *e ae callin( s)bse)ent s)b 'o(am calls 'l#sl )n time
en(ine callin( t+ose s)b 'o(am fom memo/ aea.
5. +is 'ocess a)tomaticall/ ed)ces disk &#C t+ats *+/ 'acka(es im'oes 'efomance of t+e a''lication.
6. "acka(es +ae t*o t/'es.
"acka(e $'ecification
"acka(e od/
A. &n "acka(e $'ecification *e ae definin( (lobal data and also declaeobects, s)b 'o(ams *+ee as in "acka(e od/ *e ae im'lementin( s)b 'o(ams
and also 'acka(e bod/ s)b 'o(am intenall/ be+aes like a 'iate s)b 'o(am.
Packa# S)#ci3ication Snta0 :
Syntax :
3reate !r =eplace Pacage Pacage_+ame
?s/s
l!bal *ariable Declarati!n;
7/25/2019 Pl SQL k Online Material
http://slidepdf.com/reader/full/pl-sql-k-online-material 65/98
3!nstant Declarati!n;
3urs!r Declarati!n;
),pes Declarati!n;
Pr!cedure Declarati!n;
'uncti!n Declarati!n;
End;
Packa# Bo$ Snta0 :
Syntax :
3reate !r =eplace Pacage B!d,
Pacage_+ame
?s/s
Pr!cedure ?mplementati!ns;
'uncti!n ?mplementati!ns;
End;
&nokin( "acka(ed $)b'o(ams
1. 2ec "acka(eEame."oced)eEame ct)al "aametes ;J
!. $elect "acka(eEame.B)nctionEame ct)al "aametes ; fom d)alJ
Package Specifcation
Example : 3reate !r replace pacage pac1 is
pr!cedure pr1;
pr!cedure pr6;
end;
Package ody
Example : 3reate !r replace pacage b!d, pac1 is
pr!cedure pr1
7/25/2019 Pl SQL k Online Material
http://slidepdf.com/reader/full/pl-sql-k-online-material 66/98
is
begin
dbms_!utput.put_line(MTrst pr!cedureM#;
end pr1;
pr!cedure pr6
is
begin
dbms_!utput.put_line(Msec!nd pr!cedureM#;
end pr6;
end;
E$ec Pac1.pr1;
E$ec Pac6.pr6;
!. 7lo&al Va%ia&l#
1. &t is one of t+e aiable *+ic+ is )sed to define in 'acka(e s'ecificationand im'lement in 'acka(e bod/ t+at aiables ae call it as a (lobal aiables.
o Local Va%ia&l#
1. &t is one of t+e aiable *+ic+ is )sed to define in 'o(ams "oced)e,
B)nction ; and im'lement *it+ in t+e 'o(am onl/.
Package Specifcation
Example : 3reate !r replace pacage pc6 is
g number(#%022;
pr!cedure p1;
4uncti!n 41 ( a number # return number;
end;
Package ody
Example : create !r replace pacage b!d, pc6 is
pr!cedure p1
is
number(#;
7/25/2019 Pl SQL k Online Material
http://slidepdf.com/reader/full/pl-sql-k-online-material 67/98
begin
%0g/6;
dbms_!utput.put_line(#;
end p1;
4uncti!n 41( a number # return number
is
begin
return a<g;
end 41;
end;
o P%oc#$!%#s O(#%loa$in
1. Celoadin( efes to same name can be )sed fo diffeent ')'oses i.e *e
ae im'lementin( oeloadin( 'oced)es t+o)(+ 'acka(es onl/, t+ose 'oced)es+ain( same name and also diffeent t/'es of a()ments.
Package Specifcation
Example : 3reate !r replace pacage pc@ is
pr!cedure p1(a number& b number#;
pr!cedure p1($ number& , number#;
end;
Package ody
Example : 3reate !r replace pacage b!d, pc@ is
pr!cedure p1 (a number& b number#
is
c number(12#;
begin
c%0aNb;
dbms_!utput.put_line(c#;
end p1;
pr!cedure p1 ($ number& , number#
is
number(12#;
7/25/2019 Pl SQL k Online Material
http://slidepdf.com/reader/full/pl-sql-k-online-material 68/98
begin
%0$N,;
dbms_!utput.put_line(#;
end p1;
end;/
E$ec Pac.p1 ( a0I12& b0I62 #;
E$ec Pac.p1 ( $0I122& b0I622#;
o Fo%.a%$ D#cla%ation
1. <+enee *e ae callin( 'oced)es into anot+e 'oced)e t+en onl/ *e
ae )sin( fo*od declaation i.e *+enee *e ae callin( local 'oced)e into
(lobal 'oced)e fist *e m)st im'lement local 'oced)es befoe callin( ot+e*ise)se a fo*ad declaation in 'acka(e bod/.
Package Specifcation
Example : 3reate !r replace pacage pac1A is
pr!cedure p1;
end;
Package ody
Example : 3reate !r replace pacage b!d, pac1A is
pr!cedure p6;
pr!cedure p1
is
begin
p6;
end;
pr!cedure p6
isbegin
dbms_!utput.put_line(Ml!cal pr!cedureM#;
end p6;
end;
7/25/2019 Pl SQL k Online Material
http://slidepdf.com/reader/full/pl-sql-k-online-material 69/98
o T%i#%s
1. i((e is also same as stoed 'oced)e = also it *ill a)tomaticall/
inoked *+enee ML C'eation 'efomed a(ainst table o ie*.
!. +ee ae t*o t/'es of ti((es s)''oted b/ "L#$%L.
$tatement Leel i((e
?o* Leel i((e
&n $tatement Leel i((e, i((e bod/ is eec)ted onl/ once fo ML
$tatements.
&n ?o* Leel i((e, i((e bod/ is eec)ted fo eac+ and ee/ ML
$tatements.
o Syntax :
o create W !r replace X trigger trigger_name
o be4!re / a4tertrigger e8ent
o insert / update / delete !n table_name
o W 4!r eac5 r! X
o W 5ere c!nditi!n X
o W declare X
o 8ariable declarati!ns& curs!rs
o begin
o -----
o end;
2ec)tion ode in i((es
efoe $tatement Leel
efoe ?o* Leel
fte ?o* Leel
7/25/2019 Pl SQL k Online Material
http://slidepdf.com/reader/full/pl-sql-k-online-material 70/98
fte $tatement Leel
$tatement Leel i((e
&n $tatement Leel i((e, i((e bod/ is eec)ted onl/ once fo
eac+ ML $tatement. +ats *+/ (eneall/ statement leel ti((es )sed todefine t/'e based condition and also )sed to im'lement a)ditin( e'ots.
+ese ti((es does not contain ne*, old )alifies.
Q+ 1%it# a )l/sl stat#m#nt l#(#l t%i#% on #m) ta&l# not to )#%3o%m DML
O)#%ations in sat!%$a an$ s!n$a;
Program : 3reate !r replace trigger tr1 be4!re insert !r update !r delete !n tt
begin
i4 t!_c5ar(s,sdate&MDFM# in (MS)M&MSH+M#
t5en
raise_applicati!n_err!r(-6216@&Me can n!t per4!rm DKLs !n sat and sunda,M#;
end i4;
end;
Q+ 1%it# a )l/sl stat#m#nt l#(#l t%i#% on #m) ta&l# not to )#%3o%m DML
O)#%ation on last $a o3 t4# mont4;
Program : create !r replace trigger tt6 be4!re insert !r update !r delete !n tt
begin
i4 s,sdate0last_da,(s,sdate# t5en
raise_applicati!n_err!r (-62111&Me can n!t per4!rm dml !perati!ns !n lastda, M#;
end i4;
end;
T%i#% E(#nt * o% + T%i#% P%#$icat# Cla!s#sT%i#% E(#nt * o% + T%i#%
P%#$icat# Cla!s#s
7/25/2019 Pl SQL k Online Material
http://slidepdf.com/reader/full/pl-sql-k-online-material 71/98
&f /o) *ant to define m)lti'le conditions on m)lti'le tables t+en
all database s/stems )ses ti((e eents.
+ese ae insetin(, )'datin(, deletin( cla)ses
+ese cla)ses ae )sed in eit+e o* leel o statement leel
ti((es.
Syntax : i4 inserting t5enstatements;elsi4 updating t5enstatements;elsi4 deleting t5enstatements;end i4;
Q + 1%it# a )l/sl stat#m#nt l#(#l t%i#% on #m) ta&l# not to )#%3o%m an $ml
o)#%ation in an $as !sin t%i#%in #(#nt;
Program : create !r replace trigger tr@ be4!re insert !r update !r delete !n tt
begin
i4 inserting t5en
raise_applicati!n_err!r (-62161&Me can n!t per4!rm inserting !perati!nM#;
elsi4 updating t5en
raise_applicati!n_err!r (-62166&Me can n!t per4r!m update !perati!nM#;
elsi4 deleting t5en
raise_applicati!n_err!r (-6216@&Me can n!t per4!rm deleting !perati!nM#;
end i4;
end;
Example : 3reate table test ( msg 8arc5ar6(122##;
create !r replace trigger trA a4ter insert !r update !r delete !n tt
declare
a 8arc5ar6(2#;
begin
7/25/2019 Pl SQL k Online Material
http://slidepdf.com/reader/full/pl-sql-k-online-material 72/98
i4 inserting t5en
a %0 Mr!s insertedM;
elsi4 updating t5en
a %0 Mr!s updatedM;
elsi4 deleting t5en
a %0 Mr!s deletedM;
end i4;
insert int! testt 8alues (a#;
end;
?o* Leel i((e
&n ?o* Leel i((e, i((e bod/ is eec)ted fo eac+ o* fo
ML $tatement, +ats *+/ *e ae )sin( fo eac+ o* cla)se in ti((es'ecification and also data intenall/ stoed in ! ollback se(ment )alifies
ae CL = 2<
+ese )alifies ae )sed in eit+e ti((e s'ecification o in ti((e
bod/. *+en *e ae )sin( t+ese modifies in ti((e bod/ *e m)st )se colon
'efi in t+e )alifies.
Syntax : !ld.c!lumn_name ( !r # %ne.c!lumn_name.
<+en *e ae )sin( t+ese )alifies in *+en cla)se *e ae not
allo* to )se colon infont of t+e )alifies.
%)alifie &nset 'date elete
:ne* 2$ 2$ C
:old C 2$ 2$
&n efoe i((es, i((e bod/ is eec)ted befoe ML
$tatements ae effected into database.
7/25/2019 Pl SQL k Online Material
http://slidepdf.com/reader/full/pl-sql-k-online-material 73/98
&n fte i((es, i((e bod/ is eec)ted afte ML $tatements
ae effected into database.
7eneall/ if *e *ant to estict inalid data ent/ al*a/s *e ae
)sin( befoe ti((es, *+ee as if *e ae 'efomin( o'eation on t+e one
table t+ose o'eations ae effected in anot+e table t+en *e ae )sin( afteti((e.
<+enee *e ae insetin( al)es into ne* )alifies *e m)st )se
befoe ti((e ot+e*ise oacle see et)ns an eo.
Q: 1%it# a PL/SQL Ro. L#(#l T%i#% on #m) ta&l# .4#n#(#% !s#% ins#%tin
$ata into a #m) ta&l# sal s4o!l$ &# mo%# t4an <===;
Program : 3reate !r replace trigger tG2 be4!re insert !n tb
4!r eac5 r!
begin
i4 %ne.sal222 t5en
raise_applicati!n_err!r (-6216@&Msalar, s5!uld be m!re t5an 222M#;
end i4;
end;
Q : 1%it# a PL/SQL Ro. L#(#l T%i#% on #m)> $#)t ta&l#s .4il# im)l#m#nt on$#l#t# casca$# conc#)t .it4o!t !sin on $#l#t# casca$# cla!s#;
Program : 3reate !r replace trigger t1
a4ter delete !n dept
4!r eac5 r!
begin
delete 4r!m emp 5ere deptn!0%!ld.deptn!;
end;
Q : 1%it# a PL/SQL Ro. L#(#l T%i#% on $#)t ta&l# .4#n#(#% !)$atin
$#)tno's in $#)t ta&l# a!tomaticall t4os# $#)tno's mo$i3i#$ into #m) ta&l#;
Program : 3reate !r replace trigger t1G
7/25/2019 Pl SQL k Online Material
http://slidepdf.com/reader/full/pl-sql-k-online-material 74/98
a4ter update !n dept
4!r eac5 r!
begin
update emp set deptn!0%ne.deptn! 5ere deptn!0%!ld.deptn!;
end;
Q : 1%it# a PL/SQL Ro. L#(#l T%i#% .4#n#(#% !s#% ins#%tin $ata into
#nam# col!mn a3t#% ins#%tin $ata m!st &# con(#%t#$ into !))#%cas# ;
Program : create !r replace trigger t61
be4!re insert !n emp
4!r eac5 r!
begin
%ne.ename%0upper(%ne.ename#;
end;
Q + 1%it# a PL/SQL Ro. L#(#l T%i#% on #m) ta&l# & !sin &#lo.
con$itions;
*+enee )se insetin( data t+ose al)es stoed in anot+e table
*+enee )se )'datin( data t+ose al)es stoed in anot+e table
*+enee )se deletin( data t+ose al)es stoed in anot+e table
Program : 'irst e create @ tables 5ic5 are 5a8ing t5e same structure !4 emp
table.
3reate !r replace trigger te1
a4ter insert !r update !r delete !n t21
4!r eac5 r!
begin
i4 inserting t5en
7/25/2019 Pl SQL k Online Material
http://slidepdf.com/reader/full/pl-sql-k-online-material 75/98
insert int! e1(empn!&ename# 8alues (%ne.empn!&%ne.ename#;
elsi4 updating t5en
insert int! e6(empn!&ename# 8alues (%!ld.empn!&%!ld.ename#;
elsi4 deleting t5en
insert int! e@(empn!&ename# 8alues (%!ld.empn!&%!ld.ename#;
end i4;
end;
Q : 1%it# a PL/SQL T%i#% on #m) ta&l# .4#n#(#% !s#% $#l#tin %#co%$s 3%om
#m) ta&l# a!tomaticall $is)la %#mainin n!m&#% o3 #0istin %#co%$ n!m&#%
in &ottom o3 t4# $#l#t# statm#nt;
Program : 3reate !r replace trigger tp1 a4ter delete !n emp
declare
a number(12#;
begin
select c!unt(<# int! a 4r!m emp;
dbms_!utput.put_line(Mremaining rec!rds are% Ma#;
end;
!. M)tatin( i((e
@. Example :
A. 3reate !r replace trigger tp1 a4ter delete !n emp
. 4!r eac5 r!
. declare
C. a number(12#;
. begin
7/25/2019 Pl SQL k Online Material
http://slidepdf.com/reader/full/pl-sql-k-online-material 76/98
G. select c!unt(<# int! a 4r!m emp;
12. dbms_!utput.put_line(Mremaining rec!rds are% Ma#;
11. end;
&nto a ?o* Leel i((e based on a table ti((e bod/ can not
ead data fom same table and also *e can not 'efom ML C'eations on
same table.
&f *e ae t/in( to t+is oacle see et)ns an eo is table is
m)tatin(.
+is 2o is called M)tatin( 2o
+is i((e is called M)tatin( i((e
+is able is called M)tatin( able
M)tatin( 2os ae not acc)ed in $tatement Leel i((e
eca)se t+o)(+ t+ese $tatement Leel i((e *+en *e ae 'efomin(
ML C'eations a)tomaticall/ data ommitted into database.
<+ee as in ?o* Leel i((e *+en *e ae 'efomin(
tansaction data is not committed and also a(ain *e ae eadin( t+is datafom t+e same table t+en onl/ m)tatin( eo is acc)ed.
o aoid t+is m)tatin( eo *e ae )sin( a)tonomo)s tansaction
in ti((es.
16. Example : 3reate !r replace trigger tp1 a4ter delete !n t21
1@. 4!r eac5 r!
1A. declare
1. pragma aut!n!m!us_transacti!n;
1. a number(12#;
1C. begin
1. select c!unt(<# int! a 4r!m t21;
1G. dbms_!utput.put_line(Mremaining rec!rds are% Ma#;
7/25/2019 Pl SQL k Online Material
http://slidepdf.com/reader/full/pl-sql-k-online-material 77/98
62. c!mmit;
61. end;
L i((es
<e can also ceate ti((es on sc+ema leel, database leel. +ese
t/'es of ti((es ae called L i((es o $/stem i((es.
+ese t/'es of ti((es ae ceated b/ database administato.
Syntax :3reate !r replace trigger trigger_nameBe4!re / 4ter3reate / lter / Dr!p / )runcate / =enameOn Hsername.Sc5ema
Q : 1%it# a PL/SQL T%i#% on scott sc4#ma not to $%o) #m) ta&l#;
Program : 3reate !r replace trigger td
be4!re dr!p !n apps.sc5ema
begin
i4 !ra_dict_!b_name 0 M)122M and
!ra_dict_!b_t,pe 0 M)BLEM t5en
raise_applicati!n_err!r(-62161&Me can n!t dr!p t5is tableM#;
end i4;
end;
Coll#ctions
1. Cacle see s)''ots follo*in( t/'es
"L#$%L ?ecod o ; ?ecod /'e
&nde b/ table o ; "L#$%L table o ; ssociatie
a/s.
ested tables
7/25/2019 Pl SQL k Online Material
http://slidepdf.com/reader/full/pl-sql-k-online-material 78/98
Daa/s
?ef )sos
1. &nde / able
+is is an )se defined t/'e *+ic+ is )sed to stoe
m)lti'le data items in to a sin(le )nit. asicall/ t+is is an
)nconstaint table
7eneall/ t+ese tables ae )sed to im'oe
'efomance of a''lications beca)se t+ese tables ae stoed inmemo/ aea t+ats *+/ t+ese tables ae also called as memo/
tables.
asicall/ t+ese table contains ke/ al)e 'ais i.e
al)e field is stoed in act)al data and ke/ field stoed inindees.
Ne/ field al)es ae eit+e inte(e o c+aacte and
also t+ese al)es ae eit+e -e o Oe.
+ese indees ke/ be+aes like a 'ima/ ke/ i.e
does not acce't d)'licate and n)ll al)es. basicall/ t+is ke/
datat/'e is bina/Einte(e.
&nde b/ table +ain( follo*in( collection met+ods.
eists
fist
last
'io
net
co)nt
delete an(e of indees ;
6. Example 1 :
7/25/2019 Pl SQL k Online Material
http://slidepdf.com/reader/full/pl-sql-k-online-material 79/98
@. declare
A. t,pe t1 is table !4 number(12#
. inde$ b, binar,_integer;
. 8_t t1;
C. begin
. 8_t(1#%012;
G. 8_t(6#%062;
12. 8_t(@#%0@2;
11. 8_t(A#%0A2;
16. 8_t(#%02;
1@. dbms_!utput.put_line(8_t(@##;
1A. dbms_!utput.put_line(8_t.Trst#;
1. dbms_!utput.put_line(8_t.last#;
1. dbms_!utput.put_line(8_t.pri!r(@##;
1C. dbms_!utput.put_line(8_t.ne$t(A##;
1. dbms_!utput.put_line(8_t.c!unt#;
1G. dbms_!utput.put_line(8_t(##;
62. end;
61. Example 2:
66. declare
6@. t,pe t1 is table !4 number(12#
6A. inde$ b, binar,_integer;
6. 8_t t1;
6. begin
7/25/2019 Pl SQL k Online Material
http://slidepdf.com/reader/full/pl-sql-k-online-material 80/98
6C. 8_t(1#%012;
6. 8_t(6#%062;
6G. 8_t(@#%0@2;
@2. 8_t(A#%0A2;
@1. 8_t(#%02;
@6. dbms_!utput.put_line(8_t.c!unt#;
@@. 8_t.delete(6&@#;
@A. dbms_!utput.put_line(8_t.c!unt#;
@. 8_t.delete;
@. dbms_!utput.put_line(8_t.c!unt#;
@C. end;
Q : 1%it# a PLSQL )%o%am to #t all #m)lo## nam#s 3%om #m)
ta&l# an$ sto%# it into in$#0 & ta&l# an$ $is)la $ata 3%om in$#0
& ta&l#;
Program :
declare
t,pe t1 is table !4 8arc5ar6(12#
inde$ b, binar,_integer;
8_t t1;
curs!r c1 is select ename 4r!m emp;
n number(#%01;
begin
!pen c1;
l!!p
4etc5 c1 int! 8_t(n#;
7/25/2019 Pl SQL k Online Material
http://slidepdf.com/reader/full/pl-sql-k-online-material 81/98
e$it 5en c19n!t4!und;
n%0nN1;
end l!!p;
cl!se c1;
4!r i in 8_t.Trst..8_t.last
l!!p
dbms_!utput.put_line(8_t(i##;
end l!!p;
end;
Program :
declare
t,pe t1 is table !4 8arc5ar6(12#
inde$ b, binar,_integer;
8_t t1;
begin
select ename bul c!llect int! 8_t 4r!m emp;
4!r i in 8_t.Trst..8_t.last
l!!p
dbms_!utput.put_line(8_t(i##;
end l!!p;
end;
Program :
declare
t,pe t1 is table !4 date
7/25/2019 Pl SQL k Online Material
http://slidepdf.com/reader/full/pl-sql-k-online-material 82/98
inde$ b, binar,_integer;
8_t t1;
begin
4!r i in 1..12
l!!p
8_t(i#%0s,sdateNi;
end l!!p;
4!r i in 8_t.Trst..8_t.last
l!!p
dbms_!utput.put_line(8_t(i##;
end l!!p;
end;
Q : 1%it# a PLSQL P%o%am to %#t%i#(# all ?oinin $at#s 3%om
#m) ta&l# an$ sto%# it into in$#0 & ta&l# an$ $is)la cont#nt
3%om in$#0 & ta&l#;
Program :
declare
t,pe t1 is table !4 date
inde$ b, binar,_integer;
8_t t1;
begin
select 5iredate bul c!llect int! 8_t 4r!m emp;
4!r i in 8_t.Trst..8_t.last
l!!p
dbms_!utput.put_line(8_t(i##;
7/25/2019 Pl SQL k Online Material
http://slidepdf.com/reader/full/pl-sql-k-online-material 83/98
end l!!p;
end;
Example :
declare
t,pe t1 is table !4 8arc5ar6(12#
inde$ b, 8arc5ar6(12#;
8_t t1;
$ 8arc5ar6(12#;
begin
8_t(MaM#%0 M=H+M;
8_t(MbM#%0 MRFM;
8_t(McM#%0 MB7?M;
$ %0MaM;
l!!p
dbms_!utput.put_line(8_t($##;
$ %0 8_t.ne$t($#;
e$it 5en $ is null;
end l!!p;
end;
Example :
declare
t,pe t1 is table !4 emp9r!t,pe
inde$ b, binar,_integer;
8_t t1;
7/25/2019 Pl SQL k Online Material
http://slidepdf.com/reader/full/pl-sql-k-online-material 84/98
$ number(#;
begin
select < bul c!llect int! 8_t 4r!m emp;
$%01;
l!!p
dbms_!utput.put_line(8_t($#.empn!M&M8_t($#.ename#;
$%08_t.ne$t($#;
e$it 5en $ is null;
end l!!p;
end;
( O= #
Example :
declare
t,pe t1 is table !4 emp9r!t,pe
inde$ b, binar,_integer;
8_t t1;
begin
select < bul c!llect int! 8_t 4r!m emp;
4!r i in 8_t.Trst..8_t.last
l!!p
dbms_!utput.put_line(8_t(i#.empn!M&M8_t(i#.ename#;
end l!!p;
end;
7/25/2019 Pl SQL k Online Material
http://slidepdf.com/reader/full/pl-sql-k-online-material 85/98
!. ested ables
+is is also )se defined t/'e *+ic+ is )sed to stoe
m)lti'le data items in a sin(le )nit b)t befoe *e ae stoin(act)al data *e m)st initialiFe t+e data *+ile )sin( const)cto.
Iee const)cto name is same as t/'e name.
7eneall/ *e ae not allo* to stoe inde b/ tables
'emanentl/ into database, to oecome t+is 'oblem t+e/ aeintod)ce ested ables to etension of t+e inde b/ tables.
+ese )se defined t/'es stoed 'emanentl/ into
database )sin( sl.
&n &nde b/ tables *e can not add o emoe t+e
indees. *+ee as in ested tables *e can add o emoe t+e
indees )sin( 2tend, im collection met+ods.
&n ested tables *e can allocate t+e memo/
e'licitl/ *+ile )sin( 2tend met+od
S,nta$ % ),pe t,pe_name is )able !4 datat,pe( sie #;8ariable_name t,pe_name( #; Y 3!nstruct!r_+ame
Example :
Declare
t,pe t1 is table !4 number(12#;
8 t1%0t1(#;
begin
8.e$tend(122#;
8(122#%012;
dbms_!utput.put_line(8(122##;
end;
Example :
Declare
7/25/2019 Pl SQL k Online Material
http://slidepdf.com/reader/full/pl-sql-k-online-material 86/98
t,pe t1 is table !4 number(12#;
81 t1%0t1(12&62&@2&A2&2#;
begin
dbms_!utput.put_line(81.Trst#;
dbms_!utput.put_line(81.last#;
dbms_!utput.put_line(81.pri!r(@##;
dbms_!utput.put_line(81.ne$t(@##;
dbms_!utput.put_line(81.c!unt#;
dbms_!utput.put_line(81(@##;
4!r i in 81.Trst..81.last
l!!p
dbms_!utput.put_line(81(i##;
end l!!p;
end;
Example :
Declare
t,pe t1 is table !4 number(12#;
81 t1;
86 t1%0t1(#;
begin
i4 81 is null t5en
dbms_!utput.put_line(M81 is nullM#;
else
dbms_!utput.put_line(M81 is n!t nullM#;
7/25/2019 Pl SQL k Online Material
http://slidepdf.com/reader/full/pl-sql-k-online-material 87/98
end i4;
i4 86 is null t5en
dbms_!utput.put_line(M86 is nullM#;
else
dbms_!utput.put_line(M86 is n!t nullM#;
end i4;
end;
Example :
Declare
t,pe t1 is table !4 number(12#;
8 t1%0t1(#;
begin
8.e$tend;
8(1#%0;
dbms_!utput.put_line(8(1##;
end;
Q : 1%it# a PLSQL )%o%am to #t all #m)lo## nam#s 3%om #m)
ta&l# an$ sto%# it into "#st#$ Ta&l# an$ $is)la $ata 3%om "#st#$
Ta&l#;
Program
declare
t,pe t1 is table !4 8arc5ar6(12#;
8 t1%0t1(#;
curs!r c1 is select ename 4r!m emp;
n number(12#%01;
7/25/2019 Pl SQL k Online Material
http://slidepdf.com/reader/full/pl-sql-k-online-material 88/98
begin
4!r i in c1
l!!p
8.e$tend(#;
8(n#%0i.ename;
n%0nN1;
end l!!p;
4!r i in 8.Trst..8.last
l!!p
dbms_!utput.put_line(8(i##;
end l!!p;
end;
( O= #
Program :
declare
t,pe t1 is table !4 8arc5ar6(12#;
8 t1%0t1(#;
begin
select ename bul c!llect int! 8 4r!m emp;
4!r i in 8.Trst..8.last
l!!p
dbms_!utput.put_line(8(i##;
end l!!p;
end;
7/25/2019 Pl SQL k Online Material
http://slidepdf.com/reader/full/pl-sql-k-online-material 89/98
Program :
declare
t,pe t1 is table !4 emp9r!t,pe;
8 t1%0t1(#;
begin
select < bul c!llect int! 8 4r!m emp;
4!r i in 8.Trst..8.last
l!!p
dbms_!utput.put_line(8(i#.empn!M&M8(i#.enameM&M8(i#.!b#;
end l!!p;
end;
Daa/s
+is is also )se defined t/'e *+ic+ is )sed to stoe
m)lti'le data items in a sin(le )nit b)t befoe *e ae stoin(act)al data *e m)st initialiFe t+e data *+ile )sin( const)cto.
+ese )se defined t/'es stoed 'emanentl/ into
database )sin( sl.
asicall/ *e ae )sin( t+e Daa/s fo etiein( t+e
+)(e data.
S,nta$ % ),pe t,pe_name is 8arra,( ma$sie # !4datat,pe( sie #;*ariable_name ),pe_name %0 ),pe_name( #;
Program :
Declare
t,pe t1 is 8arra,(2# !4 emp9r!t,pe;
8 t1%0t1(#;
begin
7/25/2019 Pl SQL k Online Material
http://slidepdf.com/reader/full/pl-sql-k-online-material 90/98
select < bul c!llect int! 8 4r!m emp;
4!r i in 8.Trst..8.last
l!!p
dbms_!utput.put_line(8(i#.empn!M&M8(i#.enameM&M
8(i#.!b#;
end l!!p;
end;
iffeence b#* &nde b/ able, ested able,
Daa/s
In$#0 & Ta&l# "#st#$ Ta&l# Va%%as
&t is not stoed 'emanentl/
in database.
<e can not add o emoe
indees.
&ndees statin( fom
ne(atie to 'ositien)mbes and also +ain(
ke/ al)e 'ais.
&t is stoed 'emanentl/
in database b/ )sin(
sl.
<e can add o emoeindees )sin(
etend, timmet+od.
&ndees statin( fom 1.
&t is stoed
in datab
sl.
<e can addindees
etend,
&ndees sta
)lk Mec+anism
)lk is one of t+e met+od *+ic+ is )sed to im'oe
t+e 'efomance of t+e a''lications.
Cacle intod)ce b)lk bind 'ocess )sin( collection
i.e in t+is 'ocess *e ae ')ttin( all sl statement elated
al)es into collection and in t+is collection *e ae 'efomin(
inset, )'date, delete at a time )sin( fo all statement.
7/25/2019 Pl SQL k Online Material
http://slidepdf.com/reader/full/pl-sql-k-online-material 91/98
&n t+is b)lk *e +ae t*o actions
1. )lk ollect
!. )lk ind
1. )lk ollect
1. &n t+is cla)se *e ae )sed to fetc+ t+e data fom
eso)ce into collection
!. +is cla)ses )sed in
1. $elect ...........into............cla)se
!. )so...........Betc+...........$tatement
3. ml............?et)nin(.........la)ses
1. )lk ollect )sed in select .....into.....cla)se Syntax : select < bul c!llect int!c!llecti!n_name 4r!m table_name.
6. Program :
@. Declare
A. t,pe t1 is table !4 emp9r!t,pe
. inde$ b, binar,_integer;
. 8 t1;
C. begin
. select < bul c!llect int! 8 4r!m emp;
G. 4!r i in 8.Trst..8.last
12. l!!p
11. dbms_!utput.put_line(8(i#.empn!M&M
8(i#.enameM&M8(i#.!b#;
16. end l!!p;
7/25/2019 Pl SQL k Online Material
http://slidepdf.com/reader/full/pl-sql-k-online-material 92/98
1@. end;
14. )lk ollect )sed inc)so......fetc+.......statement Syntax : 4etc5curs!r_name bul c!llect int! c!llecti!n_8ariable.
1. Program
1. Declare
1C. t,pe t1 is table !4 8arc5ar6(12#
1. inde$ b, binar,_integer;
1G. 81 t1;
62. 86 t1;
61. curs!r c1 is select ename&!b 4r!m emp;
66. begin
6@. !pen c1;
6A. 4etc5 c1 bul c!llect int! 81&86;
6. cl!se c1;
6. 4!r i in 81.Trst..81.last
6C. l!!p
6. dbms_!utput.put_line(81(i#M&M86(i##;
6G. end l!!p;
@2. end;
ime "o(am *it+ o)t LN
Declare
7/25/2019 Pl SQL k Online Material
http://slidepdf.com/reader/full/pl-sql-k-online-material 93/98
8r! 8arc5ar6(2#;
curs!r c1 is select !bect_name 4r!m
all_!bects;
1 number(12#;
6 number(12#;
begin
1%0dbms_utilit,.get_time;
!pen c1;
l!!p
4etc5 c1 int! 8r!;
e$it 5en c19n!t4!und;
end l!!p;
cl!se c1;
6%0dbms_utilit,.get_time;
dbms_!utput.put_line(1#;
dbms_!utput.put_line(6#;
dbms_!utput.put_line(6-1#;
end;
ime "o(am *it+ LN
Declare
t,pe t1 is table !4 8arc5ar6(2# inde$ b,
binar,_integer;
81 t1;
7/25/2019 Pl SQL k Online Material
http://slidepdf.com/reader/full/pl-sql-k-online-material 94/98
curs!r c1 is select !bect_name 4r!m
all_!bects;
1 number(12#;
6 number(12#;
begin
1%0dbms_utilit,.get_time;
!pen c1;
l!!p
4etc5 c1 bul c!llect int! 81;
e$it 5en c19n!t4!und;
end l!!p;
cl!se c1;
6%0dbms_utilit,.get_time;
dbms_!utput.put_line(1#;
dbms_!utput.put_line(6#;
dbms_!utput.put_line(6-1#;
end;
31. )lk ollect )sed in ML..........?et)nin(
cla)ses. Syntax : dml statement returningc!lumn_name int! 8ariable_name;
@6. Example :
@@. *ariable a 8arc5ar6(12#;
@A. Hpdate emp set sal0salN122 5ere ename
0M?+M returning !b int! %a;
@. Print a;
7/25/2019 Pl SQL k Online Material
http://slidepdf.com/reader/full/pl-sql-k-online-material 95/98
% : <ite a "L$%L $toed "oced)e modif/ salaies
of t+e clek fom em' table and also t+ese modified
al)e immediatel/ stoed into inde b/ table b/ )sin(dml ...et)nin( cla)se and also dis'la/ content fom
inde b/ tableP
Program :
3reate !r replace pr!cedure p1 is
t,pe t1 is table !4 emp9r!t,pe
inde$ b, binar,_integer;
81 t1;
begin
update emp set sal0salN122 5ere
!b0M3LE=M
returning
empn!&ename&!b&mgr&5iredate&sal&c!mm&deptn!
bul c!llect int! 81;
dbms_!utput.put_line(Mupdated n!%!4 clers
are%Msl9r!c!unt#;
4!r i in 81.Trst..81.last
l!!p
dbms_!utput.put_line(81(i#.enameM&M
81(i#.!bM&M81(i#.sal#;
end l!!p;
end;
!. )lk ind
1. &n b)lk bind 'ocess *e ae 'efomin( b)lk of
o'eations )sin( collection i.e in t+is 'ocess *e ae
)sin( b)lk )'date, b)lk delete, b)lk inset )sin( foallstatement.
7/25/2019 Pl SQL k Online Material
http://slidepdf.com/reader/full/pl-sql-k-online-material 96/98
!. efoe *e ae )sin( b)lk bind 'ocess *e ae fetc+in(
data fom database into collections )sin( b)lk collect
cla)se.
Syntax : 4!rall inde$8ar in c!llecti!n8ar.4rist..c!llecti!n8ar.last
Example :
Declare
t,pe t1 is 8arra,(12# !4 number(12#;
81 t1%0t1(12&62#;
begin
4!rall i in 81.Trst..81.last
update emp set sal0salN122 5ere deptn!081(i#;
end;
B!lk U)$at#
Program :
Declare
t,pe t1 is table !4 number(# inde$ b, binar,_integer;
81 t1;
begin
select empn! bul c!llect int! 81 4r!m emp;
4!rall i in 81.Trst..81.last
update emp set sal0salN111 5ere empn!081(i#;
end;
B!lk D#l#t#
Program :
Declare
7/25/2019 Pl SQL k Online Material
http://slidepdf.com/reader/full/pl-sql-k-online-material 97/98
t,pe t1 is 8arra,(12# !4 number(12#;
81 t1%0t1(62&@2&A2#;
begin
4!rall i in 81.Trst..81.last
delete 4r!m emp 5ere empn!081(i#;
end;
B!lk Ins#%t
Program :
Declare
t,pe t1 is table !4 number(12# inde$ b, binar,_integer;
81 t1;
begin
4!r i in 1..122
l!!p
81(i#%0i;
end l!!p;
4!rall i in 81.Trst..81.last
insert int! bt 8alues (81(i##;
end;
7/25/2019 Pl SQL k Online Material
http://slidepdf.com/reader/full/pl-sql-k-online-material 98/98
o'/i(+t Q ***.k)nc+ams.comesi(ned and eelo'ed b/ N)nc+am Cnline ainin( "t. Ltd.