Record & Collection

Oracle PL/SQL Collections and Records: Types, Methods, Operations, and Practical Applications

Abstract

Oracle PL/SQL provides composite data structures that enable developers to organize, process, and manipulate multiple values within a single program unit. Collections represent groups of elements of the same data type, whereas records combine fields that may have different data types. This article examines the three principal PL/SQL collection types—associative arrays, nested tables, and variable-size arrays (VARRAYs)—and explains their declarations, characteristics, methods, and practical applications. It also discusses collection assignments, equality comparisons, multiset operations, membership conditions, and multidimensional collection structures. In addition, the article presents table-based, cursor-based, and user-defined records, including records as subprogram parameters and collections of records. Practical PL/SQL examples illustrate common implementation patterns and explain important considerations such as collection initialization, sparse indexing, traversal, and exception handling. The discussion is based primarily on Oracle's official PL/SQL documentation and provides a practical reference for database developers.

Keywords: Oracle Database, PL/SQL, collections, associative arrays, nested tables, VARRAYs, records, collection methods, MULTISET

1. Introduction

Oracle PL/SQL supports composite data structures that allow developers to represent complex information more effectively than scalar variables. A composite variable contains multiple internal components that can be accessed individually or manipulated as a complete structure. Oracle identifies collections and records as the principal composite data types in PL/SQL (Oracle, 2023).

A collection is an ordered group of elements of the same data type. Each element is identified by a subscript or key. A record, in contrast, is a structure containing multiple fields that may have different data types. Records are accessed through their field names rather than collection subscripts (Oracle, 2023).

Collections and records are particularly useful when developing database applications that require temporary data structures, processing multiple rows in memory, passing structured information between subprograms, or organizing application data into logical units.

This article presents the principal collection types, their characteristics, collection methods and operations, and the different record structures available in PL/SQL.

2. Overview of PL/SQL Collections

Oracle PL/SQL provides three principal collection types:

  1. Associative arrays, also known as index-by tables.
  2. Nested tables.
  3. Variable-size arrays, commonly referred to as VARRAYs.

Although all three types store multiple elements of the same data type, they differ in indexing, maximum capacity, density, initialization requirements, and database storage capabilities (Oracle, 2023).

2.1 Comparison of Collection Types

Characteristic Associative Array Nested Table VARRAY
Maximum number of elementsUnbounded within index-type limitsUnboundedBounded
Index typeInteger or stringIntegerInteger
DensityMay be dense or sparseInitially dense; may become sparseAlways dense
InitializationEmpty when declaredAtomically null until initializedAtomically null until initialized
Maximum sizeNot declared explicitlyNot declared explicitlyDeclared in type definition
Schema-level declarationNoYesYes
Database table columnNoYes, using schema-level typeYes, using schema-level type
Individual element deletionSupportedSupportedNot supported
EXTEND and TRIMNot supportedSupportedSupported

Note. Based on Oracle's documentation of PL/SQL collection types (Oracle, 2023).

Associative arrays are particularly useful for key-based access, nested tables support SQL-oriented collection operations, and VARRAYs are suitable when a collection has a defined maximum capacity.

3. Associative Arrays (Index-by Tables)

An associative array is a collection of key-value pairs in which each key is unique and identifies its associated element.

Associative arrays are declared using the INDEX BY clause. The index can be an integer type or a string type, allowing developers to access elements using either numeric positions or meaningful character-based keys (Oracle, 2023).

3.1 Declaration Syntax

The general syntax for declaring an associative array is:

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

collection_variable type_name;

For example:

DECLARE
  TYPE number_table IS TABLE OF NUMBER
    INDEX BY PLS_INTEGER;

  v_numbers number_table;
BEGIN
  v_numbers(1) := 10;
  v_numbers(5) := 50;
END;
/

The collection can contain elements at indexes 1 and 5 without requiring the intermediate indexes to exist. This demonstrates the sparse nature of associative arrays.

3.2 Associative Array Using String Keys

The following example creates an associative array that stores salary values associated with employee names.

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

  salary_list salary_map;
  v_name      VARCHAR2(20);
BEGIN
  salary_list('Rajnish')  := 62000;
  salary_list('Minakshi') := 75000;
  salary_list('Martin')   := 100000;
  salary_list('James')    := 78000;

  v_name := salary_list.FIRST;

  WHILE v_name IS NOT NULL LOOP
    DBMS_OUTPUT.PUT_LINE(
      'Salary of ' || v_name || ' is ' ||
      TO_CHAR(salary_list(v_name))
    );

    v_name := salary_list.NEXT(v_name);
  END LOOP;
