Composite Data Types:( User defined Data Types )
- Defined by the user.
- Valid in pl/sql block only.
- They are not stored in the database permenantly.
- They will not hold data.
- They are not re-usabale.
- They will improve performance of Oracle while retrieving or manipulating huge loads of data in pl/sql blocks.
2 Types
i). Pl/Sql Records ii). Pl/sql Tables
1. Pl/sql Records: It is an collection of elements of "Different data types" stored at one location.
It is similar to "C Prog" Structures.
Syntax:
Type <record name> is record
(element1 <datatype>,
element2 <datatype>,
element3 <datatype>,
.....
elementN <datatype>);
2. Pl/Sql Tables It is an collection of elements of "Same data type" stored in continuous memory locations.
It is similar to "C Prog" Arrays
It is supported with an Index automatically
Syntax:
Type <table name> is table of
<data type> index by Binary_integer;
Using Composite Data Types: Ex 1:
declare
-- declaring pl/sql record
type erec is record
( eid number(4),
name emp.ename%type,
basic emp.sal%type,
da emp.sal%type,
hra emp.sal%type,
pf emp.sal%type,
gross emp.sal%type);
e erec; -- record type variable
begin
e.eid := &employ;
select ename,sal into e.name,e.basic from emp
where empno = e.eid;
e.da := e.basic * .25;
e.hra := e.basic * .35;
e.pf := e.basic * .15;
e.gross := e.basic + e.da + e.hra - e.pf;
dbms_output.put_line(e.eid||' '||e.name
||' '||e.basic||' '||e.pf ||' '||e.gross);
end;
Ex 2:
type erec is record ( eid number(4),
name emp.ename%type,
basic emp.sal%type, da emp.sal%type,
hra emp.sal%type, pf emp.sal%type,
gross emp.sal%type, vdept dept.dname%type,
vloc dept.loc%type, totincr number(12));
e erec;
---------------------------------------------------------------
Ex 3: Using Pl/sql Tables declare
-- declaring pl/sql tables
type names is table of emp.ename%type index by binary_integer;
type pays is table of emp.sal%type index by binary_integer;
n names;
p pays;
totsal number(12) := 0;
ctr number(3) := 1;
begin
-- filling tables
for i in (select ename,sal from emp) loop
n(ctr) := i.ename;
p(ctr) := i.sal;
ctr := ctr + 1;
end loop;
-- printing table contents
for k in 1 .. n.count loop
dbms_output.put_line(n(k)||' '||p(k));
totsal := totsal + p(k);
end loop;
dbms_output.put_line('Total salary is :'||totsal);
end;
---------------------------------------------------------------
n.count ---> gives no.of elements in pl/sql table---------------------------------------------------------------
* Pl/sql block using Nested records and Pl/sql Tables using Records:
declare
type pf_rec is record
(pfno number(4), amt number(12,2));
-- Nested Record
type erec is record
(eid number(4), name emp.ename%type,
basic emp.sal%type, job emp.job%type,
pf pf_rec);
-- pl/sql table using records
type etab is table of erec index by binary_integer;
ctr number(3) := 1;
e etab;
begin
for i in (select empno,ename,sal,job from emp) loop
e(ctr).eid := i.empno;
e(ctr).name := i.ename;
e(ctr).basic := i.sal;
e(ctr).job := i.job;
e(ctr).pf.pfno := i.empno + 1000;
e(ctr).pf.amt := round(i.sal * .15);
ctr := ctr + 1;
end loop;
-- Printing table contents
dbms_output.put_line('Employee Pay Details are');
for k in 1 .. e.count loop
dbms_output.put_line(e(k).eid ||' '||e(k).name
||' '||e(k).basic||' '||e(k).job||' '||e(k).pf.pfno
||' '||e(k).pf.amt);
end loop;
end;
e(1) -- eid, name, basic, job, pf.pfno, pf.amt
e(2) -- eid, name, basic, job, pf.pfno, pf.amt
---------------------------------------------------------------
Write a Pl/sql program to check the given username & password are correct. Display the meaningful messages.
User_info --- Table
-----------
Uname --- sridhar , ram , anil
Upin --- sri123 , ram123 , anil123
---------------------------------------------------------------
- Defined by the user.
- Valid in pl/sql block only.
- They are not stored in the database permenantly.
- They will not hold data.
- They are not re-usabale.
- They will improve performance of Oracle while retrieving or manipulating huge loads of data in pl/sql blocks.
2 Types
i). Pl/Sql Records ii). Pl/sql Tables
1. Pl/sql Records: It is an collection of elements of "Different data types" stored at one location.
It is similar to "C Prog" Structures.
Syntax:
Type <record name> is record
(element1 <datatype>,
element2 <datatype>,
element3 <datatype>,
.....
elementN <datatype>);
2. Pl/Sql Tables It is an collection of elements of "Same data type" stored in continuous memory locations.
It is similar to "C Prog" Arrays
It is supported with an Index automatically
Syntax:
Type <table name> is table of
<data type> index by Binary_integer;
Using Composite Data Types: Ex 1:
declare
-- declaring pl/sql record
type erec is record
( eid number(4),
name emp.ename%type,
basic emp.sal%type,
da emp.sal%type,
hra emp.sal%type,
pf emp.sal%type,
gross emp.sal%type);
e erec; -- record type variable
begin
e.eid := &employ;
select ename,sal into e.name,e.basic from emp
where empno = e.eid;
e.da := e.basic * .25;
e.hra := e.basic * .35;
e.pf := e.basic * .15;
e.gross := e.basic + e.da + e.hra - e.pf;
dbms_output.put_line(e.eid||' '||e.name
||' '||e.basic||' '||e.pf ||' '||e.gross);
end;
Ex 2:
type erec is record ( eid number(4),
name emp.ename%type,
basic emp.sal%type, da emp.sal%type,
hra emp.sal%type, pf emp.sal%type,
gross emp.sal%type, vdept dept.dname%type,
vloc dept.loc%type, totincr number(12));
e erec;
---------------------------------------------------------------
Ex 3: Using Pl/sql Tables declare
-- declaring pl/sql tables
type names is table of emp.ename%type index by binary_integer;
type pays is table of emp.sal%type index by binary_integer;
n names;
p pays;
totsal number(12) := 0;
ctr number(3) := 1;
begin
-- filling tables
for i in (select ename,sal from emp) loop
n(ctr) := i.ename;
p(ctr) := i.sal;
ctr := ctr + 1;
end loop;
-- printing table contents
for k in 1 .. n.count loop
dbms_output.put_line(n(k)||' '||p(k));
totsal := totsal + p(k);
end loop;
dbms_output.put_line('Total salary is :'||totsal);
end;
---------------------------------------------------------------
n.count ---> gives no.of elements in pl/sql table---------------------------------------------------------------
* Pl/sql block using Nested records and Pl/sql Tables using Records:
declare
type pf_rec is record
(pfno number(4), amt number(12,2));
-- Nested Record
type erec is record
(eid number(4), name emp.ename%type,
basic emp.sal%type, job emp.job%type,
pf pf_rec);
-- pl/sql table using records
type etab is table of erec index by binary_integer;
ctr number(3) := 1;
e etab;
begin
for i in (select empno,ename,sal,job from emp) loop
e(ctr).eid := i.empno;
e(ctr).name := i.ename;
e(ctr).basic := i.sal;
e(ctr).job := i.job;
e(ctr).pf.pfno := i.empno + 1000;
e(ctr).pf.amt := round(i.sal * .15);
ctr := ctr + 1;
end loop;
-- Printing table contents
dbms_output.put_line('Employee Pay Details are');
for k in 1 .. e.count loop
dbms_output.put_line(e(k).eid ||' '||e(k).name
||' '||e(k).basic||' '||e(k).job||' '||e(k).pf.pfno
||' '||e(k).pf.amt);
end loop;
end;
e(1) -- eid, name, basic, job, pf.pfno, pf.amt
e(2) -- eid, name, basic, job, pf.pfno, pf.amt
---------------------------------------------------------------
Write a Pl/sql program to check the given username & password are correct. Display the meaningful messages.
User_info --- Table
-----------
Uname --- sridhar , ram , anil
Upin --- sri123 , ram123 , anil123
---------------------------------------------------------------
No comments:
Post a Comment