Skip to content

9. Funciones Oracle

Las funciones en Oracle Database son bloques de código predefinidos que realizan operaciones específicas y devuelven un valor. Estas funciones son herramientas esenciales para manipular datos, realizar cálculos y transformar información dentro de consultas SQL. Oracle proporciona una amplia variedad de funciones integradas que facilitan el procesamiento de diferentes tipos de datos.

  • Funciones de cadena: Manipulan datos de tipo texto
  • Funciones numéricas: Realizan operaciones matemáticas
  • Funciones de fecha y hora: Manipulan y calculan valores temporales
  • Funciones de conversión: Transforman datos de un tipo a otro
  • Funciones de grupo: Realizan cálculos sobre conjuntos de filas
  • Funciones analíticas: Realizan cálculos sobre particiones de datos

Las funciones de cadena en Oracle permiten manipular datos de tipo VARCHAR2, CHAR, CLOB, NVARCHAR2, NCHAR y NCLOB.

Estas funciones modifican la capitalización de las cadenas de texto.

-- UPPER: Convierte una cadena a mayúsculas
SELECT UPPER('Oracle Database') FROM dual;
-- Resultado: ORACLE DATABASE
-- LOWER: Convierte una cadena a minúsculas
SELECT LOWER('Oracle DATABASE') FROM dual;
-- Resultado: oracle database
-- INITCAP: Convierte la primera letra de cada palabra a mayúscula
SELECT INITCAP('oracle database') FROM dual;
-- Resultado: Oracle Database

Extrae una subcadena de una cadena dada, especificando la posición de inicio y opcionalmente la longitud.

-- Sintaxis: SUBSTR(cadena, posición_inicio [, longitud])
-- Extraer desde la posición 8, 8 caracteres
SELECT SUBSTR('Oracle Database', 8, 8) FROM dual;
-- Resultado: Database
-- Extraer desde la posición 1, 6 caracteres
SELECT SUBSTR('Oracle Database', 1, 6) FROM dual;
-- Resultado: Oracle
-- Posición negativa (cuenta desde el final)
SELECT SUBSTR('Oracle Database', -8) FROM dual;
-- Resultado: Database

Devuelve el número de caracteres en una cadena.

-- Contar caracteres en una cadena
SELECT LENGTH('Oracle Database') FROM dual;
-- Resultado: 15
-- Uso en una consulta
SELECT nombre, LENGTH(nombre) AS longitud
FROM empleados
WHERE LENGTH(nombre) > 10;

Estas funciones eliminan caracteres específicos (por defecto espacios) del inicio, final o ambos extremos de una cadena.

-- Eliminar espacios de ambos extremos
SELECT TRIM(' Oracle Database ') FROM dual;
-- Resultado: "Oracle Database"
-- Eliminar un carácter específico
SELECT TRIM('O' FROM 'Oracle Database') FROM dual;
-- Resultado: "racle Database"
-- Especificar posición (LEADING, TRAILING, BOTH)
SELECT TRIM(LEADING 'O' FROM 'Oracle Database') FROM dual;
-- Resultado: "racle Database"
SELECT TRIM(TRAILING 'e' FROM 'Oracle Database') FROM dual;
-- Resultado: "Oracle Databas"

Concatena dos cadenas. Es equivalente al operador ||.

-- Concatenar dos cadenas
SELECT CONCAT('Oracle ', 'Database') FROM dual;
-- Resultado: "Oracle Database"
-- Equivalente usando el operador ||
SELECT 'Oracle ' || 'Database' FROM dual;
-- Resultado: "Oracle Database"
-- Concatenar múltiples cadenas
SELECT 'Oracle ' || 'Database ' || 'Version ' || '19c' FROM dual;
-- Resultado: "Oracle Database Version 19c"

Busca la posición de una subcadena dentro de una cadena.

