Lecture Notes Unit5 chapter18 Packages.pdf

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 : Packages
Sub-Topic :
Definition of packages, Package Specification Section, Package body section, Syntaxt for package and package body creation, Steps for creating and executing a packages, deleting a packages, oracle packages


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 17: https://www.slideshare.net/slideshow/lecture-notes-unit5-chapter17-stored-procedures-and-functions/270844763
Chapter 16 : https://www.slideshare.net/slideshow/lecture-notes-unit5-chapter16-trigger-creation/270631080
Chapter 15 : https://www.slideshare.net/slideshow/lecture-notes-unit5-chapter-15-pl-sql-programming/270383949

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 18
Packages
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. Package -Definition
➢Apackageisacollectionofdatabaseobjects,suchas
storedprocedures,functions,variables,constants,and
cursors.
➢Apackagemaycontainsubprogramsthatcanbecalled
fromatrigger,procedure,orfunction.
➢Apackagealsoimprovesmachineperformance,
becauseitsimultaneouslytransfersseveralobjectsto
thememory.

3. Package Structure
➢Apackagehastwosections:thespecificationsection
andthebodysection.
➢Theyneedtobecreatedseparately.

4. Specification Section
➢Thespecificationsectionisatypeofsummaryforthe
contentsofthepackagebody
➢ThespecificationsectioniscreatedwiththeCREATE
PACKAGEcommand,asshownbelow:
Syntax:
CREATE[ORREPLACE]PACKAGE [schema.]
package{IS|AS}pl/sql_package_spec
➢Arguments
ORREPLACE
Re-createsthepackagespecificationifitalreadyexists.

5. Specification Section
schema
➢Theschemathatwillcontainthepackage.Ifschemais
omitted,Oraclecreatesthepackageinitsownschema.
package
➢Thenameofthepackagetobecreated.
pl/sql_package_spec
➢Thepackagespecification.Itcandeclareprogram
objects.PackagespecificationsarewritteninPL/SQL.

6. Package Body
➢Thepackagebodycontainstheformaldefinitionofall
theobjectsreferencedinthedeclarationsection.
➢ThepackagebodyiscreatedwiththeCREATE
PACKAGEBODYcommand,asshownbelow:
➢Syntax:
CREATE[ORREPLACE]PACKAGEBODY[schema.]
package{IS|AS}pl/sql_package_body

7. Package Body
Arguments:
ORREPLACE
➢Re-createsthebodyofthepackageifitalreadyexists.
Schema
➢Theschemathatwillcontainthepackage.Ifschemais
omitted,Oraclecreatesitinitsownschema.
package
➢Thenameofthepackagetobecreated.
pl/sql_package_body
➢Thebodyofthepackage.Itcandeclareanddefine
programobjects.Packagebodiesarewrittenin
PL/SQL.

8. Steps to working with package
1.Createapackage(Declarationoffunctionand
procedure)
2.Compilethepackage
3.Createapackagebody(Definitionoffunctionand
procedure)
4.Compilethepackagebody
5.Executetheprocedurebyusingthefollowing
command.
Execpackagename.procedurename(argumentlist)
Ex:Execgenemp.increase2(1001,10)
6.Executethefunctionbyusingthefollowingcommand.
Selectpackagename.functionname(argument)fromdual.
Ex:Selectgenemp.countemp(1001)fromdual.

9. Package Creation
createPACKAGEGENEMP1AS
PROCEDURE increase2 (veno IN
emp2.eno%type,percentnumber);
functioncountemp(vdnoinemployee.dno%type)
RETURNnumber;
END;
/

10. Package Body Creation
createpackagebodygenemp1is
PROCEDURE increase2(venoINemp2.eno%type,percent
number)IS
BEGIN
updateemp2setsalary=salary*(1+percent/100)
whereeno=veno;
END;
functioncountemp(vdnoinempl.dno%type)returnnumberis
emptotnumber;
begin
selectcount(*)intoemptotfromemplwheredno=vdno;
returnemptot;
end;
end;
/

11. Referencing a Package Subprogram
To access the procedure specified inside a package, then
use the packagenameassociated with the procedure name.
SQL>Execgenemp.increase2(105,10)
PL/SQL procedure successfully completed.
Toaccessthefunctionspecifiedinsideapackage,then
usethepackagenameassociatedwiththefunctionname.
SQL>Selectgenemp.countemp(101)fromdual;
PL/SQLproceduresuccessfullycompleted.

12. Deleting a Package
➢To delete the package then use the following
command
SQL>Droppackagepackagename
Ex:Droppackagegenemp;
➢To delete the package body then use the following
command
SQL>Droppackagebodypackagename
Ex:Droppackagebodygenemp;

13. Oracle Packages
➢Oraclehasdozensofpackagescontainingprocedures
thatextenditsfunctionality.Mostofthemstartwith
theprefixDBMS_,UTL_,DEBUG_,orOUTLN_.
➢Followingisanexampleoftheuseofthe
DBMS_OUTPUT package,whichiscalledbya
triggerthatisenabledwhenanyDMLoperationis
performedintheEMP2table:

14. Oracle Packages
CREATEORREPLACETRIGGERsaldif
BEFOREDELETEORINSERTORUPDATEONemp2FOR
EACHROWWHEN(new.eno>0)
DECLARE
sal_diffnumber;
BEGIN
sal_diff:=:new.salary-:old.salary;
dbms_output.put('Old:'||:old.salary);
dbms_output.put('New:'||:new.salary);
dbms_output.put_line('Difference'||sal_diff);
END;
/
Output:
SQL> update emp2 set sal=sal*1.3 where eno=101;
Old: 1100 New: 1430 Difference 330