Lecture Notes Unit5 chapter 15 PL/SQL Programming

Description:
Welcome to the comprehensive guide on Relational Database Management System (RDBMS) concepts, tailored for final year B.Sc. Computer Science students affiliated with Alagappa University. This document covers fundamental principles and advanced topics in RDBMS, offering a structured approach to understanding databases in the context of modern computing. PDF content is prepared from the text book Learn Oracle 8I by JOSE A RAMALHO.

Key Topics Covered:

Main Topic : PL/SQL
Sub-Topic :
Structure of PL/SQL Block, Declaration Section, Variable, Constant, Execution Section, Exception, How PL/SQL works, Control Structures, If then Command,
Loop Command, Loop with IF, Loop with When, For Loop Command, While Command, Integrating SQL in PL/SQL program.



Target Audience:

Final year B.Sc. Computer Science students at Alagappa University seeking a solid foundation in RDBMS principles for academic and practical applications.

URL for previous slides
Unit V
Chapter 15

Unit IV

Chapter 14 Synonym : https://www.slideshare.net/slideshow/lecture_notes_unit4_chapter14_synonyms-pdf/270327685
Chapter 13 Users, Privileges : https://www.slideshare.net/slideshow/lecture-notes-unit4-chapter13-users-roles-and-privileges/270304806
Chapter 12 View : https://www.slideshare.net/slideshow/rdbms-lecture-notes-unit4-chapter12-view/270199683
Chapter 11 Sequence: https://www.slideshare.net/slideshow/sequnces-lecture_notes_unit4_chapter11_sequence/270134792
chapter 8,9 and 10 : https://www.slideshare.net/slideshow/lecture_notes_unit4_chapter_8_9_10_rdbms-for-the-students-affiliated-by-alagappa-university/270123800

About the Author:
Dr. S. Murugan is Associate Professor at Alagappa Government Arts College, Karaikudi. With 23 years of teaching experience in the field of Computer Science, Dr. S. Murugan has a passion for simplifying complex concepts in database management.

Disclaimer:
This document is intended for educational purposes only. The content presented here reflects the author’s understanding in the field of RDBMS as of 2024.

1. RDBMS -Unit V
Chapter 15
PL/SQL
Prepared By
Dr. S.Murugan, Associate Professor
Department of Computer Science,
AlagappaGovernment Arts College, Karaikudi.
(Affiliated by AlagappaUniversity)
Mailid: [email protected]
Reference Book:
LEARN ORACLE 8i, JOSE A RAMALHO

2. PL/SQL
➢PL/SQLisanextensionoftheSQLlanguage.
➢Itisaprocedurallanguage.
➢PL/SQL combines the SQL language’s ease of data
manipulation and the procedural language’s ease of
programming.
➢With the PL/SQL language, we can create schema
objects, including Stored procedures and functions,
Packages, Triggers, Cursors.

3. PL/SQL
➢Storedproceduresandfunctions.Astoredprocedure
isaPL/SQLprogramthatcanbeenabledbyan
application,atrigger,oranOracletool.
➢Thebasicdifferencebetweenaprocedureanda
functionisthataprocedureexecutesitscommands
andafunctionexecutescommandsandreturnsa
result.
➢Packages.Apackageisafilethatcontainsagroupof
functions,cursors,storedprocedures,andvariablesin
oneplace.

4. PL/SQL
➢Triggers.AtriggerisaPL/SQLprogramthatisstored
inthedatabaseandexecutedimmediatelybeforeor
aftertheINSERT,UPDATE,andDELETE
commands.
➢Cursors.OracleusesworkspacestoexecutetheSQL
commands.ThroughPL/SQLcursors,itispossibleto
nametheworkspaceandaccessitsinformation.

5. PL/SQL BLOCK
➢ThebasicstructureofPL/SQLblockisgivenbelow:

6. PL/SQL BLOCK
TherearethreepartstothePL/SQLblock:
➢Declarationsection/Declare(optional),inwhichall
theobjectsaredeclared.
➢Executionsection/Begin,inwhichthePL/SQL
commandsareplaced.
➢Exceptionsection/Exception(optional),inwhichthe
errorsarehandled.

7. PL/SQL BLOCK -Declaration Section
Inthedeclarationsection,thedevelopercanperformthe
following:
➢Declare the name of an identifier -PI.
➢Declare the type of identifier (constantor variable).
➢Declare the data type of the identifier. -REAL
➢Assign (initialize) contents to the identifier. –3.14
Ex:PI CONSTANT REAL := 3.14

