CURSOR In Oracle
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 |
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.