FUNCTIONS PACKAGES TRIGGERS

EX NO: 8 FUNCTIONS PACKAGES TRIGGERS



Functions
Functions have the same structure as procedures. The only difference is that a function returns value whose data type (unconstrained) must be specified.

Syntax
Create function <function name> [ list of parameters ] return Data type

create function get_dept_salary(dno number) return number is
all _sal number;
begin
All_ sal := 0;
for emp_sal in (select SAL from EMP where DEPTNO = dno
and SAL is not null) loop
all _sal := all_ sal + emp_ sal.sal;
end loop;
return all_ sal;
end get_ dept_ salary;

OUTPUT
Function created.
  1  declare
  2  salary number;
  3  begin
  4  salary:=get_dept_salary(20);
  5  dbms_output.put_line(salary);
  6* end;

SQL> run;
  1  declare
  2  salary number;
  3  begin
  4  salary:=get_dept_salary(20);
  5  dbms_output.put_line(salary);
  6* end;
13379.25                                                                                                              

PL/SQL procedure successfully completed.

In SQL*Plus a variable can be defined using the command variable <variable name> <data type>;
 for example, variable salary number. The above function then can be
called using the command execute :salary := get_ dept_ salary(20);

Function to generate fibonacci series

create or replace function fibb(n number) return number is
a number:=0;
t number:=1;
c number:=0;
n1 number:=0;
begin
loop
dbms_output.put_line(to_char(a));
c:=a;
a:=t;
t:=c+t;
n1:=n1+1;
exit when n<n1;
end loop;
return c;
end fibb;

OUTPUT
Function created.
SQL> execute :result:=fibb(5);
0
1
1
2
3
5
PL/SQL procedure successfully completed.


Function to generate pascal triangle
create or replace function pascal(n number) return number is
a number:=0;
c number:=0;
d number:=0;
s number:=0;
n1 number:=0;
begin
for a in 1..n
loop
   s:=a;
   while n-s>0
   loop
   s:=s+1;
   dbms_output.put(to_char(' '));
   end loop;
   for c in 1..a
   loop
   dbms_output.put(to_char(c));
   end loop;
   d:=a-1;
   while d>0
   loop
   dbms_output.put(to_char(d));
   d:=d-1;
   end loop;
dbms_output.put_line(to_char(''));
end loop;
return c;
end pascal;

OUTPUT
SQL> @pascal.sql
SQL> execute :result:=pascal(5);
1
121
12321
1234321
123454321
PL/SQL procedure successfully completed.

Packages

PL/SQL supports the concept of modularization by which modules and other constructs can be organized into packages. A package consists of a package specification and a package body. The package specification defines the interface that is visible for application programmers, and the package body implements the package specification

Package specification

create package manage_employee as
function hire_emp (name varchar2, job varchar2, mgr number, hiredate
date,sal number, comm number default 0, deptno number) return
number;
procedure fire_emp (emp_id number);
procedure raise_sal (emp_id number, sal_incr number);
end manage_employee;

create package body manage_employee as
function hire_emp (name varchar2, job varchar2, mgr number, hiredate
date,sal number, comm number default 0, deptno number)
return number is
new_empno number(10);
begin
select emp_sequence.nextval into new_empno from dual;
insert into emp values(new_empno, name, job, mgr, hiredate,sal,
comm, deptno);
return new_empno;
end hire_emp;

Package body specification

procedure fire_emp(emp_id number) is
begin
delete from emp where empno = emp_id;
if SQL%NOTFOUND then
raise_application_error(-20011, ’Employee with Id ’ || to_char(emp_id) ||
’ does not exist.’);
end if;
end fire_emp;

procedure raise_sal(emp_id number, sal_incr number) is
begin
update emp set sal = sal + sal_incr where empno = emp_id;
if SQL%NOTFOUND then
raise_application_error(-20012, ’Employee with Id ’ ||  to_char(emp_id)
|| ’ does not exist’);
end if;
end raise_sal;
end manage_employee;

A procedure or function implemented in a package can be called from other procedures and functions using the statement <package name>.<procedure name>[(<list of parameters>)].

 Triggers
Triggers provide a procedural technique to specify and maintain integrity constraints. Triggers
even allow users to specify more complex integrity constraints since a trigger essentially is a
PL/SQL procedure. Such a procedure is associated with a table and is automatically called by the
database system whenever a certain modification (event) occurs on that table. Modifications on a
table may include insert, update, and delete operations

Structure of Trigger

A trigger definition consists of the following (optional) components:
• trigger name
create [or replace] trigger <trigger name>
• trigger time point
before | after
• triggering event(s)
insert or update [of <column(s)>] or delete on <table>
• trigger type (optional)
for each row
• trigger restriction (only for for each row triggers !)
when (<condition>)
• trigger body
<PL/SQL block>


Row level triggers vs. Statement level triggers
A row level trigger is defined using the clause for each row. If this clause is not given, the trigger
is assumed to be a statement trigger. A row trigger executes once for each row after (before) the
event. In contrast, a statement trigger is executed once after (before) the event, independent of
how many rows are affected by the event. For example, a row trigger with the event specification
after update is executed once for each row affected by the update. Thus, if the update affects 20
tuples, the trigger is executed 20 times, for each row at a time. In contrast, a statement trigger is
only executed once. When combining the different types of triggers, there are twelve possible
trigger configurations that can be defined for a table.

