計(jì)算機(jī)課件Oracle8i SQLPLSQL基礎(chǔ)

Oracle8iSQL&PLSQL基礎(chǔ)

Oracle8iPLSQL04

16-1CopyrightOOracleCorporation,1998.Allrightsreserved.

ControllingPL/SQLFlowof

Execution

Youcanchangethelogicalflowof

statementsusingconditionalIF

statementsandloopcontrolstructures.

ConditionalIFstatements:

?IF-THEN-ENDIF

?IF-THEN-ELSE-ENDIF丫

?IF-THEN-ELSIF-ENDIF

16-2Copyright6OracleCorporation,1998.Allrightsreserved.

IFStatements

Syntax

IFconditionTHEN

statements;

[ELSIFconditionTHEN

statements;]

[ELSE

statements;]

ENDIF;

SimpleIFStatement:

SetthemanagerIDto22iftheemployee

nameisOsborne.

IFv_ename=1OSBORNE1THEN

v_jngr:=22;

END一工F;

16-3Copyright6OracleCorporation,1998.Allrightsreserved.

SimpleIFStatements

SetthejobtitletoSalesman,the

departmentnumberto35,andthe

commissionto20%ofthecurrentsalaryif

thelastnameisMiller.

Example

IFv_ename='MILLER1THEN

v_job:=1SALESMAN1;

v_deptno:=35;

v_new_comm:=sal*0.20;

END-IFr

16-4CopyrightOOracleCorporation,1998.Allrightsreserved.

IF-THEN-ELSEStatement

ExecutionFlow

TRUEFALSE

_IFCondition_

THENActionsELSEactions

(includingfurtherIFs)(includingfurtherIFs)

16-5CopyrightOOracleCorporation,1998.Allrightsreserved.

IF-THEN-ELSEStatements

Setaflagfororderswheretherearefewer

than5daysbetweenorderdateandship

date.

Example

IFv_shipdate-v_orderdate<5THEN

v_ship_flag:=*Acceptable,;

ELSE一

v_ship_flag:=*Unacceptable1;

END-IF;

16-6Copyright0OracleCorporation,1998.Allrightsreserved.

IF-THEN-ELSIFStatements

Foragivenvalueentered,returna

calculatedvalue.

Example

IFv_start>100THEN

v_start:=2*v_start;

ELS?Fv_start>=50THEN

v_start:=.5*v_start;

ELSE一

v_start:=.1*v_start;

END-IF;

16-8CopyrightOOracleCorporation,1998.Allrightsreserved.

BuildingLogicalConditions

?YoucanhandlenullvalueswiththeIS

NULLoperator.

?Anyexpressioncontaininganullvalue

evaluatestoNULL.

?Concatenatedexpressionswithnull

valuestreatnullvaluesasanempty

string.

16-9CopyrightOOracleCorporation,1998.Allrightsreserved.

LogicTables

BuildasimpleBooleanconditionwitha

comparisonoperator.

ANDTRUEFALSENULLORTRUEFALSENULLNOT

TRUETRUEFALSENULLTRUETRUETRUETRUE\TRUEFALSE

FALSEFALSEFALSEFALSE\FALSETRUEFALSENULLEALSETRUE

NULLNULLFALSENULLNULLTRUENULLNULL\NULLNULL

16-10CopyrightOOracleCorporation,1998.Allrightsreserved.

BooleanConditions

WhatisthevalueofV_FLAGineachcase?

v_flag:=v_reorder_flagANDv_available_flag;

V_REORDER_FLAGV_AVAILABLE_FLAGV_FLAG

TRUETRUETRUE

TRUEFALSEFALSE

NULLTRUENULL

NULLFALSEFALSE

16-11CopyrightOOracleCorporation,1998.Allrightsreserved.

IterativeControl:LOOP

Statements

?Loopsrepeatastatementorsequence

ofstatementsmultipletimes.

?Therearethreelooptypes:

-Basicloop

一FORloop

一WHILEloop

16-12CopyrightOOracleCorporation,1998.Allrightsreserved.

BasicLoop

Syntax

