Collections In Oracle

Collections (8.0)
---------------------

 A group of similar rows stored at one location.

2 Types:  

1. Nested Tables   Table with in a table

   A column holds a table of contents in it.

2. Varrying Arrays ( Varrays )    It is an array of elements stored in a column.

Nested Table Example:i). create type book_type as object

    (book_id number(4), title varchar2(20), doi date,

      dor date);

/     

   * Object representing Table of books

ii). create type books is table of book_type;

   

iii). create table stu_lib (roll number(3),

      name varchar2(20), course varchar2(20),

    binfo books) Nested table binfo store as         book_table;

iv) desc book_type

     desc books

     desc stu_lib

v) insert into stu_lib values(101,'RAM','Oracle9i',

 books(book_type(11, 'sql' , sysdate, sysdate + 10),

      book_type(12, 'pl/sql' , sysdate, sysdate + 15),

      book_type(13, 'DBA' , sysdate, sysdate + 20)));

vi) select * from stu_lib;

    select name,binfo from stu_lib;

---------------------------------------------------------------

DML on Nested Table:THE - operator is used to perform DML on Nested table.

It holds the nested table content in buffer memory for manipulation purpose.

Ex:

* Adding a Book ( Only 1 book allowed )insert into

THE(select binfo from stu_lib where roll = 101)

values(book_type(15,'DBA',sysdate,sysdate + 10));

* Changing a Book detailsUpdate

THE(select binfo from stu_lib where roll = 101)

set dor = dor + 3 where book_id = 12;

Update THE(select binfo from stu_lib where roll = 101)

set title = 'Tuning'  where book_id = 15;

* Removing a book infodelete from

THE(select binfo from stu_lib where roll = 101)

 where book_id in = 12;

---------------------------------------------------------------

 Varrying Array Example:i). create type book_type as object

(book_id number(4), title varchar2(20), doi date,

dor date);

/

* Object representing Array of booksii). create type barray is varray(3) of book_type;

iii). create table library (roll number(3),name varchar2(20), course varchar2(20), binfo barray);

iv) desc book_type

     desc barray

     desc library

v) insert into library values(101,'RAM','Oracle9i',

barray(book_type(11, 'sql' , sysdate, sysdate + 10),

      book_type(12, 'pl/sql' , sysdate, sysdate + 15),

    book_type(13, 'DBA' , sysdate, sysdate + 20)));

     

vi) select * from library;

    select name,binfo from library;

Varray Structure:  Libraryroll  name   course   book_id   title   doi   dor

            11

101  ram     oracle      12  

            13

---------------------------------------------------------------

Nested Table               Vs          Varray 

-------------                   ----        -------------

Stored outside the table              Stored with in the table

DML is allowed                          DML Not Allowed

Stores unlimited data                 Stores limited data

Note:

Collections will not support constraints.

Long, RAW, LOB data types are not valid in collections.

Collections will not support Developer 6i,VB (GUI Tools)

JAVA supports collections. 

---------------------------------------------------------------

Ex 2: Varray

create type blist is varray(5) of varchar2(25);

create table items(itno number(3),

name varchar2(20),brands blist);

insert into items values(101,'Television',

blist('SONY','LG','ONIDA','SAMSUNG','TCL'));

insert into items values (102,'Micro-oven',blist('LG','ONIDA','IFB'));

update items set brands = blist('LG','ONIDA','IFB','KENSTAR','SAMSUNG')

where itno = 102;

desc blist

desc items

select * from items;

select name,brands from items;

---------------------------------------------------------------

Temporary Tables (8i)

 ---------------------------

 Used to hold the information in logical memory but  not in physical memory.

 They hold the data for a particular period of time   but not permenantly.

 Ex: 1 Holds the Data until Transaction is Closed

 create global temporary table temp(c1 date)

 on commit delete rows;

 insert into temp values(sysdate);

 select * from temp; --- 17-feb-10

 commit;

 select * from temp; --- no rows

 Ex: 2  Holds the Data until Session is closed

 create global temporary table temp(c1 date)

 on commit preserve rows;

 insert into temp values(sysdate);

 select * from temp; --- 17-feb-10

 commit;

 select * from temp; --- 17-feb-10

 exit --- rows are removed ( Temp will be empty )

