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

KTU DBMS LAB CSL 333 Syllabus and Experiments

 Syllabus 1. Design a database schema for an application with ER diagram from a problem description **. 2. Creation, modification, configuration, and deletion of databases using UI and SQL Commands **. 3. Creation of database schema - DDL (create tables, set constraints, enforce relationships, create indices, delete and modify tables). Export ER diagram from the database and verify relationships** (with the ER diagram designed in step 1). 4. Database initialization - Data insert, Data import to a database (bulk import using UI and SQL Commands)**. 5. Practice SQL commands for DML (insertion, updating, altering, deletion of data, and viewing/querying records based on condition in databases)**. 6. Implementation of built-in functions in RDBMS**. 7. Implementation of various aggregate functions in SQL**. 8. Implementation of Order By, Group By & Having clause **. 9. Implementation of set operators nested queries, and join queries **. 10. Implementation of queries using temp tables. 11
Read more

Creating a Database and Table ,executing queries - MySQL Lab Exercise

Create a database school and use it CREATE DATABASE school; USE school; Let's create a simple table schema for a students table in MySQL, followed by a set of queries to interact with the data. id : An integer that uniquely identifies each student (primary key). first_name : The student's first name. last_name : The student's last name. birthdate : The student's birth date. email : The student's email address. gender : The student's gender. enrollment_date : The date when the student was enrolled. CREATE TABLE students (     id INT AUTO_INCREMENT PRIMARY KEY,     first_name VARCHAR(50) NOT NULL,     last_name VARCHAR(50) NOT NULL,     birthdate DATE NOT NULL,     email VARCHAR(100) NOT NULL,     gender ENUM('Male', 'Female', 'Other') NOT NULL,     enrollment_date DATE NOT NULL ); check the schema desc students; Inserting Sample Data INSERT INTO students (first_name, last_name, birthdate, email, gender, enrollment_date) VALUES ('John&
Read more