Composite Data Types:( User defined Data Types ) in Oracle

 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
---------------------------------------------------------------