---------------------------------------------------------------

 Data Partitioning with Partition Tables: Used to manage huge loads of data in table by  creating  Logical Partitions.

 Improves the performance of Oracle while retrieving or  manipulating data from Huge Tables.

 Ex:   

 create table emp_part (ecode number(2) primary key,

 ename varchar2(20), sal number(10,2))

 partition by range (ecode)

 (partition p1 values less than (11),

 partition p2 values less than (21),

 partition p3 values less than (31),

 partition p4 values less than (41),

 partition p5 values less than (maxvalue));

 Before ALTER :       After ALTER :

 p1 -- 1 - 10             p1 -- 1 - 10 

 p2 -- 11 - 20           p2 -- 11 - 20

 p3 -- 21 - 30           p3 -- 21 - 40

 p4 -- 31 - 40           p4 -- 41 - 50

 p5 -- 41 - N            p5 -- 51 - N

 insert into emp_part values (1,'RAM',12000);

 insert into emp_part values (3,'RAM',13000);

 insert into emp_part values (7,'RAM',17000);

 insert into emp_part values (11,'RAM',2000);

 insert into emp_part values (13,'RAM',3000);

 insert into emp_part values (17,'RAM',7000);

 insert into emp_part values (21,'RAM',22000);

 insert into emp_part values (23,'RAM',23000);

 insert into emp_part values (27,'RAM',27000);

 insert into emp_part values (31,'RAM',10000);

 insert into emp_part values (33,'RAM',14000);

 insert into emp_part values (37,'RAM',15000);

 insert into emp_part values (41,'RAM',31000);

 insert into emp_part values (43,'RAM',33000);

 insert into emp_part values (47,'RAM',37000);

 insert into emp_part values (67,'RAM',57000);

 select * from emp_part;

 select * from emp_part partition (p2);

 select * from emp_part partition (p3)

 where sal > 20000;

 Alter table emp_part merge partitions (p3,p4);

 [ p4 - removed ]

 alter table emp_part drop partition p5;

 alter table emp_part add partition

 p4 values less than (51);

 alter table emp_part add partition p5 values less than  (maxvalue);

---------------------------------------------------------------

 Ref Cursors:

 - Dynamic cursors

 - supports to define the cursor without select statement.

 - select statement will be provided while "opening"      cursor.

 - used to send cursor as an parameter in sub programs.

 - made easy to transfer huge data thru sub programs

  Syntax:  Type <Ref cursor name> is Ref cursor;

 create package pack1 as

 type rcur is ref cursor;

 end pack1;

 ** Procedure returning multiple rows thru OUT parameter  create or replace procedure get_rows(vdept in number,

  vcur out pack1.rcur) is

  begin

  open vcur for select empno,ename,sal,job from emp

  where deptno = vdept;

  end;

  Using Procedure:  declare

  pcur pack1.rcur;

  veno number(4);  vname varchar2(20);

  vsal number(12,2);  vjob varchar2(20);

  begin

  -- calling procedure

  get_rows(30,pcur);

  -- printing cursor contents

  dbms_output.put_line(' Employee Details are ');

  loop

  fetch pcur into veno,vname,vsal,vjob;

  exit when pcur%notfound;

  dbms_output.put_line(veno||'  '||vname||'  '||vsal

  ||'  '||vjob);

  end loop;

  close pcur;

  end;

 Re-using Ref cursor: declare

 scur pack1.rcur;

 vroll number(3); vname varchar2(20); vfee number(5);

 begin

 open scur for select roll,name,fee from student

 where course = '&course';

 loop

 fetch scur into vroll,vname,vfee;

 exit when scur%notfound;

 dopl(vroll||'  '||vname||'  '||vfee);

 end loop;

 close scur;

 end;

---------------------------------------------------------------

Important Topics:Joins, 9i Joins, Constraints, Sub queries, Views, Index,  clusters, Pseudo columns, All 8.0 features, Date  Manipulations, Procedures, Functions, Triggers, Packages,  Utilities, Pragma, Autonomous Transactions, Exception_init,  Ref cursors, Temporary tables, Normalization, Architecture,  Partition Tables, File I/O.

---------------------------------------------------------------

 Roles of an Developer in Oracle : * Designing the database objects using            Normalization .

 * Applying Constraints, Indexes to maintain Data        Integrity while defining objects.

 * Defining code components like Triggers to apply    User defined restrictions in database as per client     requirement

 * Defining Procedures , Functions  &  Packages as      per client requirement .

 * Generating different types of Queries for     reporting purpose.

--------------------------------------------------------------------------

 Versions Features: Oracle 6.0 - It is an DBMS Tool

 Oracle 7.x - It is an RDBMS Tool (7.0 / 7.1 / 7.2 / 7.3)

 Oracle 8.0 - It supports OOPS Features.(ORDBMS) New Features: Objects , Object with Methods, Nested  Tables, Varrying Arrays, Rename, Sharing Columns,  Partition Tables, Key Preserved Table, Inline Views, Scalar  Query, File I/O ,Autonoumus Transactions , Trim, Reverse,  stddev, variance , ceil, floor ,LOBS, Returning into, Bulk  Collect, bulk bind, Rollup, Cube.

 Oracle 8i : It is in_built with JAVA New Features: SQLJ, Iterators, Temporary Tables, Drop  Column, Instead of Triggers, case, Materialized views.

 Oracle 9i : Supports Advanced Features of JAVA. New Features : Supports XML .

 New Date Functions, New General Functions , Multiple  Inserts, Merge, Rename columns & Rename Constraints ,  9i Joins.

 Oracle 10g : Its an DBA version Supports Grid Technology - used to store EJB components  directly into Database .

 Using Aggregates in Returning into clause, spool - Append,  Using Regular Expressions while searching character data .

---------------------------------------------------------------------------