Functions , Procedures
Triggers
FUNCTIONS
• Functions and procedures allow “business logic”
to be stored in the database and executed from SQL
statements.
• These can be defined either by the procedural
component of SQL or by an external programming
language such as Java, C, or C++.
FUNCTIONS
SYNTAX:
create [or replace]
function <function name> (<arg list>) return data type
Is
............. ---------> Declaration part
Begin
............. ---------->Execution part
exception
............-------------->Exception handling part
return <value>;
end;
/
FUNCTIONS
Program: Given string is palindrome are not using functions
create or replace function funpalin(str varchar) return varchar
is
str1 varchar2(15);
begin
for i in reverse 1..length(str)
loop
str1:=str1||substr(str,i,1);
end loop;
return str1;
end;
/
FUNCTIONS
declare
str varchar2(15):='&str';
r varchar2(15);
begin
r:=funpalin(str);
if (str=r) then
dbms_output.put_line(' Given string is palindrome');
else
dbms_output.put_line('Given string is not a palindrome');
end if;
end;
/
PROCEDURES
• Procedures are used to perform a specific task and may
or may not return a value.
• Syntax:
create [or replace]
procedure <procedure name> (<arg list>)
is
............. ---------> Declaration part
begin
............. ---------->Execution part
exception
............ ---------->Exception handling part
end;
PROCEDURES
create or replace
procedure fact2(n in number, m out number)
is
fact number:=1;
begin
for i in 1..n
loop
fact:=fact*i;
end loop;
m:=fact;
end;
PROCEDURES
Types of parameters used in procedures
1. IN parameter:
• This parameter is the default parameter and it
is same as a constant.
• Initialization is must to parameter and value
of this parameter is unchanged through out
the execution of the program .
i.e.,it remains the same.
PROCEDURES
[Link] parameter:
• This parameter is exactly same as the uninitialized
variable initially , but during execution of the
program , the program assigns value to this
parameter and this value is sent back to the calling
environment.
OUT parameter works same as the return value of a
function. Only one time assignment is allowed
through out the execution of the program.
PROCEDURES
[Link] OUT parameter
• This parameter is exactly same as initialized
variable and during the execution of the program,
the program assigns the value returned from the
calling environment to this variable.
TRIGGERS
• A trigger is a database object.
• A trigger is almost same as procedure.
• A procedure always requires an explicit call for
execution but Triggers are executed automatically
when any triggering event occurs.
• Insert , Delete , Update are considered as the
triggering events of the database trigger.
• These events initiate the firing of trigger.
TRIGGERS
Advantages:
1.A database trigger is a security object to provide
security to the table like tracking the transaction.
2.A database trigger is used to define the complex
business constraints.
3.A database trigger is also used to define the default
values of database table.
TRIGGERS
Parts of a trigger:
[Link] event
[Link] type
[Link] restriction
TRIGGERS
[Link] event:
• The trigger event is the time, when the trigger must
be implicitly executed .
• There are three trigger events usually used in a
trigger definition. i.e., insert/update/delete.
TRIGGERS
2. Trigger types:
(i) Before trigger may be defined to execute a trigger,
before trigger event fires.
(ii) After trigger executes after the trigger event.
(iii)for each row type of trigger executes everytime a
record is deleted, updated and inserted.
For example,
"for each row trigger" executes 10 times when 10
records are updated by an sql query.
(iv) for each statement trigger may be executed only
once for any sql statement.
TRIGGERS
[Link] restriction:
• It is the when clause added with the trigger
definition . It is an additional event to the trigger
definition.
TRIGGERS
create or replace
trigger <trigger name>
before/after [insert][or update] [or delete]
on <table name>
[for each row]
when (<condition>)
declare
----
begin
---
exception
----
end;
TRIGGERS
• create table student77
( rno number(5),
sname varchar2(15),
course varchar2(4),
sem number(2));
• insert into student77 values (&rno, '&sname', '&course',&sem);
• create table student_backup
( rno number(5),
sname varchar2(15),
course varchar2(4),
sem number(2));
TRIGGERS
• Write a trigger to backup a record or multiple
records are deleted from the student77 table.
create or replace trigger trig1
before delete on student77
for each row
begin
insert into student_backup values
(:[Link], :[Link], :[Link], :[Link]);
end;
/
THANK YOU
[Link]@[Link] 9959597459
