CURSOR in PLSQL | Best Explanation

A cursor in SQL and PL/SQL is a database object used to retrieve, process, and manipulate query results row by row. Unlike regular SQL queries, which return entire result sets at once, a cursor fetches one row at a time, allowing you to handle individual rows in a controlled manner.

Basics -

Cursor in PLSQL

Pranav Bakare ・ Sep 28

#sql #oracle #database #fullstack

%TYPE and %ROWTYPE Attributes in PLSQL

Pranav Bakare ・ Sep 29

#sql #fullstack #backend #oracle

Normal and reff cursor -

Cursor Types in PLSQL -Normal cursor and Reference cursor

Pranav Bakare ・ Sep 29

#sql #devops #fullstack #backend

Types of Cursors

Implicit Cursors: Automatically created by Oracle for SELECT INTO statements or DML operations (INSERT, UPDATE, DELETE). These are simple and hidden from the user.
Automatically managed by Oracle.

Example:

BEGIN
   SELECT employee_name INTO v_name FROM employees WHERE employee_id = 101;
END;

Explicit Cursors: Manually defined by the developer for queries that return multiple rows. You explicitly open, fetch, and close the cursor.
Defined and controlled by the user.

Example (basic steps):

DECLARE
   CURSOR emp_cursor IS SELECT * FROM employees;
BEGIN
   OPEN emp_cursor;
   FETCH emp_cursor INTO ... ;
   CLOSE emp_cursor;
END;

Let's create a simple table and insert sample data to demonstrate the cursor example.

1. Create the employees Table

The table will have fields like employee_id, employee_name, and department_id.

CREATE TABLE employees (
    employee_id    NUMBER PRIMARY KEY,
    employee_name  VARCHAR2(100),
    department_id  NUMBER
);

2. Insert Sample Data into the employees Table

We will insert a few rows with different department_id values to later filter and fetch data using the cursor.

INSERT INTO employees (employee_id, employee_name, department_id) 
VALUES (1, 'John Doe', 10);
INSERT INTO employees (employee_id, employee_name, department_id) 
VALUES (2, 'Jane Smith', 20);
INSERT INTO employees (employee_id, employee_name, department_id) 
VALUES (3, 'Robert Brown', 10);
INSERT INTO employees (employee_id, employee_name, department_id) 
VALUES (4, 'Emily Davis', 30);
INSERT INTO employees (employee_id, employee_name, department_id) 
VALUES (5, 'Michael Johnson', 10);

3. Cursor Example to Fetch Employees from Department 10

Now that the employees table and data are ready, let's run the cursor example that fetches and displays data for employees from department 10.

DECLARE
   -- Declare variables to hold employee data
   v_employee_id employees.employee_id%TYPE;
   v_employee_name employees.employee_name%TYPE;

   -- Declare a cursor to select employee ID and name from department 10
   CURSOR emp_cursor IS
      SELECT employee_id, employee_name 
      FROM employees 
      WHERE department_id = 10;
BEGIN
   -- Open the cursor
   OPEN emp_cursor;

   -- Loop through each row fetched by the cursor
   LOOP
      -- Fetch a row into the variables
      FETCH emp_cursor INTO v_employee_id, v_employee_name;

      -- Exit the loop if no more rows are found
      EXIT WHEN emp_cursor%NOTFOUND;

      -- Output the fetched employee data
      DBMS_OUTPUT.PUT_LINE('Employee ID: ' || v_employee_id || ', Name: ' || v_employee_name);
   END LOOP;

   -- Close the cursor
   CLOSE emp_cursor;
END;
/

Output:

When this block of PL/SQL is executed, it will output the details of employees who belong to department_id = 10:

Employee ID: 1, Name: John Doe
Employee ID: 3, Name: Robert Brown
Employee ID: 5, Name: Michael Johnson

Explanation of Example:

Cursor Declaration: The cursor emp_cursor selects employee_id and employee_name for employees in department_id = 10.

Cursor Open: OPEN emp_cursor; prepares the result set.

Fetch Loop: The loop fetches each row one at a time into the variables v_employee_id and v_employee_name, and prints them using DBMS_OUTPUT.PUT_LINE.

Cursor Close: The cursor is closed after processing all rows.

This example demonstrates how to use a cursor to retrieve and process data from a table row by row.