Cursors in Oracle DBMS

When a SQL statement is executed in Oracle, the temporary context area is created. This area contains all the relevant information relating to the statement and its execution. The cursor is a pointer to this context area and allows the PL/SQL program to control this area.

There are two types of Cursors.

Implicit Cursors
Explicit Cursors

Let us begin with Implicit Cursors −

Implicit Cursors

Whenever an SQL statement is executed, the implicit cursors are automatically created. This happens if there is no explicit cursor for the particular statement. Implicit cursors cannot be controlled by the programmers.

There are many different attributes for Implicit Cursors. Some of them are −

%FOUND

If one or more records were fetched successfully with commands such as INSERT, UPDATE, DELETE etc., then it returns TRUE. Otherwise it returns FALSE.

%NOTFOUND

This is the direct opposite of %FOUND. If one or more records were fetched successfully with commands such as INSERT, UPDATE, DELETE etc., then it returns FALSE. Otherwise it returns TRUE.

%ROWCOUNT

This returns the number of rows that are affected by different commands such as INSERT, UPDATE, DELETE etc.

%ISOPEN

This returns TRUE if the cursor is open and false otherwise. However, for implicit cursors, the value is always FALSE because the cursor is closed immediately after executing its instruction.

Explicit Cursors

While implicit cursors were automatically created, explicit cursors are specifically created by the programmers. There definition is provided in the declaration section of the PL/SQL block.

Creating an explicit cursor has the following steps −

Declare Cursor

The cursor is declared as follows. Here, the cursor is c_student −

CURSOR c_student IS
Select Stu_ID,Stu_Name from Student;

Open Cursor

The cursor is opened as follows −

OPEN c_student;

Fetch Cursor

One row at a time is accessed while fetching the cursor. Fetching the cursor is done as follows −

FETCH c_student INTO
c_stuID, c_stuName;

Close Cursor

The allocated memory is released when the cursor is closed. This is done as follows −

CLOSE c_student;

Amit Diwan

Published on 03-Aug-2018 08:49:10

Previous Page Print Page