Nested Table in PLSQL

Nested Table :

In earlier post we had learnt about VARRAYS, now lets see about Nested table concept.

1) Like VARRAY ,Nested Table size doesn't fix and its Maximum size is unlimited
2) Like VARRAY,Nested table also can be created at SQL and PLSQl level.

as we already know if we create at SQL level then we can use it as DATA TYPE.

Ref : How to use VARRAY/Nested table as data Type in table creation or package level.Check below post .

https://myplsqltutorial.blogspot.in/2017/02/how-to-use-varraynested-table-as-data.html




3) Only Difference between VARRAY and Nested Table is Size ,
In Varray while declaration time itself we wll come to know Maximum size of the collection but in Nested table there is no fixed size .

So based on this point ,you would be able to understand when to use VARRAY and Nested Table.

If you know maximum size before in hand ,then go for VARRAY otherwise NESTED Table. Most of the people use nested table for not to take risk on this size.


Syntax:

CREATE OR REPLACE TYPE TYPE_NAME IS TABLE Of DATATYPE;

Eg:

SQL> CREATE OR REPLACE Type nested_table_Varchar_type is Table Of Varchar2(32767);
  2  /

Type created

SQL> CREATE OR REPLACE Type nested_table_Integer_type is Table Of Integer;
  2  /

Type created


One thing to remember is ,we cant create Nested table OR VARRAY with %TYPE In SQL level ,only we can use %TYPE in PLSQL level with Collections.
Because we know that ,if its created in SQL level then it ill work as DATA type so it will accept only data types to work as a data type anywhere in your schema.

Program 1: 



SQL> DECLARE
  2    TYPE INTEGER_TYPE IS TABLE OF EMP.EMPNO%TYPE;
  3  --Initializing nested table at declaration time itself
  4    V_INTEGER_TYPE INTEGER_TYPE := INTEGER_TYPE();
  5    V_INDX         INTEGER := 0;
  6    CURSOR C1 IS
  7      SELECT EMPNO FROM EMP;
  8  BEGIN
  9  -- If you want to initialize the nested table in body  then you can use as
 10  --below command and as of now in this program i commented because
 11  --in declare section itself it got initialized.
 12  -- V_INTEGER_TYPE := INTEGER_TYPE();
 13    FOR J IN C1 LOOP
 14      V_INTEGER_TYPE.EXTEND;
 15      V_INDX := V_INDX + 1;
 16      V_INTEGER_TYPE(V_INDX) := J.EMPNO;
 17      DBMS_OUTPUT.PUT_LINE(V_INTEGER_TYPE(V_INDX));
 18 
 19    END LOOP;
 20    --Checking count in nested table
 21    DBMS_OUTPUT.PUT_LINE('No of Record in collection ' ||
 22                         V_INTEGER_TYPE.count);
 23 
 24    --Deleting 1 element/subscript from nested table
 25    V_INTEGER_TYPE.DELETE(1);
 26    --Checking count in nested table after deletion
 27    DBMS_OUTPUT.PUT_LINE('No of Record in collection ' ||
 28                         V_INTEGER_TYPE.count);
 29 
 30  END;
 31  /

7369
7499
7521
7566
7654
7698
7782
7788
7839
7844
7876
7900
7902
7934
No of Record in collection 14
No of Record in collection 13

PL/SQL procedure successfully completed


Program 2:

SQL> SET SERVEROUTPUT ON;
SQL>
SQL> DECLARE
  2    TYPE INTEGER_TYPE IS TABLE OF EMP.EMPNO%TYPE;
  3  -- Initializing and assigning values to collection at declaration time itself
  4    V_INTEGER_TYPE INTEGER_TYPE := INTEGER_TYPE(10, 20, 30, 41, 51);
  5    V_INDX         INTEGER := 0;
  6 
  7  BEGIN
  8  --Check Count
  9    DBMS_OUTPUT.PUT_LINE('No of Record in collection ' ||
 10                         V_INTEGER_TYPE.count);
 11--  Delete 3rd element from nested table,
     -- DELETE(3) Means it wont delete 3 elements from end of nested table 
     --  It ill just delete 3rd element/Subscript from collection
 12    V_INTEGER_TYPE.DELETE(3);
 13  --  Getting 1st subscript to move back or front
 14    V_INDX := V_INTEGER_TYPE.FIRST;
   
 15  DBMS_OUTPUT.PUT_LINE('Element is ' || V_INDX);
 16    WHILE V_INDX IS NOT NULL LOOP
 17      DBMS_OUTPUT.PUT_LINE('Element Value ' || V_INTEGER_TYPE(V_INDX));
-- Get next subscript and it ill be success for 4 times, after that it ill return NULL
-- So condition will be failed and  exit the loop
 18      V_INDX := V_INTEGER_TYPE.NEXT(V_INDX);
 19    END LOOP;
 20  END;
 21  /

No of Record in collection 5
Element Value 10
Element Value 20
Element Value 41
Element Value 51

PL/SQL procedure successfully completed