SQL Capitulo 4 : Uso de Funciones de Conversión y Expresiones Condicionales en SQL

Sean bienvenidos a esta sección donde se revisa el uso de conversiones y expresiones condicionales, recuerden estas sentencias estan siendo ejecutadas en SQL developer administrador de la Base de datos Oracle.

Objetivos de la publicación:
Al finalizar esta lección, debería estar capacitado para lo siguiente:

• Describir varios tipos de funciones de conversión que están disponibles en SQL
• Utilizar las funciones de conversión TO_CHAR, TO_NUMBER y TO_DATE
• Aplicar expresiones condicionales en una sentencia SELECT

Esta lección se centra en funciones que convierten los datos de un tipo en otro (por ejemplo, conversión de datos de caracteres en datos numéricos) y describe las expresiones condicionales en sentencias SQL SELECT.

Conversión de tipo de dato implícito y explícito

Funciones de Conversión

Además de los tipos de dato de Oracle, las columnas de las tablas de una Oracle Database se pueden definir utilizando los tipos de dato American National Standards Institute (ANSI), DB2 y SQL/DS. Sin embargo, el servidor de Oracle convierte internamente esos tipos de dato a tipos de dato de Oracle.

En algunos casos, el servidor de Oracle recibe datos de un tipo de dato cuando espera datos de un tipo de dato diferente. Cuando esto ocurre, el servidor de Oracle puede convertir automáticamente los datos al tipo de dato esperado. Esta conversión de tipo de dato puede realizarla el servidor de Oracle implícitamente o el usuario explícitamente.

Las conversiones de tipo de dato funcionan según las reglas explicadas.

Las conversiones de tipo de dato explícitas se realizan utilizando las funciones de conversión. Las funciones de conversión sirven para convertir los valores de un tipo de dato a otro. Generalmente, el formato de los nombres de funciones sigue la convención data type TO data type. El primer tipo de dato es el tipo de dato de entrada; el segundo tipo de dato es la salida.

Nota: aunque la conversión de tipo de dato implícita está disponible, se recomienda que realice una conversión de tipo de dato explícita para asegurar la fiabilidad de las sentencias SQL.

Conversión Implícita del Tipo de Dato

En expresiones, el servidor de Oracle puede convertir automáticamente:

El servidor de Oracle puede realizar automáticamente una conversión de tipo de dato en una expresión. Por ejemplo, la expresión hire_date > '01-JAN-90' tiene como resultado la conversión implícita de la cadena '01-JAN-90' a una fecha. Por lo tanto, un valor VARCHAR2 o CHAR se puede convertir de forma implícita a un tipo de dato de fecha o número en una expresión.

Para la evaluación de expresiones, el servidor de Oracle puede convertir automáticamente:

En general, el servidor de Oracle utiliza la regla de expresiones cuando se necesita una conversión de tipo de dato. Por ejemplo, la expresión grade = 2 tiene como resultado la conversión implícita del número 2 a la cadena “2” porque el grado es una columna CHAR(2).

Nota: las conversiones de CHAR a NUMBER se realizan correctamente sólo si la cadena de caracteres representa un número válido.

SQL proporciona tres funciones para convertir un valor de un tipo de dato a otro:

Nota: la lista de funciones mencionadas en esta lección incluye sólo algunas de las funciones de conversión disponibles.

Para obtener más información, consulte la sección sobre funciones de conversión en Oracle Database SQL Language Reference (Referencia sobre Lenguaje SQL de Oracle Database).

Funciones TO_CHAR, TO_DATE, TO_NUMBER

Uso de la Función TO_CHAR con Fechas

El modelo de formato:

• Debe estar entre comillas simples.
• Es sensible a mayúsculas/minúsculas.
• Puede incluir cualquier elemento de formato de fecha válido.
• Tiene un elemento fm para eliminar los espacios en blanco o suprimir ceros iniciales.
• Está separado del valor de fecha por una coma.

TO_CHAR convierte un tipo de dato de fecha y hora a un valor de tipo de dato VARCHAR2 con el formato especificado porformat_model. Un modelo de formato es un carácter literal que describe el formato de fecha y hora almacenado en una cadena de caracteres. Por ejemplo, el modelo de formato de fecha y hora '11-Nov-1999' es 'DD-Mon-YYYY'. Puede utilizar la función TO_CHAR para convertir una fecha de su formato por defecto a uno que especifique.

