Otras Funciones SQL de Oracle

Objetivos:

• Ampliar el repertorio de herramientas disponibles en Oracle.

NOTA: Usamos como ejemplo la Base de Datos: ORCL, la cual viene por defecto en cualquier versión de ORACLE.

____________________________________________________________________________________
Funciones Útiles.

Esta publicación viene a complementar las antes publicadas: Sentencias POCO Comunes en SQL y Funciones Útiles, SQL, en un intento de expandir y/o ampliar el repertorio programático del lector.

____________________________________________________________________________________

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.^[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.

Ejemplos:

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;

/*Notar en el OUTPUT los registros duplicados por la cláusula ROLLUP con el valor 1 en la columna GROUP_ID()*/

---OUTPUT:

____________________________________________________________________________________

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.^[1]

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.

Sintaxis:

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;

/*En esta consulta usamos las funciones ASCII y CHR con algunos valores; Notar que en el ejemplo ASCII('Hola') la función retorna el valor numérico del primer  carácter, en este caso 'H' = 72, también vemos como el valor retornado de la función CHR varia si se especifica el cláusula opcional: USING NCHAR_CS.*/

---OUTPUT:

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));
                        --CHR(33) = ! y con RPAD se le concatena 2 caracteres mas...

    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)
--CHR(9) = Espacio Tabular(horizontal tab)
                            );
    END LOOP;
END;

/*Tratamos de darle un poco más de rigurosidad a este ejemplo en el cual usamos las funciones CHR y ASCII de manera combinada; Notar como en el OUTPUT se imprime la cadena 'A Continuación el Abecedario!!!' y posteriormente se muestra '|65|32|67|111|110|116|105|110|117|...' la cual representa el valor numérico de cada carácter usado.*/
---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;
/**/
---OUTPUT:

____________________________________________________________________________________

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.

Ejemplo:
SELECT
        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
FROM dual;
/*El ejemplo muestra la funcionalidad de ambas funciones.*/
---OUTPUT:

_____________________________________________________________________________________

Fuentes: https://docs.oracle.com