PL/SQL - (Nested) tables (Table Of without indexing)

> Procedural Languages > PL/SQL - (Procedure Language|PL) SQL

1 - About

A “table of” data type without the index-by clause is a list of elements (collection).

For a table of indexed by, see PL/SQL - Associative Arrays (Index-By Tables) - Table Of Indexed By - Map

Advertising

3 - Syntax

TYPE table_of_type IS TABLE OF VARCHAR2(30) ; -- default indexed by integer  -- List of Called a Nested table

Syntax

4 - Construction

SET serveroutput ON
DECLARE
   TYPE listVarchar2 IS TABLE OF VARCHAR2(30);
   myList    listVarchar2;
BEGIN
   IF myList.EXISTS(1) = FALSE THEN
        DBMS_OUTPUT.PUT_LINE('No Constructor');
        myList := listVarchar2(); -- Null Constructor
        myList := listVarchar2('1','2','3','4');
        DBMS_OUTPUT.Put_Line('Loop:');
        FOR i IN myList.FIRST..myList.LAST LOOP
           DBMS_OUTPUT.Put_Line('element(' || i || '): ' || myList(i));
        END LOOP;
    END IF;
END;
/
Loop:
element(1): 1
element(2): 2
element(3): 3
element(4): 4

5 - Methods

Using Collection Methods :

  • Checking If a Collection Element Exists (EXISTS Method)
  • Counting the Elements in a Collection (COUNT Method)
  • Checking the Maximum Size of a Collection (LIMIT Method)
  • Finding the First or Last Collection Element (FIRST and LAST Methods)
  • Looping Through Collection Elements (PRIOR and NEXT Methods)
  • Increasing the Size of a Collection (EXTEND Method)
  • Decreasing the Size of a Collection (TRIM Method)
  • Deleting Collection Elements (DELETE Method)
  • Applying Methods to Collection Parameters
Advertising

6 - Management

6.1 - Null

SET serveroutput ON;
DECLARE
    TYPE t_tabOfInteger IS TABLE OF INTEGER;
    l_Integers t_tabOfInteger;
BEGIN
    IF (l_Integers IS NULL) THEN
        DBMS_OUTPUT.PUT_LINE('t_tabOfInteger is null');
    ELSE 
        DBMS_OUTPUT.PUT_LINE('t_tabOfInteger is not null');
    END IF;
    l_Integers := NULL;
END;
t_tabOfInteger is null

6.2 - Count

SET serveroutput ON;
DECLARE
    TYPE t_tabOfInteger IS TABLE OF INTEGER;
    l_Integers t_tabOfInteger := t_tabOfInteger(1,2,3);
BEGIN
    IF (l_Integers IS NOT NULL) THEN
        DBMS_OUTPUT.PUT_LINE('l_integers has (' || l_Integers.COUNT || ') elements ');
    ELSE
        DBMS_OUTPUT.PUT_LINE('Error: 06531. 00000 -  "Reference to uninitialized collection"');
    END IF;
END;
l_integers has (3) elements 

6.3 - Empty

l_integers not initalized is not empty
l_integers after initalization without element is empty
SET serveroutput ON;
DECLARE
    TYPE t_tabOfInteger IS TABLE OF INTEGER;
    l_Integers t_tabOfInteger;
BEGIN
    IF (l_Integers IS empty) THEN
        DBMS_OUTPUT.PUT_LINE('l_integers not initalized is empty');
    ELSE
        DBMS_OUTPUT.PUT_LINE('l_integers not initalized is not empty');
    END IF;
    l_integers:= t_tabOfInteger();
    IF (l_Integers IS empty) THEN
        DBMS_OUTPUT.PUT_LINE('l_integers after initalization without element is empty');
    ELSE
        DBMS_OUTPUT.PUT_LINE('l_integers after initalization without element is not empty');
    END IF;
END;

6.4 - Exist (Element exist)

IF n.EXISTS(1) = FALSE  THEN
   DBMS_OUTPUT.PUT_LINE('element 1 doesn''t exist.');
   -- Extend   
   n.extend(1);
   n(1) := 10;
END IF;
 
IF n.EXISTS(1)  THEN
   DBMS_OUTPUT.PUT_LINE('element 1 exists with value ' || n(1) );
   DBMS_OUTPUT.PUT_LINE('Last : ' || n.LAST);
END IF;

6.5 - MEMBER OF

answer := 4 MEMBER OF nt1; -- false, no element matches

6.6 - SUBMULTISET

 TYPE nested_typ IS TABLE OF NUMBER;
  nt3 nested_typ := nested_typ(2,3,1,3);
  nt4 nested_typ := nested_typ(1,2,4);
answer := nt1 SUBMULTISET OF nt3; -- true, all elements match
answer := nt1 NOT SUBMULTISET OF nt4; -- also true

6.7 - CARDINALITY

howmany := CARDINALITY(nt3); -- number of elements in nt3
howmany := CARDINALITY(SET(nt3)); -- number of distinct elements

6.8 - Extend

DECLARE
 
   TYPE t_var IS TABLE OF VARCHAR2(30);
   t_vars t_var := t_var('One');
 
BEGIN
 
   -- Add an element at the end
   t_vars.extend(1);
   t_vars(t_vars.LAST) := 'Two';
 
   -- Loop through the elemnt
   FOR i IN t_vars.FIRST..t_vars.LAST
   LOOP
       DBMS_OUTPUT.Put_Line('element(' || i || '): ' || t_vars(i));
   END LOOP;
 
