Cursor in PL/SQL
- Oracle Engine uses a work area for its internal processing to execute SQL statements.
- This work area is private to SQL's operations and is called a cursor.
- The data stored in the cursor is called the active data set.
- Cursor contains information on a SELECT statement and the rows of data accessed by it.
- It can hold more than one row, but can process only one row at a time.
- Cursor is used to access the result-set present in the memory.
- This result set contains the records returned on execution of a query.
Attributes of Cursors
|%ISOPEN||Returns TRUE if cursor is open, else FALSE.|
|%FOUND||Returns TRUE if record was fetch successfully, else FALSE.|
|%NOTFOUND||Returns TRUE if records was not fetched successfully, else FALSE.|
|%ROWCOUNT||Returns number of records processed from the cursor.|
Types of CursorsThe two types of cursors are:
1. Implicit cursors
- Implicit cursors are automatically generated by the Oracle engine.
- If the Oracle Engine opens a cursor for its internal processing, it is known as Implicit cursor.
- Implicit cursors are created by default to process the statements when DML statements(INSERT, UPDATE, DELETE) are executed.
Update the information of employees using implicit cursor.
Consider the following table titled 'Employee'
Write a procedure to update the salary of employees using implicit cursor.
The result is shown in the following table.
SET salary = salary + 1000;
IF sql%notfound THEN
dbms_output.put_line('no Employee updated');
ELSIF sql%found THEN
total_rows := sql%rowcount;
dbms_output.put_line( total_rows | | ' Employee updated ');
2. Explicit Cursor
Cursor cursor_name IS select_statement;
Following are the steps to work with an explicit cursor:
- If a cursor is opened for processing data through a PL/SQL block as per requirement like user defined cursor, is known as an Explicit cursor.
- Explicit cursor is created while executing a SELECT statement that returns more than one row.
- These cursor should be defined in the declaration section of the PL/SQL block and created on a SELECT statement which returns more than one row.
CURSOR Name IS
This statement is used to access one row at a time.
FETCH cursor_name INTO variable_list;
Write a PL/SQL code to retrieve the employee name and designation using explicit cursor.
Consider the following table titled 'Employee'.
Write a Pl/SQL code to retrieve the employee name and designation using explicit cursor.
CURSOR c_employee is
SELECT id, name, designation FROM employee;
FETCH c_employee into c_id, c_name, c_addr;
EXIT WHEN c_employee%notfound;
dbms_output.put_line(c_id | | ' ' | | c_name | | ' ' | | c_addr);
1 Albert Programmer
2 Anna HR
3 Mark Analyst
4 Jason Content writer
5 Andrew Programmer