Post on 06-Nov-2018
transcript
Copyright © Oracle Corporation, 1998. All rights reserved.
3 Funciones a Nivel de Fila
3-2 Copyright © Oracle Corporation, 1998. All rights reserved.
Objetivos Al completar esta lección, debería ser capaz de hacer lo siguiente: • Explicar los diversos tipos de
funciones disponibles en SQL. • Incluir una variedad de funciones de
caracteres, numéricas y de datos en sentencias SELECT.
• Explicar las funciones de conversión y la manera en que podrían ser usadas.
3-3 Copyright © Oracle Corporation, 1998. All rights reserved.
Funciones SQL
Función Entrada
arg 1
arg 2
arg n
Las Funciones Realizan Acciones
Salida
Valor Resultado
3-4 Copyright © Oracle Corporation, 1998. All rights reserved.
Dos Tipos de Funciones SQL
Funciones
Funciones a Nivel de Fila
Funciones a Nivel de
Múltiples Filas
3-5 Copyright © Oracle Corporation, 1998. All rights reserved.
Funciones a Nivel de Fila: Sintaxis
• Manipulan ítems de datos. • Aceptan argumentos y devuelven un valor. • Actúan sobre cada fila retornada. • Devuelven un resultado por fila. • Modifican el tipo de datos. • Pueden estar anidadas. Sintaxis:
function_name (column|expression, [arg1, arg2,...])
3-6 Copyright © Oracle Corporation, 1998. All rights reserved.
Funciones a Nivel de Fila
Conversion
Character
Number
Date
General Single-row functions
3-7 Copyright © Oracle Corporation, 1998. All rights reserved.
Funciones de Caracteres Funciones de
Caracteres
LOWER UPPER INITCAP
CONCAT SUBSTR LENGTH INSTR LPAD
Funciones de Conversión Funciones de Manipulación de Caracteres
3-8 Copyright © Oracle Corporation, 1998. All rights reserved.
Función Resultado
Funciones de Conversión
Conversión de cadenas de caracteres
LOWER('Curso SQL') UPPER ('Curso SQL') INITCAP('Curso SQL')
curso sql CURSO SQL Curso Sql
3-9 Copyright © Oracle Corporation, 1998. All rights reserved.
Uso de Funciones de Conversión Visualizar el número de empleado, nombre y nº de departamento de “Blake”. SQL> SELECT empno, ename, deptno 2 FROM emp 3 WHERE ename = 'blake'; no rows selected
EMPNO ENAME DEPTNO --------- ---------- --------- 7698 BLAKE 30
SQL> SELECT empno, ename, deptno 2 FROM emp 3 WHERE LOWER(ename) = 'blake';
3-10 Copyright © Oracle Corporation, 1998. All rights reserved.
CONCAT('Good', 'String') SUBSTR('String',1,3) LENGTH('String') INSTR('String', 'r') LPAD(sal,10,'*')
GoodString Str 6 3 ******5000
Función Resultado
Funciones de Manipulación de Caracteres
Manipulación de cadenas de caracteres
3-11 Copyright © Oracle Corporation, 1998. All rights reserved.
Uso de Funciones de Manipulación de Caracteres
SQL> SELECT ename, CONCAT (ename, job), LENGTH(ename), 2 INSTR(ename, 'A') 3 FROM emp 4 WHERE SUBSTR(job,1,5) = 'SALES';
ENAME CONCAT(ENAME,JOB) LENGTH(ENAME) INSTR(ENAME,'A') ---------- ------------------- ------------- ---------------- MARTIN MARTINSALESMAN 6 2 ALLEN ALLENSALESMAN 5 1 TURNER TURNERSALESMAN 6 0 WARD WARDSALESMAN 4 2
3-12 Copyright © Oracle Corporation, 1998. All rights reserved.
Funciones Numéricas • ROUND: Redondea un valor al decimal
especificado ROUND(45.926, 2) 45.93
• TRUNC: Trunca un valor en el decimal especificado TRUNC(45.926, 2) 45.92
• MOD: Devuelve el resto de la división MOD(1600, 300) 100
3-13 Copyright © Oracle Corporation, 1998. All rights reserved.
Uso de la Función ROUND
Visualizar el valor 45.923 redondeado a centenas, 0, y 10 posiciones decimales.
SQL> SELECT ROUND(45.923,2), ROUND(45.923,0), 2 ROUND(45.923,-1) 3 FROM SYS.DUAL;
ROUND(45.923,2) ROUND(45.923,0) ROUND(45.923,-1) --------------- -------------- ----------------- 45.92 46 50
3-14 Copyright © Oracle Corporation, 1998. All rights reserved.
SQL> SELECT TRUNC(45.923,2), TRUNC(45.923), 2 TRUNC(45.923,-1) 3 FROM SYS.DUAL;
TRUNC(45.923,2) TRUNC(45.923) TRUNC(45.923,-1) --------------- ------------- --------------- 45.92 45 40
Uso de la Función TRUNC
Visualizar el valor 45.923 con TRUNC a centenas, 0, y 10 posiciones decimales.
3-15 Copyright © Oracle Corporation, 1998. All rights reserved.
Uso de la Función MOD Calcular el resto de dividir el salario entre la comisión de cada empleado, cuyo trabajo sea “VENDEDOR”.
SQL> SELECT ename, sal, comm, MOD(sal, comm) 2 FROM emp 3 WHERE job = 'SALESMAN';
ENAME SAL COMM MOD(SAL,COMM) ---------- --------- --------- ------------- MARTIN 1250 1400 1250 ALLEN 1600 300 100 TURNER 1500 0 1500 WARD 1250 500 250
3-16 Copyright © Oracle Corporation, 1998. All rights reserved.
Trabajando con Fechas
• Oracle almacena fechas en un formato numérico interno. – Siglo, año, mes, día, horas, minutos,
segundos. • El formato de fecha por defecto es DD-MON-YY. • SYSDATE es una función que devuelve fecha y
hora. • DUAL es una tabla virtual de la Base de Datos,
que puede ser usada para inspeccionar SYSDATE.
3-17 Copyright © Oracle Corporation, 1998. All rights reserved.
Operadores Aritméticos de Fechas
• Sumar o restar un número a ó de una fecha da por resultado una fecha.
• Restar dos fechas para encontrar la cantidad de días entre esas fechas.
• Sumar horas a una fecha dividiendo la cantidad de horas por 24.
3-18 Copyright © Oracle Corporation, 1998. All rights reserved.
Uso de Operadores Aritméticos en Fechas
SQL> SELECT ename, (SYSDATE-hiredate)/7 WEEKS 2 FROM emp 3 WHERE deptno = 10;
ENAME WEEKS ---------- --------- KING 830.93709 CLARK 853.93709 MILLER 821.36566
3-19 Copyright © Oracle Corporation, 1998. All rights reserved.
Funciones de Fecha
Número de meses entre dos fechas MONTHS_BETWEEN
ADD_MONTHS
NEXT_DAY
LAST_DAY
ROUND
TRUNC
Agregar meses según calendario, a una fecha Próximo día de la fecha especificada
Ultimo día del mes
Redondea una fecha
Trunca una fecha
FUNCION DESCRIPCION
3-20 Copyright © Oracle Corporation, 1998. All rights reserved.
• MONTHS_BETWEEN ('01-SEP-95','11-JAN-94')
Uso de Funciones de Fecha
• ADD_MONTHS ('11-JAN-94',6)
• NEXT_DAY ('01-SEP-95','FRIDAY')
• LAST_DAY('01-SEP-95')
19.6774194
'11-JUL-94'
'08-SEP-95'
'30-SEP-95'
3-21 Copyright © Oracle Corporation, 1998. All rights reserved.
Uso de Funciones de Fecha
• ROUND('25-JUL-95','MONTH')
• ROUND('25-JUL-95','YEAR')
• TRUNC('25-JUL-95','MONTH')
• TRUNC('25-JUL-95','YEAR')
01-AUG-95
01-JAN-96
01-JUL-95
01-JAN-95
3-22 Copyright © Oracle Corporation, 1998. All rights reserved.
Funciones de Conversión
Conversión Implícita
Conversión Explícita
Conversión de Tipos de Datos
3-23 Copyright © Oracle Corporation, 1998. All rights reserved.
Conversión Implícita del Tipo de Dato
Para asignaciones, Oracle automáticamente puede convertir:
VARCHAR2 o CHAR
De A
VARCHAR2 o CHAR
NUMBER
DATE
NUMBER
DATE
VARCHAR2
VARCHAR2
3-24 Copyright © Oracle Corporation, 1998. All rights reserved.
Conversión Explícita del Tipo de Dato
Para evaluar una expresión, Oracle automáticamente puede convertir:
VARCHAR2 o CHAR
De A
VARCHAR2 o CHAR
NUMBER
DATE
3-25 Copyright © Oracle Corporation, 1998. All rights reserved.
Conversión Explícita del Tipo de Dato
NUMBER CHARACTER
TO_CHAR
TO_NUMBER
DATE
TO_CHAR
TO_DATE
3-26 Copyright © Oracle Corporation, 1998. All rights reserved.
Función TO_CHAR con Fechas
El formato: • Debe estar encerrado entre comillas simples y
es sensible a mayúsculas/minúsculas. • Puede incluir cualquier elemento de formato
de fecha válido. • Tiene un elemento fm (fill mode) para eliminar
espacios en blanco de relleno o suprimir ceros a la izquierda.
• Está separado de la fecha por una coma.
TO_CHAR(date, 'fmt')
3-27 Copyright © Oracle Corporation, 1998. All rights reserved.
YYYY
Elementos del Modelo Formato de Fecha
YEAR
MM
MONTH
DY
DAY
Año completo en número
Año en letras
Nº del mes con dos dígitos
Abreviatura de tres letras del día de la semana
Nombre completo del día
Nombre completo del mes
3-28 Copyright © Oracle Corporation, 1998. All rights reserved.
Elementos del Formato de Fechas
• Obtención de la hora:
• Añadir cadenas de caracteres encerrándolas entre dobles comillas.
• Sufijo que permita obtener el nº del día en letra.
HH24:MI:SS AM 15:45:32 PM
DD "of" MONTH 12 of OCTOBER
ddspth fourteenth
3-29 Copyright © Oracle Corporation, 1998. All rights reserved.
Formato de Fecha RR
Año Actual 1995 1995 2001 2001
Fecha Específicada 27-OCT-95 27-OCT-17 27-OCT-17 27-OCT-95
Formato RR 1995 2017 2017 1995
FormatoYY 1995 1917 2017 2095
Si los 2 dígitos especificados del año están
Si los 2 dígitos del año corriente están
0-49
0-49 50-99
50-99
La fecha devuelta corresponde al siglo corriente. La fecha devuelta corresponde al siglo posterior al corriente.
La fecha devuelta corresponde al siglo anterior al corriente.
La fecha devuelta corresponde al siglo corriente.
3-30 Copyright © Oracle Corporation, 1998. All rights reserved.
Función TO_CHAR con Fechas
SQL> SELECT ename, 2 TO CHAR(hiredate, 'fmDD Month YYYY') HIREDATE 3 FROM emp;
ENAME HIREDATE ---------- ----------------- KING 17 November 1981 BLAKE 1 May 1981 CLARK 9 June 1981 JONES 2 April 1981 MARTIN 28 September 1981 ALLEN 20 February 1981 ... 14 rows selected.
3-31 Copyright © Oracle Corporation, 1998. All rights reserved.
Función TO_CHAR con Números
Use estos formatos con la función TO_CHAR para mostrar un carácter como un número.
TO_CHAR(number, 'fmt')
9 0 $ L . ,
Representa un número Fuerza a que se muestre el 0 (cero) Signo del dólar Usa el símbolo de moneda local Imprime el punto decimal Imprime el indicador de millar
3-32 Copyright © Oracle Corporation, 1998. All rights reserved.
SQL> SELECT TO_CHAR(sal,'$99,999') SALARY 2 FROM emp 3 WHERE ename = 'SCOTT';
SALARY -------- $3,000
Uso de la Función TO_CHAR con Números
3-33 Copyright © Oracle Corporation, 1998. All rights reserved.
Funciones TO_NUMBER y TO_DATE
• Convierte una cadena de caracteres a un formato numérico usando la función TO_NUMBER
TO_NUMBER(char)
• Convierte una cadena de caracteres a un formato de fecha usando la función TO_DATE
TO_DATE(char[, 'fmt'])
3-34 Copyright © Oracle Corporation, 1998. All rights reserved.
NVL Function
Convierte un nulo a un valor. • Los tipos de datos pueden ser de fecha,
cadenas de caracteres y números. • Los tipos de datos deben coincidir:
– NVL(comm,0) – NVL(hiredate,'01-JAN-97') – NVL(job,'No Job Yet')
3-35 Copyright © Oracle Corporation, 1998. All rights reserved.
SQL> SELECT ename, sal, comm, (sal*12)+NVL(comm,0) 2 FROM emp;
Uso de la Función NVL
ENAME SAL COMM (SAL*12)+NVL(COMM,0) ---------- --------- --------- -------------------- KING 5000 60000 BLAKE 2850 34200 CLARK 2450 29400 JONES 2975 35700 MARTIN 1250 1400 16400 ALLEN 1600 300 19500 ... 14 rows selected.
3-36 Copyright © Oracle Corporation, 1998. All rights reserved.
La Función DECODE
Hace las veces de sentecias CASE o IF-THEN-ELSE, para facilitar consultas condicionales.
DECODE(col/expression, search1, result1 [, search2, result2,...,] [, default])
3-37 Copyright © Oracle Corporation, 1998. All rights reserved.
Uso de la Función DECODE
SQL> SELECT job, sal, 2 DECODE(job, 'ANALYST' SAL*1.1, 3 'CLERK', SAL*1.15, 4 'MANAGER', SAL*1.20, 5 SAL) 6 REVISED_SALARY 7 FROM emp;
JOB SAL REVISED_SALARY --------- --------- -------------- PRESIDENT 5000 5000 MANAGER 2850 3420 MANAGER 2450 2940 ... 14 rows selected.
3-38 Copyright © Oracle Corporation, 1998. All rights reserved.
Anudamiento de Funciones • Las funciones a nivel de fila pueden ser
anidadas hasta cualquier nivel. • Las funciones anidadas son evaluadas desde
el nivel más profundo al nivel menos profundo.
F3(F2(F1(col,arg1),arg2),arg3)
Paso 1 = Resultado 1!Paso 2 = Resultado 2!
Paso 3 = Resultado 3!
3-39 Copyright © Oracle Corporation, 1998. All rights reserved.
Funciones Anidadas
SQL> SELECT ename, 2 NVL(TO_CHAR(mgr),'No Manager') 3 FROM emp 4 WHERE mgr IS NULL;
ENAME NVL(TO_CHAR(MGR),'NOMANAGER') ---------- ----------------------------- KING No Manager
3-40 Copyright © Oracle Corporation, 1998. All rights reserved.
Resumen
Use funciones para: • Realizar cáculos sobre los datos • Modificar datos de forma individual • Manipular la salida de grupos de
registros • Alterar formatos de fecha en su
visualización • Convertir tipos de datos de columnas
3-41 Copyright © Oracle Corporation, 1998. All rights reserved.
Visión General de la Práctica
• Crear consultas que requieran el uso de funciones numéricas, de caracteres y de fechas.
• Usar concatenación con funciones. • Escribir consultas case insensitive para probar
la utilidad de las funciones de caracteres. • Ejecutar cálculos sobre años y meses de
servicio para un empleado. • Determinar la fecha de revisión para un
empleado.