CURSOR in Oracle

by

ORACLE CURSOR
In Oracle, there is a private SQL area which is used to store info about the processing of a SELECT or DML statement. A SELECT or DML statement can be an INSERT, UPDATE, DELETE, MERGE etc. A cursor is a pointer to this private SQL area. It can be understood as a mechanism used to specify the name of a SELECT statement. It can further be used to modify the data within the SQL statement itself.

Syntax: To declare a cursor. 

CURSOR cursor_name  
IS  
SELECT columns  
FROM table_name  
WHERE conditions;

Syntax: To open a cursor. 

OPEN cursor_name;

Syntax: To fetch rows from a cursor. 

FETCH cursor_name INTO variables;

Syntax: To close cursor. 

CLOSE cursor_name;

Parameters:
columns: It is used to specify the columns of the table which needs to be selected.
table_name: It is used to specify the name of the tables from which the records need to be selected.
conditions: It is used to specify the conditions to be strictly followed by the rows to be included in the result set.
variable_list: It is used to specify the variables to be utilized to store the cursor result set.

Example 1: Using cursor in a function. 

CREATE OR REPLACE Function Search_Students  
( name IN varchar2 )  
RETURN number  
IS  
num number;  
CURSOR cur  
IS  
SELECT student_name  
FROM students  
WHERE student_name = name;  
BEGIN  
OPEN cur;  
FETCH cur INTO num;  
 if cur % notfound then  
num := 9999;  
end if;  
CLOSE cur;  
RETURN num;  
END;

Output: 

Function created.
0.1 seconds

Explanation:
Here we are using a cursor ‘cur’ within a function ‘Search_Students’. Within the function, we are declaring the cursor. After declaration, the cursor is opened to fetch the rows from the cursor. And at last, the cursor is closed, followed by the closing of the function declaration.

Example 2: Declaring a cursor within a cursor. 

CREATE OR REPLACE PROCEDURE CURSOR_IN_CURSOR is first_name varchar2(50);  
last_name varchar2(50);  
teacher_name varchar2(100);   
 
/* First cursor */  
CURSOR std_name IS  
SELECT DISTINCT std.first_name, std.last_name  
FROM all_info std  
WHERE std.first_name = 'IMPORTANT';    
 
/* Second cursor */ 
CURSOR teach_name IS  
SELECT DISTINCT tech.teacher_name  
FROM all_teachers tech  
WHERE tech.t_first_name = first_name  
AND col.t_last_name = last_name;  
BEGIN  
 
/* Open first cursor */ 
OPEN std_name;  
LOOP  
FETCH std_name INTO first_name, last_name;  
 
/* Open second cursor */ 
OPEN teach_name;  
LOOP  
FETCH teach_name INTO teacher_name;  
END LOOP;  
CLOSE teach_name;  
END LOOP;  
CLOSE std_name;  
EXCEPTION  
WHEN OTHERS THEN  
raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);  
END CURSOR_IN_CURSOR;

Output: 

Procedure created.
0.16 seconds

Explanation:
Here we are declaring a cursor within a cursor. This is a unique feature of the Oracle database. The first cursor here is ‘std_name’ and the second cursor is ‘teach_name’. The second cursor will be continuously opened and closed each time the first cursor is used to retrieve a new record. Thus the new variable values will be used by the second cursor from the first cursor.