A collection just like an array is an ordered group of elements of the same type. Each elements position is determined by a unique subscript.

Index by tables:- They are similar to hash arrays that allows to search for subscript values using arbitrary numbers and strings.

They can be declared as:

TYPE type_name IS TABLE OF element_type [NOT NULL]

INDEX BY [BINARY_INTEGER | PLS_INTEGER | VARCHAR2(size_limit)];

INDEX BY key_type;

Example:

TYPE studenttyp IS TABLE OF emp%ROWTYPE

INDEX BY BINARY_INTEGER;

stud_tab studenttyp;

Nested tables:- they hold random number of elements and use sequential numbers as sub scripts.

They can be declared as:

TYPE type_name IS TABLE OF element_type [NOT NULL];

Example: TYPE employee_type IS TABLE OF NUMBER INDEX BY VARCHAR2(64);

Varrays: Holds a fixed number of elements which can be changed in run time. 

They can be declared as:

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

Example: TYPE Calendar IS VARRAY(366) OF DATE;

EXECUTE IMMEDIATE command can be used to call DDL statements. 

Example:

BEGIN

EXECUTE IMMEDIATE  'TRUNCATE TABLE employee';

END;

The UTL_FILE database package can be used to read and write operating system files. You need to have read /write access rights in that directory before the package can be used.

Example to write file:

Fhandler is a variable of type UTL_FILE.FILE_TYPE

UTL_FILE.PUTF(fHandler, 'Im writing to a file\n');

Example to read file:

UTL_FILE.GET_LINE(fHandler, buf);

Even though both PL/SQL and Java (or) .NET can be used, PL/SQL stands above these two in terms of integration overhead. This is because Java is an open source proprietary and Data manipulation is slightly faster in PL/SQL than in Java. 

- Because of the block nature, multiple statements are processed at once thereby improving performance. 

- PL/SQL handles exceptions on catching which, action can be taken. 

- PL/SQL is highly portable as it works with all procedural languages and is highly secured because of privileges.

In a correlated query, the outer query is related to the inner query. This means one or more columns in the outer query are referenced. It’s used when the outer queries value is being used by inner query. For example, we need to find which employee had more perks in the current month than they did in the previous month. The correlated subquery is executed for each row of perks information in the parent query to first determine what the perks were for each employee in the previous month. This data, in turn, is compared to perks for each employee in the current month, and only those employees whose perks in the current month were greater that their previous month's perks are returned.

Joins are used to combine data of one or more tables. Joins should be used when there is abundant data. Joins can be LEFT, RIGHT, OUTER, INNER or even SELF JOIN. The purpose is to bind data from multiple tables without any receptivity

The number of rows returned by a select query can be restricted by the LIMIT clause.
Example:
SELECT * from employee
LIMIT 20