Collections and Records :: Oracle World

Post Views: 169

PLSQL offers you an option how to store variables within an array; collections and records. They can hold values that can be treated as standalone values.

Collections‘ elements must always be of the same data type. They are accessible by its unique subscript.

Records, on the other hand, can have different data types and you can access those fields by their name. We usually store there a whole row from the table or selected columns (depends on the need).

Types of collections

Nested tables
- can have from 1 up to 2147483647 number of elements
- can be created in a PL/SQL block or at a schema level (meaning you can use it outside of your block as well if defined outside of the block)
Varrays (or variable-size arrays)
- can have from 1 up to 2147483647 number of elements (you have to define it in a declaration)
- can be created in a PL/SQL block or at a schema level (meaning you can use it outside of your block as well if defined outside of the block)
Index-by tables (or associative arrays)
- can have from -2147483648 up to 2147483647 number of elements
- in case of the string, the limit is the same as it is for VARCHAR2
- can be created only in a PL/SQL block
Multidimensional arrays
- see article “Multidimensional arrays in Oracle PL/SQL“

Even though collections have only one dimension, you can easily create a multidimensional collection with elements of a collection type. In other words, a collection that will store multiple other collections.

Nested tables

Nested tables are very similar to arrays but there are some differences you have to keep in mind. First of all, you don’t have to define the number of elements while defining a nested table unlike with array, where you have to know how many elements are you going to store. With nested tables, you can dynamically change the number of stored elements.

Another difference is, that arrays are always dense. That means all values are in consecutive order and one value is followed by another until the end of the array. With nested tables, you can delete an element in the middle and there will be an empty space left – that means they are sparse.

See the example of a nested table.

-- create a collection
CREATE OR REPLACE TYPE members AS TABLE OF VARCHAR2(30);

-- create a nested table with that collection
CREATE TABLE nested_table (id_member NUMBER, member_list members)
       NESTED TABLE member_list STORE AS member_list_tab;
       
-- add some values       
INSERT INTO nested_table VALUES (1, members('Admin'));
INSERT INTO nested_table VALUES (2, members('Editor', 'CEO'));
INSERT INTO nested_table VALUES (3, members('CIO', 'Secretary', 'HelpDesk'));
INSERT INTO nested_table VALUES (4, members('Assistant'));
COMMIT;


