Collections

A collection is nothing more than an array. The most basic collection is a single dimension array.A collection can be loosely defined as a group of ordered elements, all of the same type, that allows programmatic access to its elements through an index.

The Varray is short for Variable Array.A Varray stores elements of the same type in the order in which they are added.The number of elements in a Varray must be known at the time of its declaration.In other words, a Varray has a fixed lower and upper bounds.

Method - Action It Performs
COUNT Returns number of elements in the Collection
EXISTS Returns Boolean
true if element at specified index exists; otherwise, false
EXTEND Increases size of Collection by 1 or number specified, ie.EXTEND(n)
**Cannot use with Associative Array
FIRST Navigates to the first element in the Collection
LAST Navigates to the last element
PRIOR Navigates to the previous element
NEXT Navigates to the next element
TRIM Removes the last element, or the last n elements if a number is specified,
ie. TRIM(n) **Cannot use with Associative Array
DELETE Removes all elements of a Collection, or the nth element, if a parameter is specified
===============================================
Simple table type example
===============================================
DECLARE
v is table of varchar2(10);
v1 v := v();
BEGIN
v1.extend;
v1(1):='Hari';
v1.extend;
v1(2):='Santosh';
v1.extend;
v1(3):='Chandra';
v1.extend;
v1(4):='Jeevan';
for i in v1.first..v1.last
loop
dbms_output.put_line(v1(i));
end loop;
end;
/
============================================================
BULK COLLECT
============================================================
declare
type v is table of emp_bkp%rowtype;
v1 v;
begin
select *
bulk collect into v1
from emp;
forall i in v1.first..v1.last
insert into emp_bkp values v1(i);
end;
============================================================
RECORD and TABLE TYPE
=============================================================
DECLARE
TYPE ssn_rec IS RECORD(ssn NUMBER,name VARCHAR2(30),age NUMBER);
TYPE ssn_tbl1 IS TABLE OF ssn_rec INDEX BY BINARY_INTEGER;
ssn_tbl ssn_tbl1;
BEGIN
ssn_tbl(1).ssn:=223344;
ssn_tbl(1).name:='Hari';
ssn_tbl(1).age:=30;
ssn_tbl(2).ssn:=223345;
ssn_tbl(2).name:='Jeevan';
ssn_tbl(2).age:=31;
ssn_tbl(3).ssn:=223346;
ssn_tbl(3).name:='Ashok';
ssn_tbl(3).age:=29;

FOR i IN ssn_tbl.first .. ssn_tbl.last
LOOP
dbms_output.put_line('Name of the person is'ssn_tbl(i).name);
INSERT INTO test_tab VALUES(ssn_tbl(i).ssn,ssn_tbl(i).name,ssn_tbl(i).age);
END LOOP;
END;
OTHER Queries:
=============
CREATE TABLE test_tab(ssn1 NUMBER,name VARCHAR2(30),age NUMBER)
SELECT * FROM test_tab
DELETE test_tab