PL/SQL Tutorial
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
Method | Description |
COUNT | Count function:returns Number of elements in the collection |
DELETE | DELETE Procedure: Removes one or more elements,reduces the COUNT. |
EXISTS | EXISTS fn:Returns True or False indicates element exists or not |
EXTEND | EXTEND Proc:Increases the number of elements in VARRAY or NESTED TABLE.Increases COUNT |
FIRST | FIRST fn:First element in the collection |
LAST | LAST Fn:Last element in the collection |
LIMIT | LIMIT fn: Returns maximum number of elements allowed in a VARRAY. |
PRIOR | This function,Moves pointer to previous element, if pointer is at First location then returns NULL. |
NEXT | Moves pointer to next element in the collection, |
TRIM | Removes collection elements from the end of the collection |
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.
User wants to limit the Number of elements, Then he can use VARRAY collection
Varrying Arrays can be created at theSyntax:
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.
DELETE
In VARRAY DELETE proc deletes all elements
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.
In Associate Arrays indexes can be negative or positive
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.
DELETES all elements in the ASSOCIATE Array, and count will be adjusted to zero.
DELETE 1st index array element.
Deletes from index 1 to 2 elements, range of elements . Elements can be contigoues or spased.
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];
Method | Description |
= | |
<> | |
[NOT]IN() | |
CORDINALITY(X) | |
CAST(K AS T) | |
COLLECT | |
MULTISET X MULTISET [EXCEPT] [DISTINCT] Y X MULTISET INTERSECT [DISTINCT] Y, X MULTISET UNION [DISTINCT] Y | |
SET(x) X is [NOT] A SET X is [NOT] EMPTY, e [NOT] MEMBER OF X Y [NOT] SUBMULTISET[OF] X |
ADS