BANK PROJECT AND PL/SQL KCB_ACC_TAB --------------------- 1 create table kcb_acc_tab 2 ( 3 acc! "#ber $r%#ar& ke&' a#e )arc*ar2(2+, )arc*ar2(2+, c!tra%t c!tra%t a#e_ !t "ll' . act&$e c*ar c*eck(act&$e %('c'0,,' !! ate e0a"lt &ate' &ate' bal "#ber(4'2, "#ber(4'2, !t !t "ll 45 , QL6 / Table Table create7 create7 QL6 %ert %t! % t! kcb_acc_tab )al"e(3++21.3'r%%)a )al"e(3++21.3'r%%)a''&ate'1.+++, ''&ate'1.+++, 2 / r!8 create7 QL6 c!##%t 2 / c!##%t c!#$lete7 KCB_TRAN_TAB ------------------------create table kcb_tra_tab ( t% "#ber' acc! "#ber(2+, re0erece re0erece kcb_acc_tab(acc!,' trt&$e c*ar(1+, c*eck(trt&$e %('8,,' !t ate e0a"lt &ate' a#t "#ber('2, c*eck(a#t61++, , SEQ9ENCE ----------------create e:"ece 1 tart 8%t* 1 %cre#et b& 1 #a;)al"e 1+++ #%)al"e + !cac*e !c&cle 1,
eclare % kcb_acc_tab>r!8t&$e? kcb_acc_tab>r!8t&$e? k kcb_tra_tab>r!8t&$e? be=% %7acc!@acc!? k7trt&$e@trt&$e? k7a#t@a#!"t? elect bal %t! %7bal 0r!# kcb_acc_tab 8*ere acc!%7acc!? %0 k7trt&$eD t*e %7bal@%7balk7a#t? e %0? "$ate kcb_acc_tab et bal%7bal 8*ere acc!%7acc!? %ert %t! kcb_tra_tab )al"e(17e;t)al'%7acc!'k7tr )al"e(17e;t)al'%7acc!'k7trt&$e'&ate t&$e'&ate'k7a#t,? 'k7a#t,? c!##%t? e? 2, 8r%te a PL/SQL $r!=ra# 0!r eter t*e traact%! eta%l $er0!r# t*e )al%at%! %,%0 %t % e$!%te "$ate t*e bal a %ert t*e traact%! eta%l %%, %0 %t % 8%t*ra8 be0!re 8%t*ra8 c*eck t*e c"rret bal %0 )al%at%!c!tr!l at%0& t*e !l& $er0!r# t*e 8%t*ra8 eclare % kcb_acc_tab>r!8t&$e? kcb_acc_tab>r!8t&$e? k kcb_tra_tab>r!8t&$e? be=% %7acc!@acc!? k7trt&$e@trt&$e? k7a#t@a#t? elect act&$e'bal %t! %7at&$e'%7balace 0r!# kcb_acc_tab 8*ere acc!%7acc!? %0 k7trt&$eD t*e %7bal@%7balk7a#t? ele %7bal@%7bal-k7a#t? %0 %7act&$e a %7bal.+++ t*e Ra%e_a$$l%cat%!_err!r(-2+.'t*e Ra%e_a$$l%cat%!_er r!r(-2+.'t*e bal % t!! l!8 t! $er0!r# traact%!,? e%0? "$ate kcb_acc_tab et bal%7bal 8*ere acc!%7acc!? %ert %t! kcb_tra_tab )al"e(17e;t)al'%7acc!'k7tr )al"e(17e;t)al'%7acc!'k7trt&$e'&ate t&$e'&ate'k7a#t,? 'k7a#t,? c!##%t? e? PROCED9RE -------------------create !r re$lace $r!ce"re "$_bal ($acc! kcb_acc_tab7acc!>t kcb_acc_tab7acc!>t&$e' &$e' $a#t kcb_tra_tab7a#t>t&$e, kcb_tra_tab7a#t>t&$e, % cbal kcb_acc_tab7bal>t&$e kcb_acc_tab7bal>t&$e?? be=% elect bal %t! cbal 0r!# kcb_acc_tab 8*ere 8*ere acc!$acc!?
cbal@cbal$a#t? "$ate kcb_acc_tab et balcbal 8*ere acc!$acc!? %ert %t! kcb_tra_tab )al"e(1++1'$acc!''&ate'$a#t,? c!##%t? e;ce$t%! 8*e !_ata_0!" t*e %$la&($acc!% !t e;%t,? e "$_bal? create !r re$lace $r!ce"re "$_bal ($acc! kcb_acc_tab7acc!>t&$e' $a#t kcb_tra_tab7a#t>t&$e, % cbal kcb_acc_tab7bal>t&$e? )at&$e kcb_acc_tab7at&$e>t&$e? be=% elect acct&$e'bal %t! )at&$e'cbal 0r!# kcb_acc_tab 8*ere acc!$acc!? %0 "$$er($tt&$e, t*e cbal@cbal$a#t? el%0 "$$er($tt&$e,8 t*e cbal@cbal-$a#t? %0 )al"e a cbal.+++ t*e Ra%e_a$$l%cat%!_err!r(-2+.'t*ere % %"Fc%et balace ! 8e ca!t ! t*e traact%!@,? e %0? e %0? "$ate kcb_acc_tab et bal cbal 8*ere acc!$acc!? %ert %t! kcb_tra_tab )al"e(1+1'$acc!'$trt&$e'&ate'$a#t,? c!##%t? e;ce$t%! 8*e !_ata_0!" t*e %$la&($acc!% !t e;%t,? e "$_bal? G9NCTHONS -----------------8r%te a 0"ct%! t*e acc!"t *!ler % el%=%ble 0!r t*e 8%t*ra8 !r !t create !r re$lace 0"ct%! c*k_bal ($acc! kcb_acc_tab7acc!>t&$e' $a#t kcb_tra_tab7a#t>t&$e, ret"r b!!lea % cbal kcb_acc_tab7bal>t&$e? )at&$e cb_acc_tab7acct&$e>t&$e? be=% elect acct&$e'bal %t! )acct&$e'cbal 0r!# kcb_acc_tab 8*ere acc!$acc!? cbal@cbal-$a#t? %0 )acct&$e a cbal.+++ t*e ret"r(0ale,?
el%0 )at&$eca cbal1++++ t*e ret"r(0ale,? ele ret"r(tr"e,? e %0? e c*k_bal?
call t*% 0"ct%! 8%t* a!t*er $l/:l $=# 8%t* a$$r!$r%ate #=7 be=% %0 c*k_bal(acc!'a#t,t*e %$la&(%t % )al%ate,? ele %$la&(%t % !t )al%ate,? e %0? e? call t*% 0"ct%! % a $r!ce"re 0!r t*e )al%at%! create !r re$lace $r!ce"re "$_bal ($acc! kcb_acc_tab7acc!>t&$e' $trt&$e kcb_tra_tab7trt&$e>t&$e' $a#t kcb_acc_tab7a#t>t&$e, % cbal kcb_acc_tab7bal>t&$e? be=% elect bal %t! cbal 0r!# kcb_acc_Tab 8*ere acc!$acc!? %0 "$$er($trt&$e,D t*e cbal@cbal$a#t? el%0 "$$er($trt&$e,8 t*e %0 c*k_bal($acc!'$a#t,t*e cbal@cbal-$a#t? ele Ra%e_a$$l%cat%!_err!r(-2+.'T*ere HB ! 8e ca!t ! t*e traact%!@,? e %0? e %0? "$ate kcb_acc_tab et balcbal8*ere acc!$acc!? %ert %t! kcb_tra_tab )al"e(1+1'$acc!'$trt&$e'&ate'$a#t,? c!##%t? e;ce$t%! 8*e !_ata_0!" t*e %$la&($acc!% !t e;%t,? e "$_bal? PACKAIES ---------------PACKAIE SPECHGHCATHON -------------------------------------create !r re$lace $acka=e $ack_"$bal
a cbal bak#ater7c"rr_bal>t&$e? $r!ce"re "$_bal()acc! kc b_acc_tab7acc!>t&$e' )t&$e kcb_tra_tab7tt&$e>t&$e' )a#t kcb_tra_tab7a#t>t&$e,? 0"ct%! c*k_bal()acc! kcb_acc_tab7acc!>t&$e' )a#t kcb_tra_tab7a#t>t&$e, ret"r b!!lea? cbal7kcb_acc_tab7bal>t&$e? e $ack_"$bal? PACKAIE BOD -----------------------create !r re$lace $acka=e b!& $ack_"$bal a $r!ce"re "$_bal()acc! kcb_acc_tab7acc!>t&$e' )trt&$e kcb_tra_tab7trt&$e>t&$e' )a#t kcb_tra_tab7a#t>t&$e, % be=% elect bal %t! cbal 0r!# kcb_acc_tab 8*ere acc!)acc!? %0 "$$er()t&$e,8 t*e cbal@cbal_)a#t? e %0? "$ate kcb_acc_tab et alcbal 8*ere acc!)acc!? c!##%t? e "$_bal? 0"ct%! c*k_bal()acc! kcb_acc_tab7acc!>t&$e' )a#t kcb_tra_tab7a#t>t&$e, ret"r b!!lea % )at&$e kcb_acc_tab7acct&$e>t&$e? be=% elect acct&$e'bal %t! )at&$e'cbal 0r!# kcb_acc_tab 8*ere acc!)acc!? cbal@cbal-)a#t? (=l!bal )ar%able, %0 )at&$e a cbal.+++ t*e ret"r(0ale,? el%0 )at&$ec a cbal1++++ t*e ret"r(0ale,? ele ret"r(tr"e,? e %0? e c*k_bal? e $ack_"$bal? Tr%==er ----------create !r re$lace tr%==er tr=_bal be0!re %ert ! kcb_tra_tab
0!r eac* r!8 be=% %0 @e87trt&$e t*e $ack_"$bal7"$_bal(@e87acc!'@e87trt&$e'@e87a#t,? el%0 @e87trt&$e8 t*e %0 $ack_"$bal7c*k_bal(@e87acc!'@e87a#t,t*e $ack_"$bal7"$_bal(@e87acc!'@e87trt&$e'@e87a#t,? ele Ra%e_a$$l%cat%!_err!r(-2+.1't*e bal % t!! l!8 ! ! traact%!@,? e %0? e %0? e;ce$t%! 8*e !_ata_0!" t*e %$la&(@e87acc!% !t e;%t,? e? PL/SQL@ Ht % a $r!=ra##%= la="a=e 8*%c* % e)el!$e b& !racle c!#$a&7 Ht % a $r!ce"ral la="a=e %t % "e t! $r!ce !l& a r!8 at a t%#e 8*ere a ! $r!ce"ral la="a=e $r!ce a et !0 r!8 at a t%#e7 Ht "$$!rt t! e;ec"te a bl!c !0 t#t at !ce7 Bl!ck@ c!llect%! !0 e;ec"table tate#et7 tr"t"re !0 bl!ck@ Declare )ar%able Declarat%!? Be=% e;ec"table tate#et6? e;ce$t%! e;ec"table tate#et? E? T*ere are t8! t&$e !0 bl!ck H, A!!"# bl!ck HH, a#e bl!ck A!#!" Blc!k@ T*e Bl!ck 8*%c* % *a)%= ! a#e calle a a!#!" Bl!ck T*% bl!ck ca!t call a& !t*er $r!=ra#7 "e % D2K 0!r#7 Na#e Bl!ck@ T*e Bl!ck 8*%c* % *a)%= a a#e calle a a#e bl!ck7 T*% bl!ck ca call % !t*er PL/SQL $r!=ra#7 e=@ $r!ce"re 0"ct%! Tr%==er $acka=e PL/SQL "$$!rt t*e )ar%ablec!tra%t SQL 8%ll "$$!rt t*e B% )ar%able !l&7 e=M? )ar a "#ber
e;ec a@1+++ $r%t @a PL/SQL 8%ll "$$!rt b% )ar%able l%t )ar%able7 Ht "$$!rt t*e Err!r *al%=7 H SQL 8e ca ee t*e err!r ! t*e $r!=ra# Or elect t#t' B"t 8e ca!t *ale$r!)%e t*e !l"t%!7 <*ere a % PL/SQL 8e ca *ale t*at err!r a $r!)%e t*e A$$r!$r%ate act%!7 Ht "$$!rt c!%t%!al c!tr"ct7 Ht "$$!rt t*e Hterat%! c!tr!l %,%#$le l!!$ %%,8*%le l!!$ %%%,0!r l!!$ Ht "$$!rt t*e "b $r!=ra# T*ere are T8! t&$e !0 "b $r!=ra#@ %,0"ct%! %%,$r!e"re EI@ eclare Be=% "ll? e? Data t&$e % PL/SQL@ Scalar C!#$!%te E=@ Table Rec!r )arra& Re0erece@ Re0 c"r!r Re0 !bect_t&$e LOB ar%able@ )ar%able are "e t! t!re ata)al"e t*at are "e b& $l/:l )ar%able are re$reet #e#!r& l!cat%! "e t! t!re "er !r atabae ata7 )ar%able "$$!rt t*e %#$le ata t&$e a c!#$!%te ata t&$e7 !t )ar%able are "$$!rt t*e B!!lea Datat&$e 8*ere a B% )ar%able are !t "$$!rt t*e B!!lea Datat&$e7 S&ta;@ )ar%able a#e6 atat&$e(%e,? eclarat%! $art !l& " eclare t*e )ar%able7
e=@ eclare )_e#$! "#ber(,@+2? )_a#e )arc*ar2(2+, !t "ll? !te@ 8e *!"l !t a%= t*e "ll )al"e7 A%=#et !$erat!r@ Ht!@ T*% !$erat!r 0!r %teral )al"e @ T*% !$erat!r a& e;teral )al"e7 E;ec"table "b la="a=e are@ DQL DL TCL
1 eclare 2 )e#$! "#ber(,@e#$!? 3 )ea#e )arc*ar2(2+,? )al "#ber('2,? . )c!## "#ber('2,? etal "#ber('2,? be=% 4 elect ea#e'al'c!## %t! )ea#e')al')c!## 0r!# e#$ 8*ere e#$!)e#$!? 1+ etal@)al)l()c!##'+,? 11 b#_!"t$"t7$"t_l%e(ea#e al c!## etal,? 12 b#_!"t$"t7$"t_l%e(r$a()ea#e', r$a()al', r$a()c!##', 135 e? Nete Bl!ck@ PL/SQL bl!ck ca be ete t*e bl!ck 8*%c* % eclare8 % a!t*er Bl!ck calle a ete bl!ck !r %er bl!ck !r c*%l bl!ck7 Declare Be=% Declare Be=% e? e? !te@ )ar%able 0!r8ar Re0erece % $!%ble t*e back8!r re0erece #a& !t $!%ble7 1 eclare 2 # "#ber@1++? 3 be=% #@.++? . eclare "#ber@++? t!tal "#ber? 4 be=% #@++? 1+ t!tal@#? 11 b#_!"t$"t7$"t_l%e(t*e "# !0 #' %@t!tal,? 12 e? --e t*e %er bl!ck 13 b#_!"t$"t7$"t_l%e(t*e # )al"e %@#,? 15 e?
ar%able Attr%b"te@ T*ere are T8! t&$e !0 )ar%able attr%b"te7 B& "%= t*% )ar%able attr%b"te 8e ca ake t*e Datat&$e'%e %e$eetel& 0!r a )ar%able77 C!l"# T&$e Attr%b"te@
S&ta;@ )ar%able a#e6 table a#e67c!l"# a#e6>t&$e? Percet%le t&$e(>,@- "e t! eclare c!l"# t&$e )ar%able7 e=@ )ea#e e#$7ea#e>t&$e? eclare 2 )a#e e#$7ea#e>t&$e? 3 be=% elect ea#e %t! )a#e 0r!# e#$ . 8*ere e#$!e!? b#_!"t$"t7$"t_l%e(t*e ea#e@)a#e,? e?
eclare 2 )a#e e#$7ea#e>t&$e? 3 )e$t! e#$7e$t!>t&$e? be=% . elect ea#e'e$t! %t! )a#e')e$t! 0r!# e#$ 8*ere e#$!e!? b#_!"t$"t7$"t_l%e(t*e ea#e'e$t!@)a#e)e$t!,? 45 e? R!8 T&$e )ar%able attr%b"te@ Ht % "e t! eclare a rec!r t&$e )ar%able S&ta;@ )ar%able a#e6 table a#e6>r!8 t&$e? e=@ % e#$>r!8 t&$e? !te@% e#$ table *!8 #a& c!l"# are t*ere all are re$reet !r a)a%lable % % )ar%able7
1 eclare 2 % e#$>r!8t&$e? 3 be=% %7e#$!@e!? . elect ea#e'al'c!##'e$t! %t! %7ea#e'%7al'%7c!##'%7e$t! 0r!# e#$ 8*ere e#$!%7e#$!? b#_!"t$"t7$"t_l%e(t*e e#$ eta%l are@%7ea#e %7al %7c!## %7e$t!,? 45 e? create !r re$lace $r!ce"re %$la&( )arc*ar2, % be=% b#_!"t$"t7$"t_l%e(,? e? Gl!8 c!tr!l tate#et@ B& "%= !8 c!tr!l tate#et 8e ca #a%$"late a $r!ce !racle ata7
T*e cate=er& !0 !8 c!tr!l tate#et are@ c!%t%!al c!tr!l %terat%)e c!tr!l c!%t%!al c!tr!l@ e:"ece !0 tate#et ca be e;ec"te bae ! certa% c!%t%! "%= t*e %0 t#t7 T*ere are t*ree 0!r# !0 %0 tate#et7 %,HG_TEN_ENDHG HH,HG_TEN-ELSE-ENDHG HHH,HG-TEN-ELSHG-ENDHG S&ta;@ HG c!%t%! t*e e"ece !0 tate#et? e %0? eclare be=% %0 ac%%(Alle,. t*e D%$la&(%t % tr"e,? e %0? e?
&ta;@ H0 c!%t%! t*e e:"ece !0 tate#et? ele e:"ece !0 tate#et? e %0? eclare )_"# "#ber(2,@e!? be=% %0 #!()_"#'2,+ t*e %$la&()_"#% a e)e "#ber,? ele %$la&()_"#% a ! "#ber,? e %0? e? &ta;@ %0 c!%t%!6t*e e;ec"table t#t6 el%0c!%t%!6t*e e;ec"tt#t6 el%0c!%t%!6t*e e;t#t6
ele e;t#t6 e %0? eclare a)=_#ark "#ber@a)=#ark? =rae )arc*ar2(1+,? be=% %0 a)=_#ark bet8ee 4+ a 1++ t*e =rae@A? el%0 a)=_#ark bet8ee + a t*e =rae@B? el%0 a)=_#ark bet8ee + a . t*e =rae@C? el%0 a)=_#ark bet8ee + a 3 t*e =rae@D? ele =rae@"k!8? e %0? Hterat%)e c!tr!l@ %terat%)e tate#et eable &!" t! e;ec"te a =r!"$ !0 tate#et #a& t%#e7 T*ree T&$e? %#$le l!!$ 0!r l!!$ 8*%le l!!$ l!!$ e;ec"table t#t6? e l!!$? eclare "#ber@1? be=% l!!$ %$la&(,? @1? e l!!$? e? eclare "#ber@1? be=% l!!$ %$la&(,? e;%t 8*e (61+,? @1? e l!!$? e?
8*%lec!%t%!6 l!!$ e;ec"table t#t6? e l!!$? eclare "#ber(3,@1? be=% 8*%le(1+, l!!$ %$la&(,? @1? e l!!$? e? GOR l!!$_c!"ter HNRe)ere l!8er_b!"777*%=er_b!" l!!$ e;c"t t#t6? e l!!$? eclare be=% 0!r % % 1771+ l!!$ %$la&(%,? e l!!$? e? C"r!r@ c"r!r are )ar%able t*at 8e ca eUe a eclare ect%! !0 $l/:l Bl!ck7 A c"r!r % a$l/:l c!tr"ct a all!8 &!" t! a#e !0 t*e 8!rk area acce t*e%r t!re %0!r#at%! $r!ce t*e #"lt%$le Rec!r !te@ #a%$"late t*e #!re t*a !e rec!r t*at t%#e "e c"r!r t#t7 T&$e !0 c"r!r@ t8! t&$e@ 1,Stat%c c"r!r a,E;$l%c%t c"r!r b,H#$l%c%t c"r!r 2,D&a#%c c"r!r (Re0 c"r!r, E;$l%c%t c"r!r@ Ht % eclare b& t*e "er a #a%$"late b& t*e"er a )al%e !l& % $l/:l7 t*e l%0e c&cle !0 E;$l%c%t c"r!r =!e t*r!"=* 0!"r ta=e %, eclare %%,!$e %%%,0etc* %),cl!e %,Declare@ T*e c"r!r t#t % eclare e;$l%c%tel& % eclarat%)e ect%! &ta;@ Declare c"r!rc"r!r a#e6 %
electl%t !0 c!l"#6 0r!# table a#e6 8*ere c!%t%!? Be=% ------E?
SQL6 eclare 2 c"r!r c1 % 3 elect e#$!'ea#e'al'e$t! 0r!# e#$ . 8*ere e$t!2+? be=% "ll? 4 e? %%, !$e@ ere t*e :"er& e;ec"t%! !e !8 8e ca 0etc* t*e ata7 &ta;@ !$ec"r!r a#e6 %%%, 0etc*@ T*e retr%e)e !0 ata %t! t*e $l/:l )ar%able !r *!t )ar%able % !e t*r!"=* t*e 0etc* t#t7 T*e 0etc* t#t % a %#$le l!!$ t! 0etc* t*e all t*e c"r!r t#t7 &ta;@ 0etc*c"r!r a#e6 %t! )ar%able6? %), cl!e@ E;$l%c%tel& cl!e t*e c"r!r &ta;@ cl!e c"r!r a#e6 C"r!r Attr%b"te@ %, > 0!"@ %t rer"r tr"e %0 0etc* % "cce %%, > !t 0!"@ %t rer"r tr"e %0 0etc* % !t "cce %%%, > % !$e@ %t ret"r tr"e %0 c"r!r % !$e "cce0"ll& %), > r!8 c!"t@ %t ret"r !7!0 r!8 0etc*e E=@ 1 eclare c"r!r c1 % 2 elect e#$!'ea#e'al 3 0r!# e#$ 8*ere e$t!3+? . )e#$! e#$7e#$!>t&$e? )ea#e e#$7ea#e>t&$e? )al e#$7al>t&$e? 4 be=% !$e c1? 1+ l!!$ 11 0etc* c1 %t! )e#$!')ea#e')al? 12 e;%t 8*e c1>!t0!"? 13 b#_!"t$"t7$"t_l%e()e#$! )ea#e )al,? 1 e l!!$? 1. cl!e c1? 15 e?
1 eclare 2 c"r!r c_al % 3 elect e#$!'ea#e'al'!b'e$t! 0r!# e#$? . % e#$>r!8t&$e? be=% !$e c_al? 4 l!!$ 0etc* c_al %t! %7e#$!'%7ea#e'%7al'%7!b'%7e$t!? 1+ e;%t 8*e c_al>!t0!"? 11 %0 %7!bclerkt*e 12 %7al@%7al%7al5+72.? 13 el%0 %7!b#aa=ert*e 1 %7al@%7al%7al5+73.? 1. ele 1 %7al@%7al%7al5+71.? 1 e %0? 14 "$ate e#$ et al%7al 1 8*ere e#$!%7e#$!? 2+ b#_!"t$"t7$"t_l%e(r$a(%7ea#e'4,r$a(%7al',r$a(%7e$t!'1+,,? T&$e !0 e;$l%c%t c"r!r@ E;$l%c%t c"r!r 8%t* %#$le l!!$ E;$l%c%t c"r!r 8%t* 0!r l!!$ A)ata=e@ ! ee t! eclare )ar%able ! ee t! !$e t*e c"r!r e;$l%c%tel& ! ee t! 0etc* t*e ata ! ee t! e;$l%c%tel& t!$ t*e l!!$ ! ee t! cl!e t*e c"r!r &ta;@ 0!r )ar%able6 % c"r!r a#e6 l!!$ t#t? e l!!$? 1 eclare 2 c"r!r 3 c!##_c"r % elect e#$!'ea#e'al'c!##'e$t! . 0r!# e#$? be=% 0!r k % c!##_c"r 4 l!!$ %0 k7c!## % "ll t*e 1+ k7c!##@3++? 11 el%0 k7c!##+ t*e 12 k7c!##@2.+? 13 ele 1 k7c!##@k7c!##k7al5+71.? 1. e %0? 1 "$ate e#$ et c!##k7c!## 1 8*ere e#$!k7e#$!?
14 b#_!"t$"t7$"t_l%e(r$a(k7e#$!'4,k7c!## 1 k7e$t!,? 2+ e l!!$? 215 e? 1 eclare 2 c"r!r !c % 3 elect e$t!'#%(al, l!8_$a&' #a;(al, *%=*_$a&'"#(al, t!t_$a&' . c!"t(e#$!,!e 0r!# e#$ =r!"$ b& e$t!? 4 be=% 0!r % % !c 1+ l!!$ 11 b#_!"t$"t7$"t_l%e(%7e$t!%7l!8_$a&%7*%=*_$a&%7t!t_$a& %7!e,? 12 e l!!$? 135 e? H#$l%c%t c"r!r@ DeUe b& t*e !racle 8*e e)erD#l % $er0!r#e % $l/:l $r!=ra#7 H#$l%c%t c"r!r@ A %#$l%c%t c"r!r craete a erae b& a"t!#at%call&7 ---6 D"r%= t*e $r!ce !0 a %#$l%c%t c"r!r !racle a"t!#at%call& $er0!r# t*e !$erat%! l%ke !$e ' 0etc* a cl!e7 ---6 Attr%b"te are %, S:l > 0!" %%, S:l > !t 0!" %%%, S:l > % !$e %), S:l > r!8 c!"t 1 be=% 2 0!r % %(elect e#$!' ea#e'al'e$t! 0r!# e#$, 3 l!!$ %0 %7e$t!1+ t*e . %7al@%7al.++? el%0 %7e$t!2+ t*e 4 %7al@%7al++? ele 1+ %7al@%7al++? 11 e %0? 12 "$ate e#$ et al%7al 13 8*ere e#$!%7e#$!? 1 b#_!"t$"t7$"t_l%e(r$a(%7e#$!'4,%7ea#e%7al%7e$t!,? 1. e l!!$? 15 e? Re0 c"r!r@ D&a#%c c"r!r % re0 c"r!r %t re0er %Veret 8!rk area % #e#!r&7
Ht % "e t! eclare a c"r!r 8/! elect t#t7 A Re0 c"r!r ca be re"e %0 %t % eclare % $acka=e7 A Re0 c"r!r ca "$$!rt t! ret"r #!re t*a !e r!8 0r!# "b$r!=ra#7 T8! t&$e !0 &a#%c c"r!r@ H, r!8t&$e !te@ E;$l%c%t c"r!r % a tat%c c"r!r E;$l%c%t c"r!r re0er al8a& !e 8!rk area a!c%ate 8%t* t*e c"r!r7 0eat"re !0 c"r!r@ c"r!r 8%t* $ara#eter 0!r "$ate a W !0 c!l"# a#e !8 8a%t X ---6 8*ere c"rret !0 c"r!r6 ---6 r!8 % ---6 "b :"er%e $ara#etr%c c"r!r@ A c"r!r eUe 8%t* $ara#eter % calle $ara#etr%c c"r!r7 t*e e0a"lt #!e !0 $ara#eter % Y % Y7 &ta;@ c"r!r c"r!r a#e6 ($ara#eter a#e t&$e'---, % elect t#t? 8*ere c"rret !0 cla"e t! re0"e t*e c"rret rec!r a 0etc*e 0r!# t*e e;$l%c%t c"r!r7 0!r "$ate cla"e e;$l%c%tel& 0!c" t*e rec!r t!re % t*e $r%)ate 8!rk area7 C!#$!%te ata t&$e (c!llect%!, @ T*ee are t8! t&$e 8*%c* ca!t *!l a& ata $*&%call& %, PL/SQL Rec!r %%, PL/SQL Table %, PL/SQL Rec!r@ A PL/SQL Rec!r % all!8 &!" t! treat e)aral )ar%able a a "%t7 PL/SQL Rec!r are %#%lar t! tr"t"re % C &ta;@ T&$e T&$e a#e6 % rec!r (Uel a#e1' Uel a#e2---,? %%, PL/SQL Table@ Ht *!l t*e ele#et !0 t*e %#%lar atat&$e7 %t % %#%lar l%ke a arra& c!ce$t % C &ta;@
t&$e table a#e6 % table !0 ata t&$e6 %e; b& b%ar&-%te=er? *ere table a#e % c!llect%! a#e ata t&$e---6 8*at t&$e !0 ata ca $lce % a %e; %e; b&---6 $er0!r# t*e !7!0 %e;e 8*%c* ca *!l t*e ata te#$!rarl& b%ar&-%te=er---6 %t % a &te# atat&$e
E;ce$t%!@ A e;ce$t%! % $l/:l bl!ck % ra%e "r%= e;ce$t%! !0 bl!ck7 ---6 %t ter#%ate t*e #a% b!& !0 t*e act%! #ea a bl!ck al8a& ter#%ate 8*e $l/:l ra%e a e;ce$t%!7 ---6 %0 t*e e;ce$t%! % *ale t*e t*e $l/:l bl!ck ter#%ate "cce0"ll&7 T&$e !0 E;ce$t%!@ Ra%e %#$l%c%t&@ ($reeUe e;ce$t%!, T*e e;ce$t%! 8*%c* are eclare b& t*e !racle a ra%e %#$l%c%t& t*ee e;ce$t%! are calle Ra%e %#$l%c%t&7 Ra%e e;$l%c%t&@ DeUe b& t*e "er *ale b& t*e "er acc!r%= t! t*e re:"%re#et7 PreeUe e;ce$t%! l%t@ E;ce$t%! Na#e %, D"$_al_O_He; %%, H)al% c"r!r %%%, L!=%_De%e $a8!r %), N!_Data_G!" ), T!_a&_R!8 t*a !e r!8 )%, H)al%_N"#ber
E;ce$t%! Ora_+++1 Ora_1++1 Ora_+1+1
Decr%$t%! 9%:"e c!tra%t )%!late Hll%=al c"r!r !$erat%! Hcae !0 %)al% "era#e a
Ora_+1+3 Ora_122
Data % !t e;%t % t*e table A elect ---%t! t#t #atc*e #!re
!ra_122
C!)er%! t! a "#ber 0a%le
&ta;@ E;ce$t%! <*e e;ce$t%!_a#e6 T*e S:l t#t <*e e;ce$t%!_a#e6 T*e S:l t#t <*e Ot*er T*e S:l t#t Ra%e A$$l%cat%! Err!r@ Ht % b"%lt % 0"ct%! Ht % "e % $l/:l T*% eable "e t! $ec%Ue "er eUe err!r #ea=e a "$e t*e tak7 &ta;@ Ra%e_A$$l%cat%!_Err!r err!r_"#6 err!r_#=6? Err!r "# l%#%t %(-2+'+++ a -2+', 9er DeUe E;ce$t%!@ T*e e;ce$t%! 8*%c* % eUe b& "er calle a a 9er DeUe E;ce$t%!7 T*% e;ce$t%! ca %)!ke b& t*e "er b& ra%e t#t7
te$@ %, eclare t*e e;ce$t%! (% eclare 0"ct%!, %%, ra%e % e;ec"table ect%! (e;$l%c%tl& "%= ra%e t#t, %%%, *ale t*e ra%e e;ce$t%! S:l C!e@ Ht *!l t*e c"rretl& ra%e err!r "#ber S:l Err!r@ Ht *!l t*e c"rretl& ra%e err!r #= &ta;@ %$la& (S:l C!e S:l Err!r,? Pr=a#a E;ce$t%!-%%t@ H ee t! %$la& #& !"r #ea=e' ---6 %t % "e t! *ale t*e err!r "t l%ke a e;ce$t%! ---6 t! a!c%ate e;ce$t%! % t*e err!r c!e t*e $ra=#a e;ce$t%! %%t % e0ecte7 ---6 %t c!ta% t8! $ara#eter %, e;ce$t%! a#e %%, err!r "#ber(!r,c!e S"b Pr=ra# A et !0 PL/SQL t#t 8%t* a a#e t!re $er#etel& % atabae Calle T8! t&$e@ Pr!ce"re G"ct%! Pr!ce"re@ A Pr!ce"re % a Na#e PL/SQL Bl!ck $er0!r#%= !e !r #!re act%! a!c%ate t! b"%e l!=%c7 Ht % al! calle St!re $r!ce"re Ht acce$t t*e $ara#eter Ht % E;$l%c%tel& calle b& "%= E;ec"t%)e7 Ht #a& !r #a& !t Ret"r )al"e7 Pr!ce"rre ca!t "e <%t* Select State#et7 A)ata=e !0 Pr!ce"re@ 9%= Pr!ce"re a $r!=ra##er t! *%0t t*e c!e 0r!# t*e A$$l%cat%! %e t! t*e atabae %e7 B"%e R"le a A$$l%cat%! l!=%c t! be t!re A Pr!ce"re 8%t* % t*e atabae %e7 T*e $r!ce"ral c!e t!re 8%t*% t*e atabae ca be Re"e A&8*ere 8%t* % t*e A$$l%cat%!7 T*e& e!t be re8r%tte % eac* c!e #!"le !0 t*e a$l%cat%! ! a)e t*e creat%! t%#e !0 t*e a$$l%cat%!7 Re"ce t*e #a%ta%ce eV!rt7 T&$e !0 Pr!ce"re@ Sta al!e Pr!ce"re@ T*e $r!ce"re 8*%c* % create !"t%e !0 t*e $acka=e calle a
T*% $r!ce"re 8%ll t!re % "er_$r!ce"re7 Part !0 t*e $acka=e@ T*% Pr!ce"re % eUe % a $acka=e T*% $r!ce"re % !t t!re A&8*ere7 Part !0 t*e Pr!ce"re@ S$ec%Ucat%!@ !t*%= b"t eclarat%! ect%! *ere 8e ca =%)e t*e $r!ce"re a#e $ara#eter a l!cal )ar%able7 B!&@ %t c!ta% $l/:l c!e bae ! t*% PL/SQL !l& t*e Act%! !0 $r!ce"re e$e7 S&ta;@ Create !r re$lace Pr!ce"re$r!ca#e6 $ara#eter#!eatat&$e'-----, %/a l!cal )ar%able Be=% e;ec"table t#t6? E;ce$t%! e;ec"table t#t6? E$r!ce"re a#e? Pr!=ra#@
Para#eter #!e@ T*ree t&$e !0 #!e %,HN %%, O9T %%%, HNO9T HN@ Ht acce$t a e0a"lt t&$e Al8a& Acce$t )al"e %t! "b$=# Ht ca!t be a%=e 8%t* )al"e % "b$=# O"t@ Ht ret"r a )al"e 0r!# "b$=# %%t%l%e % "b$=# %t ca!t carr%e a )al"e %t! a "b$=# H!"t@ carr%e a )al"e %t! a "b$=# Ret"r a )al"e 0r!# T*e "b$=# a; 32 Ar=#et %t! "b$=# S&ta;@ $ara#eter a#e6 $ara#eter #!eatat&$e6 !te@ Pr!ce"re #a&!t Ret"r a )al"e b"t t*r!"=* t*e !"t#!e $ara#eter $r!ce"re #a& Ret"re Re#!)e t*e $r!ce"re@ Dr!$ $r!ce"re$r!c a#e6? E=@ create !r re$lace $r!ce"re $ (# % "#ber' !"t "#ber, % be=% @#5#? e $? "%= B% )ar%able )ar r "#ber e;ec $(1+'@r,? $r!ce"re create $r%t @r create !r re$lace $r!ce"re $r!c_cal (# % "#ber'r !"t "#ber'cr !"t "#ber, % be=% r@#5# cr@#5#5#? e $r!c_cal? )ar r "#ber )ar cr "#ber e;ec $r!c_cal(1+'@r'@cr, $r%t @r Pr%t @cr
Call t*% $r!ce"re % a!t*er PL/SQL Bl!ck Declare r "#ber? cr "#ber? be=% $r!c_cal(1+'r'cr,? b#_!"t$"t7$"t_l%e(t*e r %@r,? (t*e cr %@cr,? e? !/$@ r %@ cr %@ Re#!)e t*e $r!ce"re@ Dr!$ $r!ce"re $r!ce"re a#e6? Pra=#a _a"t!!#!" traact%!@ Ht % "e 8r%te t*e TCL c!##! % tr%==er7 %t % "e al! % ete $r!ce"re t! #ake eac* $r!ce"re %e$eetl& 0!r TCL7 G"ct%!@ T*e 0"ct%! % a PL/SQL Bl!ck t*at Ret"r a S%=le )al"e7 G"ct%! ca acce$t !e !r #!re $ara#eter !r ! $ara#eter7 0"ct%! #"t *a)a a Ret"r cla"e % t*e e;ec"table ect%! !0 a 0"ct%!7 %t % calle $art !0 e;$re%!7 %t % "e 0!r calc"lat%! !0 a )al"e7 0"ct%! ca "er 8%t* t*e elect t#t7 0"ct%! ca!t "e 8%t* elect t#e 8*e %t *a a !"t#!e'%!"t #!e $ara#eter7 S&ta;@ create !r re$lace 0"ct%! 0a#e6 ($ara#eter a#e#!e atat&$e'------, Ret"r atat&$e6 %/a l!cal )ar%able Be=% e;ec"table t#t6? Ret"r )ar%able/)al"e6? E;ce$t%! e;ec"table t#t6? e 0"ct%! a#e6? E;ec"t%!@ elect 0"ct%! a#e6(ar=#et, 0r!# "al? $l/:l@ be=% :l t#t6? e?
Packa=e@ Ht =r!"$ !0 l!=%call& Relate PL/SQL T&$e Obect a S"b$r!=ra#7 Packa=e ca !t be %)!ke'$ara#eter a ete7 Ht *a ""all& T8! $art %,$ec%Ucat%! HH,B!& Ht all!8a t*e !racle er)er t! rea #"lt%$le !bect %t! #e#!r& at !ce7 #ea 8*e 8e call $acka=e PL/SQL c!tr"ct 0!r t*e Urt t%#e't*e 8*!le $acka=e % l!ae %t! #e#!r&7 later call t! c!tr"ct % t*e a#e $acka=e Re:"%re ! %k H/O S&ta; 0!r $ec%Ucat%!@ create !r re$lace $ackb!&$acka#e6 %/a $"bl%c )ar%able eclarat%!? "b$r!=ra# b!%e6 E $ack a#e6? S&ta; 0!r T*e B!&@ create !r Re$lace $ackB!& Pack a#e6 %/a $r%)ate ar%able eclarat%!? "b$r!=ra# b!%e6 E $ack a#e? E;ec"t%!@ E;ec"tePacka#e67"bPr!=a#e6($ara#eter )al"e,? Tr%==er@ A et !0 PL/SQL t#t a"t!#at%call& e;ec"te 8*ee)er a DL t#t % $er0!r#e ! table7 Or A Databae Tr%==er % a a#e PL/SQL Bl!ck t!re % a atabae a E;ec"te H#$l%c%tel& 8*e Tr%==er%= E)et !cc"r7 A Tr%==er%= E)et % a D#l t#t A!c%ate 8%t* Y%ertY'Y"$ateY !r YeleteY t#t e;ec"te a=a%et a atabae Table7
A)ata=e !0 Tr%==er@ Ht % "e t! %#$le#et "er eUe Retr%ct%! ! table7 "e t! %#$!e b"%e R"le ! ata7 Pr!)%e *%=* ec"r%t&7
Tr%==er ca Ure be0!re !r a0ter a Tr%==er%= E)et7 Be0!re@ B& "%= Be0!re 8e ca #ake t*e Tr%==er act%! Be0!re $er0!r#%= Traact%!7 A0ter@ B& "%= A0ter 8e ca e;ec"te t*e Tr%==er a0ter $er0!r# a Traact%!7 DeUe Tr%==er @ R!8 Tr%==er@ T*% Tr%==er 8%ll be Ure 0!r e)er& r!8 #a%$"late7 Ieeral& "e 0!r ata a"%t%= a$$l%cat%!7 B& "%= GOR EACRO< " ca #ake t*e tr%==er a R!8 le)el7 State#et Tr%==er@ (table le)el, T*e Tr%==er 8%ll be Ure !l& !ce 0!r D#l t#t7 T&$e !0 Tr%==er@ %, A$$l%cat%! le)el HH, Databae Tr%==er7 A$$l%cat%! le)el Tr%==er@ T*e )al%at%! !r r"le %Veret 0r!# eac* a e)er& a$$l%cat%! 8e *!"l "e t*e a$$l%cat%! tr%==er7 E=@ D2K 0!r# a Re$!rt@ Databae Tr%==er@ T*e )al%at%! !r R"le are c!##! 0!r E)er& A$$l%cat 8*! are A$$l%cat 8*! are acce%= t*e Databae 8e ca "e t*e Databae Tr%==er7 Tr%==er Q"l%Uer@ @Ne87c!l# a#e6 @Ol7c!l"# a#e6 Tr%==er Q"l%Uer ca "e !l& % R!8 le)el7 S&ta;@ Create !r Re$lace Tr%==er Tr%==er a#e6 A0ter/Be0!re %ert/"$ate/elete6 (!0 c!l"# a#e6, ! table a#e6 0!r eac*r!8 (8*e c!%t%!6, eclare l!cal )ar%able? Be=% S:l t#t--------------------E;ce$t%! E;ec"table t#t E Tr%==er a#e? D%Verece bet8ee Pr!ce"re a Tr%==er7
Pr!ce"re %,call e;$l%c%tel& HH, acce$t t*e $ara#eter %%%,Pr!ce"re ca ete %),$r!ce"re ca "e 0!r t!r%= t*e %#a=e
Tr%==er %,call %#$l%c%tel& a=a%t t*e e)et %%, tr%==er !t acce$t $ara#eter %%%,tr%==er ca!t ete %), tr%==er ca!t "e
, % $r!ce"re " ca "e
,Tr%==er #a& !t =%)e t*e t*e c!#$%lat%! err!r b"t =%)e t*e
R" t%#e err! t*e TCL c!##a 8/! c!#$%le a R"t%#e err!r Gra=#a a"t!!"# % !$t%!al
Gra=#a a"t!!#!" % #aat!r&
D%Verece Betee Tr%==er a C!tra%t7 Tr%==er
C!tra%t c!tra%t are t*e b"%l%= $r!=ra# a 8e ca!t c*a=e t*e be*a)%!r !0 c!tra%t7 %t 8%ll c*eck t*e e;%t%= ata a 0et"re ata7 %0 #a& !t %able all t*e c!tra%t % a table at !ce7