PL/SQL Cursors

What is a PL/SQL cursor?

A PL/SQL cursor is a pointer to a result set, or the data that results from a query. Cursors let you fetch one or more rows from the database into memory, process them, and then either commit or roll back those changes.

How to use a PL/SQL cursor?

In order to use a cursor, you first need to declare it. This is done using the DECLARE statement. Once declared, you can then open the cursor to populate it with data from your query.

Finally, you can fetch rows from the cursor one at a time or in bulk. When you’re finished with the cursor, you need to close and deallocate it.

Benefits of using Oracle PL/SQL cursors

There are several benefits to using cursors, including:

– improved performance for large data sets;
– the ability to process rows individually or in groups;
– better control over commits and rollbacks; and
– the ability to reuse SQL statements.

Types of cursors

There are two types of cursors: implicit and explicit.

Implicit cursors are generated automatically by Oracle database when an SQL statement occurs in the PL/SQL executable part;

Explicit cursors are declared and defined by the user when an query which appears in a PL/SQL return multiple lines as a result.

Both implicit and explicit cursors have attributes. The attributes are:

%ROWCOUNT is an integer data type and represents the number of lines the cursor loaded.
%FOUND is an boolean and has the value TRUE if the last load operation FETCH from a cursor successful (for explicit cursors) or SQL statement returned at least one line (for implicit cursors).
%NOTFOUND is an boolean and has the opposite meaning of the attribute %FOUND.
%ISOPEN for implicit cursors this attribute is always FALSE because a implicit cursor is closed immediately after execution.

Implicit cursors

DECLARE 
   updated_rows number(2);
BEGIN
   UPDATE I_EMPLOYEES SET salary = salary + 1000 WHERE EMPLOYEE_ID=3;
   IF SQL%NOTFOUND THEN
      dbms_output.put_line('No employee found');
   ELSIF SQL%FOUND THEN
      updated_rows := SQL%ROWCOUNT;
      dbms_output.put_line('Updated rows = ' || updated_rows);
   END IF; 
END;

Explicit cursors

DECLARE
  CURSOR c_emp IS SELECT * FROM i_employees;
  temp I_EMPLOYEES%rowtype;
BEGIN
  OPEN c_emp;
  LOOP
    FETCH c_emp into temp;
    dbms_output.put_line(temp.employee_id||' '||temp.first_name||' '||temp.last_name);      
    EXIT WHEN c_emp%NOTFOUND;
  END LOOP;
  CLOSE c_emp;
END;

Looping in a cursor

One of the most common uses for cursors is to loop through a result set, one row at a time. This is done using a cursor FOR loop. Within the loop, you can perform any necessary processing for each row, such as retrieving data or updating records. When the loop is finished, the cursor is automatically closed.

Overall, cursors provide a powerful way to work with data in Oracle PL/SQL. By understanding how to declare, open, fetch from, and close cursors, you can leverage their strengths in your own applications.