Cursors and Collections

Q1. Ref Cursors?

Ref Cursors is one of the most powerful, flexible and scalable ways to return query results from
an Oracle Database to a Client Application.
Op_data OUT SYS_REFCURSOR
OPEN op_data FOR
SELECT……

Q2. Record and its types?

A Record is a data structure that can hold data items of different kinds. Records consist of
different fields, similar to a row of a database table.
For example: To keep track of books in a library, we might want to track the following attributes
about each book, such as Title, Author, Subject, Book ID.
It is of 3 types:
- Table Based
- Cursor Based Records
- User Defined Records.

a) Table Based
The % ROWTYPE attribute enables a programmer to create table-based and cursor based
records.
DECLARE customer_rec customers%rowtype;
BEGIN
SELECT * INTO customer_rec FROM customers WHERE id=5;
dbms_output.putline(‘Customers ID:’||customer_rec.id);
dbms_output.putline(‘Customer Name:’||customer_rec.name);
END;
/

b) Cursor Based Records

DECLARE
CURSOR customer_cur IS
SELECT id, name, address FROM customers;
customer_rec customer_cur%rowtype;
BEGIN
OPEN customer_cur;
LOOP
FETCH customer_cur INTO customer_rec
EXIT WHEN customer_cur%NOTFOUND;
 dbms_output.putline(‘Customer_rec.id:’||’ ‘ ||customer_rec.name);
END LOOP;
END;
/

c) User Defined Records

DECLARE
Type books IS RECORD
(
Title varchar(50);
Author varchar(50);
subject varchar(50);
book_id varchar(50);
);
Book1 books;
Book2 books;

Q3. Collection and its types?

A collection is an ordered group of elements having the same data type. Each element is
identified by a unique subscript that represents its position in the collection. Developers
utilize collections to 'cache' static data that needs to be regularly accessed.
PL/SQL provides three collection types −

 Variable-size Array or Varray

 Nested Table
 Index-by tables or Associative array

Variable Size Array or Index-By Tables or

SrNo. Varray Nested Table Associative Arrays
It is Equivalent to
1 It is Equivalent to Array. It is Equivalent to Lists. Hash.
TYPE name IS
TYPE name IS TABLE OF datatype
VARRAY(n) OF TYPE name IS TABLE OF INDEX BY
2 datatype; datatype; indexdatatype;
TYPE salary IS
TYPE weekdays IS TABLE OF number
VARRAY(7) OF TYPE empname IS TABLE OF INDEX BY
3 varchar2(30); varchar2(20); varchar2(20);
The maximum limit is
defined. Here (n)
4 defined in example. There is no maximum limit defined.
5 We are not able to We will be able to delete. Suppose
Delete a specific 2nd Item is not required, we can go
 element in a Varray. and delete the index.
Indextype is
automatically Indextype is automatically We are able to define
maintained by Oracle is maintained by Oracle is of integer the datatype for index
6 of integer type. type. part.
Number of elements Number of elements are Number of elements
7 are bounded Unbounded are Unbounded
Subscript Type:
8 Subscript Type: Integer Subscript Type: Integer Integer or String
Either Dense or
9 Always Dense Starts dense, can become parse Parse
It is created either in
PL/SQL Block or at It is created either in PL/SQL Block It is created in
10 schema level or at schema level PL/SQL Block.

Q4. What are Collection Methods?

PL/SQL provides the built-in collection methods that make collections easier to use. The
following table lists the methods and their purpose –
SNo Method Name & Purpose
EXISTS(n)
1
Returns TRUE if the nth element in a collection exists; otherwise returns FALSE.
COUNT
2
Returns the number of elements that a collection currently contains.
LIMIT
3
Checks the maximum size of a collection.
FIRST
4
Returns the first (smallest) index numbers in a collection that uses the integer subscripts.
LAST
5
Returns the last (largest) index numbers in a collection that uses the integer subscripts.
PRIOR(n)
6
Returns the index number that precedes index n in a collection.
NEXT(n)
7
Returns the index number that succeeds index n.
EXTEND
8
Appends one null element to a collection.
EXTEND(n)
9
Appends n null elements to a collection.
EXTEND(n,i)
10
Appends n copies of the ith element to a collection.
TRIM
11
Removes one element from the end of a collection.
 TRIM(n)
12
Removes n elements from the end of a collection.
DELETE
13
Removes all elements from a collection, setting COUNT to 0.
DELETE(n)
Removes the nth element from an associative array with a numeric key or a nested table.
14
If the associative array has a string key, the element corresponding to the key value is
deleted. If n is null, DELETE(n) does nothing.
DELETE(m,n)
15 Removes all elements in the range m..n from an associative array or nested table. If m is
larger than n or if m or n is null, DELETE(m,n) does nothing.

Q5. What are Collection Types in details?

There are 3 Types of Collections:

a) VARIABLE SIZE ARRAY
It can store a fixed size sequential collection of elements of the same type.
Syntax:
TYPE name IS VARRAY(n) OF datatype1
Where:
Datatype1: Element Datatype

Example:
DECLARE
type namesarray IS VARRAY(5) OF VARCHAR2(10);
type grades IS VARRAY(5) OF INTEGER;
names namesarray;
marks grades;
total integer;
BEGIN
names := namesarray('Kavita', 'Pritam', 'Ayan', 'Rishav', 'Aziz');
marks:= grades(98, 97, 78, 87, 92);
total := names.count;
dbms_output.put_line('Total '|| total || ' Students');
FOR i in 1 .. total LOOP
dbms_output.put_line('Student: ' || names(i) || '
Marks: ' || marks(i));
END LOOP;
END;
/
b) NESTED TABLE
A nested table is like a one-dimensional array with an arbitrary number of elements. But it differs
from an Array:-
- An array has a declared number of elements, but a nested table doesn’t. It can increase
dynamically.
- An array is always dense i.e. it always has consecutive subscripts. A nested array is
dense initially dense initially but can become sparse when elements are deleted from it.
Syntax:
TYPE name IS TABLE OF datatype1;
Where:
Datatype1: Element Datatype

Example:
DECLARE
TYPE names_table IS TABLE OF VARCHAR2(10);
TYPE grades IS TABLE OF INTEGER;
names names_table;
marks grades;
total integer;
BEGIN
names := names_table('Kavita', 'Pritam', 'Ayan', 'Rishav', 'Aziz');
marks:= grades(98, 97, 78, 87, 92);
total := names.count;
dbms_output.put_line('Total '|| total || ' Students');
FOR i IN 1 .. total LOOP
dbms_output.put_line('Student:'||names(i)||', Marks:' || marks(i));
end loop;
END;

c) INDEX BY TABLE ( ASSOCIATIVE ARRAY)

It 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 string.
Syntax:
TYPE name IS TABLE OF datatype1 INDEX by datatype2
Where:
Datatype1: Element Datatype
Datatype2: Index Datatype

Example:
DECLARE
TYPE salary IS TABLE OF NUMBER INDEX BY VARCHAR2(20);
salary_list salary;
name VARCHAR2(20);
BEGIN
-- adding elements to the table
salary_list('Rajnish') := 62000;
salary_list('Minakshi') := 75000;
salary_list('Martin') := 100000;
salary_list('James') := 78000;

-- printing the table

name := salary_list.FIRST;
WHILE name IS NOT null LOOP
dbms_output.put_line
('Salary of ' || name || ' is ' || TO_CHAR(salary_list(name)));
name := salary_list.NEXT(name);
END LOOP;
END;