Types of trigger
Only with a row trigger it is possible to access the attribute values of a tuple before and after the
modification (because the trigger is executed once for each tuple). For an update trigger, the old
attribute value can be accessed using :old.<column> and the new attribute value can be
accessed using :new.<column>. For an insert trigger, only :new.<column> can be used, and for a
delete trigger only :old.<column> can be used (because there exists no old, respectively, new
value of the tuple).



Event Trigger time point
Before                     After Trigger type                    
Statement                      row  
Insert x                        x x                         x  
Update x                        x          x                         x  
Delete          x                         x x                         x

Types of trigger
In these cases, :new.<column> refers to the  Attribute value of <column> of the inserted tuple,
and :old.<column>  refers to the attribute value of  <column> of the deleted tuple. In a row
trigger thus it is possible to specify comparisons between old add new  attribute values in the
PL/SQL block, e.g., “if :old.SAL < :new.SAL then . . . ”. If for a row trigger the trigger time
point before is specified, it is even possible to modify the new values of the row,
e.g., :new.SAL := :new.SAL * 1.05 or :new.SAL := :old.SAL.

Such modifications are not possible with after row triggers.

create or replace trigger check_salary before insert or update of name,deptno,salary on dept2 for each row
declare
k number;
begin
select avg(salary) into k from dept2 where deptno=:new.deptno;
if(:new.salary>k) then
raise_application_error(-20456,'salary greater than average salary of dept');
end if;
end;

create or replace trigger check_ salary_ EMP
after insert or update of SAL, JOB on EMP
for each row
when (new.JOB != ’PRESIDENT’)
declare
minsal, maxsal SALGRADE.MAXSAL%TYPE;
begin
select MINSAL, MAXSAL into minsal, maxsal from SALGRADE
where JOB = :new.JOB;
if (:new.SAL < minsal or :new.SAL > maxsal) then
raise application error(-20225, ’Salary range exceeded’);
elsif (:new.SAL < :old.SAL) then
raise application error(-20230, ’Salary has been decreased’);
elsif (:new.SAL > 1.1 * :old.SAL) then
raise application error(-20235, ’More than 10% salary increase’);
end if ;
end;

create or replace trigger check_salary before update or
delete on SALGRADE
for each row
when (new.MINSAL > old.MINSAL
or new.MAXSAL < old.MAXSAL)
declare
Job_emps number(3) := 0;
begin
if deleting then select count(*) into job_emps from EMP
where JOB = :old.JOB;
if job_emps != 0 then
Raise_application_error(-20240, ’ There still exist employees with the job ’ || :old.JOB);
end if ;
end if ;
if updating then
select count(*) into job_emps from EMP where JOB = :new.JOB
and SAL not between :new.MINSAL and :new.MAXSAL;
if job_emps != 0 then
:new.MINSAL := :old.MINSAL;
:new.MAXSAL := :old.MAXSAL;
end if ;
end if ;end;

  1  create or replace trigger check_eval before insert or update of e,f on T for each row
  2  when(new.e<new.f)
  3  begin
  4  if(:new.e<:new.f) then
  5  raise_application_error(-20223,'e is less than f');
  6  end if;
  7* end;
SQL> run;
  1  create or replace trigger check_eval before insert or update of e,f on T for each row
  2  when(new.e<new.f)
  3  begin
  4  if(:new.e<:new.f) then
  5  raise_application_error(-20223,'e is less than f');
  6  end if;
  7* end;

Trigger created.

SQL> insert into T values(3,4);
insert into T values(3,4)
      *
ERROR at line 1:
ORA-20223: e is less than f
ORA-06512: at "ME0821.CHECK_EVAL", line 3
ORA-04088: error during execution of trigger 'ME0821.CHECK_EVAL'

SQL> select * from T;
         E          F                                                                                                  
---------- ----------                                                                                                  
         2          1                                                                                                  
         3          2                                                                                                  
        13         12                                                                                                  
        10          4                                                                                                  

SQL> update T set f=11 where e=10;
update T set f=11 where e=10
      *
ERROR at line 1:
ORA-20223: e is less than f
ORA-06512: at "ME0821.CHECK_EVAL", line 3
ORA-04088: error during execution of trigger 'ME0821.CHECK_EVAL'
create or replace trigger sal_ls_avgsal before insert or update of sal on emp for
each row
  2         declare
  3          avgsal emp.sal%type;
  4           begin
  5         select avg(sal) into avgsal from emp  where deptno=&temp;
  6           if(:new.sal>avgsal) then
  7           raise_application_error(-20000,'salary is greater than average salary');
  8        end if;
  9*     end;
SQL> /

Enter value for temp: 10
old   5:       select avg(sal) into avgsal from emp  where deptno=&temp;
new   5:     select avg(sal) into avgsal from emp  where deptno=10;

Trigger created.

SQL> insert into emp(sal) values(45000);
insert into emp(sal) values(45000)
            *
ERROR at line 1:
ORA-20000: salary is greater than average salary
ORA-06512: at "ME0821.SAL_LS_AVGSAL", line 6
ORA-04088: error during execution of trigger 'ME0821.SAL_LS_AVGSAL'

SQL> update emp set sal=45000 where empno=2296;
update emp set sal=45000 where empno=2296
       *
ERROR at line 1:
ORA-04091: table ME0821.EMP is mutating, trigger/function may not see it
ORA-06512: at “ME0821.SAL_LS_AVGSAL", line 4
ORA-04088: error during execution of trigger 'ME0821.SAL_LS_AVGSAL'