16-13CopyrightOOracleCorporation,1998.Allrightsreserved.

BasicLoop

Example

DECLARE

v_ordiditem.ordid%TYPE:=101;

v_counterNUMBER(2):=1;

BEG?N

LOOP

INSERTINTOitem(ordid,itemid)

VALUES(v__ordid,v_counter);

v__counter:=v_counter+1;

EXITWHENv_counter>10;

ENDLOOP;一

END;

16-14CopyrightOOracleCorporation,1998.Allrightsreserved.

FORLoop

Syntax

FORcounterin[REVERSE]

lower_bound..upper_boundLOOP

statementl;

statement2;

???

ENDLOOP;

?UseaFORlooptoshortcutthetestfor

thenumberofiterations.

?Donotdeclaretheindex;itisdeclared

implicitly.

16-15CopyrightOOracleCorporation,1998.Allrightsreserved.

FORLoop

Guidelines

?Referencethecounterwithintheloop

only;itisundefinedoutsidetheloop.

?Useanexpressiontoreferencethe

existingvalueofacounter.

?Donotreferencethecounterasthetarget

ofanassignment.

16-16CopyrightOOracleCorporation,1998.Allrightsreserved.

FORLoop

Insertthefirst10newlineitemsfororder

number101.

Example

DECLARE

v_ordiditem.ordid%TYPE:=101;

BEG?N

FORiIN1..10LOOP

INSERTINTOitem(ordid,itemid)

VALUES(v_ordid,i);

ENDLOOP;一

END;

16-17CopyrightOOracleCorporation,1998.Allrightsreserved.

WHILELoop

Syntax

WHILEconditionLOOPConditionis

statementl;evaluatedatthe

statement2;beginningof

eachiteration.

ENDLOOP;

UsetheWHILElooptorepeatstatements

whileaconditionisTRUE.

16-18CopyrightOOracleCorporation,1998.Allrightsreserved.

WHILELoop

Example

ACCEPTp_j?ricePROMPT*Enterthepriceoftheitem:

ACCEPTp_itemtotPROMPT*Enterthemaximumtotalfor

purchaseofitem:,

DECLARE

???

v_qtyNUMBER(8):=1;

v_running_totalNUMBER(7,2):=0;

BEGIN一

???

WHILEv_running_total<&p_itemtotLOOP

v_qty:=v_qty+1;

v_running_total:=v_qty*&p_price;

ENDLOOP;———

16-19Copyright0OracleCorporation,1998.Allrightsreserved.ORACLe

NestedLoopsandLabels

?Nestloopstomultiplelevels.

?Uselabelstodistinguishbetween

blocksandloops.

?ExittheouterloopwiththeEXIT

statementreferencingthelabel.

16-20CopyrightOOracleCorporation,1998.Allrightsreserved.

NestedLoopsandLabels

BEGIN

?Outer_loop?

LOOP一

v_counter:=v_counter+l;

EXITWHENv_counter>10;

?Inner_loop?

LOOP一

???

EXITOuter_loopWHENtotal_done=*YES';

——Leavebothloops

EXITWHENinner_done='YES1;

--Leaveinnerlooponly

???

ENDLOOPInner_loop;

???

ENDLOOPOuter_loop;

END;

16-21Copyright6OracleCorporation,1998.Allrightsreserved.

The%ROWTYPEAttribute

?Declareavariableaccordingtoa

collectionofcolumnsinadatabase

tableorview.

?Prefix%ROWTYPEwiththedatabase

table.

?Fieldsintherecordtaketheirnames

anddatatypesfromthecolumnsofthe

tableorview.

16-22CopyrightOOracleCorporation,1998.Allrightsreserved.

AdvantagesofUsing

%ROWTYPE

?Thenumberanddatatypesofthe

underlyingdatabasecolumnsmaynot

beknown.

?Thenumberanddatatypesofthe

underlyingdatabasecolumnmay

changeatruntime.

?Usefulwhenretrievingarowwiththe

SELECTstatement.

16-23CopyrightOOracleCorporation,1998.Allrightsreserved.

The%ROWTYPEAttribute

Examples

