Collections in Oracle PL/SQL

Oracle uses collections in PL/SQL the same way other languages use arrays. Oracle provides three basic collections, each with an assortment of methods.

Index-By Tables (Associative Arrays)
Nested Table
Varrays
Assignments and Equality Tests
Collection Methods
MULTISET Conditions
SUBMULTISET Condition
MEMBER Condition
Multidimensional Collections

Index-By Tables (Associative Arrays)

The first type of collection is known as index-by tables. These behave in the same way as arrays except that have no upper bounds, allowing them to constantly extend. As the name implies, the collection is indexed using BINARY_INTEGER values, which do not need to be consecutive. The collection is extended by assigning values to an element using an index value that does not currently exist.

SET SERVEROUTPUT ON SIZE 1000000
DECLARE
  TYPE table_type IS TABLE OF NUMBER(10)
    INDEX BY BINARY_INTEGER;
  
  v_tab  table_type;
  v_idx  NUMBER;
BEGIN
  -- Initialise the collection.
  << load_loop >>
  FOR i IN 1 .. 5 LOOP
    v_tab(i) := i;
  END LOOP load_loop;
  
  -- Delete the third item of the collection.
  v_tab.DELETE(3);
  
  -- Traverse sparse collection
  v_idx := v_tab.FIRST;
  << display_loop >>
  WHILE v_idx IS NOT NULL LOOP
    DBMS_OUTPUT.PUT_LINE('The number ' || v_tab(v_idx));
    v_idx := v_tab.NEXT(v_idx);
  END LOOP display_loop;
END;
/

The number 1
The number 2
The number 4
The number 5

PL/SQL procedure successfully completed.

A PL/SQL record is a data structure that can hold data items of different kinds. Records consist of different fields, similar to a row of a database table.
For example, you want to keep track of your books in a library. You might want to track the following attributes about each book like, Title, Author, Subject, Book ID. A record containing a field for each of these items allows treating a BOOK as a logical unit and allows you to organize and represent its information in a better way.
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

Oracle documentation provides the following characteristics for each type of collections:

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
We have already discussed varray in the chapter 'PL/SQL arrays'. In this chapter, we will discuss PL/SQL tables.

Both types of PL/SQL tables, i.e., index-by tables and nested tables have the same structure and their rows are accessed using the subscript notation. However, these two types of tables differ in one aspect; the nested tables can be stored in a database column and the index-by tables cannot.

Index-By Table
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;
Example:
Following example shows how to create a table to store integer values along with names and later it prints the same list of names.

DECLARE
TYPE salary IS TABLE OF NUMBER INDEX BY VARCHAR2(20);
salary_list salary;
name VARCHAR2(20);
BEGIN
-- adding elements to the table
salary_list('Rajnish') := 62000;
salary_list('Minakshi') := 75000;
salary_list('Martin') := 100000;
salary_list('James') := 78000;

-- printing the table
name := salary_list.FIRST;
WHILE name IS NOT null LOOP
dbms_output.put_line
('Salary of ' || name || ' is ' || TO_CHAR(salary_list(name)));
name := salary_list.NEXT(name);
END LOOP;
END;
/
When the above code is executed at SQL prompt, it produces the following result:

Salary of Rajnish is 62000
Salary of Minakshi is 75000
Salary of Martin is 100000
Salary of James is 78000

PL/SQL procedure successfully completed.
Example:
Elements of an index-by table could also be a %ROWTYPE of any database table or %TYPE of any database table field. The following example illustrates the concept. We will use the CUSTOMERS table stored in our database as:

Select * from customers;

+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
+----+----------+-----+-----------+----------+
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;
/
When the above code is executed at SQL prompt, it produces the following result:

Customer(1): Ramesh
Customer(2): Khilan
Customer(3): kaushik
Customer(4): Chaitali
Customer(5): Hardik
Customer(6): Komal

PL/SQL procedure successfully completed
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.

Example:
The following examples illustrate the use of nested table:

DECLARE
TYPE names_table IS TABLE OF VARCHAR2(10);
TYPE grades IS TABLE OF INTEGER;

names names_table;
marks grades;
total integer;
BEGIN
names := names_table('Kavita', 'Pritam', 'Ayan', 'Rishav', 'Aziz');
marks:= grades(98, 97, 78, 87, 92);
total := names.count;
dbms_output.put_line('Total '|| total || ' Students');
FOR i IN 1 .. total LOOP
dbms_output.put_line('Student:'||names(i)||', Marks:' || marks(i));
end loop;
END;
/
When the above code is executed at SQL prompt, it produces the following result:

