Referencing Co flections - Oracle PL/SQL Programming

Reierenum;i*,u^i'dto<Ki"es\Hinfl-t'nle-nLfrom<linllerMini.Synl.isI l>leleierue.111elementisas follows:

colljiame [subscript):

Inlliii <ynl.ix:

U

C 1_nd[ne io|Mftt-ntsHit'nriinuof atollcs linn.

u

ju^jct.pt

i ropreseiustht?elementthat h.attobe

pronged,

rherc i&

some

specified r.innc for iuoscript. which van0*1 jeeordm^ to the iolleUion lype. Following are the sijIjmnptr.Jnjie^ .uinrdinj"to[heir1ulletLion\\pi's:

* Forv,irrr,ivs,therongt¹ij.1 toupperImundof(lievarr.iy.

* E-ornesledtable.[lieranjieisIin2N74JJlf>4 7.

* *issutraTke.nra\^uithnuiiieru kev,therangeis-2I474HS647(n+2

M74H

164 7t

l~or.i^isih.\.iU\\<.irr.iy^.withY-.PCSJAR/v.itue/iskey,iheran#'depends onthelengthof thevaluespetiiieilduringivpe.drtl.ic.ition.inddatabaseMhleerection

Listing6.6show;

how

¹⁰perform rderentinj>in atnllection.

Listing6.6:PL/SQL programlousereierennngin aillijeiioris

Theoutputoif.NinfiU 6i*.nsfollow^1-:

InIisling 6.Ej.

we

h.iveused,1turn tiun."CN"" fL,

w

1 1ith1uunlsthenumber(ilelementsin tile nested^t<iblcandmoves(heloopupto lhalcount.

116

6: Colfcctiowa<dRecords

this

we

concludediscussiononreferencing (hecollections.Now, sometimes you

may

need (oassignonecollection intoanothercollection,

how

youcandothisisexplainednext.

Collections

assignacollection intoanothercollection, theelement typesand^thedatatypenamesofboth collectionsmustbesame.Thereare variouswaystoassignacollection intoanothercollection, suchas using

INSERT

statement

UPDATE

statement,

SELECT

statement,assignment statement, andalso the subprogramcall. Listing6.7 shows

how

lo assign one collectioninto another

6.7:PL/SQL programto assign collections

outputof Listing6.7isasfollows:

PL/SQL procedure succesfully completed.

InListing 6.7:

First statement,

A

:-B, is allowed, becauseIxith

A

and B have thesameelement type (NUMBER) anddatatype(eid)name.

Secondstatement.B:=C,isnotallowed,becausealthough theyhavethe

same

element type buttheirdatatypenamesaredifferent,thai ^is.tliedatatype

name

offtis

eid

butthat of

C

^is esal.

of Listing6.7showsthattheprocedureiscompletedsuccessfully.It isbecause

we

given theillegalassignment asacomment. However,ifyoutry torunListing6.7without theillegalassignment acomment,then the followingerrormessageappears:

117

You

canalso assignanullvalueloanothercollodion orcanalso assignvaluesin acollectionby u^ingexpressions. SecListingfer8to

know how

toassignanexpressionandnullintoanother collection.

Listingfi,6:PL/SQL programtoassignexpressionandnullvalue

3nListing(tJi,

we

have.l^sigrii'dan expreviirmtoanelement presentattheirrstpositioninthe varMy,andthen displayedIhevalue assignedlo[heelement

M

firsl|Hisiticin.Allerih.ir

we

have assignedllic nestedtable3(inilializedasnullito ncslt'dtableC,andilllastvvc h*ivcusedihc

COUNT

method to

know

the number ni dements in ihe nested table C alterassigning the

l-xpredion.

OulputofListingGilisastollers;

Thisisal[.itnmlaligningQ>llc*clions.Iet's

now

learn

how

loeompiirecollecljons

Comparing

Collections

Sometimes^vnu

mav

requirelo

know

uhellu't,\culiec-Iiori_isnullornot;orwhethertheelements in(wo collections are similaror notrInPL/SQL you cancompare twocolleclionslofindoi_r|

whetherthey^.ir**nlil 3or similar. Listingtt.'Jshows

how

locomparetwocollections.

Listing b.9:PL/SQLprogramtocornparecollections

118

Qmptcr6:Understanding

PUSQL

ColiecliomandRecords

InListing 6.9,

we

havedeclaredtwovariablesof nestedtablesbulinitializedonlyonevariable, thatisC,andkept

B

asblank. Afterthat,

we

havecompared BforNull, andthenB and Cto check whethertheyhavesimilarelementsor not.Theoutput ofListing6.9isasfollows:

Thisisallaboutcomparingcollections.Let'ssee

how

tousecollectionswithdatabasetablesby performingoperations,suchas create,insert,update,onatableinthedatabase.

Usinga

VARRAY

Toperform operationsonadatabasetableusingvarrays,you mustdefine,declare,andinitialize thevan-ay.Theprocessto define, declare,andinitializea varrayhas alreadybeendiscussed.

Let'sfeam

how

tocreateatable thathas acolumnof the varraycollection type.

SQL> CREATETABLE

V.jrny

(ENAMEUABCHJKI(30J, EAOOBiruddl);

Theoutput ofthisstatementisasfollows:

"7 Table created.

In thepreceding statement,

we

havecreated a [able

named V

array. This tablehastwo columns;oneofthemisof

VARCHAR2

datatypewhile [he other^isof

varray

datatype.Lei's

now

take thetable,

emp

^addl, that

we

have createdearlier (describedunder theheading 'Definingvarrays'l,andinsertarccoid inthat table. Listing6.10showsthecodeto inserta recordintheerop addltable.

listing6.10:

PUSQL

programtoaddrecordsinemp_addl table

119

Quiputof Listing6-10isas lollows.

PL/sqL procedurecompleted suceessf jlly.

Afler understanding

how

to insert rcinrd> in^,1 daUkisetable, let'*. urxleNand

how

^to use i;?tar~.,indif.:

~rr

^.ltemem^on,1i!,il,th.i^e(ablehavingroluimv;ofrvpevarr.iy Listingb1

uses

itta

:? and^"".F""'i.iti'mcnson.i<Ln,ib.i*irlable

Listing b.11:ML/SQL programiouM¹ ⁱⁱD-\ I'Kand£"L.lLC:istalornentonr.rp idallable.

SQ.L> SET SERVE ROUT PUTON DECLARE

add„varray enp_add_; Declaring, variable of vaprav

iemrj_addl;

—

Declaring variable of varray BEGIN

add_varray ;- emp_addl ("Street no 12', 'H.no20'};

—

Initializing varray variable

--Updating values in thetable V_array

UPDATE V_array set EADD* add_varrayWHERE ENAME- 'Anrit';

-- selecting the updated field from v_array SELECT EADD intok fromv_array inhere ENAME = 'Amit^h:

fori in«,FIflST

„

k.laST loop DBM5_0uTPLiT.PUT_L.lNE (ati));

end loop;

END;

In Ii<-tmgb 1I,

we

h,i\e performedluoOfM'r.Hinr^ 1ir-st

we

li.iv<> irfniJ^le-rIthe d.tt.ib.i'iet.ilile

\ -iz ljvriinf1thenwe?have

cucutcd

(he ..:\L^-"Lstatementtovic^^-theupdatedvalue:*;.

OiiiputofIidlingF h11 ,jk[nllnws:

Street no 12 ii.no20

PL/SQL procedure completed successfully.

[iithesame

wav

asv<irray\ne^ledlables<.inalsolieusedin.idatabase Hul there\ba^differente intredlirijT[hedatabasetablewithcolumnas nestedtable (\pe.Let'screatea database(able

\viththecolumnsoftvpenestedtabIf.

5£}L>CREATE TABLE ne5t_dema (ENAME VARCHAR2 (30)_T EADD emp_addZ) NESTED TABLE EADD STORE AS

EJDDl

;

In the. precedingstatement

we

h.nocreated ,t tabb

named

nnc: doro. Thi, cablehas(wo columns: one ol them isof YAi-aiAr^ tipe while(lie other isof nested table lv|X\ The i^^_^;lnrhas alreadvl>rt-riire.ilE'dunder ihoheading 'DefiningNestedTable'.Oulpul of ihe preceding stalcmentisas follows:

Table created.

Tlii¹. is.ill about usingvarray andnested tabicwilh databasetables. Now,

wo

continueour di^tussionbyesp^I.liningnilIntioumethods ^In ]isling6.1.

we

li.ncusedamethodCO'JKTto

know

thenumbernlelemenl'.inanestedtable.Pt/SQI hdsminimsoiliermethodsk)rerJmeIhe progranimingtasks. Lot's stuclvi'L'SCJL built-inmethodstlnat

cm

beu^ed with tollotUons,

120

Chapter6:Understanding

PUSQL

CoUectioitsandRetards

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