Declareavariabletostorethesame

informationaboutadepartmentasitis

storedintheDEPTtable.

deptrecorddept%ROWTYPE;

Declareavariabletostorethesame

informationaboutaemployeeasitis

storedintheEMPtable.

emprecordemp%ROWTYPE;

16-24CopyrightOOracleCorporation,1998.Allrightsreserved.

AboutCursors

EverySQLstatementexecutedbythe

OracleServerhasanindividualcursor

associatedwithit:

?Implicitcursors:DeclaredforallDML

andPL/SQLSELECTstatements.

?Explicitcursors:Declaredandnamed

bytheprogrammer.

16-25CopyrightOOracleCorporation,1998.Allrightsreserved.

ExplicitCursorFunctions

ResultSet

CurrentRow

16-26CopyrightOOracleCorporation,1998.Allrightsreserved.

ControllingExplicitCursors

DECLAREOPENCLOSE

?Createa?Identify?Loadthe?Testfor?Release

namedtheactivecurrentexistingtheactive

SQLareasetrowintorowsset

variables?Returnto

FETCHif

rows

found

16-27CopyrightOOracleCorporation,1998.Allrightsreserved.

ControllingExplicitCursors

Openthecursor.

Pointer

curso=

Pointer

Pointer

Cursor

Closethecursor.

Cursor

16-28CopyrightOOracleCorporation,1998.Allrightsreserved.

DeclaringtheCursor

Syntax

CURSORcursor_nameIS

select_statement;

?DonotincludetheINTOclauseinthe

cursordeclaration.

?Ifprocessingrowsinaspecific

sequenceisrequiredusetheORDER

BYclauseinthequery.

16-29CopyrightOOracleCorporation,1998.Allrightsreserved.

DeclaringtheCursor

Example

DECLARE

CURSORclIS

SELECTempno,ename

FROMemp;

CURSORc2IS

SELECT*

FROMdept

WHEREdeptno=10;

BEGIN

16-30CopyrightOOracleCorporation,1998.Allrightsreserved.

OpeningtheCursor

Syntax

OPENcursor_name;

?Openthecursortoexecutethequery

andidentifytheactiveset.

?Ifthequeryreturnsnorows,no

exceptionisraised.

?Usecursorattributestotestthe

outcomeafterafetch.

16-31CopyrightOOracleCorporation,1998.Allrightsreserved.

FetchingDatafromtheCursor

Syntax

FETCHcursor_nameINTO[variablel,variable2f...]

|record_name];

?Retrievethecurrentrowvaluesinto

outputvariables.

eIncludethesamenumberofvariables.

?Matcheachvariabletocorrespondto

thecolumnspositionally.

?Testtoseeifthecursorcontainsrows.

16-32Copyright6OracleCorporation,1998.Allrightsreserved.

FetchingDatafromtheCursor

Examples

FETCHclINTOv_empno,v_ename;

OPENdefined_cursor;

LOOP一

FETCHdefined^cursorINTOdefined^variables

EXITWHEN...7—

???

--Processtheretrieveddata

???

END;

16-33CopyrightOOracleCorporation,1998.Allrightsreserved.

ClosingtheCursor

Syntax

CLOSEcursorename;

?Closethecursoraftercompletingthe

processingoftherows.

?Reopenthecursor,ifrequired.

?Donotattempttofetchdatafroma

cursoronceithasbeenclosed.

16-34CopyrightOOracleCorporation,1998.Allrightsreserved.

ExplicitCursorAttributes

Obtainstatusinformationaboutacursor.

AttributeTypeDescription

%ISOPENBooleanEvaluatestoTRUEifthecursor

isopen

%NOTFOUNDBooleanEvaluatestoTRUEifthemost

recentfetchdoesnotreturnarow

%FOUNDBooleanEvaluatestoTRUEifthemost

recentfetchreturnsarow;

complementof%NOTFOUND

%ROWCOUNTNumberEvaluatestothetotalnumberof

rowsreturnedsofar

16-35CopyrightOOracleCorporation,1998.Allrightsreserved.

ControllingMultipleFetches

