CURSOR

What is cursor ? explain with an example

[1] A cursor is a temporary work area created in the system memory when a SQL statement is executed.
[2] 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.

There are two types of cursors in PL/SQL:
[1]Implicit cursors:
[2]Explicit cursors:

[1]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.

[2]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,

For Example: Consider the PL/SQL Block that uses implicit cursor attributes as shown below:

DECLARE var_rows number(5);

BEGIN

UPDATE employee

SET salary = salary + 1000;

IF SQL%NOTFOUND THEN

dbms_output.put_line('None of the salaries where updated');

ELSIF SQL%FOUND THEN

var_rows := SQL%ROWCOUNT;

dbms_output.put_line('Salaries for ' || var_rows || 'employees are updated');

END IF;

END;

In the above PL/SQL Block, the salaries of all the employees in the ‘employee’ table are updated. If none of the employee’s salary are updated we get a message 'None of the salaries where updated'. Else we get a message like for example, 'Salaries for 1000 employees are updated' if there are 1000 rows in ‘employee’ table.