END;
/

The FIRST method returns the first existing key, while NEXT returns the subsequent existing key. This approach allows the collection to be traversed without assuming that the indexes are consecutive (Oracle, 2023).

Expected output:

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

The output order follows the associative array's key ordering rather than the order in which the values were assigned.

3.3 Associative Arrays Using Table Column Data Types

Associative array elements can use the %TYPE attribute to derive their data type from an existing database column.

This approach reduces data-type duplication and helps maintain consistency between PL/SQL declarations and database definitions.

DECLARE
  CURSOR c_customers IS
    SELECT name
    FROM customers;

  TYPE customer_names IS TABLE OF customers.name%TYPE
    INDEX BY PLS_INTEGER;

  name_list customer_names;
  v_counter PLS_INTEGER := 0;
BEGIN
  FOR r IN c_customers LOOP
    v_counter := v_counter + 1;

    name_list(v_counter) := r.name;

    DBMS_OUTPUT.PUT_LINE(
      'Customer(' || v_counter || '): ' ||
      name_list(v_counter)
    );
  END LOOP;
END;
/

This example retrieves customer names from a cursor and stores them in an associative array.

The %TYPE attribute ensures that the collection element data type corresponds to the data type of the CUSTOMERS.NAME column.

4. Nested Tables

A nested table is an integer-indexed collection whose maximum number of elements is not specified in its declaration.

Unlike associative arrays, nested tables can be declared at the schema level and used as database table column types. They can also participate in SQL collection operations, including multiset operations (Oracle, 2023).

4.1 Declaration Syntax

A nested table is declared using the following syntax:

TYPE type_name IS TABLE OF element_type
  [NOT NULL];

collection_variable type_name;

For example:

DECLARE
  TYPE names_table IS TABLE OF VARCHAR2(20);

  names names_table;
BEGIN
  names := names_table('Ali', 'Ahmed', 'Yasser');

  DBMS_OUTPUT.PUT_LINE(names(1));
  DBMS_OUTPUT.PUT_LINE(names(2));
  DBMS_OUTPUT.PUT_LINE(names(3));
END;
/

Nested tables must be initialized before their elements can be accessed. The collection constructor initializes the collection with the specified values.

An empty nested table can also be initialized using an empty constructor:

names := names_table();

This creates an initialized collection containing zero elements. It is different from an atomically null collection, which has not yet been initialized (Oracle, 2023).

4.2 Nested Tables Containing Multiple Values

The following example demonstrates two nested tables containing student names and grades.

DECLARE
  TYPE names_table IS TABLE OF VARCHAR2(20);
  TYPE grades_table IS TABLE OF INTEGER;

  names names_table;
  marks grades_table;
BEGIN
  names := names_table(
    'Kavita',
    'Pritam',
    'Ayan',
    'Rishav',
    'Aziz'
  );

  marks := grades_table(98, 97, 78, 87, 92);

  FOR i IN 1 .. names.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE(
      'Student: ' || names(i) ||
      ', Marks: ' || marks(i)
    );
  END LOOP;
END;
/

Expected output:

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

The example illustrates how nested tables can store multiple values and allow developers to process the elements using collection indexes.

4.3 Loading Nested Tables from Database Queries

Nested tables can be populated dynamically by extending the collection before assigning values to new elements.

DECLARE
  CURSOR c_customers IS
    SELECT name
    FROM customers;

  TYPE customer_names IS TABLE OF customers.name%TYPE;

  name_list customer_names := customer_names();
BEGIN
  FOR r IN c_customers LOOP
    name_list.EXTEND;

    name_list(name_list.LAST) := r.name;

    DBMS_OUTPUT.PUT_LINE(
      'Customer(' || name_list.LAST || '): ' ||
      name_list(name_list.LAST)
    );
  END LOOP;
END;
/

The EXTEND method adds an element to the collection. The LAST method returns the index of the newly added element, allowing the value to be assigned correctly.

This pattern is useful when the number of elements is not known in advance.

5. VARRAY Collections

A VARRAY is a collection whose maximum number of elements must be specified in the declaration.

VARRAYs are always dense and maintain consecutive indexes. Individual elements cannot be deleted, although elements can be added or removed from the end using supported collection methods (Oracle, 2023).

5.1 Declaration Syntax

TYPE type_name IS VARRAY(maximum_size)
  OF element_type;

collection_variable type_name;

For example:

