How to use Cursors with OFFSET and LIMIT In SQL

In this approach, we utilize cursors with the OFFSET and LIMIT clauses to achieve pagination.

Syntax:

DECLARE
    CURSOR employee_cursor IS
        SELECT employee_id, first_name, last_name, salary
        FROM employees
        ORDER BY employee_id;
    v_employee_id employees.employee_id%TYPE;
    v_first_name employees.first_name%TYPE;
    v_last_name employees.last_name%TYPE;
    v_salary employees.salary%TYPE;
BEGIN
    OPEN employee_cursor;
    FETCH employee_cursor INTO v_employee_id, v_first_name, v_last_name, v_salary;
    FOR i IN 1..3 LOOP
        DBMS_OUTPUT.PUT_LINE('Employee ID: ' || v_employee_id || ', Name: ' || v_first_name || ' ' || v_last_name || ', Salary: ' || v_salary);
        FETCH employee_cursor INTO v_employee_id, v_first_name, v_last_name, v_salary;
    END LOOP;
    CLOSE employee_cursor;
END;
/

Explanation:

• We declare a cursor employee_cursor to select the desired columns from the employees table, ordered by employee_id. Next, we declare variables to store the fetched values from the cursor.
• Inside the BEGIN block, we open the cursor and fetch the first record into the declared variables.
• We then iterate through the cursor using a FOR loop and FETCH statement to retrieve and display the desired number of records (in this case, 3). Finally, we close the cursor.

Example: Using Cursors with OFFSET and LIMIT

DECLARE
    CURSOR employee_cursor IS
        SELECT employee_id, first_name, last_name, salary
        FROM employees
        ORDER BY employee_id;
    v_employee_id employees.employee_id%TYPE;
    v_first_name employees.first_name%TYPE;
    v_last_name employees.last_name%TYPE;
    v_salary employees.salary%TYPE;
BEGIN
    OPEN employee_cursor;
    FETCH employee_cursor INTO v_employee_id, v_first_name, v_last_name, v_salary;
    FOR i IN 1..3 LOOP
        DBMS_OUTPUT.PUT_LINE('Employee ID: ' || v_employee_id || ', Name: ' || v_first_name || ' ' || v_last_name || ', Salary: ' || v_salary);
        FETCH employee_cursor INTO v_employee_id, v_first_name, v_last_name, v_salary;
    END LOOP;
    CLOSE employee_cursor;
END;
/

Output:

Explanation: This PL/SQL block fetches records from the employees table using a cursor and outputs the first 3 records.

Pagination is a technique used to divide large datasets into smaller, manageable chunks called pages. It’s important for improving user experience and optimizing database performance. In PL/SQL, pagination can be achieved using various methods, allowing users to navigate through query results efficiently.

In this article, we’ll explore how to efficiently manage large datasets in PL/SQL using pagination techniques. We’ll cover three methods: ROW_NUMBER() with Subqueries, Cursors with OFFSET and LIMIT, and Analytic Functions with ROWNUM. Each method is explained with examples to provide a clear understanding.