funciones-oracle

Vista previa del material en texto

1
Gestión de Bases de Datos
Funciones de una sola fila de Oracle
Jorge Sánchez Asenjo, 2017
www.jorgesanchez.net
info@jorgesanchez.net 
@jorgesancheznet
Funciones de una sola 
fila
2
Gestión de Bases de Datos
Funciones de una sola fila de Oracle
Jorge Sánchez Asenjo, 2017
www.jorgesanchez.net
info@jorgesanchez.net 
@jorgesancheznet
¿Qué son las funciones?
3
Gestión de Bases de Datos
Funciones de una sola fila de Oracle
Jorge Sánchez Asenjo, 2017
www.jorgesanchez.net
info@jorgesanchez.net 
@jorgesancheznet
Idea de función
4
Gestión de Bases de Datos
Funciones de una sola fila de Oracle
Jorge Sánchez Asenjo, 2017
www.jorgesanchez.net
info@jorgesanchez.net 
@jorgesancheznet
Invocar a una función
nombreFunción(parámetro1,parámetro2,…)
5
Gestión de Bases de Datos
Funciones de una sola fila de Oracle
Jorge Sánchez Asenjo, 2017
www.jorgesanchez.net
info@jorgesanchez.net 
@jorgesancheznet
Invocar a una función. Ejemplos
•SYSDATE
•TO_DATE(‘12/3/2017’,’dd/mm/yyyy’)
•ROUND(8.3)
6
Gestión de Bases de Datos
Funciones de una sola fila de Oracle
Jorge Sánchez Asenjo, 2017
www.jorgesanchez.net
info@jorgesanchez.net 
@jorgesancheznet
Tabla DUAL. Permite hacer pruebas
•SELECT SYSDATE FROM DUAL;
•SELECT ROUND(8.3) FROM DUAL;
7
Gestión de Bases de Datos
Funciones de una sola fila de Oracle
Jorge Sánchez Asenjo, 2017
www.jorgesanchez.net
info@jorgesanchez.net 
@jorgesancheznet
Funciones de fila
•SELECT SYSDATE FROM DUAL;
•SELECT ROUND(8.3) FROM DUAL;
8
Gestión de Bases de Datos
Funciones de una sola fila de Oracle
Jorge Sánchez Asenjo, 2017
www.jorgesanchez.net
info@jorgesanchez.net 
@jorgesancheznet
Funcionamiento de 
las funciones de fila
9
Gestión de Bases de Datos
Funciones de una sola fila de Oracle
Jorge Sánchez Asenjo, 2017
www.jorgesanchez.net
info@jorgesanchez.net 
@jorgesancheznet
Funciones numéricas
10
Gestión de Bases de Datos
Funciones de una sola fila de Oracle
Jorge Sánchez Asenjo, 2017
www.jorgesanchez.net
info@jorgesanchez.net 
@jorgesancheznet
Redondeos
•ROUND(7.667,2) Obtiene 7,67
•TRUNC(7.667,2) Obtiene 7,66
11
Gestión de Bases de Datos
Funciones de una sola fila de Oracle
Jorge Sánchez Asenjo, 2017
www.jorgesanchez.net
info@jorgesanchez.net 
@jorgesancheznet
Matemáticas 
•MOD(15,4) Obtiene 3 (resto de 15/4)
•SQRT(9) Obtiene 3 (raíz cuadrada)
•SIGN(-3) Obtiene -1
•SIGN(3) Obtiene 1
•SIGN(0) Obtiene 0
•ABS(3) Obtiene 3
•ABS(-3) Obtiene 3
12
Gestión de Bases de Datos
Funciones de una sola fila de Oracle
Jorge Sánchez Asenjo, 2017
www.jorgesanchez.net
info@jorgesanchez.net 
@jorgesancheznet
Funciones de texto
13
Gestión de Bases de Datos
Funciones de una sola fila de Oracle
Jorge Sánchez Asenjo, 2017
www.jorgesanchez.net
info@jorgesanchez.net 
@jorgesancheznet
Mayúsculas y minúsculas 
•LOWER(‘Jorge’)  Obtiene jorge
•UPPER(‘Jorge’)  Obtiene JORGE
•INITCAP(‘jorge sánchez’) 
 Obtiene Jorge Sánchez
14
Gestión de Bases de Datos
Funciones de una sola fila de Oracle
Jorge Sánchez Asenjo, 2017
www.jorgesanchez.net
info@jorgesanchez.net 
@jorgesancheznet
Espacios 
•RTRIM(‘Jorge ’)  Obtiene ‘Jorge’
•LTRIM(‘ Jorge’)  Obtiene ‘Jorge’ 
•TRIM(‘ Jorge Sánchez ’) 
 Obtiene ‘Jorge Sánchez’