?Processseveralrowsfromanexplicit

cursorusingaloop.

?Fetcharowwitheachiteration.

?Usethe%NOTFOUNDattributetowrite

atestforanunsuccessfulfetch.

?Useexplicitcursorattributestotestthe

successofeachfetch.

16-36CopyrightOOracleCorporation,1998.Allrightsreserved.

The%ISOPENAttribute

?Fetchrowsonlywhenthecursoris

open.

?Usethe%ISOPENcursorattribute

beforeperformingafetchtotest

whetherthecursorisopen.

Example

IFNOTcl%ISOPENTHEN

OPENcl;

ENDIF;

LOOP

FETCHcl...

16-37CopyrightOOracleCorporation,1998.Allrightsreserved.

The%NOTFOUNDand

%ROWCOUNTAttributes

?Usethe%ROWCOUNTcursorattribute

toretrieveanexactnumberofrows.

?Usethe%NOTFOUNDcursorattribute

todeterminewhentoexittheloop.

16-38CopyrightOOracleCorporation,1998.Allrightsreserved.

CursorsandRecords

Processtherowsoftheactiveset

convenientlybyfetchingvaluesintoa

PL/SQLRECORD.

Example

CURSORclIS

SELECTempno,ename

FROMemp;

emp_recordcl%ROWTYPE;

BEGIN-

OPENcl;

???

FETCHclINTOemp_record;

16-39Copyright6OracleCorporation,1998.Allrightsreserved.

CursorFORLoops

Syntax

FORrecord__nameINcursor_nameLOOP

statementl;

statement2;

???

ENDLOOP;

?Shortcuttoprocessexplicitcursors.

?Implicitopen,fetch,andcloseoccur.

?Donotdeclaretherecord;itis

implicitlydeclared.

16-40Copyright6OracleCorporation,1998.Allrightsreserved.

CursorFORLoops

Retrieveemployeesonebyoneuntilthere

arenomoreleft.

Example

DECLARE

CURSORclIS

SELECTempno,ename

FROMemp;

BEGIN

FORemp_recordINclLOOP

--implicitopenandimplicitfetchoccur

IFemp_record.empno=7839THEN

???

ENDLOOP;--implicitcloseoccurs

END;

16-41CopyrightOOracleCorporation,1998.Allrightsreserved.

CursorFORLoopsUsing

Subqueries

NoNeedtodeclarethecursor.

Example

BEGIN

FORemp_recordIN(SELECTempno,ename

一FROMemp)LOOP

--implicitopenandimplicitfetchoccur

IFemprecord.empno=7839THEN

ENDLOOP;--implicitcloseoccurs

END;

16-42CopyrightOOracleCorporation,1998.Allrightsreserved.

CursorswithParameters

Syntax

CURSORcursor_name

[(parameter_namedatatypef...)]

IS

select_statement;

?Passparametervaluestoacursorwhen

thecursorisopenedandthequeryis

executed.

?Openanexplicitcursorseveraltimes

withadifferentactiveseteachtime.

16-43Copyright6OracleCorporation,1998.Allrightsreserved.尺

CursorswithParameters

Passthedepartmentnumberandjobtitle

totheWHEREclause.

Example

DECLARE

CURSORcl

(v_deptnoNUMBER,v_jobVARCHAR2)IS

SELECTempno,ename

FROMemp

WHEREdeptno=v_deptno

ANDjob=v_job;

BEGIN

OPENcl(10,*CLERK1);

16-44CopyrightOOracleCorporation,1998.Allrightsreserved.

TheFORUPDATEClause

Syntax

SELECT

FROM

FORUPDATE[OFcolumn_reference][NOWAIT]

?Explicitlockingletsyoudenyaccessfor

thedurationofatransaction.

?Locktherowsbeforetheupdateor

delete.

16-45CopyrightOOracleCorporation,1998.Allrightsreserved.

Retrievetheordersforamountsover

$1000thatwereprocessedtoday.

Example

DECLARE

CURSORclIS

SELECTempno,ename

FROMemp

FORUPDATENOWAIT;

16-46CopyrightOOracleCorporation,1998.Allrightsreserved.

