IntroductiontoPL/SQL
IS620
AdvancedDatabaseProjects
1
Roadmap
n
n
n
MotivationforPL/SQL
Anonymousvs.namedPL/SQLsubprogram
BasicPL/SQLfeatures:
n
n
n
n
Basicstructure
VariablesandAssignment
Controlstructures(ifthen,loop)
UsingSQLinPL/SQL
n
n
n
Insert,update,delete
Implicitcursor(select)
Explicitcursor(select)
2
IsSQLPowerfulEnough?
n
DoyouneedtowritemultipleSQLstatementsforatask?
n
E.g., to transfer a certain amount from your checking
account to saving, only transfer when there is enough
balanceinchecking.
n
DoesSQLhavevariables,loops,ifthenelse?
n
HowcanothersusetheSQLstatementsyoudeveloped?
3
WhyPL/SQL
n
Real life example: E.g., a credit bureau provides a credit
scoringservice.
n
n
n
n
The company collects personal financial data from credit card
companies,banks,.,andstorethedatainadatabase
Customerscanaskthecreditscoreofsomeperson
Thescoreiscomputedfromthedatausingacomplexformula(a
topcommercialsecret)
CanwejustuseSQL?
What'smy
creditscore
700!
4
WhyPL/SQL
n
Solution1:embedSQLintoaprocedurelanguagesuchasJAVAorC
n
n
n
n
E.g.,JDBC,ODBC
ClientprogramsendsSQLtoDBserverandgetsbackintermediateresults
Clientprogramthencomputescreditscore
Problems?
JAVA
program
What'smy
creditscore
700!
SQL
Intermediate
results
5
WhyPL/SQL
n
n
OracleProcedureLanguage/SQL
n
SQL+procedurelanguagefeaturessuchasvariables,IFTHEN,loop,
functionorprocedure,etc.
n
AnamedPL/SQLprogramisstoredwithinthedatabase,andcanbeused
byotherusers
Benefits
n
n
n
n
Communicationcost
Portability
Security
Learningcurve
What'smycreditscore?CallaPL/SQLfunction
ondatabaseserver
700!
6
WhyPL/SQL
n
OracleProcedureLanguage/SQL
n
Downside?
n
n
WhichDMBStouse?
DoesPL/SQLprovideaGUI?
What'smycreditscore?CallaPL/SQLfunction
ondatabaseserver
700!
7
Roadmap
n
n
n
MotivationforPL/SQL
Anonymousvs.namedPL/SQLsubprogram
BasicPL/SQLfeatures:
n
n
n
n
Basicstructure
VariablesandAssignment
Controlstructures(ifthen,loop)
UsingSQLinPL/SQL
n
n
n
Insert,update,delete
Implicitcursor(select)
Explicitcursor(select)
8
AnonymousPL/SQLPrograms
n
n
n
n
n
Programhasnoname
CodeisNOTstoredindatabase(discardedafter
execution)
Codecanbestoredasascriptfileinlocalfile
system,butyouhavetomanuallysave&loadit.
Cannotacceptorpassparametervalues
Whendoyouwanttouseanonymousprogram?
9
NamedPL/SQLPrograms
n
Stored
n
n
n
n
n
Ascompiledobjectsindatabase(functions,
procedures)
Cantakeparametersasinput
Canbecalledbyotherprograms
Canbeexecutedbyotherusers
Whendoyouwanttousenamedprogram?
10
Roadmap
n
n
n
MotivationforPL/SQL
Anonymousvs.namedPL/SQLsubprogram
BasicPL/SQLfeatures:
n
n
n
n
Basicstructure
VariablesandAssignment
Controlstructures(ifthen,loop)
UsingSQLinPL/SQL
n
n
n
Insert,update,delete
Implicitcursor(select)
Explicitcursor(select)
11
AnonymousPL/SQLSubprogram
BasicStructure:
Declare
Declarationofvariables
Begin
statements
Exception
handleexceptions
End;
n Thesubprogramisunnamedandcanbeusedonlyonce
n Declarationisoptional
n Exceptionsectionisalsooptional
n Musthavebeginend;
n
12
ASimplePL/SQLExample
DECLARE
foovarchar(20);
BEGIN
foo:='Hello,world!';
dbms_output.put_line(foo);
END;
13
PL/SQLProgramLines
Mayspanmultipletexteditorlines
Eachlineendswithasemicolon
Textisnotcasesensitive
Note:quoteinPowerPointandWordmaynot
berecognizedinPL/SQL,usenotepad
14
CommentStatements
Blockofcommentsaredelimitedwith/**/
/* <comment that spans more than one line of
code> */
4
Singlecommentlinestartswith2hyphens
-- comment on a single line
15
FirstPL/SQLProgram
DECLARE
foovarchar(20);
BEGIN
foo:='Hello,world!';
dbms_output.put_line(foo);
END
16
DisplayingPL/SQLOutputin
SQL*Plus
NormallyPL/SQLisusedwithotherOracle
utilitiessuchasformsorreports
YouwilllearntousePL/SQLinSQL*Plus
CommandtoenableoutputfromPL/SQL
programs:
SET SERVEROUTPUT ON;
4
You need to turn on serveroutput every time
you log in
17
DisplayingPL/SQLOutputin
SQL*Plus
UsingDBMS_OUTPUTpackagetooutput
DBMS_OUTPUT.PUT_LINE(item);
Item can be either varchar or number, integer,
E.g.
Declare
greeting varchar(50) := 'Hello World';
x integer := 10;
begin
dbms_output.put_line('Hello World');
dbms_output.put_line(greeting);
dbms_output.put_line(x);
dbms_output.put_line(10);
End;
18
DisplayingPL/SQLOutputin
SQL*Plus
n
n
DBMS_OUTPUT.PUT(iteminvarcharor
number);
outputtheitemwithoutendofline
DBMS_OUTPUT.NEW_LINE;
outputanendofline
19
Exercises
WriteaPL/SQLprogramtoprint'Thisismyfirst
PL/SQLprogram'
20
Roadmap
n
n
n
MotivationforPL/SQL
Anonymousvs.namedPL/SQLsubprogram
BasicPL/SQLfeatures:
n
n
n
n
Basicstructure
VariablesandAssignment
Controlstructures(ifthen,loop)
UsingSQLinPL/SQL
n
n
n
Insert,update,delete
Implicitcursor(select)
Explicitcursor(select)
21
Variables
Typicallyusedtostoresomevalues
n
Stepsusingavariable
Declare
variable_namedata_type;
Begin
variable_name:=value;assignvaluetoavariable
usevariables(e.g.,inassignment,output,SQL,etc.)
dbms_output.put_line(variable_name);
End;
n
E.g.
Declarexnumber;
Begin
X:=10;
dbms_output.put_line('xis'||x);
End;
22
Variables
n
n
n
n
n
Namingconventions
Declarationandscope
Assignment
Datatypes
Assignmentofcompositedatatype
23
NamingConventions
VariablenamesmustfollowtheOraclenaming
standard:startwithaletter,followedbyletter,digit,or
_(space,notallowed)
n Arethefollowingnamesvalid?(separatedby
comma)
empname1,empname1,1emp_name,emp_name1,
empname1,
n
n
Makevariablenamesdescriptive,andseparate
wordswithunderscores
n
Example:current_s_id
24
Declaration
n
Syntaxfordeclaringavariable:
variable_namedata_type_declaration;
n
Example:current_s_idNUMBER;
25
Scope
Scope:therangewherethevariableisvalid
Validinthebeginendblockafteritisdeclared
n
E.g.,
Declarexnumber;
Begin
X:=10;
End;
xisnotvalidoutsidetheprogram
26
AssignmentStatements(Morelater)
Assignmentoperator::=
Variablebeingassignedtoanewvalueison
leftsideofassignmentoperator
Newvalueisonrightsideofoperator
student_name := 'John Miller';
student_name := current_student;
27
Exercise
n
Declareavariablegreeting,andgiveitvalue
'Thisismysecondpl/sqlprogram',andprint
thevariableonscreen.
28
PL/SQLDataTypes
n
n
AllSQLdatatypecanbeusedinPL/SQL
Scalar
n
n
Composite
n
n
Varray,record,table
Reference
n
n
Asinglevalue,e.g.,number,integer,varchar
Pointers:refcursor,refobject_type
LOB
n
largeobject:clob,blob,bfile
29
ScalarDataTypes
n
n
n
n
n
n
VARCHAR:variablelength.
E.g.,namevarchar(20)
CHAR:fixedlength.E.g.,zipchar(9)
INTEGER:e.g.,idinteger;
NUMBER:e.g.,balancenumber;
FLOAT,REAL:e.g.,amountfloat;
BOOLEAN:e.g.,validboolean:=TRUE;
30
Date&TimeTypes
DATE,Timestamp:sameasinSQL
Declare
start_datedate;
Checkouttimestamp;
Begin
n
start_date:=date'200525';
Checkout:=timestamp'2005020517:00:53.00';
End;
31
CommonErrors
n
n
Tooshortlengthforcharandvarchartype
Forgettoputdateandtimestampprefixfor
dateandtimestampvalues
32
SelectCurrentTime/Date
InPL/SQLprogram,youjustneedtousesystemvariable
systimestamp
E.g.,
declare
current_timetimestamp;
Current_datedate;
begin
current_time:=systimestamp;
Current_date:=sysdate;
dbms_output.put_line(current_time+interval'1'hour);
Dbms_output.put_line(current_date+interval'1'day);
end;
n
33
Exercise
n
Printoutcurrenttimeandoneweekfrom
currenttime
34
CompositeDataTypes
n
n
n
Referringtodatabasecolumnandrowtypes
RECORD(notrequired)
VARRAY,nestedtable,associatedarray(not
required)
35
ReferringtoColumnDataType
n
UsedatatypeofaDBcolumn
n
n
Tablename.columnname%TYPE:samedatatypeasthat
column
Examples
Declare
e_nameemp.ename%type;
Begin
e_name:='john';
Insertintoempvalues(5,e_name,2,date'200691',55000);
End;
n
Benefits?
36
Exercise
n
Createthefollowingtwotables:
createtabledept(
didint,
dnamevarchar(30),
primarykey(did)
);
createtableemp(
eidint,
enamevarchar(30),
didint,departmentid
hiredatedate,
salarynumber,
primarykey(eid),
foreignkey(did)referencesdept(did));
37
Exercise
Declareavariabled_nameoftypedept.dname,
andgiveitavalue'service',printthevariableon
screen
38
CommonErrors
n
n
Miss%betweencolumnnameandtype
Misstablename
39
RowTypeVariable
n
n
n
n
Tablename%ROWTYPE:assumesdatatypeof
entirerow,whereeachcolumnasafieldofthistype
Usevariable_name.column_nametorefertoafieldof
thevariable
OftenusedininsertorstoreresultsofaSQLquery
Benefits?
40
RowType
Examples
Declare
Rec1dept%rowtype;
Begin
n
Rec1.did:=3;
Rec1.dname:='service';
InsertintodeptvaluesRec1;
End;
41
Exercise
n
Declareavariablenew_emphavingtherow
typeofemp.Assignvalue'john'totheename
fieldofthevariable.Printouttheenamefield
ofthevariable
Declare
new_empemp%rowtype;
Begin
new_emp.ename:='jeff';
dbms_output.put_line(new_emp.ename);
End;
42
CommonErrors
Miss%betweentablenameandrowtype
n Miss.Betweenrowtypevariablenameand
columnname
n Dbms_output.put_linecannottakearowtype
input,soyoumustuse
row_type_variable.column_name
E.g.,dbms_output.put_line(new_emp.ename);
n
43
AssignmentStatements(cont.)
Forcompositetypes,onlyiftypesarethesame
Anyprobleminthefollowingprogram?
Declare
Rec1emp%rowtype;
Rec2dept%rowtype;
Begin
Rec1.eid:=6;
Rec1.ename:='Bob';
Rec1.did:=1;
Rec2:=Rec1;
End;
Notworkbecausecolumnisnotmatch.
44
ArithmeticOperators
Example
n
n
n
n
n
n
n
**Exponentiation2**38
*Multiplication2*36
/ Division 9/24.5
+Addition3+25
Subtraction 321
Negation 5 Negative5
Ln()NaturallogLn(10)
Result
45
Exercise
n
Printouttheresultof2**10
46
NULLValuesinAssignment
Statements
n
n
n
n
Untilavalueisassignedtoavariable,the
variable'svalueisNULL
Performinganarithmeticoperationona
NULLvaluealwaysresultsinaNULLvalue
dbms_output.put_linewilloutputnothingfora
nullvalue
Advice:Alwaysinitializevariablevalues
47
Example
n
Whatwillbescreenoutput?
Declare
xvarchar(20);
Begin
dbms_output.put_line(x);
x:='hello';
end;
48
Roadmap
n
n
n
MotivationforPL/SQL
Anonymousvs.namedPL/SQLsubprogram
BasicPL/SQLfeatures:
n
n
n
n
n
Basicstructure
Variables
Assignment
Controlstructures(ifthen,loop)
UsingSQLinPL/SQL
n
n
n
Insert,update,delete
Implicitcursor(select)
Explicitcursor(select)
49
PL/SQLControlStructures
n
n
Conditional:ifandcasestatements
Loopandexit
50
PL/SQLSelectionStructures
IF/ENDIF:
IF condition THEN
program statements
END IF;
51
PL/SQLSelectionStructures
Example:
Declare
Xdate;
Begin
x:=sysdate;
ifx>date'200691'then
dbms_output.put_line(x||'laterthan200691');
Else
dbms_output.put_line(x||'earlierthan200691');
Endif;
End;
52
PL/SQLSelectionStructures
IF/ELSIF:
IFcondition1THEN
programstatements;
ELSIFcondition2THEN
alternateprogramstatements;
ELSIFcondition3THEN
alternateprogramstatements;
...
ELSE
alternateprogramstatements;
ENDIF;
53
PL/SQLComparisonOperators
SameasSQL
=,<>,>=,<=,<,>
n Logicaloperators
ANDORNOT
E.g.,x>10andx<20
n Comparisonwithnullvalues:
Ifxisnullthen
Ifxisnotnullthen
Cannotuse:
Ifx=nullthen(later)
n
54
PL/SQLSelectionStructures
Exercise:useifelsif
Printout'TodayisinSeptember2006'iftodayisnolater
than200691andisearlierthan2006101,
Printout'todayisbeforeSept2006'iftodayisbefore2006
91
Printout'todayisafterSept.2006'iftodayisequaltoor
after2006101
55
CommonErrors
n
n
n
Spelling:elsif(correct)ratherthanelseif
Forgetendif
Forgettogivethevariableintheconditiona
value(next)
56
EvaluatingNULLConditionsin
IF/THENStructures
n
n
IfaconditionevaluatesasNULL,thenitis
FALSE
HowcanaconditionevaluateasNULL?
n
Itusesanyvariablethathasnotbeeninitialized
57
PL/SQLSelectionStructures
Whatwillbetheoutput?Ifaconditionevaluatesas
NULL,thenitisFALSE
Declare
Xinteger;
Begin
x:=10;
ifx>0then
dbms_output.put_line(x||'greaterthanzero');
Elsifx=0then
dbms_output.put_line(x||'equaltozero');
Else
dbms_output.put_line(x||'lessthanzero');
Endif;
End;
58
PL/SQLSelectionStructures
Howtocheckanullvalue?
Declare
Xinteger;
Begin
ifxisnullthen
dbms_output.put_line('nullinput');
elsifx>0then
dbms_output.put_line(x||'greaterthanzero');
Elsifx=0then
dbms_output.put_line(x||'equaltozero');
Else
dbms_output.put_line(x||'lessthanzero');
Endif;
End;
59
Roadmap
n
n
n
MotivationforPL/SQL
Anonymousvs.namedPL/SQLsubprogram
BasicPL/SQLfeatures:
n
n
n
n
n
Basicstructure
Variables
Assignment
Controlstructures(ifthen,loop)
UsingSQLinPL/SQL
n
n
n
Insert,update,delete
Implicitcursor(select)
Explicitcursor(select)
60
PL/SQLLoops
Loop:repeatsoneormoreprogram
statementsmultipletimesuntilanexit
conditionisreached
OftenneededwhenweprocessresultsofaSQL
query
61
PL/SQLLoops
Example:
Declare
Totalinteger;
iinteger;
Begin
i:=1;
total:=0;
loop
total:=total+i;
i:=i+1;
exitwheni>10;
Endloop;
dbms_output.put_line('totalis'||total);
End;
4
62
PL/SQLLoops
Declare
Totalinteger;
iinteger;
Begin
i:=1;initializationbeforeloop
total:=0;initializationbeforeloop
loop
total:=total+i;
i:=i+1;
exitwheni>10;
Endloop;
dbms_output.put_line('totalis'||total);
End;
Initializationbeforeloop:iisaloopvariable(usedtotellwhentoendloop),
totalistheresultwecompute.Bothneedtobeinitialized.
i=1becauseyouwanttostartwith1
total=0becauseyouarecomputingasum
63
PL/SQLLoops
Declare
Totalinteger;
iinteger;
Begin
i:=1;
total:=0;
loopstartofloop
total:=total+i;repeatedaction
i:=i+1;
exitwheni>10;
Endloop;endofloop
dbms_output.put_line('totalis'||total);postloopaction
End;
Repeatedactioniswhatyouwanttodousingtheloop(e.g.,computea
sum)
64
PL/SQLLoops
Declare
Totalinteger;
iinteger;
Begin
i:=1;
total:=0;
loopstartofloop
total:=total+i;
i:=i+1;updateloopvariableifany
exitwheni>10;
Endloop;endofloop
dbms_output.put_line('totalis'||total);postloopaction
End;
Youneedtoupdatetheloopvariable(counter)becauseotherwiseyouwill
haveinfiniteloop.
65
PL/SQLLoops
Declare
Totalinteger;
iinteger;
Begin
i:=1;
total:=0;
loopstartofloop
total:=total+i;
i:=i+1;
exitwheni>10;checkexitcondition
Endloop;endofloop
dbms_output.put_line('totalis'||total);postloopaction
End;
Youalsoneedtocheckexitconditionbecauseotherwiseitwillbean
infiniteloop.Hereyouwanttoendloopwheni>10becauseyouwantto
computeasumfrom1to10
66
PL/SQLLoops
Declare
Totalinteger;
iinteger;
Begin
i:=1;
total:=0;
loopstartofloop
total:=total+i;
exitwheni>=10;checkexitcondition
i:=i+1;
Endloop;endofloop
dbms_output.put_line('totalis'||total);postloopaction
End;
Theexitconditionmaychangedependingonwheretotestthecondition.
Thebestwaytomakeitrightisleti=boundaryvalue(10)andgothrough
thelooptocheckifyouwanttoexit.
67
Exercise
n
Computetheproductof1,2,3,4usingaloop
68
CommonErrors
n
n
Forgetinitializationbeforetheloop
Infiniteloop
n
n
n
Forgettoupdateloopvariable
Forgettoaddexitcondition
Incorrectexitcondition
n
n
Exitifvariableisoutofrange
Manualcheckofboundaryvaluescanfixthis
69
PL/SQLLoops
Anyprobleminthefollowingprogram?
Declare
Totalinteger;
Iinteger;
Begin
I:=1;
total:=0;
loop
total:=total+I;
exitwhenI>10;
Endloop;
dbms_output.put_line('totalis'||total);
End;
4
70
NumericFORLoop(Optional)
FORcounter_variable
IN[reverse]start_value..end_value
LOOP
programstatements
ENDLOOP;
n
n
n
n
n
n
Noneedtodeclarecounter_variable.Ifitisnotdeclared,itisonlyvalid
inloop(cannotuseitbeforeorafterloop)
Cannotmodifycounter_variableinsidetheloop
Start_valuealwayslowerthanend_value
Usereverseifyouwanttostartfromend_value
Thelooprepeatedend_valuestart_value+1times
Usingexitwhenstatementwhenyouwanttojumpoutofforloop
71
NumericFORLoop(Optional)
Declare
totalinteger:=0;
begin
FORiIN1..10LOOP
total:=total+i;
ENDLOOP;
dbms_output.put_line(total);
End;
72
Exercise(Optional)
n
Computetheproductof1,2,3,4usingforloop
73
Roadmap
n
n
n
MotivationforPL/SQL
Anonymousvs.namedPL/SQLsubprogram
BasicPL/SQLfeatures:
n
n
n
n
n
Basicstructure
Variables
Assignment
Controlstructures(ifthen,loop)
UsingSQLinPL/SQL
n
n
n
Insert,update,delete
Implicitcursor(select)
Explicitcursor(select)
74
UsingSQLCommandsinPL/SQL
Programs
n
Candirectlyuseinsert,update,delete,
commit,rollback
75
UsingSQLCommandsinPL/SQL
Programs
n
Canuseselectthroughcursors
n
n
n
n
Needawaytostoreresultsofselecttolocal
variables
Cursorisavariablepointingtoaresultrow
Aloopisoftenneededifresultscontainmultiple
rows
CanuseDataDefinitionstatementsthrough
dynamicSQL(later)
n
DDLincludingcreate,drop,alter,grant,revoke
76
UsingInsert,Update,Delete
n
CanusethemasinSQL,i.e.,notreferringto
anyvariables
Begin
insertintoemp
values(5,'alice',1,date'200691',45000);
End;
77
UsingInsert,Update,Delete
n
Canrefertoanyinitializedvariableswhereavalueis
expected
Declare
Dept_idnumber;
Begin
dept_id:=2;
updateemp
Setdid=dept_id
Whereeid=4;
End;
78
InsertUsingRowtypeVariable
declare
xemp%rowtype;
Begin
x.eid:=5;
x.ename:='alice';
x.did:=1;
x.hiredate:=date'200691';
x.salary:=45000;
insertintoempvaluesx;
end;
79
Exercise
n
Insertanewdepartmentwithdid=4and
dname='accounting',pleaseusearowtype
variableinyourinsertstatement
80
CommonErrors
n
Insert:thecolumnvaluesandordersmust
matchcolumntypesandcolumnorders
n
Fordatetype,don'tforgetdateprefix
81
UsingSelect
n
n
Mustuseacursortorefertoresults
Followingexamplewillgetanerror
Begin
selecteid,ename
fromemp;
End;
82
Cursor
n
n
Pointertoaservermemorylocation
Containsinformationabouttheresultsofa
SQLcommandinaPL/SQLprogram
n
Calledthecommand'scontextarea
Cursor
Numberof
rows
processed
active set
CID
1
2
3
4
5
CALLID
MIS 101
MIS 301
MIS 441
CS 155
MIS 451
Parsed
command
statement
CNAME
Intro. to Info. Systems
Systems Analysis
Database Management
Programming in C++
Client/Server Systems
CCREDIT
3
3
3
3
3
83
TypesofCursors
Implicit
Easiertouse
ResultsmustbeEXACTLYonerow
Explicit
Morecomplextouse
Resultscanbeonerow,multiplerows,oreven
empty
Cursorcannotappearinassignmentor
expressions
84
ImplicitCursors
SELECT column1, column2,
INTO variable1, variable2,
FROM tablename
WHERE search_condition;
Canonlybeusedifqueryreturnsoneandonlyone
record
Variablesmusthavebeendeclaredandhavethe
sametypeasresults
Forstrings,makesurethevariable'ssizeislong
enoughtostoretheresults(that'swhycolumntypeis
useful)
4
85
ImplicitCursorSyntax(cont.)
Declare
E_idnumber;
E_namevarchar(50);
Begin
selecteid,enameintoe_id,e_name
fromemp
Whereeid=1;
Dbms_output.put_line('e_idis:'||e_id||'e_nameis:'||
e_name);
end;
86
Exercise
n
Useimplicitcursortoreturnthesalaryof
employeewhoseidis1,printoutthesalary
87
CommonErrors
n
n
n
n
SQLstatementdoesnotreturn1row
Forgettodefinevariableusedtostoretheresult
Thevariable'sdatatypedoesnotmatchtheresult
(userowtypeorcolumntypewillavoidthisproblem)
SQLstatementerror(usecorrecttablename,
columnname,donotconfusewithvariables).You
cantestyourSQLstatementfirst
88
ConditiononPrimaryKey
Declare
E_idnumber;
E_namevarchar(50);
Begin
selecteid,enameintoe_id,e_name
fromemp
Whereeid=1;
Dbms_output.put_line('e_idis:'||e_id||'e_nameis:'||e_name);
end;
n
Whytheselectstatementonlyreturnsonerow?
89
NoRowsReturned
Declare
E_idnumber;
E_namevarchar(50);
Begin
selecteid,enameintoe_id,e_name
fromemp
Whereeid=10;
Dbms_output.put_line('e_idis:'||e_id||'e_nameis:'||
e_name);
end;
90
NoRowsReturned
Declare
E_idnumber;
E_namevarchar(50);
Begin
selecteid,enameintoe_id,e_name
fromemp
Whereeid=10;
Dbms_output.put_line('e_idis:'||e_id||'e_nameis:'||
e_name);
end;
Anexception(nodatafound)willbereported,solution?
91
Exception
Declare
E_idnumber;
E_namevarchar(50);
Begin
selecteid,enameintoe_id,e_name
fromemp
Whereeid=10;
Dbms_output.put_line('e_idis:'||e_id||'e_nameis:'||e_name);
Exception
whenno_data_foundthen
Dbms_output.put_line('norowsfound');
whentoo_many_rowsthen
dbms_output.put_line('toomanyrows');
End;
92
ExceptionSyntax
Exception
Whenexception_namethen
handleexception
Whenexception_namethen
handleexception
whenothersthen
handallotherexception
n
93
ExceptionsforImplicitCursor
n
n
No_data_found(caseinsensitive)
Too_many_rows
94
Exercise
n
Useimplicitcursortoreturnthesalaryof
employeewitheid=5.Pleasehandle
exceptions
95
Aggregations(Optional)
n
Exceptions?
Declare
avg_salnumber;
Begin
4;
selectavg(salary)intoavg_salfromempwheredid=
dbms_output.put_line(avg_sal);
End;
96
Aggregations(Optional)
Incaseemptyanswers,checkifresultisnull
Declare
n
avg_salnumber;
Begin
selectavg(salary)intoavg_salfromempwheredid=4;
ifavg_salisnotnullthen
dbms_output.put_line(avg_sal);
else
dbms_output.put_line('Nosuchemployees');
endif;
End;
97
TipsforImplicitCursor
n
Useitonlyfortwocases:
n
n
n
Whereconditiononprimarykey(zerooronerows)
Aggregationswithoutgroupby(1row)
Otherwiseuseexplicitcursor(next)
98
Roadmap
n
n
n
MotivationforPL/SQL
Anonymousvs.namedPL/SQLsubprogram
BasicPL/SQLfeatures:
n
n
n
n
Basicstructure
VariablesandAssignment
Controlstructures(ifthen,loop)
UsingSQLinPL/SQL
n
n
n
Insert,update,delete
Implicitcursor(select)
Explicitcursor(select)
99
ExplicitCursors
MustbedeclaredinprogramDECLAREsection
Canbeusedifqueryreturnsmultiplerecordsor
norecords
100
UsinganExplicitCursor
Declarethecursor
Openthecursor
FetchthecursorresultintoPL/SQL
programvariablesusingaloop
Makesurecheckexitconditioninloop
(whatconditiontocheck?)
Closethecursor
101
Template
Declare
Cursor cursor_name is SQL-statements;
Variable-definitions;
Begin
Open cursor_name;
loop
FETCH cursor_name INTO
variable_name(s);
exit when cursor_name%notfound;
-- handle results, e.g., print them out
End loop;
Close cursor_name;
102
FetchingExplicitCursorRecords
Declare
Cursorc1isSelectename,dnameFromemp,deptwheredept.did=
emp.did;cursordefinition
e_nameemp.ename%type;variablestoholdresults
e_deptdept.dname%type;variablestoholdresults
Begin
Openc1;opencursor
Loop
Fetchc1intoe_name,e_dept;fetch
Exitwhenc1%NOTFOUND;exitcheck
Dbms_output.put_line('name='||e_name|| ',dept='||e_dept);
ENDLOOP;
Closec1;closecursor
END;
103
Exercise
n
Useanexplicitcursortoprintoutnamesof
employeesworkingatITdepartment
104
CommonErrors
n
Cursordefinitionerror:makesureyoutryoutthe
SQLstatementbeforedefiningthecursor
n
n
n
n
n
n
n
DoNOTusevariablenamesthatarethesameastableor
columnnames
Forgettocheckexitcondition(neverdothis!)
Forgettoopencursor
Forgettofetchcursorintheloopbody
Forgetusingaloop
Forgettoclosecursor
Wrongorderintheloop(mustbefetch,exittest,
processresults)
105
Error?
Declare
Cursorc1isSelectename,dnameFromemp,deptwheredept.did=
emp.did;cursordefinition
e_nameemp.ename%type;variablestoholdresults
e_deptdept.dname%type;variablestoholdresults
begin
openc1;opencursor
loop
fetchc1intoe_name,e_dept;fetch
dbms_output.put_line('name='||e_name|| ',dept='||e_dept);
print
ENDLOOP;
Closec1;closecursor
END;
106
Error?
Mustcheckexitcondition,otherwiseyouwillgetaninfiniteloop
Declare
Cursorc1isSelectename,dnameFromemp,deptwheredept.did=
emp.did;cursordefinition
e_nameemp.ename%type;variablestoholdresults
e_deptdept.dname%type;variablestoholdresults
begin
openc1;opencursor
loop
fetchc1intoe_name,e_dept;fetch
dbms_output.put_line('name='||e_name|| ',dept='||e_dept);
print
exitwhenc1%notfound;
ENDLOOP;
Closec1;closecursor
END;
n
107
Whatifcheckconditionafterprint?
Declare
Cursorc1isSelectename,dnameFromemp,deptwheredept.did=
emp.did;cursordefinition
e_nameemp.ename%type;variablestoholdresults
e_deptdept.dname%type;variablestoholdresults
begin
openc1;opencursor
loop
fetchc1intoe_name,e_dept;fetch
dbms_output.put_line('name='||e_name|| ',dept='||e_dept);
print
exitwhenc1%NOTFOUND;exitcheck
ENDLOOP;
Closec1;closecursor
END;
108
Whatifcheckconditionafterprint?
loop
fetchc1intoe_name,e_dept;fetch
dbms_output.put_line('name='||e_name|| ',dept='||e_dept);
print
exitwhenc1%NOTFOUND;exitcheck
ENDLOOP;
Errorbecauseweshouldprintonlyifexitconditionisnot
satisfied
Soweprintonemoretimewhentheexitconditionissatisfied
n
n
Similarly,fetchshouldbeplacedbeforeexitbecauseonly
fetchwillupdatecursorstatus(notfoundornot),andwecan
notcheckcursorstatusbeforeitisupdated
109
Sticktotheorderoffetch,exit,print
Declare
Cursor cursor_name is SQL-statements;
Variable-definitions;
Begin
Open cursor_name
loop
FETCH cursor_name INTO
variable_name(s);
exit when cursor_name%notfound;
-- handle results, e.g., print them out
End loop;
Close cursor_name;
110
UseRowtypeVariable
Declare
Cursor cursor_name is SQL-statements;
Variable-name cursor_name%rowtype;
Begin
Open cursor_name
loop
FETCH cursor_name INTO variable_name;
exit when cursor_name%notfound;
-- handle results, e.g., print them out
End loop;
Close cursor_name;
n The row type variable will have columns in the
select clause of SQL
n You can use variable-name.column-name after
fetch
111
UseRowtypeVariable
Declare
Cursorc1isSelectename,dnameFromemp,deptwheredept.did=
emp.did;cursordefinition
rc1%rowtype;variablehasthesametypeasarowreturnedbythecursor
begin
openc1;opencursor
loop
fetchc1intor;fetch
exitwhenc1%NOTFOUND;exitcheck
dbms_output.put_line('name='||r.ename|| ',dept='||r.dname);
ENDLOOP;
Closec1;closecursor
END;
112
TipsforEfficientPL/SQL
n
n
Trytominimizetheamountofprocessing
doneinPL/SQL,pushmostprocessingto
SQL
DonotissuemanySQLstatements
(overheadofparsingsql,allocatememoryfor
cursor,passingresultsback,etc.)
113
PL/SQLjustprintoutresults
Declare
Cursorc1isSelectename,dnameFromemp,deptwheredept.did=
emp.did;cursordefinition
e_nameemp.ename%type;variablestoholdresults
e_deptdept.dname%type;variablestoholdresults
begin
openc1;opencursor
loop
fetchc1intoe_name,e_dept;fetch
exitwhenc1%NOTFOUND;exitcheck
dbms_output.put_line('name='||e_name|| ',dept='||e_dept);
print
ENDLOOP;
Closec1;closecursor
END;
114
InefficientProgram
Declare
Cursorc1isSelectename,didFromemp;
cursorc2isselectdname,didfromdept;
e_nameemp.ename%type;variablestoholdresults
did1emp.did%type;
did2dept.did%type;
e_deptdept.dname%type;variablestoholdresults
begin
openc1;opencursor
loop
fetchc1intoe_name,did1;fetch
exitwhenc1%NOTFOUND;exitcheck
openc2;
loop
fetchc2intoe_dept,did2;fetch
exitwhenc2%NOTFOUND;exitcheck
ifdid1=did2then
dbms_output.put_line('name='||e_name|| ',dept='||e_dept);
endif;
ENDLOOP;
closec2;
Endloop;
115
Closec1;closecursor
InefficientProgram
n
n
Theaboveexampletriestodothejoinin
PL/SQL
Reallyinefficientbecause
n
n
n
Cursor2isexecutedonceforeachrowreturnedby
cursor1(manysqlstatementsexecuted)
Oraclehasmuchbetterwaystocomputeajoin
(usingindex,hashtable,etc.)
Don'texpectnestedloopsinvolvingcursors
(youcanalwaysdoajoin)
116
Cursorwithinputparameter(Optional)
Whendeclarecursor
cursorname(inputparamenterparametertype)
isSQLstatement
Whenopencursor
Opencursorname(inputparamenter)
n TheSQLstatementcanusetheinput
parameter
117
Cursorwithinputparameter(Optional)
highestsalaryemp
Declare
e_nameemp.ename%type;
msalemp.salary%type;
cursorc1(msalnumber)isselectenamefromempwheresalary=
msal;
begin
selectmax(salary)intomsalfromemp;
if(msalisnotnull)then
openc1(msal);
loop
fetchc1intoe_name;
exitwhenc1%NOTFOUND;
dbms_output.put_line('name='||e_name);
ENDLOOP;
Closec1;
endif;
END;
118
ForLoop(Optional)
CursorFORLoopapproach:
Declare
cursorcursor_nameisselectstatement
begin
FORrowtypevariableincursor_nameLOOP
processingstatements;
ENDLOOP;
End;
n
Donotopen/close/fetchcursor(getopened/closed/fetched
automatically)
n
Noneedtodeclarethevariables.Arowtypevariableis
createdautomatically
n
Youcanstilladdexitconditiontoendloopearlier
n
119
ForLoop(Optional)
CursorFORLoopapproach:
Declare
Cursorc1isSelectename,dnameFromemp,deptwhereemp.did
=dept.did;
begin
n
foriteminc1
loop
dbms_output.put_line('name='||item.ename||',dept='||
item.dname);
ENDLOOP;
END;
120
ExplicitCursorAttributes
Attribute
Return Value
%NOTFOUND
TRUE when no rows left to
fetch; FALSE when rows left
to fetch
TRUE when rows left to
fetch; FALSE when no rows
left to fetch
Number of rows a cursor has
fetched so far
TRUE if cursor is open and
FALSE is cursor is closed
%FOUND
%ROWCOUNT
%ISOPEN
Note that notfound, found, and rowcount are only valid after the
cursor is open and result has been fetched at least once
These variables may change values after each fetch
121
UseRowcounttolimitresultsize
Returntwoemployeeswithhighestsalary
Declare
Cursorc1isSelectenamefromemporderbysalarydesc;
e_nameemp.ename%type;
begin
openc1;
loop
fetchc1intoe_name;
exitwhenc1%notfoundorc1%rowcount>2;
dbms_output.put_line('name='||e_name);
ENDLOOP;
Closec1;
END;
122
CommonError
Returntwoemployeeswithhighestsalary
Declare
Cursorc1isSelectenamefromemporderbysalarydesc;
e_nameemp.ename%type;
begin
openc1;
loop
fetchc1intoe_name;
exitwhenc1%rowcount>2;
dbms_output.put_line('name='||e_name);
ENDLOOP;
Closec1;
END;
Whatswrongwiththeexitcheck?
123