8. PL/SQL BLOCK -Variables
➢Thevariablescancontainanydatatypethatisvalid
forSQLandOracle(suchaschar,number,long,
varchar2,anddate)inadditiontothesetypes:
➢BooleanCanbeassignedthevaluesTrue,False,or
NULL.
➢Binary_integerAcceptsnumbersbetween
-2,147,483,647and2,147,483,647.
➢PositiveAcceptsnumbersfrom1to2,147,483,647.
➢NaturalAcceptsnumbersfrom0to2,147,483,647.

9. PL/SQL BLOCK -Variables
➢%typeAssignstothevariablethatisbeingcreatedthe
samedatatypesusedbythecolumnthatisbeingused.
Toaccessthefieldofdeptnofromdept.tableand
assignedtodeptp.
deptp:=dept.deptno%type;
➢%rowtypeDeclaresacomposedvariablethatis
equivalenttotherowofthetable.
➢Afterthevariableiscreated,thefieldsofthetablecan
beaccessed,usingthenameofthisvariablefollowed
byaperiodandthenameofthefield:
Empline:=emp%rowtype
➢Afterthevariableiscreated,youcanusethefollowing

10. PL/SQL BLOCK -Variables
➢Afterthevariableiscreated,youcanusethefollowing
expressiontoassignacolumntoanothervariable:
Newvar:=empline.ename;

11. PL/SQL BLOCK -Variables
➢Therearetwowaystoassignvaluestoavariable.The
firstistousetheassignmentoperator“:=”:
tot:=price*margin;
increase:=sal*1.10;
➢Thesecondwaytoassignvaluestovariablesistouse
theSELECTcommandtoassignthecontentsofthe
fieldsofatabletoavariable:
SELECTsal*0.10INTOincreasedFROM
empWHEREempno=emp_id;

12. PL/SQL BLOCK -Constant
➢Thedeclarationofaconstantissimilartothe
declarationofavariable,excepttoaddthekeyword
Constantafterthenameoftheconstant:
PICONSTANTREAL:=3.14;
➢Eachvariableorconstantmustbespecifiedwithits
name,type,and,optionally,initialvalue.Alltherows
mustendwithasemicolon:
DECLARE
Cust_namevarchar2(20);
Creditnumber(5,2):=100;

13. PL/SQL BLOCK -Execution Section
➢ThissectionbeginswiththeBegindeclaration.
➢ThissectioncancontainSQLcommands,logical
controlcommands,andassignmentcommands,aswell
asothercommands.
➢Allcommandsarealwaysendwithasemicolon.

14. PL/SQL BLOCK -Exception
➢Inthissection,thedevelopercanusecommandsto
handleanerrorthatoccursduringtheexecutionofa
PL/SQLprogram.

15. How PL/SQL Works
➢PL/SQLisanenginethatmakesuppartoftheOracle
server.
➢Itexecutestheproceduralcommandsandpassesthe
SQLcommandsfortheOracleservertoprocess.
➢PL/SQLblockscanbecreatedwithanyofthe
processingeditors.(Ex:WindowsNotepad)
➢ToexecuteaPL/SQLprogramorscript,youcanuse
SQL*Plus,whichallowscreating,storing,and
executingPL/SQLblocks.

16. How PL/SQL Works

17. Example Program
➢Step1:Typetheprogramineditorandexecuteusing
@symbol.
➢SQL>setserveroutputon;

18. Example Program for variable and constant

19. Control Structures
➢PL/SQLhassomecommandstocontroltheexecution
flowoftheprogram.Theyareresponsiblefordecision
makinginsidetheapplication.
➢controlstructuresinPL/SQL,whichcanbedivided
intoconditioncontrolstructures(selection),sequence
structures,andrepetitionoriterationstructures.
➢Differenttypesofstructureexplainedinthefigure
15.3.

20. Control Structures

21. The IF..THEN Command
➢TheIF..THENcommandevaluatesaconditionand
executesoneormorerowsofcommandsonlyifthe
analyzedconditionistrue.
➢Ithastwovariationsasfollows:

22. The IF..THEN Command
➢Insyntax1,thecommandsthatappearbetweenthe
THENandENDIFclauseswillbeexecutedonlyif
theconditionistrue.
Example:
declare
salnumber(7,2);
begin
select salary into salfrom emp2 where eno=101;
IF SAL>= 5000 THEN
UPDATE EMP2 SET salary = salary*1.2;
END IF;
dbms_output.put_line(sal);
end;
/

