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; /
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.
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.