AdvancedPL/SQL
Programming
IS620
AdvancedDatabase
Projects
1
Roadmap
n
n
n
n
n
n
Procedures
HowtodebugPL/SQLprogram
Functions
DynamicSQL
HowtoAutomaticallyGenerateValues
DuringInsert
VArrayDataType
2
NamedProgramTypes
n
n
n
n
Procedures
Functions(proceduresthatreturna
value)
Packages(setofprocedures&
functions,notcovered)
Triggers(later)
3
ProgramUnits
n
Procedures
n
n
n
Canreceiveandpassmultipleparameter
values
Cancallotherprogramunits
Functions
n
Likeprocedures,excepttheyreturna
singlevalue
4
GeneralSteps
n
n
Createthefunctionorprocedureusingcreate
functionorcreateprocedurecommand
Oraclewillcompileandstoretheprocedure
n
n
n
n
Incaseofcompilationerror,type
showerrors
Youwillseetheline/columnoferrorand
descriptionoferror
Trytofixerrors
Otherwise,youwillseeprocedurecreated
message
5
ShowErrors
Supposeyouhavecreatedaprocedurelike
createorreplaceprocedureHelloWorld(svarchar)
as
begin
dbms_output.putline(s);
end;
n Nowyoutype(mustdoitafteryouclickexecute)
showerrors;
n
6
ShowErrors
Results:
LINE/COL ERROR
4/3 PL/SQL:Statementignored
4/15 PLS00302:component'PUTLINE'mustbe
declared
n Meaning:
ErroroccursonLine4
Putlineisnotrecognized
Youcanalsogoogleerrornumber:PLS00302to
someidea
n
7
GeneralSteps
n
Executethefunctionorprocedurewithpassed
parameter
n
n
n
ExecuteinSQL:
execprocedurename(parametervalues);
ExecuteinPL/SQL(inanonymousprogramoranother
procedurefunction):
procedurename(parametervalues);
Inthewebinterface,youmustclickexecutebutton
aftercreatingtheprocedureandbeforeexecutingthe
procedure.Don'tcreateandexecuteatonetime.
8
GeneralSteps
Toseethenamesofprocedures
Select*fromuser_procedures;
n
Toseethecode
SELECT*FROMUSER_SOURCEWHERE
NAME='PROCEDURENAME';
9
GeneralStepsUsingSQLDeveloper
n
n
n
n
n
n
Thesamewayaswebinterfaceorclickprocedures,
rightclick,newprocedure
Enterprocedurename&parameters
ClickSaveIcon,itwillgetcompiled.
Theerrormessageisshownatthebottomofthe
screen(compilerlog)
Rightclickgiveoptionssuchascompile,run,drop
Watchforinvisiblestrangecharactersifyoucopy
codefromwordorpowerpoint(usenotepadorother
texteditorwillbebetter)
10
GeneralStepsUsingSQLDeveloper
n
n
n
n
Whenexecuteprocedure,youcandothesamething
asWebInterface(typeinSQLwindow)
Oryoucanrightclickontheprocedure,selectrun
Awindowwillpopup,withsomePL/SQLcodetocall
theprocedure
Edittheprogramtoenterparametervalues(usually
throughassignment)
11
BenefitsofUsingSQLDeveloper
n
n
n
Syntaxreminderwheneditingprocedure
Candebugyourprocedure(settingbreakpoint,check
variablevalues,etc.)
Recommendedforlargeprojects
12
Example
Example:
createorreplaceprocedureHelloWorld(svarchar)
as
begin
dbms_output.put_line(s);
end;
clickexecutebuttonnow
execHelloWorld('Hello');
Sisaparameter
Youcannotspecifysizesforparametertype(sinceoracledoes
notallow()withinparametertype)
13
CreatingaProcedure
CREATE OR REPLACE PROCEDURE procedure_name
(parameter1 mode datatype,
parameter2 mode datatype,
)
IS | AS
local variable declarations
BEGIN
program statements
EXCEPTION
body
exception handlers
END;
Differenceswithanonymoussubprograms?
14
header
Differences
n
n
n
Theprocedureheadercontains
procedurenameandparameters
UseISorAstoseparateheaderand
localvariabledeclaration(nodeclare)
Canuseparametersinthebody(justas
initializedvariables)
15
Parameters
Variablesusedtopassdatavaluesin/outof
programunits
Declaredintheprocedureheader
Parametervaluesarepassedwhenthe
procedureiscalledfromthecallingprogram
Parametersareusedjustasvariablesinthe
bodyofprocedure/function
16
ParameterModes
n
IN
n
n
n
OUT
n
n
n
Incomingvalues,readonly
Defaultmodein
Outgoingvalues,writeonly
Mustbegivenavalueintheprocedure/function
body(throughassignmentorcursor).
INOUT
n
Readandwrite
17
Exercise
n
n
CreateaprocedurePrintMessagewith
aparametermasmessage
Executetheprocedurewithparameter
value'thisismyfirstPL/SQLprocedure'
18
ExampleforNoParameter
createorreplacePROCEDUREraise_salaryIS
BEGIN
UPDATEempSETsalary=salary*1.1;
END;
Execraise_salary;
Notethatyoudon'tneedtoput()ifthereisno
parameter.
19
ExampleforOutputParameter
CreateorreplacePROCEDUREraise_salary(emp_idinINTEGER,
amountinREAL,current_saloutreal)IS
BEGIN
UPDATEempSETsalary=salary+amountWHEREeid=
emp_id;
Selectsalaryintocurrent_salfromempwhereeid=emp_id;
exception
whenno_data_foundthen
dbms_output.put_line('nosuchemployee');
end;
20
ExampleforOutputParameter
Declare
C_salreal;
Begin
Raise_salary(1,1000,C_sal);
Dbms_output.put_line('newsalaryis:'||C_sal);
End;
21
UseParameterinCursor
Createorreplace
PROCEDUREget_salary(emp_nameinvarchar)IS
Cursorc1isselectsalaryfromempwhereename=emp_name;
E_salnumber;
BEGIN
Openc1;
Loop
fetchc1intoe_sal;
exitwhenc1%notfound;
dbms_output.put_line(e_sal);
Endloop;
END;
Execget_salary('jeff');
22
UseParameterinCursor
Anybugintheprocedurebelow?
Createorreplace
PROCEDUREget_salary(enameinvarchar)IS
Cursorc1isselectsalaryfromempwhereename=ename;
E_salnumber;
BEGIN
Openc1;
Loop
fetchc1intoe_sal;
exitwhenc1%notfound;
dbms_output.put_line(e_sal);
Endloop;
END;
Execget_salary('jeff');
23
Exercise
PleaseusetheEMPtable.
Createaprocedureget_salarywithan
inputparametere_id,andprintthesalary
oftheemployeewiththatid
Thenexecutethisprocedurewithe_id=1
ande_id=2
24
Tips
n
n
Usecreateorreplaceprocedure,sononeedto
droppreviousprocedurewiththesamename
Whencalltheprocedure,parametervaluesmust
matchtheorderofhowparametersaredefinedin
procedureheader
25
Tips
n
n
Whencalltheprocedure,addexecif
youareusingSQLdirectly,noexecif
usePL/SQL
Iftheprocedurehasanoutput
parameter,youneedtowriteaPL/SQL
programtocalltheprocedureand
declaretheoutputparameter(no
variableinSQL)
26
Exercise
Createaprocedureget_salarywithaninput
parametere_id,andanoutputparametersal,
whichreturnsthesalaryoftheemployeewith
thate_id
ThenwriteananonymousPL/SQLprogramto
executethisprocedurewithe_id=1,andprint
outtheoutputparametersal
27
Roadmap
n
n
n
n
n
n
Procedures
HowtodebugPL/SQLprogram
Functions
DynamicSQL
HowtoAutomaticallyGenerateValues
DuringInsert
VArrayDataType
28
WebInterface
Aftercreation,checkifthereiserror
messageby:
Showerrors;
n Calltheprocedureseveraltimeswith
differentinputparameters
n UseDBMS_output.put_linetooutput
resultsintheprocedure
n
29
DebuggingPL/SQLPrograms
Syntaxerror
Doesnotfollowlanguageguidelines
Causesacompileerror
Examples:misspellingareservedword,
usingafunctionincorrectly
Logicalerror
Programcompilescorrectly,butdoesnot
givecorrectoutput
30
LocatingandCorrecting
SyntaxErrors(WebInterface)
Complierusuallyreportsthelineoferror
Isolatethelinethatiscausingtheerror
Thismaybebeforeorafterthelinethatisflaggedby
thecompiler
Commentoutlinesasnecessaryuntilprogram
runs
Oneerrormaycauseseveralcascadingerrors,
sorerunprogramafterfixingeacherror
31
DebugUsingSQLDeveloper
n
n
n
n
n
Setabreakpointatthelineyouwantto
stop
Startdebugging
Cancheckvaluesofprogramvariables
Canalsostepthroughtheprogram(line
bylineorcontinuetonextbreakpoint)
PleaserefertotheSQLDeveloper
guideatblackboard
32
Roadmap
n
n
n
n
n
n
Procedures
HowtodebugPL/SQLprogram
Functions
DynamicSQL
HowtoAutomaticallyGenerateValues
DuringInsert
VArrayDataType
33
Function
n
n
Sameasprocedureexceptthata
functionreturnsasinglevalue
Mostlyforconvenience
34
CreatingaFunction
CREATE OR REPLACE FUNCTION function_name
(parameter1 mode datatype,
parameter2 mode datatype,
)
RETURN function_return_data_type
IS local variable declarations
BEGIN
program statements
body
RETURN return_value;
EXCEPTION
exception handlers
RETURN return_value;
35
END;
header
DifferenceswithProcedures
n
n
n
Returndatatypeinheader
Areturnstatementinbody
Areturnstatementineachexception
36
FunctionSyntaxDetails
RETURNcommandinheaderspecifiesdata
typeofvaluethefunctionwillreturn
(noneedtospecifyavariablename)
n RETURNcommandinbodyspecifiesactual
valuereturnedbyfunction
n Incaseofexception,returnavaluenotinthe
validrangetoindicateexception
E.g.,return1whenvalidreturnvaluesare
positive
n
37
CallingaFunction
n
Canbecalledfromeithernamedor
anonymousPL/SQLprogram
(mustuseavariabletostorereturned
value,somustdoitinPL/SQL)
return_value :=
function_name(parameter1_value,
parameter2_value, );
38
ExampleofCallingaFunctionin
Anonymousprogram
Createorreplacefunctionreturn_salary(emp_idinnumber)
returnnumber
IS
e_salnumber;
BEGIN
selectsalaryintoe_salfromempwhereeid=emp_id;
returne_sal;
exception
whenno_data_foundthen
dbms_output.put_line('nosuchemployee');
return1;
End;
39
ExampleofCallingaFunctionin
Anonymousprogram
declare
E_salnumber;
Begin
e_sal:=return_salary(2);
ife_sal>0then dbms_output.put_line(e_sal);
else dbms_output.put_line('nodatafound');
endif;
End;
40
ExampleofCallingFunctioninA
Procedure
Createorreplaceprocedureraise_salary(emp_idinnumber,amountin
number)IS
E_salnumber;
Begin
e_sal:=return_salary(emp_id);
ife_sal>0then
dbms_output.put_line('currentsal:'||e_sal);
emp_id;
updateempsetsalary=salary+amountwhereeid=
dbms_output.put_line('salincreasedby:'||amount);
else
dbms_output.put_line('nosuchemployeefound');
endif;
End;
41
ExampleofCallingFunctioninA
Procedure
execraise_salary(1,100);
execraise_salary(1,100);
42
Exercise
Createafunctionget_salary_bynamewithan
inputparametere_nameandreturnsthesalary
oftheemployeewithe_name
ThenwriteananonymousPL/SQLprogramto
executethisprocedurewithe_name='jeff',and
printoutthesalary
43
Roadmap
n
n
n
n
n
n
Procedures
HowtodebugPL/SQLprogram
Functions
DynamicSQL
HowtoAutomaticallyGenerateValues
DuringInsert
VArrayDataType
44
DynamicSQL
n
n
AllowsyoutocreateSQLcommandsat
runtime
AllowsDDLstatements(create,drop,
grant,revoke)
45
HowtoUseDynamicSQL
n
DDL(noreturnresults)
n
n
Executeimmediateusing
DML(select)
n
Usingcursor,notrequired
46
ExecuteImmediate
n
n
Executeimmediatesqlstring;
EXECUTEIMMEDIATE'CREATE
TABLEbonus(idNUMBER,amt
NUMBER)';
47
Roadmap
n
n
n
n
n
n
Procedures
HowtodebugPL/SQLprogram
Functions
DynamicSQL
HowtoAutomaticallyGenerateValues
DuringInsert
VArrayDataType
48
HowtoAutomaticallyGenerate
ValuesDuringInsert
n
GeneratePrimarykeyvalues
n
n
E.g.,eidofnewemployeeshallnotequal
toanyexistingones,butthereisalready
datainthetable
Solution1:selectthemaximaleidfrom
table,thenewid=1+maxeid
n
Problem?
49
HowtoAutomaticallyGenerate
ValuesDuringInsert
n
UsingOracleSequence
FirstcreateasequenceinSQL(cannotdoitin
PL/SQL)
Createsequencesequence_namestartwithmin_val
[incrementbyincrement_val];
n
Themin_valshallbelargerthananyexistingprimary
keyifany
n
Referencetothenextvalofsequencein
insert
50
HowtoAutomaticallyGenerate
ValuesDuringInsert
dropsequenceeid_seq;
supposethereare4rowsinemp
CREATESEQUENCEeid_seqSTARTWITH7;
insertintoempvalues(eid_seq.nextval,'jefferson',2,sysdate,
50000);
Eachreferencetoeid_seq.nextvalwillincreasethevalueby
1(i.e.,1,2,)
Youcangetthecurrentsequencevaluebyeid_seq.currval
(whentousethis?)
51
HowtoAutomaticallyGenerate
ValuesDuringInsert
n
n
Generatecurrentdate:sysdate
Generatecurrenttime:SYSTIMESTAMP
52
Exercise
n
n
Createasequencedid_seqfordidin
Depttable,setitsstartvaluebethenext
did(youcanlookitupmanually)
Insertarowintodepttablewith
automaticallygenerateddid,anddname
='testing';
53
UsingSequenceinPL/SQL
SequencecannotbecreatedinPL/SQL(mustbe
createdinSQL)
n Cannotuseassignmenttogetvalueofsequence
(needtouseimplicitcursor)
n Canonlyuseitinselect,insert,delete
declare
xint;
begin
selectdid_seq.currvalintoxfromdual;
dbms_output.put_line(x);
end;
n
54
Roadmap
n
n
n
n
n
n
Procedures
HowtodebugPL/SQLprogram
Functions
DynamicSQL
HowtoAutomaticallyGenerateValues
DuringInsert
VArrayDataType
55
VarrayDataType
n
n
n
n
n
Orderedsetofelements
Allelementsareofthesametype
Eachelementhasanindexidentifyingthe
element'spositioninthearray
Themaximumarraysizeisspecified
duringthearraydeclaration
Similartoarraysinotherlanguagessuch
asJava,C
56
WhenToUseIt
n
Usedascolumndatatype:storeashortlistofitems
withoutusingaseparatetable(e.g.,listofphone#s)
n
n
n
Catch:noteasytodirectlyrefertoanelementinarrayinSQL
(oftenneedtousePL/SQLorconvertittoatable)
Sonotrecommendedunlessthatcolumnisnotfrequentlyused
UsedasalocalvariableorparameterinPL/SQL
n
n
n
Topassavariable#ofparameterswiththesametype
(rememberinJava:main(Stringargv[]))
Totemporarystoresomevalues
Catch:shouldnotbeusedtostoremanyvalues
57
StepsUsingVaryingArrays
n
n
n
n
Createadatatypewithvarray
Canusethistypeasacolumndatatypeora
typeinPL/SQLprogram
Note:canNOTdirectlyrefertoaspecific
elementinthearrayinSQLselect,usually
needtousePL/SQL
InPL/SQL:usealooptorefertoeachelement
inthearray
58
CreateVarrayDataType
create or replace type type_name as
varray(maxsize) of element-type;
E.g., create a phone list type of 3
phones
Create or replace type
phoneListType as varray(3) of
varchar(20)
59
UseVarrayTypeinColumn
createtablecust2
(cidinteger,
cnamevarchar(20),
cphonephoneListType,
primarykey(cid));
60
InsertaVarrayValue
Use constructor:
Type_name(element value list);
insertintocust2values
(1,'john',
phoneListType('4104551111',
'4434101234'));
61
AccessElementsinArray
CanonlyaccessitselementsinPL/SQL
ThefollowingSQLisNOTallowed:
Selectcphone(1)fromcust2;
n Needtouseacursortostorethearraytype
valueintoavariable
n UsingPL/SQLbuiltinfunctionsforcollections
n
n
n
n
Declareavariableofarraytype
Variable_name.count:arraysize(notmaxsize)
Variable_name(i):itheelement
62
UseVarrayValueinPL/SQL
Program
createorreplaceprocedureprintPhone(idinteger)
as
rphoneListType;
begin
selectcphoneintorfromcust2wherecid=id;
ifrisnotnullthen
foriin1..r.countloop
dbms_output.put_line(r(i));
endloop;
endif;
exception
whenno_data_foundthen
dbms_output.put_line('nosuchcustomerid');
end;
63
execprintPhone(1);
Exercise
n
n
n
CreatethesamephoneListTypeand
cust2table
CreateaPL/SQLprocedurethatprint
outthefirsttwophonenumbersofa
givencustomer(cidisinput)
Callthisprocedurewithid=1
64
PassVariableNumberof
Parameters
createorreplaceprocedureprintPhone(idlistphoneListType)
as
begin
foriin1..idlist.countloop
dbms_output.put_line(idlist(i));
endloop;
end;
execprintPhone(phoneListType('4104551111','443455
1234'));
65