PL/SQL Tutorial

---

PL/SQL Collections

PL/SQL Collections are data structures, which stores temporary data in the memory. These are also called as composite data types. That means Collection Object can store mutiple data type fields and call it as a unit.

Collections are implemented as TYPEs. Programmer first define the type, then declare instance of that type. Each instance of the TYPE is a collection.

There are three Types of collections

• Associate Arrays
• VARRAYS
• Nested Tables

trims collection

Collection Methods

Method	Description
COUNT	Number of elements in the collection
DELETE
EXISTS
EXTEND
FIRST	First element in the collection
LAST	Last element in the collection
LIMIT
PRIOR	Moves pointer to previous element, if pointer is at First location then returns NULL.
NEXT	Moves pointer to next element in the collection,
TRIM

VARRAYS

    VARRAYS are bounded arrays,which has max limit on the number of elements. Indexed by Integer.It contains homogenious data. i.e all elements are of same type. Index starts from 1.
Varrying Arrays can be created at the

• Database Level
• Package level
• Stored Procedures
• Stored Functions
• Anonymous Block

Syntax:

CREATE OR REPLACE TYPE type_name {IS | AS} {VARRAY | VARYING ARRAY} (maxElements) OF element_type  [NOT NULL];    	
     	

Creating VARRAY using constructor

Creating VARRAY using EXTEND method.

Associate Arrays

    Associte Arrays are unbounded arrays,which has no max limit on the number of elements. Indexed by Integer or varchar. It contains homogenious data. i.e all elements are of same type.

Note: Associate Arrays are NOT NULL, even there is no elements in that. it is treated as empty

create or replace NONEDITIONABLE package myPkg
is

 type emp_salary is table of Number index by PLS_INTEGER;

end myPkg;

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

Using FIRST and NEXT methods
Using LAST and PRIOR Methods


declare
  e_salary mypkg.emp_salary;
  idx number;
begin

e_salary(0):=1000;
e_salary(1):=200;
e_salary(2):=300;

  idx:=e_salary.FIRST;
  while idx is not null
  loop
  
  dbms_output.put_line(e_salary(idx));
  
  idx:=e_salary.next(idx);
  end loop;
end;
	
	

declare
  e_salary mypkg.emp_salary;
  idx number;
begin

e_salary(0):=1000;
e_salary(1):=200;
e_salary(2):=300;

  idx:=e_salary.LAST;
  while idx is not null
  loop
  
  dbms_output.put_line(e_salary(idx));
  
  idx:=e_salary.PRIOR(idx);
  end loop;
end;
	
	
	
		
output: 
1000
200
300
output:
300
200
1000
	

Usage of DELETE method

     DELETE is a procedure which accepts 2 parameters or 1 parameter or 0 parameters. Which deletes elements from the Associate Arrays and count will be adjusted.

• e_salary.DELETE

   DELETES all elements in the ASSOCIATE Array, and count will be adjusted to zero.

• e_salary.DELETE(1)

   DELETE 1st index array element.

• e_salary.DELETE(1,2)

   Deletes from index 1 to 2 elements, range of elements . Elements can be contigoues or spased.

Nested Tables

    Nested Tables are unbounded arrays,which has no max limit on the number of elements. Indexed by Integer.It contains homogenious data. i.e all elements are of same type.

Syntax:

     		CREATE OR REPLACE  TYPE  type_name {IS | AS } TABLE OF element_type [NOT NULL];
     	
     	

Create a TYPE at the Schema level gives following error:
"PLS-00355: use of pl/sql table not allowed in this context"