Total 5 Students
Student:Kavita, Marks:98
Student:Pritam, Marks:97
Student:Ayan, Marks:78
Student:Rishav, Marks:87
Student:Aziz, Marks:92

PL/SQL procedure successfully completed.
Example:
Elements of a nested table could also be a %ROWTYPE of any database table or %TYPE of any database table field. The following example illustrates the concept. We will use the CUSTOMERS table stored in our database as:

Select * from customers;

+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
+----+----------+-----+-----------+----------+
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;
/
When the above code is executed at SQL prompt, it produces the following result:

Customer(1): Ramesh
Customer(2): Khilan
Customer(3): kaushik
Customer(4): Chaitali
Customer(5): Hardik
Customer(6): Komal

PL/SQL procedure successfully completed.
Collection Methods
PL/SQL provides the built-in collection methods that make collections easier to use. The following table lists the methods and their purpose:

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.
Collection Exceptions
The following table provides the collection exceptions and when they are raised:

Collection Exception Raised in Situations
COLLECTION_IS_NULL You try to operate on an atomically null collection.
NO_DATA_FOUND A subscript designates an element that was deleted, or a nonexistent element of an associative array.
SUBSCRIPT_BEYOND_COUNT A subscript exceeds the number of elements in a collection.
SUBSCRIPT_OUTSIDE_LIMIT A subscript is outside the allowed range.
VALUE_ERROR A subscript is null or not convertible to the key type. This exception might occur if the key is defined as a PLS_INTEGER range, and the subscript is outside this range.
PL/SQL can handle the following types of records:

Table-based
Cursor-based records
User-defined records
Table-Based Records
The %ROWTYPE attribute enables a programmer to create table-based and cursor-based records.
The following example would illustrate the concept of table-based records. We will be using the CUSTOMERS table we had created and used in the previous chapters:

DECLARE
customer_rec customers%rowtype;
BEGIN
SELECT * into customer_rec
FROM customers
WHERE id = 5;

dbms_output.put_line('Customer ID: ' || customer_rec.id);
dbms_output.put_line('Customer Name: ' || customer_rec.name);
dbms_output.put_line('Customer Address: ' || customer_rec.address);
dbms_output.put_line('Customer Salary: ' || customer_rec.salary);
END;
/
When the above code is executed at SQL prompt, it produces the following result:

Customer ID: 5
Customer Name: Hardik
Customer Address: Bhopal
Customer Salary: 9000

PL/SQL procedure successfully completed.
Cursor-Based Records
The following example would illustrate the concept of cursor-based records. We will be using the CUSTOMERS table we had created and used in the previous chapters:

DECLARE
CURSOR customer_cur is
SELECT id, name, address
FROM customers;
customer_rec customer_cur%rowtype;
BEGIN
OPEN customer_cur;
LOOP
FETCH customer_cur into customer_rec;
EXIT WHEN customer_cur%notfound;
DBMS_OUTPUT.put_line(customer_rec.id || ' ' || customer_rec.name);
END LOOP;
END;
/
When the above code is executed at SQL prompt, it produces the following result:
1 Ramesh
2 Khilan
3 kaushik
4 Chaitali
5 Hardik
6 Komal

PL/SQL procedure successfully completed.
User-Defined Records
PL/SQL provides a user-defined record type that allows you to define different record structures. Records consist of different fields. Suppose you want to keep track of your books in a library. You might want to track the following attributes about each book:

Title
Author
Subject
Book ID

Defining a Record
The record type is defined as:

