PL/SQL   «Prev  Next»

Lesson 3 How to flatten a nested table
Objective Write a query that flattens a nested table

Write a Query which Flattens a Nested Table in Oracle

Flattening a nested table means querying the nested table within an outer table. This can be accomplished by using either the THE keyword or the TABLE keyword. We will use THE and TABLE throughout this module to familiarize you with using both the functions. These two functions, which can be used within Data Manipulation Language (DML) statements such as INSERT, UPDATE, or DELETE, query the outer table as if it were a single table, even though it is a table within a table. For example, the SALE_HEADER table has a nested table. We will look at a query that flattens a nested table. You will use the SALE_HEADER (outer table) and DETAIL_TABLE (inner or nested table) tables, and will query from the nested table where sale_id is 101 for the outer table.

Query to flatten a Nested Table

  1. Connect to the database by using PETSTORE as the User Name, GREATPETS as the Password, and MYDB as the Host String. Approve the entries.
  2. Build a SQL statement to select product_id, item_qty, and detail_amount from the nested table (DETAIL_TABLE) within the SALE_HEADER table. Approve the entries and execute the SQL.
  3. SQL*Plus now displays the result of the compilation of your query.

Working with Nested Tables

Nested tables are single-dimensional structures of an Oracle 10g data type or a userdefined record/object type. This section focuses on single-dimensional structures of an Oracle 10g data type. Nested tables may be used in table, record, and object definitions. They may be accessed in SQL and PL/SQL. They are different than arrays in the traditional sense of programming languages such as Java, C, C++, and C#. While they use sequential index values to reference elements in the structure, their size is unconstrained. The closest corollaries to standard programming languages are bags and sets.

Defining Nested Tables as Object Types as PL/SQL Program Constructs

The syntax to define an object type of nested tables in the database is 
CREATE OR REPLACE TYPE type_name
AS TABLE OF element_type [ NOT NULL ];

As discussed, the type name is often a string followed by an underscore and the word table. Some programming traditions prefer the suffix of tab to that of table. It does not matter what you choose to do. It does matter that you do it consistently. The following example program demonstrates defining, declaring, and initializing a nested table of cards in a PL/SQL program unit. The cards will be limited to a single suit. They will be defined as variable-length strings:
Available online as part of create_nestedtable1.sql 
DECLARE
-- Define a nested table of variable-length strings.
TYPE card_table IS TABLE OF VARCHAR2(5 CHAR);
-- Declare and initialize a nested table with three rows.
cards CARD_TABLE := card_table(NULL,NULL,NULL);
BEGIN
-- Print title.
dbms_output.put_line(
'Nested table initialized as null values.');
dbms_output.put_line('--------');


-- Loop through the three records.
FOR i IN 1..3 LOOP
-- Print the contents.
dbms_output.put ('Cards Varray ['||i||'] ');
dbms_output.put_line('['||cards(i)||']');
END LOOP;
-- Assign values to subscripted members of the varray.
cards(1) := 'Ace';
cards(2) := 'Two';
cards(3) := 'Three';
-- Print title.
dbms_output.put (CHR(10)); - Visual line break.
dbms_output.put_line(
'Nested table initialized as 11, 12 and 13.');
dbms_output.put_line('--------');
-- Loop through the records to print the varrays.
FOR i IN 1..3 LOOP
dbms_output.put_line('Cards ['||i||'] '
|| '['||cards(i)||']');
END LOOP;
END;
/

The section below discusses querying a nested table that has a reference operator (REF).

Querying Nested Tables in Oracle

  1. First, query a nested table that has a reference operator (REF).
  2. After completing the required entry fields to connect to the database, you completed the following steps:
  3. You queried the table by building the following SQL string:

SELECT DEREF(a.product_id), item_qty, detail_amount 
FROM THE 
(SELECT detail_nest 
FROM sale_header 
WHERE sale_id = 34) a;

  1. After the process is completed, SQL*Plus displays the result of the compilation of your query.


Nested Tables

Whereas varying arrays have a limited number of entries, a second type of collector, nested tables[1], has no limit on the number of entries per row. A nested table is, as its name implies, a table within a table. In this case, it is a table that is represented as a column within another table. You can have multiple rows in the nested table for each row in the main table. For example, if you have a table of animal breeders, you may also have data about their animals. Using a nested table, you could store the information about breeders and all of their animals within the same table. Consider the ANIMAL_TY datatype. 

create or replace type ANIMAL_TY as object
(Breed VARCHAR2(25),
Name VARCHAR2(25),
BirthDate DATE);

Note: To keep this example simple, this version of the ANIMAL The ANIMAL_TY datatype contains a record for each animal, its breed, name, and birthdate. To use this datatype as the basis for a nested table, you need to create a new abstract datatype: 
create type ANIMALS_NT 
as table of ANIMAL_TY;

The as table of clause of this create type command tells Oracle that you will be using this type as the basis for a nested table. The name of the type, ANIMALS_NT, has a pluralized root to indicate that it stores multiple rows, and has the suffix NT to indicate that it will be a nested table. You can now create a table of breeders, using the ANIMALS_NT datatype: 
create table BREEDER
(BreederName VARCHAR2(25),
Animals ANIMALS_NT)
nested table ANIMALS store as ANIMALS_NT_TAB;

In this create table command, the BREEDER table is created. The first column of the BREEDER table is a Breeder Name column. The second column is a column named Animals, whose definition is the nested table ANIMALS_NT: 
create table BREEDER
(Breeder Name VARCHAR2(25),
Animals ANIMALS_NT);

When creating a table that includes a nested table, you must specify the name of the table that will be used to store the nested table's data. That is, the data for the nested table is not stored inline with the rest of the table's data. Instead, it is stored apart from the main table. Thus, the data in the Animals column will be stored in one table, and the data in the Name column will be stored in a separate table. Oracle will maintain pointers between the tables. In this example, the out-of-line data for the nested table is stored in a table named ANIMALS_NT_TAB: 
nested table ANIMALS 
store as ANIMALS_NT_TAB;

This example highlights the importance of naming standards for collectors. If you pluralize the column names ("Animals" instead of "Animal") and consistently use suffixes (TY, NT, and so forth), you can tell what an object is just by looking at its name. Furthermore, since nested tables and varying arrays may be based directly on previously defined datatypes, their names may mirror the names of the datatypes on which they are based. Thus, a datatype named ANIMAL_TY could be used as the basis for a varying array named ANIMALS_VA or a nested table named ANIMALS_NT. The nested table would have an associated out-of-line table named ANIMALS_NT_TAB. If you know that a table named ANIMALS_NT_TAB exists, you automatically know that it stores data based on a datatype named ANIMAL_TY. In the next lesson, you will learn the easiest way to query a varray.

[1]: A nested table in Oracle PL/SQL is a data structure that can store an arbitrary number of elements. It is similar to an array in other programming languages, but it has some important differences. Nested tables are indexed by numbers, starting at 1. This means that you can access individual elements of a nested table using their index.