-- Sintaxis: INSTR(cadena, subcadena [, posición_inicio [, ocurrencia]])
-- Buscar la primera ocurrencia
SELECT INSTR('Oracle Database Oracle', 'Oracle') FROM dual;
-- Resultado: 1
-- Buscar desde una posición específica
SELECT INSTR('Oracle Database Oracle', 'Oracle', 2) FROM dual;
-- Resultado: 17
-- Buscar la segunda ocurrencia
SELECT INSTR('Oracle Database Oracle', 'Oracle', 1, 2) FROM dual;
-- Resultado: 17
FunciónDescripciónEjemplo
REPLACEReemplaza todas las ocurrencias de una subcadenaREPLACE('Oracle DB', 'DB', 'Database')
LPADRellena por la izquierda hasta una longitudLPAD('Oracle', 10, '*')
RPADRellena por la derecha hasta una longitudRPAD('Oracle', 10, '*')
TRANSLATEReemplaza caracteres individualesTRANSLATE('Oracle', 'Oa', '0@')
ASCIIDevuelve el código ASCII de un carácterASCII('A')
CHRDevuelve el carácter de un código ASCIICHR(65)

Las funciones numéricas en Oracle permiten realizar operaciones matemáticas sobre valores numéricos.

Estas funciones redondean o truncan números a un número específico de decimales.

-- Redondear a 2 decimales
SELECT ROUND(123.456, 2) FROM dual;
-- Resultado: 123.46
-- Redondear a 0 decimales
SELECT ROUND(123.567) FROM dual;
-- Resultado: 124
-- Redondear a negativo (unidades superiores)
SELECT ROUND(123.567, -1) FROM dual;
-- Resultado: 120
SELECT ROUND(123.567, -2) FROM dual;
-- Resultado: 100

Devuelve el resto de una división.

-- Sintaxis: MOD(dividendo, divisor)
SELECT MOD(10, 3) FROM dual;
-- Resultado: 1
-- Verificar si un número es par o impar
SELECT
numero,
CASE WHEN MOD(numero, 2) = 0 THEN 'Par' ELSE 'Impar' END AS tipo
FROM numeros;

Calculan potencias y raíces cuadradas.

-- POWER: Eleva un número a una potencia
SELECT POWER(2, 3) FROM dual;
-- Resultado: 8
-- SQRT: Calcula la raíz cuadrada
SELECT SQRT(16) FROM dual;
-- Resultado: 4

Devuelve el valor absoluto de un número.

SELECT ABS(-15.5) FROM dual;
-- Resultado: 15.5
SELECT ABS(15.5) FROM dual;
-- Resultado: 15.5

Redondean hacia arriba o hacia abajo al entero más cercano.

-- CEIL: Redondea hacia arriba
SELECT CEIL(15.1) FROM dual;
-- Resultado: 16
SELECT CEIL(-15.1) FROM dual;
-- Resultado: -15
-- FLOOR: Redondea hacia abajo
SELECT FLOOR(15.9) FROM dual;
-- Resultado: 15
SELECT FLOOR(-15.9) FROM dual;
-- Resultado: -16
FunciónDescripciónEjemplo
SIGNDevuelve el signo de un número (-1, 0, 1)SIGN(-10)
EXPDevuelve e elevado a una potenciaEXP(1)
LNLogaritmo naturalLN(10)
LOGLogaritmo en una base específicaLOG(10, 100)
SIN, COS, TANFunciones trigonométricasSIN(0)
ASIN, ACOS, ATANFunciones trigonométricas inversasASIN(0)

Las funciones de fecha y hora en Oracle permiten manipular valores temporales, realizar cálculos con fechas y extraer componentes específicos.

Devuelven la fecha y hora actuales del sistema.

-- SYSDATE: Fecha y hora actuales del servidor de base de datos
SELECT SYSDATE FROM dual;
-- Resultado: 18-AGO-25 (formato depende de NLS_DATE_FORMAT)
-- CURRENT_DATE: Fecha y hora actuales en la zona horaria de la sesión
SELECT CURRENT_DATE FROM dual;
-- Mostrar con formato
SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') AS fecha_actual FROM dual;

Añade un número específico de meses a una fecha.

