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 .
---------------------------------------------------------------------------
No comments:
Post a Comment