Oracle PL/SQL Collections

Yusuf SEZER October 11, 2020 ORACLE

This article contains information about Oracle PL/SQL Collections, types such as Associative Array, Nested Table and Varray, also collection methods.

What is Collection Oracle?

Collections are data types where values with a similar data type are stored and the capacity of the variables grows dynamically.

Why do we use collections in Oracle?

Collections are used to store data of the same data type in a variable, such as arrays contained in PL / SQL.

The difference of the collections from the arrays is that their dimensions can be changed when new elements are added or requested.

Collection Types in Oracle PL/SQL

There are 3 types of PL / SQL collection structures.

Associative (Index-By Table)
Nested Table
Varray

Associative Array in Oracle PL/SQL with Example

A collection type that stores data as a key-value pair.

The collection size increases as data is added. Associative Array definition is as follows.

TYPE array_name IS TABLE OF value_data_type [NOT NULL] INDEX BY key_data_type;

1	TYPE array_name IS TABLE OF value_data_type [NOT NULL] INDEX BY key_data_type;

Sample Associative Array usage is as follows.

DECLARE
    TYPE PERSON_LIST IS TABLE OF NUMBER INDEX BY VARCHAR2(20);
    PERSON PERSON_LIST;
    PERSON_NAME VARCHAR2(20);
BEGIN
    PERSON('Yusuf') := 123;
    PERSON('Ramazan') := 456;
    PERSON('Sinan') := 789;
    PERSON('Mehmet') := 987;

    PERSON_NAME := PERSON.FIRST;
    WHILE PERSON_NAME IS NOT NULL LOOP
        DBMS_OUTPUT.put_line(PERSON_NAME || ' - ' || PERSON(PERSON_NAME));
        PERSON_NAME := PERSON.NEXT(PERSON_NAME);
    END LOOP; 
END;

DECLARE

TYPE PERSON_LIST IS TABLE OF NUMBER INDEX BY VARCHAR2(20);

PERSON PERSON_LIST;

PERSON_NAME VARCHAR2(20);

BEGIN

PERSON('Yusuf') := 123;

PERSON('Ramazan') := 456;

PERSON('Sinan') := 789;

PERSON('Mehmet') := 987;

PERSON_NAME := PERSON.FIRST;

WHILE PERSON_NAME IS NOT NULL LOOP

DBMS_OUTPUT.put_line(PERSON_NAME || ' - ' || PERSON(PERSON_NAME));

PERSON_NAME := PERSON.NEXT(PERSON_NAME);

END LOOP;

END;

If you want to make the key value a numerical value, a numeric data type such as PLS_INTEGER, NUMBER can be specified instead of VARCHAR2 (20).

Nested Table in Oracle PL/SQL with Example

It is a type of collection that allows us to store data as in arrays.

The most important feature that distinguishes this collection from the arrays is that its capacity can be increased.

TYPE array_name IS TABLE OF value_data_type [NOT NULL]

1	TYPE array_name IS TABLE OF value_data_type [NOT NULL]

Unlike the Associative Array type, INDEX BY cannot be used.

DECLARE
    TYPE PERSON_LIST IS TABLE OF VARCHAR2(20);
    PERSON PERSON_LIST;
    PERSON_COUNT PLS_INTEGER;
BEGIN
    PERSON := PERSON_LIST('Yusuf', 'Ramazan', 'Sinan', 'Ramazan');

    PERSON_COUNT := PERSON.COUNT;
    FOR I IN 1 .. PERSON_COUNT LOOP
        DBMS_OUTPUT.put_line(I || ' - ' || PERSON(I));
    END LOOP;
END;

DECLARE

TYPE PERSON_LIST IS TABLE OF VARCHAR2(20);

PERSON PERSON_LIST;

PERSON_COUNT PLS_INTEGER;

BEGIN

PERSON := PERSON_LIST('Yusuf', 'Ramazan', 'Sinan', 'Ramazan');

PERSON_COUNT := PERSON.COUNT;

FOR I IN 1 .. PERSON_COUNT LOOP

DBMS_OUTPUT.put_line(I || ' - ' || PERSON(I));

END LOOP;

END;

Adding value to the collection is done by expanding the collection with EXTEND method.

