What do you know by PL/SQL Cursors?

by

Oracle uses workspaces to execute the SQL commands. When Oracle processes a SQL command, it opens an area in the memory called Private SQL Area. This area is identified by the cursor. It allows programmers to name this area and access it?s information.

In PL/SQL, a cursor is a named private SQL area where you can store intermediate results and process them sequentially, one at a time. Cursors are used to retrieve and process rows returned by a query.

There are two types of cursors in PL/SQL:

  1. Implicit Cursors:
    • These cursors are created by default when DML statements like SELECT, INSERT, UPDATE, or DELETE are executed.
    • They are automatically created by the PL/SQL engine to process the SQL statements.
    • You don’t have to explicitly declare or manage implicit cursors.

    Example of an implicit cursor (SELECT statement):

    DECLARE
    employee_name employees.employee_name%TYPE;
    BEGIN
    SELECT employee_name INTO employee_name FROM employees WHERE employee_id = 1;
    -- Process the retrieved data
    END;
  2. Explicit Cursors:
    • These cursors are explicitly declared, opened, fetched, and closed by the programmer.
    • They provide more control over the processing of query results.
    • Explicit cursors are useful when you need to process multiple rows returned by a query.

    Example of an explicit cursor:

    DECLARE
    CURSOR employee_cursor IS
    SELECT employee_id, employee_name FROM employees;
    emp_rec employee_cursor%ROWTYPE;
    BEGIN
    OPEN employee_cursor;
    LOOP
    FETCH employee_cursor INTO emp_rec;
    EXIT WHEN employee_cursor%NOTFOUND;
    -- Process the retrieved data
    END LOOP;
    CLOSE employee_cursor;
    END;

In the example above, employee_cursor is an explicit cursor that is declared, opened, fetched in a loop, and closed. The fetched data is stored in the emp_rec record variable, and you can perform operations on this data within the loop.