Funciones númericas en PL/SQL y el SQL de Oracle

Las bases de datos Oracle ofrecen un extenso conjunto de funciones estándar SQL y PL/SQL para manipular números y realizar conversiones entre números y cadenas de caracteres. En este artículo hablaremos sobre las funciones numéricas más comunes y que se tienen que utilizar con mayor frecuencia a la hora de programar en PL/SQL, siendo su conocimiento fundamental para cualquier programador de bases de datos Oracle.

Las funciones numéricas estándar más comunes del PLSQL y el SQL de Oracle son:

ROUND (m, n): la función ROUND acepta como entrada un número y devuelve como salida otro número redondeado a un número específico de decimales (que se indica en el segundo parámetro de la función). Si no se especifica el número de decimales a mostrar (parámetro n), la función ROUND devolverá el número redondeado al entero más próximo. Si el número de decimales a redondear es negativo (n es negativo), entonces el redondeo se realiza avanzando hacia la izquierda del separador decimal, en lugar de hacia la derecha (ver ejemplo).

BEGIN
  DBMS_OUTPUT.put_line (ROUND (20.35));
  DBMS_OUTPUT.put_line (ROUND (20.35, 1));
  DBMS_OUTPUT.put_line (ROUND (20.33, 1));
  DBMS_OUTPUT.put_line (ROUND (20.35, 2));
  DBMS_OUTPUT.put_line (ROUND (20.35, -2));
  DBMS_OUTPUT.put_line (ROUND (225, -2));
END;

La salida de este bloque PL/SQL sería:
20
20.4
20.3
20.35
0
200

TRUNC (m, n): la función TRUNC es muy parecida a la función ROUND, permitiendo también indicar el número de decimales a truncar a derecha (n positivo) o izquierda (n negativo) del separador decimal. La diferencia estriba en que la función TRUNC simplemente elimina o trunca los dígitos, es decir, no realiza un redondeo.

BEGIN
  DBMS_OUTPUT.put_line (TRUNC (20.35));
  DBMS_OUTPUT.put_line (TRUNC (20.35, 1));
  DBMS_OUTPUT.put_line (TRUNC (20.33, 1));
  DBMS_OUTPUT.put_line (TRUNC (20.35, 2));
  DBMS_OUTPUT.put_line (TRUNC (20.35, -2));
  DBMS_OUTPUT.put_line (TRUNC (225, -2));
END;
/

La salida de este bloque PL/SQL sería:
20
20.3
20.3
20.35
0
200

FLOOR (m): esta función devuelve el mayor entero menor o igual que el número especificado como parámetro.

BEGIN
  DBMS_OUTPUT.put_line (FLOOR (3.5));
END;
/

La salida de este bloque PL/SQL sería:
3

CEIL (m): esta función devuelve el menor entero mayor o igual que el número especificado como parámetro.

BEGIN
  DBMS_OUTPUT.put_line (CEIL (3.5));
END;
/

La salida de este bloque PL/SQL sería:
4

MOD (m, n): calcula el resto del resultado de dividir m entre n. La fórmula que emplea la base de datos Oracle para realizar el cálculo es la siguiente MOD (m, n) = m - n * FLOOR (m/n).

BEGIN
  DBMS_OUTPUT.put_line (MOD (25, 6));
  DBMS_OUTPUT.put_line (MOD (25, 9));
END;
/

La salida de este bloque PL/SQL sería:
1
7

REMAINDER (m, n): calcula el resto por exceso del resultado de dividir m entre n. La fórmula que emplea la base de datos Oracle para realizar el cálculo es la siguiente REMAINDER (m, n) = m - n * ROUND (m/n).

BEGIN
  DBMS_OUTPUT.put_line (REMAINDER (25, 6));
  DBMS_OUTPUT.put_line (REMAINDER (25, 9));
END;
/

La salida de este bloque PL/SQL sería:
1
-2

TO _CHAR (m): la función PL/SQL TO_CHAR, en su formato más sencillo, sirve para convertir un número en una cadena de caracteres. Se pasa como único parámetro un número, devolviendo la función la cadena de caracteres que representa a dicho número mostrando únicamente los dígitos significativos de dicho número (es decir, los ceros que no son significativos son eliminados).

BEGIN
  DBMS_OUTPUT.put_line (TO_CHAR (200.64));
  DBMS_OUTPUT.put_line (TO_CHAR (000200.6400));
  DBMS_OUTPUT.put_line (TO_CHAR (20000.00));
END;

La salida de este bloque PL/SQL sería:
200.64
200.64
20000

Función TO_CHAR (m, formato)

Con cierta frecuencia, los programadores PL/SQL se encuentran con la necesidad de que al convertir un número en una cadena de caracteres, dicho número cumpla con cierto formato. Por ejemplo, puede ser necesario mostrar el número de manera que incluya siempre un número fijo de decimales (aunque estos sean cero), o puede necesitarse mostrar el separador de miles. Para estos casos la función TO_CHAR debe incluir un segundo parámetro que representa la máscara del formato que debe seguir el número a representar.

Esta máscara se representa mediante una cadena de caracteres y lo más sencillo para comprender su funcionamiento es utilizar una serie de ejemplos.

En el primer ejemplo vemos que el carácter "G" indica en qué lugar de la cadena se quiere colocar el separador de grupo (en nuestro caso separador de miles). El carácter separador de grupo viene determinado por el valor del parámetro NLS_NUMERIC_CHARACTERS de la configuración del lenguaje de la base de datos Oracle, es decir, puede modificarse según las necesidades específicas de cada país (por ejemplo, en España se utiliza el punto "." mientras que en UK o USA se utiliza la coma ","). El carácter "9" le indica a la base de datos Oracle que en esa posición debe colocar un dígito significativo o, si el dígito no es significativo, un espacio en blanco.

BEGIN
  DBMS_OUTPUT.put_line (TO_CHAR (20000, '9G999G999'));
END;
/
La salida de este bloque PL/SQL sería:
   20,000

El este otro ejemplo podéis ver que si queremos que aparezcan ceros en lugar de espacios en blanco para los dígitos no significativos, entonces deberemos utilizar el carácter "0" en lugar del "9".

BEGIN
  DBMS_OUTPUT.put_line (TO_CHAR (20000, '0G000G999'));
END;
/
La salida de este bloque PL/SQL sería:
0,020,000

Pero si lo que queremos es que no aparezcan ni ceros, ni espacios en blanco al convertir nuestro número en una cadena de caracteres, tendremos que usar la cadena "FM".

BEGIN
  DBMS_OUTPUT.put_line (TO_CHAR (20000, 'FM9G999G999'));
END;
/
La salida de este bloque PL/SQL sería:
20,000

Ahora supongamos que nuestro número debe representar una cantidad en euros incluyendo los céntimos de euro y, además, queremos mostrar el símbolo del euro. Entonces deberemos usar la siguiente máscara:

BEGIN
  DBMS_OUTPUT.put_line (TO_CHAR (20000.57, 'FM999G999D99L'));
END;
/
La salida de este bloque PL/SQL sería:
20000.57€

En el ejemplo vemos que el carácter "L" especifica el lugar donde aparecerá el símbolo de la moneda local (dicho símbolo viene determinado por el parámetro de la base de datos Oracle NLS_CURRENCY). El carácter "D" determina la posición del separador decimal (al igual que el separador de grupo, el carácter que se utiliza como separador decimal, en España la coma "," y en UK o USA el punto ".", viene determinado por el parámetro NLS_NUMERIC_CHARACTERS).