23. The IF..THEN Command
In syntax2, more than one condition can be analyzed
and, therefore, several actions can be executed:
1. create table emp2(enonumber(3), salary number(7,2))
2. insert into emp2 values (102, 1500)
3. Type the following program and execute:
declare
salnumber(7,2);
begin
select salary into salfrom emp2 where eno=101;
IF sal<2000 THEN
UPDATE EMP2 SET salary = salary * 1.2;
ELSIF sal<3000 THEN
UPDATE EMP2 SET salary = salary * 1.3;
ELSE
UPDATE EMP2 SET salary = salary * 1.4;
END IF;
dbms_output.put_line(sal);
end;

24. The IF..THEN Command
➢Ifthemainconditionistrue,thefollowingcommands
willbeexecuteduntilanotherELSIForELSEclause
isfound.
➢IfthefirstELSIFconditionisfalse,theprogramtests
thesecondcondition,andsoon.
➢Whenthefirsttrueconditionisfound,itscommands
areexecuted,andtheprogramjumpstotherowafter
theENDIFcommand.

25. The LOOP Command
➢TheLOOPcommandinitializesagroupofcommands
indefinitely,oruntilaconditionforcesa“break”inthe
loopandtheexecutionoftheprogramtoanother
place.
➢TheEXITcommandisresponsibleforinterruptingthe
loopexecution:

26. The LOOP Command -Example
LOOPwith IF Loop with WHEN
declare
I number(3);
begin
I := 1;
Loop
I := I+1;
DBMS_OUTPUT.PUT_LINE(I);
IF I>= 30 THEN
EXIT;
END IF;
END LOOP;
END;
/
declare
I number(3);
begin
I := 1;
Loop
I := I+1;
DBMS_OUTPUT.PUT_LINE(I);
EXIT WHEN I>= 30;
END LOOP;
END;
/
➢Thefollowinglooprepeateduntilthecountervariable
Ibecome30.

27. The FOR..LOOP Command
➢TheFOR..LOOPcommandisavariationoftheLOOP
command.
➢Here,thecommandsareautomaticallyexecuteduntil
anevaluatedconditionreturnsfalse.
Syntax:
FOR <counter> IN [REVERSE] <initial_value> . . <final_value>
LOOP
<commands>
END LOOP;

28. The FOR LOOPCommand -Example
Example:
declare
j number(3);
begin
FOR j IN 1..10
LOOP
DBMS_OUTPUT.PUT_LINE(j);
END LOOP;
END;
/
➢TheFORcommandinitializesacontrolvariablecalledJ,which
hastheinitialvalueof1.
➢ThecommandswillbeexecuteduntilENDLOOPisfound.
➢Then,thecontrolreturnstotheFORcommand,which
incrementsthevariableandanalyzesthemastercondition,i.e.,
whetherthevalueofJislessthanthefinalvalue.
➢Whenthishappensthecycleisreinitialized.

29. The FOR LOOPCommand -Example
➢TheREVERSEclausemakesthecounterstartwiththe
highestvalueanddecreaseuntilitreachesthelowest
value.
declare
j number(3);
begin
FOR j IN reverse 1..10
LOOP
DBMS_OUTPUT.PUT_LINE(j);
END LOOP;
END;
/

30. The WHILE Command
➢TheWHILEcommandisanothercontrolstructure.
Thisstructureonlyexecutesthecommandsifthe
analyzedconditionistrue.
declare
x number(3);
begin
x:=1;
while x<=20 loop
DBMS_OUTPUT.PUT_LINE(x);
x:=x+1;
END LOOP;
END;
/

31. Integrating SQL in a PL/SQL Program
➢SQLcommandscanbeinsertedintheexecution
sectionofaPL/SQLblock.
➢Thedevelopercanuse(reference)avariableor
constantdeclaredinthedeclarationsection.
➢InsideaPL/SQLprogramtheSELECTcommanduses
anewclausecalledINTOthatallowstransferringthe
contentsofthefieldsinarowtothevariablesin
memory.
➢Command:SETSERVEROUTPUT ON,GET
(Retreivethefile),RUN(@Executetheprogram)

32. Integrating SQL in a PL/SQL Program
Declare
Var_namenumber(7,2);
Begin
SELECTsalaryINTOVar_nameFROMemp2whereeno
=101;
dbms_output.put_line('salary='||Var_name);
End;
/
➢Notethattoattributecontentstoafieldinavariable,
theSELECTcommandmustreturnonlyonerow;
➢otherwiseanerrorwilloccur,sinceseveralvaluesare
beingassignedtoonlyonevariable.