PL/SQL Collections and Records

Collections and recordings are compound data types containing internal elements like array, record or table.


Collections

Collections are data types that allow simultaneous processing of multiple variables of the same type. Each element has a unique index, which determines its position in the collection.

Types of collections: Nested Tables, Varrays, Associative Arrays (Index-By Tables)

Nested Tables:
DECLARE
TYPE test IS TABLE OF VARCHAR2(50);
t1 test ;
t2 test := test() ;
BEGIN
  IF t1 IS NULL THEN
    DBMS_OUTPUT.PUT_LINE('t1 is NULL');
  ELSE
    DBMS_OUTPUT.PUT_LINE('t1 is NOT NULL');
  END IF;
  IF t2 IS NULL THEN
    DBMS_OUTPUT.PUT_LINE('t2 is NULL');
  ELSE
    DBMS_OUTPUT.PUT_LINE('t2 is NOT NULL');
  END IF;
END;

Varrays:
DECLARE
TYPE t_code IS VARRAY(5) OF NUMBER(3);
test t_code := t_code(100,101);
BEGIN
  FOR i IN test.FIRST..test.LAST LOOP
    DELETE FROM customers WHERE customer_id = test (i);
  END LOOP;
END;

Associative Arrays:
DECLARE
TYPE type_table IS TABLE OF NUMBER;
t_table type_table:= type_table();
BEGIN
  FOR i IN 1..5 LOOP
    t_table.EXTEND;
    t_table(i) := i;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE('The table has ' || t_table.COUNT|| ' rows');
  FOR i IN t_table.FIRST..t_table.LAST LOOP
    DBMS_OUTPUT.PUT_LINE('Row ' || i||' have value '|| t_table (i));
  END LOOP;
  FOR i IN t_table.FIRST..t_table.LAST LOOP
    t_table (i) := NULL;
  END LOOP;
END;

Records

A PL/SQL record is a variable that can contain values separated, each individually addressable. A record consists of fields, and have names that can be referenced in assignments and expressions. Fields can have a recording data types and sizes.

Example:
DECLARE
TYPE cust_type IS RECORD
  (customer_id customers.customer_id%TYPE,
  customer_name customers.customer_name%TYPE,
  product_id customers.product_id%TYPE);
  rec cust_type;
BEGIN
  rec.customer_id:=500;
  rec.customer_name:='COMPANY';
  rec.product_id:='121';
  DBMS_OUTPUT.PUT_LINE(rec.customer_id||' '|| rec.customer_name ||' '|| rec.product_id);
END;

%ROWTYPE attribute is used to declare a record based on a collection of columns in database table. Fields with registration they will get their name and type of data from the table or view columns referenced in the declaration of registration.

DECLARE
   CURSOR c1 IS
   SELECT product_id, product_name, price
      FROM products;
   rec1 c1%ROWTYPE;
   TYPE ProRec2 IS RECORD (
      pro_id products.product_id%TYPE,
      pro_name products.product_name%TYPE,
      pro_price products.price%TYPE
    );
   rec2 ProRec2;
   TYPE ProRec3 IS RECORD (
      pro_id NUMBER,
      pro_name VARCHAR2(14),
      pro_price VARCHAR2(13)
    );
   rec3 ProRec3;
BEGIN
   NULL;
END;

Database online tutorials:  Transact-SQL,  Learn sqlplus