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.
Let us begin with 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 −
If one or more records were fetched successfully with commands such as INSERT, UPDATE, DELETE etc., then it returns TRUE. Otherwise it returns FALSE.
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.
This returns the number of rows that are affected by different commands such as INSERT, UPDATE, DELETE etc.
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.
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 −
The cursor is declared as follows. Here, the cursor is c_student −
CURSOR c_student IS Select Stu_ID,Stu_Name from Student;
The cursor is opened as follows −
OPEN c_student;
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;
The allocated memory is released when the cursor is closed. This is done as follows −
CLOSE c_student;