Cursor in Oracle

Cursors in Oracle:
A cursor is used to process individual rows returned by database system for a query.
In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the set of data returned by the SQL statement.
In PLSQL a cursor is assigned to a specific SQL area for a specific SQL statement. For example if we create a cursor for a simple select statement then the server allocate a specific area for that statement. So the result of the statement is stored in that particular area.

Types of Cursors:

There are two types of cursors:

• Explicit cursor

• Implicit cursor

Explicit cursor:

Explicit cursor is like a select statement, it stores the result set of the select statement.
This type of the cursor is declared in the declaration section and used in the execution section.
There are three commands to process the cursor:
•OPEN
•FETCH
•CLOSE

Declaring a cursor:
A cursor must be declared in the declaration section, before it is used in the execution section.

Syntax:
Cursor << Cursor Name >> ( Declaration ) is SELECT column1 , column2, …. Column N  FROM <<Table Name>>;

In this above syntax “Cursor”  is a keyword, “ Cursor Name “ specifies the name of the cursor to be created. “Declaration” is where we can declare input variables to the cursors. “IS ” is a keyword , In the select statement we can select a set of rows from a table.

Examples for creating cursors:

DECLARE
CURSOR EMP_cursor
IS
SELECT * FROM EMP;
CURSOR EMP_CURSOR1 (no IN NUMBER)
IS
SELECT FIRST_NAME FROM EMP WHERE EMPLOYEE_ID=no;
CURSOR EMP_cursor2 (no IN NUMBER )
RETURN EMP%ROWTYPE
IS
SELECT * FROM EMP;
BEGIN
dbms_output.put_line( ‘Cursor Created’ ) ;
END;
/

In the above code we can see that the first declaration of cursor is selecting all the rows from the employee table i.e. EMP table without an input parameter into its area.
The second example shows how we can create a cursor with an input parameter with name “EMP_CURSOR1” and accepts only one input variable as number type.  If we see the select statement, then we can see that we are selecting the “FIRST_NAME” column from EMP table for the employee ID given by the user.
No w in the third declared cursor named “EMP_CURSOR2” with a return option. Here in this cursor we have an input parameter of number type and it returns multiple values.

In the above mentioned code we can see the three different types of creating a cursor.

How to open a cursor from an explicit cursor?
For opening a cursor we should be opening it in the execution/executable section and for this we need to use “OPEN” command, because we can only fetch data only after opening it.

Syntax for opening a cursor:
OPEN cursor_name [{argument {,argument….}}] ;

In the above syntax OPEN is a key word and cursor_name is the name of the cursor which we have declared in the declaration part and arguments are the section where we can use input parameters or else we can create a cursor without an argument as shown  earlier . Arguments are optional.

OPEN process carries few operations like first it determines an execution plan of the cursor then it associates variables and parameters in the SQL statements of the cursor then it determines the result set for the SQL statement and finally it sets the current row pointer to the first row in the result set.

How to fetch the data from an explicit cursor?
A fetch statement is used to fetch the data from the cursor to a variable. To retrieve all the records from the result set every row must fetch to the variable.

Syntax for fetching data from cursor:
FETCH cursor_name into record or variable_list ;

Here Fetch and INTO are the key words, cursor_name is the cursor name that we have declared in the declaration section while creating a cursor. Record or variable list specifies the local variables where we want to store the data from the cursor.

How to close an explicit cursor?
In the cursor body we can do any operation, once the operation is finished the cursor must be closed using the CLOSE command.

Syntax:
CLOSE cursor_name;

Here cursor name is the name of the cursor and CLOSE is the key word to close the open cursor. The main reason for closing the cursor is to release the SQL statement from the private area used by that cursor.

Example of an explicit cursor:

DECLARE
new_sal NUMBER (10);
rec_val emp%ROWTYPE;

CURSOR c1
IS
SELECT *
FROM emp
WHERE employee_id = 103;
BEGIN
OPEN c1;

FETCH c1
INTO rec_val;

new_sal := rec_val.salary * 1000;
DBMS_OUTPUT.put_line (‘Year New Salary : ‘ || new_sal);

CLOSE c1;
END;
/

