Usar funciones de conversión

Además de las funciones de la utilidad SQL, la biblioteca de funciones incorporada de Oracle contiene funciones de conversión de tipos. Puede haber escenarios en los que la consulta espera una entrada en un tipo de datos específico, pero la recibe en un tipo de datos diferente. En tales casos, Oracle intenta implícitamente convertir el valor inesperado en un tipo de datos compatible que se puede sustituir en su lugar y la continuidad de la aplicación no se ve comprometida. La conversión de tipos puede ser realizada implícitamente por Oracle o explícitamente realizada por el programador.

La conversión de tipos de datos implícitos funciona en función de una matriz que muestra el soporte de Oracle para la conversión de tipos internos. Además de estas reglas, Oracle ofrece funciones de conversión de tipos que se pueden utilizar en las consultas para conversión y formateo explícitos. De hecho, se recomienda realizar una conversión explícita en lugar de depender de la inteligencia del software. Aunque la conversión implícita funciona bien, pero para eliminar las posibilidades de sesgo en las que las entradas incorrectas podrían ser difíciles de encasillar internamente.

Conversión de tipo de datos implícita

Oracle puede convertir implícitamente un valor VARCHAR2 o CHAR en un valor de tipo NUMBER o DATE. De manera similar, un valor de tipo NUMBER o DATA se puede convertir automáticamente en datos de caracteres mediante el servidor Oracle. Tenga en cuenta que la interconversión implícita ocurre solo cuando el carácter representa un número válido o un valor de tipo de fecha, respectivamente.

Por ejemplo, examine las siguientes consultas SELECT. Ambas consultas darán el mismo resultado porque Oracle trata internamente 15000 y '15000' como lo mismo.

Consulta-1

SELECT employee_id,first_name,salary
FROM employees
WHERE salary > 15000;

Consulta-2

SELECT employee_id,first_name,salary
FROM employees
WHERE salary > '15000';

Conversión explícita de tipos de datos

Las funciones de conversión de SQL son funciones de una sola fila que son capaces de encasillar el valor de la columna, literal o una expresión. TO_CHAR, TO_NUMBER y TO_DATE son las tres funciones que realizan modificaciones cruzadas de tipos de datos.

Función TO_CHAR

La función TO_CHAR se utiliza para encasillar una entrada numérica o de fecha a un tipo de carácter con un modelo de formato (opcional).

Sintaxis 

TO_CHAR(number1, [format], [nls_parameter])

Para la conversión de números a caracteres, los parámetros nls se pueden usar para especificar caracteres decimales, separador de grupos, modelo de moneda local o modelo de moneda internacional. Es una especificación opcional; si no está disponible, se utilizará la configuración nls de nivel de sesión. Para la conversión de fecha a carácter, el parámetro nls se puede utilizar para especificar los nombres de día y mes, según corresponda.

Las fechas se pueden formatear en múltiples formatos después de convertirlas a tipos de caracteres usando la función TO_CHAR. La función TO_CHAR se utiliza para que Oracle 11g muestre las fechas en un formato particular. Los modelos de formato distinguen entre mayúsculas y minúsculas y deben incluirse entre comillas simples.

Considere la siguiente consulta SELECT. La consulta formatea las columnas HIRE_DATE y SALARY de la tabla EMPLOYEES usando la función TO_CHAR.

SELECT first_name,
       TO_CHAR (hire_date, 'MONTH DD, YYYY') HIRE_DATE,
	   TO_CHAR (salary, '$99999.99') Salary
FROM employees
WHERE rownum < 5;

FIRST_NAME           HIRE_DATE          SALARY
-------------------- ------------------ ----------
Steven               JUNE      17, 2003  $24000.00
Neena                SEPTEMBER 21, 2005  $17000.00
Lex                  JANUARY   13, 2001  $17000.00
Alexander            JANUARY   03, 2006   $9000.00

El primer TO_CHAR se utiliza para convertir la fecha de contratación al formato de fecha MONTH DD, YYYY, es decir, el mes deletreado y rellenado con espacios, seguido del día del mes de dos dígitos y luego el año de cuatro dígitos. Si prefiere mostrar el nombre del mes en mayúsculas y minúsculas (es decir, "diciembre"), simplemente use este caso en el argumento de formato: ('Mes DD, YYYY').