Instrucciones

El modelo de formato debe estar entre comillas simples y es sensible a mayúsculas/minúsculas.

El modelo de formato puede incluir cualquier elemento de formato de fecha válido. Asegúrese de separar el valor de fecha del modelo de formato con una coma.

Los nombres de los días y meses en la salida se rellenan automáticamente con espacios en blanco.

Para eliminar los espacios en blanco o suprimir los ceros iniciales, utilice el elemento fm de modo de relleno.

SELECT employee_id, TO_CHAR(hire_date, 'MM/YY') Month_Hired

FROM   hr.employees

WHERE  last_name = 'Higgins';

Resultado:

Elementos del Modelo de Formato de Fecha

Los elementos de tiempo formatean la parte de la hora de la fecha:

Agregan cadenas de caracteres entre comillas dobles:

El número se agrega como sufijo de los números en letra:

Utilice los formatos de las siguientes tablas para mostrar la información de tiempo, los literales y para cambiar los numerales a números en letra.

Uso de la Función TO_CHAR con Fechas

La sentencia SQL muestra los apellidos y fechas de contratación de todos los empleados. La fecha de contratación aparece como 17 de junio de 1987.

Ejemplo:

Modifique el ejemplo para mostrar las fechas en un formato que aparezca como “Diecisiete de junio de 1987 12:00:00 AM.”

SELECT last_name, TO_CHAR(hire_date, 'fmDdspth "of" Month YYYY fmHH:MI:SS AM')

HIREDATE

FROM    hr.employees;

Uso de la Función TO_CHAR con Números

Éstos son algunos de los elementos de formato que puede utilizar con la función TO_CHAR para mostrar un valor de número como un carácter:

Al trabajar con valores de número como cadenas de caracteres, debe convertir dichos números al tipo de dato de carácter utilizando la función TO_CHAR que traduce un valor del tipo de dato NUMBER a un tipo de dato VARCHAR2. Esta técnica es muy útil con la concatenación.

