Usando Funciones SQL de una Fila
de Caracteres, Números, Fechas y Conversión de Tipos de Datos
BDY1102
Objetivos de la Clase
1. Describir las características generales de las Funciones SQL.2. Describir las características de las Funciones SQL que operan con una fila.3. Describir los tipos de Funciones que operan con una sola fila: de caracteres, números, fechas, conversión y generales. 4. Cómo convertir, reemplazar o manipular caracteres en sentencias SQL5. Cómo manipular números en sentencias SQL.6. Cómo efectuar operaciones con fechas en sentencias SQL.7. Describir las características generales de las Funciones SQL.8. Describir las características de las Funciones SQL que operan con una fila.9. Describir los tipos de Funciones que operan con una sola fila: de caracteres, números, fechas, conversión y generales. 10. Cómo convertir, reemplazar o manipular caracteres en sentencias SQL11.Cómo manipular números en sentencias SQL.12. Cómo efectuar operaciones con fechas en sentencias SQL.
Funciones SQL
Funciones SQL
Funciones SQL
Funciones SQL de una Fila
Funciones SQL
Funciones SQL
Funciones para trabajar con Caracteres
Funciones de Caracteres
Aceptan como argumentos de entrada datos del tipo caracter y retornan datos del tipo caracteres o números
Funciones de Caracteres
Funciones de Caracteres
- Funciones de conversión del texto a mayúsculas y minúsculas
- Funciones de reemplazo o manipulación de caracteres:
Funciones de Caracteres
SELECT last_name, UPPER(last_name), job_id, LOWER(job_id), INITCAP(job_id) FROM employees;
Funciones de Caracteres
Funciones de Caracteres
Funciones de Caracteres
Funciones para trabajar con Números
Funciones de Números
Funciones de Números
Funciones de Números
Funciones para trabajar con Fechas
Funciones de Fechas
Funciones de Fechas
Funciones de Fechas
Funciones de Fechas
Funciones de Fechas
Funciones de Fechas
Función EXTRACT para trabajar con Fechas
Función EXTRACT para trabajar con Fechas
Funciones de Conversiónde Tipos de Datos
Conversión de Tipos de Datos Implícita
Conversión de Tipos de Datos
Conversión de Tipos de Datos explícita
Uso Función TO_CHAR con Fechas
Uso Función TO_CHAR con Fechas
Uso Función TO_CHAR con Fechas
Uso Función TO_CHAR con Números
Uso Función TO_CHAR con números
Uso de Función TO_NUMBER con Caracteres
Uso de Función TO_DATE con Caracteres
Uso de Función TO_DATE con Caracteres
Funciones para validarvalores Nulos
Funciones Generales
Funciones Generales: NVL
Funciones Generales: NVL
Funciones Generales: NVL2
Funciones Generales: NULLIF
Funciones Generales: COALESCE
Funciones Anidadas
Funciones Anidadas
Implementar Condición Lógicaen Sentencias SQL
Expresiones Condicionales
Expresiones Condicionales: CASE
Expresiones Condicionales: CASE
Expresiones Condicionales: CASE
Expresiones Condicionales: DECODE
Resumen de la Clase
1. Se describieron las características generales de las Funciones SQL.2. Se describieron las características de las Funciones SQL que operan con una fila.3. Se describieron los tipos de Funciones que operan con una sola fila: de caracteres, numéricas, de fechas, de conversión y generales. 4. Se explicó cómo convertir, reemplazar o manipular caracteres en sentencias SQL5. Se explicó cómo manipular números en sentencias SQL.6. Se explicó cómo efectuar operaciones con fechas en sentencias SQL.7. Se explicó cómo convertir en forma explícita un tipo de dato numérico a carácter en una sentencia SQL.
8. Se explicó cómo convertir en forma explícita un tipo de dato caracter a número en una sentencia SQL.
9. Se explicó cómo convertir en forma explícita un tipo de dato fecha a caracter en una sentencia SQL.
10. Se explicó cómo convertir en forma explícita un tipo de dato caracter a fecha en una sentencia SQL.
11. Se explicó cómo definir un valor cuando alguna expresión es NULA en una sentencia SQL.
12. Se explicó cómo trabajar con Funciones Anidadas en una sentencia SQL.
13. Se explicó cómo implementar una condición lógica IF-THEN-ELSE en una sentencia SQL., con CASE
Funciones SQL de una Fila
- Se pueden usar en las cláusulas SELECT, WHERE y ORDER BY.
- En un sentencias UPDATE se puede usar en la cláusula SET.
- Actúan sobre cada fila afectada por una sentencia SELECT, INSERT o UPDATE y por cada una de las filas entrega un resultado.
- Aceptan argumentos que pueden ser: un valore constante, una variable, el nombre de una columna o una expresión.
- Manipulan datos y pueden modificar el tipo de datos
- Se pueden usar en forma anidada. No hay límite del número de funciones anidadas. El resultado de una función es el argumento para la otra.
En la Sintaxis:
- nombre_función: es la función SQL que se va a utilizar
- argumentoN: es el valor que requiere la función para realizar una acción y retornar un resultado. Pueden ser constantes proporcionadas por el usuario, valores variables, nombre de columnas y expresiones.
Funciones SQL de una Fila
Funciones de caracteres: aceptan un caracter como argumento o parámetro de entrada y retorna un valor de tipo caracter y número.
Funciones numéricas: aceptan como entrada un número y retornan un número.
Funciones de fechas: operan sobre valores de tipo fecha (DATE). Todas las funciones de fechas retornan una fecha excepto la función MONTHS_BETWEEN que retorna un número.
Funciones de Conversión: transforman un valor de un tipo a otro.
Funciones Generales: validan una columna o expresión para retornar un valor.
Funciones de Caracteres
Funciones de conversión del texto a mayúsculas y minúsculas:
- LOWER: Convierte el texto a minúsculas
- UPPER: Convierte el texto a mayúsculas
- INITCAP: Coloca la primera letra de cada palabra en mayúscula.
Funciones de reemplazo o manipulación de caracteres:
- CONCAT: concatena el valor del primer caracter con el valor del segundo caracter. Equivalente al operador de concatenación ||.
- SUBSTR: obtiene los n siguientes caracteres de la columna o texto a partir de la posición m. Si no se indica n, se recuperan los caracteres desde la posición m hasta el final.
- LENGTH: obtiene el número de caracteres o largo de la expresión.
- RTRIM–LTRIM: Elimina los espacios en blanco a la derecha (RTRIM) ó a la izquierda (LTRIM) de la columna o expresión.
- REPLACE: busca el texto especificado en la columna o expresión y lo cambia por el texto indicado como reemplazo.
- TRIM: Elimina los espacios en blanco a la izquierda y a la derecha del texto o columna.
- LPAD-RPAD: rellena el texto a la izquierda (LPAD) ó a la derecha (RPAD) n posiciones con el caracter indicado.
- INSTR: obtiene la posición en la se encuentra el texto buscado en la columna o expresión. Opcionalmente se puede indicar la posición inicial (m) desde donde se desea comenzar la búsqueda y la ocurrencia o número de posición (n) del texto buscado. Por defecto ambas posiciones es 1.
- TRIM (‘caracter’ FROM columna|expresión): Elimina el carácter especificado de la derecha e izquierda de la columna o expresión.
Funciones de Números
ROUND: redondea la columna, expresión o valor a n posiciones decimales. . Si no se especifica n o su valor es cero, el valor se redondea al valor entero. Si n es negativo, los números a la izquierda del punto decimal se redondean a decenas, centenas etc.
TRUNC: Trunca la columna, expresión o valor a n posiciones decimales. Si no se especifica n decimales el valor es 0, por lo tanto se trunca el valor sólo en su parte entera. Por defecto en cero. Si n es negativo trunca hacia la izquierda del punto decimal (coloca cero).
MOD: Devuelve el resto que resulta de dividir m por n.
En el ejemplo, las funciones ROUND y TRUNC se utilizan para redondear y truncar el valor 1234.5678 de diferentes formas (recordar que en Oracle el punto corresponde a decimales). La primera función ROUND redondea el valor en dos decimales, la segunda función ROUND redondea al valor entero (sin decimales, esto es similar a ROUND(1234.5678,0), la tercera función ROUND redondeado el valor en su parte entera a la décima más cercana a 35, la primera función TRUNC muestra el valor truncado a dos decimales, la segunda función TRUNC muestra el valor truncado solo en su valor entero (esto es similar a TRUNC(1234.5678,0) y la última función TRUNC muestra el valor truncado en su parte entera y reemplazada los dos últimos números por ceros.
Funciones de fecha
- La Base de Datos Oracle almacena las fechas en un formato numérico interno: siglo, año, mes, día, horas, minutos y segundos.
- El formato por defecto de visualización de las fechas es :DD-MON-RR, donde DD corresponde al día, MON al mes en 3 letras y RR al año en 2 dígitos según el siglo.
- Cuando se desea generar una condición por una fecha en particular, ésta debe ir entre comillas simples.
- Las fechas en la Base de Datos se almacenan como números, por lo tanto se pueden realizar cálculos usando operadores aritméticos.
Funciones de fecha
- SYSDATE: retorna la fecha y hora actual de la Base de Datos
- MONTHS_BETWEEN: obtiene la diferencia en meses entre las dos fechas. El resultado puede ser positivo o negativo. Si fecha1 es posterior a fecha2, el resultado es positivo, si fecha1 es anterior a fecha2, el resultado es negativo. La parte no entera del resultado representa una porción de la mes.
- ADD_MONTHS: añade a la fecha el número de meses indicado por n. El valor de n debe ser un número entero y puede ser negativo. Si es negativo significa que está restando meses a la fecha.
- NEXT_DAY: retorna la fecha del día de la semana del argumento busca y que es posterior a la fecha entregada. El día puede ser el nombre del día (inglés o español según como esté configurada la Base de Datos) o el número del día de la semana 1=Lunes, 2=Martes etc.
- LAST_DAY: obtiene el último día del mes de la fecha especificada.
- ROUND: redondea la fecha al formato indicado. El formato puede ser:
- YEAR: redondea la fecha al año. - MONTH: redondea la fecha al mes.
- TRUNC: trunca la fecha al formato indicado. El formato es el mismo al usado por ROUND
Funciones de fecha
- En el primer ejemplo, la función MONTHS_BETWEEN muestra cuántos meses existen entre el 01 de enero del 2019 y el 01 de octubre del 2018 (dependiendo de las fechas comparadas, la función puede retornar un valor entero o con decimales), la función ADD_MONTHS muestra la fecha que corresponde al sumar 6 meses a la fecha 20 de septiembre del 2019, la función NEXT_DAY muestra la fecha que corresponde al día domingo posterior al 11 de marzo del 2019 y la función LAST_DAY muestra el último día de febrero del 2019.
- En el segundo ejemplo, al utilizar las funciones ROUND y TRUNC con fechas el resultado estará basado en redondear las fechas al mes o al año y mostrar el primer día del mes o del año. La primera función redondea la fecha 10/10/2019 al mes y como el día es menor a 16 el resultado es 01/10/2019, si el día fuera mayor a 15 entonces el resultado sería 01/11/2019. La segunda función redondea la fecha al año y como el mes es 10 el resultado es 01/01/2020, si el mes fuera menor a 7 el resultado sería 01/01/2019. La tercera función trunca la fecha al mes (muestra el primer día del mes) por eso el resultado es el primer día del mes de octubre. La última función trunca la fecha al año (mostrar el primer día del año) por esta razón muestra el primer día del año 2019.
Funciones de fecha
- Las fechas en la Base de Datos se almacenan como números, por lo tanto se pueden realizar cálculos usando operadores aritméticos:
- Fecha + Número: retorna una fecha. Suma un número de días a la fecha.
- Fecha - Número: retorna una fecha. Resta un número de días desde la fecha.
- Fecha - Fecha: retorna el número de días. Resta una fecha desde otra.
- En el ejemplo, la sentencia muestra el apellido del empleado, la fecha de contrato, el número de semanas (entre la fecha actual y la fecha) de contrato que lleva contratado, la fecha resultante al restar 2 días a la fecha de contrato y la fecha resultante al sumar 10 días a la fecha de contrato días de cada empleado del departamento 90. Considerar que el SYSDATE retornó 10/10/2019.
Funciones SQL
Las funciones son una característica muy poderosa del SQL. A partir de parámetros que requiere (cuando se requiera) para realizar una acción y entregar un resultado.
- Las funciones pueden ser usadas para:
- Realizar cálculos sobre los datos.
- Modificar datos individuales.
- Manipular la salida para grupos de filas.
- Formatear fechas y números para ser visualizadas.
- Convertir tipos de datos de columnas.
Las funciones pueden opcionalmente recibir argumentos (valores) como entrada, pero SIEMPRE retornan un valor.Conversión de Tipos de Datos
En algunos casos, el servidor Oracle usa tipos de datos distintos a los que se requieren. Cuando esto sucede, se debe convertir al tipo de datos que se requiere.
La conversión de un tipo de dato a otro puede ser efectuada en forma implícita o automática por el servidor Oracle o en forma explícita por el usuario usando funciones de conversión.
Conversión de Tipos de Datos Implícita
La asignación de valores tiene éxito si el servidor Oracle puede convertir el tipo de dato del valor usado en el tipo de dato que se encuentra almacenado.
El servidor Oracle puede convertir implícitamente (en forma automática) siempre que el valor a convertir tenga un formato válido al tipo de dato en el que se convertirá.
Su pueden convertir en forma explícita:
- De VARCHAR2 o CHAR a NUMBER
- De VARCHAR2 o CHAR a DATE
- DE NUMBER A VARCHA2
- DE DATE A VARCHAR2
En la sentencia del ejemplo, se obtiene información de los empleados contratados después del '05/03/2008’ . La fecha es una cadena de caracteres que Oracle convierte a fecha al momento de comparar. Esto lo puede hacer porque la fecha de la condición tiene un formato de fecha válido para poder ser convertido a tipo de dato DATE.
Conversión de Tipos de Datos Explícita
Oracle proporciona tres funciones para convertir en forma explícita un tipo de dato en otro:
TO_CHAR: Obtiene un texto a partir de un número o fecha. Opcionalmente se puede dar un formato específico de conversión.
TO_NUMBER: Convierte textos en números, indicándole, si se desea, el formato de salida.
TO_DATE: Convierte textos en fechas, indicándole, si se desea, el formato de salida.
Uso de Función TO_CHAR con Fechas
Los elementos más usados en el formato de conversión son lo siguientes:
- YYYY: muestra el año en 4 dígitos
- YEAR: muestra el año en palabras. Si las dos primeras letras son mayúsculas, se mostrará todo en mayúscula. Si la segunda letra es minúscula, la primera letra se muestra en mayúscula y todo el resto en minúscula.
- MM: muestra el mes en dos dígitos
- MONTH: muestra el mes en palabras. Si las dos primeras letras son mayúsculas, se mostrará todo en mayúscula. Si la segunda letra es minúscula, la primera letra se muestra en mayúscula y todo el resto en minúscula.
- MON: muestra el mes abreviado en tres letras. Si las dos primeras letras son mayúsculas, se mostrará todo en mayúscula. Si la segunda letra es minúscula, la primera letra se muestra en mayúscula y todo el resto en minúscula.
- DY: muestra el día de la semana en tres letras. Si la segunda letra es minúscula, la primera letra se muestra en mayúscula y el resto en minúscula.
- DAY: muestra el día en palabras. Si las dos primeras letras son mayúsculas, se mostrará todo en mayúscula. Si la segunda letra es minúscula, la primera letra se muestra en mayúscula y todo el resto en minúscula.
- DD. muestra el día en dos dígitos.
- Q (o q): muestra a qué trimestre corresponde la fecha.
- AM o PM: muestra el indicador del meridiano
- HH, HH12 o HH24: muestra la hora, la hora de 1-12 y muestra la hora de 0-23 respectivamente.
- MI: muestra los minutos
- SS: muestra los segundos
- Los símbolos / . Y , : estos símbolos se muestra como parte del formato.
- “caracteres_a_visualiza”: texto fijo que se muestra en el resultado
Uso de Función TO_CHAR con Fechas
En el ejemplo, la sentencia se ejecutó 11 de Octubre del 2019 a las 15:21). El primer formato de fecha de contrato se muestra en número (año en 4 dígitos) y separado por /. El segundo formato muestra el día y año de contrato en número y el mes en palabras (la primera letra en mayúscula y el resto en minúscula. El tercer formato muestra la fecha y hora del sistema incorporando las palabras de. En el cuarto formato de fecha muestra la fecha y hora del sistema pero la hora en formato de 12 horas con el indicador de meridiano. Como la sentencia se ejecutó a las 3 de la tarde automáticamente aparece en indicador PM.
Uso de Función TO_CHAR con Fechas
En el ejemplo, la sentencia se ejecutó 11 de Octubre del 2019 a las 15:21). El primer formato de fecha de contrato se muestra en número (año en 4 dígitos) y separado por /. El segundo formato muestra el día y año de contrato en número y el mes en palabras (la primera letra en mayúscula y el resto en minúscula. El tercer formato muestra a que trimestre del año corresponde la fecha de contrato del empleado, El cuarto formato muestra la fecha y hora del sistema incorporando las palabras de. En el quinto formato de fecha muestra la fecha y hora del sistema pero la hora en formato de 12 horas con el indicador de meridiano. Como la sentencia se ejecutó a las 6 de la tarde automáticamente aparece en indicador PM.
Uso de Función TO_CHAR con números
Cuando se desean trabajar los valores numéricos como una cadena de caracteres se deben convertir esos números a un tipo de dato caracter utilizando la función TO_CHAR. Esta función convierte un dato de tipo NUMBER a un dato de tipo VARCHAR2.
El formato de conversión:
- Debe ir entre comillas simples.
- Puede incluir cualquier elemento de formato de número válido.
- Debe ir separado del valor número por una coma.
Los elementos para formatos de números válidos
- 9: Representa un número.
- 0: Fuerza a que se muestre un cero.
- $: Coloca un signo dólar.
- L: Coloca el símbolo de moneda local.
- G: muestra un punto como separador de grupo en la posición especificada.
- . : Coloca un punto decimal en la posición especificada. En una Base de Datos Oracle el punto es el separador de decimales.
- , : Coloca un separador de miles. En una Base de Datos Oracle la coma es el separador de miles.
- D: coloca el carácter decimal en la posición especificada (defecto es punto).
- V: multiplica por 10 n veces (n = número de 9 o 0 (ceros) después de la V)
Uso de Función TO_CHAR con números
En la primera sentencia del ejemplo, la muestra el salario del empleado Ernest en 4 formatos diferentes. En el formato1 se muestra el salario tal como se encuentra se almacenado en la tabla (cada 9 representa un dígito del salario). En el formato2 se antepondrán tantos ceros como falten para mostrar el valor en 7 caracteres. En el formato3 se antepondrá el signo dólar y se mostrará un punto como separador de miles. En el formato4 se antepondrá el símbolo de moneda local y se mostrarán los separadores de miles y de decimales por defecto en Oracle, es decir la coma y el punto respectivamente.
En la sentencia del segundo ejemplo, en el formato1 se antepondrán ceros hasta completar 4 caracteres antes del separador de miles. En el formato2 se mostrará un punto como separador de miles y una coma para los decimales y en formato3 dado que la cantidad entre 9 y ceros después de V son tres, se mostrará el valor del salario multiplicado por 1000.
Uso de Función TO_NUMBER con Caracteres
Para poder convertir un literal o una columna de tipo de dato caracter a número se debe usar la función TO_NUMBER. La restricción es que el valor del caracter a convertir debe tener un formato de número válido. El formato de conversión es opcional sin embargo, si se desea dar un formato en particular, los elementos que se pueden usar son los mismo que se usan para números con la función TO_CHAR.
En el ejemplo, se tiene un literal que es un número que se muestra primero como un carácter y usando la función TO_NUMBER ese literal se convierte en número. Esto sirve cuando por ejemplo a partir de una planilla Excel se deben insertar valores que tienen el formato que se muestra en el ejemplo y se deben insertar en una columna de tipo NUMBER entonces se debe efectuar la conversión explícita a número.
Uso de Función TO_DATE con Caracteres
Para poder convertir un literal o una columna de tipo de dato caracter a fecha se debe usar la función TO_DATE. La restricción es que el valor del caracter a convertir debe tener un formato de fecha válido. El formato de conversión es opcional sin embargo, si se desea dar un formato en particular, los elementos que se pueden usar son los mismo que se usan para fecha con la función TO_CHAR.
En el ejemplo, se tiene un literal que es una fecha pero no en el formato tradicional, por lo tanto cuando se trata de convertir ese caracter a fecha se produce un error. Par poder mostrar ese literal como fecha, se debe le dar un formato de conversión válido al valor literal.
Funciones Generales
Estas funciones trabajan con cualquier tipo de datos y se relacionan con el uso de valores nulos en la lista de expresiones:
- NVL: Si el valor de expr1 es NULO , devuelve el valor de expr2. Si no es NULO retorna el valor de expr1.
- NVL2: Devuelve el valor de expr2 si el valor de expr1 no es NULO. Si expr1 es NULO devuelve el valor de expr3.
- NULLIF: devuelve NULO si expr1 y expr2 son iguales. Si no lo son devuelve el valor de expr1.
- COALESCE: retorna el valor de expr1 si no es NULO. Si es NULO devuelve el valor de expr2 si no es NULO. Si los valores de expr1 y expr2 son NULOS devuelve el valor de expr3 sino es NULO y así sucesivamente.
Funciones Generales: NVL
Esta función la expr1. Si ésta es nula retorna el valor de expr2. Si expr1 no es nula la función retorna el valor de expr1.
Ambos expresiones deben ser del mismo tipo de dato.
En la sentencia del ejemplo, la función NVL retorna cero cuando la identificación del jefe sea nula es decir, cuando el empleado no posee jefe.
Funciones Generales: NVL
En el ejemplo, no todos lo empleados tiene un porcentaje de comisión asignado, por esa razón al obtener salario anual para esos empleados el valor es NULO (cualquier operación matemática con valores nulos el resultado es NULO). La primera función NVL retorna cero cuando el porcentaje de comisión es NULO. En la segunda función NVL retorna cero cuando el porcentaje de comisión es NULO y es por era razón que para estos empleados el salario anual es el valor del salario que posee multiplicado por 12.
Funciones Generales: NVL2
Esta función evalúa la primera expresión (expr1). Si no es Nula la función NVL2 retorna la segunda expresión (expr2). Si la primera expresión es Nula entonces la función retorna la tercera expresión (expr3).
El argumento expr1 puede ser cualquier tipo de dato. Los argumentos expr2 y expr3 pueden ser de cualquier tipo de dato excepto LONG.
El argumento expr3 debe ser del mismo tipo de dato de expr2 a menos que expr3 sea Nulo.
El tipo de dato del valor que retorna la función es siempre del mismo tipo de datos de expr2.
En el ejemplo, la función NVL2 mostrará el string SALARIO+COMISION si el porcentaje de comisión no es nulo. Si el porcentaje de comisión es nulo mostrará el string SOLO SALARIO para los empleados con identificación 100, 101, 114, 147,148 o 149.
Funciones Generales: NULLIF
La función NULLIF compara dos expresiones (expr1 y expr2 ). Si son iguales, la función retorna NULO.
Si ellas no son iguales, la función retorna la primera expresión (expr1).
Los tipos de datos de ambas expresiones deben ser los mismos.
No se puede especificar NULL para la primera expresión (expr1).
En la sentencia del ejemplo, la función NULLIF compara el resultado de multiplicar el salario por el porcentaje de comisión con el valor cero. Si el resultado de la expresión matemática es cero entonces mostrará NULO y si son diferentes mostrará el valor de la expresión matemática. La información se muestra para los empleados con identificación 100, 104, 106, 110, 166 o 167
Funciones Generales: COALESCE
La función COALESCE retorna la primera expresión NO NULA de la lista.
Todas las expresiones deben tener el mismo tipo de dato.
En el ejemplo, la función COALESCE mostrará el valor del porcentaje de comisión si no es nulo, de lo contrario mostrará el valor de la identificación del jefe si no es nulo y si ambos valores son nulos mostrará 9999 para los empleados que trabajan en el departamento 10, 20 o 90.
Funciones de Anidadas
- Las funciones SQL de una fila se pueden anidar y su ejecución es desde el nivel más interno al más externo.
- Esto significa que el resultado de una función se usa como parámetro para la otra función y así sucesivamente.
- Este tipo de funciones se pueden anidar sin limites.
En el ejemplo, la primera función que se ejecuta es la que transforma a caracter la columna manager_id (TO_CHAR(manager_id)). La segunda función que se ejecuta NVL y que retornará el mensaje NO POSEE JEFE si manager_id es NULO. Como manager_id es un número y el mensaje que se retorna es un tipo de dato caracter se usa TO_CHAR para convertirlo en el mismo tipo de datos que debe retornas NVL. Por último, la función LDAP mostrará en largo fijo de 13 caracteres el valor que retorne la función NVL y para completar los 13 caracteres va a rellenar a la izquierda con espacios en blanco.
Funciones de Anidadas
- En la sentencia del ejemplo, la función SUBSTR retorna los primeros seis caracteres del apellido del empleado. La función CONCAT concatena el resultado de la función SUBSTR con el string '_chile'. Finalmente la función UPPER convierte en mayúscula el resultado entregado por la función CONCAT.
Expresiones Condicionales
- Existe dos métodos que se pueden utilizar para implementar una condición lógica IF-THEN-ELSE en una sentencia SQL.
- Una de ellas es la expresión CASE que cumple con el estándar ANSI SQL.
- El segundo método es la función DECODE que es específica de Oracle.
Expresiones Condicionales: CASE
- Facilita las consultas condicionales realizando el trabajo de una sentencia IF-THEN-ELSE en sentencias SQL.
- Evalúa una lista de condiciones y devuelve una de las expresiones de resultado.
- En una expresión CASE simple, el servidor Oracle busca el primer par WHEN …. THEN para el cual expr sea igual a expresión_comparación y retorna la expr_resultado.
- Si en ningún par WHEN …. THEN se encuentra la condición y si existe una cláusula ELSE, entonces el servidor Oracle retorna la expr_else. Si no existe la cláusula ELSE el servidor Oracle retorna NULL (Nulo).
- No se puede especificar el literal NULL para las expr_resultado y para expr_else.
Expresiones Condicionales: CASE
- En el ejemplo, el valor de la columna job_id es la condición de búsqueda en la expresión CASE de la sentencia. Si el trabajo del empleado es PR_REP el salario se mostrará incrementado en 15%, si es MK_MAN el salario se mostrará incrementado en 20% y para el resto de los trabajos no se incrementa el salario mostrándose sólo el salario actual. Esto se efectuará sólo para los empleados que trabajan en el departamento 20, 70 o 110.
Expresiones Condicionales: CASE
En la sentencia del ejemplo, la condición de búsqueda en la expresión CASE puede ser cualquier columna. Para este caso, la búsqueda en todas las condiciones es el salario del empleado (columna salary). Si el salario del empleado menor o igual a 5000 entonces se mostrará el mensaje Bajo, si el salario del empleado es mayor a 5000 y menor o igual a 10000 entonces se mostrará el mensaje Medio, si el salario del empleado es mayor a 10000 y menor o igual a 20000 entonces se mostrará el mensaje Bueno y si el salario es mayor a 20000 se mostrará el mensaje Excelente. Esto se efectuará sólo para los empleados que trabajan en el departamento 60 o 90. La información se mostrará ordenada en forma descendente por el valor del salario.
Expresiones Condicionales: DECODE
En el ejemplo, el valor de la columna job_id es la condición de búsqueda para la función DECODE. Si el trabajo del empleado es PR_REP el salario se mostrará incrementado en 15%, si es MK_MAN el salario se mostrará incrementado en 20% y para el resto de los trabajos no se incrementa el salario mostrándose sólo el salario actual. La información de muestra para los empleados que trabajan en el departamento 20, 70 0 90.
1.1.2 Aplicando las Funciones SQL de Fila más usadas
VRA
Created on February 22, 2024
Start designing with a free template
Discover more than 1500 professional designs like these:
View
Project Roadmap Timeline
View
Step-by-Step Timeline: How to Develop an Idea
View
Artificial Intelligence History Timeline
View
Museum Escape Room
View
Momentum: Onboarding Presentation
View
Urban Illustrated Presentation
View
3D Corporate Reporting
Explore all templates
Transcript
Usando Funciones SQL de una Fila de Caracteres, Números, Fechas y Conversión de Tipos de Datos
BDY1102
Objetivos de la Clase
1. Describir las características generales de las Funciones SQL.2. Describir las características de las Funciones SQL que operan con una fila.3. Describir los tipos de Funciones que operan con una sola fila: de caracteres, números, fechas, conversión y generales. 4. Cómo convertir, reemplazar o manipular caracteres en sentencias SQL5. Cómo manipular números en sentencias SQL.6. Cómo efectuar operaciones con fechas en sentencias SQL.7. Describir las características generales de las Funciones SQL.8. Describir las características de las Funciones SQL que operan con una fila.9. Describir los tipos de Funciones que operan con una sola fila: de caracteres, números, fechas, conversión y generales. 10. Cómo convertir, reemplazar o manipular caracteres en sentencias SQL11.Cómo manipular números en sentencias SQL.12. Cómo efectuar operaciones con fechas en sentencias SQL.
Funciones SQL
Funciones SQL
Funciones SQL
Funciones SQL de una Fila
Funciones SQL
Funciones SQL
Funciones para trabajar con Caracteres
Funciones de Caracteres
Aceptan como argumentos de entrada datos del tipo caracter y retornan datos del tipo caracteres o números
Funciones de Caracteres
Funciones de Caracteres
Funciones de Caracteres
SELECT last_name, UPPER(last_name), job_id, LOWER(job_id), INITCAP(job_id) FROM employees;
Funciones de Caracteres
Funciones de Caracteres
Funciones de Caracteres
Funciones para trabajar con Números
Funciones de Números
Funciones de Números
Funciones de Números
Funciones para trabajar con Fechas
Funciones de Fechas
Funciones de Fechas
Funciones de Fechas
Funciones de Fechas
Funciones de Fechas
Funciones de Fechas
Función EXTRACT para trabajar con Fechas
Función EXTRACT para trabajar con Fechas
Funciones de Conversiónde Tipos de Datos
Conversión de Tipos de Datos Implícita
Conversión de Tipos de Datos
Conversión de Tipos de Datos explícita
Uso Función TO_CHAR con Fechas
Uso Función TO_CHAR con Fechas
Uso Función TO_CHAR con Fechas
Uso Función TO_CHAR con Números
Uso Función TO_CHAR con números
Uso de Función TO_NUMBER con Caracteres
Uso de Función TO_DATE con Caracteres
Uso de Función TO_DATE con Caracteres
Funciones para validarvalores Nulos
Funciones Generales
Funciones Generales: NVL
Funciones Generales: NVL
Funciones Generales: NVL2
Funciones Generales: NULLIF
Funciones Generales: COALESCE
Funciones Anidadas
Funciones Anidadas
Implementar Condición Lógicaen Sentencias SQL
Expresiones Condicionales
Expresiones Condicionales: CASE
Expresiones Condicionales: CASE
Expresiones Condicionales: CASE
Expresiones Condicionales: DECODE
Resumen de la Clase
1. Se describieron las características generales de las Funciones SQL.2. Se describieron las características de las Funciones SQL que operan con una fila.3. Se describieron los tipos de Funciones que operan con una sola fila: de caracteres, numéricas, de fechas, de conversión y generales. 4. Se explicó cómo convertir, reemplazar o manipular caracteres en sentencias SQL5. Se explicó cómo manipular números en sentencias SQL.6. Se explicó cómo efectuar operaciones con fechas en sentencias SQL.7. Se explicó cómo convertir en forma explícita un tipo de dato numérico a carácter en una sentencia SQL. 8. Se explicó cómo convertir en forma explícita un tipo de dato caracter a número en una sentencia SQL. 9. Se explicó cómo convertir en forma explícita un tipo de dato fecha a caracter en una sentencia SQL. 10. Se explicó cómo convertir en forma explícita un tipo de dato caracter a fecha en una sentencia SQL. 11. Se explicó cómo definir un valor cuando alguna expresión es NULA en una sentencia SQL. 12. Se explicó cómo trabajar con Funciones Anidadas en una sentencia SQL. 13. Se explicó cómo implementar una condición lógica IF-THEN-ELSE en una sentencia SQL., con CASE
Funciones SQL de una Fila
- Se pueden usar en las cláusulas SELECT, WHERE y ORDER BY.
- En un sentencias UPDATE se puede usar en la cláusula SET.
- Actúan sobre cada fila afectada por una sentencia SELECT, INSERT o UPDATE y por cada una de las filas entrega un resultado.
- Aceptan argumentos que pueden ser: un valore constante, una variable, el nombre de una columna o una expresión.
- Manipulan datos y pueden modificar el tipo de datos
- Se pueden usar en forma anidada. No hay límite del número de funciones anidadas. El resultado de una función es el argumento para la otra.
En la Sintaxis:Funciones SQL de una Fila
Funciones de caracteres: aceptan un caracter como argumento o parámetro de entrada y retorna un valor de tipo caracter y número. Funciones numéricas: aceptan como entrada un número y retornan un número. Funciones de fechas: operan sobre valores de tipo fecha (DATE). Todas las funciones de fechas retornan una fecha excepto la función MONTHS_BETWEEN que retorna un número. Funciones de Conversión: transforman un valor de un tipo a otro. Funciones Generales: validan una columna o expresión para retornar un valor.
Funciones de Caracteres
Funciones de conversión del texto a mayúsculas y minúsculas:
- LOWER: Convierte el texto a minúsculas
- UPPER: Convierte el texto a mayúsculas
- INITCAP: Coloca la primera letra de cada palabra en mayúscula.
Funciones de reemplazo o manipulación de caracteres:Funciones de Números
ROUND: redondea la columna, expresión o valor a n posiciones decimales. . Si no se especifica n o su valor es cero, el valor se redondea al valor entero. Si n es negativo, los números a la izquierda del punto decimal se redondean a decenas, centenas etc. TRUNC: Trunca la columna, expresión o valor a n posiciones decimales. Si no se especifica n decimales el valor es 0, por lo tanto se trunca el valor sólo en su parte entera. Por defecto en cero. Si n es negativo trunca hacia la izquierda del punto decimal (coloca cero). MOD: Devuelve el resto que resulta de dividir m por n. En el ejemplo, las funciones ROUND y TRUNC se utilizan para redondear y truncar el valor 1234.5678 de diferentes formas (recordar que en Oracle el punto corresponde a decimales). La primera función ROUND redondea el valor en dos decimales, la segunda función ROUND redondea al valor entero (sin decimales, esto es similar a ROUND(1234.5678,0), la tercera función ROUND redondeado el valor en su parte entera a la décima más cercana a 35, la primera función TRUNC muestra el valor truncado a dos decimales, la segunda función TRUNC muestra el valor truncado solo en su valor entero (esto es similar a TRUNC(1234.5678,0) y la última función TRUNC muestra el valor truncado en su parte entera y reemplazada los dos últimos números por ceros.
Funciones de fecha
Funciones de fecha
- SYSDATE: retorna la fecha y hora actual de la Base de Datos
- MONTHS_BETWEEN: obtiene la diferencia en meses entre las dos fechas. El resultado puede ser positivo o negativo. Si fecha1 es posterior a fecha2, el resultado es positivo, si fecha1 es anterior a fecha2, el resultado es negativo. La parte no entera del resultado representa una porción de la mes.
- ADD_MONTHS: añade a la fecha el número de meses indicado por n. El valor de n debe ser un número entero y puede ser negativo. Si es negativo significa que está restando meses a la fecha.
- NEXT_DAY: retorna la fecha del día de la semana del argumento busca y que es posterior a la fecha entregada. El día puede ser el nombre del día (inglés o español según como esté configurada la Base de Datos) o el número del día de la semana 1=Lunes, 2=Martes etc.
- LAST_DAY: obtiene el último día del mes de la fecha especificada.
- ROUND: redondea la fecha al formato indicado. El formato puede ser:
- YEAR: redondea la fecha al año. - MONTH: redondea la fecha al mes.Funciones de fecha
Funciones de fecha
Funciones SQL
Las funciones son una característica muy poderosa del SQL. A partir de parámetros que requiere (cuando se requiera) para realizar una acción y entregar un resultado.
- Las funciones pueden ser usadas para:
- Realizar cálculos sobre los datos.
- Modificar datos individuales.
- Manipular la salida para grupos de filas.
- Formatear fechas y números para ser visualizadas.
- Convertir tipos de datos de columnas.
Las funciones pueden opcionalmente recibir argumentos (valores) como entrada, pero SIEMPRE retornan un valor.Conversión de Tipos de Datos En algunos casos, el servidor Oracle usa tipos de datos distintos a los que se requieren. Cuando esto sucede, se debe convertir al tipo de datos que se requiere. La conversión de un tipo de dato a otro puede ser efectuada en forma implícita o automática por el servidor Oracle o en forma explícita por el usuario usando funciones de conversión.Conversión de Tipos de Datos Implícita
La asignación de valores tiene éxito si el servidor Oracle puede convertir el tipo de dato del valor usado en el tipo de dato que se encuentra almacenado. El servidor Oracle puede convertir implícitamente (en forma automática) siempre que el valor a convertir tenga un formato válido al tipo de dato en el que se convertirá. Su pueden convertir en forma explícita:
- De VARCHAR2 o CHAR a NUMBER
- De VARCHAR2 o CHAR a DATE
- DE NUMBER A VARCHA2
- DE DATE A VARCHAR2
En la sentencia del ejemplo, se obtiene información de los empleados contratados después del '05/03/2008’ . La fecha es una cadena de caracteres que Oracle convierte a fecha al momento de comparar. Esto lo puede hacer porque la fecha de la condición tiene un formato de fecha válido para poder ser convertido a tipo de dato DATE.Conversión de Tipos de Datos Explícita
Oracle proporciona tres funciones para convertir en forma explícita un tipo de dato en otro: TO_CHAR: Obtiene un texto a partir de un número o fecha. Opcionalmente se puede dar un formato específico de conversión. TO_NUMBER: Convierte textos en números, indicándole, si se desea, el formato de salida. TO_DATE: Convierte textos en fechas, indicándole, si se desea, el formato de salida.
Uso de Función TO_CHAR con Fechas
Los elementos más usados en el formato de conversión son lo siguientes:
Uso de Función TO_CHAR con Fechas
En el ejemplo, la sentencia se ejecutó 11 de Octubre del 2019 a las 15:21). El primer formato de fecha de contrato se muestra en número (año en 4 dígitos) y separado por /. El segundo formato muestra el día y año de contrato en número y el mes en palabras (la primera letra en mayúscula y el resto en minúscula. El tercer formato muestra la fecha y hora del sistema incorporando las palabras de. En el cuarto formato de fecha muestra la fecha y hora del sistema pero la hora en formato de 12 horas con el indicador de meridiano. Como la sentencia se ejecutó a las 3 de la tarde automáticamente aparece en indicador PM.
Uso de Función TO_CHAR con Fechas
En el ejemplo, la sentencia se ejecutó 11 de Octubre del 2019 a las 15:21). El primer formato de fecha de contrato se muestra en número (año en 4 dígitos) y separado por /. El segundo formato muestra el día y año de contrato en número y el mes en palabras (la primera letra en mayúscula y el resto en minúscula. El tercer formato muestra a que trimestre del año corresponde la fecha de contrato del empleado, El cuarto formato muestra la fecha y hora del sistema incorporando las palabras de. En el quinto formato de fecha muestra la fecha y hora del sistema pero la hora en formato de 12 horas con el indicador de meridiano. Como la sentencia se ejecutó a las 6 de la tarde automáticamente aparece en indicador PM.
Uso de Función TO_CHAR con números
Cuando se desean trabajar los valores numéricos como una cadena de caracteres se deben convertir esos números a un tipo de dato caracter utilizando la función TO_CHAR. Esta función convierte un dato de tipo NUMBER a un dato de tipo VARCHAR2. El formato de conversión:
- Debe ir entre comillas simples.
- Puede incluir cualquier elemento de formato de número válido.
- Debe ir separado del valor número por una coma.
Los elementos para formatos de números válidosUso de Función TO_CHAR con números
En la primera sentencia del ejemplo, la muestra el salario del empleado Ernest en 4 formatos diferentes. En el formato1 se muestra el salario tal como se encuentra se almacenado en la tabla (cada 9 representa un dígito del salario). En el formato2 se antepondrán tantos ceros como falten para mostrar el valor en 7 caracteres. En el formato3 se antepondrá el signo dólar y se mostrará un punto como separador de miles. En el formato4 se antepondrá el símbolo de moneda local y se mostrarán los separadores de miles y de decimales por defecto en Oracle, es decir la coma y el punto respectivamente. En la sentencia del segundo ejemplo, en el formato1 se antepondrán ceros hasta completar 4 caracteres antes del separador de miles. En el formato2 se mostrará un punto como separador de miles y una coma para los decimales y en formato3 dado que la cantidad entre 9 y ceros después de V son tres, se mostrará el valor del salario multiplicado por 1000.
Uso de Función TO_NUMBER con Caracteres
Para poder convertir un literal o una columna de tipo de dato caracter a número se debe usar la función TO_NUMBER. La restricción es que el valor del caracter a convertir debe tener un formato de número válido. El formato de conversión es opcional sin embargo, si se desea dar un formato en particular, los elementos que se pueden usar son los mismo que se usan para números con la función TO_CHAR. En el ejemplo, se tiene un literal que es un número que se muestra primero como un carácter y usando la función TO_NUMBER ese literal se convierte en número. Esto sirve cuando por ejemplo a partir de una planilla Excel se deben insertar valores que tienen el formato que se muestra en el ejemplo y se deben insertar en una columna de tipo NUMBER entonces se debe efectuar la conversión explícita a número.
Uso de Función TO_DATE con Caracteres
Para poder convertir un literal o una columna de tipo de dato caracter a fecha se debe usar la función TO_DATE. La restricción es que el valor del caracter a convertir debe tener un formato de fecha válido. El formato de conversión es opcional sin embargo, si se desea dar un formato en particular, los elementos que se pueden usar son los mismo que se usan para fecha con la función TO_CHAR. En el ejemplo, se tiene un literal que es una fecha pero no en el formato tradicional, por lo tanto cuando se trata de convertir ese caracter a fecha se produce un error. Par poder mostrar ese literal como fecha, se debe le dar un formato de conversión válido al valor literal.
Funciones Generales
Estas funciones trabajan con cualquier tipo de datos y se relacionan con el uso de valores nulos en la lista de expresiones:
Funciones Generales: NVL
Esta función la expr1. Si ésta es nula retorna el valor de expr2. Si expr1 no es nula la función retorna el valor de expr1. Ambos expresiones deben ser del mismo tipo de dato. En la sentencia del ejemplo, la función NVL retorna cero cuando la identificación del jefe sea nula es decir, cuando el empleado no posee jefe.
Funciones Generales: NVL
En el ejemplo, no todos lo empleados tiene un porcentaje de comisión asignado, por esa razón al obtener salario anual para esos empleados el valor es NULO (cualquier operación matemática con valores nulos el resultado es NULO). La primera función NVL retorna cero cuando el porcentaje de comisión es NULO. En la segunda función NVL retorna cero cuando el porcentaje de comisión es NULO y es por era razón que para estos empleados el salario anual es el valor del salario que posee multiplicado por 12.
Funciones Generales: NVL2
Esta función evalúa la primera expresión (expr1). Si no es Nula la función NVL2 retorna la segunda expresión (expr2). Si la primera expresión es Nula entonces la función retorna la tercera expresión (expr3). El argumento expr1 puede ser cualquier tipo de dato. Los argumentos expr2 y expr3 pueden ser de cualquier tipo de dato excepto LONG. El argumento expr3 debe ser del mismo tipo de dato de expr2 a menos que expr3 sea Nulo. El tipo de dato del valor que retorna la función es siempre del mismo tipo de datos de expr2. En el ejemplo, la función NVL2 mostrará el string SALARIO+COMISION si el porcentaje de comisión no es nulo. Si el porcentaje de comisión es nulo mostrará el string SOLO SALARIO para los empleados con identificación 100, 101, 114, 147,148 o 149.
Funciones Generales: NULLIF
La función NULLIF compara dos expresiones (expr1 y expr2 ). Si son iguales, la función retorna NULO. Si ellas no son iguales, la función retorna la primera expresión (expr1). Los tipos de datos de ambas expresiones deben ser los mismos. No se puede especificar NULL para la primera expresión (expr1). En la sentencia del ejemplo, la función NULLIF compara el resultado de multiplicar el salario por el porcentaje de comisión con el valor cero. Si el resultado de la expresión matemática es cero entonces mostrará NULO y si son diferentes mostrará el valor de la expresión matemática. La información se muestra para los empleados con identificación 100, 104, 106, 110, 166 o 167
Funciones Generales: COALESCE
La función COALESCE retorna la primera expresión NO NULA de la lista. Todas las expresiones deben tener el mismo tipo de dato. En el ejemplo, la función COALESCE mostrará el valor del porcentaje de comisión si no es nulo, de lo contrario mostrará el valor de la identificación del jefe si no es nulo y si ambos valores son nulos mostrará 9999 para los empleados que trabajan en el departamento 10, 20 o 90.
Funciones de Anidadas
- Las funciones SQL de una fila se pueden anidar y su ejecución es desde el nivel más interno al más externo.
- Esto significa que el resultado de una función se usa como parámetro para la otra función y así sucesivamente.
- Este tipo de funciones se pueden anidar sin limites.
En el ejemplo, la primera función que se ejecuta es la que transforma a caracter la columna manager_id (TO_CHAR(manager_id)). La segunda función que se ejecuta NVL y que retornará el mensaje NO POSEE JEFE si manager_id es NULO. Como manager_id es un número y el mensaje que se retorna es un tipo de dato caracter se usa TO_CHAR para convertirlo en el mismo tipo de datos que debe retornas NVL. Por último, la función LDAP mostrará en largo fijo de 13 caracteres el valor que retorne la función NVL y para completar los 13 caracteres va a rellenar a la izquierda con espacios en blanco.Funciones de Anidadas
Expresiones Condicionales
Expresiones Condicionales: CASE
Expresiones Condicionales: CASE
Expresiones Condicionales: CASE
En la sentencia del ejemplo, la condición de búsqueda en la expresión CASE puede ser cualquier columna. Para este caso, la búsqueda en todas las condiciones es el salario del empleado (columna salary). Si el salario del empleado menor o igual a 5000 entonces se mostrará el mensaje Bajo, si el salario del empleado es mayor a 5000 y menor o igual a 10000 entonces se mostrará el mensaje Medio, si el salario del empleado es mayor a 10000 y menor o igual a 20000 entonces se mostrará el mensaje Bueno y si el salario es mayor a 20000 se mostrará el mensaje Excelente. Esto se efectuará sólo para los empleados que trabajan en el departamento 60 o 90. La información se mostrará ordenada en forma descendente por el valor del salario.
Expresiones Condicionales: DECODE
En el ejemplo, el valor de la columna job_id es la condición de búsqueda para la función DECODE. Si el trabajo del empleado es PR_REP el salario se mostrará incrementado en 15%, si es MK_MAN el salario se mostrará incrementado en 20% y para el resto de los trabajos no se incrementa el salario mostrándose sólo el salario actual. La información de muestra para los empleados que trabajan en el departamento 20, 70 0 90.