15
Gestión de Bases de Datos
Funciones de una sola fila de Oracle
Jorge Sánchez Asenjo, 2017
www.jorgesanchez.net
info@jorgesanchez.net 
@jorgesancheznet
Manipulación de texto 
•LENGTH(‘Jorge’)  Obtiene 5
•SUBSTR(‘Jorge Sánchez’,4) 
 Obtiene ‘ge Sánchez’ 
•SUBSTR(‘Jorge Sánchez’,4,2) Obtiene ‘ge’ 
16
Gestión de Bases de Datos
Funciones de una sola fila de Oracle
Jorge Sánchez Asenjo, 2017
www.jorgesanchez.net
info@jorgesanchez.net 
@jorgesancheznet
Manipulación de texto 
•INSTR(‘Jorge Sánchez’,’e’) Devuelve 5
•INSTR(‘Jorge Sánchez’,’e’,6) Devuelve 12
•INSTR(‘Jorge Sánchez’,’e’,1,2) Devuelve 12
17
Gestión de Bases de Datos
Funciones de una sola fila de Oracle
Jorge Sánchez Asenjo, 2017
www.jorgesanchez.net
info@jorgesanchez.net 
@jorgesancheznet
Manipulación de texto 
•REPLACE(‘Jorge Sánchez’,’e’) 
Devuelve Jorg Sánchz
•REPLACE(‘Jorge Sánchez’,’e’,’a’) 
Devuelve Jorga Sánchaz
18
Gestión de Bases de Datos
Funciones de una sola fila de Oracle
Jorge Sánchez Asenjo, 2017
www.jorgesanchez.net
info@jorgesanchez.net 
@jorgesancheznet
Manipulación de texto 
•TRANSLATE(‘Jorge Sánchez Asenjo’,’eo’,’zx’) 
Devuelve Jxrgz Sánchzz Asznjx
•TRANSLATE(‘Jorge Sánchez 
Asenjo’,’eo’,**’) 
Devuelve J*rg* Sánch*z As*nj*
19
Gestión de Bases de Datos
Funciones de una sola fila de Oracle
Jorge Sánchez Asenjo, 2017
www.jorgesanchez.net
info@jorgesanchez.net 
@jorgesancheznet
Manipulación de texto 
•LPAD(‘Hola’,10,’-’) Devuelve ------Hola
•RPAD(‘Hola’,10,’-’) Devuelve Hola------
•REVERSE(‘Hola’) Devuelve aloH
20
Gestión de Bases de Datos
Funciones de una sola fila de Oracle
Jorge Sánchez Asenjo, 2017
www.jorgesanchez.net
info@jorgesanchez.net 
@jorgesancheznet
Otras
•ASCII(‘A’) Devuelve 65
•CHR(65) Devuelve ‘A’
21
Gestión de Bases de Datos
Funciones de una sola fila de Oracle
Jorge Sánchez Asenjo, 2017
www.jorgesanchez.net
info@jorgesanchez.net 
@jorgesancheznet
Funciones de nulos
22
Gestión de Bases de Datos
Funciones de una sola fila de Oracle
Jorge Sánchez Asenjo, 2017
www.jorgesanchez.net
info@jorgesanchez.net 
@jorgesancheznet
Funciones de trabajo con nulos
•NVL(teléfono,’sin teléfono’)
Si teléfono es nulo devuelve‘sin teléfono’
•NVL2(teléfono,’con teléfono’,’sin teléfono’)
Si teléfono es nulo devuelve ‘sin teléfono’ y si no lo es 
devuelve ‘con teléfono’
•NULLIF(valor1,valor2)
Si valor1=valor2, devuelve nulo, sino devuelve valor2
23
Gestión de Bases de Datos
Funciones de una sola fila de Oracle
Jorge Sánchez Asenjo, 2017
www.jorgesanchez.net
info@jorgesanchez.net 
@jorgesancheznet
Funciones de trabajo con nulos
•COALESCE(valor1,valor2,valor2)
Devuelve, de la lista, el primer valor no nulo
24
Gestión de Bases de Datos
Funciones de una sola fila de Oracle
Jorge Sánchez Asenjo, 2017
www.jorgesanchez.net
info@jorgesanchez.net 
@jorgesancheznet
Funciones de fecha, hora e 
intervalos
25
Gestión de Bases de Datos
Funciones de una sola fila de Oracle
Jorge Sánchez Asenjo, 2017
www.jorgesanchez.net
info@jorgesanchez.net 
@jorgesancheznet
Fecha y hora actual
•SYSDATE Devuelve la fecha actual
•SYSTIMESTAMP Devuelve la fecha y hora actual
26
Gestión de Bases de Datos
Funciones de una sola fila de Oracle
Jorge Sánchez Asenjo, 2017
www.jorgesanchez.net
info@jorgesanchez.net 
@jorgesancheznet
Funciones de cálculo sobre fechas
•ADD_MONTHS(SYSDATE,1) 
Añade un mes a la fecha actual y devuelve esa fecha
•MONTHS_BETWEEN(FECHA1,FECHA2) 
Meses de diferencia entre las fechas
•LAST_DAY(SYSDATE) 
Devuelve el último día de este mes
27
Gestión de Bases de Datos
Funciones de una sola fila de Oracle
Jorge Sánchez Asenjo, 2017
www.jorgesanchez.net
info@jorgesanchez.net 
@jorgesancheznet
Funciones de cálculo sobre fechas
•EXTRACT(DAY FROM SYSDATE) 
 Saca el número de día del mes actual
