Post on 01-Aug-2020
1Gestión de Bases de Datos
Funciones de una sola fila de OracleJorge Sánchez Asenjo, 2017
www.jorgesanchez.netinfo@jorgesanchez.net @jorgesancheznet
Funciones de una sola fila
2Gestión de Bases de Datos
Funciones de una sola fila de OracleJorge Sánchez Asenjo, 2017
www.jorgesanchez.netinfo@jorgesanchez.net @jorgesancheznet
¿Qué son las funciones?
3Gestión de Bases de Datos
Funciones de una sola fila de OracleJorge Sánchez Asenjo, 2017
www.jorgesanchez.netinfo@jorgesanchez.net @jorgesancheznet
Idea de función
4Gestión de Bases de Datos
Funciones de una sola fila de OracleJorge Sánchez Asenjo, 2017
www.jorgesanchez.netinfo@jorgesanchez.net @jorgesancheznet
Invocar a una función
nombreFunción(parámetro1,parámetro2,…)
5Gestión de Bases de Datos
Funciones de una sola fila de OracleJorge Sánchez Asenjo, 2017
www.jorgesanchez.netinfo@jorgesanchez.net @jorgesancheznet
Invocar a una función. Ejemplos
•SYSDATE•TO_DATE(‘12/3/2017’,’dd/mm/yyyy’)•ROUND(8.3)
6Gestión de Bases de Datos
Funciones de una sola fila de OracleJorge Sánchez Asenjo, 2017
www.jorgesanchez.netinfo@jorgesanchez.net @jorgesancheznet
Tabla DUAL. Permite hacer pruebas
•SELECT SYSDATE FROM DUAL;•SELECT ROUND(8.3) FROM DUAL;
7Gestión de Bases de Datos
Funciones de una sola fila de OracleJorge Sánchez Asenjo, 2017
www.jorgesanchez.netinfo@jorgesanchez.net @jorgesancheznet
Funciones de fila
•SELECT SYSDATE FROM DUAL;•SELECT ROUND(8.3) FROM DUAL;
8Gestión de Bases de Datos
Funciones de una sola fila de OracleJorge Sánchez Asenjo, 2017
www.jorgesanchez.netinfo@jorgesanchez.net @jorgesancheznet
Funcionamiento de las funciones de fila
9Gestión de Bases de Datos
Funciones de una sola fila de OracleJorge Sánchez Asenjo, 2017
www.jorgesanchez.netinfo@jorgesanchez.net @jorgesancheznet
Funciones numéricas
10Gestión de Bases de Datos
Funciones de una sola fila de OracleJorge Sánchez Asenjo, 2017
www.jorgesanchez.netinfo@jorgesanchez.net @jorgesancheznet
Redondeos
•ROUND(7.667,2) Obtiene 7,67•TRUNC(7.667,2) Obtiene 7,66
11Gestión de Bases de Datos
Funciones de una sola fila de OracleJorge Sánchez Asenjo, 2017
www.jorgesanchez.netinfo@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
12Gestión de Bases de Datos
Funciones de una sola fila de OracleJorge Sánchez Asenjo, 2017
www.jorgesanchez.netinfo@jorgesanchez.net @jorgesancheznet
Funciones de texto
13Gestión de Bases de Datos
Funciones de una sola fila de OracleJorge Sánchez Asenjo, 2017
www.jorgesanchez.netinfo@jorgesanchez.net @jorgesancheznet
Mayúsculas y minúsculas
•LOWER(‘Jorge’) Obtiene jorge•UPPER(‘Jorge’) Obtiene JORGE•INITCAP(‘jorge sánchez’) Obtiene Jorge Sánchez
14Gestión de Bases de Datos
Funciones de una sola fila de OracleJorge Sánchez Asenjo, 2017
www.jorgesanchez.netinfo@jorgesanchez.net @jorgesancheznet
Espacios
•RTRIM(‘Jorge ’) Obtiene ‘Jorge’•LTRIM(‘ Jorge’) Obtiene ‘Jorge’ •TRIM(‘ Jorge Sánchez ’) Obtiene ‘Jorge Sánchez’
15Gestión de Bases de Datos
Funciones de una sola fila de OracleJorge Sánchez Asenjo, 2017
www.jorgesanchez.netinfo@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’
16Gestión de Bases de Datos
Funciones de una sola fila de OracleJorge Sánchez Asenjo, 2017
www.jorgesanchez.netinfo@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
17Gestión de Bases de Datos
Funciones de una sola fila de OracleJorge Sánchez Asenjo, 2017
www.jorgesanchez.netinfo@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
18Gestión de Bases de Datos
Funciones de una sola fila de OracleJorge Sánchez Asenjo, 2017
www.jorgesanchez.netinfo@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*
19Gestión de Bases de Datos
Funciones de una sola fila de OracleJorge Sánchez Asenjo, 2017
www.jorgesanchez.netinfo@jorgesanchez.net @jorgesancheznet
Manipulación de texto
•LPAD(‘Hola’,10,’-’) Devuelve ------Hola•RPAD(‘Hola’,10,’-’) Devuelve Hola------•REVERSE(‘Hola’) Devuelve aloH
20Gestión de Bases de Datos
Funciones de una sola fila de OracleJorge Sánchez Asenjo, 2017
www.jorgesanchez.netinfo@jorgesanchez.net @jorgesancheznet
Otras
•ASCII(‘A’) Devuelve 65•CHR(65) Devuelve ‘A’
21Gestión de Bases de Datos
Funciones de una sola fila de OracleJorge Sánchez Asenjo, 2017
www.jorgesanchez.netinfo@jorgesanchez.net @jorgesancheznet
Funciones de nulos
22Gestión de Bases de Datos
Funciones de una sola fila de OracleJorge Sánchez Asenjo, 2017
www.jorgesanchez.netinfo@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
23Gestión de Bases de Datos
Funciones de una sola fila de OracleJorge Sánchez Asenjo, 2017
www.jorgesanchez.netinfo@jorgesanchez.net @jorgesancheznet
Funciones de trabajo con nulos
•COALESCE(valor1,valor2,valor2)Devuelve, de la lista, el primer valor no nulo
24Gestión de Bases de Datos
Funciones de una sola fila de OracleJorge Sánchez Asenjo, 2017
www.jorgesanchez.netinfo@jorgesanchez.net @jorgesancheznet
Funciones de fecha, hora e intervalos
25Gestión de Bases de Datos
Funciones de una sola fila de OracleJorge Sánchez Asenjo, 2017
www.jorgesanchez.netinfo@jorgesanchez.net @jorgesancheznet
Fecha y hora actual
•SYSDATE Devuelve la fecha actual•SYSTIMESTAMP Devuelve la fecha y hora actual
26Gestión de Bases de Datos
Funciones de una sola fila de OracleJorge Sánchez Asenjo, 2017
www.jorgesanchez.netinfo@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
27Gestión de Bases de Datos
Funciones de una sola fila de OracleJorge Sánchez Asenjo, 2017
www.jorgesanchez.netinfo@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
28Gestión de Bases de Datos
Funciones de una sola fila de OracleJorge Sánchez Asenjo, 2017
www.jorgesanchez.netinfo@jorgesanchez.net @jorgesancheznet
Comparaciones
•GREATEST(fecha1,fecha2,fecha3..) Obtiene la fecha más moderna•LEAST(fecha1,fecha2,fecha3..) Obtiene la fecha más antigua
29Gestión de Bases de Datos
Funciones de una sola fila de OracleJorge Sánchez Asenjo, 2017
www.jorgesanchez.netinfo@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
30Gestión de Bases de Datos
Funciones de una sola fila de OracleJorge Sánchez Asenjo, 2017
www.jorgesanchez.netinfo@jorgesanchez.net @jorgesancheznet
Redondeos
•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
31Gestión de Bases de Datos
Funciones de una sola fila de OracleJorge Sánchez Asenjo, 2017
www.jorgesanchez.netinfo@jorgesanchez.net @jorgesancheznet
Funciones de conversión
32Gestión de Bases de Datos
Funciones de una sola fila de OracleJorge Sánchez Asenjo, 2017
www.jorgesanchez.netinfo@jorgesanchez.net @jorgesancheznet
Resumen de funciones de conversión
33Gestión de Bases de Datos
Funciones de una sola fila de OracleJorge Sánchez Asenjo, 2017
www.jorgesanchez.netinfo@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
34Gestión de Bases de Datos
Funciones de una sola fila de OracleJorge Sánchez Asenjo, 2017
www.jorgesanchez.netinfo@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)
35Gestión de Bases de Datos
Funciones de una sola fila de OracleJorge Sánchez Asenjo, 2017
www.jorgesanchez.netinfo@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
36Gestión de Bases de Datos
Funciones de una sola fila de OracleJorge Sánchez Asenjo, 2017
www.jorgesanchez.netinfo@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
37Gestión de Bases de Datos
Funciones de una sola fila de OracleJorge Sánchez Asenjo, 2017
www.jorgesanchez.netinfo@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:
38Gestión de Bases de Datos
Funciones de una sola fila de OracleJorge Sánchez Asenjo, 2017
www.jorgesanchez.netinfo@jorgesanchez.net @jorgesancheznet
Símbolos de las funciones de conversión de fechas
• .:;/ Símbolos literales, aparecen tal cual• “texto” Texto literal
39Gestión de Bases de Datos
Funciones de una sola fila de OracleJorge Sánchez Asenjo, 2017
www.jorgesanchez.netinfo@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
40Gestión de Bases de Datos
Funciones de una sola fila de OracleJorge Sánchez Asenjo, 2017
www.jorgesanchez.netinfo@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
41Gestión de Bases de Datos
Funciones de una sola fila de OracleJorge Sánchez Asenjo, 2017
www.jorgesanchez.netinfo@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
42Gestión de Bases de Datos
Funciones de una sola fila de OracleJorge Sánchez Asenjo, 2017
www.jorgesanchez.netinfo@jorgesanchez.net @jorgesancheznet
Otras conversiones
•CAST(2.3452 AS NUMBER(2))Convierte el número a NUMBER(2)
43Gestión de Bases de Datos
Funciones de una sola fila de OracleJorge Sánchez Asenjo, 2017
www.jorgesanchez.netinfo@jorgesanchez.net @jorgesancheznet
Funciones condicionales
44Gestión de Bases de Datos
Funciones de una sola fila de OracleJorge Sánchez Asenjo, 2017
www.jorgesanchez.netinfo@jorgesanchez.net @jorgesancheznet
CASE. Sintaxis
CASE expresión WHEN valor1 THEN resultado1[WHEN valor2 THEN resultado2
…[ELSE resultado_si_nada_se_cumple]]
END
45Gestión de Bases de Datos
Funciones de una sola fila de OracleJorge Sánchez Asenjo, 2017
www.jorgesanchez.netinfo@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 calificacionFROM alumnos;
46Gestión de Bases de Datos
Funciones de una sola fila de OracleJorge Sánchez Asenjo, 2017
www.jorgesanchez.netinfo@jorgesanchez.net @jorgesancheznet
DECODE. Sintaxis
DECODE(expresión, valor1, resultado1 [,valor2, resultado2,...][,valorPordefecto]
)
47Gestión de Bases de Datos
Funciones de una sola fila de OracleJorge Sánchez Asenjo, 2017
www.jorgesanchez.netinfo@jorgesanchez.net @jorgesancheznet
DECODE. Ejemplo
SELECT nombre,DECODE(descuento,
1, precio * 0.85,2, precio * 0.93,3, precio * 0.96,precio)
FROM empleados;