What is cursor in oracle

In this post, we will learn about what is cursor in oracle, the types of cursors, how to fetch the row, and how to close the cursor. I hope you will like this

What is a cursor in oracle?

Oracle Uses a work area called Private SQL areas to execute SQL Statements and store information. An Oracle Cursor is a PL/SQL construct that allows you to name these work areas, and to access their stored information

Types of Cursors in Oracle

• Implicit Cursor in Oracle
• Explicit Cursor in Oracle

Explicit Cursors

Explicit cursors have the following stages

1) Declare: We declare the name of the cursor and define the structure of the query

2) Open:  The open statement executes the query and binds any variable that is referenced. Rows identified by the query are called Active sets. And now that is available for fetching

What is Active Set:  The Set of rows returned by a multiple-row query

Its size is the number of rows that meet your search criteria

3) Fetch: In this stage, rows are fetched from the cursor, and after each fetch, you test the cursor for any remaining rows, if no rows, you proceed to close the cursor

4) Close: The close statement releases the active set of rows and we can again open the cursor to fetch refreshed active set

Stages in details

Declaring a Cursor

• Cursor Name
• Structure of the Query

Syntax:
 CURSOR  IS   :  It includes most of the usual clauses, but INTO Clause is not allowed
Example:
 DECLARE
 CURSOR cur1 is SELECT address,salary,emp_no,emp_name FROM emp WHERE region=’US’;
 ………………………………
 BEGIN
 ………………………………
 END;

Opening a Cursor

Here Parsing and Query execution is done. After Opening the Cursor the rows returned by the query are available for fetching.

Syntax:
 Open <cursor name>
 This statement is used within the executable section of the block.
 It also establishes an active set of the rows
Example:
  OPEN cur1;
 DECLARE
 v_salary number;
 CURSOR cur1 is SELECT salary FROM emp WHERE region=’US’;
 BEGIN
 OPEN cur1;
 LOOP
 FETCH cur1 into v_salary;
 EXIT WHEN cur1%NOTFOUND;
 DBMS_OUTPUT.PUT_LINE (v_salary);
 END LOOP;
 CLOSE cur1;
 END;
 /

The cursor will now point to the first row in the active set.

Fetching the rows

After the cursor is opened the current row is loaded into variables. The current row is the row at which the cursor is currently pointing The retrieval of data into PL/SQL variable or host

The variable is done through the FETCH statement

Syntax: 
 FETCH  INTO ;

• For each column value returned by the query associated with the cursor, there must be a

corresponding variable in the INTO list.

• Also, their Datatypes must be compatible

CLOSING A CURSOR

It explicitly closes the cursor, allowing it to be Re-opened if required.

Syntax:
 CLOSE <cursor-name>;
Example: 
 CLOSE cur1
Example of Using CLOSE <Cursor Name>
 DECLARE
 v_address emp.address%TYPE;
 CURSOR cur1 is select address from emp;
 BEGIN
 OPEN cur1;
 LOOP
 FETCH cur1 into v_address;
 EXIT WHEN cur1%NOTFOUND;
 DBMS_OUTPUT.PUT_LINE(v_address);
 END LOOP;
 CLOSE cur1;
 END;
 /

Explicit Cursor Attributes

Attribute	Type	Description
%ISOPEN	Boolean	Evaluates to TRUE if the cursor is open
%NOTFOUND	Boolean	Evaluates to TRUE if the most recent fetch does not return a row
%FOUND	Boolean	Evaluates to TRUE if the most recent fetch returns a row
%ROWCOUNT	NUMBER	Evaluates to the total number of rows returned so far

Advanced concept with Oracle cursor

Oracle Cursor and Records

1)We have already read about PLSQL records.

2)We can process the rows of the active set by fetching values into a PL/SQL Record also

3) We can also define a plsql record based on the selected list of the columns in the explicit cursors also

Example

DECLARE 
 CURSOR dept_cursor is Select deptno,dept_name  FROM   dept;
 dept_record dept_cursor%rowtype;
 BEGIN
 OPEN dept_cursor;
 LOOP
 FETCH dept_cursor INTO dept_record;
 Insert into dept_temp (deptno, deptname) values (dept_record.deptno, dept_record.dept_name);
 EXIT WHEN dept_cursor%notfound;
 END LOOP;
 Commit;
 CLOSE dept_cursor;
 END;
 /

Cursor with Parameters/parameterized cursor in oracle

Syntax: 
 CURSOR cursor_name(parameter_name datatype,  )
 IS
 Select_statement;

1)Pass parameter values to a cursor when the cursor is opened and the query is executed

2) Open an explicit cursor several times with different active sets each time

Open cursor_name(parameter_value , ……);

Example

DECLARE 
 CURSOR cur1(v_deptno number) is
 Select deptname,dept_loc,dept_pincode from dept where deptno=v_deptno;
 Dept_record      cur1%rowtype;
 l_deptno  number:=111;
 BEGIN
 OPEN  cur1(112);
 LOOP
 FETCH cur1 into dept_record;
 IF cur1%FOUND THEN
 Dbms_output.put_line(dept_record.deptname);
 ELSE
 Exit;
 END IF;
 END LOOP;
 CLOSE cur1;
 OPEN cur1(l_deptno);
 LOOP
 FETCH cur1 into dept_record;
 IF cur1%FOUND THEN
 Dbms_output.put_line(dept_record.deptname);
 ELSE
 Exit;
 END IF;
 END LOOP;
 CLOSE cur1;
 END;
 /

 Related Articles

date functions in Oracle sql
Oracle PLSQL Tables
Oracle PLSQL records: Check out this article on the working of oracle plsql records.Also, find out the various ways to define it and assign value to it
oracle plsql interview questions :Check out this post for 25 Oracle PlSQL interview questions with detailed explanation and answer for the success in interview
Oracle PLSQL Block : This page contains good description on Oracle PLSQL Block Structure and types with examples and explanation
Cursor attributes