COLLECTIONS

COLLECTIONS

A collection is an ordered group of elements having the same data type. Each element is identified by a unique subscript that represents its position in the collection.

PL/SQL provides three collection types:

Index-by tables or Associative array

Nested table

Variable-size array or Varray

 

Collection Type Number of Elements Subscript Type Dense or Sparse Where Created Can Be Object Type Attribute
Associative array (or index-by table) Unbounded String or integer Either Only in PL/SQL block No
Nested table Unbounded Integer Starts dense, can become sparse Either in PL/SQL block or at schema level Yes
Variable-size array (Varray) Bounded Integer Always dense Either in PL/SQL block or at schema level Yes

 

Index-By Table (Associative Array)

Only at PLSQL level. Either dense or sparse.

 

An index-by table (also called an associative array) is a set of key-value pairs. Each key is unique and is used to locate the corresponding value. The key can be either an integer or a string.

 

An index-by table is created using the following syntax. Here, we are creating an index-by table named table_name whose keys will be of subscript_type and associated values will be of element_type.

 

TYPE type_name IS TABLE OF element_type [NOT NULL] INDEX BY subscript_type;

table_name type_name;

 

DECLARE

CURSOR c_customers is

select  name from customers;

 

TYPE c_list IS TABLE of customers.name%type INDEX BY binary_integer;

name_list c_list;

counter integer :=0;

BEGIN

FOR n IN c_customers LOOP

counter := counter +1;

name_list(counter)  := n.name;

dbms_output.put_line(‘Customer(‘||counter|| ‘):’||name_list(counter));

END LOOP;

END;

/

 

Nested Tables

A nested table is like a one-dimensional array with an arbitrary number of elements. However, a nested table differs from an array in the following aspects:

-An array has a declared number of elements, but a nested table does not. The size of a nested table can increase dynamically.

-An array is always dense, i.e., it always has consecutive subscripts. A nested array is dense initially, but it can become sparse when elements are deleted from it.

 

A nested table is created using the following syntax:

TYPE type_name IS TABLE OF element_type [NOT NULL];

table_name type_name;

 

This declaration is similar to declaration of an index-by table, but there is no INDEX BY clause.

 

A nested table can be stored in a database column and so it could be used for simplifying SQL operations where you join a single-column table with a larger table. An associative array cannot be stored in the database.

 

Elements of a nested table could also be a %ROWTYPE of any database table or %TYPE of any database table field.

DECLARE

CURSOR c_customers is

SELECT  name FROM customers;

 

TYPE c_list IS TABLE of customers.name%type;

name_list c_list := c_list();

counter integer :=0;

BEGIN

FOR n IN c_customers LOOP

counter := counter +1;

name_list.extend;

name_list(counter)  := n.name;

dbms_output.put_line(‘Customer(‘||counter||’):’||name_list(counter));

END LOOP;

END;

/

Perform DML Operation on Nested table or Using Nested Table in SQL Query

You can perform DML operations on nested tables only if they are defined as columns of a table.the collection expression must be a subquery that uses the TABLE function to select the nested table column of the table.

Suppose the database contains a table hr_info with columns department_id, location_id, and manager_id, and a column of nested table type people which has last_name, department_id, and salary columns for all the employees of each respective manager:

 

CREATE TYPE people_typ AS OBJECT (

last_name      VARCHAR2(25),

department_id  NUMBER(4),

salary         NUMBER(8,2));

/

CREATE TYPE people_tab_typ AS TABLE OF people_typ;

/

CREATE TABLE hr_info (

department_id   NUMBER(4),

location_id     NUMBER(4),

manager_id      NUMBER(6),

people          people_tab_typ)

NESTED TABLE people STORE AS people_stor_tab;

 

INSERT INTO hr_info VALUES (280, 1800, 999, people_tab_typ());

 

The following example inserts into the people nested table column of the hr_info table for department 280:

 

INSERT INTO TABLE(SELECT h.people FROM hr_info h

WHERE h.department_id = 280)

VALUES (‘Smith’, 280, 1750);

 

The next example updates the department 280 people nested table:

 

UPDATE TABLE(SELECT h.people FROM hr_info h

WHERE h.department_id = 280) p

