Oracle Tutorial

  • Home
  • Start Here
  • Basics
  • Advanced
    • Oracle View
    • Oracle Index
    • Oracle Synonym
    • Oracle Sequence
    • Oracle Administration
  • PL/SQL
  • Functions
    • Aggregate Functions
    • Analytic Functions
    • Comparison Functions
    • Date Functions
    • String Functions
  • API
    • Python Oracle
Home / PL/SQL Tutorial / PL/SQL Nested Tables

PL/SQL Nested Tables

Summary: in this tutorial, you have learned about the PL/SQL nested tables in Oracle and how to manipulate their elements effectively.

Introduction to PL/SQL nested tables

Nested tables are single-dimensional, unbounded collections of homogeneous elements.

  • First, a nested table is single-dimensional, meaning that each row has a single column of data like a one-dimension array.
  • Second, a nested table is unbounded. It means that the number of elements of a nested table is predetermined.
  • Third, homogeneous elements mean that all elements of a nested table have the same data type.

Noted that a nested table is initially dense. However, it can become sparse through the removal of elements.

Declaring a nested table variable

Declaring a nested table is a two-step process.

First, declare the nested table type using this syntax:

TYPE nested_table_type IS TABLE OF element_datatype [NOT NULL];

Then, declare the nested table variable based on a nested table type:

nested_table_variable nested_table_type;

It is possible to create a nested table type located in the database:

CREATE [OR REPLACE] TYPE nested_table_type IS TABLE OF element_datatype [NOT NULL];

If you want to drop a type, use the following DROP TYPE statement:

DROP TYPE type_name [FORCE];

Initializing a nested table

When you declare a nested table variable, it is initialized to NULL.

To initialize a nested table, you can use a constructor function. The constructor function has the same name as the type:

nested_table_variable := nested_table_type();

You can also declare a nested table and initialize it in one step using the following syntax:

nested_table_variable nested_table_type := nested_table_type();

Add elements to a nested table

To add an element to a nested table, you first use the EXTEND method:

nested_table_variable.EXTEND;

Then, use the assignment operator (:=) to add an element to the nested table:

nested_table_variable := element;

If you want to add multiple elements, you use the EXTEND(n) method, where n is the number of elements that you want to add:

nested_table_variable.EXTEND(n); nested_table_variable := element_1; nested_table_variable := element_2; .. nested_table_variable := element_n;

Accessing elements by their indexes

To access an element at a specified index, you use the following syntax:

nested_table_variable(index);

Iterate over the elements of a nested table

Nested tables have the FIRST and LAST methods that return the first and last indexes of elements respectively.

Therefore, you can use these methods to iterate over the elements of a nested table using a FOR loop:

FOR l_index IN nested_table_variable.FIRST..nested_table_variable.LAST LOOP -- access element END LOOP;

Putting it all together

We’ll use the customers table from the sample database for the demonstration:

customers table

The following example illustrates how to use a cursor to get the first 10 customer names, add the customer names to a nested table, and iterate over the elements:

DECLARE -- declare a cursor that return customer name CURSOR c_customer IS SELECT name FROM customers ORDER BY name FETCH FIRST 10 ROWS ONLY; -- declare a nested table type TYPE t_customer_name_type IS TABLE OF customers.name%TYPE; -- declare and initialize a nested table variable t_customer_names t_customer_name_type := t_customer_name_type(); BEGIN -- populate customer names from a cursor FOR r_customer IN c_customer LOOP t_customer_names.EXTEND; t_customer_names(t_customer_names.LAST) := r_customer.name; END LOOP; -- display customer names FOR l_index IN t_customer_names.FIRST..t_customer_names.LAST LOOP dbms_output.put_line(t_customer_names(l_index)); END LOOP; END;

Let’s examine the example in detail.

First, declare a cursor that returns the first 10 alphabetically sorted customer names.

CURSOR c_customer IS SELECT name FROM customers ORDER BY name FETCH FIRST 10 ROWS ONLY;

Next, declare a nested table type:

TYPE t_customer_name_type IS TABLE OF customers.name%TYPE;

Then, declare a nested table variable and initialize it using the nested table constructor:

t_customer_names t_customer_name_type := t_customer_name_type();

After that, fetch customer names from the cursor and add them to the nested table:

FOR r_customer IN c_customer LOOP t_customer_names.EXTEND; t_customer_names(t_customer_names.LAST) := r_customer.name; END LOOP;

Finally, iterate over the elements of the nested table and display each:

FOR l_index IN t_customer_names.FIRST..t_customer_names.LAST LOOP dbms_output.put_line(t_customer_names(l_index)); END LOOP;

Here is the output:

3M ADP AECOM AES AIG AT&T AbbVie Abbott Laboratories Advance Auto Parts Aetna

In this tutorial, you have learned about the PL/SQL nested tables in Oracle and how to manipulate their elements effectively.

  • Was this tutorial helpful?
  • YesNo
Previous PL/SQL Associative Array
Next PL/SQL VARRAY

Getting Started

  • What Is Oracle Database
  • Install Oracle Database Server
  • Download Oracle Sample Database
  • Create Oracle Sample Database
  • Connect To Oracle Database Server

Oracle Data Manipulation

  • SELECT
  • Oracle DUAL Table
  • ORDER BY
  • SELECT DISTINCT
  • WHERE
  • Table & Column Aliases
  • AND
  • OR
  • FETCH
  • BETWEEN
  • IN
  • LIKE
  • IS NULL
  • Joins
  • INNER JOIN
  • LEFT JOIN
  • RIGHT JOIN
  • FULL OUTER JOIN
  • CROSS JOIN
  • Self Join
  • GROUP BY
  • HAVING
  • UNION
  • INTERSECT
  • MINUS
  • GROUPING SETS
  • CUBE
  • ROLLUP
  • PIVOT
  • UNPIVOT
  • INSERT
  • INSERT INTO SELECT
  • INSERT ALL
  • UPDATE
  • DELETE
  • MERGE
  • Subquery
  • Correlated Subquery
  • EXISTS
  • NOT EXISTS
  • ANY
  • ALL

Oracle Data Types

  • Oracle Data Types
  • NUMBER
  • FLOAT
  • BINARY_FLOAT
  • CHAR
  • NCHAR
  • VARCHAR2
  • NVARCHAR2
  • DATE
  • INTERVAL
  • TIMESTAMP
  • TIMESTAMP WITH TIME ZONE

Oracle Data Definition

  • CREATE TABLE
  • Identity Column
  • ALTER TABLE
  • ALTER TABLE ADD Column
  • ALTER TABLE MODIFY Column
  • Drop Columns
  • DROP TABLE
  • TRUNCATE TABLE
  • RENAME Table
  • Oracle Virtual Column

Oracle Constraints

  • PRIMARY KEY
  • FOREIGN KEY
  • UNIQUE
  • CHECK
  • NOT NULL

Oracle Views

  • CREATE VIEW
  • DROP VIEW
  • Updatable Views
  • Inline Views
  • WITH CHECK OPTION

About Oracle Tutorial

OracleTututorial.com website provides Developers and Database Administrators with the updated Oracle tutorials, scripts, and tips.

Search

Recent Tutorials

  • Oracle Implicit Statement Results
  • Calling PL/SQL Stored Functions in Python
  • Calling PL/SQL Procedures in Python
  • Managing Transaction in Python
  • Deleting Data From Oracle Database in Python

Site Links

  • Oracle Books
  • About
  • Contact
  • Privacy Policy
  • Terms of Use

Copyright © 2021 Oracle Tutorial. All Rights Reserved.