Using Collection Methods

PUSQL

provides variousbuilt-inmethodsthat

make

^iteasytousecollections.These methods canbefunctions,suchas

exists

and

count

or aprocedure,such asextend, trim, and delete. There are

some

limitationwithbuilt-incollectionmethods,suchas they can notbe calledbyusing

SQL

statements;

EXTEND

and

TRIM

methodscan notbeused withassociative arrays.Table6.2givesyouabriefdescriptionofvariousbuilt-incollectionmethods.

Dsscription

Hmnnflw

auatwaftaioiuat«cilitUna.

Haturotha anallMtImIma«n>bar

m

acalialon

Datum

thebigot

Mm

^{flunk* celacnaa.}

Ratnmtha kidM

—

^harttatarecadaa

Max

^a.

h

atalaeilati,

kicnMMithaall*of*^COUNtlBn.

Trimafdvenanatillaiza of cafecnoii.

Oakftai

db

abwatiIron

caMta.

Let'sdiscuss thesemethodsin detail.

EXISTS

This is a function, which either returns

TRUE

or FALSE. Ifany specified element, say nth element,existsinthecollection,thenit-returns'true';otherwise,itreturns'false'.

When

the nth elementisout ofrange,then itreturns

FALSE

ratherthanraisinganexception. Thismethodis

usedToavoid the referencingofnon-existentelements. SeeListing6.12tounderstand

how

it

works.

Listing6.12;PI75QL programtouse

EXISTS

function

121

In Listing6.12, we;have usedthe

exists

method in 'lievarrayandncsied table tocheck existenceoitheelemenlwhosesubscripthasbeenpassedasparameter

m

thismethod.

We haw