DECLARE
  TYPE number_array IS VARRAY(5) OF NUMBER(10);

  v_numbers number_array;
BEGIN
  v_numbers := number_array(1, 2, 3);

  DBMS_OUTPUT.PUT_LINE(v_numbers(1));
  DBMS_OUTPUT.PUT_LINE(v_numbers(2));
  DBMS_OUTPUT.PUT_LINE(v_numbers(3));
END;
/

The declaration VARRAY(5) defines the maximum capacity of the collection as five elements.

5.2 Extending and Traversing a VARRAY

The following example initializes a VARRAY with two values and extends it to five elements.

SET SERVEROUTPUT ON

DECLARE
  TYPE number_array IS VARRAY(5) OF NUMBER(10);

  v_numbers number_array;
  v_index   PLS_INTEGER;
BEGIN
  v_numbers := number_array(1, 2);

  FOR i IN 3 .. 5 LOOP
    v_numbers.EXTEND;

    v_numbers(v_numbers.LAST) := i;
  END LOOP;

  v_index := v_numbers.FIRST;

  WHILE v_index IS NOT NULL LOOP
    DBMS_OUTPUT.PUT_LINE(
      'The number ' || v_numbers(v_index)
    );

    v_index := v_numbers.NEXT(v_index);
  END LOOP;
END;
/

Expected output:

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

A VARRAY cannot be extended beyond its declared maximum capacity. Additionally, individual elements cannot be deleted using DELETE(n).

6. Collection Assignments and Equality Tests

Collection assignments and comparisons depend on the collection type and its declaration.

For collections declared using the same collection type, assignment can transfer the collection value from one variable to another.

6.1 Collection Assignment

DECLARE
  TYPE number_table IS TABLE OF NUMBER;

  v_tab_1 number_table;
  v_tab_2 number_table;
BEGIN
  v_tab_1 := number_table(1, 2);

  v_tab_2 := v_tab_1;

  DBMS_OUTPUT.PUT_LINE(v_tab_2(1));
  DBMS_OUTPUT.PUT_LINE(v_tab_2(2));
END;
/

The second collection receives the value of the first collection.

The collection variables must be compatible with the declared collection type. Similar-looking collection declarations do not necessarily represent interchangeable collection types (Oracle, 2023).

6.2 Collection Equality

Collection comparisons are subject to Oracle's type-specific rules.

Nested tables support collection comparison operations, including equality and inequality, when their element types and comparison requirements permit them. Associative arrays and VARRAYs have different comparison restrictions.

Therefore, collection equality should not be assumed to work identically for all collection types. Developers should consult Oracle's collection comparison rules for the relevant data type and database release (Oracle, 2023).

7. Collection Methods

Oracle provides built-in collection methods that simplify collection manipulation and traversal.

These methods are called using dot notation:

collection_name.method_name

Some methods return information about a collection, while others modify its contents (Oracle, 2023).

7.1 Collection Method Reference

Method Description
EXISTS(n)Returns TRUE if element n exists; otherwise returns FALSE.
COUNTReturns the number of existing elements in the collection.
LIMITReturns the maximum number of elements allowed in a VARRAY; returns NULL for unbounded collection types.
FIRSTReturns the first existing subscript.
LASTReturns the last existing subscript.
PRIOR(n)Returns the preceding existing subscript.
NEXT(n)Returns the following existing subscript.
EXTENDAdds one null element to a nested table or VARRAY.
EXTEND(n)Adds n null elements.
EXTEND(n,i)Adds n copies of element i.
TRIMRemoves one element from the end.
TRIM(n)Removes n elements from the end.
DELETERemoves all elements from the collection.
DELETE(n)Removes element n from an associative array or nested table.
DELETE(m,n)Removes elements within the specified index range from an associative array or nested table.

Note. Collection method behavior and applicability differ among associative arrays, nested tables, and VARRAYs (Oracle, 2023).

7.2 COUNT

The COUNT method returns the number of existing elements in the collection.

DECLARE
  TYPE number_table IS TABLE OF NUMBER;

  v_numbers number_table :=
    number_table(10, 20, 30, 40, 50);
BEGIN
  DBMS_OUTPUT.PUT_LINE(
    'Number of elements: ' || v_numbers.COUNT
  );
END;
/

Output:

Number of elements: 5

For sparse nested tables, COUNT may differ from LAST because deleted elements are not included in the count.

7.3 EXISTS

The EXISTS method checks whether a specified element exists.

