Paginación de datos con PL/SQL de Oracle

¿Se puede realizar una paginación de datos con PL/SQL de Oracle? En este artículo te mostramos una manera de paginar los datos de una tabla con la creación de una función y desarrollo de un bloque anónimo en Oracle.

Como ya muchos saben la paginación es una de las maneras en la que podemos accesar a la información existente en una colección de datos de manera simplificada o reducida. Esto nos permite segmentar la información de acuerdo a un límite de registros, ayudando a mejorar el rendimiento de la consulta.

Para tratar de comprender mejor la idea supongamos que tenemos una tabla con 20 registros y queremos mostrar todos los registros pero de manera segmentada, cada 5 registros. En dicho caso, la paginación nos permitiría consultar los primeros 5 registros en una primera página, luego nos mostraría los siguientes 5 registros en una segunda página y así sucesivamente hasta llegar al final de lo registros que hay en la tabla.

Nota: Si este recurso te ha servido de ayuda recuerda que puedes apoyar mi trabajo realizando una contribución voluntaria a través de mi perfil de usuario de la página y/o puedes enviar tus comentarios a través del sistema de contacto.

Paginación con PL/SQL de Oracle

Lo primero que debemos hacer es crear una pequeña función que nos ayudará a manejar la paginación o segmentación de los registros de la tabla que vamos a consultar.

CREATE OR REPLACE FUNCTION F_PAGINATION(
P_TOTAL IN NUMBER,
P_PAGENUM IN NUMBER DEFAULT 0,
P_PAGEGROUP IN NUMBER DEFAULT 5)
RETURN VARCHAR2
IS
c_page CONSTANT NUMBER(1) := 1;

v_p NUMBER(3) := 0;
v_x NUMBER(3);
v_y NUMBER(3);
v_limit VARCHAR2(100) := '';
BEGIN

IF P_PAGENUM > 0 THEN
v_p := P_PAGENUM;
ELSE
v_p := c_page;
END IF;

IF v_p IS NOT NULL AND (v_p > 0 AND v_p <= CEIL(P_TOTAL / P_PAGEGROUP)) THEN
v_p := v_p;
ELSE
v_p := c_page;
END IF;

v_y := P_PAGEGROUP;
v_x := (v_p - c_page) * v_y;
v_limit := 'OFFSET ' || v_x || ' ROWS FETCH NEXT ' || v_y || ' ROWS ONLY';

IF v_limit IS NOT NULL THEN
RETURN v_limit;
END IF;

EXCEPTION
WHEN OTHERS
THEN
RETURN v_limit;
END F_PAGINATION;
/

Function created.

Una vez que tengamos nuestra función compilada en nuestra base de datos podemos probar el resultado de la misma a través de la siguiente sentencia SQL.

SELECT (SELECT COUNT(*) FROM HR.job_history) COUNT,
F_PAGINATION((SELECT COUNT(*) FROM HR.job_history),1,5)
FROM DUAL

Resultado:

COUNT F_PAGINATION((SELECT COUNT(*)FROM HR.JOB_HISTORY),1,5)
11 OFFSET 0 ROWS FETCH NEXT 5 ROWS ONLY

Por último debemos llevar la función a un bloque anónimo PL/SQL para trabajar con la paginación de los datos de una tabla.

DECLARE
TYPE jobHisCur IS REF CURSOR;
v_cursor jobHisCur;
his_record HR.job_history%ROWTYPE;
v_total NUMBER(5) := 1000;
v_sql VARCHAR2(1024);
v_limit VARCHAR2(100);
v_min NUMBER(1);
v_max NUMBER(2) := 5;
BEGIN

/*
* Puede acceder al total de registros
* y asignarlo a la variable v_total
SELECT COUNT(*)
INTO v_total
FROM HR.job_history;
*/

v_min := 1; -- Valor de la página a consultar que debe ser variable (1,2,3...n)

/*
* Paginación
* el valor de "v_min" se debe incrementar o disminuir en 1 para consultar los valores
* el valor de "v_max" indica la cantidad de registros a mostrar por página
*/

v_limit := F_PAGINATION(v_total,v_min,v_max);

-- DML
v_sql := 'SELECT * FROM HR.job_history ' || v_limit;

-- Abrir cursor
OPEN v_cursor FOR v_sql;

-- Recorrer cursor
LOOP
FETCH v_cursor INTO his_record;
EXIT WHEN v_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE (
his_record.EMPLOYEE_ID
||' '||his_record.START_DATE
||' '||his_record.END_DATE
||' '||his_record.JOB_ID
||' '||his_record.DEPARTMENT_ID);
END LOOP;

-- Cerrar cursor
CLOSE v_cursor;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ERROR: un error ha ocurrido');
END;
/

Statement processed.
Resultado de la Página 1:

102 13-JAN-01 24-JUL-06 IT_PROG 60
101 21-SEP-97 27-OCT-01 AC_ACCOUNT 110
101 28-OCT-01 15-MAR-05 AC_MGR 110
201 17-FEB-04 19-DEC-07 MK_REP 20
114 24-MAR-06 31-DEC-07 ST_CLERK 50

Puede comprobar los resultados de las sentencias a través de Live Sql de Oracle.

Consulta Lo+ Top de Base De Datos