Collections & Record Interview Questions

What is a collection in pl/sql ?

A collection is an ordered group of elements, all of the same type. It is a general concept that encompasses lists, arrays, and other datatypes used in classic programming algorithms. Each element is addressed by a unique subscript.

PL/SQL has three collection types:

An associative array (also called an index-by table) is a set of key-value pairs. Each key is unique, and is used to locate the corresponding value. The key can be either an integer or a string. Using a key-value pair for the first time adds that pair to the associative array. Using the same key with a different value changes the value.

Example : Declaring and Using an Associative Array

SQL> DECLARE

2 -- Associative array indexed by string:

3

4 TYPE population IS TABLE OF NUMBER -- Associative array type

5 INDEX BY VARCHAR2(64);

6

7 city_population population; -- Associative array variable

8 i VARCHAR2(64);

9

10 BEGIN

11 -- Add new elements to associative array:

12

13 city_population('Agra') := 2000;

14 city_population('Noida') := 750000;

15 city_population('Delhi') := 1000000;

16

17 -- Change value associated with key 'Agra':

18

19 city_population('Agra') := 2001;

20

21 -- Print associative array:

22

23 i := city_population.FIRST;

24

25 WHILE i IS NOT NULL LOOP

26 DBMS_Output.PUT_LINE

27 ('Population of ' || i || ' is ' || TO_CHAR(city_population(i)));

28 i := city_population.NEXT(i);

29 END LOOP;

30 END;

31 /

Population of Delhi is 1000000

Population of Noida is 750000

Population of Agra is 2001

PL/SQL procedure successfully completed.

SQL>

An associative array is intended for temporary data storage. To make an associative array persistent for the life of a database session, declare the associative array (the type and the variable of that type) in a package, and assign values to its elements in the package body.

A nested table is like a one-dimensional array with an arbitrary number of elements. Within the database, a nested table is a column type that holds a set of values.

Example : Declaring and Using a nested table:

SQL> DECLARE

2 TYPE names_table IS TABLE OF VARCHAR2(10);

3 TYPE grades IS TABLE OF INTEGER;

4

5 names names_table;

6 marks grades;

7 total integer;

8 BEGIN

9 names := names_table('Jitendra', 'Chinmay', 'Ankit', 'Abhinav', 'Vivek');

10 marks:= grades(98, 97, 78, 87, 92);

11 total := names.count;

12 dbms_output.put_line('Total '|| total || ' Students');

13 FOR i IN 1 .. total LOOP

14 dbms_output.put_line('Student:'||names(i)||', Marks:' || marks(i));

15 end loop;

16 END;

17 /

Total 5 Students

Student:Jitendra, Marks:98

Student:Chinmay, Marks:99

Student:Ankit, Marks:78

Student:Abhinav, Marks:87

Student:Vivek, Marks:92

PL/SQL procedure successfully completed.

SQL>

Elements of a nested table could also be a %ROWTYPE of any database table or %TYPE of any database table field.

Example:

TYPE DocIndex_TYPE IS TABLE OF ISDoc.DocIndex%TYPE;

doc_type DocIndex_TYPE;

doc_size DocIndex_TYPE;

A varray is a variable-size array of a datatype which has a maximum size, which you specify in its type definition. A varray can contain a varying number of elements, from zero (when empty) to the maximum size. To access an element of a varray, you use standard subscripting syntax.

Example :

      DECLARE

         TYPE Calendar IS VARRAY(366) OF DATE;

What is the difference between varray and nested table ?

• A varray has a declared number of elements, but a nested table does not. The size of a nested table can increase dynamically.
• An varray is always dense (that is, it always has consecutive subcripts). A nested table is dense initially, but it can become sparse, because you can delete elements from it.

What is a Record in pl/sql ?

A record is a group of related data items stored in fields, each with its own name and datatype. You can think of a record as a variable that can hold a table row, or some columns from a table row.

DECLARE

-- Declare a record type with 3 fields.

TYPE rec1_t IS RECORD (field1 VARCHAR2(16), field2 NUMBER, field3 DATE);

What is difference between % ROWTYPE and TYPE RECORD ?

% ROWTYPE is to be used whenever query returns a entire row of a table or view.

TYPE rec RECORD is to be used whenever query returns columns of different table or views and variables.

E.g. TYPE r_emp is RECORD (eno emp.empno% type,ename emp ename %type);

         e_rec emp% ROWTYPE

         cursor c1 is select empno,deptno from emp;

         e_rec c1 %ROWTYPE.