Explicit Cursor in Oracle

Explicit cursor in Oracle- Learn about different types of cursors and their usage in oracle. and also learn how to create and use the cursors in different Scenarios.

PLSQL Cursors

A PL/SQL cursor is like a pointer in C.
A pointer in ‘C’ holds the address of the variable and process the value at a time, based on requirement.
A PL/SQL cursor can hold more than one row, but can process only one row at a time.

A pointer occupies a temporary space for storage which is called as heap space.

In the same way Cursor also occupies a temporary work area which is called as Context area ,created in the system memory when a SQL statement is executed.

This temporary work area is used to store and execute the data (SQL statements)retreived from the database.

The cursor is a pointer variable , that holds the sql select statement and the rows of data accessed by it.

By using cursor variables information can be manipulate by assigning a name to “select statement”.

PLSQL Cursors are 2 types

Implicit Cursors -for implicit cursors please follow the given link.
Explicit Cursors.

This are explicitly defined by the programmer
This cursors is declared with in the pl/sql block .
Cursor allows sequential process of each row of the returned data from database.

PL/SQL Working Approach

PL-SQL cursors works with 4 stages.

DECLARE CURSOR
OPEN CURSOR
FETCH CURSOR
CLOSE CURSOR

1. DECLARE CURSOR

Cursor is declared in declarative block by providing a select statement.

Syntax:

CURSOR [cursor_name] is [select_statement]

Cursor name can be any valid identifier.
Any select statements are legal, including joins and statements with the set operators.
In select statement using of INTO clause is no use.

PL/SQL CURSOR Declaration

CURSOR cursor_name [ ( [ parameter_1 , parameter_2 ...] ) ]
	[ RETURN return_specification ]
	IS sql_select_statements
	[FOR UPDATE [OF [column_list]];

The parameter1,parameter2… are optional elements in the cursor declaration.
These parameters allow you to pass arguments into the cursor.
The RETURN return_specification is also an optional part.
sql_select_statements- Specify a valid SQL statement that returns a result set where the cursor points to.
[FOR UPDATE [OF [column_list]]- indicate a list of columns that you want to update after the FOR UPDATE OF. This part is optional so you can omit it in the CURSOR declaration.

Example of Declaring a Cursor

CURSOR Cur_ndp
    IS
      SELECT name,
        dept_name
      FROM employee_tbl1 e
      INNER JOIN department_tbl1 d
      ON d.id = e.departmentid;

2. OPEN CURSOR

Opening of a cursor executes the query and retrieves the information from the database and stores it into context area.

Cursors can be opened only in the Execution Section or Exception Section of the PL-SQL block.
Once the cursor is opened , it cannot be reopened until it is closed.
When you OPEN the cursor, PL/SQL executes the SQL SELECT statement and identifies the active result set.
Notice that OPEN action does not actually retrieve records from the database. It happens in the FETCH step
If the cursor was declared with the FOR UPDATE clause, PL/SQL locks all the records in the result set.

Syntax

OPEN Cursor_name[(argument_1,argument_2)];

We can open the cur_ndp Cursor as follows.

Example of opening a cursor.

OPEN  Cur_ndp;

3. FETCH CURSOR-

Fetch the record from Context Area into cursor variable.

cursor fetch one row at a time from Context Area from the set of records.

Synax

FETCH Cursor_Name into [record or list_of_variable]

The INTO clause for query is part of the Fetch statement.

Notes

Once the cursor is open, you can fetch data from the cursor into a record that has the same structure as the cursor.
Instead of fetching data into a record, you can also fetch data from the cursor to a list of variables.
The fetch action retrieves data and fills into the record or the variable list
You can manipulate this data in memory.
You can fetch the data until there is no record found in active result set.

Example of Fetching the data from a Cursor

LOOP
         --fetch information from cursor into record
FETCH Cur_ndp INTO r_ndp;

EXIT WHEN Cur_ndp%NOTFOUND;

-- print department – chief

DBMS_OUTPUT.PUT_LINE(r_ndp .depT_name || ' - ' ||r_ndp.name );
END LOOP;

4. CLOSE CURSOR

This tells the PL/SQL engine that the program is finished with the cursor and resources associated with it can be freed.

These resources include the temporary storage space that is used for determining the active set.

The active set can be reestablished several times.

Notes

Always close the cursor when it is no longer used. Otherwise, you will have a memory leak in your program, which is not expected.

Syntax

CLOSE Cursor_Name;

Example of closing a Cursor

CLOSE  Cur_ndp;

A complete PL/SQL Cursor Example .

Example to print the Name and Department name of the employee

for below example , for tables reference please click on the following link as employee_tbl1 , department_tbl1

DECLARE
  -- declare a cursor
    CURSOR Cur_ndp
    IS
      SELECT name,
        dept_name
      FROM employee_tbl1 e
      INNER JOIN department_tbl1 d
      ON d.id = e.departmentid;
    r_ndp Cur_ndp%ROWTYPE;
  BEGIN
    OPEN Cur_ndp;
    LOOP
      -- fetch information from cursor into record
      FETCH Cur_ndp INTO r_ndp;
      EXIT
    WHEN Cur_ndp%NOTFOUND;
      -- print department name – name of the employee
      DBMS_OUTPUT.PUT_LINE(r_ndp.dept_name ||
       ' - ' || r_ndp.name);
    END LOOP;
    -- close cursor  Cur_ndp
    CLOSE Cur_ndp;
  END;

Output

IT – Tom
HR – Pam
IT – John
Payroll – Sam
Payroll – Todd
IT – Ben
HR – Sara
IT – Valarie

Explicit Cursor in Oracle

PLSQL Cursors