PL/SQL Collections
Index-by tables (Associative Arrays): They are arrays with the only difference that we can use numbers, string literals for subscript values. Associative Arrays are sets of key-value pairs, where key can be an integer or a string. There is no limitation on the size of Associative Arrays.
Syntax: TYPE type_name IS TABLE OF element_type [NOT NULL] INDEX BY [BINARY_INTEGER | PLS_INTEGER | VARCHAR2 (size_limit)] ;
Nested Tables: They are single dimensional, array-like tables, that can hold any number of elements inside another database table and can be manipulated by SQL as other database objects.
Syntax: TYPE type_name IS TABLE OF element_type [NOT NULL];
Varying Arrays: As the name suggests, varrays (Varying Arrays) are fixed length arrays that use sequential numbers as subscripts. varrays need to be dense and we cannot remove/delete individual elements from the array. Length is defined at design time and can be manipulated at runtime subject to maximum length defined. They are suitable in cases where all elements in the array are accessed in sequence.
Syntax: TYPE type_name IS [VARRAY | VARYING ARRAY] (size_limit) OF element_type [NOT NULL];
Using PL/SQL Collections in SQL statements
NESTED TABLE
CREATE TYPE Courseslist AS TABLE OF VARCHAR2(50);
CREATE TABLE student as OBJECT (
roll_no NUMBER(2),
name VARCHAR2(20),
courses Courseslist)
NESTED TABLE courses STORE AS courses_tab;
VARRAY
CREATE TYPE Courseslist AS VARRAY(50) OF VARCHAR2(20);
CREATE TABLE student (
roll_no NUMBER(2),
name VARCHAR2(20),
courses Courseslist);
USING COLLECTIONS IN SQL
DECLARE
TYPE Courseslist is TABLE OF VARCHAR2(20);
TYPE Courseslist2 is VARRAY(40) OF VARCHAR2(20);
my_courses Courseslist;
my_courses2 Courseslist2;
BEGIN
my_courses := Courseslist(’English’, ‘Mathematics’, ‘Science’);
my_courses2 := Courseslist2(’English’, ‘Mathematics’, ‘Science’);
INSERT INTO student values(12, ‘Ankur’, Courseslist(’English’, ‘Mathematics’, ‘Science’));
END;
Manipulating Individual Elements in Collections
Using subqueries within TABLE operator we can extract varrays and Nested tables from containing table and execute INSERT, UPDATE and DELETE operations.
Syntax:
BEGIN
INSERT INTO TABLE (SELECT courses FROM student where roll_no=2) VALUES (’German’);
DELETE TABLE (SELECT courses FROM student where roll_no = 1) WHERE course_strength < 5;
END;
USING COLLECTION METHODS
EXISTS - Check if an element exists in the collection.
Syntax : IF courses.EXISTS(i) THEN courses(i) = new_course; END IF;
COUNT - Count the elements in collection
Syntax : IF courses.COUNT > 20 THEN...
LIMIT - Check the Maximum Size of a collection
Syntax: IF new_courses.COUNT <>
FIRST and LAST - Retrieve the FIRST and LAST element from collection
Syntax: IF courses.FIRST = courses.LAST THEN ...
PRIOR and NEXT - Retrieve Previous and Next element of an index in collection.
Syntax: courses(i) = courses.NEXT(courses.PRIOR(i));
EXTEND - Append a null element at the end of collection.
EXTEND (n) - Append n null elements to the collection.
EXTEND (n,m) - Append n copies of element m to the collection.
Syntax: courses.EXTEND(2,1) : Appends 2 copies of 1st element of courses collection.
TRIM - Remove the last element from a collection.
TRIM(n) - Remove n elements from the end of a collection.
Syntax: courses.TRIM(5) : Remove last 5 elements from courses collection.
DELETE - Delete all elements from a collection
DELETE(n) - Delete nth element from an associative array with numeric key or a nested table. If key is string in Associative Array, element corresponding to the key value is deleted. If n is null, DELETE(n) does nothing.
DELETE(m, n) - DELETE elements in the range m-n from an associative array or nested table.
Varrays are dense, therefore, individual elements cannot be deleted from Varrays.
BULK BINDING
Assigning of values to PL/SQL variables in SQL statements is called binding. Use FORALL statement for bulk-bind input operations before sending them to SQL engine. It improves performance by minimizing context switching between SQL Engine and PL/SQL Engine and passing whole collections to SQL ENGINE at a time during execution.
Syntax:
DECLARE
TYPE NumList is VARRAY(20) OF NUMBER;
courses NumList := NumList(10, 20, 30);
BEGIN
FORALL i in courses.FIRST..courses.LAST
DELETE FROM student where course_id = courses(i);
END;
We can redefine the length of loop to execute the FORALL statement selectively. We may count number of rows affected, exceptions occurred in FORALL statement using SQL%BULK_ROWCOUNT, SQL%BULK_EXCEPTIONS attributes after the end of the loop.
BULK COLLECT
We use BULK COLLECT to bulk-bind output collections before returning to PL/SQL Engine as so to improve the performance, in a similar way, we used FORALL statement.
Syntax: SELECT courses BULK COLLECT INTO my_courses FROM student;
We can also use BULK COLLECT with cursors.
Syntax:
BEGIN
OPEN c1;
FETCH c1 BULK COLLECT INTO my_courses;
END;
LIMIT clause to limit the rows fetched for a BULK FETCH Operation
Syntax:
BEGIN
OPEN c1;
FETCH c1 BULK COLLECT INTO my_courses LIMIT 10;
END;
RETURNING INTO Clause
We can use RETURNING INTO clause to retrieve DML results into a collection.
Syntax:
BEGIN
DELETE FROM student WHERE marks < 50;
RETURNING courses BULK COLLECT INTO my_courses;
END;
We can use Host Arrays with Bulk Binds for output and input, as they are the most effective way to pass collections between database server and clients.
Syntax:
BEGIN
FORALL i in :lower .. :upper
DELETE FROM student WHERE roll_no := roll(i);
END;