Oracle PL/SQL Nested Tables

These are similar to index by table but these can be stored in database columns but index by tables cannot be stored in database columns.

A nested table can be considered as a single-column table that can either be in memory, or as a column in a database table. A nested table is quite similar to a VARRAY with the exception that the order of the elements is not static. Elements can be deleted or added anywhere in the nested table where as a VARRAY can only add or delete elements from the end of the array. Nested Table is known as a sparse collection because a nested table can contain empty elements.

Nested tables are a superior choice when:

  • You need to delete or update some elements, but not all the elements at once.
  • The index values are not consecutive.
  • We don’t have any predefined upper bound for index values.

    • Nested TableVarraysIndex-by-tables
    Declare
    Cursor name_cur IS

    Select last_name
    From student
    Where rownum <= 10; Type last_name_type Is Table Of student.last_name%Type; last_name_tab
    last_name_type :=last_name_type();
    v_counter INTEGER := 0;
    BEGIN
    FOR name_rec IN name_cur
    LOOP
    v_counter :=
    v_counter + 1;
    last_name_tab.EXTEND;
    last_name_tab(v_counter)
    := name_rec.last_name;
    DBMS_OUTPUT.PUT_LINE
    ('last_name('||v_counter||'):
    '||
    last_name_tab(v_counter));
    END LOOP;
    END;    		Declare
    Cursor name_cur
    IS

    Select last_name
    From student
    Where rownum <= 10; Type last_name_type Is Varray(10) OF student. last_name%TYPE; last_name_varray last_name_type := last_name_type(); v_counter INTEGER := 0; BEGIN FOR name_rec IN name_cur LOOP v_counter := v_counter + 1; last_name_varray. EXTEND; last_name_varray(v_counter) := name_rec.last_name; DBMS_OUTPUT.PUT_LINE ('last_name('||v_counter||'): '|| last_name_varray(v_counter)); END LOOP; END;    		Declare

    Cursor name_cur IS
    Select last_name
    From student
    Where rownum <= 10; Type last_name_type Is Table Of student.last_name%TYPE Index By Binary_Integer; last_name_tab last_name_type; v_counter INTEGER := 0; Begin For name_rec IN name_cur Loop v_counter := v_counter + 1; last_name_tab(v_counter) := name_rec.last_name; Dbms_Output.Put_Line ('last_name('||v_counter||'): '|| last_name_tab(v_counter)); END LOOP; END;
    Type number_tab
    as table of number;
    numb_list2 number_tab := number_tab(23,56,34,890,21);
    SQL> declare
    2 type number_tab is table of number;
    3 numb_list number_tab := number_tab(23,56,34,890,21);

    4 begin
    5 for indx in numb_list.first..numb_list.last loop
    6 dbms_output.put_line(numb_list(indx));
    7 end loop;
    8 numb_list.delete(2);
    9 numb_list.delete(4);
    10 for indx in numb_list.first..numb_list.last loop
    11 if numb_list.exists(indx) then
    12 dbms_output.put_line(numb_list(indx));
    13 end if;
    14 end loop;
    15 end;
    16 /

    CREATE TYPE BeerType AS OBJECT (

    name CHAR(20),
    kind CHAR(10),
    color CHAR(10)
    );

    CREATE TYPE BeerTableType AS

    TABLE OF BeerType;
    CREATE TABLE Manfs (
    name CHAR(30),
    addr CHAR(50),
    beers BeerTableType
    )NESTED TABLE beers STORE AS
    BeerTable;
    		Create Type
    varray_address_typ
    AS Varray(2) OF
    Varchar2(50);

    /

    Create Table
    customers_with_varray
    (
    id
    Integer Primary key,
    first_name Varchar2(10),

    last_name Varchar2(10),
    addresses varray_address_typ
    );

    Insert Into customers_with_varray Values (
    1, 'Steve', 'Brown',
    varray_address_typ(

    '2 State Street, Beantown, MA, 12345',
    '4 Hill Street, Lost Town, CA, 54321'

    )
    );
    Update
    customers_with_varray
    SET addresses = varray_address_typ(

    '3 New Street, Middle Town, CA, 123435',
    '4 Hill Street, Lost Town, CA, 54321'

    )
    Where id = 1;



    Nested tableVarraysIndex-by-tables
    No maximum Length(unbounded)Maximum lengthNo maximum Length(unbounded)
    A nested table is similar to a VARRAY except that it is a sparse collection, meaning that it can have deleted elements contained in the collection.
    (Sparsity means whether there can be gaps in between the subscripts.)
    		A VARRAY is a dense collection, meaning that you can only add or remove objects from the end.It can always be sparse
    Can be stored in DatabaseCan be stored in DatabaseCannot be stored in Database
    Initialization:
    Via constructor, fetch, assignment
    		Via constructor, fetch, assignmentAutomatic, when declared

    ·

    Labels:

    Recent Tutorials

    Loading...