-- Añadir 3 meses a la fecha actual
SELECT ADD_MONTHS(SYSDATE, 3) FROM dual;
-- Restar 2 meses (usando número negativo)
SELECT ADD_MONTHS(SYSDATE, -2) FROM dual;
-- Calcular la fecha de vencimiento de un contrato
SELECT
fecha_inicio,
ADD_MONTHS(fecha_inicio, duracion_meses) AS fecha_vencimiento
FROM contratos;

Calcula el número de meses entre dos fechas.

-- Calcular meses entre dos fechas
SELECT MONTHS_BETWEEN(
TO_DATE('15-08-2025', 'DD-MM-YYYY'),
TO_DATE('15-02-2025', 'DD-MM-YYYY')
) FROM dual;
-- Resultado: 6
-- Calcular antigüedad de empleados en meses
SELECT
nombre,
fecha_contratacion,
ROUND(MONTHS_BETWEEN(SYSDATE, fecha_contratacion), 0) AS meses_antiguedad
FROM empleados;

Devuelve el último día del mes para una fecha dada.

-- Último día del mes actual
SELECT LAST_DAY(SYSDATE) FROM dual;
-- Último día de febrero de 2024 (año bisiesto)
SELECT LAST_DAY(TO_DATE('01-02-2024', 'DD-MM-YYYY')) FROM dual;
-- Resultado: 29-FEB-24
-- Último día de febrero de 2025 (año no bisiesto)
SELECT LAST_DAY(TO_DATE('01-02-2025', 'DD-MM-YYYY')) FROM dual;
-- Resultado: 28-FEB-25

Devuelve la primera fecha posterior a una fecha dada que corresponde a un día de la semana específico.

-- Próximo lunes desde la fecha actual
SELECT NEXT_DAY(SYSDATE, 'LUNES') FROM dual;
-- Próximo viernes desde una fecha específica
SELECT NEXT_DAY(TO_DATE('15-08-2025', 'DD-MM-YYYY'), 'VIERNES') FROM dual;
-- Nota: El nombre del día debe estar en el idioma configurado en NLS_DATE_LANGUAGE

Extrae componentes específicos (año, mes, día, etc.) de una fecha.

-- Extraer el año de la fecha actual
SELECT EXTRACT(YEAR FROM SYSDATE) FROM dual;
-- Extraer el mes
SELECT EXTRACT(MONTH FROM SYSDATE) FROM dual;
-- Extraer el día
SELECT EXTRACT(DAY FROM SYSDATE) FROM dual;
-- Extraer la hora
SELECT EXTRACT(HOUR FROM SYSTIMESTAMP) FROM dual;
-- Extraer el minuto
SELECT EXTRACT(MINUTE FROM SYSTIMESTAMP) FROM dual;

Oracle permite realizar operaciones aritméticas directamente con fechas.

-- Sumar días a una fecha
SELECT SYSDATE + 7 AS una_semana_despues FROM dual;
-- Restar días a una fecha
SELECT SYSDATE - 7 AS una_semana_antes FROM dual;
-- Calcular la diferencia en días entre dos fechas
SELECT
TO_DATE('31-12-2025', 'DD-MM-YYYY') - TO_DATE('01-01-2025', 'DD-MM-YYYY')
AS dias_en_2025
FROM dual;
-- Resultado: 364

Oracle proporciona funciones para trabajar con intervalos de tiempo.

-- Crear un intervalo de días
SELECT INTERVAL '30' DAY AS un_mes FROM dual;
-- Sumar un intervalo a una fecha
SELECT SYSDATE + INTERVAL '12' HOUR AS doce_horas_despues FROM dual;
-- Intervalos complejos
SELECT SYSDATE + INTERVAL '2 12:30:45' DAY TO SECOND AS futuro FROM dual;
FunciónDescripciónEjemplo
TRUNC(date)Trunca una fecha a medianocheTRUNC(SYSDATE)
ROUND(date)Redondea una fecha al día más cercanoROUND(SYSDATE)
SYSTIMESTAMPFecha y hora con zona horaria y fracción de segundoSYSTIMESTAMP
NEW_TIMEConvierte hora entre zonas horariasNEW_TIME(SYSDATE, 'EST', 'PST')
TO_YMINTERVALCrea un intervalo de año a mesTO_YMINTERVAL('01-06')
TO_DSINTERVALCrea un intervalo de día a segundoTO_DSINTERVAL('2 12:30:00')

