Cursors in PL/SQL


In PL/SQL, cursors are a mechanism for accessing and manipulating query result sets. A cursor allows you to fetch and process rows returned by a query one at a time. There are two main types of cursors in PL/SQL: implicit cursors and explicit cursors.

Implicit Cursors

PL/SQL automatically creates implicit cursors for SELECT INTO statements, and for INSERT, UPDATE, DELETE, and MERGE statements.

Example of Implicit Cursor
DECLARE
    v_employee_name employees.first_name%TYPE;
BEGIN
    -- This SELECT INTO statement uses an implicit cursor
    SELECT first_name
    INTO v_employee_name
    FROM employees
    WHERE employee_id = 100;

    DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_employee_name);
END;

In this example, PL/SQL creates an implicit cursor for the SELECT INTO statement and automatically fetches the result into the v_employee_name variable.

Explicit Cursors

Explicit cursors give you more control over the context area. You can open a cursor, fetch rows from the result set, and then close the cursor.
Steps to Use Explicit Cursors
  • Declare the cursor: Define the cursor with a SELECT statement.
  • Open the cursor: Execute the query and establish the result set.
  • Fetch from the cursor: Retrieve rows from the result set one at a time.
  • Close the cursor: Release the context area and free up resources.
Example of Explicit Cursor

Let's create an explicit cursor to fetch and display employee names and their job IDs from the employees table.

DECLARE
    -- Step 1: Declare the cursor
    CURSOR c_employee IS
        SELECT first_name, job_id
        FROM employees;
    
    v_first_name employees.first_name%TYPE;
    v_job_id employees.job_id%TYPE;
BEGIN
    -- Step 2: Open the cursor
    OPEN c_employee;
    
    LOOP
        -- Step 3: Fetch from the cursor
        FETCH c_employee INTO v_first_name, v_job_id;
        
        -- Exit the loop when no more rows are fetched
        EXIT WHEN c_employee%NOTFOUND;
        
        -- Process the fetched row (in this case, display the data)
        DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_first_name || ', Job ID: ' || v_job_id);
    END LOOP;
    
    -- Step 4: Close the cursor
    CLOSE c_employee;
END;

Explanation
  • Declare the cursor: The cursor c_employee is declared to select first_name and job_id from the employees table.
  • Open the cursor: The OPEN statement executes the query and establishes the result set.
  • Fetch from the cursor: The FETCH statement retrieves rows from the result set one at a time and stores the values in the variables v_first_name and v_job_id.
  • Close the cursor: The CLOSE statement releases the context area.

Benefits of Using Cursors
  • Control: Explicit cursors provide more control over query execution and result set processing.
  • Performance: They can improve performance by fetching rows in a controlled manner.
  • Flexibility: Cursors can be used to process rows in a loop and perform operations on each row.
Real Time Example

Let's create a sample schema and use it in a PL/SQL code example that utilizes cursors.

Sample Schema
We'll create a simple schema with two tables: departments and employees.

departments table:
  • department_id (Primary Key)
  • department_name
employees table:
  • employee_id (Primary Key)
  • first_name
  • last_name
  • department_id (Foreign Key referencing departments)

SQL Script to Create the Schema
-- Create departments table
CREATE TABLE departments (
department_id NUMBER PRIMARY KEY,
department_name VARCHAR2(50)
);


-- Create employees table
CREATE TABLE employees (
employee_id NUMBER PRIMARY KEY,
first_name VARCHAR2(50),
last_name VARCHAR2(50),
department_id NUMBER,
FOREIGN KEY (department_id) REFERENCES departments(department_id)
);


-- Insert sample data into departments
INSERT INTO departments (department_id, department_name) VALUES (1, 'HR');
INSERT INTO departments (department_id, department_name) VALUES (2, 'Finance');
INSERT INTO departments (department_id, department_name) VALUES (3, 'IT');


