---------------
Triggers --------------------
---
Syntax
create
or replace trigger <trigger_name> [
before/after ] [ insert/update/delete ] on <table_name>
[ for each statement/for each row ][ when <condition> ];
Program
:
create
or replace trigger orders
before delete on order_master
begin
raise_application_error(-20001,'record may be present');
end;
Program
:
create
or replace trigger newold
before update on itemfile for each row
begin
if :new.qty_hand < :old.qty_hand then
raise_application_error(-20001,'qoh is less');
end if;
end;
---
INSTEAD OF TRIGGER ( user for
view )
create
or replace trigger test
INSTEAD OF insert on ord_view
begin
.......
end;
Syntax
:-
alter
trigger <trigger_name> disable;
alter
table <table_name> disable <trigger_name>;
alter
table <table_name> disable all triggers;
alter
table <table_name> enable <trigger_name>;
alter
table <table_name> enable all triggers;
drop
trigger <trigger_name>;
------------
Composite Datatypes ------------
---
PL/SQL Records
declare
type order_typ is record(orderno varchar2(5)
not null:= 'o010',vencode
order_master.vencode%type);
item_rec order_typ;
begin
select orderno,vencode into item_rec from
order_master where orderno='o001';
dbms_output.put_line(item_rec.orderno);
dbms_output.put_line(item_rec.vencode);
end;
Program
:
declare
type
order_type is record(ono varchar2(5));
ordrec
order_type;
begin
ordrec.ono:='o001';
dbms_output.put_line(ordrec.ono);
end;
---
PL/SQL Table
--Syntax
TYPE type_name IS TABLE OF
{column_datatype | table.column%type | table%rowtype}
[NOT NULL] [INDEX BY BINARY_INTEGER];
identifier type_name;
Program
:
declare
type itemtype is table of
varchar2(5)
index by binary_integer;
itemtab itemtype;
i binary_integer:=0;
vcode vendor_master.vencode%type;
begin
for
vcode in(select vencode from vendor_master)
loop
i:=i+1;
itemtab(i):=vcode;
dbms_output.put_line(''||itemtab(i));
end
loop;
end;
(notes)
itemtab.delete ->
to delete entire rows
itemtab.delete(3) ->
to delete 3rd the row
--
Member functions and procedures
create
or replace type add_ty as object
(
dno number(5),
stname varchar2(20),
city varchar2(20),
member procedure changeadd(no in number,
st in varchar2,ct in varchar2),
member function getcity return varchar2
);
create
or replace type body add_ty as
member
procedure changeadd(no number,st varchar2,
ct varchar2) is
begin
dno:=no;
stname:=st;
city:=ct;
member function getcity return varchar2 is
begin
return city;
end;
end;
Program
:
declare
add add_ty;
begin
add:=add_ty(100,'rs street','erode');
add.changeadd(10,'s st','salem');
dbms_output.put_line(add.getcity);
end;
Report
:
set
feedback off
ttitle
'Employee|Report'
btitle
'Confidential'
break
on esal
column
eno heading 'EMPNO'
column
ename heading 'EMPNAME'
column
esal heading 'EMPSAL' format 99,999
select
eno,ename,esal from emp
order
by esal
------------------------------------------------End----------------------------------------------------
ALL
THE BEST IN YOUR BOARD EXAMS
