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;
Published on 03-Aug-2018 12:19:10
