PL/SQL Collections and Records

Collections and recordings are compound data types containing internal elements like array, record or table.

Collections

Collections are data types that allow simultaneous processing of multiple variables of the same type. Each element has a unique index, which determines its position in the collection.

Types of collections: Nested Tables, Varrays, Associative Arrays (Index-By Tables)

Nested Tables:

DECLARE
	TYPE test IS TABLE OF VARCHAR2(50);
	t1 test ;
	t2 test := test() ;
BEGIN
	IF t1 IS NULL THEN
		DBMS_OUTPUT.PUT_LINE('t1 is NULL');
	ELSE
		DBMS_OUTPUT.PUT_LINE('t1 is NOT NULL');
	END IF;
	IF t2 IS NULL THEN
		DBMS_OUTPUT.PUT_LINE('t2 is NULL');
	ELSE
		DBMS_OUTPUT.PUT_LINE('t2 is NOT NULL');
	END IF;
END;

Varrays:

DECLARE
	TYPE t_code IS VARRAY(5) OF NUMBER(3);
	test t_code := t_code(100,101);
BEGIN
	FOR i IN test.FIRST..test.LAST LOOP
		DELETE FROM customers WHERE customer_id = test (i);
	END LOOP;
END;

Associative Arrays:

DECLARE
	TYPE type_table IS TABLE OF NUMBER;
	t_table type_table:= type_table();
BEGIN
FOR i IN 1..5 LOOP
	t_table.EXTEND;
	t_table(i) := i;
END LOOP;
DBMS_OUTPUT.PUT_LINE('The table has ' || t_table.COUNT|| ' rows');
FOR i IN t_table.FIRST..t_table.LAST LOOP
	DBMS_OUTPUT.PUT_LINE('Row ' || i||' have value '|| t_table (i));
END LOOP;
FOR i IN t_table.FIRST..t_table.LAST LOOP
	t_table (i) := NULL;
END LOOP;
END;

Records

A PL/SQL record is a variable that can contain values separated, each individually addressable.
A record consists of fields, and have names that can be referenced in assignments and expressions. Fields can have a recording data types and sizes.

Example:

DECLARE
	TYPE cust_type IS RECORD
	(customer_id customers.customer_id%TYPE,
	customer_name customers.customer_name%TYPE,
	product_id customers.product_id%TYPE);
	rec cust_type;
BEGIN
	rec.customer_id:=500;
	rec.customer_name:='COMPANY';
	rec.product_id:='121';
	DBMS_OUTPUT.PUT_LINE(rec.customer_id||' '|| rec.customer_name ||' '|| rec.product_id);
END;

%ROWTYPE attribute is used to declare a record based on a collection of columns in database table.
Fields with registration they will get their name and type of data from the table or view columns referenced in the declaration of registration.

DECLARE
	CURSOR c1 IS 
	SELECT product_id, product_name, price 
	FROM products;
	rec1 c1%ROWTYPE;
	TYPE ProRec2 IS RECORD (
		pro_id    products.product_id%TYPE, 
		pro_name  products.product_name%TYPE, 
		pro_price products.price%TYPE
		);
	rec2 ProRec2;
	TYPE ProRec3 IS RECORD (
		pro_id    NUMBER, 
		pro_name  VARCHAR2(14), 
		pro_price VARCHAR2(13)
		);
	rec3 ProRec3;
BEGIN
   NULL;
END;