Associative Arrays, Nested Tables, and Varrays two minutes drill

Associative Arrays

Associative arrays are sets of key-value pairs, where each key is unique and is used to locate a corresponding value in the array. The key can be either integer- or character-based. The array value may be of the scalar data type (single value) or the record data type (multiple values).
Because associative arrays are intended for storing temporary data, you cannot use them with SQL statements such as INSERT and SELECT INTO.

The following methods make associative arrays easier to use:

EXISTS
COUNT
FIRST
LAST
PRIOR
NEXT
DELETE

DECLARE
V_EMP_NUMBER NUMBER := 124;
V_EMP_REC EMPLOYEES%ROWTYPE;
BEGIN
SELECT * INTO V_EMP_REC FROM EMPLOYEES WHERE EMPLOYEE_ID = V_EMP_NUMBER;

INSERT INTO RETIRED_EMPS
(EMP_NO, FIRST_NAME, LAST_NAME,
EMAIL, PHONE_NUMBER, HIRE_DATE,
JOB, SAL, COMM,
MGR, DEPT_NO)
VALUES
(V_EMP_REC.EMPLOYEE_ID ,V_EMP_REC.FIRST_NAME ,V_EMP_REC.LAST_NAME ,
V_EMP_REC.EMAIL,V_EMP_REC.PHONE_NUMBER ,V_EMP_REC.HIRE_DATE ,
V_EMP_REC.JOB_ID,V_EMP_REC.SALARY ,V_EMP_REC.COMMISSION_PCT ,
V_EMP_REC.MANAGER_ID, V_EMP_REC.DEPARTMENT_ID);
END;
/

DECLARE
V_EMP_NUMBER NUMBER := 124;
V_EMP_REC EMPLOYEES%ROWTYPE;
BEGIN
SELECT * INTO V_EMP_REC FROM EMPLOYEES WHERE EMPLOYEE_ID = V_EMP_NUMBER;
V_EMP_REC.COMMISSION_PCT := 0.1;
V_EMP_REC.EMPLOYEE_ID:= V_EMP_NUMBER*10;

INSERT INTO RETIRED_EMPS VALUES V_EMP_REC;
END;
/

DECLARE
V_EMP_NUMBER NUMBER := 1240;
V_EMP_REC RETIRED_EMPS%ROWTYPE;

BEGIN<
SELECT * INTO V_EMP_REC FROM RETIRED_EMPS WHERE EMP_NO = V_EMP_NUMBER;
V_EMP_REC.COMM := 0.9;

UPDATE RETIRED_EMPS SET ROW = V_EMP_REC WHERE  EMP_NO = V_EMP_NUMBER;
END;
/

Nested Tables

A nested table holds a set of values. In other words, it is a table within a table. Nested tables are unbounded; that is, the size of the table can increase dynamically. Nested tables are available in both PL/SQL and the database. Within PL/SQL, nested tables are like one-dimensional arrays whose size can increase dynamically.

Varrays

Variable-size arrays, or varrays, are also collections of homogeneous elements that hold a fixed number of elements (although you can change the number of elements at run time). They use sequential numbers as subscripts. You can define equivalent SQL types, thereby allowing varrays to be stored in database tables.