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.
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;
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.