Las funciones de conversión en Oracle permiten transformar datos de un tipo a otro, facilitando operaciones entre diferentes tipos de datos.

Convierte valores de fecha, numéricos o de intervalo a cadenas de caracteres con formato.

-- Convertir fecha a texto con formato
SELECT TO_CHAR(SYSDATE, 'DD-MM-YYYY HH24:MI:SS') FROM dual;
-- Resultado: 18-08-2025 20:44:23
-- Formato con nombres de mes y día
SELECT TO_CHAR(SYSDATE, 'Day, DD "de" Month "de" YYYY') FROM dual;
-- Resultado: Lunes, 18 de Agosto de 2025
-- Formato con trimestre y semana del año
SELECT TO_CHAR(SYSDATE, 'Q "Trimestre", WW "Semana"') FROM dual;

Convierte una cadena de caracteres a un valor de fecha.

-- Convertir texto a fecha
SELECT TO_DATE('18-08-2025', 'DD-MM-YYYY') FROM dual;
-- Convertir texto con formato complejo
SELECT TO_DATE('18 de Agosto, 2025', 'DD "de" Month, YYYY') FROM dual;
-- Convertir texto con hora
SELECT TO_DATE('18-08-2025 14:30:00', 'DD-MM-YYYY HH24:MI:SS') FROM dual;
-- Uso en consultas
SELECT *
FROM pedidos
WHERE fecha_pedido BETWEEN
TO_DATE('01-01-2025', 'DD-MM-YYYY') AND
TO_DATE('31-12-2025', 'DD-MM-YYYY');

Convierte una cadena de caracteres a un valor numérico.

-- Convertir texto a número
SELECT TO_NUMBER('1234.56') FROM dual;
-- Convertir texto con formato
SELECT TO_NUMBER('1,234.56', '9,999.99') FROM dual;
-- Convertir texto con símbolo de moneda
SELECT TO_NUMBER('$1,234.56', '$9,999.99') FROM dual;
-- Uso en consultas
SELECT *
FROM productos
WHERE precio > TO_NUMBER('1,000.00', '9,999.99');

Convierte un valor de un tipo de datos a otro.

-- Convertir número a cadena
SELECT CAST(1234.56 AS VARCHAR2(10)) FROM dual;
-- Convertir cadena a número
SELECT CAST('1234.56' AS NUMBER(10,2)) FROM dual;
-- Convertir cadena a fecha
SELECT CAST('18-AUG-25' AS DATE) FROM dual;
-- Convertir entre tipos de datos complejos
SELECT CAST(fecha_nacimiento AS TIMESTAMP) FROM empleados;

Estas funciones ayudan a manejar valores NULL en las consultas.

-- Reemplazar NULL por un valor predeterminado
SELECT
nombre,
NVL(telefono, 'Sin teléfono') AS telefono_contacto
FROM clientes;
-- Con valores numéricos
SELECT
producto,
NVL(precio, 0) AS precio_final
FROM productos;
FunciónDescripciónEjemplo
TO_TIMESTAMPConvierte texto a timestampTO_TIMESTAMP('18-08-2025 14:30:00.000', 'DD-MM-YYYY HH24:MI:SS.FF')
TO_CLOBConvierte a tipo CLOBTO_CLOB(texto_largo)
HEXTORAWConvierte texto hexadecimal a RAWHEXTORAW('7F')
RAWTOHEXConvierte RAW a texto hexadecimalRAWTOHEX(columna_raw)
BIN_TO_NUMConvierte bits a númeroBIN_TO_NUM(1,0,1)
CONVERTConvierte entre conjuntos de caracteresCONVERT(texto, 'UTF8', 'AL32UTF8')

