PL/SQL Varray – How to write syntax, example.

Unlike indexed arrays, varrays have a maximum size seted on the declaration. The PLSQL Varray are used on one-to-many relationships.

The Varray syntax is:

TYPE type_name IS {VARRAY | VARYING ARRAY} (size_limit) 
   OF element_type [NOT NULL];

Varray example 1:

DECLARE
	TYPE type_no IS VARRAY(20) OF NUMBER;
	id type_no := type_no(1200,1250,1401);
BEGIN
	FORALL i IN id.FIRST..id.LAST
	DELETE FROM customers WHERE customer_id = id (i);
END;

Varray example 2:

DECLARE
	TYPE type_custno IS TABLE OF customers.customer_id%TYPE;
	TYPE type_name IS TABLE OF customers.first_name%TYPE;
	v_custno type_custno;
	v_name type_name;
BEGIN
	SELECT customer_id,first_name 
	BULK COLLECT INTO v_custno,v_name FROM customers;
	DBMS_OUTPUT.PUT_LINE('Start');
	DELETE FROM customers WHERE email IS NULL
	RETURNING customer_id,first_name BULK COLLECT INTO v_custno, v_name;
	DBMS_OUTPUT.PUT_LINE('End');
END;