El servidor de Oracle muestra una cadena de signos de número (#) en lugar de un número completo cuyos dígitos exceden el número de dígitos proporcionado en el modelo de formato.

El servidor de Oracle redondea el valor decimal almacenado al número de decimales proporcionado en el modelo de formato.

Uso de Funciones TO_NUMBER y TO_DATE

Convertir una cadena de caracteres a un formato de número que utiliza la función TO_NUMBER:

Convertir una cadena de caracteres a un formato de fecha que utiliza la función TO_DATE:

Estas funciones tienen un modificador fx. Este modificador especifica la coincidencia exacta para el argumento de carácter y el modelo de formato de fecha de una función TO_DATE.

Puede que desee convertir una cadena de caracteres a un número o a una fecha. Para realizar esta tarea, utilice las funcione TO_NUMBER o TO_DATE. El modelo de formato que seleccione está basado en los elementos de formato demostrados anteriormente.

El modificador fx especifica la coincidencia exacta para el argumento de carácter y el modelo de formato de fecha de una función TO_DATE:

La puntuación y el texto entre comillas del argumento de carácter debe coincidir exactamente (excepto en las mayúsculas/minúsculas) con las partes correspondientes  del modelo de formato. 

El argumento de carácter no puede tener espacios en blanco adicionales. Sin fx, el servidor de Oracle ignora los espacios en blanco adicionales.

Los datos numéricos del argumento de carácter deben tener el mismo número de dígitos que el elemento correspondiente en el modelo de formato. Sin fx, los números del argumento de carácter no pueden omitir los ceros iniciales.

Muestre el nombre y fecha de contratación de todos los empleados que empezaron a trabajar el 24 de mayo de 1999. En el siguiente ejemplo, hay dos espacios después del mes May y antes del número 24. Ya que se utiliza fx, se necesita una coincidencia exacta y los espacios después de la palabra May no se reconocerán:

SELECT last_name, hire_date

FROM   hr.employees

WHERE  hire_date = TO_DATE('May  24, 1999', 'fxMonth DD, YYYY');

Uso de las Funciones TO_CHAR y TO_DATE con el Formato de Fecha RR

Para buscar los empleados contratados antes de 1990, utilice el formato de fecha RR, que produce los mismos resultados si se ejecutara el comando en 1999 o en la actualidad:

Para buscar los empleados contratados antes de 1990, se puede utilizar el formato RR. Debido a que el año actual es mayor que 1999, el formato RR interpreta la parte del año  de la fecha de 1950 a 1999.

Por otro lado, en el siguiente comando no se selecciona ninguna fila porque el formato YY interpreta la parte del año de la fecha en el siglo actual (2090).

Funciones de anidación

Las funciones de una sola fila se pueden anidar en cualquier nivel.

Las funciones anidadas se evalúan desde el nivel más profundo hasta el nivel menos profundo.

Las funciones de una sola fila se pueden anidar en cualquier profundidad. Las funciones anidadas se evalúan desde el nivel más profundo hasta el nivel menos profundo. Los siguientes ejemplos muestran la flexibilidad de estas funciones.

Funciones de Anidación: Ejemplo 1

El ejemplo muestra los apellidos de los empleados en el departamento 60. La evaluación de la sentencia SQL implica tres pasos:

1. La función interna recupera los primeros ocho caracteres del apellido.

Result1 = SUBSTR (LAST_NAME, 1, 8)

2. La función externa concatena el resultado con _US.

Result2 = CONCAT(Result1, '_US')

3. La función más externa convierte los resultados a mayúsculas.

La expresión completa se convierte en la cabecera de columna porque no se ha proporcionado ningún alias de columna.

Funciones de Anidación: Ejemplo 2

El ejemplo muestra los salarios de los empleados dividido entre 7 y redondeado a dos decimales. El resultado se formatea para mostrar el salario con notación en danés. Es decir, la coma se utilizar como decimal y el punto para los miles.

En primer lugar, la función interna ROUND se ejecuta para redondear el valor del salario dividido entre 7 a dos decimales. La función TO_CHAR se utiliza entonces para aplicar formato al resultado de la función ROUND.

Nota: los elementos D y G especificados en el parámetro de función TO_CHAR son elementos de formato de número. D devuelve un carácter decimal en la posición especificada. G se utiliza como un separador de grupo.

Funciones generales

Las siguientes funciones funcionan con cualquier tipo de dato y pertenecen al uso de valores nulos:

NVL (expr1, expr2)

NVL2 (expr1, expr2, expr3)

NULLIF (expr1, expr2)

COALESCE (expr1, expr2, ..., exprn)

Estas funciones funcionan con cualquier tipo de dato y pertenecen al uso de valores nulos en la lista de expresiones.

Nota: para obtener más información sobre los cientos de funciones disponibles, consulte la sección sobre funciones en Oracle Database SQL Language Reference (Referencia sobre Lenguaje SQL de Oracle Database).

Función NVL

Convierte un valor nulo a un valor real:

• Los tipos de dato que se pueden utilizar son fecha, carácter y número.
• Los tipos de dato deben coincidir con:

NVL(commission_pct,0)
NVL(hire_date,'01-JAN-97')
NVL(job_id,'No Job Yet')

Para convertir un valor nulo a un valor real, utilice la función NVL. 

Sintaxis

NVL (expr1, expr2)

En la sintaxis:

expr1 es el valor de origen o expresión que puede contener un valor nulo

expr2 es el valor de destino para convertir el valor nulo

Puede utilizar la función NVL para convertir cualquier tipo de dato, pero el valor de retorno siempre es el mismo que el tipo de dato de expr1.

Uso de la Función NVL

Para calcular la compensación anual de los empleados, necesita multiplicar el salario mensual por 12 y, a continuación, agregue el porcentaje de comisión al resultado:

SELECT last_name,  salary, commission_pct,

(salary*12) + (salary*12*commission_pct) AN_SAL

FROM   hr.employees;

Uso de la Función NVL2

La función NVL2 examina la primera expresión. Si la primera expresión no es nula, la función NVL2 devuelve la segunda expresión. Si la primera expresión es nula, se devolverá la tercera expresión. 

Sintaxis

NVL2(expr1, expr2, expr3)

En la sintaxis:

expr1 es el valor de origen o expresión que puede contener un valor nulo

expr2 es el valor que se devuelve si expr1 no es nulo

expr3 es el valor que se devuelve si expr1 es nulo

En el ejemplo se examina la columna COMMISSION_PCT. Si se detecta un valor, se devolverá el valor literal de texto de SAL+COMM. Si la columna COMMISSION_PCT contiene un valor nulo, se devolverá el valor literal de texto SAL.

Nota: el argumento expr1 puede tener cualquier tipo de dato. Los argumentos expr2 y expr3 pueden tener cualquier tipo de dato excepto LONG. 

Uso de la Función NULLIF

La función NULLIF compara dos expresiones.

Sintaxis

NULLIF (expr1, expr2)

En la sintaxis:

NULLIF compara expr1 y expr2. Si son iguales, la función devuelve un valor nulo. Si no, la función devuelve expr1. Sin embargo, no puede especificar el literal NULL para expr1.

En el ejemplo, la longitud del nombre en la tabla EMPLOYEES se compara con la longitud del apellido en la tabla EMPLOYEES. Cuando las longitudes de ambos son iguales, se mostrará un valor nulo. Cuando las longitudes no son iguales, se mostrará la longitud del nombre.

Nota: la función NULLIF es el equivalente lógico de la siguiente expresión CASE. La expresión CASE:

CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END

Uso de la Función COALESCE

La ventaja de la función COALESCE con respecto a la función NVL es que la función COALESCE puede obtener múltiples valores alternativos.

Si la primera expresión no es nula, la función COALESCE devuelve esa expresión; de lo contrario, aplica la función COALESCE de las expresiones restantes.

La función COALESCE devuelve la  primera expresión no nula de la lista.

Sintaxis

COALESCE (expr1, expr2, ... exprn)

En la sintaxis:

expr1 devuelve esta expresión si no es nula

expr2 devuelve esta expresión si la primera expresión es nula y ésta no es nula

exprn devuelve esta expresión si las expresiones anteriores son nulas

Tenga en cuenta que todas las expresiones deben ser del mismo tipo de dato.

Uso de la Función COALESCE

En el ejemplo se muestra el valor manager_id si éste no es nulo. Si el valor manager_id es nulo, se mostrará commission_pct. Si los valores manager_id y commission_pct son nulos, se muestra “No commission and no manager”. Tenga en cuenta que la función TO_CHAR se aplica de modo que todas las expresiones sean del mismo tipo de dato.

Ejemplo:

Para los empleados que no perciben ninguna comisión, la organización desea proporcionar un aumento de salario de 2.000 dólares y para los empleados que perciben comisión, la consulta debe calcular el nuevo salario que es igual al salario existente sumado a la comisión.

SELECT last_name,  salary, commission_pct,

 COALESCE((salary+(commission_pct*salary)), salary+2000, salary) "New Salary"

FROM   hr.employees;

Nota: examine la salida. Para los empleados que no perciben ninguna comisión, la columna de nuevo salario muestra el salario aumentado en 2.000 dólares y para los empleados que perciben comisión, la columna de nuevo salario muestra la comisión calculada sumada al salario.

Expresiones condicionales

Proporcionar el uso de la lógica IF-THEN-ELSE en una sentencia SQL

Utilizar dos métodos:

• Expresión CASE
• Función DECODE

Los dos métodos que se utilizan para implantar el procesamiento condicional (lógica IF-THEN-ELSE) en la sentencia SQL son la expresión CASE y la función DECODE.

Nota: la expresión CASE cumple con SQL ANSI. La función DECODE es específica de la sintaxis de Oracle.

Expresión CASE

Facilita las consultas condicionales realizando el trabajo de una  sentencia IF-THEN-ELSE:

Las expresiones CASE permiten utilizar la lógica IF-THEN-ELSE en las sentencias SQL sin tener que llamar a los procedimientos.

En una expresión CASE simple, el servidor de Oracle busca el primer par WHEN ... THEN para el que expr es igual a comparison_expr y devuelve return_expr. Si ninguno de los pares WHEN ... THEN cumple con esta condición y existe una cláusula ELSE, el servidor de Oracle devuelve else_expr. De lo contrario, el servidor de Oracle devuelve un valor nulo. No puede especificar el valor literal NULL para todas las expresiones return_expr y else_expr. 

Las expresiones expr y comparison_expr deben ser del mismo tipo de dato, que puede ser CHAR, VARCHAR2, NCHAR o NVARCHAR2. Todos los valores de retorno (return_expr) deben ser del mismo tipo de dato.

Uso de la Expresión CASE

Facilita las consultas condicionales realizando el trabajo de una  sentencia IF-THEN-ELSE:

En la sentencia SQL, se descodifica el valor de JOB_ID. Si JOB_ID es IT_PROG, el aumento de salario es del 10%; si JOB_ID es ST_CLERK, el aumento de salario es del 15%; si JOB_ID es SA_REP, el aumento del salario es del 20%. Para el resto de otros roles de cargo, no hay ningún aumento de salario.

La misma sentencia se puede escribir con la función DECODE.

El siguiente código es un ejemplo de la expresión buscada CASE. En una expresión CASE buscada, la búsqueda se realiza de izquierda a derecha hasta que se encuentra una coincidencia de la condición mostrada y, a continuación, se devuelve la expresión de retorno. Si no se encuentra ninguna condición verdadera y existe una cláusula ELSE, se devuelve la expresión de retorno en la cláusula ELSE; de lo contrario, se devuelve NULL.

SELECT last_name,salary, 

(CASE WHEN salary < 5000 THEN 'Low' 

      WHEN salary < 10000 THEN 'Medium' 

      WHEN salary < 20000 THEN 'Good' 

      ELSE 'Excellent' 

END) qualified_salary 

FROM employees;

Función DECODE

Facilita las consultas condicionales realizando el trabajo de una expresión CASE o una sentencia IF-THEN-ELSE:

La función DECODE decodifica una expresión de una forma similar a la lógica IF-THEN-ELSE que se utiliza en varios idiomas. La función DECODE descodifica expression después de compararla con cada valor search. Si la expresión es la misma que search, se devuelve result.

Si se omite el valor por defecto, se devuelve un valor nulo donde un valor de búsqueda no coincida con ninguno de los valores de resultado.

Uso de la Función DECODE

En la sentencia SQL de la diapositiva, se prueba el valor de JOB_ID. Si JOB_ID es IT_PROG, el aumento de salario es del 10%; si JOB_ID es ST_CLERK, el aumento de salario es del 15%; si JOB_ID es SA_REP, el aumento del salario es del 20%. Para el resto de otros roles de cargo, no hay ningún aumento de salario.

La misma sentencia se puede expresar en pseudocódigo como una sentencia IF-THEN-ELSE:

IF job_id = 'IT_PROG'     THEN  salary = salary*1.10

IF job_id = 'ST_CLERK'    THEN  salary = salary*1.15

IF job_id = 'SA_REP'      THEN  salary = salary*1.20

ELSE salary = salary

Mostrar el impuesto aplicable para cada empleado del departamento 80:

Esta diapositiva muestra otro ejemplo que utiliza la función DECODE. En este ejemplo, puede determinar los impuestos para cada empleado del departamento 80 según su salario mensual. Los impuestos son los siguientes: 

Rango de Salario Mensual Impuesto

$0,00–1.999,99             00%

$2.000,00–3.999,99     09%

$4.000,00–5.999,99     20%

$6.000,00–7.999,99     30%

$8.000,00–9.999,99     40%

$10.000,00–11.999,99             42%

$12.200,00–13.999,99             44%

14.000,00 o superior             45%

Resumen

En esta lección, debe haber aprendido lo siguiente:

• Modificar formatos de fecha para su visualización utilizando funciones
• Convertir tipos de dato de columna utilizando funciones
• Utilizar funciones NVL
• Utilizar la lógica IF-THEN-ELSE y otra expresión condicional en una sentencia SELECT

Recuerde lo siguiente:

Las funciones de conversión pueden convertir valores numéricos, de fecha y de carácter: Funciones TO_CHAR, TO_DATE, TO_NUMBER

Existen varias funciones que pertenecen a valores nulos, incluyendo NVL, NVL2, NULLIF y COALESCE.

La lógica IF-THEN-ELSE se puede aplicar en una sentencia SQL utilizando la expresión CASE o la función DECODE.