La segunda función TO_CHAR en la Figura 10-39 se usa para formatear el SALARIO para mostrar el signo de la moneda y dos posiciones decimales.

Oracle ofrece un conjunto completo de modelos de formato. La siguiente tabla muestra la lista de modelos de formato que se pueden usar para encasillar valores numéricos y de fecha como caracteres usando TO_CHAR.

Modelo de formato Descripción
,(coma) Devuelve una coma en la posición especificada. Puede especificar varias comas en un modelo de formato numérico. Restricciones: un elemento de coma no puede comenzar un modelo de formato numérico. No puede aparecer una coma a la derecha de un carácter decimal o un punto en un modelo de formato numérico.
.(período) Devuelve un punto decimal, que es un punto (.) En la posición especificada. Restricción: puede especificar solo un período en un modelo de formato numérico
PS Devuelve valor con un signo de dólar inicial
0 Devuelve ceros a la izquierda. Devuelve ceros finales.
9 Devuelve un valor con el número especificado de dígitos con un espacio inicial si es positivo o con un signo menos si es negativo. Los ceros iniciales están en blanco, excepto por un valor cero, que devuelve un cero para la parte entera del número de coma fija.
segundo Devuelve espacios en blanco para la parte entera de un número de coma fija cuando la parte entera es cero (independientemente de los "0" en el modelo de formato).
C Devuelve en la posición especificada el símbolo de moneda ISO (el valor actual del parámetro NLS_ISO_CURRENCY).
re Devuelve en la posición especificada el carácter decimal, que es el valor actual del parámetro NLS_NUMERIC_CHARACTER. El valor predeterminado es un punto (.). Restricción: puede especificar solo un carácter decimal en un modelo de formato numérico.
EEE Devuelve un valor en notación científica.
FM Devuelve un valor sin espacios en blanco al principio o al final.
GRAMO Devuelve en la posición especificada el separador de grupo (el valor actual del parámetro NLS_NUMERIC_CHARACTER). Puede especificar varios separadores de grupo en un modelo de formato de número. Restricción: un separador de grupo no puede aparecer a la derecha de un carácter decimal o un punto en un modelo de formato numérico
L Devuelve en la posición especificada el símbolo de la moneda local (el valor actual del parámetro NLS_CURRENCY).
MI Devuelve un valor negativo con un signo menos (-) al final. Devuelve un valor positivo con un espacio en blanco al final. Restricción: el elemento de formato MI solo puede aparecer en la última posición de un modelo de formato numérico.
PR Devuelve un valor negativo en. Solo puede aparecer al final de un modelo de formato de número.
Enfermera, rm Devuelve un valor como números romanos en mayúsculas. Devuelve un valor como números romanos en minúsculas. El valor puede ser un número entero entre 1 y 3999.
S Devuelve un valor negativo con un signo menos al principio o al final (-). Devuelve un valor positivo con un signo más al principio o al final (+). Restricción: el elemento de formato S solo puede aparecer en la primera o última posición de un modelo de formato numérico.
TM "Texto mínimo". Devuelve (en salida decimal) la menor cantidad de caracteres posible. Este elemento no distingue entre mayúsculas y minúsculas.
U Devuelve en la posición especificada el símbolo de moneda dual "Euro" (u otro) (el valor actual del parámetro NLS_DUAL_CURRENCY).
V Devuelve un valor multiplicado por 10n (y si es necesario, redondee hacia arriba), donde n es el número de 9 después de la "V".
X Devuelve el valor hexadecimal del número especificado de dígitos.

Función TO_NUMBER

La función TO_NUMBER convierte un valor de carácter en un tipo de datos numérico. Si la cadena que se está convirtiendo contiene caracteres no numéricos, la función devuelve un error.

Sintaxis

TO_NUMBER (string1, [format], [nls_parameter])

La siguiente tabla muestra la lista de modelos de formato que se pueden usar para encasillar valores de caracteres como números usando TO_NUMBER.