DECLARE
  TYPE number_table IS TABLE OF NUMBER
    INDEX BY PLS_INTEGER;

  v_numbers number_table;
BEGIN
  v_numbers(1) := 10;
  v_numbers(3) := 30;

  IF v_numbers.EXISTS(2) THEN
    DBMS_OUTPUT.PUT_LINE('Element 2 exists');
  ELSE
    DBMS_OUTPUT.PUT_LINE('Element 2 does not exist');
  END IF;
END;
/

Output:

Element 2 does not exist

This method is particularly useful when collections contain gaps between indexes.

7.4 FIRST, LAST, NEXT, and PRIOR

The FIRST, LAST, NEXT, and PRIOR methods support collection traversal.

For example:

DECLARE
  TYPE number_table IS TABLE OF NUMBER
    INDEX BY PLS_INTEGER;

  v_numbers number_table;
  v_index   PLS_INTEGER;
BEGIN
  v_numbers(1) := 10;
  v_numbers(5) := 50;
  v_numbers(10) := 100;

  v_index := v_numbers.FIRST;

  WHILE v_index IS NOT NULL LOOP
    DBMS_OUTPUT.PUT_LINE(v_numbers(v_index));

    v_index := v_numbers.NEXT(v_index);
  END LOOP;
END;
/

The NEXT method returns the next existing index, allowing sparse collections to be traversed without attempting to access nonexistent elements.

7.5 EXTEND

The EXTEND method adds elements to nested tables and VARRAYs.

Examples:

collection_name.EXTEND;
collection_name.EXTEND(3);
collection_name.EXTEND(2,1);

These statements add one null element, three null elements, or two copies of the first element, respectively.

EXTEND cannot be used with associative arrays (Oracle, 2023).

7.6 TRIM

The TRIM method removes elements from the end of nested tables and VARRAYs.

collection_name.TRIM;
collection_name.TRIM(2);

The first statement removes one element, while the second removes two elements.

TRIM cannot be used with associative arrays.

7.7 DELETE

The DELETE method removes collection elements.

collection_name.DELETE;
collection_name.DELETE(3);
collection_name.DELETE(2,5);

These statements delete all elements, a specified element, or elements within an index range.

Individual element deletion is supported for associative arrays and nested tables but not for VARRAYs (Oracle, 2023).

8. Collection Exceptions

Collection operations may raise exceptions when a collection is accessed or manipulated incorrectly.

The following table summarizes common collection-related exceptions.

Exception Situation
COLLECTION_IS_NULLAn operation is performed on an atomically null collection.
NO_DATA_FOUNDAn element reference identifies a deleted or nonexistent associative-array element.
SUBSCRIPT_BEYOND_COUNTAn operation requires a subscript that exceeds the current element count.
SUBSCRIPT_OUTSIDE_LIMITA subscript is outside the permitted range.
VALUE_ERRORA subscript is null or cannot be converted to the required key type or range.

Note. Exception behavior depends on the operation and collection type (Oracle, 2023).

8.1 Practical Considerations

Developers should consider the following points when working with collections:

  • Initialize nested tables and VARRAYs before accessing their elements.
  • Use EXISTS to verify whether an element exists before referencing it.
  • Use FIRST and NEXT to traverse sparse collections.
  • Do not use EXTEND or TRIM with associative arrays.
  • Do not delete individual VARRAY elements.
  • Avoid depending on undocumented or unexpected interactions between TRIM and DELETE.

Oracle documents that deleted nested-table elements can create sparse indexes and that collection methods operate according to the internal structure of the collection (Oracle, 2023).

9. Records in PL/SQL

A record is a composite data structure containing multiple fields that may have different data types.

Records are useful when several related attributes must be treated as a single logical entity. For example, a book record may contain a title, author, subject, and book identifier.

PL/SQL supports table-based records, cursor-based records, and user-defined records (Oracle, 2023).

10. Table-Based Records

The %ROWTYPE attribute enables developers to declare a record whose structure corresponds to a database table row.

For example:

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;
/

The record customer_rec contains fields corresponding to the columns of the CUSTOMERS table.

Each field can be accessed using dot notation:

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;

  CLOSE customer_cur;
END;
/

The record contains the columns selected by the cursor.

Cursor-based records are useful when a program requires a structured representation of the rows returned by a query.

For simpler processing, cursor FOR loops can automatically manage cursor opening, fetching, and closing.

12. User-Defined Records

PL/SQL allows developers to define custom record structures using the RECORD keyword.

A user-defined record can contain fields of different data types.

12.1 Defining a Record

