Oracle PL/SQL Programming

dreamlech

Anall-inclusivebookluleach you everything about Oracle PL/SQLProgramming

1Easy, Effective,and Reliable

• Quickand Easylearningin SimpleSteps

Mostpreferredchoice worldwideforlearning OraclePL/SQlProgramming

Oracle PL/SQL

Programming

Premieri2

I

10.1.0.2.0 ^- Production on Fr

c) 1982, ^200i», Oracle. All rights

r

ted ^to:

He ^Database 10g Enterprise Edition Release

Mi the Partitioning, OLAP and Data Mining opl

JpL> ^SET SERUEBOUTPUT ON

KQL> CREATE OR REPLACE PROCEDURE salary (eno

II

2 e_sal NUMBER

,

3 BEGIN

^Copyrightby DrcamtcchPress.19-A.AnsariRoad. Daryaganj.New[>clhi-110002

Thisbookmaynotbe duplicatedinanywaywithout the express written consentofthe publisher, except in the formofbriefexcerpts or quotationsforthepurposesof review.Theinformationcontained hereinisforthe personaluseofthereaiicrandanynoibe incorporatedinany commercialprograms, otherbook*, database*,or any kindofsoftwarewithout written consentofthe publisher.Makingcopies ofthisbookotany portion forany purpose other than>ourownisa violation of copyright laws.

LimitsofLiability'disclaimerof Warranty:Theauthorand publisherhaveused their bestcllbminpreparingthis hook.Theauthormakenorepresentationor warranties with respecttotheaccuracy or completeness;ofthe contentsofthisbook, and specifically disclaimanyimplied warrantiesof merchantability orfitnessofany particularpurpose.There arcnowarrantieswhichextendbeyondthedescriptionscontainedin thisparagraph.

Nowarrantymaybe created01extendedbysales representativesor written sales material*.Theaccuracyand completenessof 'He information provided hereinandtheopinionsstaled lieieinare notguaranteed or warranted toproduce;inyparticularsresults,and the adviceandstrategiescontained hereinmaynotbesuitableforevery individual.NeitherUrcamtechPressnoi authorshallbeliable foranylossofprofit01anyothercommercial damages,including butnot limited tospecial, incidental,consequential, orotherdamages.

Trademarks: All brandnamesand productnamesusedin thisbookaretrademarks, registeredtrademarks, or tradenamesoftheirrespective holders.DreamtcchPressisnotassociatedwithanypnxluel orvendormentioned in thisbook.

ISBN: I0-81-7722-S55-2

l3-y'8-«l-77>2-H55-7 Edition: 2008

Primedat:PfinimanIndia,Patpatganj, Delhi.

UthebetrechllichgeschutzlesMaterial

CONTENT S

WhaiisPl/SOLf 2

NeedofPI/SOL 2

VersionsofPL/5QL- 4

FeaturesofPL/SQL b

PL/SQLBloclc- „ b

PLfSQL VarjablejamiConstants 7

PL/SQLConltolSltuclures 7

UsingSQLwithinPL/SQL 7

PL/SQLCollections a

PUSQLRecords 6

PL/SQLSubprograms 8

PLSQlPjcbsjgS B

ExceptionHandlinH...., , 9

New

FeaturesaddedinPbSQLforOracle tug 9

PL/SQLArchitectureinOracle 10

Summary ... „ „ 1

1

DescribingBlock Structure , , , ... -.,M„,„.„.14

DiflaMlinn Sgrtinn 15

fnff ulinn aprlinn IS

Exception Section 21

KindsnfBlocfo 2j

AnonymousBlocks , 21

nlnrks 22

Nested Blocks

IntroducingDatatypes 22

NumberTypes , 22

Ntimrpr

u

Ow.n

^u-iandStringUpes 24

Bix>tejnTypes 27

IOBTypes 2B

Date. Time,andIntervalTypes , 29

P1;SQI 5uhtypes 32

Introducing lexical Unils 32

nplimitprs 12

Idenltfieis , , , ii

Utetala

Comments 3f»

V

UrheberrechllichgeschulzlesMaterial

Workingwith Declarations,..„ ... ... 37

LKinpUUAL'llValuo 37

Usinn

\OT

MULLCotwirjinl 37

UsinuAliases

W

Avsli;iinn-niQ[H-i,»or 3tt

AnlhnuHitOpi'i.ilors - - , —.39

Lofiir,ilOjiiT.ilurv 39

in

^{ComparisonUpct.ilots 40}

_ |H

^{ISNULLUptfalrir} ><>

^ Hp 8

(im>.m.-n.iliiin(tp- fiilnt , 40

LIKE Q|HT.i1nr 40

RangOpt'Mtm;BETWEEN ⁴¹

ListOperator:IN - ^ 41

Injrodwjng AHiihuic* 42

Usingllu'^.TVPF AHfihulf 42

Usinfithe'NiROWTVPEAtlnbulf 43

ImmducinKI'L/SQlExpressions

—

^{^ 43}

BooleanArithmetic Lxprcs^ions ....„ 43

Boolean('h.irjclffl.tprrssmns , _ - ~ -..44

BooleanHalf Expressions 44

DatatypesconversioninPL/SQL-... „..„ ...45

ImplicitConversion 45

Summary 4h

ASfl! Ftmrtlnn

_

mm,

in EuncSnc ill

IMTrAITiiniliim 51

roNr

at Eimdam !]

LQWEgjmLtffig&fu—

Horn !2

IMSTBF„nf i„,n

U

ITRIM.mrlKTKIMfimrliiim i!

BFPIAC~Trumliim j_

SUBSa

Eundtoa , i

U

TKAMSI AllEaocto ^ill

Il.ilrhinrlinm S'l

IU

UAH

Function .VI

111(HAHlurelinn Ml

AHO MONTHS

Function nl

MONTHS

BITUiFNFuniliiin 63

IAST

DAY

rum-linn 1.

1

vi

UrhebenechllicliocschOlzles^Material

Contents

331

1

NEXl DAY_EuatfML, SYSTIMISIVMPt.milion

•uni.-r<:[iinrnon.

AHSFunction.

TFII andFl

OOP

Funrlinrx EQYYCIUUiilOiuu-

HOUND

runrlmn,.

SORTFunction..

mnvi

i,

ln ft.-.n

...J4

...J£

...J&

...Mi ...JiZ ,...£B

...J&

...J&

....zq

SIGN fuiuiun- rtinvi'isinnFun*tinns

CQNVtRIfumjiuil-.

TO NUMB

fRr.inrtiim.

LOBruucliuiib_

BIlLITHAMLIuiKiiuu.

rMPryjnor.milimpty

g ob

runniotw...

MiypllnnmiisFunrlinns « ..

GRTATFSTandIFASTfunrlinm

_U 32 J3

_Z!

JS

_Z5 _Z6

_ZZ

-za

Summary

.

Chapter

4 Understanding

PL/SQC

ControlSlruclures

PpscribinRPL/SQLComrolStructures J12

UsingConditionalControl Statements JLi

IF-THENSlntPinpnt _B2

IF.THFN.FI SF^rnn».n. _fe

IF-THFN-fISFIFStatrnwn. -25

UsingCA5FStatements ...Jlfi

UsingSequentialControl Statements... JIB

GOTO

Strtlpmpnl J12

NllllSt..H-mnn1

Using LoopingConstructsin pi /SQi. , „ 31

IPOP

'ii.Mrmf-nl

FORI

QQE

^Sl-Hom-nt ,

J£

WHIIfInf)P

^f^nnl

JI5

Summary Jlh

Chapter

S

Implementing SQL

OperationsIn

PL/SQL

Workingwith

DDL

^and

DML

StatementsinPL/SOL -SB

UsinR theCREATEstatement _2fi

Using theINSERTstatement _aa

VII

UthebeiiechlllchgesGhuLclesMaterial

Ill

^{-| UsingtheSELECTstatement 99}

'I ₁

| I

Uim

theUPDATtstatement 100

_

j UsingtheDLTETtslalemeni 100

I-inutile[JKOPst.ili-nx-nl 101

||g ^ir.n,-.,,Imi,V.,.im.;i-h.i-i-;ai'IiI'l (.)! 1(11

Usingthe

COMMIT

slalemeni 102

gfp

^{UsingIhcROLLBACKstatement}

^KM

M ^Jm/f

UsinglheSAVLPQIMstatement IPS

1(1

B UsingIhi-StTTRANSACTIONstatement 105

• M

^{UsingtheLOCKTABLEstatement I(10}

^ lP'1

Sumnuiy 107

! ! !

WorkingwilhPL/SQLCollections 110

SelectingI'USQLColleclionTypes 110

Defining CollectionTypesinPL'SQL Ill

Declaring Collection Variables 114

InjtjajtejgBCollections 115

Referencing Collections 116

Assigning CrillciHuns 117

ComparingColliTtiims 118

Using CollectionMethods 121

WorkingwilhPL/SQL Records 128

DefiningandDeclaringRecords 129

AssigningValues_toRecords 130

InsertingRecordsintotheDatabase , , 131

UfidajjngaDatabasemillRecord Values _ „ 132

Summary 133

l-">rr l—lll

IntroducingCursors ....^ „.„.„ .. „ I36

Under*t,tnihnn Implit^itCursors , 136

Iiinilriliimsof jmgjtj^i'Ciif-prs 137

WorkingwithExplicitCursors „ ,...,.138

DetUringExplicitCursors , „ 138

0[H-nii>^ f<|)luilCursor 139

ObtainingRowsfromExplicitCursur 139

CIosihrExplicitCursor 140

CurtnrAl.tihni.-t 1AJ

LxplicitCursorAtlnhuHs. „ _ 141

impinitCqrsorAttributes 146

Cursor

FOR

loop

MB

Oirsni Variahlra 150

Cursor Expressions _, 155

viii

UrheberrechlllchgeschutztesMatotlat

Contents

Ml H.T

rOK

IIPDATFill

funnn

152

Chapter

8 '

Understanding

Subprograms

in

PL/SQL

161 162

PL/SOLProcedures 162

rux/Lrunciions , , ,.

WorkingwithSubprogramsIViMmelers

...164

167 167

)sinj;Not.ition lorSubprogramI'.ir.tmt'iL'K 168

I6B

173 174

UsinRRecursion withSubprograms.. , 175

UsiriR

AUTHID

Clause 176

177 177

Chapter 9

UnderstandingPackages' in

Pl/SQl 179

180

AdvantagesofPackages „ .. H t

IBO 182 183 The

STANDARD

Package

The

DBMS

PIPEPackage „ TheUTLFILEPackage

TheUTI.HTTPPackage The

DBMS

SQLPackage

IBS IBS

186 1B6 IB7

The

DBMS

ALERT Package 189

190 TheDBMS.SQLPackage

The

DBMS

PIPEPackaue

190 19.)

I»

195

The UTL FILEPackage 197

The UTLHTTPPackage I9B

200

Building\tuii

own

PWrKiWIf.il

n n

^ii.! 203

206

IX

UrheberrechllichgeschulzlesMaterial

iP

tabaseTriggersin

PI/SQL 207

DMITrmm-is DDITrfaen D.it.ikm.-Unithii:j'-r.

ISSIIMIOIIriTO'i*..

M.nnl.uningrriggefS InablinRor DisablingIrtRRits..

Dropping^{ii >}"¹ m<

RenamingThimerv

M.'ntiiii.¹.-Viliininniiii-Il.mv.Klinn*liMii.;I:v.'<[-

Summary

.

..206 20B

..m

..211 -212

..

JM

.216 ..217 ..217 ..218 .219 ..211

..221

UnderstandingI'lySQL Exceptions ...

Ift-BuillInceptions

..226

..226

.230

RaisingExceptionsinPL/SQL ~ .. ...

Ih..|MIVt-.Uffn.nnt

„ 231

•l'

234 2)5

UsingSQL

CODE

andSQURRM.,._ _

236 238

OlthinjiUnKindlcdIvccnlinns ...~„„™^.. „239

K ^Wp«

ⁱⁿ

^PI/SQL

²⁴¹

InlriHlminjOhjixlJ\\iCb 2-12

UsingObted Type 242

242 24.1

245 J4M

InheritanceInI'lsen 252

Mi'thod(tvt'inrlini^ 254

Di-nnincSQLTypos uquiwilcnttoPUSOLCollection Ivpcs ...J ^'

.

1siny PISOI1nllfUion*. WilliSQl(>l>|OClIvpes ^.'511

Summap

^>!,>

X

Urfiebe'rechlUchrjeschOlzles Material

Dalabase

managemenl isverycrucialandtediousworkforevery organizationasdatabases containsallimportantinformation's ofan organization.Oracleisadatabaseusedinalmost every organizaiiontormanagingdate,Oracleisaflexibledatabasethateases theproblemof managingdatastoredwithinyour system oronOracleserver.

Now,

rhequestionarisesthat

how Grade

manage*,[he dala.The answerfar (hisquestionisFL'SOL, whichIsused(ormanaging the database. PL/SQL is avcrv efficient andeasier lo learndatabase programminglanguage designedto manage database. Each Oracle versioncomes with itscorresponding version of PL/SQl.PL/SQl isaproceduraldatabaseprogramminglanguagethaiextendsIhi:functionality of StructuredQuery Language<5QIJandusesthemwithin itsproceduralstatements.PL/SQLuses

SQL DDL

and

DML

statementslo perform operations suchas [.bating, altering, deletingthe database.Theseare thebasicoperations requiredtomanagea database. PL/SQLexecutes

SQL

statements within

PU5QL

bfock. whichIsihebasicunitofPIjSQL, PL/SQL isaveryflexible databaseprogramminglanguagethaisupportstheadvancedprocedural programmingconcepts andelements,suchas:

Support forall

SQL

datatypesalong with its PL/SQL defined datatypes. Useof control aalenientsitF-blLSE-Tatfm,iterativestructures(KOR-LCOt,WHILE-LOOP).

Supportforsubprogramssuchasproceduresandfunctions,trigger*,,cursors.

Exception handlingthatletsyoucreatebugfreeprogramandhelpsinmanagingerrorssuch asdata not found.

SQL

canissuennlyasinglestatementatatime,whichwill

become

moretimeconsuming

when

needtoexecute

many SQL

statementsandleadsinlowdatabaseperformance3iutPL/SQLallows youlosendmultiple

SQL

^larementtodatabase simultaneouslyandthusreducestheoverhead ofaccessingthedatabaseforeverysingle

SQL

sljtcmcntIn thisway,therearclotsofadvantages ofusingOralle'sPL/SQL,In

know

[hoselet's startworkingwithPI/SQL.

TostartworkingwithPL/SQL, you must needto

know

itsneed, advantages,archilecture,and features.Thisisallthat

we

coverin thischapter.

What

is

PL/SQL?

PL/SQL isprocedural language, which is availablewith Oracle.PL/SQL is not a standalone languagebecauseitworkswiih Oracle.Itisan extensionloihe

SQL

(Structuredquery language, adatabase languageusedloperform various functionssuchasqueryingandupdatingdata,ona database).PLinPL/SQLstandsforprocedural language.PL/SQLextendsthe funciionaliiy of

SQL

and(hencombinesi(withproceduralfunctionalitysuch asloops,procedures, cursorsloprovide hellerandmoresatisfactory resultthanSQL.

Now,

rhequestionarises ihatif

we

alreadyhave

SQL

{.\flexibleandeasytousequery language) thenwhatistheneedto extend thefunctionalityof

SQL

by PL'SQL^Vou musttiecuriejus to

know

(hat

why we

need PL/SQLand whatare thereasonsbehindittsuccessandinmakingita ubiquitousdatabaseprogramminglanguage.Now,

we

willdepkttheneedIndevelopPL/SQL.

NeedofPVSUL

As

we know SQL

isa very easyandmoreconvcnienl dalabasequery language but besidesthatit

has

some

limitationsthai

become

theneedlordeveloping PL/SQL.InSQL,

we

havetoexecute a singlestatementaiatimerSo,if

we

needtoexecute multiple statements then Oracle database 2

Chapter1:Introductionlo

PUSQL

must becalled several limesTo execute all ihe issued statements thaireduce ihe database performance.

To

improveIhedatabase performance,PL/SQL^isdeveloped.There

was

aproblem with (hesecurity ofdatabase as thecodeisexecutedonclient-sideratherlhanserver.

We

were notablelohandle exceptionslhalleadinsuddentermination ofprogramatmnlime.

To overcomealltheseproblems,FL/5QLhasbeendeveloped.PL/SQLhaslotsofadvantagesthai have

made

^itaverysuccessfulandomnipresent daiahaseprogramminglanguagethaiisinwide usewith Oracle,Herearegiven theadvantages of PL/SQL:

Easilyadaptableand

SQL

supporting Enhanced Performance

Portability

Security

We

will studyall theseadvantage* ofPL/SQL in the sequence. Let'sstart studyingall these advantagesin detail.

Easilyadaptableand

SQL

supporting

PL/SQLsupports theentire characteristicandstatements availablein

SQL

such asselect,insert, update, delete. Databasecan easily liecreated and manipulated by incorporating the

SQL

statementsinPL/SQLblock.

PL/SQLsupportsall datatypesthataresupportedby

SQL

soyou do nothavetoconvert

SQL

datatypesinPL/SQLdatatypes.PL/SQLafsoallowsyoulouse

SQL

operators, functions,andso on.

PL/SQLiseasytolearnandunderstand asmostof thecharac[eristicsareextendedfrom

SQL

and the synta*usedin PL/SQLisverysimpleTo learn asituseslotsofkeywordsthaiclearlyexpress thepurposeofyourcode.BeingfamiliarwilhanyoftheprogrammingLanguage suchasc,c+-*-

makesiteasier tomemorize anduse thePL/SQLprogrammingsyntax.

Enhanced

Performance

PL/SQLprovidesbetterperformancethan

SQL

^becauseone

SQL

statementcanbeprocessedata timebydatabase,which means^ifyou haveloexecutemorethanone

SQL

statement thanyou haveloaccess database^as

much

as

SQL

statementyou needloexecute.Accessing the database severaltimesresullsinthecongestioninnetworkasdatabase are generallystoredon^serverand thus ihe time toprocess

SQL

slatemenl will automatically increased, which leads in low performance.

In

PUSQL,

morelhanone

SQL

statementcantiesent toOracle databaseforprocessingbecause

in PL/SQL,you cancollectallthestatementsin PL/SQLblockwhich isthe basicstructureof PL/SQL andalltheprogramsinPL/SQLcan noibecreatedwithout

PUSQL

block oryoucanalso usePL/SQL subprogramslocollectmultiple

SQL

statements.

A

subprogramisaPL/SQLblock thaiconsislsthesequenceofstatementloperform

iomp

specifictask.

A

subpn>gramtanbe calledbyotherPL/SQLprograms. Thus,Oracle databasewillbe accessed onlyonce andthere willbenocongestioninnetwork,whichresultsinthebetterperformance. See theFig.PU5QL- Lllounderstand theworkingwithout using

PUSQL

andwith usingPL/SQL.

3

IndependentSQLStatefnenL

BEGIN THEN SQLSlalernenl ELSE

SQLEJaleinent

ENDIF:

END,

OretlaDatabase

PUSGLblackrtnlhmultipleSOL$t£lsnttfil5 Fig.PiySQL-1.1

In Fig.PI/5QLl 1, ii is clearly rfepiried lhal before theuse ofPL/SQL. an application hasto communicatewith thedalabase withseveralindependent

SQL

statementsthaiultimatelyleads inoverheadonthedatabaseandreducesitsperformance,while with the use of PL/SQL,youare passingseveral

SQL

statementswithinsinglePL/SQLblockstodalabaseatsan>etimeandwhich

SQL

statements hastoheexecuteddepends onthe conditions provided within PL/SQI.block.In thisway,PL/SQLinOracle providesbelterperformance.

Portability

PL/SQL^isaportablelanguage,thatis,programscreatedinIheOracleenvironmentcanalsobe executedonany operating systemthat supportsOracle. PL/SQL programsfollowsWriteonce andruneverywhere(withinOracle environment) slogan.

Security

PL'SQLprovides highersecuritythan SQL. PL/SQL slored procedures places the application codeceniiaUyonserver ratherthan platingunclientsyslem.Byplacingtheapplicationcode centrally,you can hideIhecodefrom otherusers.You cansetthepermissiontoaccessPL/SQL procedure by varioususers torperformingdifferent activitiessuchas updating, deleting atableor data within a

ublc

These arc some advantages of PL/SQL due which PL/SQL ^is a ubiquhous daiabase programminglanguage. Let'shavean overview ofthe various PL/SQL versions to

know

the enhancement

made

ⁱⁿPL/SQLversions released with every

new

Oracleversion.

Versions ofPL/SQL

Tilltoday various versions ofPL/SQLhasbeenreleasedalong wilhtheversionsolOracle.Every

new

versionn|Oratletomeswilhits

own

versionolPL/SQL andevery

new

versioncomeswith additional characleri^ttL?from previous versiontoprovideyoubelter iunction.ilitv.In thisiwiok,

we

areconsideringIheOracle1Og andthePL/SQLversionthatis10.0thathasbeenreleased wilhit. fable1.1cfepiclsallthe versions availableMiltherelease ofOraclefOg.

4

Now, we

have discussed that

how

Oracle's PL/SQL ^hasbeen ^improving loprovide better performance.

lei'sstudythe features01PL/SQLlu

know

dllthdlPI/SQl tonsilsand

how

ithelp-*inmaking databaseprogrammingeasyandffficieni- Here,

we

studythesefeaiures in hrielbecauseall features will bediscussed in detail furtherin this book.

We

alsodiscuss the

new

features includedin(hePL/SQLforOraclelOg,

Features of PI/SQL

Many

ofthekeyfailuresinOracle1Ug PL/SQLaresameasiheprevious versions{PL/SQL9.2).

StillIhisversionhas

some new

improvements duetowhichthe databaseperformanceismore

enhanced,PL/SQLhaschangedthe

way

ofdatabaseprogramming andbecomes[heomnipresent databaseprogramminglanguage.

PL/SQLallows using

SQL

statements withinPL/SQLblock.Programflow can be controlledand multiple

SQL

statements can be ^sent to database at The same time. PL/SQL subprograms (priHⁿedure&andfunuiorvi]canbeusedto

make

theprogrameasier.

We

willdiscuss^allfeaturesindetailsinforthcomingchapters,buihere

we

canhaveanoverview ofallthemainfeaturesofPL/SQLalong with the

new

featuresaddedin(hisversion of

PUSQL

in Oracle1Og.Here

we

discussihefollowingmainfeatures,whicharethenfollowedbyihe

new

featuresaddedin(hisversion;

PL/SQLBlock

PL/SQLVariablesandconstants

u PUSQL

control structures Using

SQL

withinPL/SQL PL/SQLcollectfons PL/SQLRecords

U

PL/SQL Subprograms

PUSQL

packages ExceptionHandling

t1erer

ue

startdiscussingallthesefeatures

PI/SQL Block

A PUSQL

pn>gramconsists

minimum

nne PL/SQL^{olock. fl}isnotpossibletocreatea

PUSQL

program withtml

PUSQL

blockthat's

why PUSQL

block isvery importantand ^Ittisicunit of PL/SQL.

A PUSQL

block hasdifterentpartsandthatpartsareDeclarativepartwhere youdeclare itemssuchas types, variables;itisoptionalpart,Executedpariwhereyouwriteproceduraland

SQL

statementsandisnecessarypari ofPL/SQLblock;withoutit

PUSQL

block jsincomplete, andfinalpartisExceptionhandlingwhere youprovide(hecodetohandle exceptions;itisalso anoptionalpart.

A

PL/SQL block has tour ke>words, declare, begin,

exception,

and end.

declare

keywordisusedtobreak thePL/SQL blockin declarative_part, BilGIN isusedtodefine(he Executionpart,

EXCEPTION

keywordisusedtostarttheexception handling,and

END

keyword 6

I;IntroductionlaPL/SQL

isused toendThePL/SQLblock.See the following snippet tounderstand (hePL/SQL block structure:

Variables

and Constants

PL/SQL allows declaring^variablesandconstants so that Ihey canlieused in executionpart.

Variablesandconstantsmust haveTobedeclared belore usinginstatements (Executionpart).

Variablesand constantscan have any

SQL

Isuch asNUMBER, CHAR,

VARCHAR2)

orPL/SQL datatype(BOOLEAN).Theonly differencebetweenthe declarationofvariablesandconstantsis thatwhile declaring constants, you need ^{to use}

constant

keyword and valueshould be assignedatthetime ofdeclaration.Incase ofvariables,valuescanbeassignedinexecutionpart.

Control Structures

It isavery importantfeature ofPL/SQLthatletsyouallowaddingconstraintsonstatementsto send multiple statements to database at the same time rather than sending the several independent statements as

we

everdidinSQL. PL^SQlprovides the following controlstructures:

Conditional controls: This control allows you to execute several statements based on different conditions.

You

can check those conditionswith the help of

IF-THEN—ELSE

statement to execute the proper statement. IP clause checks the condition and ^if the conditionistruethen the statementwrittenunder

THEN

clausewillbeexecuted otherwise thecontrol transfers to

ELSE

clauseandthat willexecute.Inthisway,conditional control structureworks.

Iterativecontrols:Sometimes you needtoexecute asequenceofstatements multiplelimes, whichwasnot possiblewith

SQL

butPL/SQLprovidesyouiterativecontrolstocomplete yourrequirement. PL/5QLprovidesyou^several loopssuchas

loop — end

loop,

for

loop,

while

loop.Alltheseiterative structureswillbeexplainedinChanter-4.

Q

Sequential control: This control allowsyouto transferthe controlfromonepariofprogram toanother.PL/SQLuses

GOTO

keywordtosupport sequentialcontrol.

within

PI/SQL

hasextended all the^features ofSQL. ^II allows using all

5QL

dataypes, ^operators, andsoon. UsingPL/SQL, all the

DDL

and

DML

statementscan beexecuted from withinPL/SQLblock.

7

PI/SQL

Collections

Iike-miner pingrarnminj;language-,such asi,i+ PL/SQL^alsoallows urn

m

groupelementol similard,Va1vpevfni.i+-. jjmupr>iHi'meritsnisamel J Hi

l

H"i (

p*"-

iscInn^t'^ilh Ihelis*.'orarrays, lushtable (3 1sd s-o an. hut here arrays ,irc tailed,is varrays and hash table Lire

known

as absntlative.uravs.It> tjliarefamiliarwithanvoilierprogramminglanp,Lia£esuchasc,c+-then

it,lining i

Jl/SQI. toilerlirms heroine vitv easy but it vr>u an' no| fari«lia: wilh any other programmingI.i

n^{^.'ij,Lyf-"}andit i

1-llie: ar-sI languageurn are siudying(ht'ii

akn

have no needti>

worrybecause PL/SQL isone,i^ languagelounderstand The^List.101PL'SQLcollectionswillhe depictedintheChaptcr-uor Jlnsbook.

«/Sfl£ Records

keloids are likt? data stiuctures used in programming languages such as r. r+-. As data strutInn'sareusi'dtogroupprionselernerilsofrliiiereiitd,iiatypes,in|iit^jsanie

wav

^reinrcJ-.are

Lisi'ii."Ilie 'ink diltereiirv between(olleiboils and records is lb,il ie^.11 rnllerlionsvon ⁱ.m gioup clemcnlot similardalalypeandnsinjirf.-r.ordsdisstmiLir datalypestanIk¹grouped Fur

r\ampk\

u>nu vv,inl tntoiletr (hi¹ informationrir an empluyee such as his/her name, age, a<ldres> (henyou<_andothis\y,using asinglerecord hutrfyouusocollectionthenson havelo nirikt'\annusrnllnlion'suthash.,irc.iy)ingroupllu."elementsolsimilardalavpe. Theuse<A

PL^;

SQL

retorriswillhedeprrledin tin-Cha pier-ftoJthhbook

PL/SQL Subprograms

f'J

SQL

also.illuu'iiti-nin^pioieduresaridiLirictitirisasoilierprogramminglanguages.Inl'l_/S"QL, procedures and tunctions arccollectively

known

jssubprograms.

A

f'l.'SQL subprogram is innii.irin!'l'SCJI hlrnk ,ir:ori'.minishlni.k'.cMCjilih,LT,\siih[iin^r,ir[irnn^[Ih^'i'snnu'

name

so

lha(ilianI>i^linuiko!.mvufirrr-_inihrprogramorinanyiHlu-iapplkalion

.Subprruirani!.arc \\-t\ inipurjjni in l.ir^c applicnion,ns it breaks

down

the bi£ andcomplex apphta'ionIndi oa>ilvni.iii.inealilc

mod

u let.

SuhpruijMnjshaveprornotetJiher.oncr.'pfnireLisabihty. fhesame subprogramcanIn¹usud nioie thanonetimein \arj<jlikapplnadons

Once

^teitccisuctesiiullyIheniltan\n.-uscrldirer tly

m

.niv.i;)(>1kalumi\ilhi>nl\v,Lstiriji

i

timenn.vnliiij;il.ii^mi,iridai^amIncar\i-\\applurition, E'L'iQLproteduresandHmctions aresliticlurjlksamebuthavennl>oneditferent.t'inwhir ha ujiiLlianhas.-^"'J.-;.tlaii^e.Subprogram-,\\NIespl.irnindetailinChapM-r-Jiit\\ht\bnr>k.

Pi/SQL Packages A

^PI S

C par kajifi>knouria^1-fkitaSidsenlijct!,

w

hir hi*-um'iJInbundleloj;ir.,iilvrelaledPL'SQL

pji>iI'duri"'

mm

lions VI.'^QL iinrJc^ with In)', ril [iri'dETirierJ p.n ka^e*. surb as

::- L

:.

v

t7f, '..p,yy _;i;t r

:

-

thai ran he

n^d

rlirettb

m

ynurapfilit.stinn or

n™

fi,uka^es

%\ilri^nor riwn^peritiratmu^-ivhithran,itsobei re.iti'rl,

A

^I'i/SQI patkfik^eba^Hsup.iils,•yp-\ilii-Hirmand

hmk

S\'i-<iln.ilirjrip.irti,inbi'(iimIf-iJwilh In" o-e ol ^"i:v.¹. yr- - slafemenl and use lo deiiaie con^iants. variables, procedures,Uini'tions.cursorsandexcqulionsinapackage.

S

1;in!mdwHonto

PackagebodyIscreatedwith theuseof

CREATE PACKAGE

BODY,

SQL

statementand package bodycontainsproceduraland

SQL

statementBasicsirue lure tocreatePL/SQL packageisgiven

ThisisjustanoverviewofpackagesinPL/SQL.PackageswillbeexplainedindetailinChapter-9 ofthisbook.

Exceptionsareruntimeerrors thatinterrupttheexecution ofPL/SQLprogram.There are

many

such asprogramisnotproperty designed, dividinganynumberby zeroforoccurrence ofanexception.Toavoid theprogram interruption,PL/SQLprovides themechanismtohandle the exception.Tohandle exceptions, enclose theprogram codewithin

begin

and

end

clause with exception handler In PL/SQLblock,

exception

keyword isusedtostarttheexception handlinginPL/SQL^block.

PL/SQLalsoallows youtocreateyour

own

exceptions along wilh handling [he predefined exceptions(thrstype ofexception generally occur

when

any number

h

^triedtobe^dividedby zero,stackoverflowandsoon),

zero divide

exceptionwitloccur

when

youtrytodivideany numberbyzero.

Tocreateyour

own

exceptions,

RAISE

statementneedstobe used.

How

tohandle exceptions and

how

togenerateyour

own

exceptionwillbe^explained

m

Chaptei-11ofthisbook.

Withtheendofpreceding givenfeature,you mustunderstandthat

why

PL/SQLisso popular,

now we

continue with the

new

featuresaddedinPL/SQLforOracle10g^r

Features added

in

PI/SQL

for

Oracle lOg

featuresaddedin thisversion ofPL/SQL have

made

itmoreconvenient,easier,andmore automaticto use.

New

featuresaddedin thisversion are asfollows:

Q

ImprovedPerformance:Thisversion of L'L/SQLcarrieswilhmore automatedfeaturessuch as reuse the expression code, introduction of

new

datatypes for processing scientific operationswhichhas involvedinimprovingitsperformance,

Support for

FORALL

statement:

PDRALL

statement lets you allow to process

DML

statementsmoreefficientlybyiteratingover non-consecutive indexes,PL/5QLhas

indices OF

and

VALUE OF

clauseslo iterateover non-consecutiveindexes,

forall

statementuses specifiedindexinacollection to ilerateandprocess thestatements.

9

Introducing

BINARY, FLOAT

and

BlNARY_DOUBLF

floatingpointdatatypes: These are two

new

introductions

m

thedaIatypes.These datatypes supportIEEE 75-1 loniialandareof floating point type. These are used for intensive scientific compulation where floating dalalypes areusedinealculalion.

EnhancedOverloading:Overloading hasimproved in thisversion.Subprogramslhathave differentnumericdatatype*asparametercan be overloadedinthisversion.

Improved NestedTables:No*v vou have more enhancedNestedTallies.Usingthisversion, nestedtablescan becomparedlorequalitysuchasvon can checkthatwhethera nested tableisasuhsel ofanother nesledlable or nut.youcancheek wheihtraparticularelement

isa

member

ofaspecificnestedlableornot.

CompileTimewarnings:Thesefeatureshelps

m

making.1PL/SQLprogrammors-robustand well functioning;. By using ihis feature, Oracleissues warnings during PL/SQL program compilation

when

foundany problem suchaspassingchar valueto lloatcolumnin

INSERT

statement. Youcan use L-LsOL VJ/ilJN'[NGi initialization parameterand DPV3

WARNING

pankagetomanagesompile time warnings.

Implicitconversionbetween

CLOB

and

NCIOB:

Keiore introductionofthisfeature, user ha\etouse':'j_i:LOaand^7_i

N^LOh

toconvertCL-'iinto

acLOb

and

nclob

into

clob

but

now

userdonol have(oconvertthemexplicitlybecauseinthisversion,Oraclemakes implicilconversionbetween them wherever^required,

U

FlashbackQueryFunctions: Tins^featureisusedto

know

thetimeslampasviiMled with a particular

^CN

(SystemChangeNiirnljefiandalso 5"tJnitpartiiular

moment

oftimeandfor tinspurpi>seryou have ^£t:_r"JTT^I^'TAi-'P;mdTIV:TfjTA^!F_TCj_C!."-Nfunctionsto use..

jch

TO 'l

lM^TAUr

Junction takes

^cn

numberasparameterandreturnsthetimestamp assnt lated with tli.it

SCX

while 7

rM^T.-MP

TO .S'li function takes time value as parameterand^returnsilie3i7Natthatriionient.

Here,

we

have completed discussionon PL'SQL featuresaod the

new

fediu resadded inthe PIjSQLforOraclel()g.

Let's study the basic working structure oi PL/SQL in Oracle.

We

are talking about the architecture ofPI-/SQLinOracle,ftisnecessarytounderstandthearchitecturebecauseifyoudo not

know

that

how

Pl.'SQl proc essiheslaiementsirnludedinPI/SQlbkick.itbecomesdifficult tounderstandIho PL/SQL programming.

PL/SQL Architecture

in

Oracle

Aj chile* lureorPI.'SQL representsitsbasi<workingintoordmatiouwith Oiacle.

A

PL/SQLblock orsubprogramtonsils, ofboth procedural statementsand SQI statement Pi.'SQI.architecture describestheprocessthat

how

PL/SQLinoracleinterpretsaPL/SQLblock orsubprograms.

PL/SOLarchitectureinOraclelOgtonsists otPL/SQLblock orsubprogram, PL/SQLengine,and Oracleserver.PL/SQL engine compilesandexecutesthePLMJI.block orsubprogram. Oracle databaseorapplicationdevelopmenttoolssuchasOracleforms.OraclereportscontainsPL/SQL eiigme, PI/SQLengine contains PL'5Ql proceduralslatemenl executor toexecute procedural statements within a PL/SQL block orsubprogramand Oracleserver contains

SQL

statement executortoexecute

bQl

statements.I'j;.SQ| engineandOracleserverworkinco-ordinationto processa PL/SQL block or subprogram. Fig.PIASQL-l.2 depictsthat

bow

PL/SQL engineand Oracleserverworktogetherandprocess aPL/SQLblockorsubprogram

10

Chapter1:Intmluelkmto

PUSQL

BEGIU

IF..THEN SOLSlslemenl ELSE

SQLSldFnenl ENDIF:

END:

Procedural Slslemenls

SQLStatements

Procedural SlaFEirsnl Exocutor

PUSQLBin*

f

^\

Oracle Server

SQLStelamenl Esecutor

V

)

Fig.rUSQL-13

PL/SQLengine accepts procedural statementsand

SQL

statementsas input.

PUSQL

engine then process a! procedural statementsthrough procedural statementexecutor and send the

SQL

statementstoOracle databaseserver toprocess. In thisway,a PL/SQLblock orsubprogramis

processed.

Withthis,

we

have completeddiscussionon

PUSQL

Archilectureandthe

way

a

PUSQL

hlock or subprogramprocessed.

Withtheendof discussionon

PUSQL

architecture,

we

finishthischapter.Bynow, vou must haveenoughideaaboutPL/SQLsuch as the requirement ofPlJSQL anditsarchitecture.Before closingIhischapter,let'shaveagfanceonsummary.

Summary

In thischapter,

wc

havestudied about:

TheintroductionolPL/SQL The

Need

andadvantagesof

PUSQL

Thevarious versionsofPL/SQL Features ofthePL/SQL The

PUSQL

Architecture

11

In

^{Iheworldoi {}

nmpultwalkm,

wLumi ivctalk,iL)ootprogramsihen

we

mustalsu take into considerationconcept.:.u<-hastheprogramslructurcanddatatype.

A

program

nwv

contain venous variable- to store data and those variables must be assigned with some specific datalvpes Thus, dalatvpescanl?edefinedasa formattostore daia.PL/SQL provides various dataivpes,such as ii-jif^er, f-.gjvi lhal ht'ljjCrack:iochoose aslurageformaltorinlernal representIion of objects*»nclimposear.in.^1?orvaluesuponthon*objetts,

A

PI/SQL^developer should hoveknowledgeofllii.¹ predefinedd,jl,ir>pe^ associatedwith PL/SQL solhaionecan chooseappropriatedatatypefora variableusedinanapplication, horexample,you can choose PL/SQL data typo v"A!^JfJHAj^J2 for

name

of<i

human

being, Further knowledge oi theLmic programstruUurent PI'SQ! alsohelps usinpartitioning Ihe appliiotion into easilymanageahle sections.Thispartitioning alsohelps ustocatchand^ij\flaws

m

ditfarentpadsoflhe program.

We

begin ihischapter with a discussionon the PL;

SQL

blockstructure movingontofurther concepts, such asdalatvpi'S. lexical units, jnHoperators whoseprior knowledge is essential lieforebeginning withPl./SQI

.

Describing Block Structure

PL/SQLisalso

known

asablock-slrucluredlanguageasasimplePL'SQL programcontains

many

logicalblockswhore eachblock solves apartoitheproblem. For example, aPL'SQL program

mav

usearlv funr [ioninside iKelftojierforrnsomeoperalionamiihis lunt'EioriIhen h^tps in solving

some

part ofthe*problem,whidiistoheaddressedbythes<iniePl/SQI program.

Toundersland the functioningofaPL/SQL program,

we

divideilsstudyintoblocksandstudy eachblocksep^iralely F.veryprogramLonsisls ofoneormoreblinks,wheree.iihblock contains relaleddoclaralionsandstalenienls.

A

PI /,SQI blorkran<on^isloiIhefollowing sertionsinIhis sequence:

BlockHeader Declaration

Sen

ion F\otulionSc* tion Exceplion Section

Block Header

Theblockheader section contains(he

name

ofthebfock.IIisoptionalandused only

when

^there

isnneedtoassignany

name

loaPI/SQL program.

Name

assignedtoaPL'SQLprogramhelpsin calling it in oilierPL/SQL programs. The PL/SQL block header in PL'SQL bfockslructurc is

shown

inFig.PL/SQL-2,1

.

! n

i

*""'";"' '"

I evfptil.-^

|

I.M¹

hp.t'lVSfjL-2.1

14

Chapter2:

PUSQL

Essentials Aftertheblock header,

we

needtodeclarePL/SQLvariables(which arefurtherusedinaPL/SQL executionsection)inthe declaration^section.

Declaration Section

We now

needto putinformationinaprogramblock about thevariablesusedintheblock,such asassociatingdatatypeswithvariables, initializing variables.This objectiveisachieved using the declarationsection.However,thissectionisnotcompulsory.Itisusedinablock only

when we

needtodeclarevariables.Ifasituation ariseswhere

we

donotneedvariables, thissectioncan beavoided.

Thedeclaration section starts with the

declare

keyword. After the

declare

keyword, declarations ofvariablesareprovided.

Thesyntaxforwritingthisblockisasfollows:

DECLARE

VariablenaaecspacoDatatype:

Theexecution section follows the declarationsection.

Execution Section

Execution section follows the declaration sectionintheconventionalprogramblock.Execution sectionisthemost important sectionofthePL/SQLblockstructure as^itisresponsiblelorthe actualexecution of the code. Inotherwords,

we

can saythati! isIhe functionalpartof the PL/SQLblock.

This sectionstartswith the

begin

keywordand endswiththe

end

keyword.Between

begin

and

END

keywords,

we

writeThesetofinstructions orcodethat

we

wan!lo execute using PL/SQL.

Itishowevernot necessarythatthecode

we

writein thissectionmust produce anaction.The codegetscompiled even ifthe instructionsgiven in the executionsection donotsignifyan action.

All

DML

(DataManipulationLanguage)and

DDL

(DataDefinitionLanguage)

commands may

be used inthissection. Listing2.1 contains an exampleof the execution section of thePL/SQL block.

Listing2.1:ExampleofP1VSQLblock containingexecution section

Listing3.1 showsthecodeto retrievethesystem dateintoa variable

1v_date_time)

oftype DATE.

Toretrievethedate,

we

use

SySdate

(In-BuiltPL/SQLfunction).

15

To display the oulpul, v.c use

?UT_LINE

procedure ot

HBMoj^TrnT

fln-Huill f'USQL package).

Todisplaytheresult,

we

use

set serverout

put on becausebvdelauli

SQL

"Plusdoesnoi re.nlwhalaPL/SQL programhaswriitenwiih

dbxs_outfu~

p.irkage.

Wi

ⁱdiscussmoreaboul Theseconceptsfurtherinthebook.

Listing2.1can be executedinthefollowingtwoways:

Using

SQLTlus

UsingiSQL"PIlis

Lei'sexplaineachw*tyindetail U>executeasimplePL/SQLprogram.

Using SQL'Plus

Itisusedloexecute(hePL/SQL programs andsiaiementsonasingleusermode,thaiisOracle

PV5QL

installedonasystem cannotbesharedbymultipleusers.

lei'ssee

how

toexecute Lisling

Al

in

SQL

Plus.Toexecute Ihelisting,Killow theslepsgiven here:

1. Click slarl-^All Programs-*

Ofade-QraDblQg_homel-^

Application

Development SQL

Plus,as

shown

inrig.PL'SQL-2.2.

16

fiSrFI^QLZr7 TheLog

On window

appears,as

shown

inFip.PL/SQL-2.J.

2: [-ivi/tittls

2. In the.Log

On

window,enter theuser asscoltintheUser

Name

textbox,as

shown

in

3.

Now,

enter Ihepasswordas ligerin Passwordlextbox|FigtPI7SQL-23).

4. Then, enter Ihe hoslsiringintheHost textbox,,is

shown

inFig,PL/SQL-2. 3.

5.

Now,

clickThe

OK

button -2,3}.The

Grade SQL

*Plut-

window

appears, <is

shown

in Fig.PL/SQL-2,4.

G.

Now,

write ihecodeshown in Fig.PL/SQL-2.4.

2.1 in theOracle

5QL

'Pluswindow,asshownin

:UtiliseIt.lJ.?.!- anlur J*n39IV2I:1f?«B CO191?,it**. (Illright*-Ibr^m.

hePirlitiaalnij,m.flPjrd UliEHUEHDIIFlJIII

H

KCLME

J i>_ddle_ll"*IHIE;

3 HGIH

t ULEtr IMID

7, Now,press the

ENTER

keyto thecode.As soonasyoupress ihe

ENTER

key, ihe outputwillbedisplayedas

shown

in PL/SQL-2..K

17

Thisis(lieprocesstoexetule thePL/SQL programs andst.ilemcnlsin

5QL

"Plus.

Using iSQL'PlusConsole

It

h

ustdtoexecutethe PL/5Q!.

prngums

and SQL statements(>rinelwork'd SystemsMultiply usersinihesome network can access OraclePL/SQL installed onasystem bvspecifyingthe networkpathiForexample, tip; .'/1otJ^-

rest

:r-S60.i.^qlp_U5.')-

Ifl's fnllow Iheitepsgivenhereloexeiult?Lislitig2.1 iniSQl.'Plustonsok¹:

1. fnrerthe

UKL

^'lorexample, o

—

p: '.'Ice

jlncG^

:b5&J /laqin.-js>

m

IheAddressbarof your

weh

browser fFig.PL^QL-2-f^ to open the Oracle i$QL*Plus login consoler digPI/SQI-2.7j

i

riS-PUSQl-T-b

Click the

Go

button, as shown in Fig.PL/iQL-2.f). The iSQL'Pluslogin console appears ilifl.PlySQL-2.7).

In(he iSQl.'I'Lus login ton^okvenk'fiheu^er

name

iCOU^intht*Usprnamelext iiekl,as showninFig.PL/SQL-2. 7.

Then,enler thepasswordinthePasswordteMlieldfFig.PL/SQL-2.7^ Inourcase,

we

have used thederailIipassword(hatislifter.

Enter the host string (hatisorrlin

Conned

Idenlifirrtextlidd,as

shown

inFig.PL/SQL-2.7.

Vow,

dick the Login button(Fi[i.PL'bQL-2.7i. The Oracle iSQL'Pltisworkspace

window

appears, as

shown

inFig.PL/SlJL-2,H.

1B

Clmpler1:PL/SQLEssentials

Ltgin

Fig.PUSQL-17

7, Write thecodewritteninListing2.1 intheworkspace

window

,mdpresstheExecute bution, as

shown

inFig.PL/5QL-2.8.

The Oracle iSQL"Plusworkspace appears onceagain to display the output, as shown in Fig-PtVSQL-2.9.

19

' - '

Fig.PUSQL-2.9

Thisistheprocesstoexecule thePL/SQL programsusingiSQL'Plus console.

Now,

ifyou want toexecute

some

more programsthenclicktheClear button, as

shown

inFig.PIVSQL-2.10.

Thiswillcleartheworkspace

window

(Fig.PI75Ql-2.10)andthenyou cancontinueworking with other

PUSQL

programs.

|Boo¹UUJI

Won

I(V

Fi^PL/SQL-2.10

20

UrheberrechtlichgeschutzlesMaterial

2: Essential*

So, thesearc thetwo processes toexecute the PL/SQL programs.

No«,

Id'scontinue our discussion with the nextPL/5QLbloc kstructure thatisexceptionsection.

Section

Exceptions arc certain abnormal conditions, which occursometimesduring execution of a PL/SQL program and causeiheprogram_(oterminate.Exceptionsectionof theprogramblockis

anoptional one. Allsections oftheprogramblock except the execution section are optional sections.To

make

aPL7SQL programtreefrom runtimeerrorsor exceptions,youarcsuggestedto use exception sectionin

PUSQL

programs.

Theexception section in a block begins with the keyword

EXCEPTtON

and endswith (he keyword END.

This sectionbasicallycatches theerrors thatoccur

when

theprogramisexecuted.Thesectrors arecaughl using the various function?providedspecificallyfor thispurpose.

Some

functionsfor exception handling are provided under the

STANDARD

^or DBMS

STANDARD

packages.

We

readmore onexceptionsinChapter1[(Handling ExceptionsinPL/SQL).

Thefollowingcodesnippetshowsasimple

EXCEPTION

sectionorPL/SQLprogram.

Afterunderstanding the blockstructureofPL/SQLprogram,let'sstudy various[ypesof blocks.

of Blocks

Depending ontheheadersection,there aretwo maincategoriesofblocks—

anonymous

blocks and

named

blocks.

A

third kind ofblock

known

asnestedblock is alsoavailablethatcan containoneormoreanonymous/namedblocks.Let's

now

studyalltheseblocksindetail.

Blacks

Anonymous

blocks,as the

name

suggests,arethose blocksthatdonothaveaname.Therefore,

we

dono(haveanyblockheaderfortheseblocks;hut iheyhaveoneorminesections,which aredec bra!ion,execution,andexception.Since theydonothavcjd^rnuiici, cannotcolltliem.

Due

tothis,

anonymous

blocksare alsonotstored in thedatabase However,an

anonymous

blockcancallother

named

blocks.

Usesof

anonymous

blocksinPL/SQLare:

Declaringvariables ihatcan be usedintheexecutionsection.

Declaring cursorsthat canbe used intheexecutionsection.Cursors arcusedto access element* stored in a collection.

We

will study in detail about cursors in Chapter 7(Understanding CursorsinPL/SQL).

21

Executing Cursor

SELECT

^i^(friirjn[r

Named Blocks

N^meH

blocks aretheblot ks thathave

some

name.Itisobviousihat¹¹a blockisa

named

block,

irwouldcontain^.1blockheaderbecause[he

name

ofiheblock isdefinedinablock header.

Therefore,anv

named

blockwould

com

j in atleasttwo01mnreblocksections.Thisijibecause

file fx?iuMon section is a ncie^sary section torany PLVSQI block and ^if

we make

header compulsoryIhcntherewouldlieI

wo

compulsorysectionsin ihi-hloik.Overall,

named

block

cm

hujve.illtheloursections— block header,declaration,execution,andexception.

Nested Blocks

Nested blocksare yetanother important kindot PL'SCJL blocks.

When we

haveblocks placed insideotherblocks,

we

call this

phenomenon

asnestingandsuch blocksare

known

asnested blocks.Thesenes'tedblocks mightfurtherdifferfromeachotherdependingonseveral different

<riierions.

One

ofnuth impoitanl cnterions isMielevelofnesting. Each Nested block has a certaindepth. Thisdepth isthe numberol levels to whiih ihat hloiklirfs been nested, for example,rf

we

h.ive.iblock wilhrnablock,thenitiss^id tobe nestedto leveloncrJltheblock containedinsideIhe blockfurtherContainsanotherblock,thenthe levelof nesting increasesto two.Nestingisallowed onlvintwosetlions

—

exetutionande\<eplmn

—

^<iithePL/SQLblock.

Lei's

now

discustheprionsdata types availableinPL/SQLtostorerealworldentities,suchas numbers,texr.andimages.

Introducing Datatypes

Havingknowledgeabout datalypcs

n

essentialbe'orebeginning with applicationdevelopment

in any programming language because datatypes form ihe most haiic building blocks for developingany program.

PLAQl

supportsall thepredefined datatypesinSQL andalso

some

additional ones. There has to be a datatype for even¹ variable or p.irameter in PI/SQL.

Depending oniheneedojtheapplication,

we

canchoosethedatatypethatsuitsus thebest.

Venouscliil0typesavailableir>PI/SQlare:

J Number

Types

J

CharacterandSlungIvpes

J

NationalCharacterT\pes

liiif)k-rinTvpe¹

J LOU

Types

Daleand TimeTypes Subtypes

Number Types

Number

typesare sralardatatypes.Thedatatypesthai holdsinglevalue are

known

as scalar datatypes.A*the

name

suggests,numbertvpes areused\usiorenirmerii v.ihtes.These numeric vafues

may

includeintegers,real numbers. UsujIIv.

we

usenumbertvpeslor fieldsthatneed numbersasvalues,suchasagermeasurement

22

Chapter2:PlJSQLEaraiMls The

number

types arefurtherdividedintofollowingtypes:

BINARY.INTEGER NUMBER PLSJNTECER BINARY.DOUBLE BINARY_FLOAT BINARYJNTEGER

BINARy_INTEGER

storesthesignedintegerin a two'scomplimentform.Therange of binary typesisfrom

-2147483647

to+21474Q3647. This type isgenerally used

when

iveneed lo perform arithmeticoperations.Ithas followingsubtypos:

NATURAL: May

store integersinrangefrom0lo214748i647.

POSITIVE:

May

store inlegcrsinrangefrom1lo2147483647.

NATURALN: Same

asNATURAL,bulcannotassign null to integer variables.

POSITIVEN:

Same

asPOSITIVE,bulcannotassign nulltointeger variables,

5ICNTYPE:ItisusedinprogrammingJri-statelogic thatisanintegervariablecanonlyhave valueeither-1, 0,or1.

NUMBER

The

NUMBER

typecanholdfloating-poinivalues orintegersintherange[Venn1.0*1 0

A-130to 9.99x1

0

A125,Thegeneral syntax of

NUMBER

typeisasfollows:

NUMBER_C(P reelsion.seal e)

]

Intheprecedingsyntax:

precision:representsthetotalnumberofdigitsthatanumbervariablecarstore.

scale:representsthetotalnumberofdigitsthata

number

variablecan containtotherightof (hedecimal.Thescalecanrange from -84lo 127.

We

can only useintegerliteralsfor specifying precisionandscale.

Now,

see ihesubtypesofthe

number

typo.

DEC,DECIV-AL,andNUMERIC: Usedtodeclarefixed-pointnumberswith precisionuptoJB decimaldigits,

DOUBLE PRECISION

and FLOAT;Usedlodeclarefloating-pointnumberswith precision uplo3tidecimaldigits.

PEAL:Usedlodeclareflonltng-poinlnumberswith precisionuplo10decimaldigits,

INTEGER, INT and

SMALL

INT;Usedlodeclareintegerswith a precisionuptoJSdecimal digits.

PLSJNTEGER

PLS

INTEGER

typecansloresigned integersinrangefrom

-JI47483647

to+2147-163647.

pls_integer

datatypeismoreefficientlhan

number

datatype sincePl,S_:w"?tf:G?:^ values

23