Records and Collections

26.1 Records

DECLARE
    TYPE emp_rec_t IS RECORD (id employees.employee_id%TYPE, name VARCHAR2(100), sal NUMBER := 0);
    r emp_rec_t;
    r2 employees%ROWTYPE;
    CURSOR c IS SELECT employee_id, last_name FROM employees;
    r3 c%ROWTYPE;
BEGIN
    SELECT employee_id, last_name, salary INTO r FROM employees WHERE employee_id = 100;
    SELECT * INTO r2 FROM employees WHERE employee_id = 101;
    r2.salary := 9999;
    UPDATE employees SET ROW = r2 WHERE employee_id = 101;   -- record-based DML
END;
/

26.2 Collection Types

Stored in DB column Associative array (INDEX BY) PLS_INTEGER or VARCHAR2 Nested table Can become sparse Yes (max size)

Methods: COUNT, FIRST, LAST, NEXT, PRIOR, EXISTS, EXTEND, TRIM, DELETE, LIMIT.

Collection exceptions: COLLECTION_IS_NULL, SUBSCRIPT_BEYOND_COUNT, SUBSCRIPT_OUTSIDE_LIMIT, NO_DATA_FOUND.

Qualified expressions (18c): names := name_aa(1 => 'A', 2 => 'B');.

Nested table operators: MULTISET UNION/INTERSECT/EXCEPT [DISTINCT], SET(), MEMBER OF, SUBMULTISET, CARDINALITY, IS A SET.

SQL usage: schema-level types (CREATE TYPE num_list AS TABLE OF NUMBER;) with TABLE():

DECLARE
    TYPE name_aa  IS TABLE OF VARCHAR2(50) INDEX BY PLS_INTEGER;
    TYPE price_aa IS TABLE OF NUMBER INDEX BY VARCHAR2(30);
    TYPE num_nt   IS TABLE OF NUMBER;
    TYPE day_va   IS VARRAY(7) OF VARCHAR2(10);

    names  name_aa;
    prices price_aa;
    nums   num_nt := num_nt(10, 20, 30);
    days   day_va := day_va('Mon', 'Tue');
    k      VARCHAR2(30);
BEGIN
    names(1) := 'Asha'; names(5) := 'Ravi';
    prices('APPLE') := 120; prices('MANGO') := 80;

    k := prices.FIRST;
    WHILE k IS NOT NULL LOOP
        DBMS_OUTPUT.PUT_LINE(k || ' = ' || prices(k));
        k := prices.NEXT(k);
    END LOOP;

    nums.EXTEND; nums(nums.LAST) := 40;
    nums.DELETE(2);
    DBMS_OUTPUT.PUT_LINE('Count: ' || nums.COUNT || ', Exists(2): ' || CASE WHEN nums.EXISTS(2) THEN 'Y' ELSE 'N' END);
END;
/

SELECT * FROM employees WHERE employee_id IN (SELECT column_value FROM TABLE(num_list(100, 101, 102)));