How to Pagination in PL/SQL?

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.

How to do Pagination in PL/SQL

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

• Using ROW_NUMBER() with Subqueries
• Using Cursors with OFFSET and LIMIT
• Using Analytic Functions with ROWNUM,

Setting up Environment

First, we will create an ‘employees’ table and insert some values into it.

-- Create table 'employees'
CREATE TABLE employees (
    employee_id NUMBER,
    first_name VARCHAR2(50),
    last_name VARCHAR2(50),
    salary NUMBER
);

-- Insert sample data into 'employees'
INSERT INTO employees (employee_id, first_name, last_name, salary) VALUES (1, 'John', 'Doe', 50000);
INSERT INTO employees (employee_id, first_name, last_name, salary) VALUES (2, 'Jane', 'Smith', 60000);
INSERT INTO employees (employee_id, first_name, last_name, salary) VALUES (3, 'Michael', 'Johnson', 70000);
INSERT INTO employees (employee_id, first_name, last_name, salary) VALUES (4, 'Emily', 'Brown', 55000);
INSERT INTO employees (employee_id, first_name, last_name, salary) VALUES (5, 'David', 'Lee', 65000);

SELECT * FROM EMPLOYEES;

Output:

Create a table and insert values

1. Using ROW_NUMBER() with Subqueries

Utilize the ROW_NUMBER() function within subqueries to assign row numbers to dataset entries, enabling precise pagination.

Syntax:

SELECT * FROM (
    SELECT columns, ROW_NUMBER() OVER (ORDER BY column_name) AS row_num
    FROM table_name
    WHERE conditions
) 
WHERE row_num BETWEEN start_row AND end_row;

Let’s consider the table employees with columns employee_id, first_name, last_name, and salary. We’ll paginate through this table using this method

Example: Pagination using ROW_NUMBER() with Subqueries

SELECT * FROM (
    SELECT employee_id, first_name, last_name, salary, ROW_NUMBER() OVER (ORDER BY employee_id) AS row_num
    FROM employees
) 
WHERE row_num BETWEEN 1 AND 3;

Output:

Pagination using ROW_NUMBER() with Subqueries

Explanation: This query retrieves the first 3 records from the employees table sorted by employee_id.

2. Using Cursors with OFFSET and LIMIT

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:

Using Cursors with OFFSET and LIMIT

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

3. Using Analytic Functions with ROWNUM

In this approach, we combine analytic functions with ROWNUM to achieve pagination.

Syntax:

SELECT employee_id, first_name, last_name, salary
FROM (
    SELECT employee_id, first_name, last_name, salary, ROW_NUMBER() OVER (ORDER BY employee_id) AS row_num
    FROM employees
)
WHERE row_num BETWEEN 1 AND 3;

Explanation:

• We use a subquery to assign row numbers to each record based on the desired ordering (in this case, ORDER BY employee_id).
• Then, we select the columns we need along with the row numbers from the subquery.
• Finally, we filter the results using the ROW_NUM column to retrieve the desired page of results (in this case, rows 1 to 3).

Example: Pagination using Analytic Functions with ROWNUM

SELECT employee_id, first_name, last_name, salary
FROM (
    SELECT employee_id, first_name, last_name, salary, ROW_NUMBER() OVER (ORDER BY employee_id) AS row_num
    FROM employees
)
WHERE row_num BETWEEN 1 AND 3;

Output:

Pagination using Analytic Functions with ROWNUM

Explanation: This query retrieves the first 3 records from the employees table sorted by employee_id using analytic functions.

Conclusion

Pagination is very important for managing large datasets effectively. In PL/SQL, various methods exists for pagination, here we have used 3 approaches, use of ROWNUM, OFFSET, and LIMIT, and Analytic Functions, which help us in efficient pagination. By understanding these approaches, developers can optimize query performance and enhance user experience when dealing with sizable data in their applications.