•EXTRACT(MONTH FROM SYSDATE) 
 Saca el número de mes actual
•EXTRACT(HOUR FROM SYSTIMESTAMP) 
 Saca el número de hora actual
28
Gestión de Bases de Datos
Funciones de una sola fila de Oracle
Jorge Sánchez Asenjo, 2017
www.jorgesanchez.net
info@jorgesanchez.net 
@jorgesancheznet
Comparaciones
•GREATEST(fecha1,fecha2,fecha3..)
 Obtiene la fecha más moderna
•LEAST(fecha1,fecha2,fecha3..)
 Obtiene la fecha más antigua
29
Gestión de Bases de Datos
Funciones de una sola fila de Oracle
Jorge Sánchez Asenjo, 2017
www.jorgesanchez.net
info@jorgesanchez.net 
@jorgesancheznet
Redondeos
•ROUND(SYSDATE,’YEAR’)
 Obtiene el 1 de enero del año más cercano
•ROUND(SYSDATE,’MONTH’)
 Obtiene el primer día del mes más cercano
•ROUND(SYSTIMESTAMP,’DAY’)
 Obtiene las 0:00 del día más cercano
30
Gestión de Bases de Datos
Funciones de una sola fila de Oracle
Jorge Sánchez Asenjo, 2017
www.jorgesanchez.net
info@jorgesanchez.net 
@jorgesancheznetRedondeos
•TRUNC(SYSDATE,’YEAR’)
 Obtiene el 1 de enero de este año
•TRUNC(SYSDATE,’MONTH’)
 Obtiene el primer día de este mes
•TRUNC(SYSTIMESTAMP,’DAY’)
 Obtiene las 0:00 de hoy
31
Gestión de Bases de Datos
Funciones de una sola fila de Oracle
Jorge Sánchez Asenjo, 2017
www.jorgesanchez.net
info@jorgesanchez.net 
@jorgesancheznet
Funciones de conversión
32
Gestión de Bases de Datos
Funciones de una sola fila de Oracle
Jorge Sánchez Asenjo, 2017
www.jorgesanchez.net
info@jorgesanchez.net 
@jorgesancheznet
Resumen de funciones de conversión
33
Gestión de Bases de Datos
Funciones de una sola fila de Oracle
Jorge Sánchez Asenjo, 2017
www.jorgesanchez.net
info@jorgesanchez.net 
@jorgesancheznet
Funciones
•TO_CHAR(fecha,formato) 
 Convierte la fecha a texto usando el formato indicado
•TO_CHAR(número,formato) 
 Convierte el número a texto usando el formato indicado
•TO_NUMBER(texto,formato) 
 Convierte el texto a número usando el formato
•TO_DATE(texto,formato) 
 Convierte el texto a fecha y hora usando el formato
