2.3. Funciones

Existen en ORACLE muchas funciones que pueden complementar el manejo de los datos en las consultas. Se utilizan dentro de las expresiones y actuan con los valores de las columnas, variables o constantes.

Se pueden incluir en las clásulas SELECT, WHERE y ORDER BY.

Pueden anidarse funciones dentro de funciones. Y existe una gran variedad de funciones para cada tipo de datos:

Funciones Aritméticas
Función Descripción
ABS(n) Valor absoluto de n.
CEIL(n) Entero inmediatamente superior o igual a n.
FLOOR(n) Entero inmediatamante inferior o igual a n.
MOD(m, n) Resto de la división de m por n
NVL(val,exp) Devuelve la expresión exp si val es NULL, y val en otro caso.
POWER(m, n) Calcula la potencia n-esima de m.
ROUND(m, n)

Calcula el redondeo de m a n decimales.

Si n < 0 el redondeo se efectua por la izquierda del punto decimal.

SIGN(n) Calcula el signo de n, devolviendo -1 si n < 0, 0 si n = 0 y 1 si n > 0.
SQRT(n) Raíz cuadrada de n.
TRUNC(m,n) Calcula m truncado a n decimales (n puede ser negativo).

DUAL es una tabla virtual de la BD, que puede ser usada para inspeccionar estas funciones.

SELECT ABS(-15.7)            AS Abs,
       CEIL(15.7)            AS Piso,
       FLOOR(15.7)           AS Techo,
       MOD(11,4)             AS Modulo,
       POWER(3,2)            AS Potencia,
       ROUND(123.45678,2)    AS Round,
       TRUNC(123.45678,2)    AS Truncar,
       SIGN(-12)             AS Sign,
       SQRT(16)              AS Sqrt
FROM dual;

Funciones de cadenas de carácteres

Atención: Usar una función de conversión dentro de la cláusula WHERE puede ser altamente ineficiente porque si la columna afectada forma parte de un índice éste lo desactiva, provocando un bajo rendimiento.

Función Descripción
ASCII(cadena) Devuelve el valor ASCII de cadena.
CONCAT(c1,c2) Concatenada c1 con c2. Es esquivalente al operador ||.
INITCAP(cadena) Regresa cadena con el primer carácter en mayúsculas.
LENGTH(cadena) Devuelve la longitud de cadena.
LOWER(cadena) Retorna la cadena con todas sus letras en minúsculas.
LPAD(c1,n,c2) Devuelve c1 con longitud n, y ajustada a la derecha, rellenando por la izquierda con c2.
REPLACE(c1,c2,c3) Devuelve c1 en la que cada ocurrencia de la cadena c2 ha sido sustituida por la cadena c3.
RPAD(c1,n,c2) Devuelve c1 con longitud n, y ajustada a la izquierda, rellenando por la derecha con c2.
RTRIM(c1[,n]) c1 es una cadena que se desea compactar por la derecha. n son los carácteres individuales que se eliminaran del lado derecho. Si se omite n eliminara todos los espacios en blanco
SUBSTR(c1,m,n) Devuelve la sudcadena de c1 compuesta por n carácteres a partir de la posicion m.
TRANSLATE(c1,c2,c3) Convierte caracteres de una cadena en caracteres diferentes, según un plan de sustitución marcado por el usuario.
UPPER(cadena) Convierte la cadena con todas sus letras en mayúsculas.

SELECT ASCII('N')                 ASCII,
       INITCAP('PEDRO')           INITCAP,
       LENGTH('cadena')           LENGTH,
       LOWER('MinUsCulAs')        LOWER,
       LPAD('P',5,'*')            LPAD,
       REPLACE('Digo', 'i', 'ie') REPLACE,
       RPAD('P',5,'*')            RPAD,
       SUBSTR('ABCDEFG',3,2)      SUBSTR, 
       UPPER('maYuSCulAs')        Mayusculas
FROM dual;


Funciones Oracle de Manejo de Fechas: Funciones probadas el 27 de Junio de 2009

Oracle almacena fechas en un formato numérico interno de 7 bytes: Siglo, año, mes, día, horas, minutos, segundos

El formato de fecha por defecto es DD-MON-YYYY

SYSDATE es una función que devuelve fecha y hora (pseudocolumna del sistema)

Operadores aritméticos de fechas

Descripción
Función
ADD_MONTHS(fecha, n) Suma n meses a la fecha
EXTRACT

Extrae un valor de fecha o de intervalo de tiempo.

EXTRACT ( { YEAR | MONTH | DAY | HOUR | MINUTE | SECOND } | { TIMEZONE_HOUR | TIMEZONE_MINUTE } | { TIMEZONE_REGION | TIMEZONE_ABBR } FROM { date_value | interval_value } )

LAST_DAY(d) Fecha del último día del mes d.
MONTHS_BETWEEN(d1, d2) Diferencia en meses entre las fechas d1 y d2.
NEXT_DAY(d, cad) Próxima fecha para el día de la semana cad (Domingo, lunes...) después de la fecha d.
SYSDATE Fecha actual.
SYSTIMESTAMP Fecha y ahora actual

SELECT SYSDATE Fecha,
       SYSTIMESTAMP FechaHora,
       ADD_MONTHS(SYSDATE, 5) NuevaFecha,
       EXTRACT( YEAR FROM SYSDATE) Year,
       LAST_DAY(SYSDATE) UltimoDia,
       MONTHS_BETWEEN(SYSDATE, '08/22/2017') Dias,
      NEXT_DAY(SYSDATE, 'Monday') Lunes
FROM dual;


Funciones de Conversión de Tipo

Función Descripción
TO_NUMBER(cad, [fmto]) Convierte la cadena cad a un número, opcionalmente de acuerdo con el formato fmto. El formato es opcional
TO_CHAR(d [, fmto]) Convierte la fecha d a una cadena de carácteres, opcionalmente de acuerdo con el formato fmto.
TO_DATE(cad, fmto) Convierte la cadena cad de tipo varchar2 a fecha, opcionalmente de acuerdo con el formato fmto.

Con las fechas pueden utilizarse varios formatos. Estos formatos permiten modificar la presentación de una fecha. En la siguiente tabla se presentan algunos formatos de fecha y el resultado que generan.

Máscaras de Formato Numéricas: Funciones probadas el 28 de junio de 2009: 7:55 a.m.

Formato Descripción
cc ó scc Valor del siglo.
y, yyy ó sy, yyy Año con coma, con o sin signo.
yyyy ó yyy ó yy ó y Año sin signo con cuatro, tres, dos o un dígitos.
q Trimestre.
ww ó w Número de la semana del año o del mes.
mm Número del mes.
ddd ó dd ó d Número del día del año, del mes o de la semana.
hh ó hh12 ó hh24 La hora en formato 12h. o 24h.
mi Los minutos de la hora.
ss ó sssss Los segundos dentro del minuto, o desde las 0 horas.
syear ó year Año en Inglés
month o mon Nombre del mes o su abreviatura de tres letras.
day ó dy Nombre del día de la semana o su abreviatura de tres letras.
a.m. ó p.m. El espacio del día.
b.c. ó a.d. Indicador del año respecto al del nacimiento de Cristo.