Las funciones de grupo en Oracle realizan cálculos sobre conjuntos de filas y devuelven un único resultado por grupo. Se utilizan comúnmente con la cláusula GROUP BY.

Calculan la suma y el promedio de valores numéricos.

-- Calcular la suma total de ventas
SELECT SUM(monto_venta) AS total_ventas FROM ventas;
-- Calcular el promedio de ventas
SELECT AVG(monto_venta) AS promedio_ventas FROM ventas;
-- Agrupar por departamento
SELECT
departamento,
SUM(salario) AS total_salarios,
AVG(salario) AS salario_promedio
FROM empleados
GROUP BY departamento;

Encuentran el valor mínimo y máximo en un conjunto de datos.

-- Encontrar el precio mínimo y máximo
SELECT
MIN(precio) AS precio_minimo,
MAX(precio) AS precio_maximo
FROM productos;
-- Agrupar por categoría
SELECT
categoria,
MIN(precio) AS precio_minimo,
MAX(precio) AS precio_maximo
FROM productos
GROUP BY categoria;
-- También funcionan con fechas y texto
SELECT
MIN(fecha_contratacion) AS primera_contratacion,
MAX(fecha_contratacion) AS ultima_contratacion
FROM empleados;

Cuenta el número de filas o valores no nulos.

-- Contar todas las filas
SELECT COUNT(*) AS total_empleados FROM empleados;
-- Contar valores no nulos en una columna
SELECT COUNT(telefono) AS empleados_con_telefono FROM empleados;
-- Contar valores distintos
SELECT COUNT(DISTINCT departamento) AS numero_departamentos FROM empleados;
-- Agrupar por departamento
SELECT
departamento,
COUNT(*) AS numero_empleados
FROM empleados
GROUP BY departamento;

Concatena valores de varias filas en una sola cadena.

-- Concatenar nombres de empleados por departamento
SELECT
departamento,
LISTAGG(nombre, ', ') WITHIN GROUP (ORDER BY nombre) AS empleados
FROM empleados
GROUP BY departamento;
-- Con límite y desbordamiento
SELECT
departamento,
LISTAGG(nombre, ', ' ON OVERFLOW TRUNCATE '...')
WITHIN GROUP (ORDER BY nombre) AS empleados
FROM empleados
GROUP BY departamento;

La cláusula HAVING se utiliza para filtrar resultados basados en funciones de grupo.

-- Filtrar departamentos con más de 10 empleados
SELECT
departamento,
COUNT(*) AS numero_empleados
FROM empleados
GROUP BY departamento
HAVING COUNT(*) > 10;
-- Filtrar categorías con precio promedio superior a 100
SELECT
categoria,
AVG(precio) AS precio_promedio
FROM productos
GROUP BY categoria
HAVING AVG(precio) > 100;
FunciónDescripciónEjemplo
STDDEVDesviación estándarSTDDEV(salario)
VARIANCEVarianzaVARIANCE(salario)
MEDIANMedianaMEDIAN(salario)
PERCENTILE_CONTPercentil continuoPERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salario)
PERCENTILE_DISCPercentil discretoPERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY salario)
GROUP_IDIdentifica filas duplicadas en agrupacionesGROUP_ID()

Las funciones analíticas realizan cálculos sobre un conjunto de filas relacionadas con la fila actual, sin agrupar las filas en un único resultado como lo hacen las funciones de grupo.

Estas funciones asignan números de clasificación a las filas.

-- Clasificar empleados por salario
SELECT
nombre,
departamento,
salario,
RANK() OVER (ORDER BY salario DESC) AS ranking_general,
RANK() OVER (PARTITION BY departamento ORDER BY salario DESC) AS ranking_departamento
FROM empleados;
-- RANK deja huecos en la numeración cuando hay empates
-- Ejemplo: 1, 2, 2, 4, 5, ...