The general syntax is:

TYPE type_name IS RECORD (
  field_name1 datatype1,
  field_name2 datatype2,
  ...
  field_nameN datatypeN
);

record_variable type_name;

For example:

DECLARE
  TYPE book_record IS RECORD (
    title   VARCHAR2(50),
    author  VARCHAR2(50),
    subject VARCHAR2(100),
    book_id NUMBER
  );

  book1 book_record;
BEGIN
  book1.title   := 'C Programming';
  book1.author  := 'Nuha Ali';
  book1.subject := 'C Programming Tutorial';
  book1.book_id := 6495407;

  DBMS_OUTPUT.PUT_LINE(
    'Book title: ' || book1.title
  );

  DBMS_OUTPUT.PUT_LINE(
    'Book author: ' || book1.author
  );

  DBMS_OUTPUT.PUT_LINE(
    'Book subject: ' || book1.subject
  );

  DBMS_OUTPUT.PUT_LINE(
    'Book ID: ' || book1.book_id
  );
END;
/

The example illustrates how a record represents a book as a single logical structure containing multiple attributes.

13. Records as Subprogram Parameters

Records can be passed to procedures and functions as parameters.

This capability allows developers to group related data into a single parameter rather than passing each field separately.

DECLARE
  TYPE book_record IS RECORD (
    title   VARCHAR2(50),
    author  VARCHAR2(50),
    subject VARCHAR2(100),
    book_id NUMBER
  );

  book1 book_record;

  PROCEDURE print_book(p_book IN book_record) IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE(
      'Book title: ' || p_book.title
    );

    DBMS_OUTPUT.PUT_LINE(
      'Book author: ' || p_book.author
    );

    DBMS_OUTPUT.PUT_LINE(
      'Book subject: ' || p_book.subject
    );

    DBMS_OUTPUT.PUT_LINE(
      'Book ID: ' || p_book.book_id
    );
  END;
BEGIN
  book1.title   := 'C Programming';
  book1.author  := 'Nuha Ali';
  book1.subject := 'C Programming Tutorial';
  book1.book_id := 6495407;

  print_book(book1);
END;
/

The procedure receives the record as a single parameter and accesses its individual fields.

This approach can simplify subprogram interfaces and improve the organization of application code (Oracle, 2023).

14. Multidimensional Collections

PL/SQL collections can contain record elements or other collections, allowing developers to represent more complex data structures.

A collection of records can represent a structure similar to a table containing multiple rows and columns.

The following example demonstrates a collection of records:

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

  TYPE item_table IS TABLE OF item_record;

  items item_table := item_table();
BEGIN
  FOR i IN 1 .. 10 LOOP
    items.EXTEND;

    items(items.LAST).id := i;

    items(items.LAST).description :=
      'Description for ' || i;
  END LOOP;

  FOR i IN 1 .. items.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE(
      items(i).id || ' - ' ||
      items(i).description
    );
  END LOOP;
END;
/

This structure combines a collection with a record type.

Nested collections can also be used to model multidimensional structures. However, the design should account for initialization, indexing, and traversal at each level (Oracle, 2023).

15. MULTISET Operations

Oracle provides multiset operators for nested tables.

These operators allow developers to combine, compare, and manipulate collection values using set-oriented operations.

The principal multiset operators include:

  • MULTISET UNION
  • MULTISET EXCEPT
  • MULTISET INTERSECT

The DISTINCT option can be used to remove duplicate occurrences according to the relevant operation's semantics (Oracle, 2023).

15.1 MULTISET UNION

MULTISET UNION combines the elements of two nested tables.

DECLARE
  TYPE number_table IS TABLE OF NUMBER;

  numbers_a number_table :=
    number_table(1,2,3,4,5,6);

  numbers_b number_table :=
    number_table(5,6,7,8,9,10);
BEGIN
  numbers_a :=
    numbers_a MULTISET UNION DISTINCT numbers_b;

  FOR i IN 1 .. numbers_a.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE(numbers_a(i));
  END LOOP;
END;
/

The DISTINCT option removes duplicate values from the resulting collection.

15.2 MULTISET EXCEPT

MULTISET EXCEPT returns the multiset difference between two nested tables.

DECLARE
  TYPE number_table IS TABLE OF NUMBER;

  numbers_a number_table :=
    number_table(1,2,3,4,5,6,7,8,9,10);

  numbers_b number_table :=
    number_table(6,7,8,9,10);