Modelo de formato Descripción
CC Siglo
SCC Siglo antes de Cristo con el prefijo -
YYYY Año con 4 números
SYYY Año antes de Cristo con el prefijo -
IYYY Año ISO con 4 números
YY Año con 2 números
RR Año con 2 números con compatibilidad Y2k
AÑO Año en caracteres
SYEAR Año en caracteres, BC con el prefijo -
antes de Cristo Indicador BC / AD
Q Trimestre en números (1,2,3,4)
MM Mes del año 01, 02 ... 12
MES Mes en caracteres (es decir, enero)
LUN ENE, FEB
WW Número de semana (es decir, 1)
W Número de semana del mes (es decir, 5)
IW Número de semana del año en norma ISO.
DDD Día del año en números (es decir, 365)
DD Día del mes en números (es decir, 28)
re Día de la semana en números (es decir, 7)
DÍA Día de la semana en caracteres (es decir, lunes)
FMDAY Día de la semana en caracteres (es decir, lunes)
DY Día de la semana en una breve descripción de caracteres (es decir, SUN)
J Día Juliano (número de días desde el 1 de enero de 4713 a. C., donde el 1 de enero de 4713 a. C. es 1 en Oracle)
HH, H12 Número de hora del día (1-12)
HH24 Número de hora del día con notación de 24 horas (0-23)
AM PM Am o PM
PIERDA Número de minutos y segundos (es decir, 59),
SSSSS Número de segundos este día.
DS Formato de fecha corta. Depende de la configuración de NLS. Usar solo con marca de tiempo.
DL Formato de fecha larga. Depende de la configuración de NLS. Usar solo con marca de tiempo.
mi Nombre de la era abreviado. Válido solo para calendarios: Imperial japonés, Oficial de la República de China, Buda tailandés.
EE El nombre completo de la era
FF Las fracciones de segundo. Usar con marca de tiempo.
FF1..FF9 Las fracciones de segundo. Usar con marca de tiempo. El dígito controla el número de dígitos decimales que se utilizan durante fracciones de segundo.
FM Modo de relleno: suprime los espacios en blanco en la salida de la conversión
FX Formato exacto: requiere una coincidencia de patrones exacta entre los datos y el modelo de formato.
IYY O IY O YO Los últimos 3,2,1 dígitos del año estándar ISO. Solo salida
RM La representación en números romanos del mes (I .. XII)
RR Los 2 últimos dígitos del año.
RRRR Los 2 últimos dígitos del año cuando se utilizan para la salida. Acepta años de cuatro dígitos cuando se utiliza como entrada.
SP Formato escrito. Puede aparecer al final de un elemento numérico. El resultado siempre está en inglés. Por ejemplo, el mes 10 en formato MMSP devuelve "diez"
SPTH Formato ordinal y ortográfico; 1 da como resultado primero.
TH Convierte un número a su formato ordinal. Por ejemplo, 1 se convierte en el primero.
TS Formato de tiempo corto. Depende de la configuración de NLS. Usar solo con marca de tiempo.
TZD Nombre abreviado de la zona horaria. es decir, PST.
TZH, TZM Desplazamiento de hora / minuto de zona horaria.
TZR Región de zona horaria
X Carácter de base local. En Estados Unidos este es un período (.)

Las consultas SELECT siguientes aceptan números como entradas de caracteres y los imprimen siguiendo el especificador de formato.

SELECT  TO_NUMBER('121.23', '9G999D99') 
FROM DUAL

TO_NUMBER('121.23','9G999D99')
------------------------------
                        121.23

SELECT  TO_NUMBER('1210.73', '9999.99') 
FROM DUAL;

TO_NUMBER('1210.73','9999.99')
------------------------------
                       1210.73

Función TO_DATE

La función toma valores de caracteres como entrada y devuelve una fecha formateada equivalente a la misma. La función TO_DATE permite a los usuarios ingresar una fecha en cualquier formato, y luego convierte la entrada al formato predeterminado utilizado por Oracle 11g.

Sintaxis:

TO_DATE( string1, [ format_mask ], [ nls_language ] )

Un argumento format_mask consta de una serie de elementos que representan exactamente el aspecto que deberían tener los datos y deben introducirse entre comillas simples.

