Oracle – PL/SQL – Paginación de registros de una consulta

Sea la consulta:

SELECT primera_columna, segunda_columna
  FROM mi_tabla

Si queremos recuperar los registros que se encuentran entre las filas n_start_row y n_end_row, nuestra consulta seria:

        SELECT *
          FROM ( SELECT A.*, ROWNUM row_number
                   FROM (
                        SELECT primera_columna, segunda_columna
                        FROM mi_tabla
                    ) A
                  WHERE ROWNUM <= n_end_row
            )
        WHERE row_number >= n_start_row;

Ahora si queremos paginar, primero tendriamos que saber cuantos registros recupera nuestra consulta (n_row_count) y definir el número de registros por cada página (n_page_size). Por ejemplo:

DECLARE
    CURSOR c_row_count
    IS
        SELECT count(1)
        FROM (
            SELECT primera_columna, segunda_columna
            FROM mi_tabla
        );
    --
    n_page_size   NUMBER(2) := 10;
    n_row_count   c_row_count%ROWTYPE;
BEGIN
    OPEN c_row_count;
    FETCH c_row_count INTO n_row_count;
    CLOSE c_row_count;
    --
    -- Pintamos los números de las páginas
    --
    IF n_row_count > n_page_size
    THEN
        FOR x IN 1..CEIL(n_row_count/n_page_size)
        LOOP
            dbms_output.put_line('n_page_number: ' || x);
        END LOOP;
    END IF;
END;
/

Asumiendo que el número de la pagina sea n_page_number y el número de registros por página sea n_page_size.

        SELECT *
          FROM ( SELECT A.*, ROWNUM row_number
                   FROM (
                        SELECT primera_columna, segunda_columna
                        FROM mi_tabla
                    ) A
                  WHERE ROWNUM < ((n_page_number * n_page_size) + 1 )
            )
        WHERE row_number >= (((n_page_number-1) * n_page_size) + 1);

Recuperar un número limitado de registros en una consulta

Aparte de las cláusulas especificas de cada base de datos, la clave esta en utilizar ORDER BY ASC (ordenación de menor a mayor) para los primeros resultados y ORDER BY DESC (ordenación de mayor a menor) para los últimos resultados de la consulta.

Sea la consulta:

select primera_columna,
       segunda_columna
  from mitabla

En los ejemplos estamos recuperando los últimos 10 resultados de la consulta.

Para MS SQL Server ó MS Access, utiliza la cláusula TOP:

select TOP 10
       primera_columna,
       segunda_columna
  from mitabla
 order
    by primera_columna desc

Para MySQL ó PostgreSQL, utiliza la cláusula LIMIT:

select primera_columna,
       segunda_columna
  from mitabla
 order
    by primera_columna desc
 limit 10

Para Oracle, utiliza la cláusula ROWNUM:

select *
  from ( select primera_columna,
                segunda_columna
           from mitabla
          order
             by primera_columna desc )
 where rownum <= 10

Para DB2, utiliza la cláusula FETCH FIRST:

select primera_columna,
       segunda_columna
  from mitabla
 order
    by primera_columna descending
fetch first 10 rows only

Oracle – pl/sql – rendimiento de bucles – loop performance

Carlos Garcia publica un post en el blog de Innova acerca de las diferencias de tiempo que obtiene al hacer el mismo número de iteraciones dentro de un bucle. Yo hice la prueba de rendimiento con diferentes formas de bucles, el mismo número de iteraciones y a poder ser el mismo código a ejecutar, que será básicamente un contador:

DECLARE
    i       NUMBER := 0;
    j       NUMBER := 0;
    b       BOOLEAN := FALSE;
    i_begin INTEGER;
    n_limit CONSTANT NUMBER := 100000000;
BEGIN
    i_begin := DBMS_UTILITY.GET_TIME;
    j := 0;
    FOR i IN 0..n_limit
    LOOP
        j := j + 1;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE (
           TO_CHAR((DBMS_UTILITY.GET_TIME - i_begin)/100)
        || '  FOR'
    );
    --
    i_begin := DBMS_UTILITY.GET_TIME;
    j := 0;
    LOOP
        j := j + 1;
        EXIT WHEN j = n_limit;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE (
           TO_CHAR((DBMS_UTILITY.GET_TIME - i_begin)/100)
        || '  LOOP EXIT WHEN'
    );
    --
    i_begin := DBMS_UTILITY.GET_TIME;
    j := 0;
    LOOP
        j := j + 1;
        IF j = n_limit
        THEN
            EXIT;
        END IF;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE (
           TO_CHAR((DBMS_UTILITY.GET_TIME - i_begin)/100)
        || '  LOOP IF EXIT'
    );
    --
    i_begin := DBMS_UTILITY.GET_TIME;
    j := 0;
    WHILE TRUE
    LOOP
        j := j + 1;
        EXIT WHEN j = n_limit;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE (
           TO_CHAR((DBMS_UTILITY.GET_TIME - i_begin)/100)
        || '  WHILE TRUE EXIT WHEN'
    );
    --
    i_begin := DBMS_UTILITY.GET_TIME;
    j := 0;
    WHILE j < n_limit + 1
    LOOP
        j := j + 1;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE (
           TO_CHAR((DBMS_UTILITY.GET_TIME - i_begin)/100)
        || '  WHILE LOOP'
    );
    --
    i_begin := DBMS_UTILITY.GET_TIME;
    j := 0;
    WHILE b = FALSE
    LOOP
        j := j + 1;
        IF j = n_limit
        THEN
            b := TRUE;
        END IF;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE (
           TO_CHAR((DBMS_UTILITY.GET_TIME - i_begin)/100)
        || '  WHILE LOOP IF THEN'
    );
