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
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;
another example as follows:
DECLARE
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
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:
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;