Funciones númericas en PL/SQL y el SQL de Oracle
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).
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.
FLOOR (m): esta función devuelve el mayor entero menor o igual que el número especificado como parámetro.
CEIL (m): esta función devuelve el menor entero mayor o igual que el número especificado como parámetro.
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).
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).
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).
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.
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".
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".
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:
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).
Para finalizar, sólo comentaros que si estáis interesado en ver todos los caracteres y elementos que se pueden usar en PLSQL y el SQL de Oracle en la máscara de los formatos numéricos, podéis chequear la siguiente página de la web oficial 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).
Para finalizar, sólo comentaros que si estáis interesado en ver todos los caracteres y elementos que se pueden usar en PLSQL y el SQL de Oracle en la máscara de los formatos numéricos, podéis chequear la siguiente página de la web oficial de Oracle.
Comentarios
Publicar un comentario