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:
- Associative arrays, also known as index-by tables.
- Nested tables.
- 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 elements | Unbounded within index-type limits | Unbounded | Bounded |
| Index type | Integer or string | Integer | Integer |
| Density | May be dense or sparse | Initially dense; may become sparse | Always dense |
| Initialization | Empty when declared | Atomically null until initialized | Atomically null until initialized |
| Maximum size | Not declared explicitly | Not declared explicitly | Declared in type definition |
| Schema-level declaration | No | Yes | Yes |
| Database table column | No | Yes, using schema-level type | Yes, using schema-level type |
| Individual element deletion | Supported | Supported | Not supported |
| EXTEND and TRIM | Not supported | Supported | Supported |
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. |
| COUNT | Returns the number of existing elements in the collection. |
| LIMIT | Returns the maximum number of elements allowed in a VARRAY; returns NULL for unbounded collection types. |
| FIRST | Returns the first existing subscript. |
| LAST | Returns the last existing subscript. |
| PRIOR(n) | Returns the preceding existing subscript. |
| NEXT(n) | Returns the following existing subscript. |
| EXTEND | Adds one null element to a nested table or VARRAY. |
| EXTEND(n) | Adds n null elements. |
| EXTEND(n,i) | Adds n copies of element i. |
| TRIM | Removes one element from the end. |
| TRIM(n) | Removes n elements from the end. |
| DELETE | Removes 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_NULL | An operation is performed on an atomically null collection. |
| NO_DATA_FOUND | An element reference identifies a deleted or nonexistent associative-array element. |
| SUBSCRIPT_BEYOND_COUNT | An operation requires a subscript that exceeds the current element count. |
| SUBSCRIPT_OUTSIDE_LIMIT | A subscript is outside the permitted range. |
| VALUE_ERROR | A 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 keys | Associative array | Supports flexible key-based lookup |
| Store an unbounded integer-indexed collection | Nested table | Supports dynamic size and sparse indexing |
| Store a bounded, dense collection | VARRAY | Maintains sequential indexes and a maximum capacity |
| Represent several attributes of different data types | Record | Groups fields into a logical entity |
| Represent multiple structured rows in memory | Collection of records | Combines collection indexing with record fields |
| Store collection values in database columns | Schema-level nested table or VARRAY | Supports database storage of collection values |
| Compare or combine nested-table values | Nested table with multiset operations | Supports 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