usedtheIF.-- ELSE:controlstatement10display[heouipulonihe basisof

me

existenceand

nr>ri-exis!erit.eui Iheelement.

Outpulof ListingbA2isasfollows:

COUNT

The

COUNT

function returnsthenumberufek'menlsthat currencyexi^lin auilledion. This methodisvervuseful

when

youdonot

know

^theexactnumberofelementsinacollection.See Listingfc.JJtounderstandItsworking.

Listing6,13:PL/SQL programtouse

COUNT

function

InLisling6.1 3,Ihe

COUNT

melhodisusedtocalculateIhenumberofelementsin

varray

and

nested

-dbLe.

Outputof ListingbAiisasfollows:

You cm

usethismethodwhere;integervalueisespetled 122

PUSQL

CollectionsandRecords

functionreturnsthenumberofelementsthaia varraycancontain.Inolderwords, returnsiheupperboundof avarray.Incaseofnestedtablesandassociativearrays, functionreturnsNull.This meihod can beusedwhereveran integervalueisexpected.

6.14shows

how

thisfunctionworks:

14:

PUSQL

programlause

LIMIT

function

theprecedinglisting, first

we

havedisplayed thetotal numberofelementsinvarray,andthen tolaI

number

ofelementsthatavarraycan conla in.Theoutputof Listingb.i4isas^follows:

ofIhemarefunctions,

FIRST

returnsthesmalEe$isubscriptin[he collection,and[he

last

returns Ihe last subscript in the collection. Ifthe collection isempty, then these returnnull.

varrays,

FIRST

^{alwaysreturns1,}butforNesredtable,it

may

or

may

norreturns1;ilthefirst

has alreadybeendeletedfromthenestedtablerthen the

first

function returns2.

,the

LAST

functionreturnstheupperboundincaseofvarrays;butincase ofnested

itgivesThesameresultasthe

COUNT

^{function. If}youdelete

some

elements fromthe nestedtable,thenthevalue returnedby the

last

funciionismorethanihevalueitreturnswith

ofassociativearrayswithvarchar.2keyvalues,these functionsreturnIhe lowesTand

VARCHAB2

^keyvalues. Listing6.35shows

how

these functionswork.

6.15:

PUSQL

programtouse

FIRST

and

LAST

functions

123

In Listing 6,15,the

first

andthe

last

methodsare usedToiteratetheloopin

andthen the smallestandlargestsubscriptsof thenestedtableare displayed, deleted Ihefirst element from the nestedtableby using the

delete

method;

startingsubscriptisdisplayedonceagain.

Theoutput ofListing6.15isasfollows:

PRIOR

(n)

and NEXT(n)

Both these functions return the subscriptsof thespecified element.

PRIOR

{n) returnsthe subscript of theelementthatprecedes index ninthaicollection:whileIhe

next

methodreturns thesubscriptof[heelementthatsucceeds index n ofacollection.

prior

(n) returnsnull,ifn hasnopredecessorand

NEXT

(n) relurnsnull

when

ithasnosuccessor.

Incase, associativearrays have

dements

oflypi' ^.'ARCHA^, Thi-'&elunctiun*return thekey valuescorrespondingtothespecifiedclement. Lining6.16showstheuse of the

PRIOR

^Jn)and the

NEXT

mlmethods.

Listing6.16;PL/5QL programtouse

PRIOR

and

NEXT

functions

EXTEND

Thisisaprocedure,whichisusedloincrease[hesize ofacollection.Thismethodcan notbe usedincase of^jssehMlivearrays Tins melhod unlvlieu%ed wiihtdleUiunslhat have alreadybeeninitializcdLVol canuseibisprocedureinihefollowingthreewavs¹

EXTEND:

Itappendsonenullelemenliothe colleclion.

EXTEND

(n):liappendsnnullelcmenlsIoIhecollection.

EXTEND

(n,I):Itappendstheithelementntimesinthe colleclion.

AU „ ^_

Ifa cotecttonhasNOT NULLconflrainTthenmilvaluescannotbe appendedInacdfctiorv

Listing6.18shows

how

ihe

EXTEND

procedure works.

Listing6.18:HL/SQL programtouse

EXTEND

function

In Listing GJ8,

we

have used ihe

extend

method

m

all three

ways—

lhai 15, withoutany parameter, withsingleparameter(2),andwilhIwoparameters(2,3).

We

have usedthe

COUNT

methodafterihe

EXTEND

methodtodisplay Ihelotal numberof elementsinIhecolleclion (hereHisavarray)eachtime Ihe elementsareextended.

Theoutputof Listing6.18isasfollows:

126

Chaplet6jUnderstanding

PUSQL

Collectionsn"dRecords

TBIM

trim

deletes the elementsfrom _theendo£thecollection.Thismethodcan be used inthe followingways:

TRIM:11deletesasingleelementfromIheendofacollection.

TRIM

^(n):Itdeletesnnumberofelements fromtheendof acollection.Ifnismorethan the numberofelements containedinacollection,thenitraises

SUBSCRIPT_BE¥0ND_COUNT

exception.Listing6.19shows

how

itworks.

Listing6.19tPL/SQL programtouse

trim

function

InLisiing6.19,first

we

havedisplayed thetotalnumberofelementsinthenesiedtablebyusing

COUNT

method,andthen

we

have trimmedthenestedTablebyone elementusing the

trim

method.After that

we

haveagain applied Ihe

COUNT

method onthenestedtable.

At lastH

we

have trimmedthenestedtableonceagainbyusing

TRIM

methodwith parameter.

Theoutput ofListing6.19isasfollows;

DELETE

DELETE

isaprocedure,anditisusedtodefeleelementsfromacollection.Thismethod cannot beappliedon the varraycollection type. Usingthe

DELETE

procedure, unlike

TRIM

which deleteselementsfromtheendofthe collection,youcandeleteanyspecifiedelementfroma collection.

Thisprocedurecanbeusedinthefollowingways:

DELETE:Itremovesallelementsfromacollection.

DELETE(n): Itremovesthe nlhelementfroma nested tableorassociative array with a numeric key. If the associative array has elements of string type, then the element corresponding|othatstringtypeisdeleted.Thismeihoddoes nothingifnisnulL

127

J

DELETE (mPn);Itremovesallelementsiniherange

m

tonIroinaeorteelion- ihh method

willdonothingit

m

islargerthan n ortheyarenull.Listingh.JO shows

how

iiworks.

Listing6.20: f'L'SQI program(nuse^"ELET/.Ilihetion

S0.L> SET SFRvEROUTPlfT ON DECLARE

type esal tableof nuhber _C?) nqt NULL;

—

Defining nested table collection C esal; ^-- Declaring nested table collection variable

BEGIN

c := esal {SOOQ, 5500,6000, 6500,4500);

-

initializing nested

uble

DBMS_QUTPUT.PUT_LINE ^(' Total number of elements innested table is ^' |

|

COUNT)

;

crdelete W):

—

U5in.gDelete wi th singleparameter

ohhs._output.PuT_LlNE (' Total number of elements after removing second element is ^' 11 C.COUNT);

Crdelete (3, 5): using deletewith specific range of parameters DBMS_OUTPUTLPUT_LlNE (' Total numberof elements after removing rangeof element is ^' II C.COUNT);

c.delete: ^-- using deletewithout parameter dbm5_output.put_line (

H Total number ofelements after using delete without parameteris ^' |[

count);

end;

/

InListingf>.J<Kur^t

ue

h.ivedisplayedthetotalnLimberofelements

m

ihenestedtablebyusinp, the•:UU:.i method,andihcridole:oil(hosecond element from ihe(able,andaji<iinapplied(ho

O W.T

methodIndisplavtht¹1miniufelementsin.ihenestedtable.

Nov*

wo

usl"ihej'-L^.\.'.. methodtoremote(he speciiic rnnjjeoielements lrumthe tableand

dii.'MriuKethe."OJti'i method Similarly,

wv

ha\e.iji.iiriusedtheI. - methodwithoutanv

parameterandthenihe(nuntmethodlodisplay(he

town

onceag.nn.

Theoutput^{of Listing}6,20isasInlloivj.;

Total number of elements in nested table is 5

Total number of elements after removing second elementis 4 Total number of elements after removinga range of elementis 1 Total number of elements after using delete without parameteris o PL/SQL proceduresuccessfully completed.

With ^this

ue

complete discussion on tolleel ions, and irsinp collection melhods. In using

colli'ilinns

we

h^vesomeluiiilalioiis sinh^.is

we

t,inonly^usi'ihe elernerilsnisamedalalypes So,incasesivhentherearedifferent datatypes,voucan use Records

—

acompositedaidstrutture inI'lAQI.Lei'sunderstandindel.niaboutt'L/SCJLret.ords.

In document Oracle PL/SQL Programming (Page 127-134)