-- Insert sample data into employees
INSERT INTO employees (employee_id, first_name, last_name, department_id) VALUES (101, 'John', 'Doe', 1);
INSERT INTO employees (employee_id, first_name, last_name, department_id) VALUES (102, 'Jane', 'Smith', 2);
INSERT INTO employees (employee_id, first_name, last_name, department_id) VALUES (103, 'Mike', 'Johnson', 3);
INSERT INTO employees (employee_id, first_name, last_name, department_id) VALUES (104, 'Emily', 'Davis', 1);


PL/SQL Code Using Cursors

We'll write a PL/SQL block that uses an explicit cursor to fetch and display the names of employees along with their department names.

DECLARE
    -- Cursor to select employee details
    CURSOR c_employee IS
        SELECT e.first_name, e.last_name, d.department_name
        FROM employees e
        JOIN departments d ON e.department_id = d.department_id;
    
    v_first_name employees.first_name%TYPE;
    v_last_name employees.last_name%TYPE;
    v_department_name departments.department_name%TYPE;
BEGIN
    -- Open the cursor
    OPEN c_employee;
    
    LOOP
        -- Fetch the employee details into variables
        FETCH c_employee INTO v_first_name, v_last_name, v_department_name;
        
        -- Exit the loop when no more rows are fetched
        EXIT WHEN c_employee%NOTFOUND;
        
        -- Process the fetched row (in this case, display the data)
        DBMS_OUTPUT.PUT_LINE('Employee: ' || v_first_name || ' ' || v_last_name || ', Department: ' || v_department_name);
    END LOOP;
    
    -- Close the cursor
    CLOSE c_employee;
END;


Explanation
  • Declare the cursor: The cursor c_employee is declared to join the employees and departments tables, selecting first_name, last_name, and department_name.
  • Open the cursor: The OPEN statement executes the query and establishes the result set.
  • Fetch from the cursor: The FETCH statement retrieves rows from the result set one at a time and stores the values in the variables v_first_name, v_last_name, and v_department_name.
  • Process the fetched row: The DBMS_OUTPUT.PUT_LINE procedure is used to display the employee names along with their department names.
  • Close the cursor: The CLOSE statement releases the context area.

This example demonstrates how to use an explicit cursor in PL/SQL to fetch and process rows from a result set. 

Comments

Post a Comment

Popular posts from this blog

KTU DBMS LAB CSL 333 BTech S5 - Dr Binu V P

 About Me About the course Syllabus and Experiments **************************************** Introduction to RDBMS Working with MySQL ER Diagrams-MySQL Workbench Introduction to SQL Data types in MySQL Built-in Functions MySQL Aggregate Functions Enforcing rules using constraints MySQL -Lab Exercise ****************************************** Joining tables Order by, group by and having clause Nested Queries MySQL -Lab Exercise ******************************************* Insert Data from a CSV file Set Operations Queries using temporary tables TCL commands in MySQL DCL commands in MySQL Views and Assertions Library Database- Lab Exercise ********************************************* PL/SQL and Stored Procedures IF statement in PL/SQL CASE statement in PL/SQL Loops in PL/SQL Procedures in PL/SQL Functions in PL/SQL Triggers in PL/SQL Cursors in PL/SQL Packages in PL/SQL Exception Handling in PL/SQL Backing up and Restoring Databases Query Analysis using Query Plan ***********************
Read more

Procedures in PL/SQL

Procedures in PL/SQL (Procedural Language/Structured Query Language) are a way to encapsulate a set of SQL and PL/SQL statements that perform a specific task. They are stored in the database and can be executed as needed, providing a modular and reusable approach to database programming. Here’s an overview of creating and using procedures in PL/SQL: Creating a Procedure To create a procedure, you use the CREATE PROCEDURE statement followed by the procedure's name, parameters (if any), and the body of the procedure, which contains the executable code. Here’s a basic syntax: CREATE [OR REPLACE] PROCEDURE procedure_name     [ (parameter_name [IN | OUT | IN OUT] datatype [, ...]) ] IS    -- Declaration of variables BEGIN    -- Executable statements EXCEPTION    -- Exception handling END procedure_name; Example Here’s a simple example of a PL/SQL procedure that inserts a new employee record into an employees table: CREATE OR REPLACE PROCEDURE add_employee (     p_emp_id IN employees.emp
Read more