END;

Se obtiene como resultado (en segundos):

10,48  FOR
12,58  LOOP EXIT WHEN
12,59  LOOP IF EXIT
12,61  WHILE TRUE EXIT WHEN
13,11  WHILE LOOP
16,61  WHILE LOOP IF THEN

el WHILE como se puede ver es el más lento de ejecutar, de todas maneras si el código que vas a ejecutar son sentencias DML yo hago lo que recomienda ORACLE y es utilizar el FORALL que es incluso más eficiente que el FOR.

11 Tuning PL/SQL Applications for Performance
http://www.stanford.edu/dept/itss/docs/oracle/10g/
appdev.101/b10807/12_tune.htm

Via:
http://www.balearsinnovacio.com/blog/?p=490

QEM – Quest Error Manager – maneja errores y excepciones en plsql

Quest Error Manager es un framework que te ayuda a estandarizar el manejo de los errores y excepciones en aplicaciones ORACLE PLSQL. Escritas por el bien conocido Steven Feuersteins, básicamente son 4 tablas (q$error_context, q$error, q$error_instance, q$log) y un package (q$error_manager), te descargas el archivo qem.zip descomprimes y solo tendras que ejecutar el script: qem$install.sql que te añadirá en tu esquema las tablas y el package, luego viene una demo y ejemplos para que puedas ver su funcionamiento.

Según Steven el QEM te ayudará en 2 areas:

1.- Definición y manejo de errores

2.- Tracear tu aplicación en tiempo de ejecución.

Si luego decides quitarlo de tu esquema Steven ya te creado el script qem$uninstall.sql

No dudo que cada uno ya ha creado y tiene montado un manejo de errores y excepciones en sus aplicaciones pero no estaria mal echarle un vistazo al código que siempre se puede aprender y más de Steven Feuersteins un Gurú del PLSQL.

Info:
http://www.toadworld.com/Knowledge/DatabaseKnowledge/
StevenFeuersteinsPLSQLObsession/MyPetProjectsandContributions/
QuestErrorManagerQEM/tabid/210/Default.aspx

Descargar:
http://www.oracleplsqlprogramming.com/downloads/qem.zip

Página de Steven Feuersteins:
http://www.stevenfeuerstein.com/

Tip – Trick – Oracle – PLSQL – mostrar el número de línea donde se produce el error

Esto ayuda a los que desarrollamos aplicaciones web con acceso a base de datos ORACLE. A partir de la versión de Oracle 10g, tenemos una función que devuelve una cadena de texto con el número de línea donde se ha producido un error, hacemos un ejemplo en la pestaña SQL Editor del TOAD con un bloque anónimo, en donde provocaremos un error comparando un valor númerico (NUMBER) con una cadena de texto:

El código de nuestro pequeño bloque anónimo es el siguiente:

DECLARE
   dummy NUMBER := 0;
BEGIN
    if dummy = 'alfa'
    then
        dbms_output.put_line('if');
    else
        dbms_output.put_line('else');
    end if;
EXCEPTION
   WHEN OTHERS THEN
      dbms_output.put_line(sqlerrm);
      dbms_output.put_line(DBMS_UTILITY.format_error_backtrace);
END;

Lo que se muestra en el DBMS_OUTPUT es:

ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at line 4

El SQLERRM como todos sabemos nos devuelve el mensaje de error, y DBMS_UTILITY.format_error_backtrace nos recupera el número de línea donde esta ocurriendo el error, si el código del EXCEPTION:

dbms_output.put_line(sqlerrm);
dbms_output.put_line(DBMS_UTILITY.format_error_backtrace);

estuviera en un paquete (package) el mensaje seria de la forma:

ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at “IDENTIFICADOR_PACKAGE.IDENTIFICADOR_PROCEDIMIENTO_FUNCION”, line 4

Esto es muy útil al momento de tracear errores en tiempo de ejecución y en el manejo de excepciones, ya que no solo sabemos la descripción del error sino que también sabemos el número de la línea de nuestro paquete donde sucedio.