PL/SQL Reference III - Composite Datatypes and Collections

Using Types

===========================================
Using Types
===========================================
Declare the Type:
TYPE my_type IS RECORD (field_a field_a_type,
                        field_b field_b_type,
                        field_c field_c_type)
Declare variable of that Type:
v_my_var my_type 

Use the variable:
v_my_var.field_a

SELECT field_a , field_b , field_c 
  INTO v_my_var 
  FROM TABLE_A
 WHERE rownum<2;

===========================================
Comparing Types
===========================================
- Types cannot be tested for nullity.
- Types cannot be compared.

Whenever need to compare types, need to write your own procedure that would accept two records and compare them, field by field.

===========================================
Records
===========================================
Records are allowed only in:
UPDATE ... SET ROW = <RECORD>
INSERT INTO ... VALUES <RECORD>
INSERT/UPDATE ... RETURNING INTO <RECORD>

Records are NOT allowed in:
SELECT list
WHERE clause
GROUP BY clause
ORDER BY clause

===========================================
Inserting and Updating
===========================================
DECLARE
  my_table_rec my_table%ROWTYPE;
BEGIN
  my_table_rec.field_a :=1;
  my_table_rec.field_b :=2;
  --in UPDATE use SET ROW
  UPDATE my_table SET ROW = my_table_rec 
   WHERE field_c :=10;
  --in INSERT INTO use VALUES
  INSERT INTO my_table VALUES my_table_rec;
END;

===========================================
Collections
===========================================
in PL/SQL there are three types of collections:
- Index by Tables (Associative Array)
- Nested Table
- Varray

===========================================
Index by Tables (Associative Array)
===========================================
Index by Tables (Associative Array) - as structure of:
- Primary Key.  Integer or String
- Data, a record or scalar.

Primary Key
Number: Either BINARY_INTEGER/PLS_INTEGER or VARCHAR2


PLS_INTEGER - defined in the STANDARD package as a subtype of BINARY_INTEGER.
From Oracle 10G BINARY_INTEGER and PLS_INTEGER are identical.
Both  are generally faster than NUMBER and INTEGER operations.
Range: -2*31 .. 2*31, aka -2,147,483,647 to 2,147,483,647

VARCHAR2 - The size limit is the size at declaration time.

Keys order - keys are not stored in order.

===========================================
Index by Tables (Associative Array) Example
===========================================
DECLARE
  TYPE my_table_col_a_type IS TABLE OF my_table.column_a%TYPE INDEX BY PLS_INTEGER;
  TYPE my_table_type IS TABLE OF my_table%ROWTYPE INDEX BY 
PLS_INTEGER;

  v_my_table_col_a my_table_col_a_type;
  v_my_table my_table_type;
BEGIN
  --%TYPE example
  v_my_table(-1) := 1;
  v_my_table(4) := 33;
  v_my_table(10) := 232;
  --%ROWTYPE example
  SELECT *
  INTO v_my_table(1)
  FROM my_table
  WHERE my_table.column_a = 1;
my_table_rec;
END;

===========================================
Nested Tables
===========================================
Key column - numbers in sequence, from 1 to 2*31, aka 2,147,483,647
Elements can be deleted anywhere, leaving table with non sequential keys.
Nested Tables can be stored in database.

Initialization: 
Option A - Using constructor, a system defined function with the same name as the collection type.
Option B - Fetch from Database.
Option C - Assign another collection.

Size - Unlimited. No need to define at declaration time.
===========================================
Nested Tables Example
===========================================
DECLARE
  TYPE my_rec_type IS RECORD (field_a field_a_type,
                              field_b field_b_type,
                              field_c field_c_type);
  TYPE my_table_type IS TABLE OF my_rec_type;
  v_my_tab my_table_type;
BEGIN
  SELECT field_a, field_b, field_c
    BULK COLLECT INTO v_my_tab
    FROM MY_TABLE;
   
    FOR i IN v_my_tab..FIRST..v_my_tab.LAST LOOP
      //do something with v_my_tab(i).field_a...
    END LOOP;
