Oracle PL/SQL Nested Table Iteration
In PL/SQL nested tables, the data is stored in the same order as they were loaded. When an element is deleted gaps are created in the sequence (the elements two and three are deleted in the example below). While iterating through the sequence, the gaps have to be skipped. This is done by PRIOR and NEXT methods. The EXISTS can be used to check for existance of an element, which returns a boolean value. The PRIOR and NEXT methods return null when elements are not present. The example below shows the use of EXISTS, NEXT and PRIOR in iterating through a PL/SQL nested table.

DECLARE
  nt_base nt_numtype := nt_numtype(1, 2, 3, 4, 6, 3, 4, 2, 6, 5, 7, 18);
  idx PLS_INTEGER;
  v_data VARCHAR2(200) := '(';
 
  FUNCTION op_nt(i_nt IN nt_numtype) RETURN VARCHAR2 IS
   s_Nested_tab VARCHAR2(200);
  BEGIN
 
   FOR idx IN i_nt.FIRST .. i_nt.LAST LOOP
    IF idx < i_nt.COUNT THEN
     s_nested_tab := s_nested_tab||i_nt(idx)||', ';
    ELSE
     s_nested_tab := s_nested_tab||i_nt(idx);
    END IF;
   END LOOP;
   RETURN s_nested_tab;
  END op_nt;
 
BEGIN
  DBMS_OUTPUT.PUT_LINE(CHR(10)||'nt_base ['||nt_base.COUNT||'] '||op_nt(nt_base));
  idx := nt_base.FIRST;
  nt_base.DELETE(2);
 
  WHILE nt_base.EXISTS(idx) LOOP
    v_data := v_data||nt_base(idx);
    idx := nt_base.NEXT(idx);
    IF nt_base.EXISTS(idx) THEN
      v_data := v_data||', ';
    END IF;
  END LOOP;
  v_data := v_data||')';
  DBMS_OUTPUT.PUT_LINE(CHR(10)||'nt_base ['||nt_base.COUNT||'] '||v_data);
 
  idx := nt_base.LAST;
  nt_base.DELETE(2); -- no effect (non-existant element)
  nt_base.DELETE(3);
  v_data := '(';
 
  WHILE nt_base.EXISTS(idx) LOOP
    v_data := v_data||nt_base(idx);
    idx := nt_base.PRIOR(idx);
    IF nt_base.EXISTS(idx) THEN
      v_data := v_data||', ';
    END IF;
  END LOOP;
  v_data := v_data||')';
  DBMS_OUTPUT.PUT_LINE(CHR(10)||'nt_base ['||nt_base.COUNT||'] '||v_data);
END;
/



Back

Oracle registered trademark of Oracle Corporation.
Last Revised On: November 10, 2013

  1452