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.
Syntax: To fetch rows from a cursor.
FETCH cursor_name INTO variables;
Syntax: To close cursor.
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;
Function created. 0.1 seconds
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;
Procedure created. 0.16 seconds
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.