END;
/

===========================================
Varrays
===========================================
Similar to Nested tales, only they have fixed size.

When calling to a constructor without arguments, you get an empty collection with zero elements, but it is not NULL.
===========================================
Varrays Example
===========================================
DECLARE
  TYPE my_rec_type IS RECORD (field_a field_a_type,
                              field_b field_b_type,
                              field_c field_c_type);
  TYPE my_table_type IS VARRAY(10) OF my_rec_type;
  v_my_tab my_table_type;
BEGIN
  SELECT field_a, field_b, field_c
    BULK COLLECT INTO v_my_tab
    FROM MY_TABLE;
   
    FOR i IN v_my_tab..FIRST..v_my_tab.LAST LOOP
      //do something with v_my_tab(i).field_a...
    END LOOP;
END;
/

===========================================
Collection Methods
===========================================
These are built in functions for collections
EXISTS(n) - Returns true if element n exists.
COUNT     - Returns elements number in collection.
FIRST and LAST - Return the index numbers, smallest and largest.
LIMIT     - The maximum number of elements that the collection can contain. 
                           For Nested Tables and Index by Tables it is NULL.
EXTEND  - Append elements to the collection.
          EXTEND(n)   - Appends n NULL elements.
          EXTEND(n,i) - Appends n times (i) element.
          EXTEND      - Appends 1 NULL element.
NEXT    - Return the next index number. if no next - return NULL.
PRIOR   - Return the previous index number. if no previous - return NULL.
DELETE  - Remove elements from Nested Tables and Index by Tables.
          DELETE - Remove ALL elements
          DELETE(n) - Remove n-th element.
          DELETE(n,m) - Remove all elements in n-m range.
TRIM      TRIM - Remove 1 element from the end of the collection.
          TRIM(n) - Remove n elements from the end of the collection.

===========================================
Choosing between Collections
===========================================
INDEX BY Tables
- Appropriate for small lookup tables
- Flexible, no size limit when allocating.
- Index values are flexible. Can be negative, strings, non sequential.
- This is the best option to pass collections to and from the database, using BULK COLLECT and FORALL.

Varrays
 Should be used when:
- Number of elements is known in advance
- The elements are accessed in sequence.

Nested Tables
Should be used when:
- There is no set number for index values. Index numbers are not consecutive.
- Nested tabled can be sparse - possible to delete any element, not just from the end.

===========================================
Code Example
===========================================
Example of working with arrays. in this case a Table Type.
Populate the table in loop A, and read from table in loop B.

Declaration code
  TYPE tbs_rec_type IS RECORD (service_name    ALL_DEST_VW.service_name%TYPE,
                             customer_name   ALL_DEST_VW.customer_name%TYPE,
       schema_name     ALL_DEST_VW.user_name%TYPE,
                               db_link         ALL_DEST_VW.db_link%TYPE,
       host            DBA_DB_LINKS.host%TYPE,
       tablespace_name USER_SEGMENTS.tablespace_name%TYPE);

  TYPE tbs_tab_type IS TABLE OF tbs_rec_type;
  v_tbs_tab tbs_tab_type;

Population code
SELECT getDbListRec.service_name,
       getDbListRec.customer_name,
       getDbListRec.user_name,
       getDbListRec.db_link,
       getDbListRec.host,                 
       v_tablespace_name
BULK COLLECT INTO v_tbs_tab  
FROM XXX...                      

Reading code
FOR i_tbs_tab_ix IN v_tbs_tab.FIRST .. v_tbs_tab.LAST
LOOP
   some_logic(v_tbs_tab(i_tbs_tab_ix).service_name,
              v_tbs_tab(i_tbs_tab_ix).customer_name,
              v_tbs_tab(i_tbs_tab_ix).schema_name,
              v_tbs_tab(i_tbs_tab_ix).db_link,
              v_tbs_tab(i_tbs_tab_ix).host,                 
              );
END LOOP;

===========================================
Reference
===========================================

Working with Collections - From Oracle Magazine.