Módulo 32: Funciones de una fila
Objetivos del módulo
- Distinguir entre funciones de una fila (escalares) y funciones de grupo (de agregado)
- Manipular texto con las funciones de caracteres, incluidas las expresiones regulares (
REGEXP_LIKE,REGEXP_SUBSTR,REGEXP_REPLACE,REGEXP_COUNT) - Redondear, truncar y operar con las funciones numéricas
- Trabajar con fechas: fecha actual, aritmética de fechas,
ADD_MONTHS,MONTHS_BETWEEN,NEXT_DAY,LAST_DAY,EXTRACT, redondeo y truncado de fechas, cálculo de edades y antigüedades - Convertir entre tipos con
TO_CHAR,TO_DATE,TO_NUMBERyCAST, dominar las máscaras de formato y conocer los peligros de la conversión implícita - Tratar los nulos con
NVL,NVL2,NULLIFyCOALESCE - Escribir lógica condicional con
DECODEyCASE(simple y buscado) - Usar
GREATEST,LEAST,USERySYS_CONTEXT, y anidar funciones - Resolver 40 ejercicios sobre el esquema EMPRESA
1. Funciones de una fila y funciones de grupo
En el módulo anterior calculaste expresiones como salario * 14. Las funciones amplían enormemente lo que puedes hacer en una expresión: pasar un texto a mayúsculas, redondear un precio, sumar meses a una fecha, sustituir un nulo por un cero…
📘 Concepto: Una función de una fila (single-row function, también llamada función escalar) recibe valores de una fila y devuelve un valor por cada fila. Una función de grupo o de agregado (aggregate function) recibe los valores de muchas filas y devuelve un único valor para todas ellas.
flowchart LR
subgraph Una fila
A1["fila 1: 'ana'"] --> F1["UPPER()"] --> R1["'ANA'"]
A2["fila 2: 'luis'"] --> F2["UPPER()"] --> R2["'LUIS'"]
A3["fila 3: 'eva'"] --> F3["UPPER()"] --> R3["'EVA'"]
end
subgraph De grupo
B1["fila 1: 1000"] --> G["SUM()"]
B2["fila 2: 2000"] --> G
B3["fila 3: 3000"] --> G
G --> S["6000"]
end
| Característica | Funciones de una fila | Funciones de grupo |
|---|---|---|
| Entrada | Valores de una fila | Valores de un conjunto de filas |
| Salida | Un valor por fila | Un valor por grupo |
| Ejemplos | UPPER, ROUND, ADD_MONTHS, NVL, TO_CHAR | COUNT, SUM, AVG, MAX, MIN |
| Dónde se usan | SELECT, WHERE, ORDER BY, GROUP BY, HAVING… | SELECT, HAVING, ORDER BY (nunca en WHERE) |
| Módulo | Este | 25 |
SELECT nombre, UPPER(nombre) AS mayus, LENGTH(nombre) AS letras
FROM empleados
WHERE dept_id = 40;
NOMBRE MAYUS LETRAS
------ ------ ----------
Pedro PEDRO 5
Isabel ISABEL 6
Jorge JORGE 5
3 filas seleccionadas.
Tres filas de entrada, tres filas de salida: cada llamada a UPPER y LENGTH trabaja con el nombre de su fila. Las funciones de una fila pueden aparecer en cualquier parte de la consulta donde vaya una expresión, también en WHERE y ORDER BY:
SELECT nombre, apellidos
FROM empleados
WHERE LENGTH(nombre) > 6
ORDER BY LENGTH(nombre) DESC, nombre;
NOMBRE APELLIDOS
-------- -------------
Cristina Medina Blanco
1 fila seleccionada.
Según el tipo de dato con el que trabajan, Oracle clasifica las funciones de una fila en familias: de caracteres, numéricas, de fecha, de conversión, de tratamiento de nulos, condicionales y otras. Este módulo las recorre todas.
💡 Consejo: Las salidas de este módulo se han obtenido con la configuración de sesión del módulo 27:
NLS_LANGUAGE = 'SPANISH',NLS_TERRITORY = 'SPAIN',NLS_DATE_FORMAT = 'DD/MM/YYYY'ySET NULL '(nulo)'. El idioma y el territorio son especialmente importantes aquí: determinan los nombres de los meses y los días que devuelven las funciones de fecha y los separadores decimales y de miles de las funciones de conversión. Si tu sesión está en inglés verásSaturdayen vez deSábadoy1,234.50en vez de1.234,50.
2. Funciones de caracteres
2.1. Mayúsculas y minúsculas: UPPER, LOWER, INITCAP
| Función | Resultado | Ejemplo |
|---|---|---|
UPPER(c) | Todo en mayúsculas | UPPER('Peña') → PEÑA |
LOWER(c) | Todo en minúsculas | LOWER('VENTAS') → ventas |
INITCAP(c) | Primera letra de cada palabra en mayúscula, el resto en minúscula | INITCAP('ana garcía') → Ana García |
SELECT UPPER(nombre) AS mayusculas,
LOWER(oficio) AS minusculas,
INITCAP(oficio) AS tipo_titulo
FROM empleados
WHERE dept_id = 50;
MAYUSCULAS MINUSCULAS TIPO_TITULO
---------- ---------- -----------
LAURA director Director
NURIA diseñador Diseñador
ÁLVARO analista Analista
3 filas seleccionadas.
INITCAP considera “palabra” todo lo que va tras un carácter que no sea letra ni número, así que también capitaliza tras un guion o un apóstrofo:
SELECT INITCAP('ana garcía-lópez o''brien') AS resultado FROM dual;
RESULTADO
------------------------
Ana García-López O'Brien
1 fila seleccionada.
El uso más importante de UPPER y LOWER es buscar sin distinguir mayúsculas: se aplica la función a la columna y se compara con un literal en el mismo formato.
SELECT cliente_id, nombre
FROM clientes
WHERE UPPER(nombre) LIKE '%ACADEMIA%' OR LOWER(nombre) LIKE '%clínica%';
CLIENTE_ID NOMBRE
---------- ----------------------
1004 Academia Nova
1005 Clínica Dental Sonrisa
2 filas seleccionadas.
⚠️ Importante: Aplicar una función a una columna en el
WHEREimpide que Oracle use un índice normal sobre esa columna, porque el índice guardanombre, noUPPER(nombre). En tablas grandes se crea un índice basado en función (CREATE INDEX ... ON clientes (UPPER(nombre)), módulo 26).
2.2. Longitud: LENGTH
LENGTH(c) devuelve el número de caracteres; LENGTHB(c), el de bytes. En una base de datos UTF-8 (AL32UTF8) las letras con tilde y la ñ ocupan 2 bytes:
SELECT LENGTH('Peña') AS caracteres, LENGTHB('Peña') AS bytes,
LENGTH('') AS longitud_vacia, LENGTH(' ') AS tres_espacios
FROM dual;
CARACTERES BYTES LONGITUD_VACIA TRES_ESPACIOS
---------- ---------- -------------- -------------
4 5 (nulo) 3
1 fila seleccionada.
LENGTH('') es NULL, no 0, porque en Oracle la cadena vacía es NULL (módulo 27).
2.3. Extraer y buscar: SUBSTR e INSTR
SUBSTR(cadena, inicio [, longitud]) devuelve un trozo de la cadena. Las posiciones empiezan en 1 y un inicio negativo cuenta desde el final:
| Llamada | Resultado | Explicación |
|---|---|---|
SUBSTR('Informática', 1, 5) | Infor | 5 caracteres desde el 1.º |
SUBSTR('Informática', 3, 4) | form | 4 caracteres desde el 3.º |
SUBSTR('Informática', 6) | mática | Sin longitud: hasta el final |
SUBSTR('Informática', -4) | tica | Los 4 últimos |
SUBSTR('Informática', -4, 2) | ti | 2 caracteres empezando 4 antes del final |
SUBSTR('Hola', 10) | NULL | El inicio está fuera de la cadena |
INSTR(cadena, buscada [, inicio [, aparición]]) devuelve la posición en la que aparece buscada, o 0 si no está. Con inicio negativo busca de derecha a izquierda:
SELECT INSTR('Construcciones Levante S.A.', 'e') AS primera_e,
INSTR('Construcciones Levante S.A.', 'e', 1, 2) AS segunda_e,
INSTR('Construcciones Levante S.A.', 'e', -1) AS ultima_e,
INSTR('Construcciones Levante S.A.', 'x') AS no_esta,
SUBSTR('Informática', -4, 2) AS trozo
FROM dual;
PRIMERA_E SEGUNDA_E ULTIMA_E NO_ESTA TR
---------- ---------- ---------- ---------- --
13 17 22 0 ti
1 fila seleccionada.
Combinadas, SUBSTR e INSTR permiten “cortar” un texto por un separador. Por ejemplo, separar el usuario y el dominio de un email (todo lo que hay antes y después de la @):
SELECT email,
SUBSTR(email, 1, INSTR(email, '@') - 1) AS usuario,
SUBSTR(email, INSTR(email, '@') + 1) AS dominio
FROM clientes
WHERE cliente_id <= 1005;
EMAIL USUARIO DOMINIO
-------------------------- ---------- -----------------
compras@talleresmartin.es compras talleresmartin.es
info@clevante.es info clevante.es
mjherrera@correo.es mjherrera correo.es
secretaria@academianova.es secretaria academianova.es
admin@sonrisa.es admin sonrisa.es
5 filas seleccionadas.
Y el primer apellido de cada empleado (lo que hay antes del primer espacio):
SELECT apellidos,
SUBSTR(apellidos, 1, INSTR(apellidos, ' ') - 1) AS primer_apellido,
SUBSTR(apellidos, INSTR(apellidos, ' ') + 1) AS segundo_apellido
FROM empleados
WHERE dept_id = 30;
APELLIDOS PRIMER_APELLIDO SEGUNDO_APELLIDO
-------------- --------------- ----------------
Sánchez Gil Sánchez Gil
Moreno Herrera Moreno Herrera
Molina Prieto Molina Prieto
Castro Méndez Castro Méndez
Ortiz Delgado Ortiz Delgado
Rubio Marín Rubio Marín
6 filas seleccionadas.
💡 Consejo: Lee las funciones anidadas de dentro hacia fuera: primero
INSTR(apellidos, ' ')calcula la posición del espacio (por ejemplo, 8 enSánchez Gil), y luegoSUBSTR(apellidos, 1, 8 - 1)toma los 7 primeros caracteres.
2.4. Rellenar: LPAD y RPAD
LPAD(c, n [, relleno]) rellena por la izquierda hasta que la cadena tenga n caracteres; RPAD, por la derecha. Si la cadena ya es más larga, la corta a n caracteres. El relleno por defecto es un espacio.
SELECT LPAD(producto_id, 5, '0') AS codigo,
'PROD-' || LPAD(producto_id, 4, '0') AS referencia,
RPAD(nombre, 18, '.') || precio AS linea_catalogo,
LPAD('*', stock / 10, '*') AS grafico_stock
FROM productos
WHERE categoria_id IN (5, 6);
CODIGO REFERENCIA LINEA_CATALOGO GRAFICO_STOCK
------ ---------- ---------------------- --------------------
00006 PROD-0006 Ratón inalámbrico.19,9 ********************
00007 PROD-0007 Teclado mecánico..79,9 ******
00008 PROD-0008 Pack teclado y rat34,5 (nulo)
00009 PROD-0009 Monitor 24 FHD....139 ***
00010 PROD-0010 Monitor 27 4K.....329 *
5 filas seleccionadas.
El “gráfico” de stock dibuja un asterisco por cada 10 unidades. El Pack teclado y ratón tiene stock 0, así que LPAD('*', 0) devuelve NULL.
2.5. Recortar: TRIM, LTRIM y RTRIM
| Función | Qué quita |
|---|---|
TRIM(c) | Espacios al principio y al final |
TRIM(LEADING 'x' FROM c) | Los caracteres x del principio |
TRIM(TRAILING 'x' FROM c) | Los caracteres x del final |
TRIM(BOTH 'x' FROM c) | Los x de ambos lados (lo mismo que TRIM('x' FROM c)) |
LTRIM(c [, conjunto]) | Cualquier carácter del conjunto por la izquierda |
RTRIM(c [, conjunto]) | Cualquier carácter del conjunto por la derecha |
SELECT '[' || TRIM(' hola ') || ']' AS trim_espacios,
TRIM(LEADING '0' FROM '000120') AS sin_ceros,
TRIM(BOTH '-' FROM '--oferta--') AS sin_guiones,
RTRIM('¡Hola!!!', '!') AS rtrim_ex,
LTRIM('xyxyABCxy', 'xy') AS ltrim_conjunto
FROM dual;
TRIM_E SIN SIN_GU RTRIM_ LTRIM
------ --- ------ ------ -----
[hola] 120 oferta ¡Hola ABCxy
1 fila seleccionada.
📘 Concepto:
TRIMsolo admite un carácter a eliminar;LTRIMyRTRIMadmiten un conjunto de caracteres ('xy'quita cualquierxoyhasta encontrar otro carácter, no la secuencia"xy").
2.6. Sustituir: REPLACE y TRANSLATE
REPLACE(c, buscada [, nueva]) sustituye cada aparición de una subcadena por otra (si se omite nueva, la elimina). TRANSLATE(c, de, a) sustituye carácter a carácter: el 1.º carácter de de por el 1.º de a, el 2.º por el 2.º…
SELECT REPLACE(nombre, 'S.L.', 'Sociedad Limitada') AS replace_ex,
REPLACE(nif, 'B') AS sin_b
FROM clientes
WHERE nombre LIKE '%S.L.%';
REPLACE_EX SIN_B
----------------------------------- --------
Talleres Martín Sociedad Limitada 12345678
Hotel Mar Azul Sociedad Limitada 56789012
Diseños Creativos Sociedad Limitada 89012345
3 filas seleccionadas.
TRANSLATE es la forma clásica de quitar tildes, muy útil para generar identificadores o comparar sin acentos:
SELECT apellidos,
TRANSLATE(apellidos, 'áéíóúÁÉÍÓÚñÑ', 'aeiouAEIOUnN') AS sin_tildes
FROM empleados
WHERE dept_id IN (20, 50);
APELLIDOS SIN_TILDES
---------------- ----------------
Martínez Ruiz Martinez Ruiz
Fernández Moreno Fernandez Moreno
Ruiz Ortega Ruiz Ortega
Torres Romero Torres Romero
Navarro Castro Navarro Castro
Jiménez Vidal Jimenez Vidal
Romero Serrano Romero Serrano
Gil Soto Gil Soto
Peña Cano Pena Cano
9 filas seleccionadas.
💡 Consejo: Si la cadena
aes más corta quede, los caracteres sobrantes dedese eliminan. Así,TRANSLATE(telefono, '0123456789 -', '0123456789')deja solo los dígitos. Ojo: siaesNULLo vacía, el resultado esNULL.
2.7. Concatenar, invertir y códigos de carácter
CONCAT y || ya se vieron en el módulo 27. ASCII(c) devuelve el código numérico del primer carácter y CHR(n), el carácter de un código:
SELECT ASCII('A') AS cod_a, CHR(65) AS letra, ASCII('a') AS cod_a_min,
ASCII('ñ') AS cod_enye, CHR(50097) AS letra_enye
FROM dual;
COD_A L COD_A_MIN COD_ENYE LE
---------- - ---------- ---------- --
65 A 97 50097 ñ
1 fila seleccionada.
En una base de datos UTF-8, ASCII('ñ') no devuelve 241 (su código en Latin-1) sino 50097, que es el valor de sus dos bytes en UTF-8 (C3 B1 en hexadecimal).
⚠️ Importante: Oracle tiene una función
REVERSEque invierte una cadena (REVERSE('Hola')→aloH), pero no está documentada ni soportada para uso general (es interna, pensada para índices inversos) y trabaja con bytes, por lo que estropea los caracteres multibyte. No la uses en código real.
2.8. Expresiones regulares
Cuando LIKE no basta, Oracle ofrece funciones con expresiones regulares POSIX (con extensiones de Perl como \d):
| Función | Qué hace |
|---|---|
REGEXP_LIKE(c, patrón [, opciones]) | Condición: ¿cumple el patrón? (se usa como una condición: en WHERE, CASE WHEN, CHECK…) |
REGEXP_SUBSTR(c, patrón [, inicio [, aparición [, opciones]]]) | Devuelve el trozo que encaja con el patrón |
REGEXP_REPLACE(c, patrón [, reemplazo [, inicio [, aparición [, opciones]]]]) | Sustituye lo que encaja |
REGEXP_COUNT(c, patrón [, inicio [, opciones]]) | Cuenta las apariciones |
REGEXP_INSTR(c, patrón ...) | Posición de la aparición |
Elementos básicos de los patrones:
| Elemento | Significado | Elemento | Significado |
|---|---|---|---|
. | Cualquier carácter | ^ / $ | Principio / final de la cadena |
[abc] | Uno de esos caracteres | [^abc] | Cualquiera excepto esos |
[0-9], \d | Un dígito | [A-Z] | Una mayúscula |
* | 0 o más veces | + | 1 o más veces |
? | 0 o 1 vez | {n} / {n,m} | Exactamente n / entre n y m veces |
(...) | Grupo (referenciable con \1, \2…) | a\|b | a o b |
Las opciones más usadas son 'i' (no distinguir mayúsculas) y 'c' (distinguir, por defecto).
Clientes cuyo NIF es de persona física (8 dígitos y una letra), frente a los CIF de empresa (letra inicial):
SELECT nombre, nif
FROM clientes
WHERE REGEXP_LIKE(nif, '^[0-9]{8}[A-Z]$');
NOMBRE NIF
------------------ ---------
María José Herrera 12345678Z
Antonio Ruiz Pérez 23456789D
Laura Benítez Sanz 34567890V
Carlos Ibáñez Rey 45678901G
4 filas seleccionadas.
Productos cuyo nombre contiene algún número, sin distinguir mayúsculas al buscar “portátil”:
SELECT nombre
FROM productos
WHERE REGEXP_LIKE(nombre, '[0-9]')
AND REGEXP_LIKE(nombre, '^portátil', 'i');
NOMBRE
---------------------
Portátil UltraBook 14
Portátil Gaming 16
Portátil Básico 15
3 filas seleccionadas.
Extraer trozos con REGEXP_SUBSTR (el primer número que aparece en el nombre y la primera palabra, es decir, la primera secuencia de caracteres que no son espacios, \S+) y contar vocales con REGEXP_COUNT:
SELECT nombre,
REGEXP_SUBSTR(nombre, '\d+') AS primer_numero,
REGEXP_SUBSTR(nombre, '^\S+') AS primera_palabra,
REGEXP_COUNT(nombre, '[aeiouáéíóú]', 1, 'i') AS vocales
FROM productos
WHERE categoria_id IN (2, 6);
NOMBRE PRIMER_NUMERO PRIMERA_PALABRA VOCALES
--------------------- ------------- --------------- ----------
Portátil UltraBook 14 14 Portátil 7
Portátil Gaming 16 16 Portátil 5
Portátil Básico 15 15 Portátil 6
Monitor 24 FHD 24 Monitor 3
Monitor 27 4K 27 Monitor 3
5 filas seleccionadas.
Reformatear con REGEXP_REPLACE usando grupos: un teléfono de 9 cifras en bloques de 3, y eliminar todo lo que no sea dígito:
SELECT telefono,
REGEXP_REPLACE(telefono, '(\d{3})(\d{3})(\d{3})', '\1 \2 \3') AS formateado,
REGEXP_REPLACE('Tel: +34 (96) 123-45-67', '[^0-9]') AS solo_digitos
FROM clientes
WHERE cliente_id IN (1001, 1002);
TELEFONO FORMATEADO SOLO_DIGITO
--------- ----------- -----------
961234567 961 234 567 34961234567
963456789 963 456 789 34961234567
2 filas seleccionadas.
💡 Consejo: Las expresiones regulares son potentes pero más lentas que
LIKE,SUBSTRoINSTR. Úsalas cuando el patrón lo requiera de verdad (validaciones, extracción compleja), no para un simple “empieza por”.
2.9. Resumen de funciones de caracteres
| Función | Ejemplo | Resultado |
|---|---|---|
UPPER / LOWER / INITCAP | INITCAP('mesa elevable') | Mesa Elevable |
LENGTH | LENGTH('Peña') | 4 |
SUBSTR | SUBSTR('Valencia', 1, 3) | Val |
INSTR | INSTR('a.b.c', '.', 1, 2) | 4 |
LPAD / RPAD | LPAD('7', 3, '0') | 007 |
TRIM / LTRIM / RTRIM | TRIM(' x ') | x |
REPLACE | REPLACE('S.L.', '.', '') | SL |
TRANSLATE | TRANSLATE('Peña', 'ñ', 'n') | Pena |
CONCAT | CONCAT('a', 'b') | ab |
ASCII / CHR | ASCII('A'), CHR(65) | 65, A |
REGEXP_LIKE | REGEXP_LIKE(nif, '^\d{8}[A-Z]$') | Condición |
REGEXP_SUBSTR | REGEXP_SUBSTR('Monitor 27 4K', '\d+') | 27 |
REGEXP_REPLACE | REGEXP_REPLACE('a1b2', '\d') | ab |
REGEXP_COUNT | REGEXP_COUNT('banana', 'a') | 3 |
3. Funciones numéricas
3.1. Redondear y truncar: ROUND y TRUNC
ROUND(n [, d]) redondea a d decimales (0 por defecto) y TRUNC(n [, d]) corta sin redondear. Con d negativo actúan sobre la parte entera (decenas, centenas…):
SELECT ROUND(1234.5678, 2) AS r2,
ROUND(1234.5678) AS r0,
ROUND(1234.5678, -2) AS r_menos2,
TRUNC(1234.5678, 2) AS t2,
TRUNC(1234.5678) AS t0,
TRUNC(1234.5678, -2) AS t_menos2
FROM dual;
R2 R0 R_MENOS2 T2 T0 T_MENOS2
---------- ---------- ---------- ---------- ---------- ----------
1234,57 1235 1200 1234,56 1234 1200
1 fila seleccionada.
Ejemplo real: precio con IVA del 21 % redondeado al céntimo, y precio “comercial” redondeado a la decena:
SELECT nombre, precio,
precio * 1.21 AS con_iva,
ROUND(precio * 1.21, 2) AS con_iva_redondeado,
ROUND(precio * 1.21, -1) AS a_la_decena,
TRUNC(precio * 1.21) AS euros_enteros
FROM productos
WHERE categoria_id = 5;
NOMBRE PRECIO CON_IVA CON_IVA_REDONDEADO A_LA_DECENA EUROS_ENTEROS
-------------------- ---------- ---------- ------------------ ----------- -------------
Ratón inalámbrico 19,9 24,079 24,08 20 24
Teclado mecánico 79,9 96,679 96,68 100 96
Pack teclado y ratón 34,5 41,745 41,75 40 41
3 filas seleccionadas.
3.2. Hacia arriba y hacia abajo: CEIL y FLOOR
CEIL(n) devuelve el menor entero mayor o igual que n; FLOOR(n), el mayor entero menor o igual. Con negativos se ve la diferencia con ROUND y TRUNC:
SELECT CEIL(2.1) AS ceil_pos, FLOOR(2.9) AS floor_pos,
CEIL(-2.5) AS ceil_neg, FLOOR(-2.5) AS floor_neg,
ROUND(-2.5) AS round_neg, TRUNC(-2.5) AS trunc_neg
FROM dual;
CEIL_POS FLOOR_POS CEIL_NEG FLOOR_NEG ROUND_NEG TRUNC_NEG
---------- ---------- ---------- ---------- ---------- ----------
3 2 -2 -3 -3 -2
1 fila seleccionada.
CEIL es la función de “¿cuántas cajas necesito?”: cajas de 12 unidades para guardar el stock de cada producto:
SELECT nombre, stock, stock / 12 AS division, CEIL(stock / 12) AS cajas_necesarias
FROM productos
WHERE stock BETWEEN 1 AND 30;
NOMBRE STOCK DIVISION CAJAS_NECESARIAS
--------------------- ---------- ---------- ----------------
Portátil UltraBook 14 25 2,08333333 3
Portátil Gaming 16 10 ,833333333 1
Torre Oficina i5 15 1,25 2
Torre Workstation 4 ,333333333 1
Monitor 27 4K 12 1 1
Smartphone Gama Alta 8 ,666666667 1
Silla Ergonómica 20 1,66666667 2
Mesa Elevable 6 ,5 1
Webcam HD 30 2,5 3
Impresora Láser 9 ,75 1
Tablet 10 14 1,16666667 2
11 filas seleccionadas.
3.3. Restos: MOD y REMAINDER
MOD(m, n) devuelve el resto de dividir m entre n con el signo del dividendo (m), calculado como m - n * FLOOR(m/n) para positivos. REMAINDER(m, n) usa ROUND en vez de FLOOR, así que puede dar restos negativos. MOD(m, 0) devuelve m.
SELECT MOD(17, 5) AS mod1, REMAINDER(17, 5) AS rem1,
MOD(18, 5) AS mod2, REMAINDER(18, 5) AS rem2,
MOD(-7, 3) AS mod3, MOD(10, 0) AS mod_cero
FROM dual;
MOD1 REM1 MOD2 REM2 MOD3 MOD_CERO
---------- ---------- ---------- ---------- ---------- ----------
2 2 3 -2 -1 10
1 fila seleccionada.
REMAINDER(18, 5) es -2 porque 18 / 5 = 3,6, que se redondea a 4, y 18 - 5 × 4 = -2. El uso típico de MOD es detectar pares e impares o repartir en turnos:
SELECT pedido_id, MOD(pedido_id, 2) AS resto, MOD(pedido_id, 3) + 1 AS turno
FROM pedidos
WHERE pedido_id BETWEEN 5001 AND 5006;
PEDIDO_ID RESTO TURNO
---------- ---------- ----------
5001 1 1
5002 0 2
5003 1 3
5004 0 1
5005 1 2
5006 0 3
6 filas seleccionadas.
3.4. Otras funciones numéricas
| Función | Descripción | Ejemplo | Resultado |
|---|---|---|---|
ABS(n) | Valor absoluto | ABS(-15) | 15 |
SIGN(n) | Signo: -1, 0 o 1 | SIGN(-15) | -1 |
POWER(m, n) | m elevado a n | POWER(2, 10) | 1024 |
SQRT(n) | Raíz cuadrada | SQRT(144) | 12 |
EXP(n), LN(n), LOG(b, n) | Exponencial, logaritmo neperiano, logaritmo en base b | LOG(10, 1000) | 3 |
SELECT nombre, salario,
salario - 2500 AS diferencia,
ABS(salario - 2500) AS distancia,
SIGN(salario - 2500) AS signo
FROM empleados
WHERE dept_id = 30;
NOMBRE SALARIO DIFERENCIA DISTANCIA SIGNO
------ ---------- ---------- ---------- ----------
Marta 5000 2500 2500 1
Pablo 3500 1000 1000 1
Raquel 2800 300 300 1
Iván 2600 100 100 1
Sara 2400 -100 100 -1
Hugo 2500 0 0 0
6 filas seleccionadas.
SELECT POWER(2, 10) AS potencia, SQRT(144) AS raiz, SQRT(2) AS raiz2,
ROUND(SQRT(2), 4) AS raiz2_4, LOG(10, 1000) AS logaritmo,
POWER(1.03, 5) AS interes_5_anios
FROM dual;
POTENCIA RAIZ RAIZ2 RAIZ2_4 LOGARITMO INTERES_5_ANIOS
---------- ---------- ---------- ---------- ---------- ---------------
1024 12 1,41421356 1,4142 3 1,15927407
1 fila seleccionada.
POWER(1.03, 5) es el factor de crecimiento de un capital al 3 % durante 5 años: un salario de 2000 € que suba un 3 % anual valdrá 2000 * POWER(1.03, 5) dentro de cinco años.
4. Funciones de fecha
4.1. Cómo guarda Oracle las fechas
📘 Concepto: El tipo
DATEde Oracle guarda siempre siglo, año, mes, día, hora, minuto y segundo (7 bytes), aunque el formato de visualización solo muestre el día.TIMESTAMPañade fracciones de segundo yTIMESTAMP WITH TIME ZONE, la zona horaria. Internamente una fecha es un número: por eso se puede sumar y restar.
4.2. La fecha y hora actuales
| Función | Tipo devuelto | Zona horaria |
|---|---|---|
SYSDATE | DATE | La del sistema operativo del servidor |
CURRENT_DATE | DATE | La de la sesión del usuario |
SYSTIMESTAMP | TIMESTAMP WITH TIME ZONE | La del servidor |
CURRENT_TIMESTAMP | TIMESTAMP WITH TIME ZONE | La de la sesión |
LOCALTIMESTAMP | TIMESTAMP | La de la sesión, sin zona |
SELECT SYSDATE,
CURRENT_DATE,
TO_CHAR(SYSDATE, 'DD/MM/YYYY HH24:MI:SS') AS con_hora,
SYSTIMESTAMP
FROM dual;
SYSDATE CURRENT_DA CON_HORA SYSTIMESTAMP
---------- ---------- ------------------- -------------------------------
03/10/2026 03/10/2026 03/10/2026 23:25:13 03/10/26 23:25:13,202873 +00:00
1 fila seleccionada.
El servidor del contenedor está en hora UTC (+00:00) y la sesión también, así que SYSDATE y CURRENT_DATE coinciden. En un servidor en Madrid y un cliente en Canarias diferirían en una hora. Para ver la hora de un DATE hay que usar TO_CHAR con una máscara (apartado 5).
💡 Consejo: Todas las consultas de este módulo que usan
SYSDATEmuestran el resultado del día y la hora en que se ejecutaron (los que ves en la salida anterior). Si las repites, tus resultados serán distintos: es lo esperado.
4.3. Aritmética de fechas
| Operación | Resultado | Ejemplo |
|---|---|---|
fecha + n | Fecha n días después | fecha_pedido + 30 |
fecha - n | Fecha n días antes | SYSDATE - 7 |
fecha + n/24 | Suma n horas | SYSDATE + 2/24 |
fecha - fecha | Número de días entre ambas (con decimales si hay horas) | fecha_envio - fecha_pedido |
fecha + fecha | Error (no tiene sentido) | ORA-00975 |
SELECT pedido_id, fecha_pedido,
fecha_pedido + 30 AS vencimiento,
fecha_pedido - 1 AS dia_anterior,
fecha_envio - fecha_pedido AS dias_envio,
TO_CHAR(fecha_pedido + 36/24, 'DD/MM/YYYY HH24:MI') AS mas_36_horas
FROM pedidos
WHERE pedido_id BETWEEN 5001 AND 5004;
PEDIDO_ID FECHA_PEDI VENCIMIENT DIA_ANTERI DIAS_ENVIO MAS_36_HORAS
---------- ---------- ---------- ---------- ---------- ----------------
5001 15/01/2025 14/02/2025 14/01/2025 2 16/01/2025 12:00
5002 28/01/2025 27/02/2025 27/01/2025 2 29/01/2025 12:00
5003 03/02/2025 05/03/2025 02/02/2025 2 04/02/2025 12:00
5004 20/02/2025 22/03/2025 19/02/2025 4 21/02/2025 12:00
4 filas seleccionadas.
Días transcurridos desde cada pedido pendiente (con SYSDATE hay horas, por eso TRUNC o ROUND):
SELECT pedido_id, fecha_pedido,
SYSDATE - fecha_pedido AS dias_con_decimales,
TRUNC(SYSDATE - fecha_pedido) AS dias_completos
FROM pedidos
WHERE estado = 'PENDIENTE';
PEDIDO_ID FECHA_PEDI DIAS_CON_DECIMALES DIAS_COMPLETOS
---------- ---------- ------------------ --------------
5022 01/07/2026 94,9758449 94
5023 08/07/2026 87,9758449 87
5024 10/07/2026 85,9758449 85
3 filas seleccionadas.
4.4. ADD_MONTHS, MONTHS_BETWEEN, NEXT_DAY y LAST_DAY
| Función | Descripción |
|---|---|
ADD_MONTHS(f, n) | Suma n meses (negativo para restar). Si f es el último día del mes, o el mes destino es más corto, devuelve el último día del mes destino |
MONTHS_BETWEEN(f1, f2) | Meses entre f1 y f2 (positivo si f1 es posterior), con decimales |
NEXT_DAY(f, 'día') | Primer día de la semana indicado posterior a f. El nombre del día va en el idioma de la sesión |
LAST_DAY(f) | Último día del mes de f |
SELECT ADD_MONTHS(DATE '2026-01-15', 1) AS mas_un_mes,
ADD_MONTHS(DATE '2026-01-31', 1) AS fin_enero_mas_1,
ADD_MONTHS(DATE '2026-02-28', 1) AS fin_feb_mas_1,
ADD_MONTHS(DATE '2026-03-31', -12) AS menos_un_anio,
LAST_DAY(DATE '2028-02-10') AS fin_feb_bisiesto
FROM dual;
MAS_UN_MES FIN_ENERO_ FIN_FEB_MA MENOS_UN_A FIN_FEB_BI
---------- ---------- ---------- ---------- ----------
15/02/2026 28/02/2026 31/03/2026 31/03/2025 29/02/2028
1 fila seleccionada.
Observa que ADD_MONTHS del 31 de enero da el 28 de febrero, y que el 28 de febrero (último día del mes) más un mes da el 31 de marzo, no el 28: Oracle mantiene “último día del mes”.
SELECT MONTHS_BETWEEN(DATE '2026-10-03', DATE '2025-01-15') AS meses,
MONTHS_BETWEEN(DATE '2025-01-15', DATE '2026-10-03') AS negativo,
MONTHS_BETWEEN(DATE '2026-03-31', DATE '2026-02-28') AS fin_de_mes,
NEXT_DAY(DATE '2026-10-03', 'LUNES') AS proximo_lunes,
NEXT_DAY(DATE '2026-10-05', 'LUNES') AS lunes_siguiente
FROM dual;
MESES NEGATIVO FIN_DE_MES PROXIMO_LU LUNES_SIGU
---------- ---------- ---------- ---------- ----------
20,6129032 -20,612903 1 05/10/2026 12/10/2026
1 fila seleccionada.
NEXT_DAY es estrictamente posterior: aunque el 5 de octubre de 2026 ya es lunes, devuelve el lunes siguiente (12). Y como el nombre del día depende del idioma de la sesión, con NLS_LANGUAGE = 'SPANISH' hay que escribir 'LUNES':
SELECT NEXT_DAY(DATE '2026-10-03', 'MONDAY') FROM dual;
SELECT NEXT_DAY(DATE '2026-10-03', 'MONDAY') FROM dual
*
ERROR en línea 1:
ORA-01846: not a valid day of the week
Help: https://docs.oracle.com/error-help/db/ora-01846/
⚠️ Importante: El código que usa nombres de días o meses (
NEXT_DAY,TO_DATEconMON) solo funciona con el idioma para el que se escribió. Para código independiente del idioma, usa el tercer argumento NLS deTO_CHAR/TO_DATE(apartado 5.5) o cálculos conTRUNC(f, 'IW'), que siempre devuelve el lunes de la semana ISO.
4.5. EXTRACT
EXTRACT(campo FROM fecha) devuelve un componente numérico: YEAR, MONTH, DAY (y HOUR, MINUTE, SECOND en los TIMESTAMP):
SELECT pedido_id, fecha_pedido,
EXTRACT(YEAR FROM fecha_pedido) AS anio,
EXTRACT(MONTH FROM fecha_pedido) AS mes,
EXTRACT(DAY FROM fecha_pedido) AS dia
FROM pedidos
WHERE EXTRACT(MONTH FROM fecha_pedido) = 3;
PEDIDO_ID FECHA_PEDI ANIO MES DIA
---------- ---------- ---------- ---------- ----------
5005 05/03/2025 2025 3 5
5006 22/03/2025 2025 3 22
5018 03/03/2026 2026 3 3
3 filas seleccionadas.
SELECT EXTRACT(HOUR FROM SYSDATE) FROM dual;
SELECT EXTRACT(HOUR FROM SYSDATE) FROM dual
*
ERROR en línea 1:
ORA-30076: invalid extract field for extract source
Help: https://docs.oracle.com/error-help/db/ora-30076/
Con un DATE solo se pueden extraer año, mes y día; para la hora hay que convertirlo: EXTRACT(HOUR FROM CAST(SYSDATE AS TIMESTAMP)) o, más habitual, TO_CHAR(SYSDATE, 'HH24').
4.6. ROUND y TRUNC con fechas
Sobre fechas, TRUNC(f [, unidad]) lleva la fecha al inicio de la unidad y ROUND(f [, unidad]) a la unidad más cercana. Sin unidad trabajan con el día (quitan la hora).
| Unidad | TRUNC devuelve | ROUND redondea a partir de |
|---|---|---|
'DD' (por defecto) | La medianoche de ese día | Las 12:00 |
'MM' | El día 1 del mes | El día 16 |
'YYYY' | El 1 de enero | El 1 de julio |
'Q' | El primer día del trimestre | El día 16 del segundo mes del trimestre |
'IW' | El lunes de la semana ISO | El jueves |
SELECT TRUNC(DATE '2026-07-16', 'MM') AS trunc_mes,
ROUND(DATE '2026-07-16', 'MM') AS round_mes,
TRUNC(DATE '2026-07-16', 'YYYY') AS trunc_anio,
ROUND(DATE '2026-07-16', 'YYYY') AS round_anio,
TRUNC(DATE '2026-10-03', 'Q') AS trimestre,
TRUNC(DATE '2026-10-03', 'IW') AS lunes_semana
FROM dual;
TRUNC_MES ROUND_MES TRUNC_ANIO ROUND_ANIO TRIMESTRE LUNES_SEMA
---------- ---------- ---------- ---------- ---------- ----------
01/07/2026 01/08/2026 01/01/2026 01/01/2027 01/10/2026 28/09/2026
1 fila seleccionada.
TRUNC(SYSDATE) es la forma estándar de obtener “hoy a las 00:00” para comparar con fechas sin hora: WHERE fecha_pedido = TRUNC(SYSDATE).
4.7. Edad y antigüedad
Calcular años completos entre dos fechas es un clásico. La forma correcta es con MONTHS_BETWEEN dividido entre 12 y truncado; restar fechas y dividir entre 365 falla por los años bisiestos.
SELECT nombre, fecha_alta,
TRUNC(MONTHS_BETWEEN(SYSDATE, fecha_alta) / 12) AS anios_antiguedad,
TRUNC(MOD(MONTHS_BETWEEN(SYSDATE, fecha_alta), 12)) AS meses_extra,
TRUNC(SYSDATE - fecha_alta) AS dias_totales
FROM empleados
WHERE dept_id = 30
ORDER BY fecha_alta;
NOMBRE FECHA_ALTA ANIOS_ANTIGUEDAD MESES_EXTRA DIAS_TOTALES
------ ---------- ---------------- ----------- ------------
Marta 20/06/2011 15 3 5584
Pablo 05/05/2014 12 4 4534
Raquel 01/10/2018 8 0 2924
Hugo 20/11/2019 6 10 2509
Iván 16/03/2020 6 6 2392
Sara 01/06/2022 4 4 1585
6 filas seleccionadas.
Marta lleva 15 años y 3 meses: MONTHS_BETWEEN da los meses totales, la división entera entre 12 los años y MOD(..., 12) los meses sobrantes.
Para la edad de una persona nacida el 29 de febrero de 2008, hoy:
SELECT TRUNC(MONTHS_BETWEEN(DATE '2026-10-03', DATE '2008-02-29') / 12) AS edad_correcta,
TRUNC((DATE '2026-10-03' - DATE '2008-02-29') / 365) AS edad_dividiendo
FROM dual;
EDAD_CORRECTA EDAD_DIVIDIENDO
------------- ---------------
18 18
1 fila seleccionada.
Aquí coinciden, pero dividiendo entre 365 una persona “cumple años” unos días antes de su cumpleaños real (un día por cada año bisiesto vivido). Pruébalo el día antes de un cumpleaños: MONTHS_BETWEEN es siempre exacto.
4.8. Resumen de funciones de fecha
| Función | Ejemplo | Resultado |
|---|---|---|
SYSDATE | SYSDATE | Fecha y hora del servidor |
| Aritmética | DATE '2026-01-30' + 5 | 04/02/2026 |
ADD_MONTHS | ADD_MONTHS(DATE '2026-01-31', 1) | 28/02/2026 |
MONTHS_BETWEEN | MONTHS_BETWEEN(DATE '2026-03-15', DATE '2026-01-15') | 2 |
NEXT_DAY | NEXT_DAY(DATE '2026-10-03', 'LUNES') | 05/10/2026 |
LAST_DAY | LAST_DAY(DATE '2028-02-10') | 29/02/2028 |
EXTRACT | EXTRACT(YEAR FROM fecha_alta) | 2010 |
TRUNC | TRUNC(DATE '2026-10-03', 'MM') | 01/10/2026 |
ROUND | ROUND(DATE '2026-07-16', 'YYYY') | 01/01/2027 |
5. Funciones de conversión
5.1. Conversión explícita e implícita
Oracle convierte entre texto, números y fechas de dos maneras:
flowchart LR
N["NUMBER"] -- "TO_CHAR(n, máscara)" --> C["VARCHAR2"]
C -- "TO_NUMBER(c, máscara)" --> N
D["DATE"] -- "TO_CHAR(f, máscara)" --> C
C -- "TO_DATE(c, máscara)" --> D
- Explícita: tú llamas a
TO_CHAR,TO_DATE,TO_NUMBERoCAST. Siempre sabes qué pasa. - Implícita: Oracle convierte por su cuenta cuando los tipos no coinciden (por ejemplo,
'Gana ' || salarioconvierte el número a texto). Cómoda, pero peligrosa (apartado 5.6).
5.2. TO_CHAR con fechas
TO_CHAR(fecha, 'máscara') convierte una fecha en texto con el formato que quieras. Elementos de la máscara:
| Elemento | Significado | Ejemplo (3/10/2026 22:58:02) |
|---|---|---|
YYYY / YY | Año con 4 / 2 cifras | 2026 / 26 |
YEAR | Año en letras (en inglés) | TWENTY TWENTY-SIX |
Q | Trimestre (1-4) | 4 |
MM | Mes con 2 cifras | 10 |
MONTH / Month / month | Nombre del mes (relleno con espacios hasta 10) | OCTUBRE / Octubre / octubre |
MON / Mon | Mes abreviado | OCT / Oct |
RM | Mes en números romanos | X |
WW / IW | Semana del año / semana ISO | 40 / 40 |
DDD | Día del año (1-366) | 276 |
DD | Día del mes con 2 cifras | 03 |
D | Día de la semana (1-7, depende de NLS_TERRITORY: en España 1 = lunes) | 6 |
DAY / Day / day | Nombre del día (relleno con espacios) | SÁBADO / Sábado / sábado |
DY / Dy | Día abreviado | SÁB / Sáb |
HH24 / HH o HH12 | Hora 0-23 / hora 1-12 | 22 / 10 |
MI / SS | Minutos / segundos | 58 / 02 |
AM / PM | Indicador de mañana o tarde | PM |
SSSSS | Segundos desde medianoche | 82682 |
FF | Fracciones de segundo (solo TIMESTAMP) | 120017 |
fm | Modificador: quita espacios de relleno y ceros a la izquierda | fmDD → 3 |
"texto" | Texto literal entre comillas dobles | "de" |
- / , . ; : | Signos de puntuación literales |
SELECT TO_CHAR(DATE '2026-10-03', 'DD/MM/YYYY') AS europeo,
TO_CHAR(DATE '2026-10-03', 'YYYY-MM-DD') AS iso,
TO_CHAR(DATE '2026-10-03', 'DD-MON-YY') AS corto,
TO_CHAR(DATE '2026-10-03', 'Q"T" YYYY') AS trimestre,
TO_CHAR(DATE '2026-10-03', 'D DY DDD IW RM') AS varios
FROM dual;
EUROPEO ISO CORTO TRIMEST VARIOS
---------- ---------- --------- ------- --------------
03/10/2026 2026-10-03 03-OCT-26 4T 2026 6 SÁB 276 40 X
1 fila seleccionada.
El modificador fm (fill mode) es imprescindible para las fechas “en letra”, porque Month y Day se rellenan con espacios hasta la longitud del nombre más largo:
SELECT TO_CHAR(DATE '2026-10-03', 'Day, DD "de" Month "de" YYYY') AS sin_fm,
TO_CHAR(DATE '2026-10-03', 'fmDay, DD "de" Month "de" YYYY') AS con_fm,
TO_CHAR(DATE '2026-10-03', 'fmday, dd "de" month') AS minusculas
FROM dual;
SIN_FM CON_FM MINUSCULAS
----------------------------------- ---------------------------- --------------------
Sábado , 03 de Octubre de 2026 Sábado, 3 de Octubre de 2026 sábado, 3 de octubre
1 fila seleccionada.
fm también quita el cero a la izquierda del día (3 en vez de 03). Y las mayúsculas de la máscara mandan: DAY → SÁBADO, Day → Sábado, day → sábado.
Con horas:
SELECT TO_CHAR(SYSDATE, 'DD/MM/YYYY HH24:MI:SS') AS hora_24,
TO_CHAR(SYSDATE, 'HH12:MI AM') AS hora_12,
TO_CHAR(SYSTIMESTAMP, 'HH24:MI:SS.FF3') AS milisegundos
FROM dual;
HORA_24 HORA_12 MILISEGUNDOS
------------------- -------- ------------
03/10/2026 23:25:13 11:25 PM 23:25:13.414
1 fila seleccionada.
⚠️ Importante:
MMes el mes yMIson los minutos. EscribirHH24:MMes un error clásico que muestra el mes donde esperabas los minutos, sin dar ningún error.
Un uso muy habitual de TO_CHAR con fechas es agrupar o filtrar por mes, año o día de la semana:
SELECT pedido_id, fecha_pedido,
TO_CHAR(fecha_pedido, 'fmDay') AS dia_semana,
TO_CHAR(fecha_pedido, 'YYYY-MM') AS anio_mes
FROM pedidos
WHERE TO_CHAR(fecha_pedido, 'YYYY') = '2026'
ORDER BY fecha_pedido;
PEDIDO_ID FECHA_PEDI DIA_SEMANA ANIO_ME
---------- ---------- ---------- -------
5016 12/01/2026 Lunes 2026-01
5017 09/02/2026 Lunes 2026-02
5018 03/03/2026 Martes 2026-03
5019 21/04/2026 Martes 2026-04
5020 30/05/2026 Sábado 2026-05
5021 15/06/2026 Lunes 2026-06
5022 01/07/2026 Miércoles 2026-07
5023 08/07/2026 Miércoles 2026-07
5024 10/07/2026 Viernes 2026-07
9 filas seleccionadas.
5.3. TO_CHAR con números
TO_CHAR(número, 'máscara') da formato a los números:
| Elemento | Significado | Ejemplo | Resultado (sesión española) | |—|—|—|—| | 9 | Una cifra; no muestra ceros a la izquierda | TO_CHAR(42, '9999') | ` 42 | | 0 | Una cifra; muestra ceros a la izquierda | TO_CHAR(42, ‘0000’) | 0042 | | D | Separador decimal **según NLS** | TO_CHAR(3.5, ‘9D99’) | 3,50 | | G | Separador de miles **según NLS** | TO_CHAR(1234, ‘9G999’) | 1.234 | | . , | Punto y coma **literales** (estilo EE. UU.) | TO_CHAR(1234.5, ‘9,999.99’) | 1,234.50 | | L | Símbolo de moneda local (NLS_CURRENCY) | TO_CHAR(1234, ‘L9G999’) | €1.234 (con espacios delante) | | $ | Símbolo de dólar | TO_CHAR(1234, ‘$9999’) | $1234 | | MI | Signo menos a la derecha | TO_CHAR(-45.5, ‘999D99MI’) | 45,50- | | S | Signo (+ o -) | TO_CHAR(45, ‘S999’) | +45 | | FM | Quita los espacios de relleno | TO_CHAR(42, ‘FM9999’) | 42 | | EEEE | Notación científica | TO_CHAR(1234, ‘9.9EEEE’) | 1.2E+03` |
📘 Concepto: El espacio que aparece a la izquierda está reservado para el signo. Si el número no cabe en la máscara, Oracle muestra almohadillas (
####) en lugar de truncarlo.
SELECT TO_CHAR(1234567.891, '9G999G999D99') AS miles,
TO_CHAR(1234567.891, 'FM9G999G999D00L') AS euros,
TO_CHAR(0.5, 'FM990D00') AS cero_coma,
TO_CHAR(12, '0009') AS ceros,
TO_CHAR(-45.5, 'FM999D99MI') AS negativo,
TO_CHAR(123456, '999') AS no_cabe
FROM dual;
MILES EUROS CERO_CO CEROS NEGATIV NO_C
------------- ------------- ------- ----- ------- ----
1.234.567,89 1.234.567,89€ 0,50 0012 45,5- ####
1 fila seleccionada.
Con FM y L al final se consigue el formato europeo 1.234.567,89€. 'FM990D00' muestra 0,50 (sin 0 delante de la D saldría ,50).
Ejemplo real: salarios con formato monetario.
SELECT nombre,
salario,
TO_CHAR(salario, 'FM99G990D00') || ' €' AS salario_formateado,
TO_CHAR(salario * 14, 'FM999G990D00') || ' €' AS anual
FROM empleados
WHERE dept_id = 20;
NOMBRE SALARIO SALARIO_FORMAT ANUAL
------ ---------- -------------- -----------
Luis 4800 4.800,00 € 67.200,00 €
Carmen 2200 2.200,00 € 30.800,00 €
David 2100 2.100,00 € 29.400,00 €
Elena 2300 2.300,00 € 32.200,00 €
Sergio 1900 1.900,00 € 26.600,00 €
Lucía 1700 1.700,00 € 23.800,00 €
6 filas seleccionadas.
💡 Consejo: Usa siempre
GyDen lugar de,y.: así el mismo código muestra1.234,50en España y1,234.50en EE. UU. según la configuración de cada usuario.
5.4. TO_DATE
TO_DATE(texto, 'máscara') interpreta un texto como fecha usando los mismos elementos de máscara que TO_CHAR:
SELECT TO_DATE('15/03/2026', 'DD/MM/YYYY') AS desde_texto,
TO_DATE('2026-03-15 18:30', 'YYYY-MM-DD HH24:MI') AS con_hora,
TO_DATE('15-ENE-2026', 'DD-MON-YYYY') AS mes_en_letra,
TO_DATE('15/03/2026', 'DD/MM/YYYY') + 30 AS mas_30_dias
FROM dual;
DESDE_TEXT CON_HORA MES_EN_LET MAS_30_DIA
---------- ---------- ---------- ----------
15/03/2026 15/03/2026 15/01/2026 14/04/2026
1 fila seleccionada.
La columna con_hora sí guarda las 18:30, pero no se ven porque el formato de la sesión (DD/MM/YYYY) no incluye la hora. Si el texto no es una fecha válida, Oracle da error:
SELECT TO_DATE('31/02/2026', 'DD/MM/YYYY') FROM dual;
SELECT TO_DATE('31/02/2026', 'DD/MM/YYYY') FROM dual
*
ERROR en línea 1:
ORA-01839: date not valid for month specified
Help: https://docs.oracle.com/error-help/db/ora-01839/
YY frente a RR
Con años de dos cifras, YY asume el siglo actual (50 → 2050) y RR aplica una ventana: de 00 a 49 → siglo actual, de 50 a 99 → siglo anterior (50 → 1950). Es la razón por la que el formato por defecto español es DD/MM/RR.
SELECT TO_CHAR(TO_DATE('15/01/50', 'DD/MM/RR'), 'YYYY') AS con_rr,
TO_CHAR(TO_DATE('15/01/50', 'DD/MM/YY'), 'YYYY') AS con_yy,
TO_CHAR(TO_DATE('15/01/25', 'DD/MM/RR'), 'YYYY') AS rr_25
FROM dual;
CON_ CON_ RR_2
---- ---- ----
1950 2050 2025
1 fila seleccionada.
💡 Consejo: En los literales de tus consultas usa
DATE 'AAAA-MM-DD'(módulo 27).TO_DATEes para convertir datos que llegan como texto (de un fichero, de un formulario) y siempre con máscara explícita y años de 4 cifras.
5.5. TO_NUMBER, CAST y el parámetro NLS
TO_NUMBER(texto [, máscara]) convierte texto en número. Sin máscara, el texto debe usar el separador decimal de la sesión (la coma en España):
SELECT TO_NUMBER('1234,56') AS sin_mascara,
TO_NUMBER('1.234,56', '9G999D99') AS con_mascara,
TO_NUMBER('1,234.56', '9G999D99', 'NLS_NUMERIC_CHARACTERS=''.,''') AS formato_ingles
FROM dual;
SIN_MASCARA CON_MASCARA FORMATO_INGLES
----------- ----------- --------------
1234,56 1234,56 1234,56
1 fila seleccionada.
SELECT TO_NUMBER('1234.56') FROM dual;
SELECT TO_NUMBER('1234.56') FROM dual
*
ERROR en línea 1:
ORA-01722: unable to convert string value containing '.' to a number:
ORA-03302: (ORA-01722 details) invalid string value: 1234.56
Help: https://docs.oracle.com/error-help/db/ora-01722/
El tercer argumento NLS permite fijar el idioma o los separadores solo para esa llamada, sin depender de la sesión. Funciona también en TO_CHAR y TO_DATE:
SELECT TO_CHAR(DATE '2026-10-03', 'fmDay, Month', 'NLS_DATE_LANGUAGE=ENGLISH') AS ingles,
TO_CHAR(DATE '2026-10-03', 'fmDay, Month', 'NLS_DATE_LANGUAGE=FRENCH') AS frances,
TO_CHAR(1234.5, 'FM9G999D00', 'NLS_NUMERIC_CHARACTERS='',.''') AS espanol
FROM dual;
INGLES FRANCES ESPANOL
----------------- --------------- --------
Saturday, October Samedi, Octobre 1.234,50
1 fila seleccionada.
CAST(expresión AS tipo) es la función de conversión del estándar SQL; convierte entre casi cualquier par de tipos, pero sin máscaras (usa el formato de la sesión):
SELECT CAST('123' AS NUMBER) + 1 AS texto_a_numero,
CAST(12.7 AS NUMBER(3)) AS redondea,
CAST(DATE '2026-10-03' AS VARCHAR2(10)) AS fecha_a_texto,
CAST('03/10/2026' AS DATE) + 1 AS texto_a_fecha
FROM dual;
TEXTO_A_NUMERO REDONDEA FECHA_A_TE TEXTO_A_FE
-------------- ---------- ---------- ----------
124 13 03/10/2026 04/10/2026
1 fila seleccionada.
Observa que CAST(12.7 AS NUMBER(3)) redondea y que CAST('03/10/2026' AS DATE) solo funciona porque coincide con el NLS_DATE_FORMAT de la sesión: para textos con otro formato usa TO_DATE con máscara.
Desde Oracle 12.2 puedes evitar el error de conversión con DEFAULT ... ON CONVERSION ERROR y comprobar si un valor es convertible con VALIDATE_CONVERSION (1 = sí, 0 = no):
SELECT TO_NUMBER('12a' DEFAULT 0 ON CONVERSION ERROR) AS numero,
TO_DATE('31/02/2026' DEFAULT NULL ON CONVERSION ERROR, 'DD/MM/YYYY') AS fecha,
VALIDATE_CONVERSION('12a' AS NUMBER) AS es_numero,
VALIDATE_CONVERSION('15/03/2026' AS DATE, 'DD/MM/YYYY') AS es_fecha
FROM dual;
NUMERO FECHA ES_NUMERO ES_FECHA
---------- ------ ---------- ----------
0 (nulo) 0 1
1 fila seleccionada.
5.6. Los peligros de la conversión implícita
Cuando comparas una columna de texto con un número, Oracle convierte la columna a número, fila por fila. Funciona… mientras todos los valores sean numéricos. El teléfono de los empleados es VARCHAR2:
SELECT nombre, telefono FROM empleados WHERE telefono = 600100100;
NOMBRE TELEFONO
------ ---------
Ana 600100100
1 fila seleccionada.
Parece correcto, pero con el NIF de los clientes, que contiene letras, la misma idea revienta:
SELECT nombre FROM clientes WHERE nif = 12345678;
SELECT nombre FROM clientes WHERE nif = 12345678
*
ERROR en línea 1:
ORA-01722: unable to convert string value containing 'B' to a number: NIF
ORA-03302: (ORA-01722 details) invalid string value: B12345678
Help: https://docs.oracle.com/error-help/db/ora-01722/
Los peligros de la conversión implícita:
| Peligro | Ejemplo | Solución |
|---|---|---|
| Errores por datos no convertibles | nif = 12345678 → ORA-01722 | Compara con un literal de texto: nif = '12345678Z' |
| Se pierde el uso de índices | TO_NUMBER(telefono) implícito en cada fila | Compara cada columna con su tipo |
| Dependencia de la configuración NLS | fecha_alta > '01/01/2020' falla con otro NLS_DATE_FORMAT | DATE '2020-01-01' o TO_DATE con máscara |
| Resultados inesperados en ordenaciones | Ordenar números guardados como texto: '10' < '9' | Guardar números en columnas NUMBER |
⚠️ Importante: Regla de oro: compara siempre cada columna con un valor de su mismo tipo y haz explícitas las conversiones con
TO_CHAR,TO_DATE,TO_NUMBERoCAST.
6. Funciones para tratar los nulos
6.1. NVL
NVL(expr, valor) devuelve expr si no es nula y valor si lo es. Ambos deben ser del mismo tipo (o convertibles). Es la solución al problema de salario + comision del módulo 27:
SELECT nombre, salario, comision,
salario + comision AS sin_nvl,
salario + NVL(comision, 0) AS ingresos
FROM empleados
WHERE dept_id = 20;
NOMBRE SALARIO COMISION SIN_NVL INGRESOS
------ ---------- ---------- ---------- ----------
Luis 4800 (nulo) (nulo) 4800
Carmen 2200 500 2700 2700
David 2100 800 2900 2900
Elena 2300 300 2600 2600
Sergio 1900 0 1900 1900
Lucía 1700 (nulo) (nulo) 1700
6 filas seleccionadas.
NVL con otros tipos: textos por defecto o fechas por defecto.
SELECT pedido_id, estado,
NVL(TO_CHAR(fecha_envio, 'DD/MM/YYYY'), 'Sin enviar') AS envio,
NVL(empleado_id, 0) AS empleado
FROM pedidos
WHERE fecha_envio IS NULL OR empleado_id IS NULL;
PEDIDO_ID ESTADO ENVIO EMPLEADO
---------- --------- ---------- ----------
5008 CANCELADO Sin enviar 113
5014 ENTREGADO 05/11/2025 0
5022 PENDIENTE Sin enviar 110
5023 PENDIENTE Sin enviar 0
5024 PENDIENTE Sin enviar 113
5 filas seleccionadas.
Como fecha_envio es DATE y 'Sin enviar' es texto, primero se convierte la fecha con TO_CHAR. NVL(fecha_envio, 'Sin enviar') daría error, porque Oracle intentaría convertir 'Sin enviar' en fecha.
6.2. NVL2
NVL2(expr, si_no_nula, si_nula) elige entre dos valores según expr sea o no nula:
SELECT nombre, comision,
NVL2(comision, 'Con comisión', 'Sin comisión') AS tipo,
NVL2(telefono, 'Tiene teléfono', 'Llamar al email') AS contacto
FROM empleados
WHERE dept_id IN (20, 40);
NOMBRE COMISION TIPO CONTACTO
------ ---------- ------------ ---------------
Luis (nulo) Sin comisión Tiene teléfono
Pedro (nulo) Sin comisión Tiene teléfono
Carmen 500 Con comisión Tiene teléfono
David 800 Con comisión Tiene teléfono
Elena 300 Con comisión Tiene teléfono
Sergio 0 Con comisión Tiene teléfono
Lucía (nulo) Sin comisión Tiene teléfono
Isabel (nulo) Sin comisión Tiene teléfono
Jorge (nulo) Sin comisión Llamar al email
9 filas seleccionadas.
Fíjate en Sergio: su comisión es 0, que no es nula, así que es “Con comisión”.
6.3. NULLIF
NULLIF(a, b) devuelve NULL si a = b y a en caso contrario. Su uso estrella es evitar la división entre cero:
SELECT nombre, precio, stock,
ROUND(precio / NULLIF(stock, 0), 2) AS precio_por_unidad_stock
FROM productos
WHERE stock < 10;
NOMBRE PRECIO STOCK PRECIO_POR_UNIDAD_STOCK
-------------------- ---------- ---------- -----------------------
Torre Workstation 1899 4 474,75
Pack teclado y ratón 34,5 0 (nulo)
Smartphone Gama Alta 999 8 124,88
Altavoz Inteligente 49 0 (nulo)
Mesa Elevable 399 6 66,5
Impresora Láser 189 9 21
6 filas seleccionadas.
Sin NULLIF, el producto con stock 0 provocaría ORA-01476: divisor is equal to zero y la consulta entera fallaría:
SELECT nombre, precio / stock FROM productos WHERE stock < 10;
ERROR:
ORA-01476: divisor is equal to zero
Help: https://docs.oracle.com/error-help/db/ora-01476/
ninguna fila seleccionada
SQL*Plus llega a recibir el error al ir leyendo las filas, por eso muestra el ORA-01476 y a continuación que no ha podido devolver ninguna fila.
6.4. COALESCE
COALESCE(e1, e2, ..., en) devuelve la primera expresión no nula de la lista. Es una generalización de NVL a muchos argumentos, y pertenece al estándar SQL:
SELECT nombre, email, telefono,
COALESCE(email, telefono, 'Sin contacto') AS contacto_preferente
FROM clientes
WHERE email IS NULL OR telefono IS NULL;
NOMBRE EMAIL TELEFONO CONTACTO_PREFERENTE
------------------- ----------------- --------- -------------------
Antonio Ruiz Pérez (nulo) 623456789 623456789
Laura Benítez Sanz laurabs@correo.es (nulo) laurabs@correo.es
Panadería La Espiga (nulo) 983445566 983445566
3 filas seleccionadas.
| Función | Argumentos | Devuelve | Estándar |
|---|---|---|---|
NVL(a, b) | 2 | a si no es nula; si no, b | No (Oracle) |
NVL2(a, b, c) | 3 | b si a no es nula; si no, c | No (Oracle) |
NULLIF(a, b) | 2 | NULL si a = b; si no, a | Sí |
COALESCE(a, b, ...) | 2 o más | La primera no nula | Sí |
💡 Consejo:
COALESCEusa evaluación en cortocircuito: deja de evaluar en cuanto encuentra un valor no nulo.NVLevalúa siempre sus dos argumentos. Si el segundo argumento es costoso (una subconsulta, una función lenta),COALESCEes más eficiente.
6.5. LNNVL
LNNVL(condición) devuelve TRUE cuando la condición es FALSE o UNKNOWN, y FALSE cuando es TRUE. Sirve para negar una condición incluyendo los nulos, algo que NOT no hace (módulo 27):
SELECT emp_id, nombre, dept_id
FROM empleados
WHERE LNNVL(dept_id <> 60)
OR emp_id = 100;
EMP_ID NOMBRE DEPT_ID
---------- -------- ----------
100 Ana 10
105 Javier 60
150 Rosa 60
151 Manuel 60
152 Cristina 60
160 Tomás (nulo)
6 filas seleccionadas.
LNNVL(dept_id <> 60) es cierto para los del departamento 60 (la condición es FALSE) y para Tomás (UNKNOWN). Es una función poco conocida; la forma habitual y más legible de expresarlo es dept_id = 60 OR dept_id IS NULL.
7. Expresiones condicionales: DECODE y CASE
7.1. CASE simple
CASE permite escribir un “si… entonces…” dentro de una expresión. La forma simple compara una expresión con una lista de valores:
CASE expresión
WHEN valor1 THEN resultado1
WHEN valor2 THEN resultado2
...
[ELSE resultado_por_defecto]
END
SELECT pedido_id, estado,
CASE estado
WHEN 'PENDIENTE' THEN 'Preparar'
WHEN 'ENVIADO' THEN 'En camino'
WHEN 'ENTREGADO' THEN 'Cerrado'
ELSE 'Revisar'
END AS accion
FROM pedidos
WHERE pedido_id IN (5008, 5019, 5020, 5022);
PEDIDO_ID ESTADO ACCION
---------- --------- ---------
5008 CANCELADO Revisar
5019 ENTREGADO Cerrado
5020 ENVIADO En camino
5022 PENDIENTE Preparar
4 filas seleccionadas.
Si ninguna rama coincide y no hay ELSE, el resultado es NULL.
7.2. CASE buscado
La forma buscada evalúa condiciones independientes, de arriba abajo, y devuelve el resultado de la primera que sea cierta. Permite rangos, IS NULL, LIKE, varias columnas…
SELECT nombre, salario,
CASE
WHEN salario >= 4000 THEN 'Directivo'
WHEN salario >= 2500 THEN 'Alto'
WHEN salario >= 1800 THEN 'Medio'
ELSE 'Bajo'
END AS franja
FROM empleados
WHERE dept_id IN (20, 30)
ORDER BY salario DESC;
NOMBRE SALARIO FRANJA
------ ---------- ---------
Marta 5000 Directivo
Luis 4800 Directivo
Pablo 3500 Alto
Raquel 2800 Alto
Iván 2600 Alto
Hugo 2500 Alto
Sara 2400 Medio
Elena 2300 Medio
Carmen 2200 Medio
David 2100 Medio
Sergio 1900 Medio
Lucía 1700 Bajo
12 filas seleccionadas.
⚠️ Importante: El orden de las ramas importa. Si pusieras primero
WHEN salario >= 1800, todos los que ganan más de 1800 (también los directivos) caerían en esa rama, porqueCASEse detiene en la primera condición cierta.
CASE sirve en cualquier lugar donde vaya una expresión (también en WHERE). Un uso muy práctico es el ORDER BY con un orden personalizado, por ejemplo, primero los pedidos pendientes, después los enviados y al final el resto:
SELECT pedido_id, estado, fecha_pedido
FROM pedidos
WHERE fecha_pedido >= DATE '2026-05-01'
ORDER BY CASE estado
WHEN 'PENDIENTE' THEN 1
WHEN 'ENVIADO' THEN 2
ELSE 3
END,
fecha_pedido;
PEDIDO_ID ESTADO FECHA_PEDI
---------- --------- ----------
5022 PENDIENTE 01/07/2026
5023 PENDIENTE 08/07/2026
5024 PENDIENTE 10/07/2026
5020 ENVIADO 30/05/2026
5021 ENVIADO 15/06/2026
5 filas seleccionadas.
CASE y los nulos: en un CASE simple, WHEN NULL nunca coincide (es una comparación =). Para preguntar por nulos hay que usar el CASE buscado con IS NULL:
SELECT nombre, comision,
CASE comision WHEN NULL THEN 'Nula' ELSE 'No nula' END AS case_simple,
CASE WHEN comision IS NULL THEN 'Nula' ELSE 'No nula' END AS case_buscado
FROM empleados
WHERE dept_id = 20;
NOMBRE COMISION CASE_SI CASE_BU
------ ---------- ------- -------
Luis (nulo) No nula Nula
Carmen 500 No nula No nula
David 800 No nula No nula
Elena 300 No nula No nula
Sergio 0 No nula No nula
Lucía (nulo) No nula Nula
6 filas seleccionadas.
7.3. DECODE
DECODE(expr, v1, r1, v2, r2, ..., [defecto]) es la versión clásica de Oracle del CASE simple:
SELECT pedido_id, estado,
DECODE(estado, 'PENDIENTE', 'P', 'ENVIADO', 'E', 'ENTREGADO', 'OK', '??') AS codigo,
DECODE(empleado_id, NULL, 'Online', 'Tienda') AS canal
FROM pedidos
WHERE pedido_id IN (5008, 5014, 5020, 5022, 5023);
PEDIDO_ID ESTADO CO CANAL
---------- --------- -- ------
5008 CANCELADO ?? Tienda
5014 ENTREGADO OK Online
5020 ENVIADO E Tienda
5022 PENDIENTE P Tienda
5023 PENDIENTE P Online
5 filas seleccionadas.
A diferencia de CASE, DECODE considera iguales dos nulos: DECODE(empleado_id, NULL, ...) sí detecta los pedidos sin empleado.
DECODE | CASE | |
|---|---|---|
| Estándar SQL | No (solo Oracle) | Sí |
| Condiciones | Solo igualdad | Igualdad (simple) o cualquier condición (buscado) |
NULL | NULL coincide con NULL | WHEN NULL nunca coincide (usar IS NULL) |
| Legibilidad | Mala con muchos pares | Buena |
| Dónde se usa | Solo en sentencias SQL | En SQL y en PL/SQL |
💡 Consejo: En código nuevo usa
CASE: es estándar, más legible y más potente. ConoceDECODEporque aparece en muchísimo código antiguo de Oracle.
8. Otras funciones
8.1. GREATEST y LEAST
GREATEST(e1, e2, ...) devuelve el mayor de sus argumentos y LEAST, el menor. No confundir con MAX y MIN, que son funciones de grupo (comparan filas); estas comparan valores de la misma fila.
SELECT nombre, precio,
GREATEST(precio * 0.80, 50) AS precio_oferta_minimo_50,
LEAST(precio, 300) AS precio_tope_300
FROM productos
WHERE categoria_id IN (5, 6, 9);
NOMBRE PRECIO PRECIO_OFERTA_MINIMO_50 PRECIO_TOPE_300
--------------------- ---------- ----------------------- ---------------
Ratón inalámbrico 19,9 50 19,9
Teclado mecánico 79,9 63,92 79,9
Pack teclado y ratón 34,5 50 34,5
Monitor 24 FHD 139 111,2 139
Monitor 27 4K 329 263,2 300
Auriculares Bluetooth 59,9 50 59,9
Altavoz Inteligente 49 50 49
7 filas seleccionadas.
⚠️ Importante: Si algún argumento es
NULL,GREATESTyLEASTdevuelvenNULL. Protégelos conNVLcuando la columna admita nulos:GREATEST(NVL(comision, 0), 100).
SELECT GREATEST(3, 7, NULL) AS con_nulo,
GREATEST(DATE '2025-01-01', DATE '2026-01-01') AS fecha_mayor,
LEAST('pera', 'manzana', 'uva') AS texto_menor
FROM dual;
CON_NULO FECHA_MAYO TEXTO_M
---------- ---------- -------
(nulo) 01/01/2026 manzana
1 fila seleccionada.
8.2. USER y SYS_CONTEXT
USER devuelve el nombre del usuario conectado y SYS_CONTEXT('USERENV', 'parámetro'), información de la sesión. Como estas funciones devuelven cadenas largas, usamos el comando COLUMN ... FORMAT de SQL*Plus para estrechar las columnas en pantalla:
COLUMN usuario FORMAT A10
COLUMN esquema FORMAT A10
COLUMN pdb FORMAT A10
COLUMN idioma FORMAT A25
COLUMN sesion FORMAT A10
SELECT USER AS usuario,
SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA') AS esquema,
SYS_CONTEXT('USERENV', 'CON_NAME') AS pdb,
SYS_CONTEXT('USERENV', 'LANGUAGE') AS idioma,
SYS_CONTEXT('USERENV', 'SESSION_USER') AS sesion
FROM dual;
USUARIO ESQUEMA PDB IDIOMA SESION
------- ------- -------- ---------------------- ------
CURSO CURSO FREEPDB1 SPANISH_SPAIN.AL32UTF8 CURSO
1 fila seleccionada.
Parámetro de USERENV | Devuelve |
|---|---|
SESSION_USER | Usuario que inició la sesión |
CURRENT_SCHEMA | Esquema por defecto para los nombres sin prefijo |
CON_NAME / DB_NAME | Contenedor (PDB) / nombre de la base de datos |
HOST / IP_ADDRESS | Máquina e IP del cliente |
LANGUAGE | Idioma, territorio y juego de caracteres de la sesión |
OS_USER | Usuario del sistema operativo del cliente |
💡 Consejo:
COLUMN nombre FORMAT An(anchon) yCOLUMN nombre FORMAT 99G999D99son comandos de SQL*Plus para presentar mejor las salidas; no forman parte de SQL.COLUMN nombre CLEAR(oCLEAR COLUMNS) los deshace.
9. Funciones anidadas
Las funciones se pueden anidar sin límite práctico: el resultado de una es el argumento de otra. Se evalúan de dentro hacia fuera. Ejemplo: generar el login de cada empleado como inicial del nombre + primer apellido, en minúsculas y sin tildes:
SELECT nombre, apellidos,
LOWER(
TRANSLATE(
SUBSTR(nombre, 1, 1) || SUBSTR(apellidos, 1, INSTR(apellidos, ' ') - 1),
'áéíóúÁÉÍÓÚñÑ', 'aeiouAEIOUnN'
)
) AS login
FROM empleados
WHERE dept_id IN (30, 50);
NOMBRE APELLIDOS LOGIN
------ ---------------- ----------
Marta Sánchez Gil msanchez
Laura Fernández Moreno lfernandez
Pablo Moreno Herrera pmoreno
Raquel Molina Prieto rmolina
Iván Castro Méndez icastro
Sara Ortiz Delgado sortiz
Hugo Rubio Marín hrubio
Nuria Gil Soto ngil
Álvaro Peña Cano apena
9 filas seleccionadas.
Paso a paso con Álvaro Peña Cano:
| Paso | Expresión | Resultado |
|---|---|---|
| 1 | INSTR(apellidos, ' ') | 5 |
| 2 | SUBSTR(apellidos, 1, 5 - 1) | Peña |
| 3 | SUBSTR(nombre, 1, 1) \|\| 'Peña' | ÁPeña |
| 4 | TRANSLATE('ÁPeña', 'áé...ñÑ', 'ae...nN') | APena |
| 5 | LOWER('APena') | apena |
💡 Consejo: Con más de tres niveles de anidamiento, escribe cada función en su propia línea con sangría (como en el ejemplo) y comprueba los pasos intermedios con un
SELECTaparte. Depurar una línea con siete paréntesis seguidos es una pesadilla.
10. Errores frecuentes
| Error | Causa típica | Solución |
|---|---|---|
ORA-00909: invalid number of arguments | Número de argumentos incorrecto (p. ej. NVL(a)) | Revisa la sintaxis de la función |
ORA-01722: invalid number | Conversión implícita o TO_NUMBER de un texto no numérico, o con el separador decimal equivocado | Compara con literales del tipo correcto; usa máscara o DEFAULT ... ON CONVERSION ERROR |
ORA-01843: not a valid month / ORA-01861: literal does not match format string | El texto no encaja con la máscara de TO_DATE | Revisa la máscara; usa DATE 'AAAA-MM-DD' |
ORA-01839: date not valid for month specified | Día inexistente (31/02) | Valida los datos de entrada |
ORA-01846: not a valid day of the week | NEXT_DAY con el nombre del día en otro idioma | Usa el idioma de la sesión o TRUNC(f, 'IW') |
ORA-01476: divisor is equal to zero | División entre cero | NULLIF(divisor, 0) |
ORA-30076: invalid extract field | EXTRACT(HOUR FROM date) | TO_CHAR(f, 'HH24') o CAST(f AS TIMESTAMP) |
ORA-00932: inconsistent datatypes | NVL o CASE con ramas de tipos distintos | Convierte para que todas devuelvan el mismo tipo |
#### en la salida | El número no cabe en la máscara de TO_CHAR | Amplía la máscara |
| Espacios en mitad de una fecha | Month o Day sin fm | Añade fm a la máscara |
| Minutos que parecen meses | MM en lugar de MI | HH24:MI |
11. Ejercicios
A continuación tienes 40 ejercicios resueltos de dificultad creciente y 15 propuestos. Recuerda que los que usan SYSDATE darán resultados distintos según el día en que los ejecutes (las salidas mostradas se obtuvieron a principios de octubre de 2026).
Ejercicio 1: Nombres normalizados
Muestra el nombre de los empleados del departamento 60 en mayúsculas y sus apellidos en minúsculas.
Ver solución
SELECT UPPER(nombre) AS nombre, LOWER(apellidos) AS apellidos
FROM empleados
WHERE dept_id = 60;
NOMBRE APELLIDOS
-------- --------------
JAVIER lópez navarro
ROSA iglesias cruz
MANUEL herrero lozano
CRISTINA medina blanco
4 filas seleccionadas.
Ejercicio 2: Búsqueda sin mayúsculas
Busca los productos cuyo nombre contenga la palabra “monitor” o “ratón” escrita de cualquier forma (mayúsculas o minúsculas).
Ver solución
SELECT producto_id, nombre
FROM productos
WHERE LOWER(nombre) LIKE '%monitor%'
OR LOWER(nombre) LIKE '%ratón%';
PRODUCTO_ID NOMBRE
----------- --------------------
6 Ratón inalámbrico
8 Pack teclado y ratón
9 Monitor 24 FHD
10 Monitor 27 4K
4 filas seleccionadas.
Al pasar la columna a minúsculas, basta con escribir el patrón en minúsculas. Con REGEXP_LIKE(nombre, 'monitor|ratón', 'i') se obtendría lo mismo.
Ejercicio 3: Nombres más largos
Muestra los cinco productos con el nombre más largo y su número de caracteres.
Ver solución
SELECT nombre, LENGTH(nombre) AS longitud
FROM productos
ORDER BY longitud DESC, nombre
FETCH FIRST 5 ROWS ONLY;
NOMBRE LONGITUD
--------------------- ----------
Auriculares Bluetooth 21
Portátil UltraBook 14 21
Smartphone Gama Media 21
Pack teclado y ratón 20
Smartphone Gama Alta 20
5 filas seleccionadas.
Ejercicio 4: Iniciales
Muestra el nombre completo y las iniciales (por ejemplo, A.G.L. para Ana García López) de los empleados del departamento 40.
Ver solución
SELECT nombre || ' ' || apellidos AS empleado,
SUBSTR(nombre, 1, 1) || '.' ||
SUBSTR(apellidos, 1, 1) || '.' ||
SUBSTR(apellidos, INSTR(apellidos, ' ') + 1, 1) || '.' AS iniciales
FROM empleados
WHERE dept_id = 40;
EMPLEADO INICIALES
--------------------- ---------
Pedro Gómez Díaz P.G.D.
Isabel Domínguez Vega I.D.V.
Jorge Vázquez Ramos J.V.R.
3 filas seleccionadas.
La inicial del segundo apellido es el carácter que sigue al espacio: posición INSTR(apellidos, ' ') + 1.
Ejercicio 5: Dominios de correo
Muestra el nombre de los clientes y el dominio de su email (lo que va después de la @), solo de los que tienen email.
Ver solución
SELECT nombre, SUBSTR(email, INSTR(email, '@') + 1) AS dominio
FROM clientes
WHERE email IS NOT NULL
ORDER BY dominio;
NOMBRE DOMINIO
--------------------------- -------------------
Academia Nova academianova.es
Farmacia Central central.es
Construcciones Levante S.A. clevante.es
Carlos Ibáñez Rey correo.es
María José Herrera correo.es
Laura Benítez Sanz correo.es
Diseños Creativos S.L. disenoscreativos.es
Gestoría Delta gestoriadelta.es
...
12 filas seleccionadas.
Ejercicio 6: Primer apellido
Muestra el nombre y el primer apellido de los empleados del departamento 20, ordenados por primer apellido.
Ver solución
SELECT nombre,
SUBSTR(apellidos, 1, INSTR(apellidos, ' ') - 1) AS primer_apellido
FROM empleados
WHERE dept_id = 20
ORDER BY primer_apellido;
NOMBRE PRIMER_APELLIDO
------ ---------------
Sergio Jiménez
Luis Martínez
Elena Navarro
Lucía Romero
Carmen Ruiz
David Torres
6 filas seleccionadas.
Ejercicio 7: NIF enmascarado
Por protección de datos, muestra el NIF de los clientes de Sevilla y Valencia ocultando todo salvo los cuatro últimos caracteres con asteriscos (*****678Z).
Ver solución
SELECT nombre, nif, LPAD(SUBSTR(nif, -4), LENGTH(nif), '*') AS nif_oculto
FROM clientes
WHERE provincia IN ('Sevilla', 'Valencia');
NOMBRE NIF NIF_OCULTO
---------------------- --------- ----------
Talleres Martín S.L. B12345678 *****5678
Clínica Dental Sonrisa B45678901 *****8901
Antonio Ruiz Pérez 23456789D *****789D
Carlos Ibáñez Rey 45678901G *****901G
4 filas seleccionadas.
SUBSTR(nif, -4) toma los 4 últimos caracteres y LPAD rellena por la izquierda con asteriscos hasta la longitud original.
Ejercicio 8: Referencias de producto
Genera para cada producto de la categoría 2 una referencia con el formato CAT02-PROD0001 a partir de categoria_id y producto_id.
Ver solución
SELECT nombre,
'CAT' || LPAD(categoria_id, 2, '0') || '-PROD' || LPAD(producto_id, 4, '0') AS referencia
FROM productos
WHERE categoria_id = 2;
NOMBRE REFERENCIA
--------------------- --------------
Portátil UltraBook 14 CAT02-PROD0001
Portátil Gaming 16 CAT02-PROD0002
Portátil Básico 15 CAT02-PROD0003
3 filas seleccionadas.
Ejercicio 9: Emails corporativos sin tildes
La empresa quiere crear emails alternativos con el formato nombre.primerapellido@empresa.com, en minúsculas y sin tildes ni eñes. Genéralos para los empleados del departamento 50.
Ver solución
SELECT nombre, apellidos,
LOWER(TRANSLATE(nombre || '.' || SUBSTR(apellidos, 1, INSTR(apellidos, ' ') - 1),
'áéíóúÁÉÍÓÚñÑ', 'aeiouAEIOUnN')) || '@empresa.com' AS email_nuevo
FROM empleados
WHERE dept_id = 50;
NOMBRE APELLIDOS EMAIL_NUEVO
------ ---------------- ---------------------------
Laura Fernández Moreno laura.fernandez@empresa.com
Nuria Gil Soto nuria.gil@empresa.com
Álvaro Peña Cano alvaro.pena@empresa.com
3 filas seleccionadas.
Ejercicio 10: Teléfonos formateados
Muestra el teléfono de los clientes de Madrid y Sevilla con el formato 961 234 567.
Ver solución
SELECT nombre,
REGEXP_REPLACE(telefono, '(\d{3})(\d{3})(\d{3})', '\1 \2 \3') AS telefono
FROM clientes
WHERE provincia IN ('Madrid', 'Sevilla');
NOMBRE TELEFONO
---------------------- -----------
María José Herrera 612 345 678
Academia Nova 915 551 234
Clínica Dental Sonrisa 954 112 233
Antonio Ruiz Pérez 623 456 789
4 filas seleccionadas.
Alternativa sin expresiones regulares: SUBSTR(telefono, 1, 3) || ' ' || SUBSTR(telefono, 4, 3) || ' ' || SUBSTR(telefono, 7).
Ejercicio 11: Teléfonos fijos y móviles
En España los teléfonos que empiezan por 6 o 7 son móviles y los que empiezan por 9, fijos. Muestra los clientes con teléfono móvil usando una expresión regular.
Ver solución
SELECT nombre, telefono
FROM clientes
WHERE REGEXP_LIKE(telefono, '^[67][0-9]{8}$');
NOMBRE TELEFONO
------------------ ---------
María José Herrera 612345678
Antonio Ruiz Pérez 623456789
Carlos Ibáñez Rey 634567890
3 filas seleccionadas.
Ejercicio 12: Pulgadas de los monitores y portátiles
Extrae, de los productos de las categorías 2 y 6, el primer número que aparece en su nombre (las pulgadas) como valor numérico, y ordénalos por él.
Ver solución
SELECT nombre, TO_NUMBER(REGEXP_SUBSTR(nombre, '\d+')) AS pulgadas
FROM productos
WHERE categoria_id IN (2, 6)
ORDER BY pulgadas;
NOMBRE PULGADAS
--------------------- ----------
Portátil UltraBook 14 14
Portátil Básico 15 15
Portátil Gaming 16 16
Monitor 24 FHD 24
Monitor 27 4K 27
5 filas seleccionadas.
REGEXP_SUBSTR devuelve texto; TO_NUMBER lo convierte para que la ordenación sea numérica (como texto, '9' > '27').
Ejercicio 13: Precios con IVA
Muestra el nombre, el precio, el IVA (21 %) redondeado a 2 decimales y el precio final de los productos de más de 500 €.
Ver solución
SELECT nombre, precio,
ROUND(precio * 0.21, 2) AS iva,
ROUND(precio * 1.21, 2) AS precio_final
FROM productos
WHERE precio > 500;
NOMBRE PRECIO IVA PRECIO_FINAL
--------------------- ---------- ---------- ------------
Portátil UltraBook 14 899 188,79 1087,79
Portátil Gaming 16 1499 314,79 1813,79
Torre Oficina i5 649 136,29 785,29
Torre Workstation 1899 398,79 2297,79
Smartphone Gama Alta 999 209,79 1208,79
5 filas seleccionadas.
Ejercicio 14: Precios psicológicos
Calcula para los productos de la categoría 8 y 11 un precio de oferta: un 15 % de descuento, redondeado hacia arriba a la decena y restando 1 céntimo (por ejemplo, 254,15 → 259,99).
Ver solución
SELECT nombre, precio,
precio * 0.85 AS descuento_15,
CEIL(precio * 0.85 / 10) * 10 - 0.01 AS precio_oferta
FROM productos
WHERE categoria_id IN (8, 11);
NOMBRE PRECIO DESCUENTO_15 PRECIO_OFERTA
--------------------- ---------- ------------ -------------
Smartphone Gama Media 299 254,15 259,99
Smartphone Gama Alta 999 849,15 849,99
Silla Ergonómica 249 211,65 219,99
Mesa Elevable 399 339,15 339,99
4 filas seleccionadas.
Para redondear “hacia arriba a la decena” se divide entre 10, se aplica CEIL y se vuelve a multiplicar por 10. ROUND(x, -1) redondearía a la decena más cercana, que puede ser hacia abajo.
Ejercicio 15: Pedidos pares e impares
Muestra los pedidos de enero a marzo de 2025 indicando si su número es PAR o IMPAR.
Ver solución
SELECT pedido_id,
CASE MOD(pedido_id, 2) WHEN 0 THEN 'PAR' ELSE 'IMPAR' END AS paridad
FROM pedidos
WHERE fecha_pedido >= DATE '2025-01-01' AND fecha_pedido < DATE '2025-04-01';
PEDIDO_ID PARID
---------- -----
5001 IMPAR
5002 PAR
5003 IMPAR
5004 PAR
5005 IMPAR
5006 PAR
6 filas seleccionadas.
Ejercicio 16: Palés de almacén
Cada palé admite 25 unidades. Muestra cuántos palés completos ocupa el stock de cada producto con más de 30 unidades, cuántas unidades sobran y cuántos palés hacen falta en total.
Ver solución
SELECT nombre, stock,
TRUNC(stock / 25) AS pales_completos,
MOD(stock, 25) AS unidades_sueltas,
CEIL(stock / 25) AS pales_necesarios
FROM productos
WHERE stock > 30
ORDER BY stock DESC;
NOMBRE STOCK PALES_COMPLETOS UNIDADES_SUELTAS PALES_NECESARIOS
--------------------- ---------- --------------- ---------------- ----------------
Ratón inalámbrico 200 8 0 8
Auriculares Bluetooth 80 3 5 4
Disco SSD 1TB 70 2 20 3
Teclado mecánico 60 2 10 3
Smartphone Gama Media 50 2 0 2
Portátil Básico 15 40 1 15 2
Monitor 24 FHD 35 1 10 2
7 filas seleccionadas.
Ejercicio 17: Distancia a la media
Suponiendo que el salario de referencia es 2500 €, muestra para los empleados del departamento 20 la diferencia con ese salario en valor absoluto y una columna que diga POR ENCIMA, IGUAL o POR DEBAJO usando SIGN.
Ver solución
SELECT nombre, salario,
ABS(salario - 2500) AS diferencia,
DECODE(SIGN(salario - 2500), 1, 'POR ENCIMA', 0, 'IGUAL', -1, 'POR DEBAJO') AS posicion
FROM empleados
WHERE dept_id = 20;
NOMBRE SALARIO DIFERENCIA POSICION
------ ---------- ---------- ----------
Luis 4800 2300 POR ENCIMA
Carmen 2200 300 POR DEBAJO
David 2100 400 POR DEBAJO
Elena 2300 200 POR DEBAJO
Sergio 1900 600 POR DEBAJO
Lucía 1700 800 POR DEBAJO
6 filas seleccionadas.
Ejercicio 18: Antigüedad de la plantilla
Muestra nombre, fecha de alta y antigüedad en años completos de los empleados de los departamentos 20 y 40, del más antiguo al más reciente.
Ver solución
SELECT nombre, fecha_alta,
TRUNC(MONTHS_BETWEEN(SYSDATE, fecha_alta) / 12) AS anios
FROM empleados
WHERE dept_id IN (20, 40)
ORDER BY fecha_alta;
NOMBRE FECHA_ALTA ANIOS
------ ---------- ----------
Luis 01/03/2012 14
Pedro 10/09/2013 13
Carmen 12/04/2016 10
Isabel 08/08/2016 10
David 01/07/2017 9
Elena 15/01/2019 7
Lucía 10/02/2020 6
Sergio 01/09/2021 5
Jorge 09/01/2023 3
9 filas seleccionadas.
Ejercicio 19: Trienios
La empresa paga un complemento de 30 € por cada trienio (3 años completos) de antigüedad. Calcula el complemento de los empleados del departamento 30.
Ver solución
SELECT nombre, fecha_alta,
TRUNC(MONTHS_BETWEEN(SYSDATE, fecha_alta) / 36) AS trienios,
TRUNC(MONTHS_BETWEEN(SYSDATE, fecha_alta) / 36) * 30 AS complemento
FROM empleados
WHERE dept_id = 30;
NOMBRE FECHA_ALTA TRIENIOS COMPLEMENTO
------ ---------- ---------- -----------
Marta 20/06/2011 5 150
Pablo 05/05/2014 4 120
Raquel 01/10/2018 2 60
Iván 16/03/2020 2 60
Sara 01/06/2022 1 30
Hugo 20/11/2019 2 60
6 filas seleccionadas.
36 meses = 3 años.
Ejercicio 20: Fin del periodo de prueba
Los empleados nuevos tienen un periodo de prueba de 6 meses. Muestra, para los dados de alta desde 2022, la fecha de fin del periodo de prueba y el día de la semana en que cayó.
Ver solución
SELECT nombre, fecha_alta,
ADD_MONTHS(fecha_alta, 6) AS fin_prueba,
TO_CHAR(ADD_MONTHS(fecha_alta, 6), 'fmDay') AS dia_semana
FROM empleados
WHERE fecha_alta >= DATE '2022-01-01'
ORDER BY fecha_alta;
NOMBRE FECHA_ALTA FIN_PRUEBA DIA_SEMANA
------ ---------- ---------- ----------
Sara 01/06/2022 01/12/2022 Jueves
Manuel 17/10/2022 17/04/2023 Lunes
Jorge 09/01/2023 09/07/2023 Domingo
Tomás 01/09/2025 01/03/2026 Domingo
4 filas seleccionadas.
Ejercicio 21: Fecha de facturación
Los pedidos se facturan el último día del mes en que se realizan y se cobran 30 días después de la factura. Muestra ambas fechas para los pedidos de 2026.
Ver solución
SELECT pedido_id, fecha_pedido,
LAST_DAY(fecha_pedido) AS fecha_factura,
LAST_DAY(fecha_pedido) + 30 AS fecha_cobro
FROM pedidos
WHERE fecha_pedido >= DATE '2026-01-01'
ORDER BY fecha_pedido;
PEDIDO_ID FECHA_PEDI FECHA_FACT FECHA_COBR
---------- ---------- ---------- ----------
5016 12/01/2026 31/01/2026 02/03/2026
5017 09/02/2026 28/02/2026 30/03/2026
5018 03/03/2026 31/03/2026 30/04/2026
5019 21/04/2026 30/04/2026 30/05/2026
5020 30/05/2026 31/05/2026 30/06/2026
5021 15/06/2026 30/06/2026 30/07/2026
5022 01/07/2026 31/07/2026 30/08/2026
5023 08/07/2026 31/07/2026 30/08/2026
5024 10/07/2026 31/07/2026 30/08/2026
9 filas seleccionadas.
Ejercicio 22: Primer lunes de trabajo
Los empleados se incorporan el primer lunes posterior a su fecha de alta. Calcula la fecha de incorporación de los empleados de los departamentos 50 y 60.
Ver solución
SELECT nombre, fecha_alta,
TO_CHAR(fecha_alta, 'fmDay') AS dia_alta,
NEXT_DAY(fecha_alta, 'LUNES') AS incorporacion
FROM empleados
WHERE dept_id IN (50, 60);
NOMBRE FECHA_ALTA DIA_ALTA INCORPORAC
-------- ---------- -------- ----------
Laura 02/02/2015 Lunes 09/02/2015
Javier 11/11/2014 Martes 17/11/2014
Nuria 21/05/2018 Lunes 28/05/2018
Álvaro 12/04/2021 Lunes 19/04/2021
Rosa 03/03/2017 Viernes 06/03/2017
Manuel 17/10/2022 Lunes 24/10/2022
Cristina 22/07/2019 Lunes 29/07/2019
7 filas seleccionadas.
Recuerda que 'LUNES' depende de NLS_LANGUAGE = 'SPANISH'. Laura se dio de alta un lunes (02/02/2015) y aun así se incorpora el lunes siguiente, porque NEXT_DAY es estrictamente posterior.
Ejercicio 23: Pedidos de un trimestre cualquiera
Muestra los pedidos del segundo trimestre (abril, mayo y junio) de cualquier año, con el año y el mes en columnas separadas.
Ver solución
SELECT pedido_id, fecha_pedido,
EXTRACT(YEAR FROM fecha_pedido) AS anio,
EXTRACT(MONTH FROM fecha_pedido) AS mes
FROM pedidos
WHERE TO_CHAR(fecha_pedido, 'Q') = '2'
ORDER BY fecha_pedido;
PEDIDO_ID FECHA_PEDI ANIO MES
---------- ---------- ---------- ----------
5007 10/04/2025 2025 4
5008 06/05/2025 2025 5
5009 19/05/2025 2025 5
5010 02/06/2025 2025 6
5011 25/06/2025 2025 6
5019 21/04/2026 2026 4
5020 30/05/2026 2026 5
5021 15/06/2026 2026 6
8 filas seleccionadas.
También valdría WHERE EXTRACT(MONTH FROM fecha_pedido) BETWEEN 4 AND 6.
Ejercicio 24: Fecha de alta en letra
Muestra la fecha de alta de los directores con el formato jueves, 1 de marzo de 2012.
Ver solución
SELECT nombre,
TO_CHAR(fecha_alta, 'fmday, DD "de" month "de" YYYY') AS fecha_larga
FROM empleados
WHERE oficio LIKE 'DIRECTOR%'
ORDER BY fecha_alta;
NOMBRE FECHA_LARGA
------ --------------------------------
Ana viernes, 15 de enero de 2010
Marta lunes, 20 de junio de 2011
Luis jueves, 1 de marzo de 2012
Pedro martes, 10 de septiembre de 2013
Javier martes, 11 de noviembre de 2014
Laura lunes, 2 de febrero de 2015
6 filas seleccionadas.
La máscara en minúsculas (day, month) produce los nombres en minúsculas; fm quita los espacios de relleno y el cero del día.
Ejercicio 25: ¿Qué día de la semana se vende más?
Muestra para cada pedido entregado de 2025 el nombre del día de la semana y su número (1 = lunes), ordenando por ese número.
Ver solución
SELECT pedido_id, fecha_pedido,
TO_CHAR(fecha_pedido, 'D') AS num_dia,
TO_CHAR(fecha_pedido, 'fmDay') AS dia
FROM pedidos
WHERE estado = 'ENTREGADO'
AND fecha_pedido >= DATE '2025-01-01' AND fecha_pedido < DATE '2026-01-01'
ORDER BY num_dia, fecha_pedido;
PEDIDO_ID FECHA_PEDI N DIA
---------- ---------- - ---------
5003 03/02/2025 1 Lunes
5009 19/05/2025 1 Lunes
5010 02/06/2025 1 Lunes
5012 08/09/2025 1 Lunes
5014 03/11/2025 1 Lunes
5002 28/01/2025 2 Martes
5013 14/10/2025 2 Martes
5001 15/01/2025 3 Miércoles
5005 05/03/2025 3 Miércoles
5011 25/06/2025 3 Miércoles
...
14 filas seleccionadas.
El elemento D depende de NLS_TERRITORY: con SPAIN, el lunes es el día 1. En el módulo 29 contarás los pedidos de cada día con GROUP BY.
Ejercicio 26: Límites de crédito formateados
Muestra los clientes con límite de crédito de al menos 8000 € con el límite formateado como 25.000,00 €.
Ver solución
SELECT nombre,
TO_CHAR(limite_credito, 'FM999G999D00') || ' €' AS limite
FROM clientes
WHERE limite_credito >= 8000
ORDER BY limite_credito DESC;
NOMBRE LIMITE
--------------------------- -----------
Construcciones Levante S.A. 25.000,00 €
Instituto Tecnológico Sur 20.000,00 €
Hotel Mar Azul S.L. 15.000,00 €
Talleres Martín S.L. 10.000,00 €
Academia Nova 8.000,00 €
5 filas seleccionadas.
Ejercicio 27: Fechas que llegan como texto
Una aplicación envía la fecha '15/03/2026' como texto. Conviértela en fecha y muestra los pedidos realizados en los 90 días anteriores a esa fecha.
Ver solución
SELECT pedido_id, fecha_pedido
FROM pedidos
WHERE fecha_pedido >= TO_DATE('15/03/2026', 'DD/MM/YYYY') - 90
AND fecha_pedido < TO_DATE('15/03/2026', 'DD/MM/YYYY');
PEDIDO_ID FECHA_PEDI
---------- ----------
5016 12/01/2026
5017 09/02/2026
5018 03/03/2026
3 filas seleccionadas.
Ejercicio 28: Ingresos totales
Corrige el ejercicio 28 del módulo 27: muestra los empleados cuyos ingresos (salario más comisión) superan 2600 €, considerando 0 la comisión nula.
Ver solución
SELECT nombre, salario, comision,
salario + NVL(comision, 0) AS ingresos
FROM empleados
WHERE salario + NVL(comision, 0) > 2600
ORDER BY ingresos DESC;
NOMBRE SALARIO COMISION INGRESOS
------ ---------- ---------- ----------
Ana 6500 (nulo) 6500
Marta 5000 (nulo) 5000
Luis 4800 (nulo) 4800
Laura 4300 (nulo) 4300
Pedro 4200 (nulo) 4200
Javier 4000 (nulo) 4000
...
11 filas seleccionadas.
Ahora aparecen los 11 empleados que superan los 2600 €, no solo los dos vendedores con comisión.
Ejercicio 29: Tipo de retribución
Muestra para los empleados del departamento 20 una columna que indique Fijo + comisión si tienen comisión (aunque sea 0) y Solo fijo si no la tienen, usando NVL2.
Ver solución
SELECT nombre, comision,
NVL2(comision, 'Fijo + comisión', 'Solo fijo') AS retribucion
FROM empleados
WHERE dept_id = 20;
NOMBRE COMISION RETRIBUCION
------ ---------- ---------------
Luis (nulo) Solo fijo
Carmen 500 Fijo + comisión
David 800 Fijo + comisión
Elena 300 Fijo + comisión
Sergio 0 Fijo + comisión
Lucía (nulo) Solo fijo
6 filas seleccionadas.
Ejercicio 30: Contacto preferente
Muestra, para los clientes 1005 a 1011, un único dato de contacto: el email; si no tiene, el teléfono; y si no tiene ninguno, el texto SIN CONTACTO. Añade una columna que indique qué dato se ha usado.
Ver solución
SELECT nombre,
COALESCE(email, telefono, 'SIN CONTACTO') AS contacto,
CASE
WHEN email IS NOT NULL THEN 'email'
WHEN telefono IS NOT NULL THEN 'teléfono'
ELSE 'ninguno'
END AS medio
FROM clientes
WHERE cliente_id BETWEEN 1005 AND 1011;
NOMBRE CONTACTO MEDIO
------------------------- ---------------------- --------
Clínica Dental Sonrisa admin@sonrisa.es email
Antonio Ruiz Pérez 623456789 teléfono
Hotel Mar Azul S.L. reservas@marazul.es email
Gestoría Delta delta@gestoriadelta.es email
Laura Benítez Sanz laurabs@correo.es email
Instituto Tecnológico Sur compras@itsur.es email
Panadería La Espiga 983445566 teléfono
7 filas seleccionadas.
Ejercicio 31: Rotación del stock
Calcula, para los productos de la categoría 5, cuántos euros de precio hay por unidad de stock (precio / stock), mostrando NULL en lugar de error cuando el stock sea 0.
Ver solución
SELECT nombre, precio, stock,
ROUND(precio / NULLIF(stock, 0), 3) AS euros_por_unidad
FROM productos
WHERE categoria_id = 5;
NOMBRE PRECIO STOCK EUROS_POR_UNIDAD
-------------------- ---------- ---------- ----------------
Ratón inalámbrico 19,9 200 ,1
Teclado mecánico 79,9 60 1,332
Pack teclado y ratón 34,5 0 (nulo)
3 filas seleccionadas.
Ejercicio 32: Estado abreviado con DECODE
Muestra los pedidos de julio de 2026 con el estado traducido al inglés (PENDING, SHIPPED, DELIVERED, CANCELLED) usando DECODE.
Ver solución
SELECT pedido_id, estado,
DECODE(estado, 'PENDIENTE', 'PENDING',
'ENVIADO', 'SHIPPED',
'ENTREGADO', 'DELIVERED',
'CANCELADO', 'CANCELLED') AS status
FROM pedidos
WHERE fecha_pedido >= DATE '2026-06-01';
PEDIDO_ID ESTADO STATUS
---------- --------- -------
5021 ENVIADO SHIPPED
5022 PENDIENTE PENDING
5023 PENDIENTE PENDING
5024 PENDIENTE PENDING
4 filas seleccionadas.
Ejercicio 33: Franjas de precio
Clasifica los productos en Económico (menos de 100 €), Medio (de 100 a 499,99 €) y Premium (500 € o más), ordenados por precio.
Ver solución
SELECT nombre, precio,
CASE
WHEN precio < 100 THEN 'Económico'
WHEN precio < 500 THEN 'Medio'
ELSE 'Premium'
END AS gama
FROM productos
ORDER BY precio;
NOMBRE PRECIO GAMA
--------------------- ---------- ---------
Ratón inalámbrico 19,9 Económico
Pack teclado y ratón 34,5 Económico
Webcam HD 45 Económico
Altavoz Inteligente 49 Económico
Auriculares Bluetooth 59,9 Económico
Teclado mecánico 79,9 Económico
Disco SSD 1TB 89,9 Económico
Monitor 24 FHD 139 Medio
Impresora Láser 189 Medio
Silla Ergonómica 249 Medio
...
20 filas seleccionadas.
Ejercicio 34: Nivel de stock
Muestra los productos con un aviso de stock: AGOTADO si es 0, CRÍTICO si es menor que 10, BAJO si es menor que 20 y CORRECTO en otro caso. Muestra solo los que no estén en CORRECTO.
Ver solución
SELECT nombre, stock,
CASE
WHEN stock = 0 THEN 'AGOTADO'
WHEN stock < 10 THEN 'CRÍTICO'
WHEN stock < 20 THEN 'BAJO'
ELSE 'CORRECTO'
END AS aviso
FROM productos
WHERE stock < 20
ORDER BY stock;
NOMBRE STOCK AVISO
-------------------- ---------- -------
Pack teclado y ratón 0 AGOTADO
Altavoz Inteligente 0 AGOTADO
Torre Workstation 4 CRÍTICO
Mesa Elevable 6 CRÍTICO
Smartphone Gama Alta 8 CRÍTICO
Impresora Láser 9 CRÍTICO
Portátil Gaming 16 10 BAJO
Monitor 27 4K 12 BAJO
Tablet 10 14 BAJO
Torre Oficina i5 15 BAJO
10 filas seleccionadas.
La condición WHERE stock < 20 filtra directamente los que no son CORRECTO. No se puede usar el alias aviso en el WHERE (módulo 27).
Ejercicio 35: Orden personalizado
Muestra los empleados de Ventas (20) ordenados así: primero el director, después los vendedores (por salario descendente) y al final el resto.
Ver solución
SELECT nombre, oficio, salario
FROM empleados
WHERE dept_id = 20
ORDER BY CASE oficio
WHEN 'DIRECTOR' THEN 1
WHEN 'VENDEDOR' THEN 2
ELSE 3
END,
salario DESC;
NOMBRE OFICIO SALARIO
------ -------------- ----------
Luis DIRECTOR 4800
Elena VENDEDOR 2300
Carmen VENDEDOR 2200
David VENDEDOR 2100
Sergio VENDEDOR 1900
Lucía ADMINISTRATIVO 1700
6 filas seleccionadas.
Ejercicio 36: Precio mínimo garantizado
En las rebajas, los productos de las categorías 4 y 5 tienen un 30 % de descuento, pero el precio nunca puede bajar de 25 € (salvo que el precio original ya sea menor). Calcula el precio rebajado.
Ver solución
SELECT nombre, precio,
ROUND(precio * 0.70, 2) AS con_30,
LEAST(precio, GREATEST(ROUND(precio * 0.70, 2), 25)) AS precio_rebajado
FROM productos
WHERE categoria_id IN (4, 5)
ORDER BY precio;
NOMBRE PRECIO CON_30 PRECIO_REBAJADO
-------------------- ---------- ---------- ---------------
Ratón inalámbrico 19,9 13,93 19,9
Pack teclado y ratón 34,5 24,15 25
Webcam HD 45 31,5 31,5
Teclado mecánico 79,9 55,93 55,93
Impresora Láser 189 132,3 132,3
5 filas seleccionadas.
GREATEST(..., 25) impone el mínimo de 25 € y LEAST(precio, ...) evita que el ratón de 19,90 € suba de precio.
Ejercicio 37: Velocidad de envío
Clasifica los pedidos enviados o entregados de la segunda mitad de 2025 según los días que tardaron en enviarse: Exprés (1 día o menos), Normal (2 o 3 días) y Lento (más de 3).
Ver solución
SELECT pedido_id, fecha_pedido, fecha_envio,
fecha_envio - fecha_pedido AS dias,
CASE
WHEN fecha_envio - fecha_pedido <= 1 THEN 'Exprés'
WHEN fecha_envio - fecha_pedido <= 3 THEN 'Normal'
ELSE 'Lento'
END AS velocidad
FROM pedidos
WHERE estado IN ('ENVIADO', 'ENTREGADO')
AND fecha_pedido >= DATE '2025-06-01'
AND fecha_pedido < DATE '2026-01-01';
PEDIDO_ID FECHA_PEDI FECHA_ENVI DIAS VELOCID
---------- ---------- ---------- ---------- -------
5010 02/06/2025 06/06/2025 4 Lento
5011 25/06/2025 27/06/2025 2 Normal
5012 08/09/2025 10/09/2025 2 Normal
5013 14/10/2025 16/10/2025 2 Normal
5014 03/11/2025 05/11/2025 2 Normal
5015 27/11/2025 28/11/2025 1 Exprés
6 filas seleccionadas.
Ejercicio 38: Proyectos y su duración
Muestra los proyectos con su fecha de fin (o En curso si no tienen) y su duración en meses, calculada hasta la fecha de fin o hasta hoy si siguen abiertos.
Ver solución
SELECT nombre,
fecha_inicio,
NVL(TO_CHAR(fecha_fin, 'DD/MM/YYYY'), 'En curso') AS fin,
ROUND(MONTHS_BETWEEN(NVL(fecha_fin, SYSDATE), fecha_inicio), 1) AS meses
FROM proyectos
ORDER BY fecha_inicio;
NOMBRE FECHA_INIC FIN MESES
----------------------- ---------- ---------- ----------
Migración a la nube 07/01/2025 30/09/2025 8,7
Nueva tienda online 01/06/2025 En curso 16,1
Campaña Navidad 2025 01/10/2025 31/12/2025 3
Optimización de almacén 01/02/2026 En curso 8,1
Plan estratégico 2027 01/09/2026 En curso 1,1
5 filas seleccionadas.
NVL(fecha_fin, SYSDATE) sustituye la fecha que falta por la actual dentro del cálculo, y NVL(TO_CHAR(...), 'En curso') hace lo mismo para la presentación (las dos ramas de NVL tienen que ser texto).
Ejercicio 39: Ficha de cliente
Genera una frase como Talleres Martín S.L. (Valencia) - crédito: 10.000,00 € - cliente desde 2018 para los clientes con comercial 110.
Ver solución
SELECT nombre || ' (' || ciudad || ') - crédito: ' ||
TO_CHAR(limite_credito, 'FM999G999D00') || ' € - cliente desde ' ||
TO_CHAR(fecha_alta, 'YYYY') AS ficha
FROM clientes
WHERE comercial_id = 110;
FICHA
----------------------------------------------------------------------------------
Talleres Martín S.L. (Valencia) - crédito: 10.000,00 € - cliente desde 2018
Construcciones Levante S.A. (Alicante) - crédito: 25.000,00 € - cliente desde 2019
Diseños Creativos S.L. (Barcelona) - crédito: 7.000,00 € - cliente desde 2024
3 filas seleccionadas.
Ejercicio 40: Login de empleados
Genera el login de los empleados de Ventas: inicial del nombre, primer apellido completo e inicial del segundo apellido, en minúsculas y sin tildes (por ejemplo, Carmen Ruiz Ortega → cruizo). Ordena por login.
Ver solución
SELECT nombre, apellidos,
LOWER(
TRANSLATE(
SUBSTR(nombre, 1, 1)
|| SUBSTR(apellidos, 1, INSTR(apellidos, ' ') - 1)
|| SUBSTR(apellidos, INSTR(apellidos, ' ') + 1, 1),
'áéíóúÁÉÍÓÚñÑ', 'aeiouAEIOUnN'
)
) AS login
FROM empleados
WHERE dept_id = 20
ORDER BY login;
NOMBRE APELLIDOS LOGIN
------ -------------- ----------
Carmen Ruiz Ortega cruizo
David Torres Romero dtorresr
Elena Navarro Castro enavarroc
Luis Martínez Ruiz lmartinezr
Lucía Romero Serrano lromeros
Sergio Jiménez Vidal sjimenezv
6 filas seleccionadas.
Ejercicios propuestos
Entre paréntesis tienes el número de filas que debe devolver cada consulta.
- Muestra el nombre de los departamentos con solo la primera letra en mayúscula (
Dirección,Ventas…) y su localidad en mayúsculas (7 filas). - Muestra los productos cuyo nombre tiene más de 15 caracteres (13 filas).
- Muestra el usuario del email de cada empleado (lo que va antes de la
@) de los departamentos 40 y 60 (7 filas). - Sustituye en el nombre de los productos la palabra
PortátilporLaptop(20 filas). - Muestra los clientes cuyo email termina en
.esy no pertenece al dominiocorreo.es(9 filas). - Muestra el salario de los empleados del departamento 30 redondeado a la centena y truncado a la centena (6 filas).
- Calcula, para cada línea del pedido 5002, el importe neto truncado a euros enteros (3 filas).
- Muestra los empleados que se dieron de alta un lunes (pista:
TO_CHAR(fecha_alta, 'D')en una sesión española) (13 filas). - Muestra los pedidos realizados en el mes de noviembre de cualquier año (2 filas).
- Muestra la fecha en que cada empleado del departamento 20 cumplirá (o cumplió) 10 años en la empresa (6 filas).
- Muestra los proyectos con su fecha de inicio en formato
01-ENE-2025y el número de días que duraron (o llevan) (5 filas). - Muestra el presupuesto de los departamentos formateado con separador de miles y
€, oSin presupuestosi es nulo (7 filas). - Muestra los empleados con una columna
Contactoque valga el teléfono o, si no tienen, el email (24 filas). - Clasifica a los clientes por antigüedad:
Nuevo(alta en 2025 o después),Reciente(2022-2024) yVeterano(antes de 2022) (14 filas). - Muestra los productos con un 10 % de descuento si su stock supera las 50 unidades, un 5 % si supera las 20 y sin descuento en otro caso, con el precio final redondeado a 2 decimales (20 filas).
Resumen
| Familia | Funciones principales | Recuerda |
|---|---|---|
| Caracteres | UPPER, LOWER, INITCAP, LENGTH, SUBSTR, INSTR, LPAD, RPAD, TRIM, LTRIM, RTRIM, REPLACE, TRANSLATE, CONCAT, ASCII, CHR | Posiciones desde 1; inicio negativo cuenta desde el final |
| Expresiones regulares | REGEXP_LIKE, REGEXP_SUBSTR, REGEXP_REPLACE, REGEXP_COUNT, REGEXP_INSTR | 'i' = sin distinguir mayúsculas |
| Numéricas | ROUND, TRUNC, CEIL, FLOOR, MOD, REMAINDER, ABS, SIGN, POWER, SQRT | Decimales negativos = decenas, centenas… |
| Fecha | SYSDATE, CURRENT_DATE, SYSTIMESTAMP, ADD_MONTHS, MONTHS_BETWEEN, NEXT_DAY, LAST_DAY, EXTRACT, ROUND, TRUNC | fecha - fecha = días; años = TRUNC(MONTHS_BETWEEN/12) |
| Conversión | TO_CHAR, TO_DATE, TO_NUMBER, CAST, VALIDATE_CONVERSION | Máscaras: DD MM YYYY HH24 MI SS fm 9 0 D G L |
| Nulos | NVL, NVL2, NULLIF, COALESCE, LNNVL | NULLIF(x, 0) evita dividir entre cero |
| Condicionales | CASE simple, CASE buscado, DECODE | CASE se detiene en la primera rama cierta |
| Otras | GREATEST, LEAST, USER, SYS_CONTEXT | Con un NULL, GREATEST/LEAST dan NULL |
| Configuración | NLS_LANGUAGE, NLS_TERRITORY, NLS_DATE_FORMAT | Afectan a nombres de meses/días y separadores |