Modelo de formato Descripción
AÑO Año, deletreado
YYYY Año de 4 dígitos
YYY, YY, Y Últimos 3, 2 o 1 dígito (s) del año.
IYY, IY, yo Últimos 3, 2 o 1 dígito (s) del año ISO.
IYYY Año de 4 dígitos según el estándar ISO
RRRR Acepta un año de 2 dígitos y devuelve un año de 4 dígitos.
Q Trimestre del año (1, 2, 3, 4; ENE-MAR = 1).
MM Mes (01-12; ENE = 01).
LUN Nombre abreviado del mes.
MES Nombre del mes, rellenado con espacios en blanco hasta una longitud de 9 caracteres.
RM Mes en números romanos (I-XII; ENE = I).
WW Semana del año (1-53) donde la semana 1 comienza el primer día del año y continúa hasta el séptimo día del año.
W Semana del mes (1-5) donde la semana 1 comienza el primer día del mes y termina el séptimo.
IW Semana del año (1-52 o 1-53) según la norma ISO.
re Día de la semana (1-7).
DÍA Nombre del día.
DD Día del mes (1-31).
DDD Día del año (1-366).
DY Nombre abreviado del día.
J Día juliano; el número de días desde el 1 de enero de 4712 a. C.
HH12 Hora del día (1-12).
HH24 Hora del día (0-23).
PIERDA Minuto (0-59).
SSSSS Segundos después de la medianoche (0-86399).
FF Segundos fraccionarios. Utilice un valor del 1 al 9 después de FF para indicar el número de dígitos en fracciones de segundo. Por ejemplo, 'FF4'.
AM PM Indicador de meridiano
AD, BC Indicador AD, BC
TZD Información de horario de verano. Por ejemplo, 'PST'
TZH, TZM, TZR Hora / minuto / región de la zona horaria.

El siguiente ejemplo convierte una cadena de caracteres en una fecha:

SELECT TO_DATE('January 15, 1989, 11:00 A.M.',  'Month dd, YYYY, HH:MI A.M.',  'NLS_DATE_LANGUAGE = American')
FROM DUAL;

