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.
  • Funciones en línea.
  • Funciones en línea de multiples sentencias

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 RESULTADO

FROM 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 TABLE

AS

RETURN

(

SELECT MOVIMIENTOS.*

FROM MOVIMIENTOS

INNER JOIN CUENTAS ON MOVIMIENTOS.IDCUENTA = CUENTAS.IDCUENTA

WHERE 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 CUENTAS

INNER JOIN CUENTAS_CLIENTE
ON CUENTAS_CLIENTE.IDCUENTA = CUENTAS.IDCUENTA

INNER 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 saldo

DECLARE CDATOS CURSOR FOR

SELECT TOP 3 IDCUENTA, NUMCUENTA, SALDO

FROM CUENTAS

ORDER BY SALDO DESC

 

OPEN CDATOS

FETCH CDATOS INTO @idcuenta, @numcuenta, @saldo

 

-- Recorremos el cursor

WHILE (@@FETCH_STATUS = 0)

BEGIN

-- Insertamos la cuenta en la variable de salida

INSERT INTO @datos

(NumCuenta, Saldo)

VALUES

(@numcuenta, @saldo)

-- Insertamos los tres últimos movimientos de la cuenta

INSERT INTO @datos

(Saldo_anterior, Saldo_posterior,
Importe_Movimiento
, FxMovimiento )

SELECT TOP 3

SALDO_ANTERIOR, SALDO_POSTERIOR,
IMPORTE
, FXMOVIMIENTO

FROM MOVIMIENTOS

WHERE IDCUENTA = @idcuenta

ORDER BY FXMOVIMIENTO DESC

-- Vamos a la siguiente cuenta

FETCH CDATOS INTO @idcuenta, @numcuenta, @saldo

END

 

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

 

Pedro  Herrarte  Sánchez
Funciones en Transact SQL
Pedro Herrarte Sánchez

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 (ASP.NET, ASP.NET MVC,jQuery, HTML5), bases de datos (SQL Server y ORACLE) e integración de sistemas. Es experto en desarrollo (C#, 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). Pedro es MCP y MAP 2012, es fundador, diseñador y programador de www.devjoker.com..
Fecha de alta:08/09/2007
Última actualizacion:08/09/2007
Visitas totales:204817
Valorar el contenido:
Últimas consultas realizadas en los foros
Últimas preguntas sin contestar en los foros de devjoker.com