Oracle PL/SQL Collections

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.

Sample Associative Array usage is as follows.

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.

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

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

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.

Sample array usage is as follows.

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.

  1. What is PL/SQL
  2. Oracle PL/SQL Data Types and Variables and Literals
  3. Oracle PL/SQL Operators
  4. Oracle PL/SQL Conditional Statements
  5. Oracle PL/SQL Loops
  6. Oracle PL/SQL Procedures and Procedure Parameters
  7. Oracle PL/SQL Functions
  8. Oracle PL/SQL Cursor
  9. Oracle PL/SQL Records
  10. Oracle PL/SQL Exception
  11. Oracle PL/SQL Trigger
  12. Oracle PL/SQL Packages
  13. Oracle PL/SQL Collections

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

Yusuf SEZER
Author: Yusuf SEZER

Leave a Reply

Your email address will not be published. Required fields are marked *