BEGIN
  numbers_a :=
    numbers_a MULTISET EXCEPT numbers_b;

  FOR i IN 1 .. numbers_a.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE(numbers_a(i));
  END LOOP;
END;
/

The result contains the elements of the first collection that are not matched by elements of the second collection, subject to multiset rules.

15.3 MULTISET INTERSECT

MULTISET INTERSECT returns the elements common to both nested tables.

DECLARE
  TYPE number_table IS TABLE OF NUMBER;

  numbers_a number_table :=
    number_table(1,2,3,4,5,6,7,8,9,10);

  numbers_b number_table :=
    number_table(6,7,8,9,10);
BEGIN
  numbers_a :=
    numbers_a MULTISET INTERSECT numbers_b;

  FOR i IN 1 .. numbers_a.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE(numbers_a(i));
  END LOOP;
END;
/

The result contains the common elements.

16. SUBMULTISET Condition

The SUBMULTISET OF condition tests whether one nested table is a submultiset of another nested table.

It can be used to determine whether all elements of one collection are contained in another collection, subject to multiset semantics (Oracle, 2023).

Example:

DECLARE
  TYPE number_table IS TABLE OF NUMBER;

  numbers_a number_table :=
    number_table(1,2,3,4,5);

  numbers_b number_table :=
    number_table(2,3);
BEGIN
  IF numbers_b SUBMULTISET OF numbers_a THEN
    DBMS_OUTPUT.PUT_LINE(
      'numbers_b is a submultiset of numbers_a'
    );
  ELSE
    DBMS_OUTPUT.PUT_LINE(
      'numbers_b is not a submultiset of numbers_a'
    );
  END IF;
END;
/

Output:

numbers_b is a submultiset of numbers_a

This condition is useful when a program must verify that a collection's elements are contained within another collection.

17. MEMBER Condition

The MEMBER OF condition determines whether a specified value is a member of a collection.

DECLARE
  TYPE number_table IS TABLE OF NUMBER;

  numbers number_table :=
    number_table(1,2,3,4,5);
BEGIN
  IF 3 MEMBER OF numbers THEN
    DBMS_OUTPUT.PUT_LINE(
      '3 is a member of the collection'
    );
  ELSE
    DBMS_OUTPUT.PUT_LINE(
      '3 is not a member of the collection'
    );
  END IF;
END;
/

Output:

3 is a member of the collection

The membership condition provides a direct way to test whether an element belongs to a nested-table collection without explicitly traversing every element (Oracle, 2023).

18. Practical Guidelines for Selecting Collections and Records

The choice of collection or record structure depends on the application's requirements.

Requirement Suitable Structure Reason
Access values using integer or string keysAssociative arraySupports flexible key-based lookup
Store an unbounded integer-indexed collectionNested tableSupports dynamic size and sparse indexing
Store a bounded, dense collectionVARRAYMaintains sequential indexes and a maximum capacity
Represent several attributes of different data typesRecordGroups fields into a logical entity
Represent multiple structured rows in memoryCollection of recordsCombines collection indexing with record fields
Store collection values in database columnsSchema-level nested table or VARRAYSupports database storage of collection values
Compare or combine nested-table valuesNested table with multiset operationsSupports collection-oriented operations

These guidelines provide a starting point for design decisions. The final choice should reflect the data model, required operations, SQL integration, and expected application behavior.

19. Conclusion

PL/SQL collections and records provide complementary mechanisms for representing and manipulating composite data structures.

Associative arrays support flexible key-based access, nested tables provide dynamically sized integer-indexed collections and multiset functionality, and VARRAYs provide bounded and dense collections. Collection methods such as COUNT, EXISTS, FIRST, LAST, NEXT, EXTEND, TRIM, and DELETE support collection management and traversal.

Records provide a structured way to organize fields of different data types. Table-based and cursor-based records can derive their structures from database definitions, while user-defined records allow developers to define application-specific data structures. Records can also be passed as subprogram parameters and stored as collection elements.

Understanding these structures, their methods, and their limitations enables developers to design PL/SQL programs that are more organized, maintainable, and appropriate for their data-processing requirements.

References

Oracle. (2023). Oracle Database PL/SQL language reference: PL/SQL collections and records. Oracle Database 23c. https://docs.oracle.com/en/database/oracle/oracle-database/23/lnpls/plsql-collections-and-records.html

Oracle. (n.d.). Oracle Database PL/SQL language reference. Oracle Database documentation. https://docs.oracle.com/en/database/oracle/oracle-database/21/lnpls/

No comments:

Post a Comment