PL/SQL MÁGICO

Funciones Mágicas en SQL: del agrupamiento al redondeo

—

by

MoQueTe
in

Objetivos

  • Descubrir cómo estas funciones transforman tus consultas en herramientas poderosas y precisas.
  • Ampliar el repertorio de herramientas disponibles en Oracle.
  • Conocer las funciones GROUP_ID, ASCII, CHR, ABS, CEIL y FLOOR.

Introducción

En el universo de SQL, existen funciones que parecen esconder verdaderos “poderes mágicos”, capaces de transformar consultas comunes en soluciones precisas y eficientes. En este artículo exploramos seis de ellas que, aunque distintas en propósito, comparten algo en común: hacen que trabajar con datos sea más claro, potente y expresivo.

Desde el enigmático GROUP_ID, que permite distinguir agrupaciones dentro de conjuntos complejos, hasta ASCII y CHR, que facilitan la conversión entre caracteres y códigos, pasando por funciones numéricas como ABS, CEIL y FLOOR, cada una cumple un papel clave en la construcción de consultas bien estructuradas.

Descubre cuándo y cómo utilizarlas con ejemplos prácticos y explicaciones claras que te ayudarán a sacar el máximo provecho de su “magia”.

Función GROUP_ID

GROUP_ID nos ayuda a diferenciar grupos duplicados resultantes al especificar la cláusula GROUP BY en una consulta.

Esta Función es útil para filtrar agrupaciones duplicadas del resultado de la consulta, esto porque para cada grupo único, la función GROUP_ID devolverá 0. Cuando se encuentra grupos duplicados, la función GROUP_ID devolverá un valor > 0; si existen n cantidad de duplicados en una consulta el resultado retornado seria n-1.

Igual que con cualquier función grupal puede utilizar GROUP_ID en la cláusula HAVING. Esto le perite eliminar los grupos duplicados de la siguiente manera:

HAVING GROUP_ID() < 0;

Nota: Esta función sólo es aplicable en una sentencia SELECT que contenga una cláusula GROUP BY.

Ejemplo:

SELECT  c.country_name,
          d.department_name,
          COUNT(*),
          GROUP_ID()
FROM
      employees e,
      departments d,
      locations l,
      countries c
WHERE e.department_id = d.department_id
AND   d.location_id = l.location_id
AND   l.country_id = l.country_id
GROUP BY  c.country_name,
    ROLLUP(c.country_name, d.department_name)
ORDER BY 1, 4 ASC;

La consulta muestra cómo GROUP_ID() identifica grupos duplicados generados por ROLLUP, asignando un número único a cada combinación de país y departamento. Esto es útil para distinguir totales y subtotales en agregaciones jerárquicas.

Funciones ASCII y CHR

La Función ASCII retorna la representación numérica en el conjunto de caracteres de la Base de Datos del carácter especificado.

Sintaxis:ASCII(char)

En la Sintaxis:

  • char puede ser del tipo de dato: CHAR, VARCHAR2, NCHAR o NVARCHAR2. El valor devuelto es  siempre del tipo NUMBER. Debe tener presente que si el conjunto de caracteres de la Base de Datos es ASCII de 7 bits, el valor retornado es ASCII. Si el conjunto de caracteres de la Base de Datos es Código EBCDIC, esta función devuelve un valor EBCDIC. No hay ninguna función de carácter EBCDIC correspondiente.

Nota: Esta función no soporta datos CLOB directamente. Sin embargo, los CLOB pueden pasar como argumentos a través de la conversión de datos implícita.

La Función CHR es considerada opuesta a la función ASCII, Esto porque devuelve el carácter basado en el código NUMBER.

CHR(n [ USING NCHAR_CS ])

En la Sintaxis:

CHR devuelve el carácter binario equivalente a n como valor VARCHAR2 en el conjunto de caracteres de la Base de Datos o, si especifica USING NCHAR_CS, el conjunto de caracteres nacional.

Para conjuntos de caracteres de un solo byte, si n > 256, Oracle Database devuelve el equivalente binario de n mod 256. Para conjuntos de caracteres multibyte, n debe corresponder a un punto de código completo. Los puntos de código no válidos no se validan y el resultado de especificar puntos de código no válidos es indeterminado.

Esta función toma como argumento un valor NUMBER, o cualquier valor que se pueda convertir implícitamente a NUMBER, y devuelve un carácter.

Ejemplos