TO_DATE('
---------
15-JAN-89

Funciones generales

Las funciones generales se utilizan para manejar valores NULL en la base de datos. El objetivo de las funciones generales de manejo de NULL es reemplazar los valores NULL con un valor alternativo. Veremos brevemente estas funciones a continuación.

NVL

La función NVL sustituye un valor alternativo por un valor NULO.

Sintaxis:

NVL( Arg1, replace_with )

En la sintaxis, ambos parámetros son obligatorios. Tenga en cuenta que la función NVL funciona con todo tipo de tipos de datos. Y también que el tipo de datos de la cadena original y el reemplazo deben estar en un estado compatible, es decir, el mismo o implícitamente convertible por Oracle.

Si arg1 es un valor de carácter, entonces Oracle convierte la cadena de reemplazo al tipo de datos compatible con arg1 antes de compararlos y devuelve VARCHAR2 en el juego de caracteres de expr1. Si arg1 es numérico, Oracle determina el argumento con mayor precedencia numérica, convierte implícitamente el otro argumento a ese tipo de datos y devuelve ese tipo de datos.

La instrucción SELECT a continuación mostrará 'n / a' si un empleado aún no ha sido asignado a ningún trabajo, es decir, JOB_ID es NULL. De lo contrario, mostraría el valor real de JOB_ID.

SELECT  first_name, NVL(JOB_ID, 'n/a')
FROM employees;

NVL2

Como una mejora sobre NVL, Oracle introdujo una función para sustituir valor no solo por valores de columnas NULL sino también por columnas NOT NULL. La función NVL2 se puede utilizar para sustituir un valor alternativo para NULL y un valor no NULL.

Sintaxis:

NVL2( string1, value_if_NOT_null, value_if_null )

La instrucción SELECT a continuación mostraría 'Bench' si el JOB_CODE para un empleado es NULL. Para un valor definido no nulo de CÓDIGO DE TRABAJO, mostraría el valor constante 'Trabajo asignado'.

SQL> SELECT NVL2(JOB_CODE, 'Job Assigned', 'Bench')
FROM employees;

NULLIF

La función NULLIF compara dos argumentos expr1 y expr2. Si expr1 y expr2 son iguales, devuelve NULL; de lo contrario, devuelve expr1. A diferencia de la otra función de manejo de nulos, el primer argumento no puede ser NULL.

Sintaxis:

NULLIF (expr1, expr2)

Tenga en cuenta que el primer argumento puede ser una expresión que se evalúe como NULL, pero no puede ser el literal NULL. Ambos parámetros son obligatorios para que la función se ejecute.

La siguiente consulta devuelve NULL ya que ambos valores de entrada, 12, son iguales.

SELECT	NULLIF (12, 12)
FROM DUAL;

De manera similar, la consulta siguiente devuelve 'SUN' ya que ambas cadenas no son iguales.

SELECT NULLIF ('SUN', 'MOON')
FROM DUAL;

JUNTARSE

La función COALESCE, una forma más genérica de NVL, devuelve la primera expresión no nula en la lista de argumentos. Requiere un mínimo de dos parámetros obligatorios, pero el máximo de argumentos no tiene límite.

Sintaxis:

COALESCE (expr1, expr2, ... expr_n )

Considere la siguiente consulta SELECT. Selecciona el primer valor no nulo introducido en los campos de dirección de un empleado.

SELECT COALESCE (address1, address2, address3) Address
FROM  employees;

Curiosamente, el funcionamiento de la función COALESCE es similar a la construcción IF..ELSIF..ENDIF. La consulta anterior se puede reescribir como:

IF address1 is not null THEN
   result := address1;
ELSIF address2 is not null THEN
   result := address2;
ELSIF address3 is not null THEN
   result := address3;
ELSE
   result := null;
END IF;

Funciones condicionales

Oracle proporciona funciones condicionales DECODE y CASE para imponer condiciones incluso en declaraciones SQL.

La función DECODIFICAR

La función es la equivalencia SQL de IF..THEN..ELSE instrucción de procedimiento condicional. DECODE trabaja con valores / columnas / expresiones de todos los tipos de datos.

Sintaxis:

DECODE (expression, search, result [, search, result]... [, default])

La función DECODIFICAR compara la expresión con cada valor de búsqueda en orden. Si existe igualdad entre la expresión y el argumento de búsqueda, devuelve el resultado correspondiente. En caso de que no coincida, se devuelve el valor predeterminado, si está definido, en caso contrario, NULL. En caso de que exista alguna discrepancia en la compatibilidad de tipos, Oracle realiza internamente una posible conversión implícita para devolver los resultados.

De hecho, Oracle considera que dos nulos son equivalentes mientras trabaja con la función DECODE.

SELECT DECODE(NULL,NULL,'EQUAL','NOT EQUAL') 
FROM DUAL;

DECOD
-----
EQUAL

Si expresión es nula, Oracle devuelve el resultado de la primera búsqueda que también es nulo. El número máximo de componentes en la función DECODIFICAR es 255.

SELECT	first_name, salary, DECODE (hire_date, sysdate,'NEW JOINEE','EMPLOYEE')
	FROM employees;

Expresión CASE

Las expresiones CASE funcionan con el mismo concepto que DECODE pero difieren en sintaxis y uso.

Sintaxis:

CASE  [ expression ]
   WHEN condition_1 THEN result_1
   WHEN condition_2 THEN result_2
   ...
   WHEN condition_n THEN result_n
   ELSE result
END

La búsqueda de Oracle comienza desde la izquierda y se mueve hacia la derecha hasta que encuentra una condición verdadera y luego devuelve la expresión de resultado asociada con ella. Si no se encuentra ninguna condición verdadera y existe una cláusula ELSE, Oracle devuelve el resultado definido con else. De lo contrario, Oracle devuelve nulo.

El número máximo de argumentos en una expresión CASE es 255. Todas las expresiones cuentan para este límite, incluida la expresión inicial de una expresión CASE simple y la expresión ELSE opcional. Cada par CUANDO ... ENTONCES cuenta como dos argumentos. Para evitar exceder este límite, puede anidar expresiones CASE para que return_expr en sí sea una expresión CASE.

SELECT first_name, CASE	WHEN salary < 200 THEN 'GRADE 1'
			WHEN salary > 200 AND salary < 5000 THEN 'GRADE 2'
			ELSE 'GRADE 3'
		   END CASE
FROM employees;	

ENAM  	CASE
----    -------
JOHN    GRADE 2
EDWIN   GRADE 3
KING    GRADE 1