TheWHERECURRENTOF

Clause

Syntax

WHERECURRENTOFcursor

?Usecursorstoupdateordeletethe

currentrow.

?IncludetheFORUPDATEclauseinthe

cursorquerytolocktherowsfirst.

?UsetheWHERECURRENTOFclauseto

referencethecurrentrowfroman

explicitcursor.

16-47CopyrightOOracleCorporation,1998.Allrightsreserved.

TheWHERECURRENTOFClause

Example

DECLARE

CURSORclIS

SELECT

FORUPDATENOWAIT;

BEGIN

???

FORemp_recordINclLOOP

UPDATE

WHERECURRENTOFcl；

???

ENDLOOP；

COMMIT;

END;

16-48CopyrightOOracleCorporation,1998.Allrightsreserved.

CursorswithSubqueries

Example

DECLARE

CURSORmy_cursorIS

SELECTtl.deptno,dname,STAFF

FROMdepttl,(SELECTdeptno,

count(*)STAFF

FROMemp

GROUPBYdeptno)t2

WHEREtl.deptno=t2.deptno

ANDSTAFF>=5;

16-49CopyrightOOracleCorporation,1998.Allrightsreserved.

HandlingExceptions

16-50CopyrightOOracleCorporation,1998.Allrightsreserved.

HandlingExceptionswithPL/SQL

?Whatisanexception?

-IdentifierinPL/SQLthatisraisedduring

execution.

?Howisitraised?

一AnOracleerroroccurs.

-Youraiseitexplicitly.

?Howdoyouhandleit?

一Trapitwithahandler.

-Propagateittothecallingenvironment.

16-51Copyright6OracleCorporation,1998.Allrightsreserved.

HandlingExceptions

TraptheExceptionPropagatetheException

DECLAREDECLARE

BEGIN

ExceptionException

israisedisraised

EXCEPTIONEXCEPTION1

ExceptionExceptionis

istrappedEND;END;nottrapped

Exception

propagatestocalling

environment

16-52CopyrightOOracleCorporation,1998.Allrightsreserved.

ExceptionTypes

?PredefinedOracleServerImplicitly

?Non-predefinedOracleServer}raised

?User-definedExplicitlyraised

16-53CopyrightOOracleCorporation,1998.Allrightsreserved.

TrappingExceptions

Syntax

EXCEPTION

WHENexceptionl[ORexception2...]THEN

statementl;

statement2;

???

