Sean bienvenidos a esta secci贸n donde se revisa las formas de describir los diferentes tipos de funciones disponibles en SQL, 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 los diferentes tipos de funciones disponibles en SQL
- Utilizar caracteres, n煤meros y funciones de fecha en sentencias SELECT
Las funciones hacen m谩s potente el bloque de consultas b谩sico y se utilizan para manipular los valores de datos. Las primeras dos lecciones de este curso analizan las funciones. Se centra en funciones de fecha, de car谩cter de una fila y de n煤mero.
Funciones SQL de una sola fila
Funciones SQL
Las funciones son una caracter铆stica muy potente de SQL. Se puede utilizar para realizar las siguientes acciones:
- Realizar c谩lculos en los datos
- Modificar elementos de datos individuales
- Manipular la salida para grupos de filas
- Formatear fechas y n煤meros para su visualizaci贸n
- Convertir tipos de dato de columna
Algunas veces, las funciones SQL toman argumentos y siempre devuelven un valor.
Nota: si desea saber si una funci贸n es compatible con SQL:2003,2020, consulte la secci贸n sobre compatibilidad con SQL:2003,2020 de la gu铆a Oracle Database SQL Language Reference (Referencia sobre Lenguaje SQL de Oracle Database) para la base de datos 10g u 11g,etc.
Dos Tipos de Funciones SQL
Hay dos tipos de grupos de funciones:
- Funciones de una sola fila
- Funciones de varias filas
Funciones de Una Sola Fila
Estas funciones funcionan s贸lo en filas 煤nicas y devuelven un resultado por fila. Existen distintos tipos de funciones de una sola fila. En esta lecci贸n se abordan los siguientes temas:
- Car谩cter
- N煤mero
- Fecha
- Conversi贸n
- General
- Manipular elementos de datos
- Aceptar argumentos y devolver un valor
- Actuar en cada fila devuelta
- Devolver un resultado por fila
- Posibilidad de modificar el tipo de dato
- Posibilidad de anidamiento
- Aceptar argumentos que pueden ser una columna o una expresi贸n
Las funciones de una sola fila se utilizan para manipular elementos de datos. Aceptan uno o varios argumentos y devuelven un valor para cada fila devuelta por la consulta. Un argumento puede ser uno de los siguientes elementos:
- Constante proporcionada por el usuario
- Valor de variable
- Nombre de columna
- Expresiones
Las caracter铆sticas de las funciones de una sola fila son:
- Actuar en cada fila devuelta en la consulta
- Devolver un resultado por fila
- Posibilidad de devolver un valor de datos de un tipo diferente al que se hace referencia
- Posibilidad de esperar uno o m谩s argumentos
- Se pueden utilizar en cl谩usulas SELECT, WHERE y ORDER BY; posibilidad de anidamiento en la sintaxis:
arg1, arg2 Es cualquier argumento que utilizar谩 la funci贸n. Pueden estar representados por un nombre de columna o expresi贸n.
Esta lecci贸n trata las siguientes funciones de una sola fila:
- Funciones de car谩cter: aceptan la entrada de caracteres y pueden devolver valores de n煤mero y de car谩cter.
- Funciones num茅ricas: aceptan valores de entrada y devuelven valores num茅ricos.
- Funciones de fecha: operan en valores del tipo de dato DATE. (Todas las funciones de fecha devuelven un valor de tipo de dato DATE excepto la funci贸n MONTHS_BETWEEN, que devuelve un n煤mero.)
Las siguientes funciones de una sola fila se tratan en las lecciones tituladas “Uso de Funciones de Conversi贸n y Expresiones Condicionales”:
- Funciones de conversi贸n: Convierten un valor de un tipo de dato a otro
- Funciones generales:
- NVL
- NVL2
- NULLIF
- COALESCE
- CASE
- DECODE
Funciones de Varias Filas
Las funciones pueden manipular grupos de filas para proporcionar un resultado por grupo de filas. Estas funciones tambi茅n se conocen como funciones de grupo (se tratan en la lecci贸n titulada “Informes de Datos Agregados con Funciones de Grupo”).
Nota: para obtener m谩s informaci贸n y una lista completa de las funciones disponibles y su sintaxis, consulte la secci贸n sobre funciones en Oracle Database SQL Language Reference (Referencia sobre Lenguaje SQL de Oracle Database) para la base de datos 10g, 11g u versiones mas recientes.
Funciones de car谩cter
Las funciones de car谩cter de una sola fila aceptan los datos de caracteres como entrada y pueden devolver valores num茅ricos y de car谩cter. Las funciones de car谩cter se pueden dividir en:
- Funciones de conversi贸n de caracteres
- Funciones de manipulaci贸n de caracteres
Nota: las funciones tratadas en esta lecci贸n son s贸lo algunas de las funciones disponibles.
Funciones de Conversi贸n de Caracteres
Estas funciones convierten las may煤sculas/min煤sculas para cadenas de caracteres:
LOWER, UPPER y INITCAP son las tres funciones de conversi贸n de caracteres.
LOWER: convierte las cadenas de caracteres de may煤sculas en min煤sculas /min煤sculas a min煤sculas.
UPPER: convierte las cadenas de caracteres en min煤scula o en may煤sculas/min煤sculas a may煤sculas.
INITCAP: convierte la primera letra de cada palabra a may煤sculas y el resto de las letras a min煤sculas.
Ejemplo:
SELECT 'La identificaci贸n del trabajo para '|| UPPER(last_name)||' es '
|| LOWER(job_id) AS "DETALLES SOBRE EMPLEADOS"
FROM hr.employees;
Uso de Funciones de Conversi贸n de Caracteres
Mostrar el n煤mero de empleado, nombre y n煤mero de departamento del empleado Higgins:
SELECT employee_id, last_name, department_id
FROM hr.employees
WHERE last_name = 'higgins';
0 row mostrados
SELECT employee_id, last_name, department_id
FROM hr.employees
WHERE LOWER(last_name) = 'higgins';
Resultado de la consulta SQL
El primer ejemplo muestra el n煤mero de empleado, nombre y n煤mero de departamento del empleado Higgins:
La cl谩usula WHERE de la primera sentencia SQL especifica el nombre del empleado como higgins. Debido a que todos los datos de la tabla HR.EMPLOYEES est谩n almacenados correctamente, el nombre higgins no encuentra ninguna coincidencia en la tabla y no se selecciona ninguna fila.
La cl谩usula WHERE de la segunda sentencia SQL especifica que el nombre del empleado de la tabla HR.EMPLOYEES se compara con higgins, convirtiendo la columna LAST_NAME a min煤sculas para poder compararla. Ya que ambos nombres no est谩n en min煤sculas, se ha encontrado una coincidencia y se ha seleccionado una fila. La cl谩usula WHERE se puede volver a escribir de la siguiente forma para que produzca el mismo resultado:
...WHERE last_name = 'Higgins'
El nombre de la salida aparece tal y como se almacen贸 en la base de datos. Para mostrar el nombre en may煤sculas, utilice la funci贸n UPPER de la sentencia SELECT.
SELECT employee_id, UPPER(last_name), department_id
FROM employees
WHERE INITCAP(last_name) = 'Higgins';
Resultado de la consulta SQL
CONCAT, SUBSTR, LENGTH, INSTR, LPAD, RPAD y TRIM son funciones de manipulaci贸n de caracteres que se tratan en esta lecci贸n.
CONCAT: une los valores. (S贸lo se pueden utilizar dos par谩metros con CONCAT).
SUBSTR: extrae una cadena de una longitud determinada
LENGTH: muestra la longitud de una cadena como un valor num茅rico
INSTR: obtiene la posici贸n num茅rica de un car谩cter denominado
LPAD: Devuelve una expresi贸n con relleno a la izquierda de caracteres n con una expresi贸n de caracteres
RPAD: devuelve una expresi贸n con relleno a la derecha de caracteres n con una expresi贸n de caracteres
TRIM: recorta los caracteres finales o de encabezado (o ambos) de una cadena de caracteres (si trim_character o trim_source es un car谩cter literal, debe incluirlo entre comillas simples).
Nota: puede utilizar funciones como UPPER y LOWER con una sustituci贸n con ampersand. Por ejemplo, utilice UPPER('&job_title') para que el usuario no tenga que introducir el puesto en un formato espec铆fico.
Uso de las Funciones de Manipulaci贸n de Caracteres
El ejemplo muestra los nombres y apellidos de los empleados que se han unido, la longitud del apellido del empleado y la posici贸n num茅rica de la letra “a” en el apellido del empleado de todos los empleados que tienen la cadena REP incluida en el ID de trabajo que empieza en la cuarta posici贸n de dicho ID.
Ejemplo:
Modifique la sentencia SQL para mostrar los datos de los empleados cuyos apellidos acaben con la letra “n”.
SELECT employee_id, CONCAT(first_name, last_name) NAME,
LENGTH (last_name), INSTR(last_name, 'a') "Contains 'a'?",last_name
FROM hr.employees
WHERE SUBSTR(last_name, -1, 1) = 'n';
Resultado de la consulta SQL.
Funciones de n煤mero
Funciones Num茅ricas
- ROUND: redondea el valor a un decimal especificado
- TRUNC: trunca el valor a un decimal especificado
- MOD: devuelve el resto de la divisi贸n
Las funciones num茅ricas aceptan entradas num茅ricas y devuelven valores num茅ricos. Esta secci贸n describe algunas de las funciones num茅ricas.
Nota: esta lista contiene s贸lo algunas de las funciones num茅ricas disponibles.
Para obtener m谩s informaci贸n, consulte la secci贸n sobre funciones num茅ricas en la gu铆a Oracle Database SQL Language Reference (Referencia sobre Lenguaje SQL de Oracle Database).
Uso de la Funci贸n ROUND
DUAL es una tabla p煤blica que puede utilizar para ver los resultados de funciones y c谩lculos.
La funci贸n ROUND redondea la columna, expresi贸n o valor a n decimales. Si falta el segundo argumento o tiene un valor de 0, el valor se redondea a cero decimales. Si el segundo argumento tiene un valor de 2, el valor se redondea a dos decimales. Por el contrario, si el segundo argumento es –2, el valor se redondea a dos decimales a la izquierda (redondeados a la unidad m谩s cercana a 100).
La funci贸n ROUND tambi茅n se puede utilizar con las funciones de fecha. Ver谩 varios ejemplos m谩s adelante en esta lecci贸n.
Tabla DUAL
El usuario SYS es el propietario de la tabla DUAL, a la que pueden acceder todos los usuarios. Contiene una columna, DUMMY, y una fila con el valor X. La tabla DUAL es 煤til cuando s贸lo desea devolver un valor una vez (por ejemplo, el valor de una constante, pseudocolumna o expresi贸n que no se deriva de una tabla con datos de usuario). La tabla DUAL se utiliza generalmente para obtener una visi贸n m谩s completa de la sintaxis de la cl谩usula SELECT, porque las cl谩usulas SELECT y FROM son obligatorias y muchos c谩lculos no tienen que realizar selecciones en las tablas reales.
Uso de la Funci贸n TRUNC
La funci贸n TRUNC trunca la columna, expresi贸n o valor a n decimales.
La funci贸n TRUNC funciona con argumentos similares a los de la funci贸n ROUND. Si falta el segundo argumento o tiene un valor de 0, el valor se trunca a cero decimales. Si el segundo argumento tiene un valor de 2, el valor se trunca a dos decimales. Por el contrario, si el segundo argumento tiene un valor de –2, el valor se trunca a dos decimales a la izquierda. Si el segundo argumento tiene un valor de –1, el valor se trunca a un decimal a la izquierda.
Al igual que la funci贸n ROUND, la funci贸n TRUNC se puede utilizar con funciones de fecha.
Uso de la Funci贸n MOD
Para todos los empleados con un puesto de vendedor, calcular el resto del salario despu茅s de dividirlo entre 5.000.
La funci贸n MOD obtiene el resto del primer argumento dividido entre el segundo argumento. El ejemplo calcula el resto del salario despu茅s de dividirlo entre 5.000 de todos los empleados cuyo ID de trabajo sea SA_REP.
Nota: la funci贸n MOD se suele utilizar para determinar si un valor es par o impar. La funci贸n MOD es tambi茅n la funci贸n hash de Oracle.
Trabajo con fechas
Oracle Database almacena fechas en un formato num茅rico interno: siglo, a帽o, mes, d铆a, horas, minutos y segundos.
El formato de visualizaci贸n de la fecha por defecto es DD-MON-RR.
Permite almacenar fechas del siglo 21 en el siglo 20 especificando s贸lo los dos 煤ltimos d铆gitos del a帽o
De la misma forma, permite almacenar fechas del siglo 20 en el siglo 21.
Oracle Database almacena fechas en un formato num茅rico interno que representa el siglo, el a帽o, el mes, las horas, los minutos y los segundos.
La visualizaci贸n por defecto y el formato de entrada de cualquier fecha es DD-MON-RR. Las fechas de Oracle v谩lidas son del 1 de enero de 4712 A.C. y el 31 de diciembre de 9999 D.C.
En el ejemplo , la salida de la columna HIRE_DATE aparece en el formato por defecto DD-MON-RR. Sin embargo, las fechas no se almacenan en la base de datos en este formato. Se almacenan todos los componentes de la fecha y la hora. Por lo tanto, aunque un valor HIRE_DATE de 17-JUN-87 aparezca como el d铆a, mes y a帽o, tambi茅n existe informaci贸n de hora y siglo asociada a la fecha. Los datos completos podr铆an ser 17 de junio de 1987, 5:10:43 PM.
Formato de Fecha RR
El formato de fecha RR es similar al elemento YY, pero puede utilizarlo para especificar siglos diferentes. Utilice el elemento de formato de fecha RR en lugar de YY para que el siglo del valor de retorno var铆e seg煤n el a帽o de dos d铆gitos especificado y los 煤ltimos dos d铆gitos del a帽o actual. La tabla resume el comportamiento del elemento RR.
Observe que los valores mostrados en las dos 煤ltimas filas de la tabla anterior. Conforme nos acercamos a la mitad del siglo, el comportamiento de RR puede que no sea lo que desea.
Estos datos se almacenan internamente de la siguiente forma:
CENTURY YEAR MONTH DAY HOUR MINUTE SECOND
19 87 06 17 17 10 43
Siglos y el A帽o 2000
Cuando se inserta un registro con una columna de fecha en una tabla, la informaci贸n de siglo se selecciona de la funci贸n SYSDATE. Sin embargo, cuando la columna de fecha se muestra en la pantalla, el componente de siglo no aparece (por defecto).
El tipo de dato DATE utiliza 2 bytes para la informaci贸n de a帽o, uno para el siglo y otro para el a帽o. El valor de siglo siempre se incluye, independientemente de si se especifica o se muestra. En este caso, RR determina el valor por defecto para el siglo en INSERT.
Uso de la Funci贸n SYSDATE
SYSDATE es una funci贸n que devuelve:
- Fecha
- Hora
SYSDATE es una funci贸n de fecha que devuelve la fecha y hora actuales del servidor de base de datos. Puede utilizar SYSDATE como si utilizara cualquier otro nombre de columna. Por ejemplo, puede mostrar la fecha actual seleccionando SYSDATE de una tabla. Es muy com煤n seleccionar SYSDATE de una tabla ficticia denominada DUAL.
Nota: SYSDATE devuelve la fecha y hora actuales definidas para el sistema operativo en el que reside la base de datos. Por lo tanto, si est谩 en alg煤n lugar de Australia y se conecta a una base de datos remota en una ubicaci贸n de Estados Unidos (EE. UU.), la funci贸n sysdate devolver谩 la fecha y hora de EE. UU. En ese caso, puede utilizar la funci贸n CURRENT_DATE que devuelve la fecha actual en la zona horaria de la sesi贸n.
La funci贸n CURRENT_DATE y otras funciones de zona horaria relacionadas se abordan con mayor detalles en Oracle Database: Conceptos Fundamentales de SQL II.
Operadores Aritm茅ticos con Fechas
Sumar o restar un n煤mero de una fecha para obtener un valor de fecha resultante.
Restar dos fechas para obtener el n煤mero de d铆as entre esas fechas.
Agregar horas a una fecha dividendo entre el n煤mero de horas entre 24.
Debido a que la base de datos almacena fechas como n煤meros, puede realizar c谩lculos utilizando operadores aritm茅ticos como la suma y la resta. Puede agregar y restar constantes num茅ricas y fechas.
Puede realizar las siguientes operaciones:
El ejemplo muestra el apellido y el n煤mero de semanas durante las que han trabajado todos los empleados del departamento 90. Resta la fecha de contrataci贸n del empleado de la fecha actual (SYSDATE) y divide el resultado entre 7 para calcular el n煤mero de semanas durante las que ha trabajado el empleado.
Nota: SYSDATE es una funci贸n SQL que devuelve la fecha y hora actuales. Los resultados pueden ser diferentes seg煤n la fecha y hora definidas para el sistema operativo de la base de datos local al ejecutar la consulta SQL.
Si se resta una fecha m谩s actual de una fecha m谩s antigua, la diferencia es un n煤mero negativo.
Funciones de fecha
Funciones de Manipulaci贸n de Fecha
Las funciones de fecha funcionan en fechas de Oracle. Todas las funciones de fecha devuelven un valor del tipo de dato DATE excepto MONTHS_BETWEEN, que devuelve un valor num茅rico.
MONTHS_BETWEEN(date1, date2): obtiene el n煤mero de meses entre date1 y date2. El resultado puede ser positivo o negativo. Si date1 es posterior a date2, el resultado es positivo; si date1 es anterior a date2, el resultado es negativo. La parte del resultado que no sea un entero representa una parte del mes.
ADD_MONTHS(date, n): agrega el n煤mero n de los meses de calendario a date. El valor de n debe ser un entero y puede ser negativo.
NEXT_DAY(date, 'char'): obtiene la fecha del siguiente d铆a de la semana especificado ('char') que le sigue a date. El valor de char puede ser un n煤mero que representa un d铆a o una cadena de caracteres.
LAST_DAY(date): obtiene la fecha del 煤ltimo d铆a del mes que contiene date.
Esta lista es un subjuego de las funciones de fecha disponibles. Las funciones de n煤mero ROUND y TRUNC tambi茅n se pueden utilizar para manipular los valores de fecha como se muestra a continuaci贸n:
ROUND(date[,'fmt']): devuelve date redondeado a la unidad especificada por el modelo de formato fmt. Si se omite el modelo de formato fmt , date se redondea a la fecha m谩s cercana.
TRUNC(date[, 'fmt']): devuelve date con la parte de la hora del d铆a truncada a la unidad especificada por el modelo de formato fmt. Si se omite el modelo de formato fmt,date se redondea a la fecha m谩s cercana.
Los siguientes modelos de formato se tratan en las lecciones tituladas “Uso de Funciones de Conversi贸n y Expresiones Condicionales”:
Uso de las Funciones de Fecha
En el ejemplo, la funci贸n ADD_MONTHS agrega un mes al valor de fecha proporcionado “31-JAN-96” y devuelve “29-FEB-96”. La funci贸n reorganiza el a帽o 1996 como a帽o bisiesto y, por lo tanto, devuelve la 煤ltima fecha del mes de febrero. Si cambia el valor de fecha de entrada a “31-JAN-95”, la funci贸n devuelve “28-FEB-95”.
Por ejemplo, muestre el n煤mero de empleado, fecha de contrataci贸n, n煤mero de meses durante los que ha trabajado, fecha de revisi贸n de seis meses, primer viernes despu茅s de la fecha de contrataci贸n y 煤ltimo d铆a del mes de contrataci贸n de todos los empleados que han trabajado menos de 150 meses.
SELECT employee_id, hire_date, MONTHS_BETWEEN (SYSDATE, hire_date) TENURE,
ADD_MONTHS (hire_date, 6) REVIEW, NEXT_DAY (hire_date, 'FRIDAY'),
LAST_DAY(hire_date)
FROM hr.employees
WHERE MONTHS_BETWEEN (SYSDATE, hire_date) < 150;
Uso de las Funciones ROUND y TRUNC con Fechas
Supongamos SYSDATE = '25-JUL-03':
Las funciones ROUND y TRUNC se pueden utilizar para valores de fecha y n煤mero. Al utilizarlas con fechas, estas funciones redondean o truncan el modelo de formato especificado. Por lo tanto, puede redondear las fechas al a帽o o mes m谩s cercano. Si el modelo de formato es mes, el resultado de las fechas 1-15 es el primer d铆a del mes actual. El resultado de las fechas 16-31 es el primer d铆a del siguiente mes. Si el modelo de formato es mes, el resultado de las fechas 1-6 es el 1 de enero del a帽o actual. El resultado de los meses 7-12 es el 1 de enero del siguiente a帽o.
Ejemplo:
Compare las fechas de contrataci贸n de todos los empleados que empezaron en 1997. Muestre el n煤mero de empleado, fecha de contrataci贸n y mes de inicio con las funciones ROUND y TRUNC.
SELECT employee_id, hire_date,
ROUND(hire_date, 'MONTH'), TRUNC(hire_date, 'MONTH')
FROM hr.employees
WHERE hire_date LIKE '%97';
Resumen
Las funciones de una sola fila se pueden anidar en cualquier nivel. Las funciones de una sola fila pueden manipular los siguientes elementos:
Datos de caracteres: LOWER, UPPER, INITCAP, CONCAT, SUBSTR, INSTR, LENGTH
Datos de n煤mero: ROUND, TRUNC, MOD
Valores de fecha: SYSDATE, MONTHS_BETWEEN, ADD_MONTHS, NEXT_DAY, LAST_DAY
Recuerde lo siguiente:
Los valores de fecha tambi茅n pueden utilizar operadores aritm茅ticos.
Las funciones ROUND y TRUNC tambi茅n se pueden utilizar con valores de fecha.
SYSDATE y DUAL
SYSDATE es una funci贸n de fecha que devuelve la fecha y hora actuales. Es muy com煤n seleccionar SYSDATE de una tabla p煤blica denominada DUAL.
