What are Cursors?
A cursor is a temporary work area created in the system memory when a SQL statement is executed. A cursor contains information on a select statement and the rows of data accessed by it.
This temporary work area is used to store the data retrieved from the database, and manipulate this data. A cursor can hold more than one row, but can process only one row at a time. The set of rows the cursor holds is called the active set.
There are two types of cursors in PL/SQL:
Implicit cursors
These are created by default when DML statements like, INSERT, UPDATE, and DELETE statements are executed. They are also created when a SELECT statement that returns just one row is executed.
Implicit Cursors: Application
When you execute DML statements like DELETE, INSERT, UPDATE and SELECT statements, implicit statements are created to process these statements.
Oracle provides few attributes called as implicit cursor attributes to check the status of DML operations. The cursor attributes available are %FOUND, %NOTFOUND, %ROWCOUNT, and %ISOPEN.
For example, When you execute INSERT, UPDATE, or DELETE statements the cursor attributes tell us whether any rows are affected and how many have been affected.
When a SELECT… INTO statement is executed in a PL/SQL Block, implicit cursor
attributes can be used to find out whether any row has been returned by the SELECT statement. PL/SQL returns an error when no data is selected.
The status of the cursor for each of these attributes are defined in the below table.
| Attributes | Return Value | Example |
| %FOUND | The return value is TRUE, if the DML statements like INSERT, DELETE and UPDATE affect at least one row and if SELECT ….INTO statement return at least one row.The return value is FALSE, if DML statements like INSERT, DELETE and UPDATE do not affect row and if SELECT….INTO statement do not return a row. | SQL%FOUND |
| %NOTFOUND | The return value is FALSE, if DML statements like INSERT, DELETE and UPDATE at least one row and if SELECT ….INTO statement return at least one row.The return value is TRUE, if a DML statement like INSERT, DELETE and UPDATE do not affect even one row and if SELECT ….INTO statement does not return a row. | SQL%NOTFOUND |
| %ROWCOUNT | Return the number of rows affected by the DML operations INSERT, DELETE, UPDATE, SELECT | SQL%ROWCOUNT |
Implicit Cursor Example
Following one emp_information table:
| EMP_NO | EMP_NAME | EMP_DEPT | EMP_SALARY |
| 1 | Sushil Kumar | Web Developer | 5000 |
| 2 | Manish Bhalerao | Program Developer | 8000 |
| 3 | Seema Raut | Program Developer | 4000 |
| 4 | Kunal Sharma | Web Developer | 6000 |
Now above employee information table update the employee name ‘Saulin’ department ‘Program Developer’ update to ‘Web Developer’.
Example
SQL>edit implicit_cursorBEGIN
UPDATE emp_information SET emp_dept='Web Developer'
WHERE emp_name='Seema Raut';
IF SQL%FOUND THEN
dbms_output.put_line('Updated - If Found');
END IF;
IF SQL%NOTFOUND THEN
dbms_output.put_line('NOT Updated - If NOT Found');
END IF;
IF SQL%ROWCOUNT>0 THEN
dbms_output.put_line(SQL%ROWCOUNT||' Rows Updated');
ELSE
dbms_output.put_line('NO Rows Updated Found');
END;/Result
SQL>@implicit_cursor
Updated - If Found
1 Rows Updated
PL/SQL procedure successfully operation.Explicit cursors
They must be created when you are executing a SELECT statement that returns more than one row. Even though the cursor stores multiple records, only one record can be processed at a time, which is called as current row. When you fetch a row the current row position moves to next row.
Both implicit and explicit cursors have the same functionality, but they differ in the way they are accessed.
The syntax for creating an explicit cursor is −
CURSOR cursor_name IS select_statement; Working with an explicit cursor includes the following steps −
- Declaring the cursor for initializing the memory
- Opening the cursor for allocating the memory
- Fetching the cursor for retrieving the data
- Closing the cursor to release the allocated memory
Declaring the Cursor
Declaring the cursor defines the cursor with a name and the associated SELECT statement. For example −
CURSOR cursor_name [ parameter ] RETURN return_type;
CURSOR cursor_name [ parameter ] [ RETURN return_type ]
IS SELECT STATEMENT;Opening the Cursor
Opening the cursor allocates the memory for the cursor and makes it ready for fetching the rows returned by the SQL statement into it. For example, we will open the above defined cursor as follows −
OPEN cursor_name [( cursor_parameter )];Fetching the Cursor
Fetching the cursor involves accessing one row at a time. For example, we will fetch rows from the above-opened cursor as follows −
FETCH cursor_name INTO variable;Closing the Cursor
Closing the cursor means releasing the allocated memory. For example, we will close the above-opened cursor as follows −
CLOSE cursor_name [( cursor_parameter )];Example
SQL>edit explicit_cursorDECLARE
cursor c is select * from emp_information
where emp_name='bhavana';
tmp emp_information%rowtype;BEGIN
OPEN c;
Loop exit when c%NOTFOUND;
FETCH c into tmp;
update emp_information set tmp.emp_dept='Web Developer'
where tmp.emp_name='Seema Raut';
END Loop;IF c%ROWCOUNT>0 THEN
dbms_output.put_line(SQL%ROWCOUNT||' Rows Updated');ELSE
dbms_output.put_line('NO Rows Updated Found');END IF;CLOSE c;
END;
/Result
SQL>@explicit_cursor
1 Rows Updated
PL/SQL procedure successfully completed.