TYPE
type_name IS RECORD
( field_name1 datatype1 [NOT NULL] [:= DEFAULT EXPRESSION],
field_name2 datatype2 [NOT NULL] [:= DEFAULT EXPRESSION],
...
field_nameN datatypeN [NOT NULL] [:= DEFAULT EXPRESSION);
record-name type_name;
Here is the way you would declare the Book record:

DECLARE
TYPE books IS RECORD
(title varchar(50),
author varchar(50),
subject varchar(100),
book_id number);
book1 books;
book2 books;
Accessing Fields
To access any field of a record, we use the dot (.) operator. The member access operator is coded as a period between the record variable name and the field that we wish to access. Following is the example to explain usage of record:

DECLARE
type books is record
(title varchar(50),
author varchar(50),
subject varchar(100),
book_id number);
book1 books;
book2 books;
BEGIN
-- Book 1 specification
book1.title := 'C Programming';
book1.author := 'Nuha Ali ';
book1.subject := 'C Programming Tutorial';
book1.book_id := 6495407;

-- Book 2 specification
book2.title := 'Telecom Billing';
book2.author := 'Zara Ali';
book2.subject := 'Telecom Billing Tutorial';
book2.book_id := 6495700;

-- Print book 1 record
dbms_output.put_line('Book 1 title : '|| book1.title);
dbms_output.put_line('Book 1 author : '|| book1.author);
dbms_output.put_line('Book 1 subject : '|| book1.subject);
dbms_output.put_line('Book 1 book_id : ' || book1.book_id);

-- Print book 2 record
dbms_output.put_line('Book 2 title : '|| book2.title);
dbms_output.put_line('Book 2 author : '|| book2.author);
dbms_output.put_line('Book 2 subject : '|| book2.subject);
dbms_output.put_line('Book 2 book_id : '|| book2.book_id);
END;
/
When the above code is executed at SQL prompt, it produces the following result:

Book 1 title : C Programming
Book 1 author : Nuha Ali
Book 1 subject : C Programming Tutorial
Book 1 book_id : 6495407
Book 2 title : Telecom Billing
Book 2 author : Zara Ali
Book 2 subject : Telecom Billing Tutorial
Book 2 book_id : 6495700

PL/SQL procedure successfully completed.
Records as Subprogram Parameters
You can pass a record as a subprogram parameter in very similar way as you pass any other variable. You would access the record fields in the similar way as you have accessed in the above example:

DECLARE
type books is record
(title varchar(50),
author varchar(50),
subject varchar(100),
book_id number);
book1 books;
book2 books;

PROCEDURE printbook (book books) IS
BEGIN
dbms_output.put_line ('Book title : ' || book.title);
dbms_output.put_line('Book author : ' || book.author);
dbms_output.put_line( 'Book subject : ' || book.subject);
dbms_output.put_line( 'Book book_id : ' || book.book_id);
END;

BEGIN
-- Book 1 specification
book1.title := 'C Programming';
book1.author := 'Nuha Ali ';
book1.subject := 'C Programming Tutorial';
book1.book_id := 6495407;

-- Book 2 specification
book2.title := 'Telecom Billing';
book2.author := 'Zara Ali';
book2.subject := 'Telecom Billing Tutorial';
book2.book_id := 6495700;

-- Use procedure to print book info
printbook(book1);
printbook(book2);
END;
/
When the above code is executed at SQL prompt, it produces the following result:

Book title : C Programming
Book author : Nuha Ali
Book subject : C Programming Tutorial
Book book_id : 6495407
Book title : Telecom Billing
Book author : Zara Ali
Book subject : Telecom Billing Tutorial
Book book_id : 6495700

PL/SQL procedure successfully completed.
------

Collection Methods

A collection method is a built-in function or procedure that operates on collections and is called using dot notation. You can use the methods EXISTS, COUNT, LIMIT, FIRST, LAST, PRIOR, NEXT, EXTEND, TRIM, and DELETE to manage collections whose size is unknown or varies.

EXISTS, COUNT, LIMIT, FIRST, LAST, PRIOR, and NEXT are functions that check the properties of a collection or individual collection elements. EXTEND, TRIM, and DELETE are procedures that modify a collection.

EXISTS, PRIOR, NEXT, TRIM, EXTEND, and DELETE take integer parameters. EXISTS, PRIOR, NEXT, and DELETE can also take VARCHAR2 parameters for associative arrays with string keys. EXTEND and TRIM cannot be used with index-by tables.

Syntax

collection call method ::=

Keyword and Parameter Description

collection_name

An associative array, nested table, or varray previously declared within the current scope.

COUNT

Returns the number of elements that a collection currently contains, which is useful because the current size of a collection is not always known. You can use COUNT wherever an integer expression is allowed. For varrays, COUNT always equals LAST. For nested tables, normally, COUNT equals LAST. But, if you delete elements from the middle of a nested table, COUNT is smaller than LAST.

DELETE

This procedure has three forms. DELETE removes all elements from a collection. DELETE(n) removes the nth element from an associative array or nested table. If n is null, DELETE(n) does nothing. 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.

EXISTS

EXISTS(n) returns TRUE if the nth element in a collection exists. Otherwise, EXISTS(n) returns FALSE. Mainly, you use EXISTS with DELETE to maintain sparse nested tables. You can also use EXISTS to avoid raising an exception when you reference a nonexistent element. When passed an out-of-range subscript, EXISTS returns FALSE instead of raising SUBSCRIPT_OUTSIDE_LIMIT.

EXTEND

This procedure has three forms. EXTEND appends one null element to a collection. EXTEND(n) appends n null elements to a collection. EXTEND(n,i) appends n copies of the ith element to a collection. EXTEND operates on the internal size of a collection. If EXTEND encounters deleted elements, it includes them in its tally. You cannot use EXTEND with associative arrays.

FIRST, LAST

FIRST and LAST return the first and last (smallest and largest) subscript values in a collection. The subscript values are usually integers, but can also be strings for associative arrays. If the collection is empty, FIRST and LAST return NULL. If the collection contains only one element, FIRST and LAST return the same subscript value. For varrays, FIRST always returns 1 and LAST always equals COUNT. For nested tables, normally, LAST equals COUNT. But, if you delete elements from the middle of a nested table, LAST is larger than COUNT.

index

An expression that must return (or convert implicitly to) an integer in most cases, or a string for an associative array declared with string keys.

LIMIT

For nested tables, which have no maximum size, LIMIT returns NULL. For varrays, LIMIT returns the maximum number of elements that a varray can contain (which you must specify in its type definition).

NEXT, PRIOR

PRIOR(n) returns the subscript that precedes index n in a collection. NEXT(n) returns the subscript that succeeds index n. If n has no predecessor, PRIOR(n) returns NULL. Likewise, if n has no successor, NEXT(n) returns NULL.

TRIM

This procedure has two forms. TRIM removes one element from the end of a collection. TRIM(n) removes n elements from the end of a collection. If n is greater than COUNT, TRIM(n) raises SUBSCRIPT_BEYOND_COUNT. You cannot use TRIM with index-by tables.TRIM operates on the internal size of a collection. If TRIM encounters deleted elements, it includes them in its tally.

Usage Notes

You cannot use collection methods in a SQL statement. If you try, you get a compilation error.

Only EXISTS can be applied to atomically null collections. If you apply another method to such collections, PL/SQL raises COLLECTION_IS_NULL.

If the collection elements have sequential subscripts, you can use collection.FIRST .. collection.LAST in a FOR loop to iterate through all the elements. You can use PRIOR or NEXT to traverse collections indexed by any series of subscripts. For example, you can use PRIOR or NEXT to traverse a nested table from which some elements have been deleted, or an associative array where the subscripts are string values.

EXTEND operates on the internal size of a collection, which includes deleted elements. You cannot use EXTEND to initialize an atomically null collection. Also, if you impose the NOT NULL constraint on a TABLE or VARRAY type, you cannot apply the first two forms of EXTEND to collections of that type.

If an element to be deleted does not exist, DELETE simply skips it; no exception is raised. Varrays are dense, so you cannot delete their individual elements. Because PL/SQL keeps placeholders for deleted elements, you can replace a deleted element by assigning it a new value. However, PL/SQL does not keep placeholders for trimmed elements.

The amount of memory allocated to a nested table can increase or decrease dynamically. As you delete elements, memory is freed page by page. If you delete the entire table, all the memory is freed.

In general, do not depend on the interaction between TRIM and DELETE. It is better to treat nested tables like fixed-size arrays and use only DELETE, or to treat them like stacks and use only TRIM and EXTEND.

Within a subprogram, a collection parameter assumes the properties of the argument bound to it. You can apply methods FIRST, LAST, COUNT, and so on to such parameters. For varray parameters, the value of LIMIT is always derived from the parameter type definition, regardless of the parameter mode.

For varrays, FIRST always returns 1 and LAST always equals COUNT.
For nested tables, normally FIRST returns 1 and LAST equals COUNT. But if you delete elements from the beginning of a nested table, FIRST returns a number larger than 1. If you delete elements from the middle of a nested table, LAST becomes larger than COUNT.
When scanning elements, FIRST and LAST ignore deleted elements.

Varray Collections

A VARRAY is similar to a nested table except you must specifiy an upper bound in the declaration. Like nested tables they can be stored in the database, but unlike nested tables individual elements cannot be deleted so they remain dense.

SET SERVEROUTPUT ON SIZE 1000000
DECLARE
  TYPE table_type IS VARRAY(5) OF NUMBER(10);
  v_tab  table_type;
  v_idx  NUMBER;
BEGIN
  -- Initialise the collection with two values.
  v_tab := table_type(1, 2);

  -- Extend the collection with extra values.
  << load_loop >>
  FOR i IN 3 .. 5 LOOP
    v_tab.extend;
    v_tab(v_tab.last) := i;
  END LOOP load_loop;
  
  -- Can't delete from a VARRAY.
  -- v_tab.DELETE(3);

  -- Traverse collection
  v_idx := v_tab.FIRST;
  << display_loop >>
  WHILE v_idx IS NOT NULL LOOP
    DBMS_OUTPUT.PUT_LINE('The number ' || v_tab(v_idx));
    v_idx := v_tab.NEXT(v_idx);
  END LOOP display_loop;
END;
/

The number 1
The number 2
The number 3
The number 4
The number 5

PL/SQL procedure successfully completed.

---

Assignments and Equality Tests

Assignments can only be made between collections of the same type. Not types of similar structures, or with the same name in different packages, but literally the same type.
The following example shows a successful assignment between two collections of the same type.
DECLARE
TYPE table_type IS TABLE OF NUMBER(10);
v_tab_1 table_type; v_tab_2 table_type;
BEGIN
-- Initialise the collection with two values.
v_tab_1 := table_type(1, 2);
-- Assignment works.
v_tab_2 := v_tab_1;
END;
/
PL/SQL procedure successfully completed.
------------

Collection Methods

A variety of methods exist for collections, but not all are relevant for every collection type.

EXISTS(n) - Returns TRUE if the specified element exists.
COUNT - Returns the number of elements in the collection.
LIMIT - Returns the maximum number of elements for a VARRAY, or NULL for nested tables.
FIRST - Returns the index of the first element in the collection.
LAST - Returns the index of the last element in the collection.
PRIOR(n) - Returns the index of the element prior to the specified element.
NEXT(n) - Returns the index of the next element after the specified element.
EXTEND - Appends a single null element to the collection.
EXTEND(n) - Appends n null elements to the collection.
EXTEND(n1,n2) - Appends n1 copies of the n2th element to the collection.
TRIM - Removes a single element from the end of the collection.
TRIM(n) - Removes n elements from the end of the collection.
DELETE - Removes all elements from the collection.
DELETE(n) - Removes element n from the collection.
DELETE(n1,n2) - Removes all elements from n1 to n2 from the collection.

MULTISET Conditions

Oracle provides MULTISET conditions against collections, including the following.
MULTISET UNION joins the two collections together, doing the equivalent of a UNION ALL between the two sets.

DECLARE
TYPE t_tab IS TABLE OF NUMBER;
l_tab1 t_tab := t_tab(1,2,3,4,5,6);
l_tab2 t_tab := t_tab(5,6,7,8,9,10);
BEGIN
l_tab1 := l_tab1 MULTISET UNION l_tab2;

FOR i IN l_tab1.first .. l_tab1.last LOOP
DBMS_OUTPUT.put_line(l_tab1(i));
END LOOP;
END;

The DISTINCT keyword can be added to any of the multiset operations to removes the duplicates. Adding it to the MULTISET UNION makes it the equivalent of a UNION between the two sets.

SET SERVEROUTPUT ON
DECLARE
  TYPE t_tab IS TABLE OF NUMBER;
  l_tab1 t_tab := t_tab(1,2,3,4,5,6);
  l_tab2 t_tab := t_tab(5,6,7,8,9,10);
BEGIN
  l_tab1 := l_tab1 MULTISET UNION DISTINCT l_tab2;
  
  FOR i IN l_tab1.first .. l_tab1.last LOOP
    DBMS_OUTPUT.put_line(l_tab1(i));
  END LOOP;
END;
/
1
2
3
4
5
6
7
8
9
10

PL/SQL procedure successfully completed.

MULTISET EXCEPT returns the elements of the first set that are not present in the second set.

SET SERVEROUTPUT ON
DECLARE
  TYPE t_tab IS TABLE OF NUMBER;
  l_tab1 t_tab := t_tab(1,2,3,4,5,6,7,8,9,10);
  l_tab2 t_tab := t_tab(6,7,8,9,10);
BEGIN
  l_tab1 := l_tab1 MULTISET EXCEPT l_tab2;
  
  FOR i IN l_tab1.first .. l_tab1.last LOOP
    DBMS_OUTPUT.put_line(l_tab1(i));
  END LOOP;
END;
/
1
2
3
4
5

PL/SQL procedure successfully completed.

MULTISET INTERSECT returns the elements that are present in both sets.
DECLARE
TYPE t_tab IS TABLE OF NUMBER;
l_tab1 t_tab := t_tab(1,2,3,4,5,6,7,8,9,10);
l_tab2 t_tab := t_tab(6,7,8,9,10);
BEGIN
l_tab1 := l_tab1 MULTISET INTERSECT l_tab2;

FOR i IN l_tab1.first .. l_tab1.last LOOP
DBMS_OUTPUT.put_line(l_tab1(i));
END LOOP;
END;

/
6
7
8
9
10

PL/SQL procedure successfully completed.

MEMBER Condition

The MEMBER condition allows you to test if an element is member of a collection.

DECLARE
  TYPE t_tab IS TABLE OF NUMBER;
  l_tab1 t_tab := t_tab(1,2,3,4,5);
BEGIN
  DBMS_OUTPUT.put('Is 3 MEMBER OF l_tab1? ');
  IF 3 MEMBER OF l_tab1 THEN
    DBMS_OUTPUT.put_line('TRUE');
  ELSE
    DBMS_OUTPUT.put_line('FALSE');  
  END IF;
END;

----

Multidimensional Collections

In addition to regular data types, collections can be based on record types, allowing the creation of two-dimensional collections.

DECLARE
  TYPE t_row IS RECORD (
    id  NUMBER,
    description VARCHAR2(50)
  );

  TYPE t_tab IS TABLE OF t_row;
  l_tab t_tab := t_tab();
BEGIN
  FOR i IN 1 .. 10 LOOP
    l_tab.extend();
    l_tab(l_tab.last).id := i;
    l_tab(l_tab.last).description := 'Description for ' || i;
    :result := :result||' '||l_tab(l_tab.last).id||'    -    '||l_tab(l_tab.last).description||chr(10);
  END LOOP;
END;

Pages

Record & Collection

Collections in Oracle PL/SQL

Index-By Tables (Associative Arrays)

Varray Collections

Assignments and Equality Tests

Collection Methods

MULTISET Conditions

DECLARE
TYPE t_tab IS TABLE OF NUMBER;
l_tab1 t_tab := t_tab(1,2,3,4,5,6);
l_tab2 t_tab := t_tab(5,6,7,8,9,10);
BEGIN
l_tab1 := l_tab1 MULTISET UNION l_tab2;

FOR i IN l_tab1.first .. l_tab1.last LOOP
DBMS_OUTPUT.put_line(l_tab1(i));
END LOOP;
END;

Multidimensional Collections

No comments:

Post a Comment

Interesting Links

Pages

Record & Collection

Collections in Oracle PL/SQL

Index-By Tables (Associative Arrays)

Varray Collections

Assignments and Equality Tests

Collection Methods

MULTISET Conditions

DECLARE TYPE t_tab IS TABLE OF NUMBER; l_tab1 t_tab := t_tab(1,2,3,4,5,6); l_tab2 t_tab := t_tab(5,6,7,8,9,10);BEGIN l_tab1 := l_tab1 MULTISET UNION l_tab2; FOR i IN l_tab1.first .. l_tab1.last LOOP DBMS_OUTPUT.put_line(l_tab1(i)); END LOOP;END;

Multidimensional Collections

No comments:

Post a Comment

Interesting Links

DECLARE
TYPE t_tab IS TABLE OF NUMBER;
l_tab1 t_tab := t_tab(1,2,3,4,5,6);
l_tab2 t_tab := t_tab(5,6,7,8,9,10);
BEGIN
l_tab1 := l_tab1 MULTISET UNION l_tab2;

FOR i IN l_tab1.first .. l_tab1.last LOOP
DBMS_OUTPUT.put_line(l_tab1(i));
END LOOP;
END;