11) Procedures
declare
procedure findarea(myrad number) is
area number;
begin
area:=3.14*myrad*myrad;
dbms_output.put_line('Area is '||area);
end;
begin
findarea(3);
end;
12) Functions.
declare
function findarea(myrad number) return number is
area number;
begin
area:=3.14*myrad*myrad;
return(area);
end;
begin
dbms_output.put_line('Area is '||findarea(3));
end;
13) Stored Procedures
create or replace procedure myproc(myrad number)
is
x number;
begin
x:=5;
x:=myrad*10;
dbms_output.put_line('The output is '||x);
end;
14) Execute Stored Procedures
exec myproc(10);
15) Stored Procedures without declarations.
create or replace procedure myproc(myrad number) is
begin
dbms_output.put_line('The output is '||myrad*10);
end;
16) Creation of triggers
create or replace trigger trig1
after insert
on tea1
for each row
begin
dbms_output.put_line('U have inserted one record');
end;
Trigger created.
17) Altering of triggers.
alter trigger trig1 disable;
21) Trigger for day.
create or replace trigger trig190
before insert
on emp
begin
if(to_char(sysdate,'DY')='FRI')
then
dbms_output.put_line('Sorry , not allowed on Friday');
end if;
end;
22) Raise Application Error.
create or replace trigger trig190
before insert
on emp
begin
if(to_char(sysdate,'DY')='FRI')
then
raise_application_error(-20200,'Not allowed');
end if;
end;
Trigger to prevent any person with A or a from getting inserted.
create or replace trigger trig_a
before insert
on emp
for each row
begin
if(:new.ename like 'A%' or :new.ename like 'a%')
then
raise_application_error(-20678,'U cannot insert with A');
end if;
end;
23) Granting execute permissions to Stored Procedures.
grant all on myproc to trng100;
24) User Records
declare
type myrecord is record
(
name emp.ename%TYPE,
salary emp.sal%TYPE
);
myr myrecord;
begin
select ename,sal into myr from emp where ename='ADAMS';
dbms_output.put_line(myr.name);
end;
25) PL/SQL tables
declare
type mytable is table of varchar(20)
index by binary_integer;
mt mytable;
begin
mt(1):='Software';
mt(2):='Hardware';
dbms_output.put_line(mt.count());
end;
26) Packages
create or replace package circle as
function area(radius number) return number;
function perimeter(radius number) return number;
end;
create or replace package body circle as
function area(radius number) return number is
begin
return(3.14*radius*radius);
end;
function perimeter(radius number) return number is
begin
return(2*3.14*radius);
end;
end;
begin
dbms_output.put_line(circle.area(10));
end;
28) Triggers for Audit Operations (Reference)
create table trigger_audit
(
serial_num number(10) primary key,
name varchar(10),
operation varchar(10),
trans_date date
);
Trigger for Auditing.
create or replace trigger trig1_audit
after insert or delete or update
on emp
declare
username varchar(20);
trans_time varchar(20);
status varchar(20);
begin
if(inserting)
then
status := 'INSERT';
end if;
if(updating)
then
status := 'UPDATE';
end if;
if(deleting)
then
status := 'DELETE';
end if;
select user into username from dual;
select to_char(sysdate,'dd-mon-yyyy') into trans_time from dual;
insert into trigger_audit
values(seq1_audit.nextval,username,status,trans_time);
end;