Lecture Notes Unit5 chapter17 Stored procedures and functions

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 : Stored Procedures and functions
Sub-Topic :
Definition of stored procedures, difference between procedures and trigger, syntax for creation of procedures, Example program for procedures with no arguments , procedure program for salary increment, example program for procedure with arguments, trigger program for salary increment for the particular department, Drop procedures, Definition of function , deifference between function and procedures, syntax for creating function, sample program for function


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 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 17
Stored Procedures and Functions
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. Stored Procedure -Definition
➢AstoredprocedureisagroupofSQLandPL/SQL
commandsthatexecutecertaintasks.
Procedure Trigger
The user must call a
procedure either from a
program or manually.
A trigger is automatically
executed when a trigger event
occurs.

3. TRIGGER -Definition

4. Creating a Stored Procedure
➢Astoredprocedurehastwoparts:aspecification
sectionandtheprocedurebody.TheCREATE
PROCEDURE commandisresponsibleforthe
creationofprocedures.
➢Syntax:
CREATE [OR REPLACE] PROCEDURE [schema.]
procedure
[ (argument [IN | OUT | IN OUT] datatype
[, argument [IN | OUT | IN OUT] datatype] ...)]
{IS|AS} {pl/sql_subprogram_body| external_body}

5. Creating a Stored Procedure
Arguments:
OR REPLACE
➢Re-createstheprocedureifitalreadyexists.
schema
➢Theschemathatwillcontaintheprocedure.Ifschemais
omitted,Oraclecreatestheprocedureinthecurrentschema.
procedure
➢The name of the procedure to be created.
argument
➢Thenameofaprocedureargument.Iftheproceduredoesnot
acceptarguments,youcanomittheparenthesesafteritsname.
IN
➢Specifies that a value is determined for the argument when the
procedure is called.

6. Creating a Stored Procedure
OUT
➢Specifiesthattheprocedurereturnsavalueforthisargumentin
itscallingenvironmentaftertheexecution.
IN OUT
➢Specifiesthatavaluemustbedeterminedfortheargumentwhen
theprocedureiscalledandthattheprocedurereturnsavaluein
itscallingenvironmentaftertheexecution.
datatype
➢An argument can be any data type supported by PL/SQL.
IS pl/sql_subprogram_body
➢The procedure definition. Procedure definitions are written in
PL/SQL.

7. Creating a Stored Procedure –Example Program No Argument
1.Create table emp2 (name varchar2(25), salary number(7), eno
number(3));
2.Insert few records.
3.View the records
4. Type the following program proc1.sql (This procedure
increases the employees’ salaries 10 percent)
create or replace procedure increase1
is
begin
update system.emp2 set salary=salary*1.1;
end;
/
5. Run the program (@ filepath)
6. Execute the procedure by the following statement
exec increase1
7. Verify the modified output (salary field) in the emp2 database
with step 3. (select * from emp2;)

8. Creating a Stored Procedure –Example Program with Arguments
➢Thisprocedureincreasestheemployees’salariesbutthistime
thepayraiseisonlyforthoseinaparticularemployeenumber
andtheincreaseisadifferentpercentage.
➢CREATE PROCEDURE increase2 (venoIN emp2.eno%type,
percent number) IS
BEGIN
update emp2
set salary=salary*(1+percent/100)
where eno=veno;
END;
/
➢Execute the procedure by the following statement
exec increase2(105,20)
➢Here 105 is the employee number and 20 is the percentage to be
increase.

9. Deleting a Stored Procedure
➢To delete a stored procedure, use the DROP PROCEDURE
command:
➢Syntax :
DROP PROCEDURE name
➢Example:
SQL> drop procedure increase1;
Procedure dropped.

10. Function
➢AFunctionisagroupofSQLandPL/SQLcommandsthat
executecertaintasks.
➢DifferencebetweenProcedureandFunction
Procedure Function
It does not return a valueIt returns a value
The procedure invoked
by the procedure name.
The function invoked by
the function name with
selectstatement.

11. Creating a Function
➢TheCREATEFUNCTIONcommandcreatesafunctionasan
isolatedschemaobject.Youcanalsocreateafunctionaspartof
apackage.
Syntax:
CREATE[ORREPLACE]FUNCTION[schema.]function
[(argument[IN|OUT|INOUT]datatype
[,argument[IN|OUT|INOUT]datatype]...)]
RETURNdatatype{IS|AS}pl/sql_subprogram_body

12. Creating a Function –Example Program for counting number of records
1.Createtheempltablewiththefieldasname,salaryanddno
(departmentnumber)andinsertfewrecords.
2.Typethefollowingprogram
createorreplacefunctioncountemp(vdnoinempl.dno%type)
returnnumberis
emptotnumber;
begin
selectcount(*)intoemptotfromemplwheredno=vdno;
returnemptot;
end;
/
3.Runtheprogram
4.Executethefunctionbyusingthefollowingstatement
selectcountemp(101)fromdual