•
PL/SQL Tutorial
•
PL/SQL - Home
•
PL/SQL - Overview
•
PL/SQL - Environment
•
PL/SQL - Basic Syntax
•
PL/SQL - Data Types
•
PL/SQL - Variales
•
PL/SQL - !onstants
•
PL/SQL - Operators
•
PL/SQL - !on"itions
•
PL/SQL - Loops
•
PL/SQL - Strin#s
•
PL/SQL - $rrays
•
PL/SQL - Proce"ures
•
PL/SQL - %unctions
•
PL/SQL - !ursors
•
PL/SQL - &ecor"s
•
PL/SQL - Exceptions
•
PL/SQL - Tri##ers
•
PL/SQL - Pac'a#es
•
PL/SQL - !ollections
•
PL/SQL - Transacti Transactions ons
•
PL/SQL - Date ( Time
•
PL/SQL - DB)S Output
•
PL/SQL - Object Oriented
PL/SQL PL/SQ L Tu Tutorial tori al PL/SQL is a comination o* SQL alon# wit+ t+e proce"ural *eatures o* pro#rammin# lan#ua#es, t was "evelope" y Oracle !orporation in t+e early .0s to en+ance t+e capailities o* SQL, PL/SQL is one o* t+ree 'ey pro#rammin# lan#ua#es eme""e" in t+e Oracle Dataase1 alon# wit+ SQL itsel* an" 2ava, T+is tutorial will #ive you #reat un"erstan"in# on P L/SQL to procee" wit+ Oracle "ataase an" ot+er a"vance" &DB)S concepts,
Audience T+is tutorial is "esi#ne" *or So*tware Pro*essionals1 w+o are willin# to learn PL/SQL Pro#rammin# Lan#ua#e in simple an" easy eas y steps, T+is tutorial will #ive you #reat un"erstan"in# on PL/SQL Pro#rammin# concepts1 an" a*ter completin# t+is tutorial1 you will e at interme"iate level o* expertise *rom w+ere you can ta'e yoursel* to +i#+er level o* expertise,
Prerequisites Be*ore procee"in# wit+ t+is tutorial1 you s+oul" +ave a asic un"erstan"in# o* so*tware asic concepts li'e w+at is "ataase1 source co"e1 co" e1 text e"itor an" execution o* pro#rams1 etc, * you alrea"y +ave un"erstan"in# on SQL an" ot+er computer pro#rammin# lan#ua#e t+en it will e an a""e" a"vanta#e to procee", T+e PL/SQL pro#rammin# lan#ua#e was "evelope" y Oracle !orporation in t+e late 3.4s as proce"ural extension lan#ua#e *or SQL an" t+e Oracle relational "ataase, %ollowin# are notale *acts aout PL/SQL5 •
PL/SQL is a completely portale1 +i#+-per*ormance transaction-processin# lan#ua#e,
•
PL/SQL provi"es a uilt-in interprete" an" OS in"epen"ent pro#rammin# environment,
•
PL/SQL can also "irectly e calle" *rom t+e comman"-line SQL6Plus inter*ace,
•
Direct call can also e ma"e *rom external pro#rammin# lan#ua#e calls to "ataase,
•
PL/SQL0s #eneral syntax is ase" on t+at o* $D$ an" Pascal pro#rammin# lan#ua#e,
•
$part *rom Oracle1 PL/SQL is availale in TimesTen TimesTen in-memory "ataase an" B) DB7,
Features Feat ures of PL/SQL PL/SQL +as t+e *ollowin# *eatures5 •
PL/SQL is ti#+tly inte#rate" wit+ SQL,
•
t o**ers extensive error c+ec'in#,
•
t o**ers numerous "ata types,
•
t o**ers a variety o* pro#rammin# structures,
•
t supports structure" pro#rammin# t+rou#+ *unctions an" proce"ures,
•
t supports o8ect-oriente" pro#rammin#,
•
t supports "evelopin# we applications an" server pa#es,
Advantages of PL/SQL PL/SQL +as t+e *ollowin# a"vanta#es5 •
•
•
•
SQL is t+e stan"ar" "ataase lan#ua#e an" PL/SQL is stron#ly inte#rate" wit+ SQL, PL/SQL supports ot+ static an" "ynamic " ynamic SQL, Static SQL supports D)L operations an" transaction control *rom PL/SQL loc', Dynamic SQL is SQL allows eme""in# DDL statements in PL/SQL loc's, PL/SQL allows sen"in# an entire loc' o* statements to t+e "ataase at one time, T+is re"uces networ' tra**ic an" provi"es +i#+ per*ormance *or t+e applications, PL/SQL #ives +i#+ pro"uctivity to pro#rammers as it can 9uery1 trans*orm1 an" up"ate "ata in a "ataase, PL/SQL saves time on "esi#n an" "eu##in# y stron# *eatures1 suc+ as exception +an"lin#1 encapsulation1 "ata +i"in#1 an" o8ect-oriente" "ata types,
•
$pplications written in PL/SQL are *ully portale,
•
PL/SQL provi"es +i#+ security level,
•
PL/SQL provi"es access to pre"e*ine" SQL pac'a#es,
•
PL/SQL provi"es support *or O8ect-Oriente" Pro#rammin#,
•
PL/SQL provi"es support *or Developin# :e :e $pplications an" Server Pa#es,
PL/SQL is not a stan"-alone pro#rammin# lan#ua#e; it is a tool wit+in t+e Oracle pro#rammin# environment, SQL6 Plus is an interactive tool t+at a llows you to type SQL an" PL/SQL statements at t+e comman" prompt, T+ese comman"s are t+en sent to t+e "ataase *or processin#, Once t+e statements are processe"1 t+e results are sent ac' an" "isplaye" on screen, To run PL/SQL pro#rams1 you s+oul" +ave Oracle &BD)S Server installe" in your mac+ine w+ic+ will ta'e care o* executin# SQL comman"s, )ost recent version o* Oracle &DB)S is 33#,
Downloa" Oracle 33# Express E"ition
? it version o* t+e installation as per your operatin# system, @sually t+ere are two *iles1 as +ave "ownloa"e" *or >? it :in"owsA, :in"owsA,
win64_11g!_database_1o"!#$i%
•
win64_11g!_database_!o"!#$i%
$*ter "ownloa"in# aove two *iles1 you will nee" to unip t+em in a sin#le "irectory database an" un"er t+at you will *in" *ollowin# su-"irectories5
Step 1 Cow1 let0s let0s launc+ Oracle Dataase nstaller usin# setup *ile, %ollowin# %ollowin# is t+e *irst screen,
Step 2
Step 3 2ust select *irst option (reate and (on"igure )atabase usin# ra"io utton an" clic' &e't utton to procee"5
Step 4 assume you are installin# Oracle 8ust *or learnin# purpose an" you will install it on your P! or Laptop, So select )es*to% (lass option an" clic' &e't utton to procee"5
Step 5 Provi"e a location1 w+ere you will install Oracle Server, 2ust 2ust mo"i*y Oracle +ase an" rest o* t+e locations will set automatically, Secon"1 you will +ave to provi"e a passwor"1 w+ic+ will e use" y system DB$, Once you provi"e re9uire" in*ormation1 clic' &e't utton to procee"5
Step 6 2ust clic' &e't utton to procee"5
Step 7 Cow1 clic' clic' ,inis utton to procee"1 t+is will start actual server installation,
Step 8 2ust wait1 until Oracle starts "oin# re9uire" con*i#uration,
Step 9 Here1 Oracle installation will copy re9uire" con*i#uration *iles1 so 'in"ly 8ust wait an" watc+5
Step 10 Once everyt+in# is "one1 you will +ave t+e *ollowin# "ialo#ue ox, 2ust clic' O. utton utton an" come out,
Step 11 Once your installation is "one1 you will +ave t+e *ollowin# *inal win"ow,
Fna! Step * everyt+in# +as een "one success*ully1 t+en it0s time to veri*y your installation, $t your comman" prompt1 use t+e *ollowin# comman" i* you are usin# :in"ows5 sqlplus "/ as sysdba"
* everyt+in# is *ine1 you s+oul" +ave SQL prompt w+ere you will write your PL/SQL comman"s an" scripts5
"e#t $dtor &unnin# lar#e pro#rams *rom comman" prompt may lan" you in ina"vertently losin# some o* t+e wor', So a etter o ption is to use comman" *iles, To use t+e comman" *iles5 •
Type Type your co"e in a text e"itor1 li'e Cotepa"1 Cotepa"1 or E"itPlus1 etc,
•
Save t+e *ile wit+ t+e ,s9l extension in t+e +ome "irectory,
•
•
Launc+ SQL6Plus comman" prompt *rom t+e "irectory w+ere you create" your PL/SQL *ile, Type Type *ileFname at t+e SQL6Plus comman" prompt p rompt to execute your pro#ram,
* you are not usin# a *ile to execute PL/SQL scripts1 t+en simply copy your PL/SQL co"e an" t+en ri#+t clic' on t+e lac' win"ow +avin# SQL prompt an" use %aste option to paste complete co"e at t+e comman" prompt, %inally1 8ust 8ust press enter to execute t+e co"e1 co "e1 i* it is not alrea"y execute", PL/SQL is a loc'-structure" lan#ua#e1 meanin# t+at PL/SQL pro#rams are "ivi"e" an" written in lo#ical loc's o* co"e, Eac+ loc' consists o* t+ree su-parts5
S%& %
Se'tons ( )es'rpton
1
)e'!aratons This section section starts with with the keyword keyword )$*LA+$. )$*LA+$. It is an optional section and defnes all variables, cursors, subprograms, and other elements to be used in the program.
2
$#e'uta,!e *o--ands This section section is enclosed between the keywords keywords .$& and .$& and $&) and $&) and it is a mandatory section. It consists o the executable !"#$! statements o the program. It should have at least one executable line o code, which may be %ust a &'!! command to indicate that nothing should be executed.
(
$#'epton and!ng This section section starts with with the keyword keyword $*$P"&. $*$P"&. This section is again optional and contains exception)s* that handle errors in the program.
Every PL/SQL statement en"s wit+ a semicolon 0, PL/SQL loc's can e neste" wit+in ot+er PL/SQL loc's usin# +23& an" 2&), Here is t+e asic structure o* a PL/SQL P L/SQL loc'5 DECLARE BEGIN E$CE%&I'N END*
"e e!!o or!d $#a-p!e DECLARE !essa)e +arc(ar,,-#. 01ello2 3orld40* BEGIN db!s5output6put5line!essa)e#* END* /
T+e end0 line si#nals t+e en" o* t+e PL/SQL loc', To To run t+e co"e *rom SQL comman" line1 you may nee" to type / at t+e e#innin# o* t+e *irst lan' line a*ter t+e last line o* t+e co"e, :+en t+e aove co"e is execute" e xecute" at SQL prompt1 it pro"uces t+e *ollowin# result5 1ello 3orld %L/78L procedure success9ully co!pleted6
"e PL/SQL denters PL/SQL i"enti*iers are constants1 variales1 exceptions1 proce"ures1 cursors1 an" reserve" wor"s, T+e i"enti*iers consist o* a letter optionally *ollowe" y more letters1 numerals1 "ollar si#ns1 un"erscores1 an" numer si#ns an" s+oul" not excee" = c+aracters, By "e*ault1 identi"iers are not case-sensiti5e , So you can use integer or &T232 to to represent a numeric value,
"e PL/SQL )e!-ters $ "elimiter is a symol wit+ a special meanin#, %ollowin# is t+e list o* "elimiters in PL/SQL5
)e!-ter
)es'rpton
: ;: <: /
+ddition, subtraction"negation, multiplication, division
=
+ttribute indicator
haracter string delimiter
%
omponent selector
>:?
-xpression or list delimiter
ost variable indicator
:
Item separator
@
$uoted identifer delimiter
/elational operator
B
/emote access indicator
C
#tatement terminator
+ssignment operator
D
+ssociation operator
EE
oncatenation operator
<<
-xponentiation -xponentiation operator
: DD
!abel delimiter )begin and end*
/<:
0ultiline comment delimiter )begin and end*
;;
#ingleline comment indicator
%%
/ange operator
: D: : D
/elational operators
D: : G: H
i3erent versions o &4T -$'+!
"e PL/SQL *o--ents Pro#ram comments are explanatory statements t+at you can inclu"e in t+e PL/SQL co"e t+at you write an" +elps anyone rea"in# its source co"e, $ll pro#rammin# lan#ua#es allow *or some *orm o* comments, T+e PL/SQL supports sin#le-line an" multi-line comments, $ll c+aracters availale insi"e any comment are i#nore" y PL/SQL compiler, T+e PL/SQL sin#le-line sin#le-line comments start wit+ t+e "elimiter -- G"oule +yp+en an" multi-line comments are enclose" y /6 an" 6/, DECLARE :: +ariable declaration !essa)e +arc(ar,,-#. 01ello2 3orld40* BEGIN /; ; %L/78L eecutable state!ents# ;/ db!s5output6put5line!essa)e#* END* /
:+en t+e aove co"e is execute" at SQL prompt1 it pro"uces t+e *ollowin# result5 1ello 3orld %L/78L procedure success9ully co!pleted6
PL/SQL Progra- Ints $ PL/SQL unit is any one o* t+e *ollowin#5 •
PL/SQL loc'
•
%unction
•
Pac'a#e
•
Pac'a#e o"y
•
Proce"ure
•
Tri##er
•
Type
•
Type o"y
Eac+ o* t+ese units will e "iscusse" in t+e *ort+comin# c+apters, PL/SQL variales1 constants an" parameters must +ave a vali" "ata type1 w+ic+ speci*ies a stora#e *ormat1 constraints1 an" vali" ran#e o* values, T+is tutorial will ta'e you t+rou#+ an" LO+ "ata types availale in PL/SQL an" ot+er two " ata types will e covere" in S(ALA an" ot+er c+apters,
*ategorJ
)es'rpton
#calar
#ingle values with no internal components, such as a &'05-/, +T-, or 544!-+&.
!arge 4b%ect )!45*
ointers to large ob%ects that are stored separately rom other data items, such as text, graphic images, video clips, and sound waveorms.
omposite
ata items that have internal components that can be accessed individually. 6or example, collections and records.
/eerence
ointers to other data items.
PL/SQL S'a!ar )ata "Jpes and Su,tJpes PL/SQL Scalar Data Types Types an" Sutypes S utypes come un"er t+e *ollowin# cate#ories5
)ate "Jpe
)es'rpton
&umeric
&umeric values on which arithmetic operations are perormed.
haracter
+lphanumeric values that represent single characters or strings o characters.
5oolean
!ogical values on which logical operations are perormed.
atetime
ates and times.
PL/SQL provi"es sutypes o* "ata types, %or example1 t+e "ata type C@)BE& +as a sutype calle" CTEIE&,
PL/SQL &u-er' )ata "Jpes and Su,tJpes %ollowin# is t+e "etail o* PL/SQL pre-"e*ine" numeric "ata types an" t+eir su-types5
)ata "Jpe
)es'rpton
!#7I&T-8-/
#igned integer in range 2,19:,9;(,<9; through 2,19:,9;(,<9:, represented in (2 bits
5I&+/=7I&T-8-/
#igned integer in range 2,19:,9;(,<9; through 2,19:,9;(,<9:, represented in (2 bits
5I&+ 5I&+/ /=76! =76!4 4+T
#ing #ingle lep prrecis ecisio ion n I-I--- :>9 :>9 or orma matt ?oa ?oati ting ngp poi oint nt numb number er
5I&+ 5I&+/ /=74 =74'5 '5!!-
oub ouble lep prrecis ecisio ion n I--I--- :>9 :>9or orma matt ?oati ?oating ngp poi oint nt numb number er
6ixedpoint or ?oatingpoint number with absolute value in &'05-/)prec, scale* range 1-1(@ to )but not including* 1.@-12<. + &'05-/ variable can also represent @. -)prec, scale*
+&#I specifc fxedpoint type with maximum precision o (; decimal digits.
-I0+!)prec, scale*
I50 specifc fxedpoint type with maximum precision o (; decimal digits.
&'0-/I)pre, secale*
6loating type with maximum precision o (; decimal digits.
4'5!- /-I#I4&
+&#I specifc ?oatingpoint type with maximum precision o 12< binary digits )approximately (; decimal digits*
6!4+T
+&#I and I50 specifc ?oatingpoint type with maximum precision o 12< binary digits )approximately (; decimal digits*
I&T
+&#I specifc integer type with maximum precision o (; decimal digits
I&T-8-/
+&#I and I50 specifc integer type with maximum precision o (; decimal digits
#0+!!I&T
+&#I and I50 specifc integer type with maximum precision o (; decimal digits
/-+!
6loatingpoint type with maximum precision o <( binary digits )approximately 1; decimal digits*
%ollowin# is a vali" "eclaration5 DECLARE nu! IN&EGER* nu!, REAL* nu!= D'BLE %RECI7I'N* BEGIN null* END* /
:+en t+e aove co"e is compile" an" execute"1 it pro"uces t+e *ollowin# result5
%L/78L procedure success9ully co!pleted
PL/SQL *ara'ter )ata "Jpes "Jpes and Su,tJpes %ollowin# is t+e "etail o* PL/SQL pre-"e*ine" c+aracter "ata types an" t+eir su-types5
)ata "Jpe
)es'rpton
+/
6ixedlength character string with maximum siAe o (2,:<: bytes
B+/+/2
Bariablelength character string with maximum siAe o (2,:<: bytes
/+C /+C
Bariablelength binary or byte string with maximum siAe o (2,:<: bytes, not interpreted by !"#$!
&+/
6ixedlength national character string with maximum siAe o (2,:<: bytes
&B+/+/2
Bariablelength national character string with maximum siAe o (2,:<: bytes
!4&8
Bariablelength character string with maximum siAe o (2,:<@ bytes
!4&8 /+C
Bariablelength binary or byte string with maximum siAe o (2,:<@ bytes, not interpreted by !"#$!
/4CI
hysical row identifer, the address o a row in an ordinary table
'/4CI
'niversal row identifer )physical, logical, or oreign row identifer*
PL/SQL .oo!ean )ata "Jpes T+e +OOL2A& "ata type stores lo#ical values t+at are use" in lo#ical operations, T+e lo#ical values are t+e Boolean values T&@E an" %$LSE %$LSE an" t+e value C@LL, However1 SQL +as no "ata type e9uivalent to BOOLE$C, T+ere*ore1 Boolean values canno t e use" in5 •
SQL statements
•
Built-in SQL *unctions Gsuc+ as TOF!H$&
•
PL/SQL *unctions invo'e" *rom SQL statements
PL/SQL )atet-e and nterva! "Jpes T+e )AT2 "atatype to store *ixe"-len#t+ "atetimes1 w+ic+ inclu"e t+e time o* "ay in secon"s since mi"ni#+t, Vali" Vali" "ates ran#e *rom 2anuary 31 ?A37 B! to Decemer =31 .... .. .. $D,
T+e "e*ault "ate *ormat is set y t+e Oracle initialiation parameter CLSFD$TEF%O&)$T CLSFD$TEF%O&)$T,, %or example1 t+e "e*ault mi#+t e 0DD-)OC-<<01 w+ic+ inclu"es a two-"i#it numer *or t+e "ay o* t+e mont+1 an areviation o* t+e mont+ name1 an" t+e last two "i#its o* t+e year1 *or example1 3-O!T-37, Eac+ D$TE D$TE inclu"es t+e century1 year1 mont+1 "ay1 +our1 minute1 an" secon", T+e *ollowin# tale s+ows t+e vali" values *or eac+ *iel"5
Fe!d &a-e =-+/
Ka!d )atet-e Ka!ues
Ka!d nterva! Ka!ues
9:12 to DDDD )excluding )excluding year @* @*
+ny nonAero nonAero integer
04&T
@1 to 12
@ to 11
+= +=
@1 to (1 )limited by the values o 04&T and =-+/, according to the rules o the +ny nonAero integer calendar or the locale*
4'/
@@ to 2(
@ to 2(
0I&'T-
@@ to >D
@ to >D
#-4&
@ to >D.D)n*, where D)n* @@ to >D.D)n*, where D)n* is the precision is the precision o o time ractional seconds interval ractional seconds
TI0-E4&-74' TI0-E4&-74' 12 to 19 )range accommodates daylight &ot applicable / savings time changes* TI0-E4&-70I&' TI0-E4&-70I&' @@ to >D T-
&ot applicable
TI0-E4&-7/-8I TI0-E4&-7/-8I 6ound in the dynamic perormance view &ot applicable 4& BFTI0-E4&-7&+0-# TI0-E4&-7+55 TI0-E4&-7+55 6ound in the dynamic perormance view &ot applicable / BFTI0-E4&-7&+0-#
PL/SQL Large ,e't >L.? )ata "Jpes Lar#e o8ect GLOB "ata types re*er lar#e to "ata items suc+ as text1 #rap+ic ima#es1 vi"eo clips1 an" soun" wave*orms, LOB "ata types t ypes allow e**icient1 ran"om1 piecewise access to t+is "ata, %ollowin# are t+e pre"e*ine" PL/SQL LOB "ata types5
)ata "Jpe
)es'rpton
SMe
56I!-
'sed to store large binary ob%ects in operating system fles outside the database.
#ystemdependent. annot exceed 9 gigabytes )85*.
5!45
'sed to store large binary ob%ects in the
; to 12; terabytes )T5*
database. !45
'sed to store large blocks o character data in the database.
&!45
'sed to store large blocks o &+/ data ; to 12; T5 in the database.
; to 12; T5
PL/SQL Iser;)ened Su,tJpes $ sutype is a suset o* anot+er "ata type1 w+ic+ is calle" its ase type, $ sutype +as t+e same vali" operations as its ase type1 ut only onl y a suset o* its vali" values, PL/SQL pre"e*ines several sutypes in pac'a#e ST$CD$&D, ST$CD$&D, %or example1 PL/SQL pre"e*ines t+e sutypes !H$&$!TE& an" CTEIE& as *ollows5 7B&?%E C1ARAC&ER I7 C1AR* 7B&?%E IN&EGER I7 N@BER=2-#*
:+en t+e aove co"e is execute" at SQL prompt1 it pro"uces t+e *ollowin# result5 1ello Reader 3elco!e to t(e 3orld o9 %L/78L %L/78L procedure success9ully co!pleted6
&ILLs n PL/SQL PL/SQL C@LL values represent missin# missin# or un'nown "ata an" an " t+ey are not an inte#er1 in te#er1 a c+aracter1 or any ot+er speci*ic "ata type, Cote t+at C@LL is not t+e same as an empty "ata strin# or t+e null c+aracter value 0J0, $ null can e assi#ne" ut it cannot e e9uate" wit+ anyt+in#1 inclu"in# itsel*, $ variale is not+in# ut a name #iven to a stora#e area t+at our pro#rams can manipulate, Eac+ variale in PL/SQL +as a speci*ic "ata type1 w+ic+ "etermines t+e sie an" layout o* t+e variale0s memory; t+e ran#e o* values t+at can e store" wit+in t+at memory an" t+e set o* o* operations t+at can e applie" to t+e variale, T+e name o* a PL/SQL variale consists o* a letter optionally *ollowe" y more letters1 numerals1 "ollar si#ns1 un"erscores1 an" numer si#ns an" s+oul" not excee" = c+aracters, c+ aracters, By
"e*ault1 variale names are not case-sensitive,
Kara,!e )e'!araton n PL/SQL PL/SQL variales must e "eclare" in t+e "eclaration section or in a pac'a#e as a #loal variale, :+en you "eclare a variale1 PL/SQL P L/SQL allocates memory *or t+e variale0s value an" t+e stora#e location is i"enti*ie" y t+e variale name, T+e syntax *or "eclarin# a variale is5 +ariable5na!e C'N7&AN& datatype N'& NLL . DEAL& initial5+alue
:+ere1 variable_name is variable_name is a vali" i"enti*ier in PL/SQL1 datatype must datatype must e a vali" PL/SQL "ata type or any user "e*ine" "ata " ata type w+ic+ we alrea"y +ave "iscusse" in last c+apter, Some vali" variale "eclarations alon# wit+ t+eir "e*inition are s+own elow5 sales nu!ber-2 ,#* pi C'N7&AN& double precision . =6F* na!e +arc(ar,,#* address +arc(ar,--#*
:+en you provi"e a sie1 scale or precision limit wit+ t+e "ata type1 it is calle" a constrained declaration , !onstraine" "eclarations re9uire less memory t+an unconstraine" "eclarations, %or example5 sales nu!ber-2 ,#* na!e +arc(ar,,#* address +arc(ar,--#*
nta!Mng Kara,!es Kara,!es n PL/SQL :+enever you "eclare a variale1 PL/SQL assi#ns it a "e*ault value o* C@LL, * you want to initialie a variale wit+ a value ot+er t+an t+e C@LL value1 you can "o so "urin# t+e "eclaration1 usin# eit+er o* t+e *ollowin#5 •
T+e )2,ALT 'eywor"
•
T+e assign7ent operator
%or example5 counter binary5inte)er . -* )reetin)s +arc(ar,,-# DEAL& 01a+e a Good Day0*
t is a #oo" pro#rammin# practice to initialie variales properly ot+erwise1 sometimes pro#ram woul" pro"uce unexpecte" result, Try t+e t+e *ollowin# example w+ic+ ma'es use o* various types o* variales5 DECLARE a inte)er . -* b inte)er . ,-* c inte)er* 9 real* BEGIN c . a H b* db!s5output6put5line0alue db!s5output6put5line0a lue o9 c. 0 c#* 9 . J-6-/=6-* db!s5output6put5line0alue db!s5output6put5line0a lue o9 9. 0 9#* END* /
:+en t+e aove co"e is execute"1 it pro"uces t+e *ollowin# result5 alue o9 c. =alue o9 9. ,=6================== %L/78L procedure success9ully co!pleted6
Kara,!e S'ope n PL/SQL PL/SQL allows t+e nestin# o* Bloc's1 i,e,1 eac+ pro#ram loc' may contain anot+er inner loc', * a variale is "eclare" wit+in an inner loc'1 it is not accessile to t+e outer o uter loc', However1 i* a variale is "eclare" an" accessile to an outer Bloc'1 it is also acce ssile to all neste" inner Bloc's, T+ere are two types o* variale scope5 •
Local 5ariables - variales "eclare" in an inner loc' an" not accessile to outer loc's,
•
3lobal 5ariables - variales "eclare" in t+e outermost loc' or a pac'a#e,
%ollowin# example s+ows t+e usa#e o* Local an" 3lobal variales in its simple *orm5 DECLARE :: Global +ariables nu! nu!ber . K* nu!, nu!ber . * BEGIN db!s5output6put5line0'uter ariable nu!. 0 db!s5output6put5line0'uter db!s5output6put5line0'u ter ariable nu!,. 0 DECLARE :: Local +ariables nu! nu!ber . K* nu!, nu!ber . * BEGIN db!s5output6put5line0Inner ariable nu!. 0 db!s5output6put5line0Inner db!s5output6put5line0Inn er ariable nu!,. 0 END* END* /
nu!#* nu!,#*
nu!#* nu!,#*
:+en t+e aove co"e is execute"1 it pro"uces t+e *ollowin# result5 'uter ariable nu!. K 'uter ariable nu!,.
Inner ariable nu!. K Inner ariable nu!,. %L/78L procedure success9ully co!pleted6
Assgnng SQL QuerJ +esu!ts to PL/SQL Kara,!es
Cext1 let us insert some values in t+e tale5 IN7ER& IN&' C7&'@ER7 ID2NA@E2AGE2ADDRE7727ALAR?# ALE7 2 0Ra!es(02 =,2 0A(!edabad02 ,---6-- #* IN7ER& IN&' C7&'@ER7 ID2NA@E2AGE2ADDRE7727ALAR?# ALE7 ,2 0(ilan02 ,2 0Del(i02 --6-- #* IN7ER& IN&' C7&'@ER7 ID2NA@E2AGE2ADDRE7727ALAR?# ALE7 =2 0Maus(iM02 ,=2 0ota02 ,---6-- #* IN7ER& IN&' C7&'@ER7 ID2NA@E2AGE2ADDRE7727ALAR?# ALE7 F2 0C(aitali02 ,2 0@u!bai02 --6-- #* IN7ER& IN&' C7&'@ER7 ID2NA@E2AGE2ADDRE7727ALAR?# ALE7 2 01ardiM02 ,J2 0B(opal02 --6-- #* IN7ER& IN&' C7&'@ER7 ID2NA@E2AGE2ADDRE7727ALAR?# ALE7 2 0o!al02 ,,2 0@%02 F--6-- #*
T+e *ollowin# pro#ram assi#ns values *rom t+e aove tale to PL/SQL variales usin# t+e SELE!T CTO clause o* SQL5 DECLARE c5id custo!ers6idOtype . * c5na!e custo!ers6na!eOtype* c5addr custo!ers6addressOtype* c5sal custo!ers6salaryOtype* BEGIN 7ELEC& na!e2 address2 salary IN&' c5na!e2 c5addr2 c5sal R'@ custo!ers 31ERE id c5id*
db!s5output6put5line 0Custo!er 0 c5na!e 0 9ro! 0 c5addr 0 earns 0 c5sal#* END*
/
:+en t+e aove co"e is execute"1 it pro"uces t+e *ollowin# result5 Custo!er Ra!es( 9ro! A(!edabad earns ,--%L/78L procedure co!pleted success9ully
$ constant +ol"s a value t+at once "eclare"1 "oes not c+an#e in t+e pro#ram, $ constant "eclaration speci*ies its name1 "ata type1 an" value1 an" allocates stora#e *or it, T+e "eclaration can also impose t+e COT C@LL constraint,
)e'!arng a *onstant $ constant is "eclare" usin# t+e !OCST$CT !OCST$CT 'eywor", t re9uires an initial value an" "oes "oe s not allow t+at value to e c+an#e", %or example5 %I C'N7&AN& N@BER . =6FK,F* DECLARE :: constant declaration pi constant nu!ber . =6FK,F* :: ot(er declarations radius nu!ber2,#* dia nu!ber2,#* circu!9erence nu!berJ2 ,#* area nu!ber -2 ,#* BEGIN :: processin) radius . K6* dia . radius ; ,* circu!9erence . ,6- ; pi ; radius* area . pi ; radius ; radius* :: output db!s5output6put5line0Radius. db!s5output6put5line0Ra dius. 0 radius#* db!s5output6put5line0Dia!eter. db!s5output6put5line0Di a!eter. 0 dia#* db!s5output6put5line0Circu!9erence. db!s5output6put5line0Ci rcu!9erence. 0 circu!9erence#* db!s5output6put5line0Area. db!s5output6put5line0Ar ea. 0 area#* END* /
:+en t+e aove co"e is execute" at SQL prompt1 it pro"uces t+e *ollowin# result5 Radius. K6 Dia!eter. K Circu!9erence. K6K Area. ,=6= %l/78L procedure success9ully co!pleted6
"e PL/SQL Ltera!s $ literal literal is an explicit numeric1 c+aracter1 strin#1 or Boolean value not represente" y an i"enti*ier, %or example1 T&@E1 A4>1 C@LL1 0tutorialspoint0 are all literals o* type Boolean1 numer1 or strin#, PL/SQL1 literals are case-sensitive, PL/SQL supports t+e *ollowin# 'in"s o* literals5 •
Cumeric Literals
•
!+aracter Literals
•
Strin# Literals
•
BOOLE$C Literals
•
Date an" Time Literals
T+e *ollowin# tale provi"es examples *rom all t+ese cate#ories o* literal values,
Ltera! "Jpe
$#a-p!e
&umeric !iterals
@>@ :; 19 @ G(2:<: <.<<<: @.@ 12.@ (.191>D G:;@@.@@ <-> 1.@-; (.191>De@ 1-(; D.>e(
har harac acte terr !it !iter eral als s
H+H H+H HH HH HDH HDH H H HAH HAH H)H H)H
#tring !iterals
Hello, worldJH HTutorials ointH H1D&4B12H
544 44!!-+& +& !ite !itera rals ls
T/'T/'-,, 6+! 6+!##-,, and and &' &'!! !!..
ate and Time !iterals
+T- H1D:;122>HK TI0-#T+0 TI0-#T+0 H2@121@2D H2@121@2D 12L@1L@1HK 12L@1L@1HK
To eme" sin#le 9uotes wit+in a strin# literal1 place two sin#le 9uotes next to eac+ ot+er as s+own elow5 DECLARE !essa)e +arc(ar,=-#. 00&(at00s tutorialspoint6co!400* BEGIN db!s5output6put5line!essa)e#* END* /
:+en t+e aove co"e is execute" at SQL prompt1 it pro"uces t+e *ollowin# result5 &(at0s tutorialspoint6co!4 %L/78L procedure success9ully co!pleted6
$n operator is a symol t+at tells t+e compiler to per*orm speci*ic mat+ematical or lo#ical manipulation, PL/SQL lan#ua#e is ric+ in uilt-in operators an" provi"es t+e *ollowin# types o* operators5 •
$rit+metic operators
•
&elational operators
•
!omparison operators
•
Lo#ical operators
•
Strin# operators
T+is tutorial will explain t+e arit+metic1 relational1 comparison an" lo#ical operators one y one, T+e Strin# operators will e "iscusse" un"er t+e c+ap ter5 PL/SQL - Strings ,
Art-et' perators %ollowin# tale s+ows all t+e arit+metic operators supporte" y y PL/SQL, $ssume variale $ +ol"s 3 an" variale B +ol"s K t+en5 S+ow Examples
perat or
)es'rpton
$#a-p!e
G
+dds two operands
+ G 5 will give 1>
#ubtracts second operand rom the frst
+ 5 will give >
M
0ultiplies both operands
+ M 5 will give >@
"
ivides numerator by denumerator
+ " 5 will give 2
MM
-xponentiation operator, raises one operand + MM 5 will give 1@@@@@ to the power o other
+e!atona! perators &elational operators compare two expressions or values an" return a Boolean result, %ollowin# tale s+ows all t+e relational operators supporte" y PL/SQL, $ssume $ssume variale $ +ol"s 3 an" variale B +ol"s 71 t+en5 S+ow Examples
perat or
)es'rpton
$#a-p!e
N
hecks i the values o two operands are eOual or not, i yes then condition becomes true.
)+ N 5* is not true.
JN PQ RN
hecks i the values o two operands are eOual or not, i values are not eOual then condition becomes true.
)+ JN 5* is true.
Q
hecks i the value o let operand is greater than the value o right operand, i yes then )+ Q 5* is not true. condition becomes true.
P
hec hecks i the the value lue o let et oper perand is less ess
)+ P 5* is true true..
than the value o right operand, i yes then condition becomes true.
QN
hecks i the value o let operand is greater than or eOual to the value o right operand, i )+ QN 5* is not true. yes then condition becomes true.
PN
hecks i the value o let operand is less than or eOual to the value o right operand, i )+ PN 5* is true. yes then condition becomes true.
*o-parson perators !omparison operators are use" *or comparin# one e xpression to anot+er, T+e result is always eit+er T&@E1 %$LSE O& C@LL, S+ow Examples
perat or
!IS-
)es'rpton The !IS- operator operator compares compares a character, character, string, or !45 value to a pattern and returns T/'- i the the value matches matches the pattern pattern and 6+!#- i it does not.
$#a-p!e I HEara +liH like HE +7iH returns a 5oolean true, whereas, H&uha +liH like HE +7iH returns a 5oolean alse.
I x N 1@ then, x between > The 5-TC--& 5-TC--& operator tests tests whether a value and 2@ returns true, x 5-TC-lies in a specifed range. x 5-TC--& a +& b between > and 1@ returns & means that x QN a and x PN b. true, but x between 11 and 2@ returns alse.
I&
I x N HmH then, x in )HaH, HbH, The I& operator tests set membership. membership. x I& HcH* returns boolean alse but )set* means that x is eOual to any member o x in )HmH, HnH, HoH* returns set. 5oolean true.
The I# &'!! operator returns returns the the 544!-+& 544!-+& value T/'- i its operand is &'!! or 6+!#- i I# &'!! it is not &'!!. omparisons involving &'!! values always yield &'!!.
I x N HmH, then Hx is nullH returns 5oolean alse.
Log'a! perators %ollowin# tale s+ows t+e Lo#ical operators supporte" y PL/SQL, $ll t+ese operators wor' on Boolean operan"s an" pro"uces Boolean results, $ssume variale $ +ol"s +ol"s true an" variale B +ol"s *alse1 t+en5 S+ow Examples
perat
)es'rpton
$#a-p!e
or
and
alled logical +& operator. I both the operands are true then condition becomes true.
)+ and 5* is alse.
or
alled logical 4/ 4perator. I any o the two operands is true then condition becomes true.
)+ or 5* is true.
not
alled logical &4T 4perator. 'sed to reverse the logical state o its operand. I a condition not )+ and 5* is true. is true then !ogical &4T operator will make it alse.
PL/SQL perator Pre'eden'e Operator prece"ence "etermines t+e #roupin# o* terms in an expression, T+is a**ects +ow an expression is evaluate", !ertain operators +ave +i#+er prece"ence t+an ot+ers; *or example1 t+e multiplication operator +as +i#+er prece"ence t+an t+e a""ition operator5 %or example x A = 6 7; +ere1 x is assi#ne" 3=1 not 7 ecause operator 6 +as +i#+er prece"ence t+an 1 so it *irst #ets multiplie" wit+ =67 an" t+en a""s into A, Here1 operators wit+ t+e +i#+est prece"ence ap pear at t+e top o* t+e tale1 t+ose wit+ t+e lowest appear at t+e ottom, :it+in an expression1 +i#+er prece"ence operators will e eva luate" *irst, S+ow Examples
perator
peraton
MM
exponentiation
G,
identity, negation
M, "
multiplication, division
G, ,
addition, subtraction, concatenation
N, P, Q, PN, QN, PQ, JN, RN, UN, comparison I# &'!!, !IS-, 5-TC--&, I& &4T
logical negation
+&
con%unction
4/
inclusion
Decision-ma'in# structures re9uire t+at t+e pro#rammer speci*y one or more con"itions to e evaluate" or teste" y t+e pro#ram1 alon# wit+ a statement or statements to e exe cute" i* t+e
con"ition is "etermine" to e true1 an" optionally1 ot+er statements to e execute" i* t+e con"ition is "etermine" to e *alse, %ollowin# is t+e #eneral *rom o* a typical con "itional Gi,e,1 "ecision ma'in# structure *oun" in most o* t+e pro#rammin# lan#ua#es5
PL/SQL pro#rammin# lan#ua#e provi"es *ollowin# types o* "ecision-ma'in# statements, !lic' t+e *ollowin# lin's to c+ec' t+eir "etail, State7ent
% - THEC statement
%-THEC-ELSE statement
%-THEC-ELS% statement
!ase statement
Searc+e" !$SE statement neste" %-THEC-ELSE
)escri%tion T+e , state7ent associates a con"ition wit+ a se9uence o* statements enclose" y t+e 'eywor"s T82& an" 2&) ,, * t+e con"ition is true1 t+e statements #et execute" an" i* t+e con"ition is *alse or C@LL t+en t+e % statement "oes not+in#, , state7ent a""s t+e 'eywor" 2LS2 *ollowe" y an alternative se9uence o* statement, * t+e con"ition is *alse or C@LL 1 t+en only t+e alternative se9uence o* statements #et execute", t ensures t+at eit+er o* t+e se9uence o* statements is execute", t allows you to c+oose etween several alternatives, Li'e t+e % statement1 t+e (AS2 state7ent selects one se9uence o* statements to execute, However1 to select t+e se9uence1 t+e !$SE statement uses a selector rat+er t+an multiple Boolean expressions, $ selector is an expression w+ose value is use" to select one on e o* several alternatives, T+e searc+e" !$SE statement as no selector 1 an" it0s :HEC clauses contain searc+ con"itions t+at yiel" Boolean values,
T+ere may e a situation w+en you nee" to execute a loc' o* co"e several numer o* times, n #eneral1 statements are execute" se9uentially5 T+e *irst statement in a *unction is execute" *irst1 *ollowe" y t+e secon"1 an" so on, Pro#rammin# lan#ua#es provi"e various control structures t+at allow *or more complicate" execution pat+s, $ loop statement allows us to execute a statement or #roup o* statements multiple times an" *ollowin# is t+e #eneral *orm o* a loop statement in most o* t+e pro#rammin# lan#ua#es5
PL/SQL provi"es t+e *ollowin# types o* loop to +an"le t+e loopin# re9uirements, !lic' t+e *ollowin# lin's to c+ec' t+eir "etail,
Loop "Jpe
)es'rpton
!"#$! 5asic !44
In this loop structure, seOuence o statements is enclosed between the !44 and -& !44 statements. +t each iteration, the seOuence o statements is executed and then control resumes at the top o the loop.
!"#$! CI!- !44
/epeats a statement or group o statements while a given condition is true. It tests the condition beore executing the loop body.
!"#$! 64/ !44
-xecute a seOuence o statements multiple times and abbreviates the code that manages the loop variable.
&ested loops in !"#$!
=ou =ou can use one one or more more loop inside inside any another basic loop, while or or loop.
La,e!ng a PL/SQL Loop PL/SQL loops can e laele", T+e lael s+oul" e enclose" y "oule an#le rac'ets GMM an" NN an" appear at t+e e#innin# o* t+e LOOP statement, T+e lael name can also a ppear at t+e en" o* t+e LOOP statement, > 'R i IN 66= L''% << inner5loop >> 'R P IN 66= L''% db!s5output6put5line0i is. 0 i 0 and P is. 0 P#* END loop inner5loop* END loop outer5loop* END* /
:+en t+e aove co"e is execute" at SQL prompt1 it pro"uces t+e *ollowin# result5 i i i i i i i i i
is. is. is. is. is. is. is. is. is.
, , , = = =
and and and and and and and and and
P P P P P P P P P
is. is. is. is. is. is. is. is. is.
, = , = , =
%L/78L procedure success9ully co!pleted6
"e Loop *ontro! State-ents Loop control statements c+an#e execution *rom its normal se9uence, :+en execution leaves a scope1 all automatic o8ects t+at were create" in t+at scope are "estroye", PL/SQL supports t+e *ollowin# control statements, Laelin# loops also +elps in ta'in# t+e control outsi"e a loop, !lic' t+e *ollowin# lin's to c+ec' t+eir "etails,
*ontro! State-ent -VIT statement
4&TI&'- statement
84T4 statement
)es'rpton The -xit statement statement completes completes the loop and control control passes to the statement immediately ater -& !44 auses the loop to skip the remainder o its body and immediately retest its condition prior to reiterating. Transers Transers control control to the the labeled statement. statement. Though it it is not advised to use 84T4 statement in your program.
T+e strin# in PL/SQL is actually a se9uence o* c+aracters wit+ an optional sie speci*ication, T+e c+aracters coul" e numeric1 letters1 lan'1 special c+aracters or a comination o* all, PL/SQL o**ers t+ree 'in"s o* strin#s5
•
•
•
,i'ed-lengt strings 5 n suc+ strin#s1 pro#rammers speci*y t+e len#t+ w+ile "eclarin# t+e strin#, T+e strin# is ri#+t-pa""e" wit+ spaces to t+e len#t+ so speci*ie", 9ariable-lengt strings 5 n suc+ strin#s1 a maximum len#t+ up to =71A>A1 *or t+e strin# is speci*ie" an" no pa""in# ta'es place, (aracter large objects (LO+s 5 T+ese are variale-len#t+ strin#s t+at can e up to 374 teraytes,
PL/SQL strin#s coul" e eit+er variales or literals, $ strin# literal is enclose" wit+in 9uotation mar's, %or example1 0&(is is a strin) literal60 'r 0(ello Qorld0
To inclu"e inclu"e a sin#le 9uote insi"e a strin# literal1 you nee" to type two sin#le 9uotes next to one anot+er1 li'e5 0t(is isn00t Q(at it looMs liMe0
)e'!arng Strng Kar Kara,!es a,!es Oracle "ataase provi"es numerous n umerous strin# "atatypes 1 li'e1 !H$&1 C!H$&1 V$&!H$&71 V$&!H$&71 CV$ CV$&!H$&71 !LOB1 an" C!LOB, T+e "atatypes pre*ixe" wit+ an 0C0 are 0national c+aracter set0 "atatypes1 t+at store @nico"e c+aracter "ata, * you nee" to "eclare a variale-len#t+ strin#1 you must provi"e t+e maximum len#t+ o* t+at strin#, %or example1 t+e V$&!H$&7 "ata type, T+e *ollowin# example illustrates "eclarin# an" usin# some strin# variales5 DECLARE na!e +arc(ar,,-#* co!pany +arc(ar,=-#* introduction clob* c(oice c(ar#* BEGIN na!e . 0o(n 7!it(0* co!pany . 0In9otec(0* introduction . 0 1ello4 I00! o(n 7!it( 9ro! In9otec(60* c(oice . 0y0* I c(oice 0y0 &1EN db!s5output6put5linena!e#* db!s5output6put5lineco!pany#* db!s5output6put5lineintroduction#* END I* END* /
:+en t+e aove co"e is execute" at SQL prompt1 it pro"uces t+e *ollowin# result5 o(n 7!it( In9otec( Corporation 1ello4 I0! o(n 7!it( 9ro! In9otec(6 %L/78L procedure success9ully co!pleted
To "eclare a *ixe"-len#t+ strin#1 use t+e !H$& "atatype, Here you "o not +ave to speci*y a maximum len#t+ *or a *ixe"-len#t+ variale, * you leave o** t+e len#t+ constraint1 Oracle
Dataase automatically uses a maximum len#t+ re9uire", So *ollowin# two "eclarations elow are i"entical5 red59la) C1AR# . 0?0* red59la) C1AR . 0?0*
PL/SQL Strng Fun'tons and perators PL/SQL o**ers t+e concatenation operator G *or 8oinin# two strin#s, T+e *ollowin# tale provi"es t+e strin# *unctions provi"e" y PL/SQL5
S%& %
Fun'ton ( Purpose
1
AS*>#?C /eturns the +#II value o the character x.
2
*+>#?C /eturns the character with the +#II value o x.
(
*&*A">#: J?C oncatenates the strings x and y and return the appended string.
9
&"*AP>#?C onverts the initial letter o each word in x to uppercase and returns that string.
>
&S"+>#: ndNstrng O: start O: o''urren'e?C o''urren'e?C #earches or fnd7string in x and returns the position at which it occurs.
<
&S"+.>#?C /eturns the location o a string within another string, but returns the value in bytes.
:
L$&">#?C /eturns the number o characters in x.
;
L$&".>#?C /eturns the length o a character string in bytes or single byte character set.
D
L$+>#?C onverts the letters in x to lowercase and returns that string.
1@
LPA)>#: dt O: padNstrng? C ads x with spaces to let, to bring the total length o the string up to width characters.
11
L"+R># O: tr-Nstrng?C Trims Trims characters characters rom rom the let o o x.
&A&KL>#: va!ue?C 12 /eturns value i x matches the &a& special value )not a number*, otherwise x is returned.
1(
&LSN&"*AP>#?C #ame as the I&IT+ unction except that it can use a di3erent sort method as specifed by &!##4/T &!# #4/T..
19
&LSNL$+>#? C #ame as the !4C-/ unction except that it can use a di3erent sort method as specifed by &!##4/T &!# #4/T..
1>
&LSNIPP$+>#?C #ame as the '-/ unction except that it can use a di3erent sort method as specifed by &!##4/T &!# #4/T..
1<
&LSS+">#?C hanges the method o sorting the characters. 0ust be specifed beore any &!# unctionK otherwise, the deault sort will be used.
1:
&KL>#: va!ue?C /eturns value i x is nullK otherwise, x is returned.
1;
&KL2>#: va!ue1: va!ue2?C /eturns value1 i x is not nullK i x is null, value2 is returned.
1D
+$PLA*$>#: sear'Nstrng: rep!a'eNstrng?C #earches x or search7string and replaces it with replace7string.
2@
+PA)>#: dt O: padNstrng?C ads x to the right.
21
+"+R># O: tr-Nstrng?C Trims Trims x rom rom the right. right.
22
SI&)$>#? C /eturns a string containing the phonetic representation o x.
2(
SI.S"+>#: start O: !engt?C /eturns a substring o x that begins at the position specifed by start. +n optional length or the substring may be supplied.
29
SI.S"+.>#?C #ame as #'5#T/ except the parameters are expressed in bytes instead o characters or the singlebyte singlebyte character systems.
2>
2<
"+R>Otr-N'ar F+R? #?C Trims Trims characters characters rom rom the let and and right o x. x. IPP$+>#?C onverts the letters in x to uppercase and returns that string.
T+e *ollowin# examples illustrate some o* t+e aove-mentione" *unctions an" t+eir use5
$#a-p!e 1 DECLARE )reetin)s +arc(ar,# . 0(ello Qorld0*
BEGIN db!s5output6put5line%%ER)reetin)s##* db!s5output6put5lineL'3ER)reetin)s##* db!s5output6put5lineINI&CA%)reetin)s##* /; retrie+e t(e 9irst c(aracter in t(e strin) ;/ db!s5output6put5line 7B7&R )reetin)s2 2 ##* /; retrie+e t(e last c(aracter in t(e strin) ;/ db!s5output6put5line 7B7&R )reetin)s2 :2 ##* /; retrie+e 9i+e c(aracters2 startin) 9ro! t(e se+ent( position6 ;/ db!s5output6put5line 7B7&R )reetin)s2 J2 ##* /; retrie+e t(e re!ainder o9 t(e strin)2 startin) 9ro! t(e second position6 ;/ db!s5output6put5line 7B7&R )reetin)s2 ,##* /; 9ind t(e location o9 t(e 9irst "e" ;/ db!s5output6put5line IN7&R )reetin)s2 0e0##* END* /
:+en t+e aove co"e is execute" at SQL prompt1 it pro"uces t+e *ollowin# result5 1ELL' 3'RLD (ello Qorld 1ello 3orld ( d 3orld ello 3orld , %L/78L procedure success9ully co!pleted6
$#a-p!e 2 DECLARE )reetin)s +arc(ar,=-# . 06666661ello 3orld666660* BEGIN db!s5output6put5lineR&RI@)reetin)s2060##* db!s5output6put5lineL&RI@)reetin)s2 db!s5output6put5lineL&R I@)reetin)s2 060##* db!s5output6put5line&RI@ db!s5output6put5line&RI @ 060 9ro! )reetin)s##* END* /
:+en t+e aove co"e is execute" at SQL prompt1 it pro"uces t+e *ollowin# result5 6666661ello 3orld 1ello 3orld66666 1ello 3orld %L/78L procedure success9ully co!pleted6
PL/SQL pro#rammin# lan#ua#e provi"es a "ata structure calle" t+e V$&&$< V$&&$<11 w+ic+ can store a *ixe"-sie se9uential collection o* elements o* t+e same type, $ varray is use" to store an or"ere" collection o* "ata1 ut it is o*ten more use*ul to t+in' o* an array as a collection o* variales o* t+e same type,
$ll varrays consist o* conti#uous memory locations, T+e lowest a""ress correspon"s to t+e *irst element an" t+e +i#+est a""ress to t+e last element,
$n array is a part o* collection type "ata an" it stan"s *or variale-sie arrays, :e will stu"y ot+er collection types in a later c+apter 0PL/SQL !ollections0, Eac+ element in a varray +as an in"ex associate" wit+ it, t also +as a maximum sie t+at can e c+an#e" "ynamically " ynamically,,
*reatng a KarraJ "Jpe $ varray type is create" wit+ t+e !&E$TE !&E$TE T
:+ere1 •
•
•
varray_type_name is varray_type_name is a vali" attriute name1 n is t+e numer o* elements Gmaximum in t+e varray1 element_type is element_type is t+e "ata type o* t+e elements o* t+e array,
)aximum sie o* a varray can e c+an#e" usin# t+e $LTE& $LTE& T
T+e asic syntax *or creatin# a V&&$< type wit+in a PL/SQL loc' is5 &?%E +array5type5na!e I7 ARRA?n# o9
%or example5 &?%E na!earray I7 ARRA?# ' ARC1AR,-#* &ype )rades I7 ARRA?# ' IN&EGER*
$#a-p!e 1 T+e *ollowin# pro#ram illustrates usin# varrays5 v arrays5 DECLARE
type na!esarray I7 ARRA?# ' ARC1AR,-#* type )rades I7 ARRA?# ' IN&EGER* na!es na!esarray* !arMs )rades* total inte)er* BEGIN na!es . na!esarray0a+ita02 0%rita!02 0Ayan02 0Ris(a+02 0ASiS0#* !arMs. )radesK2 KJ2 J2 J2 K,#* total . na!es6count* db!s5output6put5line0&otal db!s5output6put5line0&o tal 0 total 0 7tudents0#* 'R i in 66 total L''% db!s5output6put5line07tudent. db!s5output6put5line07tu dent. 0 na!esi# 0 @arMs. 0 !arMsi##* END L''%* END* /
:+en t+e aove co"e is execute" at SQL prompt1 it pro"uces t+e *ollowin# result5 7tudent. 7tudent. 7tudent. 7tudent. 7tudent.
a+ita @arMs. K %rita! @arMs. KJ Ayan @arMs. J Ris(a+ @arMs. J ASiS @arMs. K,
%L/78L procedure success9ully co!pleted6
Please note5 •
•
•
•
n oracle environment1 t+e startin# in"ex *or varrays is always 3,
$#a-p!e 2 Elements o* a varray coul" also e a &O:T
%ollowin# example ma'es use o* cursor 1 w+ic+ you will stu"y in "etail in a separate c+apter,
DECLARE CR7'R c5custo!ers is 7ELEC& na!e R'@ custo!ers* type c5list is +array # o9 custo!ers6na!eOtype* na!e5list c5list . c5list#* counter inte)er .-* BEGIN 'R n IN c5custo!ers L''% counter . counter H * na!e5list6etend* na!e5listcounter# . n6na!e* db!s5output6put5line0Custo!er0counter db!s5output6put5line0Cus to!er0counter 0#.0na!e5listcounter 0#.0na!e5listcounter##* ##* END L''%* END* /
:+en t+e aove co"e is execute" at SQL prompt1 it pro"uces t+e *ollowin# result5 Custo!er#. Custo!er,#. Custo!er=#. Custo!erF#. Custo!er#. Custo!er#.
Ra!es( (ilan Maus(iM C(aitali 1ardiM o!al
%L/78L procedure success9ully co!pleted6
$ sub%rogra7 is a pro#ram unit/mo"ule t+at per*orms a particular tas', T+ese supro#rams are comine" to *orm lar#er pro#rams, p ro#rams, T+is is asically calle" t+e 0)o"ular "esi#n0, $ supro#ram supro#ram can e invo'e" y anot+er supro#ram or pro#ram w+ic+ is calle" t+e callin# pro#ram, $ supro#ram can e create"5 •
$t sc+ema level
•
nsi"e a pac'a#e
•
nsi"e a PL/SQL loc'
$ sc+ema level supro#ram is a standalone sub%rogra7 , t is create" wit+ t+e !&E$TE !&E$TE P&O!ED@&E or !&E$TE !&E$TE %@C!TOC statement, t is store" in t+e "ataase an" can e "elete" wit+ t+e D&OP P&O!ED@&E or D&OP %@C!TOC statement, $ supro#ram create" insi"e a pac'a#e is a %ac*aged sub%rogra7 , t is store" in t+e "ataase an" can e "elete" only w+en t+e pac'a#e pac'a# e is "elete" wit+ t+e D&OP P$!R$IE P$!R$IE statement, :e will "iscuss pac'a#es in t+e c+apter 0PL/SQL - Pac'a#es0, PL/SQL supro#rams are name" PL/SQL loc's t+at can e invo'e" wit+ a set o* parameters, PL/SQL provi"es two 'in"s o* supro#rams5 •
,unctions 5 t+ese supro#rams return a sin#le value1 mainly use" to compute an" return a value,
Procedures5 t+ese supro#rams "o not return a value "irectly1 mainly use" to per*orm an action,
•
T+is c+apter is #oin# to cover important aspects o* a PL/SQL %rocedure an" we will cover PL/SQL "unction in next c+apter,
Parts of a PL/SQL Su,prograEac+ PL/SQL supro#ram +as a name1 an" may ma y +ave a parameter list, Li'e anonymous anon ymous PL/SQL loc's an"1 t+e name" loc's a supro#rams will also +ave *ollowin# t+ree parts5
S%& %
Parts ( )es'rpton
1
)e'!aratve Part It is an optional part. owever, the declarative part or a subprogram does not start with the -!+/- keyword. It contains declarations o types, cursors, constants, variables, exceptions, and nested subprograms. These items are local to the subprogram and cease to exist when the subprogram completes execution.
2
$#e'uta,!e Part This is a mandatory mandatory part part and contains contains statements statements that perorm perorm the designated designated action.
(
$#'epton;and!ng This is again again an optional optional part. It contains contains the code that that handles runtime runtime errors. errors.
*reatng a Pro'edure $ proce"ure is create" wit+ t+e !&E$TE !&E$TE O& &EPL$!E P&O!ED@&E statement, T+e simpli*ie" syntax *or t+e !&E$TE !&E$TE O& &EPL$!E P&O!ED@&E statement is as *ollows5 CREA&E 'R RE%LACE %R'CEDRE procedure5na!e para!eter5na!e IN '& IN '& type 2 666# TI7 A7U BEGIN < procedure5body > END procedure5na!e*
:+ere1 •
•
•
•
procedure-name speci*ies procedure-name speci*ies t+e name o* t+e proce"ure, O& &EPL$!E option allows mo"i*yin# an existin# proce"ure, T+e optional parameter list contains name1 mo"e an" types o* t+e parameters, C represents t+at value will e passe" *rom outsi"e an" O@T represents t+at t+is parameter will e use" to return a value outsi"e o* t+e proce"ure, procedure-body contains procedure-body contains t+e executale part,
•
T+e $S 'eywor" is use" instea" o* t+e S 'eywor" *or creatin# a stan"alone proce"ure,
$#a-p!e T+e *ollowin# example creates a simple proce"ure t+at "isplays t+e strin# 0Hello :orl"U0 on t+e screen w+en execute", CREA&E 'R RE%LACE %R'CEDRE )reetin)s A7 BEGIN db!s5output6put5line01ello db!s5output6put5line01e llo 3orld40#* END* /
:+en aove co"e is execute" usin# SQL prompt1 it will pro"uce t+e *ollowin# result5 %rocedure created6
$#e'utng a Standa!one Pro'edure $ stan"alone proce"ure can e calle" in two ways5 •
@sin# t+e EE!@TE 'eywor"
•
!allin# t+e name o* t+e proce"ure proce" ure *rom a PL/SQL loc'
T+e aove proce"ure name" 0#reetin#s0 can e calle" wit+ t+e EE!@TE 'eywor" as5 E$EC&E )reetin)s*
T+e aove call woul" "isplay5 1ello 3orld %L/78L procedure success9ully co!pleted6
T+e proce"ure can also e calle" *rom anot+er PL/SQL loc'5 BEGIN )reetin)s* END* /
T+e aove call woul" "isplay5 1ello 3orld %L/78L procedure success9ully co!pleted6
)e!etng a Standa!one Pro'edure $ stan"alone proce"ure is "elete" wit+ t+e D&OP P&O!ED@&E statement, Syntax *or "eletin# a proce"ure is5 DR'% %R'CEDRE procedure:na!e*
So you can "rop greetings "rop greetings proce"ure proce"ure y usin# t+e *ollowin# statement5 BEGIN DR'% %R'CEDRE )reetin)s* END*
/
Para-eter Rodes n PL/SQL Su,progra-s S%& %
Para-eter Rode ( )es'rpton
1
& +n I& parameter lets you pass a value to the subprogram. t s a read;on!J para-eter. para-eter. Inside the subprogram, an I& parameter acts like a constant. It cannot be assigned a value. =ou can pass a constant, literal, initialiAed variable, or expression as an I& parameter. =ou can also initialiAe it to a deault valueK however, in that case, it is omitted rom the subprogram call. t s te defau!t -ode of para-eter passng% Para-eters are passed ,J referen'e%
2
I" +n 4'T parameter returns a value to the calling program. Inside the subprogram, an 4'T parameter acts like a variable. =ou can change its value and reerence the value ater assigning it. "e a'tua! para-eter -ust ,e vara,!e and t s passed ,J va!ue. va!ue.
2
& I" +n I& 4'T parameter passes an initial value to a subprogram and returns an updated value to the caller. It can be assigned a value and its value can be read. The actual parameter parameter correspond corresponding ing to an I& 4'T 4'T ormal ormal parameter must must be a variable, not a constant or an expression. 6ormal parameter must be assigned a value. A'tua! para-eter s passed ,J va!ue%
& ( I" Rode $#a-p!e 1 T+is pro#ram *in"s t+e minimum o* two values1 +ere proce"u re ta'es two numers usin# C mo"e an" returns t+eir minimum usin# O@T parameters, DECLARE a nu!ber* b nu!ber* c nu!ber* %R'CEDRE 9ind@in IN nu!ber2 y IN nu!ber2 S '& nu!ber# I7 BEGIN I < y &1EN S. * EL7E S. y* END I* END* BEGIN a. ,=* b. F* 9ind@ina2 b2 c#* db!s5output6put5line0 @ini!u! o9 ,=2 F# . 0 c#* END* /
:+en t+e aove co"e is execute" at SQL prompt1 it pro"uces t+e *ollowin# result5
@ini!u! o9 ,=2 F# . ,= %L/78L procedure success9ully co!pleted6
& ( I" Rode $#a-p!e 2 T+is proce"ure computes t+e s9uare o* value v alue o* a passe" value, T+is example s+ows + ow we can use same parameter to accept a value an" t+en return anot+er ano t+er result, DECLARE a nu!ber* %R'CEDRE squareNu! IN '& nu!ber# I7 BEGIN . ; * END* BEGIN a. ,=* squareNu!a#* db!s5output6put5line0 7quare o9 ,=#. 0 a#* END* /
:+en t+e aove co"e is execute" at SQL prompt1 it pro"uces t+e *ollowin# result5 7quare o9 ,=#. ,K %L/78L procedure success9ully co!pleted6
Retods for Passng Para-eters $ctual parameters coul" e passe" in t+ree ways5 •
Positional notation
•
Came" notation
•
)ixe" notation
PS"&AL &"A"& n positional notation1 you can call t+e proce"ure as5 9ind@ina2 b2 c2 d#*
n positional notation1 t+e *irst actual parameter is sustitute" *or t+e *irst *ormal parameter; t+e secon" actual parameter is sustitute" *or t+e secon" *ormal parameter1 an" so on, So1 a is sustitute" *or x1 is sustitute" *or y1 c is sustitute" *or an" " is sustitute" *or m,
&AR$) &"A"& n name" notation1 t+e actual parameter p arameter is associate" wit+ t+e *ormal parameter usin# t+e arrow symol G N , So t+e proce"ure call woul" loo' li'e5 9ind@in>a2 y>b2 S>c2 !>d#*
R$) &"A"& n mixe" notation1 you can mix ot+ notations in proce"ure call; +oweve r1 t+e positional notation s+oul" prece"e t+e name" notation,
T+e *ollowin# call is le#al5 9ind@ina2 b2 c2 !>d#*
But t+is is not le#al5 9ind@in>a2 b2 c2 d#*
$ PL/SQL *unction is same as a proce"ure except ex cept t+at it returns a value, T+ere*ore1 all t+e "iscussions o* t+e previous c+apter are true *or *unctions too,
*reatng a Fun'ton $ stan"alone *unction is create" usin# t+e !&E$TE !&E$TE %@C!TOC statement, T+e simpli*ie" syntax *or t+e !&E$TE O& &EPL$!E P&O!ED@&E statement is as *ollows5 CREA&E 'R RE%LACE NC&I'N 9unction5na!e para!eter5na!e IN '& IN '& type 2 666# RE&RN return5datatype TI7 A7U BEGIN < 9unction5body > END 9unction5na!e*
:+ere1 •
•
•
•
function-name speci*ies function-name speci*ies t+e name o* t+e *unction, O& &EPL$!E option allows mo"i*yin# an existin# *unction, T+e optional parameter list contains name1 mo"e an" types o* t+e parameters, C represents t+at value will e passe" *rom outsi"e an" O@T represents t+at t+is parameter will e use" to return a value outsi"e o* t+e proce"ure, T+e *unction must contain a return statement,
•
RETURN clause clause speci*ies t+at "ata type you are #oin# to return *rom t+e *unction,
•
function-body contains function-body contains t+e executale part,
•
T+e $S 'eywor" is use" instea" o* t+e S 'eywor" *or creatin# a stan"alone *unction,
$#a-p!e T+e *ollowin# example illustrates creatin# an" callin# a stan"alone *unction, T+is *unction returns t+e total numer o* !@STO)E&S in t+e customers tale, :e will will use t+e !@STO)E&S !@S TO)E&S tale1 w+ic+ we +a" create" in PL/SQL Variales c+apter5 Variales c+apter5 7elect ; 9ro! custo!ers* H::::H::::::::::H:::::H:::::::::::H::::::::::H ID NA@E AGE ADDRE77 7ALAR? H::::H::::::::::H:::::H:::::::::::H::::::::::H Ra!es( =, A(!edabad ,---6--
, (ilan , Del(i --6--- 6-- = Maus(iM ,= ota ,---6-,--- 6-- F C(aitali , @u!bai --6-- 1ardiM ,J B(opal --6-- o!al ,, @% F--6-- H::::H::::::::::H:::::H:::::::::::H::::::::::H CREA&E 'R RE%LACE NC&I'N totalCusto!ers RE&RN nu!ber I7 total nu!ber,# . -* BEGIN 7ELEC& count;# into total R'@ custo!ers* RE&RN total* END* /
:+en aove co"e is execute" usin# SQL prompt1 it will pro"uce t+e *ollowin# result5 unction created6
*a!!ng a Fun't Fun'ton on :+ile creatin# a *unction1 you #ive a "e*inition o* w+at t+e *unction +as to "o, To use a *unction1 you will +ave to call t+at *unction to per*orm t+e "e*ine" tas', :+en a pro#ram calls a *unction1 pro#ram control is trans*erre" to t+e calle" *unction, $ calle" *unction per*orms "e*ine" tas' an" w+en its return statement is execute" or w+en it last en" statement is reac+e"1 it returns pro#ram control ac' to t+e main pro#ram, To call a *unction you simply nee" to pass t+e re9uire" parameters alon# wit+ *unction name an" i* *unction returns a value t+en you can store returne" value, %ollowin# pro#ram calls t+e *unction total!ustomers *rom an anonymous loc'5 DECLARE c nu!ber,#* BEGIN c . totalCusto!ers#* db!s5output6put5line0&otal db!s5output6put5line0&o tal no6 o9 Custo!ers. 0 c#* END* /
:+en t+e aove co"e is execute" at SQL prompt1 it pro"uces t+e *ollowin# result5 &otal no6 o9 Custo!ers. %L/78L procedure success9ully co!pleted6
$#a-p!e T+e *ollowin# is one more example w+ic+ "emonstrates Declarin#1 De*inin#1 an" nvo'in# a Simple PL/SQL %unction t+at computes an" returns t+e maximum o* two values, DECLARE a nu!ber* b nu!ber* c nu!ber* NC&I'N 9ind@a IN nu!ber2 y IN nu!ber# RE&RN nu!ber I7
S nu!ber* BEGIN I > y &1EN S. * EL7E V. y* END I* RE&RN S* END* BEGIN a. ,=* b. F* c . 9ind@aa2 b#* db!s5output6put5line0 @ai!u! o9 ,=2F#. 0 c#* END* /
:+en t+e aove co"e is execute" at SQL prompt1 it pro"uces t+e *ollowin# result5 @ai!u! o9 ,=2F#. F %L/78L procedure success9ully co!pleted6
PL/SQL +e'ursve Fun'tons :e +ave seen t+at a pro#ram or supro#ram may call anot+er supro#ram, :+en a supro#ram calls itsel*1 it is re*erre" to as a recursive call an" t+e process is 'nown as recursion, To illustrate illustrate t+e concept1 let us calculate t+e *actorial o* a numer, %actorial o* a numer n is "e*ine" as5 n4 n;n:#4 n;n:#;n:,#4 666 n;n:#;n:,#;n:=#666
T+e *ollowin# pro#ram calculates t+e *actorial o* a # iven numer y callin# itsel* recursively5 DECLARE nu! nu!ber* 9actorial nu!ber* NC&I'N 9act nu!ber# RE&RN nu!ber I7 9 nu!ber* BEGIN I - &1EN 9 . * EL7E 9 . ; 9act:#* END I* RE&RN 9* END* BEGIN nu!. * 9actorial . 9actnu!#* db!s5output6put5line0 actorial 0 nu! 0 is 0 9actorial#* END*
/
:+en t+e aove co"e is execute" at SQL prompt1 it pro"uces t+e *ollowin# result5 actorial is J,%L/78L procedure success9ully co!pleted6
Oracle creates a memory area1 'nown as context area1 *or processin# an SQL statement1 w+ic+ contains all in*ormation nee"e" *or processin# t+e statement1 *or exa mple1 numer o* rows processe"1 etc, $ cursor is a pointer to t+is context area, PL/SQL controls t+e context area t+rou#+ a cursor, $ cursor +ol"s t+e rows Gone or more returne" y a SQL statement, T+e set o* rows t+e cursor +ol"s is re*erre" to as t+e acti5e set ,
mplicit cursors
•
Explicit cursors
-p!'t *ursors mplicit cursors are automatically create" y Oracle w+enever an SQL statement is execute"1 w+en t+ere is no explicit cursor *or t+e statement, Pro#rammers canno t control t+e implicit cursors an" t+e in*ormation in it, :+enever a D)L statement GCSE&T1 GCSE&T1 @PD$TE @PD$TE an" DELETE is issue"1 an implicit cursor is associate" wit+ t+is statement, %or CSE&T operations1 t+e cursor +ol"s t+e "ata t+at nee"s to e inserte", %or @PD$TE @PD$TE an" DELETE operations1 t+e cursor i"enti*ies t+e rows t+at woul" e a**ecte", n PL/SQL1 you can re*er to t+e most recent implicit cursor as t+e SQL cursor1 w+ic+ always +as t+e attriutes li'e %O@CD1 SOPEC1 COT%O@CD1 an" &O:!O@CT, T+e SQL cursor +as a""itional attriutes1 B@LRF&O:!O@CT an" B@LRFE!EPTOCS1 "esi#ne" *or use wit+ t+e %O&$LL statement, T+e *ollowin# tale provi"es t+e "escription o* t+e most use" attriutes5
Attr,ute 64'&
&4T64'&
)es'rpton /eturns T/'- i an I&#-/T, '+T-, or -!-T- statement a3ected one or more rows or a #-!-T I&T4 statement returned one or more rows. 4therwise, it returns 6+!#-. The logical opposite o 64'&. 64'&. It returns returns T/'T/'- i an I&#-/ I&#-/T, T, '+T-, or -!-T- statement a3ected no rows, or a #-!-T I&T4 statement returned no rows. 4therwise, it returns 6+!#-.
I#4-&
+lways returns 6+!#- or implicit cursors, because 4racle closes the #$! cursor automatically ater executing its associated #$! statement.
/4C4'&T
/eturns the number o rows a3ected by an I&#-/T, '+T-, or -!-T- statement, or returned by a #-!-T I&T4 statement.
$ny SQL cursor attriute will e accesse" as sql:attribute_na7e as s+own elow in t+e example,
$#a-p!e :e will e usin# t+e !@STO)E&S tale we +a" create" an" use" in t+e previous c+apters, c+a pters, 7elect ; 9ro! custo!ers* H::::H::::::::::H:::::H:::::::::::H::::::::::H ID NA@E AGE ADDRE77 7ALAR? H::::H::::::::::H:::::H:::::::::::H::::::::::H Ra!es( =, A(!edabad ,---6-- , (ilan , Del(i --6--- 6-- = Maus(iM ,= ota ,---6-,--- 6-- F C(aitali , @u!bai --6-- 1ardiM ,J B(opal --6-- o!al ,, @% F--6-- H::::H::::::::::H:::::H:::::::::::H::::::::::H
T+e *ollowin# pro#ram woul" up"ate t+e tale an" increase salary o* eac+ customer y K an" use t+e SQL&O:!O@CT attriute to "etermine t+e numer o* rows a**ecte"5 DECLARE total5roQs nu!ber,#* BEGIN %DA&E custo!ers 7E& salary salary H --* I sqlOnot9ound &1EN db!s5output6put5line0no custo!ers selected0#* EL7I sqlO9ound &1EN total5roQs . sqlOroQcount* db!s5output6put5line total5roQs 0 custo!ers selected 0#* END I* END* /
:+en t+e aove co"e is execute" at SQL prompt1 it pro"uces t+e *ollowin# result5 custo!ers selected %L/78L procedure success9ully co!pleted6
* you c+ec' t+e recor"s in customers tale1 you will *in" t+at t+e rows +ave een up"ate"5 7elect ; 9ro! custo!ers* H::::H::::::::::H:::::H:::::::::::H::::::::::H ID NA@E AGE ADDRE77 7ALAR? H::::H::::::::::H:::::H:::::::::::H::::::::::H Ra!es( =, A(!edabad ,--6-- , (ilan , Del(i ,---6-,--- 6-- = Maus(iM ,= ota ,--6-,-- 6-- F C(aitali , @u!bai J---6--
1ardiM ,J B(opal K---6-- o!al ,, @% ---6-- H::::H::::::::::H:::::H:::::::::::H::::::::::H
$#p!'t *ursors Explicit cursors are pro#rammer "e*ine" cursors *or #ainin# more control over t+e conte't area , $n explicit cursor s+oul" e "e*ine" in t+e "eclaration section o* t+e PL/SQL Bloc', t is create" on a SELE!T Statement w+ic+ returns more t+an o ne row, T+e syntax *or creatin# an explicit cursor is 5 CR7'R cursor5na!e I7 select5state!ent*
:or'in# :or'in# wit+ an explicit cursor involves *our steps5 •
Declarin# t+e cursor *or initialiin# in t+e memory
•
Openin# t+e cursor *or allocatin# memory
•
%etc+in# t+e cursor *or retrievin# "ata
•
!losin# t+e cursor to release allocate" memory
)e'!arng te *ursor Declarin# t+e cursor "e*ines t+e cursor wit+ a name an" t+e associate" SELE!T statement, %or example5 CR7'R c5custo!ers I7 7ELEC& id2 na!e2 address R'@ custo!ers*
penng te *ursor Openin# t+e cursor allocates memory *or t+e cursor an" ma'es it rea"y *or *etc+in# t+e rows returne" y t+e SQL statement into it, %or example1 we will open aove-"e*ine" cursor as *ollows5 '%EN c5custo!ers*
Fet'ng te *ursor %etc+in# t+e cursor involves accessin# one row at a time, %or example we will *etc+ rows *rom t+e aove-opene" cursor as *ollows5 E&C1 c5custo!ers IN&' c5id2 c5na!e2 c5addr*
*!osng te *ursor !losin# t+e cursor means releasin# t+e allocate" memory, %or example1 we will close aoveopene" cursor as *ollows5 CL'7E c5custo!ers*
$#a-p!e %ollowin# is a complete example to illustrate t+e concepts o* explicit cursors5
DECLARE c5id custo!ers6idOtype* c5na!e custo!ers6na!eOtype* c5addr custo!ers6addressOtype* CR7'R c5custo!ers is 7ELEC& id2 na!e2 address R'@ custo!ers* BEGIN '%EN c5custo!ers* L''% E&C1 c5custo!ers into c5id2 c5na!e2 c5addr* E$I& 31EN c5custo!ersOnot9ound* db!s5output6put5linec5id 0 0 c5na!e 0 0 c5addr#* END L''%* CL'7E c5custo!ers* END* /
:+en t+e aove co"e is execute" at SQL prompt1 it pro"uces t+e *ollowin# result5 , = F
Ra!es( A(!edabad (ilan Del(i Maus(iM ota C(aitali @u!bai 1ardiM B(opal o!al @%
%L/78L procedure success9ully co!pleted6
$ PL/SQL record is a "ata structure t+at can +ol" "ata items o* "i**erent 'in"s, &ecor"s consist o* "i**erent *iel"s1 similar to a row o* a "ataase tale, %or example1 you want to 'eep trac' o* your oo's in a lirary,
Tale-ase"
•
!ursor-ase" recor"s
•
@ser-"e*ine" recor"s
"a,!e;.ased +e'ords T+e &O:T
7ELEC& ; into custo!er5rec R'@ custo!ers 31ERE id * db!s5output6put5line0Cu sto!er db!s5output6put5line0Custo!er db!s5output6put5line0Custo!er db!s5output6put5line0Cu sto!er db!s5output6put5line0Custo!er db!s5output6put5line0Cu sto!er db!s5output6put5line0Custo!er db!s5output6put5line0Cu sto!er END* /
ID. 0 custo!er5rec6id#* Na!e. 0 custo!er5rec6na!e#* Address. 0 custo!er5rec6address#* 7alary. 0 custo!er5rec6salary#*
:+en t+e aove co"e is execute" at SQL prompt1 it pro"uces t+e *ollowin# result5 Custo!er Custo!er Custo!er Custo!er
ID. Na!e. 1ardiM Address. B(opal 7alary. K---
%L/78L procedure success9ully co!pleted6
*ursor;.ased +e'ords T+e *ollowin# example woul" illustrate t+e concept o* cursor-based recor"s, :e will e usin# t+e !@STO)E&S tale we +a" create" an" use" in t+e previous c+apters5 DECLARE CR7'R custo!er5cur is 7ELEC& id2 na!e2 address R'@ custo!ers* custo!er5rec custo!er5curOroQtype* BEGIN '%EN custo!er5cur* L''% E&C1 custo!er5cur into custo!er5rec* E$I& 31EN custo!er5curOnot9ound* DB@75'&%&6put5linecusto!er5rec6id DB@75'&%&6put5linecust o!er5rec6id 0 0 custo!er5rec6na!e#* END L''%* END* /
:+en t+e aove co"e is execute" at SQL prompt1 it pro"uces t+e *ollowin# result5 , = F
Ra!es( (ilan Maus(iM C(aitali 1ardiM o!al
%L/78L procedure success9ully co!pleted6
Iser;)ened +e'ords PL/SQL provi"es a user-"e*ine" recor" type t+at allows you to "e*ine "i**erent recor" structures, &ecor"s consist o* "i**erent *iel"s, Suppose you want to 'eep trac' o* your oo's in a lirary,
Title
•
$ut+or
•
Su8ect
•
Boo' D
)enng a +e'ord T+e recor" type is "e*ine" as5 &?%E type5na!e I7 REC'RD 9ield5na!e datatype N'& NLL . DEAL& E$%RE77I'N2 9ield5na!e, datatype, N'& NLL . DEAL& E$%RE77I'N2 666 9ield5na!eN datatypeN N'& NLL . DEAL& E$%RE77I'N#* record:na!e type5na!e*
Here is t+e way you woul" "eclare t+e Boo' recor"5 DECLARE &?%E booMs I7 REC'RD title +arc(ar-#2 aut(or +arc(ar-#2 subPect +arc(ar--#2 booM5id nu!ber#* booM booMs* booM, booMs*
A''essng Fe!ds To access any *iel" o* a recor"1 we use t+e "ot G, op erator, T+e memer access operator is co"e" as a perio" etween t+e recor" variale name an" t+e *iel" t+at we wis+ to access, %ollowin# is t+e example to explain usa#e o* recor"5 DECLARE type booMs is record title +arc(ar-#2 aut(or +arc(ar-#2 subPect +arc(ar--#2 booM5id nu!ber#* booM booMs* booM, booMs* BEGIN :: BooM speci9ication booM6title . 0C %ro)ra!!in)0* booM6aut(or . 0Nu(a Ali 0* booM6subPect . 0C %ro)ra!!in) &utorial0* booM6booM5id . FKF-J* :: BooM , speci9ication booM,6title . 0&eleco! Billin)0* booM,6aut(or . 0Vara Ali0* booM,6subPect . 0&eleco! Billin) &utorial0* booM,6booM5id . FKJ--* :: %rint booM record db!s5output6put5line0BooM db!s5output6put5line0Bo oM db!s5output6put5line0BooM db!s5output6put5line0Bo oM db!s5output6put5line0BooM db!s5output6put5line0Bo oM db!s5output6put5line0BooM db!s5output6put5line0Bo oM :: %rint booM , record
title . 0 booM6title#* aut(or . 0 booM6aut(or#* subPect . 0 booM6subPect#* booM5id . 0 booM6booM5id#*
db!s5output6put5line0Bo oM db!s5output6put5line0BooM db!s5output6put5line0BooM db!s5output6put5line0Bo oM db!s5output6put5line0BooM db!s5output6put5line0Bo oM db!s5output6put5line0BooM db!s5output6put5line0Bo oM END* /
, , , ,
title . 0 booM,6title#* aut(or . 0 booM,6aut(or#* subPect . 0 booM,6subPect#* booM5id . 0 booM,6booM5id#*
:+en t+e aove co"e is execute" at SQL prompt1 it pro"uces t+e *ollowin# result5 BooM BooM BooM BooM BooM BooM BooM BooM
, , , ,
title . C %ro)ra!!in) aut(or . Nu(a Ali subPect . C %ro)ra!!in) &utorial booM5id . FKF-J title . &eleco! Billin) aut(or . Vara Ali subPect . &eleco! Billin) &utorial booM5id . FKJ--
%L/78L procedure success9ully co!pleted6
+e'ords as Su,progra- Para-eters
:+en t+e aove co"e is execute" at SQL prompt1 it pro"uces t+e *ollowin# result5
BooM BooM BooM BooM BooM BooM BooM BooM
title . C %ro)ra!!in) aut(or . Nu(a Ali subPect . C %ro)ra!!in) &utorial booM5id . FKF-J title . &eleco! Billin) aut(or . Vara Ali subPect . &eleco! Billin) &utorial booM5id . FKJ--
%L/78L procedure success9ully co!pleted6
$n error con"ition "urin# a pro#ram execution ex ecution is calle" an exception in PL/SQL, PL/SQL supports pro#rammers to catc+ suc+ con"itions usin# 2;(2PTO& loc' in t+e pro#ram an" an appropriate action is ta'en a#ainst t+e error con"ition, T+ere are two types o* exceptions5 •
System-"e*ine" exceptions
•
@ser-"e*ine" exceptions
SJnta# for $#'epton and!ng T+e Ieneral Syntax *or exception +an"lin# is as *ollows, Here you can list "own as many as exceptions you want to +an"le, T+e "e*ault exception will e +an"le" usin# WHEN others THEN 5 DECLARE BEGIN E$CE%&I'N 31EN eception &1EN eception:(andlin):state!ents 31EN eception, &1EN eception,:(andlin):state!ents 31EN eception= &1EN eception=:(andlin):state!ents 66666666 31EN ot(ers &1EN eception=:(andlin):state!ents END*
$#a-p!e Let us write some simple co"e to illustrate t+e concept, :e will e usin# t+e !@STO)E&S tale we +a" create" an" use" in t+e previous c+apters5 DECLARE c5id custo!ers6idOtype . * c5na!e custo!ers6na!eOtype* c5addr custo!ers6addressOtype* BEGIN 7ELEC& na!e2 address IN&' c5na!e2 c5addr R'@ custo!ers 31ERE id c5id*
DB@75'&%&6%&5LINE 0Na!e. 0 c5na!e#* DB@75'&%&6%&5LINE 0Address. 0 c5addr#* E$CE%&I'N 31EN no5data59ound &1EN db!s5output6put5line0No suc( custo!er40#* 31EN ot(ers &1EN db!s5output6put5line0Error40#* END* /
:+en t+e aove co"e is execute" at SQL prompt1 it pro"uces t+e *ollowin# result5 No suc( custo!er4 %L/78L procedure success9ully co!pleted6
T+e aove pro#ram "isplays t+e name na me an" a""ress o* a customer w+ose D is #iven, Since t+ere is no customer wit+ D value 4 in our "ataase1 t+e pro#ram raises t+e run-time exception &O_)ATA_,O&)1 w+ic+ is capture" in 2;(2PTO& loc',
+asng $#'eptons Exceptions are raise" y t+e "ataase server automatically w+enever t+ere is any internal "ataase error1 ut exceptions can e raise" explicitly y t+e pro#rammer y usin# t+e comman" AS2, %ollowin# is t+e simple syntax o* raisin# an exception5 DECLARE eception5na!e E$CE%&I'N* BEGIN I condition &1EN RAI7E eception5na!e* END I* E$CE%&I'N 31EN eception5na!e &1EN state!ent* END*
Iser;dened $#'eptons PL/SQL allows you to "e*ine your own exceptions accor"in# to t+e nee" o* your pro#ram, $ user-"e*ine" exception must e "eclare" an" t+en raise" explicitly1 usin# eit+er a &$SE statement or t+e proce"ure DB)SFST$CD$&D,&$SEF$PPL!$ DB)SFST$CD$&D,&$SEF$PPL!$T TOCFE&&O&, OCFE&&O&, T+e syntax *or "eclarin# an exception exce ption is5 DECLARE !y:eception E$CE%&I'N*
$#a-p!e T+e *ollowin# example illustrates t+e concept, T+is pro#ram as's *or a customer D1 w+e n t+e user enters an invali" D1 t+e exception invali"Fi" is raise", DECLARE
c5id custo!ers6idOtype . Wcc5id* c5na!e custo!ers6na!eOtype* c5addr custo!ers6addressOtype* :: user de9ined eception e5in+alid5id E$CE%&I'N* BEGIN I c5id < - &1EN RAI7E e5in+alid5id* EL7E 7ELEC& na!e2 address IN&' R'@ custo!ers 31ERE id c5id*
c5na!e2 c5addr
DB@75'&%&6%&5LINE 0Na!e. 0 c5na!e#* DB@75'&%&6%&5LINE 0Address. 0 c5addr#* END I* E$CE%&I'N 31EN e5in+alid5id &1EN db!s5output6put5line0ID !ust be )reater t(an Sero40#* 31EN no5data59ound &1EN db!s5output6put5line0No suc( custo!er40#* 31EN ot(ers &1EN db!s5output6put5line0Error40#* END* /
:+en t+e aove co"e is execute" at SQL prompt1 it pro"uces t+e *ollowin# result5 Enter +alue 9or cc5id. : let0s enter a +alue :# old ,. c5id custo!ers6idOtype . Wcc5id* neQ ,. c5id custo!ers6idOtype . :* ID !ust be )reater t(an Sero4 %L/78L procedure success9ully co!pleted6
Pre;dened $#'eptons PL/SQL provi"es many pre-"e*ine" exceptions1 w+ic+ are execute" w+en any "ataase rule is violate" y a pro#ram, %or example1 t+e pre"e*ine" exception COFD$T$F%O@CD COFD$T$F%O@CD is raise" w+en a SELE!T CTO statement returns no rows, T+e *ollowin# tale lists *ew o* t+e important pre-"e*ine" exceptions5
$#'epton
ra'!e SQL* $rror )$
)es'rpton
<>( <>(@ @
It is raised when a null ob%ect is automatically assigned a value.
@<>D2
<>D2
It is raised when none o the choices in the C-& clauses o a +#- statement is selected, and there is no -!#- clause.
4!!-TI4&7I#7&'! @<>( @<>(1 1 !
<>( <>(1 1
+ -# -##7 #7I& I&T4 T47& 7&'! '!! ! @<>( @<>(@ @
+#-7&4T764'&
It is rais raised ed whe when n a pr progra ogram m att attem empt pts s to to app apply ly collection methods other than -VI#T# to an uninitialiAed nested table or varray, or the program attempts to assign values to the elements o an uninitialiAed nested table or
varray.
'7 '7B+!7 +!74&7I& 7I&-V -V @@@@ @@@@1 1
I&B+!I7'/#4/
I&B+!I7&'05-/
@1@@1
@1:22
1
It is raised when duplicate values are attempted to be stored in a column with uniOue index.
1@@1
It is raised when attempts are made to make a cursor operation that is not allowed, such as closing an unopened cursor.
1:22
It is raised when the conversion o a character string into a number ails because the string does not represent a valid number.
!48I&7-&I-
@1@1:
1@1:
It is raised when s program attempts to log on to the database with an invalid username or password.
&47+T+764'&
@19@(
G1@@
It is raised when a #-!-T I&T4 statement returns no rows.
&4T7!488-74&
@1@12
1@12
It is raised when a database call is issued without being connected to the database.
/48/+07-//4/
@<>@1
<>@1
It is raised when !"#$! has an internal problem.
/4CT=-70I#0+T @<>@9
<>@9
It is raised when a cursor etches value in a variable having incompatible data type.
#-!67I#7&'!!
(@<2>
It is raised when a member method is (@<2> invoked, but the instance o the ob%ect type was not initialiAed.
#T4/+8-7-//4/
@<>@@
<>@@
It is raised when !"#$! ran out o memory or memory was corrupted.
T4470+&=7/4C# T4470+&=7/4C#
@1922
1922
It is raised when s #-!-T I&T4 statement returns more than one row.
B+!'-7-//4/
@<>@2
<>@2
It is raised when an arithmetic, conversion, truncation, or siAeconstraint error occurs.
E-/47IBI-
@19:<
19:<
It is raised when an attempt is made to divide a number by Aero.
Tri##ers are store" pro#rams1 w+ic+ are automatically execute" or *ire" w+en some even ts occur, Tri##ers are1 in *act1 written to e execute" in response to an y o* t+e *ollowin# events5 •
$ "ataase manipulation GD)L statement GDELETE1 CSE&T1 or @PD$TE,
•
$ "ataase "e*inition GDDL statement G!&E$TE1 $LTE&1 or D&OP,
•
$ "ataase operation GSE&VE&E&&O&1 LOIOC1 LOIO%%1 ST$&T@P1 or SH@TDO:C,
Tri##ers coul" e "e*ine" on t+e tale1 view1 sc+ema1 or "ataase wit+ w+ic+ t+e ev ent is associate",
.enets of "rggers Tri##ers can e written *or t+e *ollowin# purposes5 •
Ieneratin# some "erive" column values automatically
•
En*orcin# re*erential inte#rity
•
Event lo##in# an" storin# in*ormation on tale access
•
$u"itin#
•
Sync+ronous replication o* tales
•
mposin# security aut+oriations
•
Preventin# invali" transactions
*reatng "rggers T+e syntax *or creatin# a tri##er is5 CREA&E 'R RE%LACE &RIGGER tri))er5na!e TBE'RE A&ER IN7&EAD ' U TIN7ER& 'R %DA&E 'R DELE&EU ' col5na!e 'N table5na!e REERENCING 'LD A7 o NE3 A7 n 'R EAC1 R'3 31EN condition# DECLARE Declaration:state!ents BEGIN Eecutable:state!ents E$CE%&I'N Eception:(andlin):state!ents END*
:+ere1 •
•
•
!&E$TE !&E$TE O& &EPL$!E T&IIE& tri##erFname5 !reates or replaces an existin# tri##er wit+ t+e trigger_name, trigger_name, BE%O&E $%TE& CSTE$D O%W 5 T+is speci*ies w+en t+e tri##er woul" e execute", T+e CSTE$D O% clause is use" *or creatin# tri##er on a view, CSE&T O& O& @PD$TE @PD$TE O& DELETEW5 T+is speci*ies t+e D)L operation,
•
O% colFname5 T+is speci*ies t+e column name t+at woul" e up"ate",
•
OC taleFname5 T+is speci*ies t+e name o* t+e tale associate" wit+ t+e tri##er,
•
•
•
&E%E&EC!CI OLD $S o CE: $S n5 T+is allows you to re*er new an" ol" values *or various D)L statements1 li'e CSE&T1 @PD$TE1 an" DELETE, %O& E$!H &O:5 T+is speci*ies a row level tri##er1 i,e,1 t+e tri##er woul" e execute" *or eac+ row ein# a**ecte", Ot+erwise t+e tri##er will execute 8ust once w+en t+e SQL statement is execute"1 w+ic+ is calle" a tale level tri##er, :HEC Gcon"ition5 T+is provi"es a con"ition *or rows *or w+ic+ t+e tri##er woul" *ire, T+is clause is vali" only *or row level tri##ers,
$#a-p!e To start start wit+1 we will e usin# t+e !@STO)E&S tale we +a" create" an" use" in t+e previous c+apters5 7elect ; 9ro! custo!ers* H::::H::::::::::H:::::H:::::::::::H::::::::::H ID NA@E AGE ADDRE77 7ALAR? H::::H::::::::::H:::::H:::::::::::H::::::::::H Ra!es( =, A(!edabad ,---6-- , (ilan , Del(i --6--- 6-- = Maus(iM ,= ota ,---6-,--- 6-- F C(aitali , @u!bai --6-- 1ardiM ,J B(opal --6-- o!al ,, @% F--6-- H::::H::::::::::H:::::H:::::::::::H::::::::::H
T+e *ollowin# pro#ram creates a row le5el tri##er *or t+e customers tale t+at woul" *ire *or CSE&T or @PD$TE @PD$TE or DELETE operations per*orme" on t+e !@STO)E&S tale, T+is tri##er will "isplay t+e salary "i**erence etween t+e ol" values an" new values5 CREA&E 'R RE%LACE &RIGGER display5salary5c(an)es BE'RE DELE&E 'R IN7ER& 'R %DA&E 'N custo!ers 'R EAC1 R'3 31EN NE36ID > -# DECLARE sal5di99 nu!ber* BEGIN sal5di99 . .NE36salary : .'LD6salary* db!s5output6put5line0'ld db!s5output6put5line0'l d salary. 0 .'LD6salary#* db!s5output6put5line0NeQ db!s5output6put5line0Ne Q salary. 0 .NE36salary#* db!s5output6put5line07alary db!s5output6put5line07a lary di99erence. 0 sal5di99#* END* /
:+en t+e aove co"e is execute" at SQL prompt1 it pro"uces t+e *ollowin# result5 &ri))er created6
Here *ollowin# two points are important an" s+oul" e note" care*ully5
•
•
•
OLD an" CE: re*erences are not availale *or tale level tri##ers1 rat+er you can use t+em *or recor" level tri##ers, * you want to 9uery t+e tale in t+e same tri##er1 t+en you s+oul" use t+e $%TE& 'eywor"1 ecause tri##ers can 9uery t+e tale or c+an#e it a#ain a#a in only a*ter t+e initial c+an#es are applie" an" t+e tale is ac' in a consistent state, $ove tri##er +as een written in suc+ a way t+at it will *ire e*ore any DELETE or CSE&T or @PD$TE @PD$TE operation on t+e tale1 ut you can write your tri##er on a sin#le or multiple operations1 *or example BE%O&E DELETE1 w+ic+ will *ire w+enever a recor" will e "elete" usin# DELETE operation on t+e tale,
"rgger rggerng ng a "rgger "rgger Let us per*orm some D)L operations on t+e !@STO)E&S tale, Here is one CSE&T statement1 w+ic+ will create a new recor" in t+e tale5 IN7ER& IN&' C7&'@ER7 ID2NA@E2AGE2ADDRE7727ALAR?# ALE7 J2 0riti02 ,,2 01%02 J--6-- #*
:+en a recor" is create" in !@STO)E&S !@S TO)E&S tale1 aove create tri##er dis%la<_salar<_canges will e *ire" an" it will "isplay t+e *ollowin# result5 'ld salary. NeQ salary. J-7alary di99erence.
Because t+is is a new recor" so ol" salary is not availale an" aove result is comin# as null, Cow1 let let us per*orm one more D)L operation on t+e !@STO)E&S !@STO)E&S tale, Here is one @PD$TE @PD$TE statement1 w+ic+ will up"ate an existin# recor" in t+e tale5 %DA&E custo!ers 7E& salary salary H -31ERE id ,*
:+en a recor" is up"ate" in !@STO)E&S tale1 aove create tri##er dis%la<_salar<_canges will e *ire" an" it will "isplay t+e *ollowin# result5 'ld salary. -NeQ salary. ,--7alary di99erence. --
PL/SQL pac'a#es are sc+ema o8ects t+at #roups lo#ically relate" PL/SQL types1 variales an" supro#rams, $ pac'a#e will +ave two man"atory parts5 •
Pac'a#e speci*ication
•
Pac'a#e o"y or "e*inition
Pa'age Spe''aton T+e speci*ication is t+e inter*ace to t+e pac'a#e, t 8ust DE!L$&ES t+e types1 variales1 constants1 exceptions1 cursors1 an" supro#rams t+at can e re*erence" *rom outsi"e t+e pac'a#e, n ot+er wor"s1 it contains all in*ormation aout t+e conten t o* t+e pac'a#e1 ut exclu"es t+e co"e *or t+e supro#rams, $ll o8ects place" in t+e speci*ication are calle" %ublic o8ects, $ny supro#ram not in t+e pac'a#e speci*ication ut co"e" in t+e pac'a#e o"y is calle" a %ri5ate o8ect, T+e *ollowin# co"e snippet s+ows a pac'a#e pa c'a#e speci*ication +avin# a sin#le proce"ure,
:+en t+e aove co"e is execute" at SQL prompt1 it pro"uces t+e *ollowin# result5 %acMa)e created6
Pa'age .odJ T+e pac'a#e o"y +as t+e co"es *or various met+o"s "eclare" in t+e pac'a#e speci*ication an" ot+er private "eclarations1 w+ic+ are +i""en *rom co"e outsi"e t+e pac'a#e, T+e !&E$TE !&E$TE P$!R$IE P$!R$IE BOD< Statement is use" *or creatin# t+e pac'a#e p ac'a#e o"y, o" y, T+e *ollowin# co"e snippet s+ows t+e pac'a#e o"y "eclaration *or t+e cust_sal pac'a#e pac'a#e create" aove, assume" t+at we alrea"y +ave !@STO)E&S tale create" in our "ataase as mentione" in PL/SQL - Variales c+apter, Variales c+apter, CREA&E 'R RE%LACE %ACAGE B'D? cust5sal A7 %R'CEDRE 9ind5salc5id custo!ers6idO&?%E# I7 c5sal custo!ers6salaryO&?%E* BEGIN 7ELEC& salary IN&' c5sal R'@ custo!ers 31ERE id c5id* db!s5output6put5line07alary. db!s5output6put5line07al ary. 0 c5sal#* END 9ind5sal* END cust5sal* /
:+en t+e aove co"e is execute" at SQL prompt1 it pro"uces t+e *ollowin# result5 %acMa)e body created6
Isng te Pa'age $!e-ents T+e pac'a#e elements Gvariales1 proce"ures or *unctions are accesse" wit+ t+e *ollowin# syntax5 pacMa)e5na!e6ele!ent5na!e*
!onsi"er1 we alrea"y +ave create" aove pac'a#e in our "ataase sc+ema1 t+e *ollowin# pro#ram uses t+e find_sal t+e find_sal met+o" met+o" o* t+e cust_sal pac'a#e5 pac'a#e5
DECLARE code custo!ers6idOtype . Wcc5id* BEGIN cust5sal69ind5salcode#* END* /
:+en t+e aove co"e is execute" at SQL prompt1 it prompt to enter customer D an" w+en you enter an D1 it "isplays correspon"in# salary as *ollows5 Enter +alue 9or cc5id. 7alary. =--%L/78L procedure success9ully co!pleted6
$#a-p!e T+e *ollowin# pro#ram provi"es a more complete pac'a#e, :e will use t+e !@STO)E&S tale store" in our "ataase wit+ t+e *ollowin# recor"s5 7elect ; 9ro! custo!ers* H::::H::::::::::H:::::H:::::::::::H::::::::::H ID NA@E AGE ADDRE77 7ALAR? H::::H::::::::::H:::::H:::::::::::H::::::::::H Ra!es( =, A(!edabad =---6-- , (ilan , Del(i =---6-=--- 6-- = Maus(iM ,= ota =---6-=--- 6-- F C(aitali , @u!bai J--6-- 1ardiM ,J B(opal K--6-- o!al ,, @% --6-- H::::H::::::::::H:::::H:::::::::::H::::::::::H
"$ PA*TA$ SP$*F*A"& SP$* F*A"& CREA&E 'R RE%LACE %ACAGE c5pacMa)e A7 :: Adds a custo!er %R'CEDRE addCusto!erc5id custo!ers6idOtype2 c5na!e custo!ers6na!eOtype2 c5a)e custo!ers6a)eOtype2 c5addr custo!ers6addressOtype2 c5sal custo!ers6salaryOtype#* :: Re!o+es a custo!er %R'CEDRE delCusto!erc5id ::Lists all custo!ers %R'CEDRE listCusto!er*
custo!ers6idO&?%E#*
END c5pacMa)e* /
:+en t+e aove co"e is execute" at SQL prompt1 it creates t+e aove pac'a#e an" "isplays t+e *ollowin# result5 %acMa)e created6
*+$A"& "$ PA*TA$ .)U CREA&E 'R RE%LACE %ACAGE B'D? c5pacMa)e A7 %R'CEDRE addCusto!erc5id custo!ers6idOtype2 c5na!e custo!ers6na!eOtype2 c5a)e custo!ers6a)eOtype2 c5addr custo!ers6addressOtype2 c5sal custo!ers6salaryOtype#
I7 BEGIN IN7ER& IN&' custo!ers id2na!e2a)e2address2salary id2na!e2a)e2address2salary# # ALE7c5id2 c5na!e2 c5a)e2 c5addr2 c5sal#* END addCusto!er* %R'CEDRE delCusto!erc5id BEGIN DELE&E R'@ custo!ers 31ERE id c5id* END delCusto!er*
custo!ers6idOtype# I7
%R'CEDRE listCusto!er I7 CR7'R c5custo!ers is 7ELEC& na!e R'@ custo!ers* &?%E c5list is &ABLE ' custo!ers6na!eOtype* na!e5list c5list . c5list#* counter inte)er .-* BEGIN 'R n IN c5custo!ers L''% counter . counter H* na!e5list6etend* na!e5listcounter# . n6na!e* db!s5output6put5line0Custo!er0 db!s5output6put5line0Cus to!er0 counter 0#0na!e5listcounter##* END L''%* END listCusto!er* END c5pacMa)e* /
$ove example ma'es use o* nested table w+ic+ we will "iscuss in t+e next c+apter, :+en t+e aove co"e is execute" at SQL prompt1 it pro"uces t+e *ollowin# result5 %acMa)e body created6
IS& "$ PA*TA$ T+e *ollowin# pro#ram uses t+e met+o"s "eclare" an" "e*ine" in t+e pac'a#e c_package, c_package, DECLARE code custo!ers6idOtype. * BEGIN c5pacMa)e6addcusto!erJ2 0RaPnis(02 ,2 0C(ennai02 =--#* c5pacMa)e6addcusto!er2 07ub(a!02 =,2 0Del(i02 J--#* c5pacMa)e6listcusto!er* c5pacMa)e6delcusto!ercode#* c5pacMa)e6listcusto!er* END* /
:+en t+e aove co"e is execute" at SQL prompt1 it pro"uces t+e *ollowin# result5 Custo!er#. Custo!er,#. Custo!er=#. Custo!erF#. Custo!er#. Custo!er#. Custo!erJ#. Custo!er#. Custo!er#. Custo!er,#. Custo!er=#. Custo!erF#.
Ra!es( (ilan Maus(iM C(aitali 1ardiM o!al RaPnis( 7ub(a! Ra!es( (ilan Maus(iM C(aitali
Custo!er#. 1ardiM Custo!er#. o!al Custo!erJ#. RaPnis( %L/78L procedure success9ully co!pleted
$ collection is an or"ere" #roup o* elements +avin# t+e same "a ta type, Eac+ element is i"enti*ie" y a uni9ue suscript t+at represents its position in t+e collection, PL/SQL provi"es t+ree collection types5 •
•
•
n"ex-y tales or $ssociative array Ceste" tale Variale-sie array or Varray
Oracle "ocumentation provi"es t+e *ollowin# c+aracteristics *or eac+ type o* collections5
*o!!e'ton "Jpe
&u-,er of Su,s'rp $!e-ents t "Jpe
)ense or Sparse
+ssociative #tring or array )or index 'nbounded -ither integer by table* &ested table
BariablesiAe array )B ) Barray*
ere *reated
*an .e ,e't "Jpe Attr,ute
4nly in !"#$! &o block
'nbounded Integer
#tarts dense, -ither in !"#$! can become block or at =es =es sparse schema level
5ounded
-ither in !"#$! +lways dense block or at =es =es schema level
Integer
:e +ave alrea"y "iscusse" varray in t+e c+apter 0PL/SQL arrays0, n t+is c+apter1 we will "iscuss PL/SQL tales, Bot+ types o* PL/SQL tales1 i,e,1 in"ex-y tales an" neste" ta les +ave t+e same structure an" t+eir rows are accesse" usin# t+e suscript notation, However1 t+ese two types o* tales "i**er in one aspect; t+e neste" tales can e store" in a "ataase column an" t+e in"ex-y tales cannot,
nde#;.J "a,!e $n inde'-b< tale Galso calle" an associative array is a set o* *e<-5alue pairs, Eac+ 'ey is uni9ue an" is use" to locate t+e correspon"in# value, T+e 'ey can e eit+er an inte#er or a strin#,
$n in"ex-y tale is create" usin# t+e *ollowin# syntax, Here1 we are creatin# an in"ex-y tale name" table_na7e w+ose 'eys will e o* subscript_type o* subscript_type an" an" associate" values will e o* element_type &?%E type5na!e I7 &ABLE ' ele!ent5type N'& NLL INDE$ B? subscript5type* table5na!e type5na!e*
$#a-p!e %ollowin# example s+ows +ow to create a tale to store inte#er values alon# wit+ names an" later it prints t+e same list o* names, DECLARE &?%E salary I7 &ABLE ' N@BER INDE$ B? ARC1AR,,-#* salary5list salary* na!e ARC1AR,,-#* BEGIN :: addin) ele!ents to t(e table salary5list0RaPnis(0# . ,---* salary5list0@inaMs(i0# . J---* salary5list0@artin0# . -----* salary5list0a!es0# . J---* :: printin) t(e table na!e . salary5list6IR7&* 31ILE na!e I7 N'& null L''% db!s5output6put5line 07alary o9 0 na!e 0 is 0 &'5C1ARsalary5listna!e###* na!e . salary5list6NE$&na!e#* END L''%* END* /
:+en t+e aove co"e is execute" at SQL prompt1 it pro"uces t+e *ollowin# result5 7alary 7alary 7alary 7alary
o9 o9 o9 o9
RaPnis( is ,--@inaMs(i is J--@artin is ----a!es is J---
%L/78L procedure success9ully co!pleted6
$#a-p!e Elements o* an in"ex-y tale coul" also e a &O:T
DECLARE CR7'R c5custo!ers is select na!e 9ro! custo!ers* &?%E c5list I7 &ABLE o9 custo!ers6na!eOtype INDE$ B? binary5inte)er* na!e5list c5list* counter inte)er .-* BEGIN 'R n IN c5custo!ers L''% counter . counter H* na!e5listcounter# . n6na!e* db!s5output6put5line0Custo!er0counter db!s5output6put5line0Cus to!er0counter 0#.0na!e5listcounter##* END L''%* END* /
:+en t+e aove co"e is execute" at SQL prompt1 it pro"uces t+e *ollowin# result5 Custo!er#. Custo!er,#. Custo!er=#. Custo!erF#. Custo!er#. Custo!er#.
Ra!es( (ilan Maus(iM C(aitali 1ardiM o!al
%L/78L procedure success9ully co!pleted
&ested "a,!es $ nested table is li'e a one-"imensional array wit+ an aritrary numer o* elements, However1 a neste" tale "i**ers *rom an array in t+e *ollowin# aspects5 •
•
$n array +as a "eclare" numer o* elements1 ut a neste" tale "oes not, T+e sie o* a neste" tale can increase "ynamically, " ynamically, $n array is always alwa ys "ense1 i,e,1 it always +as consecutive suscripts, $ neste" array is "ense initially1 ut it can ecome sparse w+en elements are "elete" *rom it,
$ nested table is create" usin# t+e *ollowin# syntax5 &?%E type5na!e I7 &ABLE ' ele!ent5type N'& NLL* table5na!e type5na!e*
T+is "eclaration is similar to "eclaration o* an inde'-b< tale1 ut t+ere is no CDE B< clause, $ neste" tale can e store" in a "ataase co lumn an" so it coul" e use" *or simpli*yin# SQL operations w+ere you 8oin a sin#le-column tale wit+ a lar#er tale, $n associative array cannot e store" in t+e "ataase,
$#a-p!e T+e *ollowin# examples illustrate t+e use o* neste" tale5 DECLARE &?%E na!es5table I7 &ABLE ' ARC1AR,-#* &?%E )rades I7 &ABLE ' IN&EGER* na!es na!es5table*
!arMs )rades* total inte)er* BEGIN na!es . na!es5table0a+ita02 0%rita!02 0Ayan02 0Ris(a+02 0ASiS0#* !arMs. )radesK2 KJ2 J2 J2 K,#* total . na!es6count* db!s5output6put5line0&otal db!s5output6put5line0&o tal 0 total 0 7tudents0#* 'R i IN 66 total L''% db!s5output6put5line07tudent.0na!esi#02 db!s5output6put5line07tu dent.0na!esi#02 @arMs.0 !arMsi##* end loop* END* /
:+en t+e aove co"e is execute" at SQL prompt1 it pro"uces t+e *ollowin# result5 &otal 7tudents 7tudent.a+ita2 @arMs.K 7tudent.%rita!2 @arMs.KJ 7tudent.Ayan2 @arMs.J 7tudent.Ris(a+2 @arMs.J 7tudent.ASiS2 @arMs.K, %L/78L procedure success9ully co!pleted6
$#a-p!e Elements o* a nested table coul" also e a &O:T
:+en t+e aove co"e is execute" at SQL prompt1 it pro"uces t+e *ollowin# result5 Custo!er#. Ra!es( Custo!er,#. (ilan
Custo!er=#. Custo!erF#. Custo!er#. Custo!er#.
Maus(iM C(aitali 1ardiM o!al
%L/78L procedure success9ully co!pleted6
*o!!e'ton Retods PL/SQL provi"es t+e uilt-in collection met+o"s t+at ma'e collections easier to use, T+e *ollowin# tale lists t+e met+o"s an" t+eir purpose5
S%& %
Retod &a-e ( Purpose
1
$S"S>n? /eturns T/'- i the nth element in a collection existsK otherwise returns 6+!#-.
2
*I&" /eturns the number o elements that a collection currently contains.
(
LR" hecks the 0aximum #iAe o a ollection.
9
F+S" /eturns the frst )smallest* index numbers in a collection that uses integer subscripts.
>
LAS" /eturns the last )largest* index numbers in a collection that uses integer subscripts.
<
P++>n? /eturns the index number that precedes index n in a collection.
:
&$">n? /eturns the index number that succeeds index n.
;
$"$&) +ppends one null element to a collection.
D
$"$&)>n? +ppends n null elements to a collection.
1@
$"$&)>n:? +ppends n copies o the ith element to a collection.
11
"+R /emoves one element rom the end o a collection.
12
"+R>n? /emoves n elements rom the end o a collection.
1(
)$L$"$ /emoves all elements rom a collection, setting 4'&T to @.
19
)$L$"$>n? /emoves the nth element rom an associative array with a numeric key or a nested table. I the associative array has a string key, the element corresponding to the key value is deleted. I n is null, -!-T-)n* does nothing.
1>
)$L$"$>-:n? /emoves all elements in the range m..n rom an associative array or nested table. I m is larger than n or i m or n is null, -!-T-)m,n* does nothing.
*o!!e'ton $#'eptons T+e *ollowin# tale provi"es t+e collection exceptions an" w+en t+ey are raise"5
*o!!e'ton $#'epton
+ased n Stuatons
4!!-TI 4!!-TI4&7I 4&7I#7&' #7&'!! !!
=ou try try to operate operate on on an atomic atomically ally null collectio collection. n.
&47+T+764'&
+ subscript designates an element that was deleted, or a nonexistent element o an associative array.
#'5#/IT75-=4&7 + subscript exceeds the number o elements in a collection. 4'&T #'5#/IT74'T#I-7!I + subscript is outside the allowed range. 0IT
B+!'-7-//4/
+ subscript is null or not convertible to the key type. This exception might occur i the key is defned as a !#7I&T-8-/ range, and the subscript is outside this range.
$ "ataase transaction is an atomic unit o* wor' t+at may consist o* one or more relate" SQL S QL statements, t is calle" atomic ecause t+e "ataase mo"i*ications rou#+t a out y t+e SQL statements t+at constitute a transaction can collectively e eit+er committe"1 i,e,1 ma"e permanent to t+e "ataase or rolle" ac' Gun"one *rom t+e "ataase, $ success*ully success*ully execute" SQL statement an" a committe" transaction are n ot same, Even i* an SQL statement is execute" success*ully1 unless t+e transaction containin# t+e statement is committe"1 it can e rolle" ac' an" all c+an#es ma"e y t+e statementGs can e un"one,
Startng an $ndng a "ransa'ton $ transaction +as a beginning an" an end, $ transaction transaction starts w+en one o* t+e *ollowin# events ta'e place5 •
T+e *irst SQL statement is per*orme" a*ter connectin# to t+e "ataase,
•
$t eac+ new SQL statement issue" a*ter a transaction is complete",
$ transaction en"s w+en one o* t+e *ollowin# events ta'e place5 •
$ !O))T or a &OLLB$!R statement is issue",
•
•
•
•
•
•
$ DDL statement1 li'e !&E$TE !&E$TE T$BLE T$BLE statement1 is issue"; ecause in t+at case a !O))T is automatically per*orme", $ D!L statement1 suc+ as a I&$CT statement1 is issue"; ecause in t+at case a !O))T is automatically per*orme", @ser "isconnects *rom t+e "ataase, @ser exits *rom SQL6PL@S y issuin# t+e ET comman"1 a !O))T is automatically per*orme", SQL6Plus terminates anormally1 a &OLLB$!R is automatically per*orme", $ D)L statement statement *ails; in t+at case a &OLLB$!R is automatically per*orme" p er*orme" *or un"oin# t+at D)L statement,
*o--ttng a "ransa'ton $ transaction is ma"e permanent y issuin# t+e SQL comman" !O))T, T+e #eneral syntax *or t+e !O))T comman" is5 C'@@I&*
%or example1 IN7ER& IN&' C7&'@ER7 ID2NA@E2AGE2ADDRE7727ALAR?# ALE7 2 0Ra!es(02 =,2 0A(!edabad02 ,---6-- #* IN7ER& IN&' C7&'@ER7 ID2NA@E2AGE2ADDRE7727ALAR?# ALE7 ,2 0(ilan02 ,2 0Del(i02 --6-- #* IN7ER& IN&' C7&'@ER7 ID2NA@E2AGE2ADDRE7727ALAR?# ALE7 =2 0Maus(iM02 ,=2 0ota02 ,---6-- #* IN7ER& IN&' C7&'@ER7 ID2NA@E2AGE2ADDRE7727ALAR?# ALE7 F2 0C(aitali02 ,2 0@u!bai02 --6-- #* IN7ER& IN&' C7&'@ER7 ID2NA@E2AGE2ADDRE7727ALAR?# ALE7 2 01ardiM02 ,J2 0B(opal02 --6-- #* IN7ER& IN&' C7&'@ER7 ID2NA@E2AGE2ADDRE7727ALAR?# ALE7 2 0o!al02 ,,2 0@%02 F--6-- #* C'@@I&*
+o!!ng .a' "ransa'tons !+an#es ma"e to t+e "ataase wit+out !O))T coul" e un"one usin# t+e &OLLB$!R comman", T+e #eneral syntax *or t+e &OLLB$!R comman" co mman" is5 R'LLBAC &' 7AE%'IN& < sa+epoint5na!e>*
:+en a transaction is aorte" "ue to some unprece"ente" situation1 li'e system *ailure1 t+e entire transaction since a commit is automatically rolle" ac', * you are not usin# sa5e%oint 1 t+en simply use t+e *ollowin# statement to rollac' all t+e c+an#es5 R'LLBAC*
Saveponts Savepoints are sort o* mar'ers t+at +elp in splittin# a lon# transaction into smaller units y settin# some c+ec'points, By settin# savepoints wit+in a lon# transaction1 you can roll ac' to a c+ec'point i* re9uire", T+is is "one y issuin# t+e S$VEPOCT S$VEPOCT comman", T+e #eneral syntax *or t+e S$V S$VEPOCT comman" is5 7AE%'IN& < sa+epoint5na!e >*
%or example5 IN7ER& IN&' C7&'@ER7 ID2NA@E2AGE2ADDRE7727ALAR?# ALE7 J2 0RaPnis(02 ,J2 01%02 K--6-- #* IN7ER& IN&' C7&'@ER7 ID2NA@E2AGE2ADDRE7727ALAR?# ALE7 2 0Ridd(i02 ,2 03B02 F--6-- #* 7AE%'IN& sa+* %DA&E C7&'@ER7 7E& 7ALAR? 7ALAR? H ---* R'LLBAC &' sa+* %DA&E C7&'@ER7 7E& 7ALAR? 7ALAR? H --31ERE ID J* %DA&E C7&'@ER7 7E& 7ALAR? 7ALAR? H --31ERE ID * C'@@I&*
Here1OLL+A(. TO sa510 statement rolls ac' t+e c+an#es up to t+e point1 w+ere you +a" mar'e" savepoint sa51 an" a*ter t+at new c+an#es will start,
Auto-at' "ransa'ton "ransa'ton *ontro! To execute a !O))T automatically w+enever an CSE&T1 CSE&T1 @PD$TE @PD$TE or DELETE comman" is execute"1 you can set t+e $@TO!O))T environment variale as5 7E& A&'C'@@I& 'N*
PL/SQL provi"es two classes o* "ate an" time relate" "ata types5 •
Datetime "ata types
•
nterval "ata types
T+e Datetime "ata types are5 •
D$TE
•
T)EST$)P
•
T)EST$)P :TH T)E XOCE
•
T)EST$)P :TH LO!$L T)E XOCE
T+e nterval "ata types are5 •
CTE&V$L CTE&V$L
•
CTE&V$L D$< TO SE!OCD
Fe!d Ka!ues for )atet-e and nterva! )ata "Jpes Bot+ dateti7e an" inter5al "ata types consist o* "ields , T+e values o* t+ese *iel"s "etermine t+e value o* t+e "atatype, T+e *ollowin# tale lists t+e *iel"s an" t+eir possile values *or "atetimes an" intervals,
Fe!d &a-e =-+/
Ka!d )atet-e Ka!ues
Ka!d nterva! Ka!ues
9:12 to DDDD )excluding )excluding year @* @*
+ny nonAero nonAero integer
04&T
@1 to 12
@ to 11
+= +=
@1 to (1 )limited by the values o 04&T and =-+/, according to the rules o the +ny nonAero integer calendar or the locale*
4'/
@@ to 2(
@ to 2(
0I&'T-
@@ to >D
@ to >D
#-4&
@@ to >D.D)n*, where D)n* is the precision o time ractional seconds The D)n* portion portion is not applicable applicable or +T-.
@ to >D.D)n*, where D)n* is the precision o interval ractional seconds
12 to 19 )range accommodates daylight TI0-E4&-74' TI0-E4&-74' savings time changes* &ot applicable / &ot applicable ap plicable or +T- or TI0-#T TI0-# T+0. +0. TI0-E4&-70I&' TI0-E4&-70I&' @@ to >D T&ot applicable ap plicable or +T- or TI0-#T TI0-# T+0. +0.
&ot applicable
TI0-E4&-7/-8I TI0-E4&-7/-8I &ot applic applicabl able e or or +T+T- or TI0-#T TI0-#T+0 +0. 4&
&ot applic applicab able le
TI0-E4&-7+55 TI0-E4&-7+55 &ot applic applicabl able e or or +T+T- or TI0-#T TI0-#T+0 +0. /
&ot applic applicab able le
"e )atet-e )ata "Jpes and Fun'tons %ollowin# are t+e Datetime "ata types5
)AT2 - it stores "ate an" time in*ormation in ot+ c +aracter an" numer "atatypes, t is ma"e o* in*ormation on century1 year1 mont+1 "ate1 +our1 minute1 an" secon", t is speci*ie" as5
•
D$TE "atatype, t stores t+e year1 mont+1 an" T=2STA=P - it is an extension o* t+e D$TE "ay o* t+e D$TE D$TE "atatype1 alon# wit+ +our1 minute1 an" secon" values, t is use*ul *or storin# precise time values,
•
T=2STA=P T=2STA=P >T8 T=2 ?O&2 - it is a variant o* T)EST$)P t+at inclu"es a time one re#ion name or a time one o**set in its value, T+e time one o**set is t+e "i**erence Gin +ours an" minutes etween local time an" @T!, T+is "atatype is use*ul *or collectin# an" evaluatin# "ate in*ormation across #eo#rap+ic re#ions,
•
T=2STA=P T=2STA=P >T8 LO(AL T=2 ?O&2 - it is anot+er variant o* T)EST$)P t+at inclu"es a time one o**set in its value,
•
%ollowin# tale provi"es t+e Datetime *unctions Gw+ere1 x +as "atetime value5
S%&
Fun'ton &a-e ( )es'rpton
1
A))NR&"S>#: J?C +dds y months to x.
2
LAS"N)AU>#?C /eturns the last day o the month.
(
R&"SN.$"$$&>#: J?C /eturns the number o months between x and y.
9
&$"N)AU>#: &$"N)AU>#: daJ?C /eturns the datetime o the next day ater ater x.
>
&$N"R$C /eturns the time"day value rom a time Aone specifed by the user.
<
+I&)># O: unt?C /ounds xK
:
SUS)A"$>?C /eturns the current datetime.
;
"+I&*># O: unt?C Truncates Truncates x.
Timestamp *unctions Gw+ere1 x +as a timestamp value5
S%& 1
Fun'ton &a-e ( )es'rpton *I++$&"N"R$S"ARP>?C /eturns a TI0-#T+0 CIT TI0- E4&- containing the current session time
along with the session time Aone.
2
(
$"+A*">V U$A+ E R&" E )AU E I+ E R&I"$ E S$*&) W E V "R$X&$NI+ E "R$X&$NR&I"$ W E V "R$X&$N+$& E W "R$X&$NA..+ ? F+R #? -xtracts and returns a year, month, day, hour, minute, second, or time Aone rom xK F+RN"X>#: t-eNMone?C onverts the TI0-#T+0 x and time Aone specifed by time7Aone to a TI0-#T+0 TI0-#T+0 CIT TI0-E4&-. TI0-E4&-.
9
L*AL"R$S"ARP>?C /eturns a TI0-#T+0 containing the local time in the session time Aone.
>
SUS"R$S"ARP>?C /eturns a TI0-#T+0 CIT TI0- E4&- containing the current database time along with the database time Aone.
<
SUSN$"+A*"NI"*>#?C onverts the TI0-#T+0 CIT TI0-E4&- x to a TI0-#T+0 containing the date and time in 'T.
:
"N"R$S"ARP>#: Ofor-at?C onverts the string x to a TI0-#T+0.
;
"N"R$S"ARPN"X>#: Ofor-at?C onverts the string x to a TI0-#T+0 CIT TI0-E4&-.
$#a-p!es T+e *ollowin# co"e snippets illustrate t+e use o* t+e ao ve *unctions5 7ELEC& 7?7DA&E R'@ DAL*
Output5 -/=/,-, .,.=F %@ 7ELEC& &'5C1ARCRREN&5DA&E2 0DD:@@:???? [email protected]# R'@ DAL*
Output5 =:-:,-, -.,.F 7ELEC& ADD5@'N&177?7DA&E2 # R'@ DAL*
Output5 -/=/,-= .,.= %@ 7ELEC& L'CAL&I@E7&A@% R'@ DAL*
Output5 /=/,-, .,.6=FJ--- %@
"e nterva! )ata "Jpes and Fun'tons %ollowin# are t+e nterval "ata types5
•
•
CTE&V$L
nterval *unctions5
S% &
Fun'ton &a-e ( )es'rpton
1
&IR")S&"$+KAL>#: nterva!Nunt?C onverts the number x to an I&T-/B+! += T4 #-4&.
2
&IR"UR&"$+KAL>#: nterva!Nunt?C onverts the number x to an I&T-/B+! =-+/ T4 04&T.
(
"N)S&"$+KAL>#?C onverts the string x to an I&T-/B+! += T4 #-4&.
9
"NUR&"$+KAL>#?C onverts the string x to an I&T-/B+! =-+/ T4 04&T.
T+e )+=S_OTPT is a uilt-in pac'a#e t+at enales you to "isplay output1 "isplay "eu##in# in*ormation1 an" sen" messa#es *rom PL/SQL loc's1 supro#rams1 pac'a#es1 an " tri##ers, :e :e +ave alrea"y use" t+is pac'a#e all t+rou#+out t+rou#+ou t our tutorial, Let us loo' at a small co"e snippet t+at woul" "isplay all t+e user tales in t+e "ataase, Try it in your "ataase to list "own all t+e tale names5 BEGIN db!s5output6put5line user 0 &ables in t(e database.0#* 'R t IN 7ELEC& table5na!e R'@ user5tables# L''% db!s5output6put5linet6table5na!e#* END L''%* END* /
).RSNI"PI" Su,progra-s T+e DB)SFO@TP@T pac'a#e +as t+e *ollowin# supro#rams5
S%&
Su,progra- ( Purpose
1
).RSNI"PI"%)SA.L$C isables message output
2
).RSNI"PI"%$&A.L$>,u ).RSNI"PI"%$&A.L$>,uYerNsMe YerNsMe & &"$$+ )$FAIL )$FAIL" " 20000?C -nables message output. + &'!! value o bu3er7siAe represents unlimited bu3er siAe.
(
).RSNI"PI"%$"NL&$ >!ne I" KA+*A+2: status I" &"$$+?C
/etrieves a single line o bu3ered inormation. 9
).RSNI"PI"%$"NL&$S >!nes I" *A+A++: nu-!nes & I" &"$$+?C /etrieves an array o lines rom the bu3er.
>
).RSNI"PI"%&$NL&$C uts an endoline marker
<
).RSNI"PI"%PI">te).RSNI"PI"%PI">te- & KA+*A+2?C laces a partial line in the bu3er.
:
).RSNI"PI"%PI"NL&$>t ).RSNI"PI"%PI"NL&$>tee- & KA+*A+2?C laces a line in the bu3er.
$#a-p!e DECLARE lines db!s5output6c(ararr* nu!5lines nu!ber* BEGIN :: enable t(e bu99er Qit( de9ault siSe ,--- db!s5output6enable* db!s5output6put5line01ello Reader40#* db!s5output6put5line01ope db!s5output6put5line01o pe you (a+e enPoyed t(e tutorials40#* db!s5output6put5line01a+e db!s5output6put5line01a +e a )reat ti!e eplorin) pl/sql40#* nu!5lines . =* db!s5output6)et5lineslines2 nu!5lines#* 'R i IN 66nu!5lines L''% db!s5output6put5linelinesi##* END L''%* END* /
:+en t+e aove co"e is execute" at SQL prompt1 it pro"uces t+e *ollowin# result5 1ello Reader4 1ope you (a+e enPoyed t(e tutorials4 1a+e a )reat ti!e eplorin) pl/sql4 %L/78L procedure success9ully co!pleted6
PL/SQL allows "e*inin# an o8ect type1 w+ic+ +elps in "esi#nin# o 8ect-oriente" "ataase in Oracle, $n o8ect type allows you to crate composite types, @sin# o8ects allow you implementin# real worl" o8ects wit+ speci*ic structure o* "ata an" met+o"s *or ope ratin# it, O8ects +ave attriutes an" met+o"s, $ttriutes are properties o* an o8ect an" are use" *or storin# an o8ect0s state; an" met+o"s are use" *or mo"elin# its e+aviors, O8ects are create" usin# t+e !&E$TE !&E$TE O& &EPL$!E T
CREA&E 'R RE%LACE &?%E address A7 'BEC& (ouse5no +arc(ar,-#2 street +arc(ar,=-#2 city +arc(ar,,-#2 state +arc(ar,-#2 pincode +arc(ar,-# #* /
:+en t+e aove co"e is execute" at SQL prompt1 it pro"uces t+e *ollowin# result5 &ype created6
Let0s create one more o8ect custo7er w+ere we will wrap attributes an" 7etods to#et+er to +ave o8ect oriente" *eelin#5 CREA&E 'R RE%LACE &?%E custo!er A7 'BEC& code nu!ber#2 na!e +arc(ar,=-#2 contact5no +arc(ar,,#2 addr address2 !e!ber procedure display #* /
:+en t+e aove co"e is execute" at SQL prompt1 it pro"uces t+e *ollowin# result5 &ype created6
nstantatng an ,e't De*inin# an o8ect type provi"es a lueprint *or t+e o8ect, To use t+is o8ect1 you nee" to create instances o* t+is o8ect,
:+en t+e aove co"e is execute" at SQL prompt1 it pro"uces t+e *ollowin# result5 1ouse No. -=A 7treet. @6G6Road City. aipur 7tate. RaPast(an %incode. ,-=- %L/78L procedure success9ully co!pleted6
Re-,er Retods )emer 7etods are use" *or manipulatin# t+e attributes o* t+e o8ect,
co"e *or t+e memer met+o"s, T+e o8ect o"y is create" usin# t+e !&E$TE !&E$TE T
T+e co7%arison 7etods are use" *or comparin# o8ects, T+ere are two ways to compare o8ects5 •
•
=a% 7etod5 T+e =a% 7etod is a *unction implemente" in suc+ a way t+at its value "epen"s upon t+e value o* t+e attriutes, %or example1 *or a customer o8ect1 i* t+e customer co"e is same *or two customers1 ot+ customers coul" e t+e same an" one, one , So t+e relations+ip etween t+ese two o8ects woul" "epen" upon t+e value o* co"e, Order 7etod 5 T+e Order 7etods implement some internal lo#ic *or comparin# two o8ects, %or example1 *or a rectan#le o8ect1 a rectan#le is i##er t+an anot+er rectan#le i* ot+ its si"es are i##er,
Isng Rap -etod Let us try to un"erstan" aove concepts usin# t+e *ollowin# rectan#le o8ect5 CREA&E 'R RE%LACE &?%E rectan)le A7 'BEC& len)t( nu!ber2 Qidt( nu!ber2 !e!ber 9unction enlar)e inc nu!ber# return rectan)le2 !e!ber procedure display2 !ap !e!ber 9unction !easure return nu!ber #* /
:+en t+e aove co"e is execute" at SQL prompt1 it pro"uces t+e *ollowin# result5 &ype created6
!reatin# t+e type o"y5 CREA&E 'R RE%LACE &?%E B'D? rectan)le A7 @E@BER NC&I'N enlar)einc nu!ber# return rectan)le I7 BEGIN return rectan)lesel96len)t( H inc2 sel96Qidt( H inc#* END enlar)e*
@E@BER %R'CEDRE display I7 BEGIN db!s5output6put5line0Len)t(. db!s5output6put5line0Len )t(. 0 len)t(#* db!s5output6put5line03idt(. db!s5output6put5line03id t(. 0 Qidt(#* END display*
@A% @E@BER NC&I'N !easure return nu!ber I7 BEGIN return sqrtlen)t(;len)t( H Qidt(;Qidt(##* END !easure* END* /
:+en t+e aove co"e is execute" at SQL prompt1 it pro"uces t+e *ollowin# result5
&ype body created6
Cow usin# t+e rectan#le o8ect an" its memer *unctions5 DECLARE r rectan)le* r, rectan)le* r= rectan)le* inc59actor nu!ber . * BEGIN r . rectan)le=2 F#* r, . rectan)le2 J#* r= . r6enlar)einc59actor#* r=6display* I r > r,# &1EN :: callin) !easure 9unction r6display* EL7E r,6display* END I* END* /
:+en t+e aove co"e is execute" at SQL prompt1 it pro"uces t+e *ollowin# result5 Len)t(. 3idt(. K Len)t(. 3idt(. J %L/78L procedure success9ully co!pleted6
Isng rder -etod Cow1 t+e t+e sa7e e""ect could be acie5ed using an order 7etod , Let us recreate t+e rectan#le o8ect usin# an or"er met+o"5 CREA&E 'R RE%LACE &?%E rectan)le A7 'BEC& len)t( nu!ber2 Qidt( nu!ber2 !e!ber procedure display2 order !e!ber 9unction !easurer rectan)le# return nu!ber #* /
:+en t+e aove co"e is execute" at SQL prompt1 it pro"uces t+e *ollowin# result5 &ype created6
!reatin# t+e type o"y5 CREA&E 'R RE%LACE &?%E B'D? rectan)le A7 @E@BER %R'CEDRE display I7 BEGIN db!s5output6put5line0Len)t(. db!s5output6put5line0Len )t(. 0 len)t(#* db!s5output6put5line03idt(. db!s5output6put5line03id t(. 0 Qidt(#* END display* 'RDER @E@BER NC&I'N !easurer rectan)le# return nu!ber I7 BEGIN Isqrtsel96len)t(;sel96len)t( Isqrtsel96len)t(;sel96 len)t( H sel96Qidt(;sel96Qidt(#> sqrtr6len)t(;r6len)t( H r6Qidt(;r6Qidt(## t(en return#* EL7E
return:#* END I* END !easure* END* /
:+en t+e aove co"e is execute" at SQL prompt1 it pro"uces t+e *ollowin# result5 &ype body created6
@sin# t+e rectan#le o8ect an" its memer *unctions5 DECLARE r rectan)le* r, rectan)le* BEGIN r . rectan)le,=2 FF#* r, . rectan)le2 J#* r6display* r,6display* I r > r,# &1EN :: callin) !easure 9unction r6display* EL7E r,6display* END I* END* /
:+en t+e aove co"e is execute" at SQL prompt1 it pro"uces t+e *ollowin# result5 Len)t(. ,= 3idt(. FF Len)t(. 3idt(. J Len)t(. ,= 3idt(. FF %L/78L procedure success9ully co!pleted6
nertan'e for PL/SQL ,e'ts PL/SQL allows creatin# o8ect *rom existin# ase o8ects, To implement in+eritance1 t+e ase o8ects s+oul" e "eclare" as COT %C$L, T+e "e*ault is %C$L, T+e *ollowin# pro#rams illustrate in+eritance in PL/SQL O8ects, Let us create anot+er o8ect name" TaleTop1 TaleTop1 w+ic+ is in+eritin# *rom t+e &ectan#le o8ect, !reatin# t+e ase rectangle o8ect5 CREA&E 'R RE%LACE &?%E rectan)le A7 'BEC& len)t( nu!ber2 Qidt( nu!ber2 !e!ber 9unction enlar)e inc nu!ber# return rectan)le2 N'& INAL !e!ber procedure display# N'& INAL /
:+en t+e aove co"e is execute" at SQL prompt1 it pro"uces t+e *ollowin# result5 &ype created6
!reatin# t+e ase type o"y5 CREA&E 'R RE%LACE &?%E B'D? rectan)le A7 @E@BER NC&I'N enlar)einc nu!ber# return rectan)le I7
BEGIN return rectan)lesel96len)t( H inc2 sel96Qidt( H inc#* END enlar)e*
@E@BER %R'CEDRE display I7 BEGIN db!s5output6put5line0Len)t(. db!s5output6put5line0Len )t(. 0 len)t(#* db!s5output6put5line03idt(. db!s5output6put5line03id t(. 0 Qidt(#* END display* END* /
:+en t+e aove co"e is execute" at SQL prompt1 it pro"uces t+e *ollowin# result5 &ype body created6
!reatin# t+e c+il" o8ect tabletop5 tabletop5 CREA&E 'R RE%LACE &?%E tabletop NDER rectan)le !aterial +arc(ar,,-#* 'ERRIDING !e!ber procedure display # /
:+en t+e aove co"e is execute" at SQL prompt1 it pro"uces t+e *ollowin# result5 &ype created6
!reatin# t+e type o"y *or t+e c+il" o8ect tabletop5 tabletop5 CREA&E 'R RE%LACE &?%E B'D? tabletop A7 'ERRIDING @E@BER %R'CEDRE display I7 BEGIN db!s5output6put5line0Len)t(. db!s5output6put5line0Le n)t(. 0 len)t(#* db!s5output6put5line03idt(. db!s5output6put5line03i dt(. 0 Qidt(#* db!s5output6put5line0@aterial. db!s5output6put5line0@a terial. 0 !aterial#* END display* /
:+en t+e aove co"e is execute" at SQL prompt1 it pro"uces t+e *ollowin# result5 &ype body created6
@sin# t+e tabletop o8ect tabletop o8ect an" its memer *unctions5 DECLARE t tabletop* t, tabletop* BEGIN t. tabletop,-2 -2 03ood0#* t, . tabletop-2 =-2 07teel0#* t6display* t,6display* END* /
:+en t+e aove co"e is execute" at SQL prompt1 it pro"uces t+e *ollowin# result5 Len)t(. ,3idt(. @aterial. 3ood Len)t(. 3idt(. =@aterial. 7teel
%L/78L procedure success9ully co!pleted6
A,stra't ,e'ts n PL/SQL T+e COT CST$CT$BLE CST$CT$BLE clause allows you to "eclare an astract o8ect,
:+en t+e aove co"e is execute" at SQL prompt1 it pro"uces t+e *ollowin# result5 &ype created6