Here in this above example in the declaration section we have declared a variable called “new_sal” as number type and the cursor named “C1” to select a row from the EMP table where the employee ID is 103 and finally “REC_VAL” variable is created as row type of the EMP table which will hold the values for that row.  In the executable section the cursor is opened and the value of the cursor is fetched into the variable “REC_VAL”, then the salary column is manipulated and finally the cursor is closed.

Implicit Cursor:

These types of cursors are not declared like the explicit cursors. When a SQL statement is directly used in the executable section of the PLSQL block , then this works like an implicit cursor.
There type of cursors are implicitly created when ever INSERT, UPDATE, DELETE and SELECT INTO statement s are executed in any program.
Here in this type of cursors we don’t have to declare, open, fetch and close the cursor. This supports all explicit cursor attributes. We can refer implicit cursor attribute through SQL cursor but where in an explicit cursor we need to use the cursor name and its attributes.

At the time when we execute an SELECT statement and which does not return any row then “PLSQL” immediately raise the “NO_DATA_FOUND” exception.  And when the SELECT statement returns more than one row then the “PLSQL” raises the “TOO_MANY_ROWS” exception.

Example of a program where implicit cursor is used:

First of all let me create a sample table called “ASSOCIATE” and insert one record.

Create table:

CREATE TABLE ASSOCIATE
(
user_id NUMBER PRIMARY KEY,
first_name VARCHAR2(80),
last_name VARCHAR2(50),
email VARCHAR2(100),
salary NUMBER,
hire_date DATE,
creation_date DATE
);

Insert a record:

INSERT INTO ASSOCIATE
VALUES (1, ‘SLOBA’, ‘RAY’, ‘slobaexpert@gmail.com’, 5000, ’26-FEB-2006′,
’26-FEB-2006′);

COMMIT ;

Now the implicit cursor example:

BEGIN
UPDATE ASSOCIATE
SET salary = 7000
WHERE user_id = 2;

IF SQL%NOTFOUND
THEN
DBMS_OUTPUT.put_line (‘No row found for update Associate Table’);
END IF;
END;

Here in this above code we are using an update statement inside the executable section of a PLSQL block, so an implicit cursor is created automatically. In this we have user the attribute “NOTFOUND” to check if the cursor is created or not. We have used this implicit cursor through the SQL.

Cursor variable:
Cursor variable is the data structure which points to the cursor’s result set, we usually use the cursor variable to retrieve rows from the result set.

Syntax:
TYPE ref_cursor_name IS REF CURSOR
[RETURN record_type];

For using cursor variable we should first create a REF_CURSOR type. The above syntax shows how to create a new ref cursor type, we need to create a ref cursor declaration with a return clause, which defines the query must rerun rows at runtime.
REF Cursors are usually used to handle multiple row result set that are obtained from a query in PLSQL program. Implicit cursor is used by oracle to handle these multiple row queries.
An unnamed memory space is used to store the data that is used by an implicit cursor.

So we can use the ref cursor multiple times in out execution block as like below example:

/* Formatted on 2012/06/11 14:53 (Formatter Plus v4.8.8) */
DECLARE
TYPE curvar_type IS REF CURSOR;