END;
/
PL/SQL procedure successfully completed.

element(1): One
element(2): Two
Advertising

6.9 - Set (Make a set of distinct element, Test if it's a set)

-- Make CollectionVariable a set (no duplicate)
SET(CollectionVariable);
-- Test if CollectionVariable is a set and return a boolean
CollectionVariable IS A SET;
-- Test if CollectionVariable is not a set and return a boolean
CollectionVariable IS NOT A SET;

Example:

SET serveroutput ON;
DECLARE
 
    TYPE t_tabOfInteger IS TABLE OF INTEGER;
    l_Integers t_tabOfInteger := t_tabOfInteger( 1, 2, 2 );
 
    PROCEDURE PRINT(
            l_Integers t_tabOfInteger )
    AS
 
        l_boolean BOOLEAN;
 
    BEGIN
 
        l_boolean := l_Integers IS a SET;
        DBMS_OUTPUT.PUT_LINE( 'l_Integers is ' || CASE WHEN l_boolean = FALSE THEN 'not ' ELSE ''  END || 'a set ' );
 
        FOR i IN l_Integers.FIRST .. l_Integers.LAST
        LOOP
            DBMS_OUTPUT.PUT_LINE( 'Element: ' || l_Integers( i ) );
        END LOOP;
 
    END;
 
BEGIN
 
    DBMS_OUTPUT.PUT_LINE( 'Before set, 2 is a duplicate' );
    PRINT( l_Integers );
    l_Integers := SET( l_Integers );
    DBMS_OUTPUT.PUT_LINE( 'After set, their is no duplicate anymore' );
    PRINT( l_Integers );
 
END;
Before set, 2 is a duplicate
l_Integers is not a set 
Element: 1
Element: 2
Element: 2
After set, their is no duplicate anymore
l_Integers is a set 
Element: 1
Element: 2

6.10 - Loop

DBMS_OUTPUT.Put_Line('Loop:');
FOR i IN n.FIRST..n.LAST
LOOP
   DBMS_OUTPUT.Put_Line('element(' || i || '): ' || n(i));
END LOOP;

Loop elements:

DECLARE
   TYPE t_var IS TABLE OF VARCHAR2(30);
   t_vars t_var := t_var('One');
BEGIN
   -- Before extends
   DBMS_OUTPUT.PUT_LINE  ('before extends, COUNT is ' || t_vars.COUNT || ' elements');
   DBMS_OUTPUT.PUT_LINE  ('before extends, FIRST is ' || t_vars.FIRST );
   DBMS_OUTPUT.PUT_LINE  ('before extends, LAST is ' || t_vars.LAST);
   IF (t_vars.LIMIT IS NULL ) THEN
    DBMS_OUTPUT.PUT_LINE  ('before extends, LIMIT is NULL');
   END IF;
 
   -- Extends
   DBMS_OUTPUT.PUT_LINE  ('We extend t_vars of 1');
   t_vars.extend(1);
 
   -- After extends
   DBMS_OUTPUT.PUT_LINE  ('after extends, COUNT is ' || t_vars.COUNT);
   DBMS_OUTPUT.PUT_LINE  ('after extends, FIRST is ' || t_vars.FIRST );
   DBMS_OUTPUT.PUT_LINE  ('after extends, LAST is ' || t_vars.LAST );
   IF (t_vars.LIMIT IS NULL ) THEN
    DBMS_OUTPUT.PUT_LINE  ('after extends, LIMIT is NULL');
   END IF;
 
END;
/
before extends, COUNT is 1 elements
before extends, FIRST is 1
before extends, LAST is 1
before extends, LIMIT is NULL
We extend t_vars of 1
after extends, COUNT is 2
after extends, FIRST is 1
after extends, LAST is 2
after extends, LIMIT is NULL

6.11 - Fetch

6.12 - Destruction

n := NULL;
IF n.EXISTS(1) = FALSE THEN
   DBMS_OUTPUT.PUT_LINE('element 1 doesn''t exists.');
END IF;

6.13 - Select

table_collection_expression

The select will work

  • in SQL if the type is created as schema level. If it's a a package type definition, the table function will not work.
  • in PLSQL if the type is created at package level.
CREATE TYPE phone AS TABLE OF NUMBER;   
/

SELECT t.COLUMN_VALUE from table(phone(1,2,3)) t;
COLUMN_VALUE
------------
          1
          2
          3

6.14 - Print

PROCEDURE print_nested_table(the_nt nested_typ) IS
     output VARCHAR2(128);
  BEGIN
     IF the_nt IS NULL THEN
        DBMS_OUTPUT.PUT_LINE('Results: <NULL>');
        RETURN;
     END IF;
     IF the_nt.COUNT = 0 THEN
        DBMS_OUTPUT.PUT_LINE('Results: empty set');
        RETURN;
     END IF;
     FOR i IN the_nt.FIRST .. the_nt.LAST
     LOOP
        output := output || the_nt(i) || ' ';
     END LOOP;
     DBMS_OUTPUT.PUT_LINE('Results: ' || output);
  END;
lang/plsql/table_of.txt · Last modified: 2017/09/06 22:18 by gerardnico