DECLARE
    TYPE PERSON_LIST IS TABLE OF VARCHAR2(20);
    PERSON PERSON_LIST;
    PERSON_COUNT PLS_INTEGER;
BEGIN
    PERSON := PERSON_LIST('Yusuf', 'Ramazan', 'Sinan', 'Ramazan');

    PERSON.EXTEND;
    PERSON(PERSON.COUNT) := 'New value added.';

    PERSON_COUNT := PERSON.COUNT;
    FOR I IN 1 .. PERSON_COUNT LOOP
        DBMS_OUTPUT.put_line(I || ' - ' || PERSON(I));
    END LOOP;
END;

DECLARE

TYPE PERSON_LIST IS TABLE OF VARCHAR2(20);

PERSON PERSON_LIST;

PERSON_COUNT PLS_INTEGER;

BEGIN

PERSON := PERSON_LIST('Yusuf', 'Ramazan', 'Sinan', 'Ramazan');

PERSON.EXTEND;

PERSON(PERSON.COUNT) := 'New value added.';

PERSON_COUNT := PERSON.COUNT;

FOR I IN 1 .. PERSON_COUNT LOOP

DBMS_OUTPUT.put_line(I || ' - ' || PERSON(I));

END LOOP;

END;

Varray in Oracle PL/SQL with Example

Arrays in many programming languages are also available in PL/SQL.

Unlike other programming languages, PL / SQL arrays start from 1. Array definition is as follows.

CREATE OR REPLACE TYPE array_name IS VARRAY(array_dimension) OF array_data_type

1	CREATE OR REPLACE TYPE array_name IS VARRAY(array_dimension) OF array_data_type

TYPE array_name IS VARRAY(array_dimension) OF array_data_type

1	TYPE array_name IS VARRAY(array_dimension) OF array_data_type

Sample array usage is as follows.

SET SERVEROUTPUT ON;
DECLARE
    TYPE PERSON IS VARRAY(4) OF VARCHAR2(20);
    MYLIST PERSON;
    PERSON_COUNT PLS_INTEGER := 0;
BEGIN
    MYLIST := PERSON('Yusuf', 'Ramazan', 'Sinan', 'Mehmet');
    PERSON_COUNT := MYLIST.COUNT;

    FOR i IN 1..PERSON_COUNT LOOP
        DBMS_OUTPUT.put_line(MYLIST(i));
    END LOOP;

END;

SET SERVEROUTPUT ON;

DECLARE

TYPE PERSON IS VARRAY(4) OF VARCHAR2(20);

MYLIST PERSON;

PERSON_COUNT PLS_INTEGER := 0;

BEGIN

MYLIST := PERSON('Yusuf', 'Ramazan', 'Sinan', 'Mehmet');

PERSON_COUNT := MYLIST.COUNT;

FOR i IN 1..PERSON_COUNT LOOP

DBMS_OUTPUT.put_line(MYLIST(i));

END LOOP;

END;

Collection Methods in Oracle PL/SQL

There are several methods for using collections flexibly.

EXISTS (n): Checks if the specified element exists in the collection.

COUNT: Returns the number of elements in the collection.

LIMIT: Returns the collection capacity.

FIRST: Returns the first index value in the collection.

LAST: Returns the last index value in the collection.

PRIOR (n): Returns the sequence number before n.

NEXT (n): Returns the sequence number after n.

EXTEND: Extends the collection by appending a single null element to the collection.

EXTEND (n): Extends the collection by appending n single null element to the collection.

EXTEND (n1, n2): Extends the collection by appending n1 copies of the n2th element to the collection.

TRIM: Removes the last element in the collection.

TRIM (n): Removes elements as much as n 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.

You can find more detailed information about below topics in the below link.

PL/SQL Tutorial

You will find below topics in this article.

You can find more information about collections at docs.oracle.com

Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux

Oracle PL/SQL Collections

What is Collection Oracle?

Why do we use collections in Oracle?

Collection Types in Oracle PL/SQL

Associative Array in Oracle PL/SQL with Example

Nested Table in Oracle PL/SQL with Example

Varray in Oracle PL/SQL with Example

Collection Methods in Oracle PL/SQL

About Yusuf SEZER

Leave a Reply Cancel reply