pl/sql-collection

There are three kinds of collection for pl/sql:

1 nested tables - covered in part 10 of this Oracle PLSQL tutorial

2 varrays - covered in this tutorial

3 PL/SQL associative arrays - covered in part 12 of this PL/SQL tutorial .

 

 1 http://www.smart-soft.co.uk/Oracle/oracle-plsql-tutorial-part-12.htm

   DECLARE

  CURSOR name_cur IS
  SELECT last_name
  FROM student
  WHERE rownum <= 10;
  
  --the nested table is initialized at the time of declaration
  --Notice that this declaration is very similar to the declaration of an associative array, except that
  --it has no INDEX BY BINARY_INTEGER clause.
  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;
    
    --In the cursor loop is a statement with one of the collection methods,
    --EXTEND. This method allows you to increase the size of the collection.
    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;

 

 

2 http://www.smart-soft.co.uk/Oracle/oracle-plsql-tutorial-part-11.htm

A good example of the use of these would be for storing telephone numbers or addresses for say employees - each person is unlikely to have more than half a dozen telephone numbers or addresses. We can create a table for storing these as follows:-

 

 TYPE phone_no_tab_typ IS VARRAY(6) OF VARCHAR2(20;

phone_nos phone_no_tab_typ;

 

 TYPE varray_type IS VARRAY(10) OF NUMBER;

varray varray_type := varray_type(1, 2, 3, 4, 5, 6);

 

 another example as follows: 

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;

 

 

3 http://www.smart-soft.co.uk/Oracle/oracle-plsql-tutorial-part-10.htm

   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;
  
  DBMS_OUTPUT.PUT_LINE ('last_name(11): '||
  last_name_tab(11));
END;

 

 Please pay attention to the index-by table of associative arrays:

 

set serveroutput on;
DECLARE
     type num_tab is table of varchar2(1000) 
     index by binary_integer;
     
     v_example_tab num_tab;
     v_num number:=13;
BEGIN
     
    v_example_Tab(-10):='abbb';
    dbms_output.put_line(v_example_Tab(-10));
END;