PL/SQL Indexed Arrays – How to write syntax, example

Indexed arrays have elements with two components: a primary key of type BINARY_INTEGER and a column of type scalar or record.

The Indexed Arrays syntax is:

TYPE type_name IS TABLE OF {
{ element_type | variable%TYPE |
table.column%TYPE }[NOT NULL]}
| table%ROWTYPE }
INDEX BY BINARY_INTEGER;
var_array type_name;

First example:

Example below shows an array indexed by numbers, for which the first 5 natural numbers.
It will display the number of rows contained in the array and its elements.

SET SERVEROUTPUT ON
DECLARE
	TYPE type_array IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
	ta type_array;
BEGIN
	FOR i IN 1..5 LOOP
		ta (i):=i;
	END LOOP;
	DBMS_OUTPUT.PUT_LINE('The rows of array: ' ||ta.COUNT||' rows');
	FOR i IN ta.FIRST..ta.LAST LOOP
		DBMS_OUTPUT.PUT_LINE('The element ' || i||' is '|| ta(i));
	END LOOP;
END;
SET SERVEROUTPUT OFF

Second example:

In the following example we define an array of records with the type of the EMPLOYEE table.
It will insert a new record in the EMPLOYEE table, and the same recording will be saved in the table EMPLOYEE_HISTORY.

SET SERVEROUTPUT ON
DECLARE
	TYPE type_array IS TABLE OF employee%ROWTYPE INDEX BY BINARY_INTEGER;
	table_1 type_array;
	table_2 type_array;
	i BINARY_INTEGER;
BEGIN
	IF table_1.COUNT<>0 THEN 
		i:= tablou1.LAST+1;
	ELSE 
		i:=1;
	END IF;
	table_1(i).EMPLOYEE_ID:=231;
	table_1(i).FIRST_NAME:='LARRY';
	table_1(i).LAST_NAME:='ROMPUY';
	table_1(i).HIRE_DATE:=SYSDATE;
	table_1(i).SALARY:=5000;
	table_1(i).DEPARTMENT_ID:=123;
	table_2:=table_1;
	INSERT INTO employee VALUES(
	table_1(i).EMPLOYEE_ID,
	table_1(i).FIRST_NAME
	table_1(i).LAST_NAME,
	table_1(i).HIRE_DATE, 
	table_1(i).SALARY, 
	table_1(i).DEPARTMENT_ID);
	INSERT INTO employee_history VALUES(
	table_2(i).EMPLOYEE_ID,
	table_2(i).FIRST_NAME
	table_2(i).LAST_NAME,
	table_2(i).HIRE_DATE, 
	table_2(i).SALARY, 
	table_2(i).DEPARTMENT_ID);
END;
SET SERVEROUTPUT OFF