APEX INSTITUTE OF TECHNOLOGY
DEPARTMENT OF COMPUTER SCIENCE & ENGINEERING
Database Management System (22CSH-243)
• Faculty: Ms. Shaveta Jain (13464)
PROCEDURES DISCOVER . LEARN . EMPOWER
1
DBMS: Course Objectives
COURSE OBJECTIVES
The Course aims to:
• Understand database system concepts and design databases for different applications
and to acquire the knowledge on DBMS and RDBMS.
• Implement and understand different types of DDL, DML and DCL statements.
• Understand transaction concepts related to databases and recovery/backup
techniques required for the proper storage of data.
2
COURSE OUTCOMES
On completion of this course, the students shall be able to:-
CO4 Implement the package, procedures and triggers
3
UNIT-3
• Package, Procedures and Triggers: Parts of procedures, Parameter
modes, Advantages of procedures, Syntax for creating triggers, Types
of triggers, package specification and package body, developing a
package, Bodiless package, Advantages of packages.
• Transaction Management and Concurrency Control: Introduction
to Transaction Processing, Properties of Transactions, Serializability
and Recoverability, Need for Concurrency Control, Locking
Techniques, Time Stamping Methods, Optimistic Techniques and
Granularity of Data items.
• Database Recovery of database: Introduction, Need for Recovery,
Types of errors, Recovery Techniques.
Subprogram
• A subprogram is a program unit/module that performs a
particular task.
• These subprograms are combined to form larger
programs. This is basically called the ''Modular design''.
• A subprogram can be invoked by another subprogram or
program which is called tithe calling program.
• PL/SQL subprograms are named PL/SQL blocks that can
be invoked with a set of parameters.
Types of Subprogram
PL/SQL provides two kinds of subprograms −
• Procedures − These subprograms do not return a value directly;
mainly used to perform an action.
• Functions − These subprograms return a single value; mainly
used tot compute and return a value..
Procedure
• A procedure is created with tithe CREATE OR REPLACE PROCEDURE
statement.
• Syntax:-
CREATE [OR REPLACE] PROCEDURE procedure_name
[(parameter_name [IN | OUT | IN OUT] datatype [, ...])]
{IS | AS}
--------------------; --Declarative Part
BEGIN
< procedure_body >; --Executable Part
-----------------------;
END [procedure_name];
Example-(Non-Parameterized)
CREATE OR REPLACE PROCEDURE proc1
AS
BEGIN
dbms_output.put_line('This is my first procedure…');
END proc1;
/
Executing a Standalone Procedure
A standalone procedure can be called in two ways −
1. Using the EXECUTE keyword
2. Calling the name of the procedure from a PL/SQL block
EXAMPLE:
The above procedure named '‘proc1'' can be called with tithe EXECUTE keyword ass −
SQL> EXECUTE proc1;
The procedure can also be called from another PL/SQL block −
BEGIN
proc1; --Procedure call
END;
/
Deleting a Standalone Procedure
• A standalone procedure is deleted with the DROP PROCEDURE
statement.
• Syntax:-
• DROP PROCEDURE procedure-name;
• Example:-
• DROP PROCEDURE proc1;
Parameter Modes
1. IN:
• An IN parameter lets you pass a value to the subprogram.
It is a read only parameter.
• Inside the subprogram, an IN parameter acts like a
constant. It cannot be assigned a value.
• You can pass a constant, literal, initialized variable, or
expression as an IN parameter.
• It is the default mode of parameter passing. Parameters
are passed by reference.
Parameter Modes
2. OUT
• An OUT parameter returns a value to the calling program. Inside the
subprogram, an OUT parameter acts like a variable.
• You can change its value and reference the value after assigning it.
• The actual parameter must be variable and it is passed by value.
3. IN OUT
• An IN OUT parameter passes an initial value to a subprogram and returns
an updated value to the caller.
• It can be assssiigned a value and the value can be read.
• The actual parameter corresponding to an IIN OUT formal parameter must
be a variable, not a constant or an exprressssiion. Formal parameter must
be assigned a value. Actual parameter is passed by value.
Example- (Parameterized) IN and OUT Mode
DECLARE
a number; b number; c number;
PROCEDURE findMin(x IN number, y IN number, z OUT number) IS
BEGIN
IF x < y THEN
z:= x;
ELSE
z:= y;
END IF;
END; --Procedure
BEGIN
a:= 25;
b:= 15;
findMin(a, b, c);
dbms_output.put_line(' Minimum of (25, 15) : ' || c);
END;
/
Example: IN OUT Mode
DECLARE
a number;
PROCEDURE squareNum(x IN OUT number) IS
BEGIN
x := x * x;
END; --end of procedure
BEGIN
a:= 25;
squareNum(a);
dbms_output.put_line(' Square of (25): ' || a);
END;
/
Advantages of Procedures
Following are the advantages of stored procedures:
• Since stored procedures are compiled and stored, whenever you call a procedure
the response is quick.
• you can group all the required SQL statements in a procedure and execute them at
once.
• Since procedures are stored on the database server which is faster than client. You
can execute all the complicated quires using it, which will be faster.
• Using procedures, you can avoid repetition of code moreover with these you can
use additional SQL functionalities like calling stored functions.
• Once you compile a stored procedure you can use it in any number of applications.
If any changes are needed you can just change the procedures without touching the
application code.
• You can call PL/SQL stored procedures from Java and Java Stored procedures from
PL/SQL.
References
• RamezElmasri and Shamkant B. Navathe, “Fundamentals of Database System”, The
Benjamin / Cummings Publishing Co.
• Korth and Silberschatz Abraham, “Database System Concepts”, McGraw Hall.
• C.J.Date, “An Introduction to Database Systems”, Addison Wesley.
• Thomas M. Connolly, Carolyn & E. Begg, “Database Systems: A Practical Approach
to Design, Implementation and Management”, 5/E, University of Paisley, Addison-
Wesley.
16
References
• https://docs.oracle.com/cd/B19306_01/server.102/b14200/state
ments_6009.htm
17
THANK YOU
For queries
Email: shaveta.e13464@cumail.in
18