34
Gestión de Bases de Datos
Funciones de una sola fila de Oracle
Jorge Sánchez Asenjo, 2017
www.jorgesanchez.net
info@jorgesanchez.net 
@jorgesancheznet
Símbolos de las funciones de conversión de 
fechas
•RR: Dos cifras para el año (formato de Oracle)
•Y: Última cifra del año
•YY: Año en dos cifras
•YYY: Año en tres cifras
•YYYY: Año en cuatrocifras
•YEAR: Formato hablado de año (en inglés)
35
Gestión de Bases de Datos
Funciones de una sola fila de Oracle
Jorge Sánchez Asenjo, 2017
www.jorgesanchez.net
info@jorgesanchez.net 
@jorgesancheznet
Símbolos de las funciones de conversión de 
fechas
•CC: Siglo
•AC: Si el año es superior al 0, se pone DC
•BC: Si el año es inferior al 0, se pone AC
•MM: Mes en dos cifras
•MON: Mes usando tres letras
•MONTH: Nombre del mes
•RM: Mes en romano
36
Gestión de Bases de Datos
Funciones de una sola fila de Oracle
Jorge Sánchez Asenjo, 2017
www.jorgesanchez.net
info@jorgesanchez.net 
@jorgesancheznet
Símbolos de las funciones de conversión de 
fechas
•DY: Tres letras para el día de la semana
•DAY: Día de la semana
•D: Día de la semana (de 1 a 7)
•DD: Día del mes en dos cifras
•DDD: Día del año
•Q: Trimestre
•W: Semana del año
• IW: Semana del mes en formato ISO
37
Gestión de Bases de Datos
Funciones de una sola fila de Oracle
Jorge Sánchez Asenjo, 2017
www.jorgesanchez.net
info@jorgesanchez.net 
@jorgesancheznet
Símbolos de las funciones de conversión de 
fechas
• J: Día Juliano
•HH12: Hora, formato 12 horas
•HH24: Hora, formato 24 horas
•MI: Minutos
• SS: Segundos (0 a 59)
•SSSS: Segundos transcurridos desde medianoche
• AM: 
• PM:
38
Gestión de Bases de Datos
Funciones de una sola fila de Oracle
Jorge Sánchez Asenjo, 2017
www.jorgesanchez.net
info@jorgesanchez.net 
@jorgesancheznet
Símbolos de las funciones de conversión de 
fechas
• .:;/ Símbolos literales, aparecen tal cual
• “texto” Texto literal
39
Gestión de Bases de Datos
Funciones de una sola fila de Oracle
Jorge Sánchez Asenjo, 2017
www.jorgesanchez.net
info@jorgesanchez.net 
@jorgesancheznet
Sufijos
•TH: Pone los números en ordinal (en inglés)
•SP: Números en formato hablado (en inglés)
•SPTH: Números ordinales hablados en inglés
•FM: Al final del todo, elimina los espacios sobrantes
40
Gestión de Bases de Datos
Funciones de una sola fila de Oracle
Jorge Sánchez Asenjo, 2017
www.jorgesanchez.net
info@jorgesanchez.net 
@jorgesancheznet
Símbolos de las funciones de conversión de 
números
•9 Posición opcional para un número
•0 Posición de número obligatorio
•$ Símbolo del dólar
•L Posición del símbolo de la monerda local (€ por ej.)
•S Posición del signo del número
•D Posición del separador decimal
41
Gestión de Bases de Datos
Funciones de una sola fila de Oracle
Jorge Sánchez Asenjo, 2017
www.jorgesanchez.net
info@jorgesanchez.net 
@jorgesancheznet
Símbolos de las funciones de conversión de 
números
•G Posición del separador de grupo
•0 Posición de número obligatorio
• . Posición del punto decimal (números en inglés)
• , Posición del separador de grupo (números en inglés)
•PR Los números negativos entre paréntesis
•MI  Signo menos para los negativos (se coloca a la dcha)
• EEEE Notación científica
42
Gestión de Bases de Datos
Funciones de una sola fila de Oracle
Jorge Sánchez Asenjo, 2017
www.jorgesanchez.net
info@jorgesanchez.net 
@jorgesancheznet
Otras conversiones
•CAST(2.3452 AS NUMBER(2))
Convierte el número a NUMBER(2)
43
Gestión de Bases de Datos
Funciones de una sola fila de Oracle
Jorge Sánchez Asenjo, 2017
www.jorgesanchez.net
info@jorgesanchez.net 
@jorgesancheznet
Funciones condicionales
44
Gestión de Bases de Datos
Funciones de una sola fila de Oracle
Jorge Sánchez Asenjo, 2017
www.jorgesanchez.net
info@jorgesanchez.net 
@jorgesancheznet
CASE. Sintaxis
CASE expresión WHEN valor1 THEN resultado1
[WHEN valor2 THEN resultado2
…
[ELSE resultado_si_nada_se_cumple]]
END
45
Gestión de Bases de Datos
Funciones de una sola fila de Oracle
Jorge Sánchez Asenjo, 2017
www.jorgesanchez.net
info@jorgesanchez.net 
@jorgesancheznet
CASE. Ejemp’lo
SELECT nombre, 
CASE WHEN nota>=5 AND nota<7 THEN ‘Suficiente’
WHEN nota>=7 AND nota<9 THEN ‘Notable’
WHEN nota>=9 THEN ‘Sobresaliente’
ELSE ‘Suspenso’ 
END AS calificacion
FROM alumnos;
46
Gestión de Bases de Datos
Funciones de una sola fila de Oracle
Jorge Sánchez Asenjo, 2017
www.jorgesanchez.net
info@jorgesanchez.net 
@jorgesancheznet
DECODE. Sintaxis
DECODE(expresión, 
valor1, resultado1 
[,valor2, resultado2,...]
[,valorPordefecto]
)
47
Gestión de Bases de Datos
Funciones de una sola fila de Oracle
Jorge Sánchez Asenjo, 2017
www.jorgesanchez.net
info@jorgesanchez.net 
@jorgesancheznet
DECODE. Ejemplo
SELECT nombre,
DECODE(descuento,
1, precio * 0.85,
2, precio * 0.93,
3, precio * 0.96,
precio)
FROM empleados;