SET p.salary = p.salary + 100;

 

The next example deletes from the department 280 people nested table:

 

DELETE TABLE(SELECT h.people FROM hr_info h

WHERE h.department_id = 280) p

WHERE p.salary > 1700;

 

Collection Unnesting: Examples

To select data from a nested table column, use the TABLE function to treat the nested table as columns of a table. This process is called collection unnesting.

 

You could get all the rows from hr_info, which was created in the preceding example, and all the rows from the people nested table column of hr_info using the following statement:

 

SELECT t1.department_id, t2.* FROM hr_info t1, TABLE(t1.people) t2

WHERE t2.department_id = t1.department_id;

 

Now suppose that people is not a nested table column of hr_info, but is instead a separate table with columns last_name, department_id, address, hiredate, and salary. You can extract the same rows as in the preceding example with this statement:

 

SELECT t1.department_id, t2.*

FROM hr_info t1, TABLE(CAST(MULTISET(

SELECT t3.last_name, t3.department_id, t3.salary

FROM people t3

WHERE t3.department_id = t1.department_id)

AS people_tab_typ)) t2;

 

Finally, suppose that people is neither a nested table column of table hr_info nor a table itself. Instead, you have created a function people_func that extracts from various sources the name, department, and salary of all employees. You can get the same information as in the preceding examples with the following query:

 

SELECT t1.department_id, t2.* FROM hr_info t1, TABLE(CAST

(people_func( … ) AS people_tab_typ)) t2;

 

VARRAY

 

PL/SQL programming language provides a data structure called the VARRAY, which can store a fixed-size sequential collection of elements of the same type. Each element in a varray has an index associated with it. It also has a maximum size that can be changed dynamically. A varray type is created with the CREATE TYPE statement. You must specify the maximum size and the type of elements stored in the varray.

 

The basic syntax for creating a VRRAY type at the schema level is:

CREATE OR REPLACE TYPE varray_type_name IS VARRAY(n) of <element_type>

 

Maximum size of a varray can be changed using the ALTER TYPE statement.

 

CREATE OR REPLACE TYPE namearray AS VARRAY(3) OF VARCHAR2(10);–at schema level

 

TYPE namearray IS VARRAY(5) OF VARCHAR2(10); –in plsql block

Type grades IS VARRAY(5) OF INTEGER; –in plsql block

 

DECLARE

CURSOR c_customers is

SELECT  name FROM customers;

type c_list is varray (6) of customers.name%type;

name_list c_list := c_list();

counter integer :=0;

BEGIN

FOR n IN c_customers LOOP

counter := counter + 1;

name_list.extend;

name_list(counter)  := n.name;

dbms_output.put_line(‘Customer(‘||counter ||’):’||name_list(counter));

END LOOP;

END;

/

 

Collection Methods

 

 

S.N. Method Name & Purpose
1 EXISTS(n)

Returns TRUE if the nth element in a collection exists; otherwise returns FALSE.

2 COUNT

Returns the number of elements that a collection currently contains.

3 LIMIT

Checks the Maximum Size of a Collection.

4 FIRST

Returns the first (smallest) index numbers in a collection that uses integer subscripts.

5 LAST

Returns the last (largest) index numbers in a collection that uses integer subscripts.

6 PRIOR(n)

Returns the index number that precedes index n in a collection.

7 NEXT(n)

Returns the index number that succeeds index n.

8 EXTEND

Appends one null element to a collection.

9 EXTEND(n)

Appends n null elements to a collection.

10 EXTEND(n,i)

Appends n copies of the ith element to a collection.

11 TRIM

Removes one element from the end of a collection.

12 TRIM(n)

Removes n elements from the end of a collection.

13 DELETE

Removes all elements from a collection, setting COUNT to 0.

14 DELETE(n)

Removes the nth element from an associative array with a numeric key or a nested table. If the associative array has a string key, the element corresponding to the key value is deleted. If n is null, DELETE(n) does nothing.

15 DELETE(m,n)

Removes all elements in the range m..n from an associative array or nested table. If m is larger than n or if m or n is null, DELETE(m,n) does nothing.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s