PL/SQL Cursors: Explanation and Example

In PL/SQL, cursors are used to process individual rows returned from a query. They allow for more granular control over result sets and enable developers to manipulate data row by row.

To illustrate the concept of cursors, let's consider a scenario where we need to retrieve employee information from a database table and perform some calculations on each employee's salary.

DECLARE
   CURSOR emp_cursor IS
      SELECT employee_id, employee_name, salary
      FROM employees;
   emp_rec emp_cursor%ROWTYPE;
BEGIN
   OPEN emp_cursor;
   LOOP
      FETCH emp_cursor INTO emp_rec;
      EXIT WHEN emp_cursor%NOTFOUND;
      -- Perform calculations or operations on emp_rec data
   END LOOP;
   CLOSE emp_cursor;
END;

In the example above, we define a cursor emp_cursor to select employee details from the employees table. We then open the cursor, fetch each row into the emp_rec record variable, and process the data as needed before closing the cursor.

By using cursors in PL/SQL, developers can iterate over result sets and apply business logic to individual rows, making them a versatile tool for database programming.