[WHENexceptions[ORexception4...]THEN

statementl;

statement2;

..J

[WHENOTHERSTHEN

statementl;

statement2;

?.J

16-54Copyright6OracleCorporation,1998.Allrightsreserved.

TrappingExceptionsGuidelines

?WHENOTHERSisthelastclause.

?EXCEPTIONkeywordstartsexception-

handlingsection.

?Severalexceptionhandlersareallowed.

?Onlyonehandlerisprocessedbefore

leavingtheblock.

16-55CopyrightOOracleCorporation,1998.Allrightsreserved.

TrappingPredefinedOracle

ServerErrors

?Referencethestandardnameinthe

exception-handlingroutine.

?Samplepredefinedexceptions:

-NO_DATA_FOUND

-TOO_MANY_ROWS

-INVALID_CURSOR

-ZERO.DIVIDE

-DUPVALONINDEX

16-56CopyrightOOracleCorporation,1998.Allrightsreserved.

PredefinedException

Syntax

BEGINSELECT…COMMIT;

EXCEPTION

WHENNO_DATAL_FOUNDTHEN

statementl;

statement2;_____

WHEN(TOOJMANY_ROWSTHEN

statementl;

WHENOTHERSTHEN

statementl;

statement2;

statements;

END;

16-57Copyright6OracleCorporation,1998.Allrightsreserved.尺

TrappingNon-PredefinedOracle

ServerErrors

Declare------Associate-----------Reference

DeclarativeSectionException-Handling

Section

?Namethe?CodethePRAGMA?Handlethe

exceptionEXCEPTIONJNITraised

exception

16-58CopyrightOOracleCorporation,1998.Allrightsreserved.

Non-PredefinedError

TrapforOracleServererrornumber

-2292anintegrityconstraintviolation

1

2

3

16-59CopyrightOOracleCorporation,1998.Allrightsreserved.

TrappingUser-Defined

Exceptions

DeclareRaise—*Reference

DeclarativeExecutableException-Handling

SectionSectionSection

?Namethe?Explicitlyraise?Handlethe

exceptiontheexceptionbyraised

usingtheRAISEexception

statement

16-60CopyrightOOracleCorporation,1998.Allrightsreserved.

User-DefinedException

Example

[DECLARE]

e_amount_remainingEXCEPTION;

???

BEGIN

???

RAISEe__amount_remaining;

???

EXCEPTION

WHENe_amount__remainingTHEN

:g_jnessage:='Thereisstillanamount

instock.,;

???

END;

16-61Copyright6OracleCorporation,1998.Allrightsreserved.

FunctionsforTrapping

Exceptions

?SQLCODE

Returnsthenumericvaluefortheerror

code

?SQLERRM

Returnsthemessageassociatedwiththe

errornumber

16-62CopyrightOOracleCorporation,1998.Allrightsreserved.

FunctionsforTrappingExceptions

Example

DECLARE

v_error_codeNUMBER;

v_error_jnessageVARCHAR2(255);

BEGIN

EXCEPTION

WHENOTHERSTHEN

ROLLBACK;

v_error__code:=SQLCODE;

v_error_message:=SQLERRM;

INSERTINTOerrorsVALUES(v_error_code,

v_error_message);

END;

16-63CopyrightOOracleCorporation,1998.Allrightsreserved.

CallingEnvironments

SQL*PlusDisplayserrornumberandmessage

toscreen

ProcedureDisplayserrornumberandmessage

Builder

toscreen

Accesseserrornumberandmessage

Developer/2000inatriggerbymeansofthe

FormsERROR_CODEandERROR_TEXT

packagedfunctions

PrecompilerAccessesexceptionnumberthrough

applicationtheSQLCAdatastructure

AnenclosingTrapsexceptioninexception-

PL/SQLblockhandlingroutineofenclosingblock

16-64CopyrightOOracleCorporation,1998.Allrightsreserved.

PropagatingExceptions

DECLARE

e_no_rowsexception;

e_integrityexception;

PRAGMAEXCEPTION_INIT(e_integrityz-2292);

BEGIN一

FORc_recordINemp_cursorLOOP

BEGIN

SELECT

SubblockscanhandleUPDATE

IFSQL%NOTFOUNDTHEN

anexceptionorpassRAISEe_no_rows;

ENDIF;—一

theexceptiontotheEXCEPTION

WHENe_integrityTHEN

enclosingblock.WHENe二no_rowsTHEN

END;

ENDLOOP;

EXCEPTION

WHENNO_DATA_FOUNDTHEN...

WHENTOO_MAN\_ROWSTHEN...

END;一

16-65Copyright6OracleCorporation,1998.Allrightsreserved.

RAISE_APPLICATION_ERROR

Syntax

raise_application_error(error_number,

message[,{TRUE|FALSE}]);

?Aprocedurethatletsyouissueuser-

definederrormessagesfromstored

subprograms.

?Calledonlyfromanexecutingstored

subprogram.

16-66Copyright6OracleCorporation,1998.Allrightsreserved.

RAISE_APPLICATION_ERROR

?Usedintwodifferentplaces:

一Executablesection

一Exceptionsection

?Returnserrorconditionstotheuserina

mannerconsistentwithotherOracle

Servererrors

16-67CopyrightOOracleCorporation,1998.Allrightsreserved.

SuccesswithMoneyandJoy

附熠人生心語

?成功是一種觀念

?致富是一種義務(wù)

?快樂是一種權(quán)利

?每個(gè)人都有能力、有義

務(wù)、有權(quán)利辦到成功

致富快樂

附贈(zèng)人生心語

成成功不是打敗別人

功成功不是超越別人

成功不是名、利、權(quán)的獲得

致?lián)碛薪】档纳眢w