SELECT
       ASCII('\')               AS  "Función ASCII",
       CHR(555)                 AS  "Función CHR",
       ASCII('Hola')            AS  "Función ASCII 2",
       CHR(555 USING NCHAR_CS)  AS  "Función CHR 2"
FROM dual;

Esta consulta demuestra cómo funcionan ASCII y CHR en Oracle.

  • ASCII(”) devuelve el código del carácter \ (92).
  • CHR(555) convierte un número en carácter según el charset de la BD.
  • ASCII(‘Hola’) toma solo el primer carácter (H → 72).
  • CHR(555 USING NCHAR_CS) hace lo mismo, pero usando Unicode.

💡 En resumen: ASCII convierte texto a número y CHR número a texto, con diferencias según el tipo de codificación.

SET SERVEROUTPUT ON
DECLARE
    v_encabezado        VARCHAR2(100);
    v_longitud          NUMBER(2);
    v_encab_number      VARCHAR2(200);
BEGIN

    v_encabezado    :=  'A Continuación el Abecedario'||RPAD(CHR(33),3,CHR(33));

    v_longitud := LENGTH(v_encabezado);

    DBMS_OUTPUT.PUT_LINE(v_encabezado);

    FOR i IN 1..v_longitud LOOP

        v_encab_number  :=  v_encab_number||'|'||ASCII(SUBSTR(v_encabezado,i,1));

    END LOOP;

    DBMS_OUTPUT.PUT_LINE(v_encab_number);

    DBMS_OUTPUT.PUT_LINE('|Número|'||'|ASCII|');

    FOR i IN 65..90 LOOP
        DBMS_OUTPUT.PUT_LINE(CHR(9)||i||RPAD(CHR(9),3,CHR(9))||CHR(i));
    END LOOP;
END;

Este bloque PL/SQL muestra cómo combinar ASCII y CHR para trabajar con texto y códigos.

  • Construye un encabezado con caracteres especiales (CHR).
  • Recorre el texto y convierte cada carácter a su valor numérico (ASCII).
  • Finalmente, imprime el abecedario usando códigos del 65 al 90 (A–Z).

💡 Demuestra cómo transformar texto ↔ números y generar salidas dinámicas en consola con DBMS_OUTPUT.

Función ABS

La Función ABS devuelve el valor absoluto de número recibido como parámetro.

Esta función toma como argumento cualquier tipo de dato numérico o cualquier tipo de dato no numérico que se pueda convertir implícitamente en un tipo de dato numérico. El valor de retorno es del mismo tipo de dato numérico que el del argumento.

Ejemplo:

SELECT  ABS(-56),
        ABS(4),
        ABS('-6'),
        ABS((5*8/17)*-6)
FROM    dual;

Funciones CEIL y FLOOR

Presentamos estas funciones en conjunto debido a que guardan cierta similitud.

La función CEIL devuelve el menor valor entero que es mayor o igual que el número especificado.

  • Si el número especificado es NULL, el resultado es NULL.
  • Si el número especificado es igual a un entero matemático, el resultado es el mismo número especificado.
  • Si el número especificado es cero (0), el resultado es cero.
  • Si el número especificado es menor que cero pero mayor que -1.0, el resultado es cero.

El valor devuelto es el valor de punto flotante doble más pequeño (más cercano al valor negativo) que es mayor o igual al número especificado. El valor devuelto es igual a un entero matemático. El tipo de dato del mismo es un número de DOBLE PRECISIÓN.

Nota: la función CEIL es equivalente a CEILING.

La función FLOOR devuelve el mayor valor entero que es igual o menor que el número especificado.

  • Si el número especificado es NULL, el resultado es NULL.
  • Si el número especificado es igual a un entero matemático, el resultado es el mismo número.
  • Si el número especificado es cero (0), el resultado es cero.

El valor devuelto es el valor de doble punto flotante más grande (más cercano al infinito positivo) que es menor o igual al número especificado. El valor devuelto es igual a un entero matemático. El tipo de dato del mismo es un número de DOBLE PRECISIÓN.

SELECT
        FLOOR(-15),
        CEIL(-15),
        FLOOR(1.2),
        CEIL(1.2),
        FLOOR(-0.4),
        CEIL(-0.4),
        FLOOR(-5.6),
        CEIL(-5.6)
FROM dual;
  • FLOOR(-15): Retorna -15 porque es el mayor entero que es igual a -15.
  • CEIL(-15): Retorna -15 porque es el menor entero que es igual a -15.
  • FLOOR(1.2): Retorna 1 porque es el mayor entero que es menor que 1.2.
  • CEIL(1.2): Retorna 2 porque es el menor entero que es mayor que 1.2.
  • FLOOR(-0.4): Retorna -1 porque es el mayor entero que es menor que -0.4.
  • CEIL(-0.4): Retorna 0 porque es el menor entero que es mayor que -0.4.
  • FLOOR(-5.6): Retorna -6 porque es el mayor entero que es menor que -5.6.
  • CEIL(-5.6): Retorna -5 porque es el menor entero que es mayor que -5.6.