Funciones para convertir datos a otros tipos de datos. TO_CHAR(número) convierte un número en texto. TO_NUMBER(cadena) convierte el texto en un número.
SELECT TO_CHAR (123) FROM DUAL devolverá la cadena 123, SELECT TO_NUMBER (`12345") FROM DUAL devolverá el número 12345.
Cambios de formato numérico en Oracle SQL, función TO_CHAR para trabajar con valores numéricos.
Ejercicio:
Escriba una consulta que muestre información sobre el nombre, el apellido y el salario de los empleados de la tabla hr.employees en el formato que se muestra en la figura 1. 3.4-1:
Arroz. 3.4 -1
Al mismo tiempo, los datos deben ordenarse de tal manera que las filas de los empleados con el salario más alto se muestren primero.
Nota:
Algunos valores de salario en la Fig. 3.4-1 se han cambiado, por lo que es posible que no coincidan con sus valores.
Solución:
SELECCIONE first_name COMO "Nombre", last_name Como "Apellido", TO_CHAR(SALARIO, "L999999999.99") Como "Salario" FROM hr.employees ORDENAR POR SALARIO DESC.
Función para convertir la cadena a la fecha TO_DATE (cadena, formato). Los posibles valores de formato ya se han discutido anteriormente, por lo que daré algunos ejemplos del uso de esta función. Ejemplos:
SELECCIONE HASTA_FECHA("01/01/2010", `DD.MM.AAAA") DE DUAL devolverá la fecha `01.01.2010";
SELECCIONE TO_DATE("01.ENE.2010",`DD.MON.YYYY") DE DUAL devolverá la fecha `01.01.2009";
SELECCIONE TO_DATE("15-01-10",`DD-MM-AA") DE DUAL devolverá la fecha `15/01/2010'.
Una función para convertir una cadena en un valor numérico TO_NUMBER (cadena, formato). Los valores de formato más comunes se enumeran en la tabla, así que consideremos el uso de esta función usando ejemplos. Ejemplos:
SELECCIONE A_NUMERO(`100") DE DUAL devolverá el número 100 SELECT TO_NUMBER(`0010.01", "9999D99") DE DUAL devolverá el número 10.01;
SELECCIONE A_NUMERO("500,000", "999G999") DE DUAL devolverá el número 500000.
El elemento de formato de fecha y hora RR es similar al elemento de formato de fecha y hora YY, pero proporciona flexibilidad adicional para almacenar valores de fecha en otros siglos. El elemento de formato de fecha y hora RR le permite almacenar fechas del siglo XX en el siglo XXI especificando solo los dos últimos dígitos del año.
Si los dos últimos dígitos del año actual son 00 a 49, entonces el año devuelto tiene los mismos primeros dos dígitos que el año actual.
Si los dos últimos dígitos del año actual están entre 50 y 99, entonces los primeros 2 dígitos del año devuelto son 1 mayor que los primeros 2 dígitos del año actual.
Si los dos últimos dígitos del año actual van del 00 al 49, entonces los primeros 2 dígitos del año devuelto son 1 menos que los primeros 2 dígitos del año actual.
Si los dos últimos dígitos del año actual están entre 50 y 99, entonces el año devuelto tiene los mismos primeros dos dígitos que el año actual.
La función NVL es generalmente la más utilizada. La función recibe dos parámetros: NVL(expr1, expr2). Si el primer parámetro expr1 no es NULL, la función devuelve su valor. Si el primer parámetro es NULL, la función devuelve el valor del segundo parámetro exp2 en su lugar.
Ejemplo: seleccione NVL (ciudad_proveedor, n/a") de los proveedores:
En la sentencia SQL anterior, devolverá n/" si el campo ciudad_proveedor contiene un valor nulo. De lo contrario, devolverá el valor ciudad_proveedor.
Otro ejemplo del uso de la función NVL en Oracle/PLSQL es:
seleccione id_proveedor, NVL (desc_proveedor, nombre_proveedor) de los proveedores.
Esta instrucción SQL devolverá Nombre del proveedor campo si proveedor_desc contiene un valor nulo. De lo contrario, volverá proveedor_desc.
Último ejemplo: usar la función NVL en Oracle/PLSQL es: seleccionar NVL (comisión, 0) de ventas;
Esta instrucción SQL devolvió el valor 0 si comisión el campo contiene un valor nulo. De lo contrario, volvería comisiones campo.
Para convertir un valor indefinido en un valor real, use la función NVL: NVL ( expresión1, expresión2), dónde:
expresión1- El valor original o calculado, que puede ser nulo.
expresión2- El valor que se sustituye por el valor nulo.
Nota: La función NVL se puede usar para convertir cualquier tipo de datos, pero el resultado siempre será del mismo tipo que expresión1.
conversión NVL para varios tipos:
NÚMERO-NVL (columna numérica, 9).
CHAR o VARCHAR2 - NVL (caracteres|columna,"Indisponible").
Función NVL para tratar con valores nulos en Oracle SQL.
Ejercicio:
Escriba una consulta que muestre información sobre los nombres y apellidos de los empleados de la tabla hr.employees., así como la tasa de comisión (columna COMMISSION_PCT) para el empleado. En este caso, para aquellos empleados para quienes la comisión no está definida, debe mostrar el valor 0. El resultado de la consulta debe ser el mismo que se muestra en la Fig. 3.5-1.
Arroz. 3.5 -1 (valores mostrados a partir de la línea 51)
Solución:
El código de solicitud correspondiente podría verse así:
SELECCIONE first_name COMO "Nombre", last_name Como "Apellido", NVL (COMMISSION_PCT, 0) Como "Tasa de comisión" DE hr.employees.
La función NVL es generalmente la más utilizada. La función recibe dos parámetros: NVL(expr1, expr2). Si el primer parámetro expr1 no es NULL, la función devuelve su valor. Si el primer parámetro es NULL, la función devuelve el valor del segundo parámetro expr2 en su lugar.
Consideremos un ejemplo práctico. El campo COMM en la tabla EMP puede contener valores NULL. Al ejecutar una consulta como:
SELECCIONE EMPNO, ENAME, COMM, NVL(COMM, 0) NVL_COMM
DE SCOTT.EMP
el valor NULL será reemplazado por cero. Tenga en cuenta que si se genera un valor mediante una función, se le asigna un alias. Los resultados de la consulta se verán así:
| EMPNO | NOMBRE | COMUNICACIÓN | NVL_COMM |
| 7369 | HERRERO | 0 | |
| 7499 | allen | 300 | 300 |
| 7521 | PABELLÓN | 500 | 500 |
| 7566 | JONES | 0 | |
| 7654 | MARTÍN | 1400 | 1400 |
| 7698 | BLAKE | 0 | |
| 7782 | CLARK | 0 | |
| 7839 | REY | 0 | |
| 7844 | TORNERO | 0 | 0 |
| 7900 | JAMES | 0 | |
| 7902 | VADO | 0 | |
| 7934 | MOLINERO | 0 |
La función CEIL devuelve el entero más pequeño mayor o igual que el número n pasado como parámetro. Por ejemplo:
SELECCIONE CEIL(100) X1, CEIL(-100) X2, CEIL(100.2) X3 , CEIL(-100.2) X4
DESDE DOBLE
La función TRUNC devuelve el número n truncado a m decimales. El parámetro m puede omitirse, en cuyo caso n se trunca a un número entero.
SELECCIONAR TRONCO(100.25678) X1, TRONCO(-100.25678) X2, TRONCO(100.99) X3,
TRONCO (100.25678, 2) X4
DESDE DOBLE
La función SIGNO determina el signo de un número. Si n es positivo, la función devuelve 1. Si es negativo, devuelve -1. Si es cero, se devuelve 0. Por ejemplo:
SELECCIONE SIGNO(100.22) X1, SIGNO(-100.22) X2, SIGNO(0) X3
DESDE DOBLE
Una característica interesante de esta función es la capacidad de pasar m igual a cero; en este caso, no hay error de división por 0.
La función POTENCIA eleva el número n a la potencia m. El grado puede ser fraccionario y negativo, lo que amplía significativamente las capacidades de esta función.
SELECCIONE POTENCIA (10, 2) X1, POTENCIA (100, 1/2) X2,
POTENCIA (1000, 1/3) X3, POTENCIA (1000, -1/3) X4
DESDE DOBLE
| X1 | X2 | X3 | X4 |
| 100 | 10 | 10 | 0,1 |
En algunos casos, puede ocurrir una excepción al llamar a esta función. Por ejemplo:
SELECCIONE POTENCIA (-100, 1/2) X2
DESDE DOBLE
En este caso, se intenta calcular la raíz cuadrada de un número negativo, lo que dará como resultado un error ORA-01428 "Argumento fuera de rango".
Esta función devuelve la raíz cuadrada de n. Por ejemplo:
SELECCIONAR CUADRADO(100) X
DESDE DOBLE
La función EXP eleva e a la potencia de n, y la función LN calcula el logaritmo natural de n (con n debe ser mayor que cero). Ejemplo:
SELECCIONAR EXP(2) X1, LN(1) X2, LN(EXP(2)) X3
Tocamos las funciones anidadas un poco antes, ahora veámoslas con un poco más de detalle. También veremos funciones para trabajar con el valor NULL y funciones que ayudan a implementar la operación de bifurcación en una consulta.
funciones anidadas
Las funciones anidadas utilizan el valor de retorno de una función como parámetro de entrada para otra función. Las funciones siempre devuelven un solo valor. Por lo tanto, puede tratar el resultado de una llamada de función como un valor literal cuando lo usa como parámetro para otra llamada de función. Las funciones de fila se pueden anidar hasta cualquier nivel de anidamiento. Una sola llamada de función se ve así
Función1(parámetro1, parámetro2, …) = resultado
Reemplazar un parámetro de función con una llamada a otra función puede generar expresiones como
F1(parámetro 1.1, F2(parámetro 2.1, parámetro 2.2, F3(parámetro 3.1)), parámetro 1.3)
Las funciones anidadas se evalúan primero antes de que sus resultados se utilicen como valores de entrada para otras funciones. Las funciones se evalúan desde el nivel de anidamiento más profundo hasta el más alto de izquierda a derecha. La expresión anterior se ejecuta de la siguiente manera
Por lo tanto, la función F3 está en el tercer nivel de anidamiento.
Considere una consulta
seleccione next_day(last_day(sysdate)-7, 'tue') from dual;
Es bastante difícil entender y construir expresiones complejas usando muchas llamadas a funciones anidadas, pero esto viene con el tiempo y la práctica. Puede dividir tales expresiones en partes y probarlas por separado. La tabla DUAL es muy útil para probar consultas y los resultados de las llamadas a funciones. Puede probar y depurar componentes pequeños, que luego se combinan en una gran expresión deseada.
Funciones de rama
Las funciones de bifurcación, también conocidas como IF-THEN-ELSE, se utilizan para determinar la ruta de ejecución según algunas circunstancias. Las funciones de bifurcación devuelven diferentes resultados según el resultado de evaluar la condición. En el grupo de tales funciones, hay funciones para trabajar con el valor NULL: NVL, NVL2, NULLIF y COALESCE. Y también las funciones generales representadas por la función DECODE y la expresión CASE. La función DECODE es una función de Oracle, mientras que la expresión CASE está en el estándar ANSI SQL.
Función NVL
La función NVL verifica el valor de una columna o expresión de cualquier tipo de datos para un valor nulo. Si el valor es NULL, devuelve un valor predeterminado alternativo no NULL; de lo contrario, se devuelve el valor original.
La función NVL tiene dos parámetros obligatorios y la sintaxis es NVL (original, si es nulo), donde original es el valor original que se va a verificar y si es nulo es el resultado devuelto por la función si el original es NULL. El tipo de datos de los parámetros ifnull y original debe ser compatible. Es decir, o el tipo de datos debe ser el mismo o debe ser posible convertir implícitamente valores de un tipo a otro. La función NVL devuelve un valor del mismo tipo de datos que el tipo de datos del parámetro original. Considere tres consultas
Consulta 1: seleccione nvl (1234) de dual;
Consulta 2: seleccione nvl (null, 1234) de dual;
Consulta 3: seleccione nvl(substr('abc', 4), 'No existe ninguna subcadena') de dual;
Dado que la función NVL requiere dos parámetros, la consulta 1 devolverá un ORA-00909: error de número de argumentos no válido. La consulta 2 devolverá 1234 porque se está comprobando NULL y es NULL. La consulta tres usa una función SUBSTR anidada que intenta extraer el cuarto carácter de una cadena de tres caracteres, devuelve NULL y la función NVL devuelve la cadena 'No existe sbustring'.
La función NVL es muy útil cuando se trabaja con números. Se utiliza para convertir valores NULL a 0 para que las operaciones aritméticas sobre números no devuelvan NULL.
Función NVL2
La función NVL2 proporciona más funciones que NVL, pero también maneja NULL. Comprueba el valor de una columna o expresión de cualquier tipo para un valor nulo. Si el valor no es NULL, se devuelve el segundo parámetro; de lo contrario, se devuelve el tercer parámetro, a diferencia de la función NVL, que en este caso devuelve el valor original.
La función NVL2 tiene tres parámetros requeridos y la sintaxis es NVL2 (original, si no es nulo, si es nulo), donde original es el valor a probar, si no es nulo es el valor a devolver si el original no es NULL y si es nulo es el valor a devolver si el original es NULO. Los tipos de datos de los parámetros ifnotnull e ifnull deben ser compatibles y no pueden ser de tipo LONG. El tipo de datos devuelto por la función NVL2 es igual al tipo de datos del parámetro ifnotnull. Veamos algunos ejemplos
Consulta 1: seleccione nvl2 (1234, 1, 'una cadena') de dual;
Consulta 2: seleccione nvl2 (null, 1234, 5678) de dual;
Consulta 3: seleccione nvl2(substr('abc', 2), 'No bc', 'Sin subcadena') de dual;
El parámetro ifnotnull en la consulta 1 es un número y el parámetro ifnull es una cadena. Dado que los tipos de datos son incompatibles, se devuelve el error "ORA-01722: número no válido". La consulta dos devuelve un parámetro ifnull porque el original es NULL y el resultado es 5678. La consulta tres usa la función SUBSTR que devuelve 'bc' y llama a NVL2('bc','Not bc','No substring'), que devuelve un ifnotnull parámetro – 'No bc'.
Función NULLIF
La función NULLIF prueba dos valores para el mismo. Si son iguales, se devuelve NULL, de lo contrario, se devuelve el primer parámetro. La función NULLIF tiene dos parámetros obligatorios y la sintaxis es NULLIF(ifunequal, compare_item). La función compara dos parámetros, y si son idénticos, devuelve NULL, de lo contrario, el parámetro si no es igual. Considerar consultas
Consulta 1: seleccione nullif (1234, 1234) de dual;
La consulta uno devuelve NULL porque los parámetros son idénticos. Las cadenas de la consulta 2 no se convierten en una fecha, sino que se comparan como cadenas. Dado que las cadenas tienen diferentes longitudes, se devuelve el parámetro ifunequal 24-JUL-2009.
En la Figura 10-4, la función NULLIF está anidada dentro de la función NVL2. La función NULLIF, a su vez, usa las funciones SUBSTR y UPPER como parte de la expresión en el parámetro ifunequal. La columna EMAIL se compara con esta expresión, que devuelve la primera letra del nombre concatenada con el apellido para los empleados cuyo nombre tiene 4 caracteres. Cuando estos valores sean iguales, NULLIF devolverá NULL, en caso contrario devolverá el valor del parámetro ifunequal. Estos valores se utilizan como parámetro para la función NVL2. NVL2, a su vez, devuelve una descripción de si los elementos comparados coinciden o no.
Figura 10-4 - Uso de la función NULLIF
Función COALESCE
La función COALESCE devuelve el primer valor no NULL de la lista de parámetros. Si todos los parámetros son NULL, se devuelve NULL. La función COALESCE tiene dos parámetros requeridos y cualquier número de parámetros opcionales, y la sintaxis COALESCE(expr1, expr2, ..., exprn) donde el resultado es expr1 si el valor de expr 1 no es NULL, de lo contrario el resultado es expr2 si no es NULL, y así sucesivamente. COALESCE tiene el mismo significado que las funciones NVL anidadas
COALESCE(expr1, expr2) = NVL(expr1, expr2)
COALESCE(expr1, expr2, expr3) = NVL(expr1,NVL(expr2, expr3))
El tipo de datos del valor devuelto si se encuentra un valor no NULL es igual al tipo de datos del primer valor no NULL. Para evitar el error 'ORA-00932: tipos de datos inconsistentes', todos los parámetros que no sean NULL deben ser compatibles con el primer parámetro que no sea NULL. Considere tres ejemplos
Consulta 1: seleccione coalesce(null, null, null, 'a string') de dual;
Consulta 2: seleccione fusionar (nulo, nulo, nulo) de dual;
Consulta 3: seleccione coalesce(substr('abc', 4), 'Not bc', 'No substring') from dual;
La consulta 1 devuelve el cuarto parámetro: una cadena, ya que es el primer parámetro no NULL. La consulta dos devuelve NULL porque todos los parámetros son NULL. La consulta 3 evalúa el primer parámetro, obtiene NULL y devuelve el segundo parámetro porque es el primer parámetro no NULL.
Los parámetros de la función NVL2 pueden resultar confusos si ya está familiarizado con la función NVL. NVL(original, ifnull) devuelve original si el valor no es NULL, de lo contrario, si es nulo. NVL2 (original, si no es nulo, si es nulo) devuelve si no es nulo si el original no es nulo; de lo contrario, si es nulo. La confusión proviene del hecho de que el segundo parámetro de la función NVL es ifnull, mientras que NVL2 tiene ifnotnull. Así que no confíes en la posición del parámetro en la función.
Función DECO
La función DECODE implementa la lógica if-then-else comprobando la igualdad de los dos primeros parámetros y devolviendo el tercer valor si son iguales, o un valor diferente si no lo son. La función DECODE tiene tres parámetros requeridos y la sintaxis es DECODE(expr1, comp1, iftrue1, , ). Estos parámetros se utilizan como se muestra en el siguiente ejemplo de pseudocódigo.
SI expr1=comp1 entonces devuelve iftrue1
De lo contrario, si expr1 = comp2, devuelva iftrue2
De lo contrario, si exprN = compN, devuelva iftrueN
De lo contrario, devuelve NULL | iffalse;
Primero, expr1 se compara con comp1. Si son iguales, se devuelve iftrue1. Si expr1 no es igual a comp1, lo que sucede a continuación depende de si se especifican los parámetros comp2 y iftrue2. Si se da, entonces el valor de expr1 se compara con comp2. Si los valores son iguales, se devuelve iftrue2. Si no, entonces si hay compN, se comparan pares de parámetros iftrueN, expr1 y compN, y si es verdadero, se devuelve iftrueN si es igual. Si no se encuentra ninguna coincidencia en ningún conjunto de parámetros, se devuelve iffalse si se especificó este parámetro o NULL.
Todos los parámetros de la función DECODE pueden ser expresiones. El tipo del valor devuelto es igual al tipo del primer validador: el parámetro comp 1. Expresión expr 1 se convierte implícitamente al tipo de datos de parámetro de comparación1. Todas las demás opciones de compensación disponibles 1 … componente también se convierten implícitamente al tipo comp 1. DECODE trata un valor NULL como igual a otro valor NULL, es decir si expr1 es NULL y comp3 es NULL y comp2 no es NULL, entonces se devuelve iftrue3. Veamos algunos ejemplos
Consulta 1: seleccione decodificar (1234, 123, '123 es una coincidencia') de dual;
Consulta 2: seleccione decodificar (1234, 123, '123 es una coincidencia', 'No coincide') de dual;
Consulta 3: seleccione decode('buscar', 'comp1', 'true1', 'comp2', 'true2', 'buscar', 'true3', substr('2search', 2, 6), 'true4', ' falso') de dual;
La consulta uno compara el valor 1234 y 123. Dado que no son iguales, se ignora iftrue1 y, dado que iffalse no está definido, se devuelve NULL. La consulta dos es idéntica a la consulta 1 excepto que se define el valor iffalse. Dado que 1234 no es igual a 123, si es falso, se devuelve 'No hay coincidencia'. La consulta tres compara los valores de los parámetros con el valor de búsqueda. Los parámetros comp1 y comp2 no son iguales a 'buscar', por lo que se omiten los resultados de iftrue1 y iftrue2. Se encuentra una coincidencia en la tercera operación de comparación del elemento comp3 (posición de parámetro 6) y el valor de retorno de iftrue3 (parámetro 7) es 'true3'. Dado que se encuentra una coincidencia, no se realizan más cálculos. Es decir, a pesar de que el valor de comp4 (parámetro 8) también coincide con expr1, esta expresión nunca se evalúa porque la coincidencia se encontró en la comparación anterior.
expresión CASO
Todos los lenguajes de programación de tercera y cuarta generación implementan la construcción de casos. Al igual que la función DECODE, la expresión CASE le permite implementar la lógica if-then-else. Hay dos opciones para usar la expresión CASE. Una declaración CASE simple establece que el elemento de origen se comparará una vez y luego enumera todas las condiciones de prueba necesarias. El CASE complejo (buscado) evalúa ambas sentencias para cada condición.
La expresión CASE tiene tres parámetros obligatorios. La sintaxis de una expresión depende del tipo. Para una expresión CASE simple se ve así
CASE search_expr
CUANDO comparación_expr1 ENTONCES iftrue1
)
La función TRUNC devuelve el número n truncado a m decimales. El parámetro m puede omitirse, en cuyo caso n se trunca a un número entero.
SELECCIONAR TRONCO(100.25678) X1, TRONCO(-100.25678) X2, TRONCO(100.99) X3,
TRONCO (100.25678, 2) X4
DESDE DOBLE
La función SIGNO determina el signo de un número. Si n es positivo, la función devuelve 1. Si es negativo, devuelve -1. Si es cero, se devuelve 0. Por ejemplo:
SELECCIONE SIGNO(100.22) X1, SIGNO(-100.22) X2, SIGNO(0) X3
DESDE DOBLE
Una característica interesante de esta función es la capacidad de pasar m igual a cero; en este caso, no hay error de división por 0.
La función POTENCIA eleva el número n a la potencia m. El grado puede ser fraccionario y negativo, lo que amplía significativamente las capacidades de esta función.
SELECCIONE POTENCIA (10, 2) X1, POTENCIA (100, 1/2) X2,
POTENCIA (1000, 1/3) X3, POTENCIA (1000, -1/3) X4
DESDE DOBLE
| X1 | X2 | X3 | X4 |
| 100 | 10 | 10 | 0,1 |
En algunos casos, puede ocurrir una excepción al llamar a esta función. Por ejemplo:
SELECCIONE POTENCIA (-100, 1/2) X2
DESDE DOBLE
En este caso, se intenta calcular la raíz cuadrada de un número negativo, lo que dará como resultado un error ORA-01428 "Argumento fuera de rango".
Esta función devuelve la raíz cuadrada de n. Por ejemplo:
SELECCIONAR CUADRADO(100) X
DESDE DOBLE
La función EXP eleva e a la potencia de n, y la función LN calcula el logaritmo natural de n (con n debe ser mayor que cero). Ejemplo:
SELECCIONAR EXP(2) X1, LN(1) X2, LN(EXP(2)) X3
