funciones de sql server
TRANSCRIPT
Funciones de SQL server
¿Qué es SQL? Funciones BásicasSQL es un lenguaje universal dedicado a las bases de datos. Es un lenguaje de sintaxis simple y muy potente. Mediante él se puede recorrer, modificar o borrar registros de las tablas de datos.
Propiedad SELECT : Hace la selección en una tabla de la BD.SELECT * FROM datos'Esta sentencia seleccionaría todos, absolutamente todos los registros dentro de la tabla datos.SELECT * FROM datos WHERE usuario='Juan'Seleccionaríamos todos los registros dentro de la tabla datos que tengan como usuario a "Juan".Propiedad INSERT INTO : Agrega un nuevo registro a la tabla elegidaINSERT INTO datos (usuario) VALUES ('corsa') Insertamos en la tabla datos en la columna usuario, un registro nuevo.INSERT INTO datos (usuario, edad) VALUES ('corsa',20) Aquí insertamos 2 registros al mismo tiempo.Propiedad DELETE : Borra registros de nuestra tablaDELETE FROM datos WHERE usuario = 'Pedrito''Borramos los registros donde el usuario sea "Pedrito".DELETE FROM datos WHERE usuario = 'Mario' AND edad = 16 Borramos solo los usuario de nombre Mario que tenían 16 años.Propiedad UPDATE : Actualiza registros, modificando datos ya existentes.UPDATE datos SET usuario = 'Pedrito' Esta modificación renombrará todos los usuarios a "Pedrito".
Tipos de funciones Funciones de conjuntos de filas: Devuelven un objeto que se puede usar como las referencias a tablas en una instrucción SQL.Funciones de agregado: Operan sobre una colección de valores y devuelven un solo valor de resumen.Funciones de categoría: Devuelven un valor de categoría para cada fila de una partición.Funciones escalares: Operan sobre un valor y después devuelven otro valor. Las funciones escalares se pueden usar donde la expresión sea válida.
Funciones de cadenaLas funciones de cadena trabajan con campos char y varchar por lo que los literales que escribamos se deben encerrar entre comillas simples. Estas funciones que vamos a explicar a continuación pueden manipular cadenas de letras u otros caracteres por lo que las vamos a dividir en dos grupos:
Funciones que devuelven caracteresEste tipo de funciones devuelven un carácter o varios caracteres.Función PropósitoCHR(n) Nos devuelve el carácter cuyo valor en binario es nCONCAT Nos devuelve cad1 concatenada con cad2UPPER Convierte cad a mayúsculasLOWER Convierte cad a minúsculasLPAD Con esta función añadimos caracteres a cad1 por la izquierda hasta una
longitud máxima dada por nINITCAP Convierte la primera letra de cad a mayúsculaLTRIM Elimina un conjunto de caracteres a la izquierda de cad, siendo set el
conjunto de caracteres a eliminarRPAD Con esta función añadimos caracteres de la misma forma que con la función
LPAD pero esta vez los añadimos a la derechaRTRIM Hace lo mismo que LTRIM pero por la derecha
REPLACE Sustituye un conjunto de caracteres de 0 o más caracteres, devuelve cad con cada ocurrencia de cadena_buscada sustituida por cadena_sustitucion
SUBSTR Devuelve la subcadena de cad que abarca desde m hasta el numero de caracteres dados por n.
TRANSLATE Convierte caracteres de una cadena en caracteres diferentes. Devuelve cad1 con los caracteres encontrados en cad2 y sustituidos por los caracteres de cad3
Ponemos algunos ejemplos de utilización de estas funciones: Sentencia sql que nos devuelve las letras cuyo valor asccii es el 45 y el 23 select CHR(45), CHR(23) FROM TABLA; Sentencia sql que obtiene el nombre de los alumnos sacando por pantalla la siguiente frase: el nombre del alumno es (nombre que está almacenado en la tabla) select CONCAT ('el nombre de alumno es', nombre) from alumno;
Sentencia sql que me devuelve los nombres de los alumnos en mayúsculas select UPPER(nombre) from alumno; Sentencia sql que obtiene de un campo nombre, las 3 primeras letras select SUBSTR(nombre,0,3) from alumno; Y asi con el resto de funciones…
Funciones que devuelven valores numéricos Estas funciones nos devuelven números a modo de información.
Función PropósitoASCII(cad) Devuelve el valor ASCII de la primera letra de cadINSTR(cad1, cad2[,comienzo[,m]])
Función que busca un conjunto de caracteres dentro de una cadena. Nos devuelve la posición de cad2 en cad1 empezando a buscar en comienzo
LENGTH(cad) Devuelve en número de caracteres de cad
Como con las funciones anteriores dejamos unos ejemplos para que veáis su funcionamiento. Sentencia sql que nos devuelve el valor ASCII de la letra ('s') select ASCII('s') from tabla; Sentencia que nos devuelve la posición de la ocurrencia 'pe' dentro de la cadena 'Los perros están bien' a partir de la posición 2 select INSTR('Los perros están bien','pe',2) from tabla; Sentencia sql que nos devuelve el numero de caracteres de los nombres de los alumnos select LENGTH(nombre) from alumnos;
Funciones en línea Las funciones en línea son las funciones que devuelven un conjunto de resultados correspondientes a la ecuación de una sentencia SELECT. El siguiente ejemplo muestra cómo crear una función en línea.
CREATE FUNCTION fn_MovimientosCuenta(@NumCuenta VARCHAR(20))RETURNS TABLEASRETURN (SELECT MOVIMIENTOS.* FROM MOVIMIENTOSINNER JOIN CUENTAS ON MOVIMIENTOS.IDCUENTA = CUENTAS.IDCUENTAWHERE CUENTAS.NUMCUENTA = @NumCuenta)
No podemos utilizar la clausula ORDER BY en la sentencia de una función el línea. Las funciones en línea pueden utilizarse dentro de joins o querys como si fueran una tabla normal.
SELECT * FROM fn_MovimientosCuenta('200700000001') SELECT *FROM CUENTASINNER JOIN CUENTAS_CLIENTE
ON CUENTAS_CLIENTE.IDCUENTA = CUENTAS.IDCUENTAINNER JOIN CLIENTES
ON CLIENTES.id = CUENTAS_CLIENTE.IDCLIENTE INNER JOIN fn_MovimientosCuenta('200700000001') A
ON A.IDCUENTA= CUENTAS.IDCUENTA
Tipos de datos de fecha y hora
En la tabla siguiente se enumeran los tipos de datos de fecha y hora de Transact-SQL.
Tipo de datos
Formato Intervalo Precisión
Tamaño de
almacenamiento (bytes)
Precisión
Desplazamien
to
time hh:mm:ss[.nn
nnnnn]
De 00:00:00.0000000 a
23:59:59.9999999
100
nanosegun
do
De 3 a 5 Sí No
date AAAA-MM-DD De 0001-01-01 a 9999-12-31 1 día 3 No No
smalldat
etime
AAAA-MM-DD
hh:mm:ss
De 1900-01-01 a 2079-06-06 1 minuto 4 No No
date
time
AAAA-MM-DD
hh:mm:ss[.nn
n]
De 1753-01-01 a 9999-12-31 0,00333
segundos
8 No No
datetime
2
AAAA-MM-DD
hh:mm:ss
De 0001-01-01
00:00:00.0000000 a 9999-12-31
23:59:59.9999999
100
nanosegun
do
De 6 a 8 Sí No
datetime
offset
AAAA-MM-DD
hh:mm:ss[.nn
nnnnn]
[+|]hh:mm
De 0001-01-01
00:00:00.0000000 a 9999-
12-31 23:59:59.9999999 (en
UTC)
100
nanosegun
do
De 8 a
10
Sí Sí
NOTAEl tipo de datos rowversion de Transact-SQL no es un tipo de datos de fecha y hora. timestamp es un sinónimo obsoleto de rowversión.
Funciones de fecha y hora En las tablas siguientes se enumeran las funciones de fecha y hora de Transact-SQL. Para obtener más información acerca del determinismo, vea Funciones deterministas y no deterministas.
Funciones que obtienen valores de fecha y hora del sistemaTodos los valores de fecha y hora del sistema se derivan del sistema operativo del equipo en el que se ejecuta la instancia de SQL Server.
Funciones de fecha y hora del sistema de precisión elevada
SQL Server 2008 R2 obtiene los valores de fecha y hora utilizando la API de Windows GetSystemTimeAsFileTime(). La exactitud depende del hardware del equipo y de la versión de Windows en las que la instancia de SQL Server se esté ejecutando. La precisión de esta API
se fija en 100 nanosegundos. La precisión se puede determinar mediante la API de Windows GetSystemTimeAdjustment().
Función Sintaxis Valor devuelto Tipo de datos
Determinismo
SYSDATETIME SYSDATETIME () Devuelve un valor datetime2(7) que contiene la
fecha y hora del equipo en el que la instancia de
SQL Server se está ejecutando. El ajuste de zona
horaria no está incluido.
datetime2 No
determinista
SYSDATETIME
OFFSET
SYSDATETIMEOFFSE
T ( )
Devuelve un valor datetimeoffset(7) que contiene
la fecha y hora del equipo en el que la instancia
de SQL Server se está ejecutando. El ajuste de
zona horaria está incluido.
datetimeoffse
t
No
determinista
SYSUTCDATETI
ME
SYSUTCDATETIME ( ) Devuelve un valor datetime2(7) que contiene la
fecha y hora del equipo en el que la instancia de
SQL Server se está ejecutando. La fecha y hora se
devuelven como hora universal coordinada (UTC).
datetime2 No
determinista
Funciones de fecha y hora del sistema de precisión bajaFunción Sintaxis Valor devuelto Tipo de
datos devuelto
Determinismo
CURRENT_TIMESTAMP CURRENT_TIMESTAMP Devuelve un valor que contiene la fecha y
hora del equipo en el que la instancia de SQL
Server se está ejecutando.
datetime No determinista
GETDATE GETDATE ( ) Devuelve un valor que contiene la fecha y
hora del equipo. El ajuste de zona horaria no
está incluido.
datetime No determinista
GETUTCDATE GETUTCDATE ( ) Devuelve un valor que contiene la fecha y
hora del equipo. La fecha y hora se devuelven
como una hora universal coordinada (UTC).
datetime No determinista
Funciones que obtienen partes de la fecha y hora
Función Sintaxis Valor devuelto Tipo Determinismo
DATENAME DATENAME
(datepart
date)
Devuelve una cadena de caracteres que representa el
datepart especificado de la fecha especificada.
nvarch
ar
No determinista
DATEPART DATEPART Devuelve un entero que representa el datepart
especificado
int No determinista
DAY DAY ( date ) Devuelve un entero que representa la parte del día de
date especificado.
int Determinista
MONTH MONTH (
date )
Devuelve un entero que representa la parte del mes de
un date especificado.
int Determinista
YEAR YEAR ( date ) Devuelve un entero que representa la parte del año de un
date especificado.
int Determinista
Funciones escalares
Categoría de la funciónFunciones de configuración: Devuelven información acerca de la configuración actual.Funciones criptográficas: Admiten cifrado, descifrado, firma digital y la validación de firmas digitales.Funciones del cursor: Devuelven información acerca de los cursores.Funciones de tipo de datos : Devuelven información sobre los valores de identidad y los valores de otros tipos de datos.Tipos de datos y funciones de fecha y hora: Llevan a cabo operaciones sobre un valor de entrada de fecha y hora, y devuelven un valor numérico, de cadena o de fecha y hora.Funciones matemáticas: Realizan cálculos basados en valores de entrada proporcionados como parámetros a las funciones y devuelven valores numéricos. Funciones de metadatos: Devuelven información acerca de la base de datos y los objetos de la base de datos.Funciones escalares ODBC: Devuelven información sobre funciones ODBC escalares en una instrucción Transact-SQL.Funciones de replicación: Devuelven información que se usa para administrar, supervisar y mantener la topología de replicaciónFunciones de seguridad: Devuelven información acerca de usuarios y roles.Funciones de cadena: Realizan operaciones en el valor de entrada de una cadena (char o varchar) y devuelven una cadena o un valor numérico.Funciones del sistema: Realizan operaciones y devuelven información acerca de valores, objetos y configuraciones de una instancia de SQL Server.Funciones estadísticas del sistema: Devuelven información estadística acerca del sistema.
Funciones de texto e imagen: Realizan operaciones sobre los valores de entrada o columnas de texto o imagen, y devuelven información acerca del valor.
Funciones de desencadenador: Devuelven información acerca de los desencadenadores.
Determinismo de función Las funciones integradas de SQL Server son deterministas o no deterministas. Las funciones son deterministas cuando devuelven siempre el mismo resultado cada vez que se llaman con un conjunto específico de valores de entrada. Las funciones son no deterministas cuando es posible que devuelvan distintos resultados cada vez que se llaman con un mismo conjunto específico de valores de entrada. Para obtener más información, vea Funciones deterministas y no deterministas
Intercalación de funciones Las funciones que toman una entrada de cadena de caracteres y devuelven una salida de cadena de caracteres utilizan la intercalación de la cadena de entrada para la salida.Las funciones que toman entradas que no son de caracteres y devuelven una cadena de caracteres utilizan la intercalación predeterminada de la base de datos actual para la salida.Las funciones que toman varias entradas de cadena de caracteres y devuelven una cadena de caracteres utilizan las reglas de prioridad de intercalación para establecer la intercalación de la cadena de salida. Para obtener más información, vea Prioridad de intercalación (Transact-SQL).
Funciones escalares Las funciones escalares definidas por el usuario devuelven un único valor de datos del tipo definido en la cláusula RETURNS. Las funciones escalares en línea no tienen cuerpo; el valor escalar es el resultado de una sola instrucción. Para una función escalar de múltiples instrucciones, el cuerpo de la función, definido en un bloque BEGIN...END, contiene una serie de instrucciones Transact-SQL que devuelven el valor único. El tipo devuelto puede ser de cualquier tipo de datos excepto text, ntext, image, cursor y timestamp.En el ejemplo siguiente se crea una función escalar con múltiples instrucciones. La función toma un valor de entrada, ProductID, y devuelve un solo valor de datos, la cantidad agregada del producto especificado en el inventario.
otherCopiar USE AdventureWorks;GOIF OBJECT_ID (N'dbo.ufnGetInventoryStock', N'FN') IS NOT NULL DROP FUNCTION ufnGetInventoryStock;GOCREATE FUNCTION dbo.ufnGetInventoryStock(@ProductID int)RETURNS int AS -- Returns the stock level for the product.BEGIN DECLARE @ret int; SELECT @ret = SUM(p.Quantity) FROM Production.ProductInventory p WHERE p.ProductID = @ProductID AND p.LocationID = '6'; IF (@ret IS NULL) SET @ret = 0; RETURN @ret;END;GO
Funciones integradas SQL Server proporciona las funciones integradas para ayudarle a realizar diversas operaciones. No se pueden modificar. Puede utilizar funciones integradas en instrucciones Transact-SQL para:
Tener acceso a información de las tablas del sistema de SQL Server sin tener acceso a las tablas del sistema directamente. Para obtener más información, vea Usar las funciones del sistema.
Realizar tareas habituales como SUM, GETDATE o IDENTITY. Para obtener más información, vea Funciones (Transact-SQL).
Las funciones integradas devuelven tipos de datos escalares o table. Por ejemplo, @@ERROR devuelve 0 si la última instrucción Transact-SQL se ejecutó correctamente. Si la instrucción generó un error, @@ERROR devuelve el número de error. Y la función SUM(parameter) devuelve la suma de todos los valores del parámetro.
Consultas agregadasLa clausula GROUP BY combina los registros con valores idénticos en un único registro. Para cada registro se puede crear un valor agregado si se incluye una función SQL agregada, como por ejemplo Sum o Count, en la instrucción SELECT. Su sintaxis es:
SELECT [ALL | DISTINCT ] <nombre_campo> [{,<nombre_campo>}] [{,<funcion_agregado>}]FROM <nombre_tabla>|<nombre_vista> [{,<nombre_tabla>|<nombre_vista>}][WHERE <condicion> [{ AND|OR <condicion>}]][GROUP BY <nombre_campo> [{,<nombre_campo >}]][HAVING <condicion>[{ AND|OR <condicion>}]][ORDER BY <nombre_campo>|<indice_campo> [ASC | DESC] [{,<nombre_campo>|<indice_campo> [ASC | DESC ]}]]
GROUP BY es opcional. Si se utiliza GROUP BY pero no existe una función SQL agregada en la instrucción SELECT se obtiene el mismo resultado que con una consulta SELECT DISTINCT. Los valores Null en los campos GROUP BY se agrupan y no se omiten. No obstante, los valores Null no se evalúan en ninguna de las funciones SQL agregadas.
Todos los campos de la lista de campos de SELECT deben incluirse en la cláusula GROUP BY o como argumentos de una función SQL agregada.
SELECT marca, modelo, SUM(numero_kilometros)FROM tCochesGROUP BY marca, modelo
La cláusula HAVING Una vez que GROUP BY ha combinado los registros, HAVING muestra cualquier registro agrupado por la cláusula GROUP BY que satisfaga las condiciones de la cláusula HAVING. Se utiliza la cláusula WHERE para excluir aquellas filas que no desea agrupar, y la cláusula HAVING para filtrar los registros una vez agrupados. HAVING es similar a WHERE, determina qué registros se seleccionan. Una vez que los registros se han agrupado utilizando GROUP BY, HAVING determina cuáles de ellos se van a mostrar. HAVING permite el uso de funciones agregadas.
SELECT marca, modelo, SUM(numero_kilometros)FROM tCochesWHERE marca <> 'BMW'GROUP BY marca, modeloHAVING SUM(numero_kilometros)>100000
En el ejemplo anterior, no se cuentan los datos para todas las marcas menos "BMW", una vez que se han contado, se evalua HAVING, y el conjunto de resultados devuelve solo aquellos modelos con más de 100.000 km.AVG.- Calcula la media aritmética de un conjunto de valores contenidos en un campo especificado de una consulta. Su sintaxis es la siguiente En donde expr representa el campo que contiene los datos numéricos para los que se desea calcular la media o una expresión que realiza un cálculo utilizando los datos de dicho campo. La media calculada por Avg es la media aritmética (la suma de los valores dividido por el número de valores). La función Avg no incluye ningún campo Null en el cálculo.
SELECT marca, modelo, AVG(numero_kilometros)FROM tCochesGROUP BY marca, modelo
Count.- Calcula el número de registros devueltos por una consulta. Su sintaxis es la siguiente: En donde expr contiene el nombre del campo que desea contar. La función Count no cuenta los registros que tienen campos null a menos que expr sea el carácter comodín asterisco (*). Si utiliza un asterisco, Count calcula el número total de registros, incluyendo aquellos que contienen campos null. Count(*) es considerablemente más rápida que Count(Campo).
SELECT COUNT(*) FROM tCoches;SELECT marca, COUNT(modelo)FROM tCoches GROUP BY marca;SELECT marca, COUNT(DISTINCT modelo)FROM tCochesGROUP BY marca;
Max, Min Devuelven el mínimo o el máximo de un conjunto de valores contenidos en un campo especifico de una consulta. Su sintaxis es: En donde expr es el campo sobre el que se desea realizar el cálculo. Expr pueden incluir el nombre de un campo de una tabla, una constante o una función (la cual puede ser intrínseca o definida por el usuario pero no otras de las funciones agregadas de SQL).
SELECT marca, modelo, MIN(numero_kilometros) , MAX(numero_kilometros)FROM tCochesGROUP BY marca, modelo
Sum.- Devuelve la suma del conjunto de valores contenido en un campo especifico de una consulta. Su sintaxis es: En donde expr respresenta el nombre del campo que contiene los datos que desean sumarse o una expresión que realiza un cálculo utilizando los datos de dichos campos. Los operandos de expr pueden incluir el nombre de un campo de una tabla, una constante o una función (la cual puede ser intrínseca o definida por el usuario pero no otras de las funciones agregadas de SQL).
SELECT marca, modelo, SUM(numero_kilometros)FROM tCochesGROUP BY marca, modelo
Funciones de valores simples:
ABS(n)= Devuelve el valor absoluto de (n). CEIL(n)=Obtiene el valor entero inmediatamente superior o igual a "n". FLOOT(n) = Devuelve el valor entero inmediatamente inferior o igual a "n". MOD (m, n)= Devuelve el resto resultante de dividir "m" entre "n". NVL (valor, expresión)= Sustituye un valor nulo por otro valor. POWER (m, exponente)= Calcula la potencia de un numero. ROUND (numero [, m])= Redondea números con el numero de dígitos de precisión indicados. SIGN (valor)= Indica el signo del "valor". SQRT(n)= Devuelve la raíz cuadrada de "n". TRUNC (numero, [m])= Trunca números para que tengan una cierta cantidad de dígitos de precisión. VAIRANCE (valor)= Devuelve la varianza de un conjunto de valores.
Funciones de grupos de valores: AVG(n)= Calcula el valor medio de "n" ignorando los valores nulos. COUNT (* | Expresión)= Cuenta el numero de veces que la expresión evalúa algún dato con valor no nulo. La opción "*" cuenta todas las filas seleccionadas. MAX (expresión)= Calcula el máximo. MIN (expresión)= Calcula el mínimo. SUM (expresión)= Obtiene la suma de los valores de la expresión. GREATEST (valor1, valor2…)= Obtiene el mayor valor de la lista. LEAST (valor1, valor2…)= Obtiene el menor valor de la lista. Funciones que devuelven valores de caracteres: CHR(n) = Devuelve el carácter cuyo valor en binario es equivalente a "n". CONCAT (cad1, cad2)= Devuelve "cad1" concatenada con "cad2". LOWER (cad)= Devuelve la cadena "cad" en minúsculas. UPPER (cad)= Devuelve la cadena "cad" en mayúsculas. INITCAP (cad)= Convierte la cadena "cad" a tipo titulo. LPAD (cad1, n[,cad2])= Añade caracteres a la izquierda de la cadena hasta que tiene una cierta longitud. RPAD (cad1, n[,cad2])= Añade caracteres a la derecha de la cadena hasta que tiene una cierta longitud. LTRIM (cad [,set])= Suprime un conjunto de caracteres a la izquierda de la cadena. RTRIM (cad [,set])= Suprime un conjunto de caracteres a la derecha de la cadena. REPLACE (cad, cadena_busqueda [, cadena_sustitucion])= Sustituye un carácter o caracteres de una cadena con 0 o mas caracteres. SUBSTR (cad, m [,n])= Obtiene parte de una cadena. TRANSLATE (cad1, cad2, cad3)= Convierte caracteres de una cadena en caracteres diferentes, según un plan de sustitución marcado por el usuario.
Funciones que devuelven valores numéricos: ASCII(cad)= Devuelve el valor ASCII de la primera letra de la cadena "cad". INSTR (cad1, cad2 [, comienzo [,m]])= Permite una búsqueda de un conjunto de caracteres en una cadena pero no suprime ningún carácter después. LENGTH (cad)= Devuelve el numero de caracteres de cad.
Funciones de conversión: TO_CHAR= Transforma un tipo DATE ó NUMBER en una cadena de caracteres. TO_DATE= Transforma un tipo NUMBER ó CHAR en DATE. TO_NUMBER= Transforma una cadena de caracteres en NUMBER.
Funciones en línea de múltiples sentencias
Las funciones en línea de múltiples sentencias son similares a las funciones en línea excepto que el conjunto de resultados que devuelven puede estar compuesto por la ejecución de varios consultas SELECT.El siguiente ejemplo muestra el uso de una función de tabla de multi sentencias.
/* Esta función busca la tres cuentas con mayor saldo * y obtiene los tres últimos movimientos de cada una * de estas cuentas */
CREATE FUNCTION fn_CuentaMovimietos() RETURNS @datos TABLE ( -- Estructura de la tabla que devuelve la funcion.NumCuenta varchar(20), Saldo decimal(10,2),Saldo_anterior decimal(10,2),Saldo_posterior decimal(10,2),Importe_Movimiento decimal(10,2), FxMovimiento datetime ) AS BEGIN-- Variables necesarias para la lógica de la funcion.DECLARE @idcuenta int,
@numcuenta varchar(20), @saldo decimal(10,2)
-- Cursor con las 3 cuentas de mayor saldoDECLARE CDATOS CURSOR FORSELECT TOP 3 IDCUENTA, NUMCUENTA, SALDOFROM CUENTASORDER BY SALDO DESC OPEN CDATOSFETCH CDATOS INTO @idcuenta, @numcuenta, @saldo -- Recorremos el cursorWHILE (@@FETCH_STATUS = 0)BEGIN-- Insertamos la cuenta en la variable de salidaINSERT INTO @datos(NumCuenta, Saldo)VALUES(@numcuenta, @saldo)-- Insertamos los tres últimos movimientos de la cuentaINSERT INTO @datos(Saldo_anterior, Saldo_posterior, Importe_Movimiento, FxMovimiento )SELECT TOP 3 SALDO_ANTERIOR, SALDO_POSTERIOR, IMPORTE, FXMOVIMIENTO FROM MOVIMIENTOSWHERE IDCUENTA = @idcuentaORDER BY FXMOVIMIENTO DESC-- Vamos a la siguiente cuentaFETCH CDATOS INTO @idcuenta, @numcuenta, @saldoEND CLOSE CDATOS;DEALLOCATE CDATOS; RETURN END
Para ejecutar la función:select * from fn_CuentaMovimietos()
Funciones de conversión caracteres LOWER: Convierte a minúsculas. UPPER: Convierte a mayúsculas.
INITCAP: Convierte la primera letra de cada palabra en mayúsculas, y el resto en minúscula.
Atención: Usar una función de conversión dentro de la cláusula WHERE puede ser altamente ineficiente porque si la columna afectada forma parte de un índice éste lo desactiva, provocando un bajo rendimiento.
Funciones manipulación caracteres CONCAT: Concatena dos valores. SUBSTR: Extrae una subcadena. LENGTH: Devuelve la longitud de la cadena. INSTR: Devuelve la posición de un carácter o subcadena. LPAD: Justifica a la derecha la cadena. RPAD: Justifica a la izquierda la cadena.
Funciones Numéricas ROUND (columna | expresión, n)
Redondea a n posiciones decimales. Si se omite n, no se redondea con decimales. Si n es negativo, los números a la izquierda del punto decimal se redondean a decenas, centenas, ...
TRUNC (columna | expresión, n) Trunca en la enésima posición decimal. Si se omite n, sin lugares decimales. Si n es negativa, los números a la izquierda del punto decimal se truncan a cero.
MOD (m, n) Devuelve el resto de la división de m por n.
Ejemplos de funciones numéricasSQL> SELECT ROUND (45.923, 2), ROUND (45.923, 0), ROUND (45.923, -1)FROM SYS.DUAL; Resultado: 45.92 46 50 SQL> SELECT TRUNC (45.923, 2), TRUNC (45,923), TRUNC (45.923, -1)FROM SYS.DUAL; Resultado: 45.92 45 40
Funciones de conversión Conversión Implícita de datos
De VARCHAR2 o CHAR a NUMBER De VARCHAR2 o CHAR a DATE De NUMBER a VARCHAR2 De DATE a VARCHAR2
Funciones de conversión TO_CHAR (número | fecha [,’fmt’])
Convierte un número o fecha en una cadena de caracteres VARCHAR2 con el modelo de formato fmt.
9: Representa un número 0: Fuerza a que se muestra el cero $: Signo de dólar L: Usa el signo de moneda local .: Imprime el punto decimal ;: Imprime el indicador de millar Para fechas, los fmt anteriores.
Funciones de conversión TO_NUMBER (char)
Convierte una cadena de caracteres con dígitos en un número. TO_DATE (char [,’fmt’])
Convierte una cadena de caracteres representando una fecha en un valor de fecha según el fmt especificado. Si se omite el fmt, el formato es DD-MON-YY.
NVL (expr1, expr2) Convierte un nulo (expr1) a un valor de tipo fecha, cadena o número (expr2).
La Función DECODE
Hace las veces de sentencia CASE o IF-THEN-ELSE, para facilitar consultas condicionales. Descifra una expresión después de compararla con cada valor de búsqueda. Si la expresión es la misma que la búsqueda, se devuelve el resultado. Si se omite el valor por defecto, se devolverá un valor nulo donde una búsqueda no coincida con ninguno de los valores resultantes.