 |
|
|
Funciones en Transact SQL
SQL Server proporciona al usuario la posibilidad de definir sus propias funciones, conocidad como UDF (user defined functions). Exisiten tres tipos de funciones. Estas son:
Funciones escalares
Las funciones escalares devuelven un único valor de cualquier tipo de los datos tal como int, money, varchar, real, etc.
La sintaxis para una función escalar es la siguiente:
CREATE
FUNCTION <Scalar_Function_Name, sysname, FunctionName> ( -- Lista de parámetros<@Param1, sysname, @p1> <Data_Type_For_Param1, , int>, ...)
-- Tipo de datos que devuelve la función. RETURNS <Function_Data_Type, ,int>AS BEGIN
...
END |
El siguiente ejemplo muestra como crear una función escalar.
CREATE
FUNCTION fn_MultiplicaSaldo( @NumCuenta
VARCHAR(20),@Multiplicador DECIMAL(10,2)
) RETURNS DECIMAL(10,2)AS BEGIN
DECLARE @Saldo DECIMAL(10,2), @Return DECIMAL(10,2) SELECT @Saldo = SALDO FROM CUENTAS WHERE NUMCUENTA = @NumCuenta SET @Return = @Saldo * @Multiplicador RETURN @Return END |
Pueden ser utilizadas en cualquier sentencia Transact SQL. Un aspecto a tener en cuenta, es que para utilizar una función escalar debemos identificar el nombre de la función con el propietario de la misma.
El siguiente ejemplo muestra como utilizar la función anteriormente creada en una sentencia Transact SQL. Un aspecto muy a tener en cuenta es que la función ejecutará sus sentencias SELECT una vez por cada fila del conjunto de resultados devuelto por la consulta SELECT principal.
SELECT
IDCUENTA, NUMCUENTA , SALDO , FXALTA , -- Ejecucion de la funcion: dbo.fn_MultiplicaSaldo( NUMCUENTA, IDCUENTA) AS RESULTADOFROM CUENTAS
|
El siguiente ejemplo muestra como utilizar una función escalar en un script Transact SQL.
DECLARE
@NumCuenta VARCHAR(20), @Resultado DECIMAL(10,2) SET @NumCuenta = '200700000001'SET @Resultado = dbo.fn_MultiplicaSaldo(@NumCuenta, 30.5) PRINT @Resultado
|
Las funciones escalares son muy similares a procedimientos almacenados con parámetros de salida, pero estas pueden ser utilizadas en consultas de seleccion y en la clausula where de las mismas.
Las funciones no pueden ejecutar sentencias INSERT o UPDATE.
Funciones en linea
Las funciones en linea son las funciones que devuelven un conjunto de resultados correspondientes a la eecución de una sentencia SELECT.
La sintaxis para una función de tabla en linea es la siguiente:
CREATE
FUNCTION <Inline_Function_Name, sysname, FunctionName> ( -- Lista de parámetros<@param1, sysname, @p1> <Data_Type_For_Param1, , int>,... ) RETURNS TABLE AS RETURN ( -- Sentencia Transact SQL)
|
El siguiente ejemplo muestra como crear una función en linea.
CREATE
FUNCTION fn_MovimientosCuenta( @NumCuenta
VARCHAR(20)
) RETURNS TABLEAS RETURN (
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 linea 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
|
Funciones en línea de multiples sentencias
Las funciones en línea de multiples 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.
Este tipo de función se usa en situaciones donde se requiere una mayor lógica de proceso.
La sintaxis para una funciones de tabla de multi sentencias es la siguiente:
CREATE
FUNCTION <Table_Function_Name, sysname, FunctionName> ( -- Lista de parámetros<@param1, sysname, @p1> <data_type_for_param1, , int>, ...) RETURNS -- variable de tipo tabla y su estructura < @Table_Variable_Name, sysname, @Table_Var> TABLE ( <Column_1, sysname, c1> <Data_Type_For_Column1, , int>, <Column_2, sysname, c2> <Data_Type_For_Column2, , int>) AS BEGIN -- Sentencias que cargan de datos la tabla declarada RETURN END
|
El siguiente ejemplo muestra el uso de una funcion de tabla de multi sentencias.
/* Esta funcion 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 cursor WHILE (@@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()
|
Y el resultado obtenido ... NumCuenta Saldo Saldo_anterior Saldo_posterior Importe_Movimiento FxMovimiento
------------ ------ --------------- ---------------- ------------------- -----------------------
200700000002 500.00 NULL NULL NULL NULL
NULL NULL 550.00 500.00 50.00 2007-08-25 16:18:36.490
NULL NULL 600.00 550.00 50.00 2007-08-23 16:20:41.183
NULL NULL 600.00 550.00 50.00 2007-08-23 16:14:05.900
200700000001 100.99 NULL NULL NULL NULL
NULL NULL 50.99 100.99 50.00 2007-08-25 16:18:36.490
NULL NULL 0.99 50.99 50.00 2007-08-23 16:20:41.183
NULL NULL 50.99 0.99 50.00 2007-08-23 16:16:29.840
|
| |
 |
chdoi
por
dj
Respuesta recibida el [26/05/2009 05:13:04]
|
 |
muy buen tutorial, entendible y sin complicaciones
|
| |
|
Super!!
por
Fatima
Respuesta recibida el [17/06/2009 11:23:35]
|
|
|
Este documento si que me esta sirviendo mucho!! esta muy completo en lo q respecta a las necesidades que tengo.
|
| |
|
1A
por
Martin Bustamante
Respuesta recibida el [18/06/2009 12:46:09]
|
|
|
Este es mi lugar de consulta favorito
|
| |
|
graxias
por
Dana
Respuesta recibida el [09/07/2009 07:38:19]
|
|
|
gracias eh, el tutorial esta clarisimo, me sirvio para lo q necesitaba hacer
|
| |
|
Buen tutorial
por
superricagoku
Respuesta recibida el [13/07/2009 05:10:24]
|
|
|
Gracias por el tutorial, es bastante claro y es fácil de entender
|
| |
|
Buena nota
por
Miguel González
Respuesta recibida el [08/09/2009 10:04:56]
|
|
|
Felicidades, el tutorial es claro y concreto, solo que tengo ciertas dudas de como extender algunas funcionalidad, no se si este medio sea el apropiado para hacerle llegar las mismas.
Muchas gracias
Miguel González
jmgoc76@gmail.com
|
| |
|
Función Recursiva
por
TomYorke
Respuesta recibida el [06/10/2009 04:18:29]
|
|
|
como lo hago para utilizar mi funcion recursivamente, lo he intentado pero me da errores al utilizar if o while.
|
| |
|
muy buen trabajo , esta muy bu...
por
Roger Bonilla
Respuesta recibida el [07/01/2010 09:27:50]
|
|
|
muy buen trabajo , esta muy buena la pagina todo el tutorial en si.....
|
| |
|
Que buen aporte
por
Tony
Respuesta recibida el [18/02/2010 07:15:52]
|
|
|
es una muy buena página de consultas... gracias
|
| Añadir comentario ... |
Para preguntar utiliza los foros
|
|
|
Funciones en Transact SQL |
|
Autor:
Pedro Herrarte Sánchez
|
|
Visitas:
40217 |
Fecha de publicación:
08/09/2007 |
Pedro Herrarte, es consultor independiente, ofreciendo servicios de consultoría, análisis, desarrollo y formación.
Posee mas de diez años de experiencia trabajando para las principales empresas de España.
Es especialista en tecnologías .NET, entornos Web, bases de datos (SQL Server y ORACLE) e integración de sistemas.
Es experto en desarrollo (C#, ASP.NET, VB.Net, T-SQL, PL/SQL, , ASP, CGI , C, Pro*C, Java, Essbase, Vignette, PowerBuilder y Visual Basic ...) y bases de datos (SQL Server y ORACLE).
Es fundador, diseñador y programador de www.devjoker.com. |
|
|
Visitas:
81
|
Comentarios:
2
|
Archivo:
Articulos
|
Visitas:
786
|
Comentarios:
2
|
Archivo:
Articulos
|
Visitas:
725
|
Comentarios:
3
|
Archivo:
Articulos
|
Visitas:
644
|
Comentarios:
2
|
Archivo:
Articulos
|
Visitas:
3116
|
Comentarios:
2
|
Archivo:
Articulos
|
Visitas:
14470
|
Comentarios:
7
|
Archivo:
Articulos
|
Visitas:
1462
|
Comentarios:
3
|
Archivo:
Articulos
|
|
Visitas:
688
|
Comentarios:
2
|
Archivo:
Articulos
|
|
Visitas:
367
|
Comentarios:
0
|
Archivo:
Articulos
|
Visitas:
1983
|
Comentarios:
2
|
Archivo:
Articulos
|
|
|
|
|
|