Query to flatten a Nested Table
- Connect to the database by using
PETSTORE as the User Name, GREATPETS as the Password, and MYDB as the Host String. Approve the entries.
- 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.
- SQL*Plus now displays the result of the compilation of your query. This is the end of the simulation. Click Exit.
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;
/