ROWNUM es una pseudocolumna que asigna números secuenciales a las filas devueltas por una consulta, mientras que ROW_NUMBER() es una función analítica más flexible.

-- ROWNUM: Asigna números antes de ordenar
SELECT
ROWNUM,
nombre,
salario
FROM empleados
WHERE ROWNUM <= 5;
-- ROW_NUMBER: Permite ordenar primero y luego asignar números
SELECT * FROM (
SELECT
ROW_NUMBER() OVER (ORDER BY salario DESC) AS rn,
nombre,
salario
FROM empleados
)
WHERE rn <= 5;

Las funciones de ventana realizan cálculos sobre un conjunto de filas relacionadas con la fila actual.

-- Calcular suma acumulada
SELECT
fecha,
monto,
SUM(monto) OVER (ORDER BY fecha) AS suma_acumulada
FROM ventas;
-- Calcular promedio móvil de 3 días
SELECT
fecha,
monto,
AVG(monto) OVER (ORDER BY fecha ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS promedio_movil
FROM ventas;
-- Calcular porcentaje del total
SELECT
departamento,
salario,
salario / SUM(salario) OVER () * 100 AS porcentaje_total,
salario / SUM(salario) OVER (PARTITION BY departamento) * 100 AS porcentaje_departamento
FROM empleados;

Acceden a datos de filas anteriores o posteriores sin necesidad de auto-uniones.

-- Comparar con el valor anterior
SELECT
fecha,
monto,
LAG(monto) OVER (ORDER BY fecha) AS monto_anterior,
monto - LAG(monto) OVER (ORDER BY fecha) AS diferencia
FROM ventas;
-- Comparar con el valor siguiente
SELECT
fecha,
monto,
LEAD(monto) OVER (ORDER BY fecha) AS monto_siguiente,
monto - LEAD(monto) OVER (ORDER BY fecha) AS diferencia
FROM ventas;
-- Especificar un valor predeterminado para la primera/última fila
SELECT
fecha,
monto,
LAG(monto, 1, 0) OVER (ORDER BY fecha) AS monto_anterior
FROM ventas;
FunciónDescripciónEjemplo
FIRST_VALUEPrimer valor de la ventanaFIRST_VALUE(salario) OVER (...)
LAST_VALUEÚltimo valor de la ventanaLAST_VALUE(salario) OVER (...)
NTH_VALUEEnésimo valor de la ventanaNTH_VALUE(salario, 2) OVER (...)
NTILEDivide las filas en grupos igualesNTILE(4) OVER (ORDER BY salario)
CUME_DISTDistribución acumulativaCUME_DIST() OVER (ORDER BY salario)
PERCENT_RANKRango porcentualPERCENT_RANK() OVER (ORDER BY salario)

Las funciones de Oracle Database son herramientas poderosas que permiten manipular y transformar datos de manera eficiente. Dominar estas funciones es esencial para escribir consultas SQL efectivas y expresivas.

  • Funciones de cadena: Manipulan y transforman datos de texto.
  • Funciones numéricas: Realizan operaciones matemáticas y transformaciones.
  • Funciones de fecha y hora: Manipulan valores temporales y realizan cálculos con fechas.
  • Funciones de conversión: Transforman datos entre diferentes tipos.
  • Funciones de grupo: Realizan cálculos sobre conjuntos de filas.
  • Funciones analíticas: Realizan cálculos sobre conjuntos de filas relacionadas con la fila actual.
  1. Elija la función adecuada: Asegúrese de seleccionar la función que mejor se adapte a su necesidad específica.
  2. Considere el rendimiento: Algunas funciones pueden ser más costosas en términos de rendimiento. Evalúe alternativas si es necesario.
  3. Pruebe con datos de ejemplo: Verifique el comportamiento de las funciones con datos de prueba antes de implementarlas en producción.
  4. Documente el uso de funciones complejas: Especialmente para funciones analíticas o combinaciones complejas de funciones.
  5. Mantenga la compatibilidad: Tenga en cuenta la versión de Oracle Database al utilizar funciones más recientes.
🐝