cur_variable curvar_type;
get_result VARCHAR2 (100);
BEGIN
DBMS_OUTPUT.put_line
(‘##############################################################’);
DBMS_OUTPUT.put_line
(‘Using the ref cursor for selecting Email from associate table’);
DBMS_OUTPUT.put_line
(‘**************************************************************’);

OPEN cur_variable FOR
SELECT email
FROM ASSOCIATE;

FETCH cur_variable
INTO get_result;

DBMS_OUTPUT.put_line (‘Ref Cursor points to Email as = ‘ || get_result);
DBMS_OUTPUT.put_line (‘ ‘);
DBMS_OUTPUT.put_line
(‘##############################################################’);
DBMS_OUTPUT.put_line
(‘Using the same ref cursor for selecting First Name from associate table’
);
DBMS_OUTPUT.put_line
(‘**************************************************************’);

OPEN cur_variable FOR
SELECT first_name
FROM ASSOCIATE;

FETCH cur_variable
INTO get_result;

DBMS_OUTPUT.put_line (‘Ref Cursor points to First name as = ‘ || get_result);
DBMS_OUTPUT.put_line
(‘**************************************************************’);
END;
Here in this above code you can see that I have used a type CURVAR_TYPE as ref cursor and used it on variable called cur_variable . In the code I used the same cursor to select the email from the table associate and then selected first name from the same table implicitly.

For more examples like to have a return type on a ref cursor refer Oracle documentation link i.e. : http://docs.oracle.com/

Explicit cursor attributes and loops:

Attributes: There are four types of attributes that are associated with explicit cursor.

They are:
•ISOPEN
•FOUND
•NOTFOUND
•ROWCOUNT

Syntax to use cursor attributes:

Cursor_name%attribute ;

Here cursor name specifies the name of the cursor; “%” symbol is used with the attributes.

ISOPEN: This attribute is used to check if the cursor is already open or not. This attribute will return the values TRUE is if the cursor is open else it will return FALSE.

FOUND: This attribute will return TRUE if a record is fetched from a table else return FALSE if the record is not fetched from the table.
If the cursor is not opened or it is already closed then values returned as INVALID_CURSOR.

NOTFOUND: This attribute returns TRUE when there is no record to fetch and returns FALSE when the records are fetched successfully. If the cursor is not opened or it is already closed then values returned as INVALID_CURSOR.

ROWCOUNT: This attribute is used to count the number of records fetched from the cursor. It also return INVALID_CURSOR when the cursor is not opened or already been closed.

Example:

DECLARE
CURSOR c1
IS
SELECT *
FROM emp;

rec emp%ROWTYPE;
lot INTEGER;
BEGIN
OPEN c1;

IF c1%ISOPEN
THEN
DBMS_OUTPUT.put_line (‘Cursor is opened’);

FETCH c1
INTO rec;

IF c1%FOUND
THEN
DBMS_OUTPUT.put_line (‘Cursor is having records … ‘);
END IF;

lot := c1%ROWCOUNT;
DBMS_OUTPUT.put_line (‘Cursor Row count : ‘ || lot);
END IF;
END;

OUTPUT:
Cursor is opened
Cursor is having records …
Cursor Row count : 1

In this above program we have created a cursor named “C1” by selecting rows from a table EMP. In the executable section we have used the cursor attributes to find the details for the cursor.
Here we have used the cursor attributes IFOPEN,FOUND and ROWCOUNT in our program.
And finally we have closed our cursor program. By executing the program it displayed the total number of rows.

Loops in Cursors: 

Simple loops:
Simple loops used in the cursors to determine if any rows are left out while fetching the rows. It also used to determine the status of the cursors, cursor attribute to be checked.

Example:

DECLARE
CURSOR c1
IS
SELECT first_name
FROM emp;

NAME VARCHAR2 (20);
BEGIN
OPEN c1;

LOOP
FETCH c1
INTO NAME;

EXIT WHEN c1%NOTFOUND;
DBMS_OUTPUT.put_line (NAME);
END LOOP;

CLOSE c1;
END;

OUTPUT:
Steven
Neena
Lex
Alexander
Bruce
David
Valli
Diana
Nancy
Daniel

Here in this program we have used a simple loop to fetch the values and we have also used the cursor attribute “%notfound” to check if the rows are fetched from the cursor or not.
When the EXIT condition becomes TRUE then the loop is terminated. Finally the values are inserted into a new table i.e. NAME and then the loop and the cursor are closed.

For Loop in Cursor:
Let us see how we can use a cursor for loop in a program by the below example:

DECLARE
CURSOR c1
IS
SELECT employee_id, first_name, salary
FROM emp;
BEGIN
FOR irec IN c1
LOOP
irec.salary := irec.salary + 10;
DBMS_OUTPUT.put_line (‘Calculation = ‘ || irec.salary);
DBMS_OUTPUT.put_line (irec.employee_id || ‘,’ || irec.first_name);
END LOOP;
END;

OUTPUT:
Calculation = 24010
100,Steven
Calculation = 17010
101,Neena
Calculation = 17010
102,Lex
Calculation = 9010
103,Alexander
Calculation = 6010
104,Bruce
Calculation = 4810
105,David

When we are using for loop there is no need to open and fetch a cursor. We can see the same in the above mentioned program. Here in this program for loop will go to all the record in the cursor and add a values 10 with all the salary and display these rows.

Lastly for having more information on Cursors in Oracle you can refer to doc.oracle.com