-- check the data (all we can see that there is a collection stored but we don't see the actual values)
SELECT *
FROM nested_table


/* output
ID_MEMBER   |   MEMBER_LIST
-----------------------------
1	    |  <Collection>
2	    |  <Collection>
3	    |  <Collection>
4	    |  <Collection>
*/

-- unnesting the table (looping through each value)
SELECT * 
FROM nested_table t1, TABLE(t1.member_list) t2;

/* output
ID_MEMBER	 |	MEMBER_LIST	 |	COLUMN_VALUE
--------------------------------------------------------------
1	 	 |	<Collection>	 |	Admin
2	 	 |	<Collection>	 |	Editor
2	 	 |	<Collection>	 |	CEO
3	 	 |	<Collection>	 |	CIO
3		 |	<Collection>	 |	Secretary
3		 |	<Collection>	 |	HelpDesk
4		 |	<Collection>	 |	Assistant

*/

Index-by tables

Index-by tables (sometimes referred to as associative arrays as well) are tables holding paired values; a key and its value. All values are accessible via its unique key. If you use the key which does not exist, it will create value in the table. However, if you try to define it again with a different value it will overwrite the existing one (update). See the example demonstrating how index-by tables work.

DECLARE 
   -- define the index_by table
   TYPE members IS TABLE OF VARCHAR2(100)
       INDEX BY BINARY_INTEGER;
   
   -- create the instance    
   oracleWorldMembers members;
   
   -- just a helping variable (must be of the same data type as the INDEX BY)
   x BINARY_INTEGER;
   
BEGIN
  
   -- adding values into the table
   OracleWorldMembers(1) := 'Admin';
   OracleWorldMembers(2) := 'Moderator';
   OracleWorldMembers(3) := 'Editor';
   OracleWorldMembers(100) := 'Guest';
   OracleWorldMembers(23) := 'CEO';
   OracleWorldMembers(13) := 'Assistant';
   
   -- updating value with the defined subscript
   OracleWorldMembers(2) := 'Janitor';
   
   -- get the first element
   x := OracleWorldMembers.FIRST;
   
   -- loop through the table
   WHILE x IS NOT NULL LOOP
     
      dbms_output.put_line(OracleWorldMembers(x)||' has id '||x||'.');
      
      -- get next element from the table
      x := OracleWorldMembers.NEXT(x);
     
   END LOOP;
   
   EXCEPTION
     WHEN OTHERS THEN
         dbms_output.put_line('Ooops!');
   
END;
/



------------------------
Admin has id 1.
Janitor has id 2.
Editor has id 3.
Assistant has id 13.
CEO has id 23.
Guest has id 100.

As you can see, the output is sorted, no matter in what order you insert the records 🙂

Varrays

Varrays has a definite number of elements varying from zero (empty) to the maximum size. Access to the values is done via subscripts. Varrays are always dense! See the example below.

DECLARE
  -- varray definition
  TYPE t_varray IS 
                VARRAY(5) -- number of elements
               OF 
                NUMBER(10); -- data type of elements

  -- create the instance   
  v_items  t_varray;
  
  -- just a helping variable
  x   NUMBER;
  
BEGIN
  
  -- init the varray
  v_items := t_varray(1, 2);

  -- add extra values
  FOR i IN 150 .. 152 LOOP
    v_items.extend; -- extend (mandatory first)
    v_items(v_items.last) := i;  -- assign element
  END LOOP; 

  -- get the first value from varray
  x := v_items.FIRST;
  
  -- loop through the varray
  WHILE x IS NOT NULL LOOP
    
    dbms_output.put_line(v_items(x));
    
    -- get next element from the table
    x := v_items.NEXT(x);
    
  END LOOP;
   
   EXCEPTION
     WHEN OTHERS THEN
         dbms_output.put_line('Ooops!');

END;
/

Collections methods

There are multiple methods allowing you access, insert, modify, or delete data in collections.

EXISTS
- returns true if the given element exists
- usage EXISTS(x)
COUNT
- returns the number of elements existing at the moment in a collection
LIMIT
- in case of nested tables, it returns NULL because there is no limit set
- in case of varrays, it returns the maximum limit of elements it can be stored
FIRST
- returns the first element in the collection
LAST
- returns the last element in the collection
PRIOR
- returns the previous element in the collection from the given subscript
- PRIOR(x) will return the element that precedes the element with index x
- if there is no element, it returns NULL
NEXT
- returns the following element in the collection from the given subscript
- NEXT(x) will return the element that succeeds the element with index x
- if there is no element, it returns NULL
EXTEND – there are three ways of usage
- EXTEND – will add a NULL element at the end of the collection
- EXTEND(x) – will add x-times NULL element at the end of the collection
- EXTEND(x,y) – will add x-times element with index y at the end of the collection
TRIM
- cannot be used with index-by tables
- with no parameter, it will remove the last element from the collection
- with a parameter -> TRIM(x), it will remove X last parameters from the collection
  - if the X is higher than COUNT (number of elements) it will return an error SUBSCRIPT_BEYOND_COUNT
DELETE – there are three ways of usage
- DELETE – removes the last element in the collection
- DELETE(x) – removes the element with index x in the collection
- DELETE(x,y) – removes all elements in an index range between x and y. If x is greater than y than nothing happens.

Records

I will show you a few examples of records usage at the end of this section. Records might be pretty handy when you need to use a little more complicated structures.

-- simple record
DECLARE
   TYPE t_recOrders IS RECORD (
      id_order NUMBER(4) NOT NULL := 99, -- if NOT NULL, you always have to define a default value
      name_product    products.name_product%TYPE,
      code_type       action_codes%ROWTYPE );
   orders_rec t_recOrders;
BEGIN
   orders_rec.name_product := 'Laptop';
END;
/

-- record as a parameter
DECLARE
   TYPE t_OraWorldMmbr IS RECORD (
      id            NUMBER(6),
      flag_locked   NUMBER );
   PROCEDURE unlock_user (member t_OraWorldMmbr) IS
   BEGIN

      UPDATE member 
      SET flag_locked = 0
      WHERE id_member = member.id;

   END unlock_user;
BEGIN
    -- call the procedure or/and do something else
END;
/