funciones definidas por el usuario - sql

7/31/2019 Funciones Definidas Por El Usuario - SQL

1/20

FUNCIONES DEFINIDAS

POR EL USUARIO

MG. Luis Boy Chavil


2/20

Funciones definidas por el usuario

Son rutinas de una o ms instrucciones Transac-SQLque pueden utilizarse para encapsular cdigo con

el fin de reutilizarlo.

Una funcin toma 0 o ms parmetros de entrada ydevuelve un valor escalar o una tabla.

Los valores de entrada pueden ser de cualquier tipo de

datos, excepto timestamp, cursor o table.

Las funciones no aceptan parmetros de salida.


3/20

Ventajas de las UDFs

Permiten una programacin modular

Las UDFs, despus de creadas, pueden ser modificadas.

Permiten una ejecucin ms rpida

Las UDFs, una vez compiladas, no es necesario volver aanalizarlas y optimizarlas.

Pueden reducir el trfico de red

Pueden expresar operaciones complejas de filtro de datos

y luego estas funciones se pueden expresar en la clusula

WHERE para reducir el nmero de filas que se envian al

cliente.


4/20

Componentes de una UDF

El encabezado, que define: Nombre de la funcin con nombre del propietario o esquema

opcional

Nombre del parmetro de entrada y tipo de datos

Opciones aplicables al parmetro de entrda Tipo de datos de parmetro devueltos y nombre opcional

Opciones aplicables al parmetro devuelto.

El cuerpo de la funcin, que define: Una o ms instrucciones que ejecutan la lgica de la funcin

Una referencia a un ensamblado .NET


5/20

Funciones Escalares

Devuelven un nico valor de datos del tipo definido enla clusula RETURN.

El tipo devuelto puede ser de cualquier tipo de datosexcepto text, ntext, image, cursor y timestamp.


6/20

Funciones Escalares

Sintaxis:CREATE FUNCTION [schema_name.]funcion_name ([{@parameter_name

[AS]

[Type_schema_name.]parameter_data_type [=default] [READONLY]} [, n])

RETURNS return_data_type[WITH [,n]] [AS]

BEGIN

function_body

RETURN scalar_expression

END[;]


7/20

Schema_name Nombre del esquema al que pertenece la UDF

Function_name

Nombre de la funcin definida por el usuario @parameter_name

La UDF puede tener un mximo de 2,100 parmetros

[type_schema_name.]parameter_data_type

Es el tipo de datos del parmetro, y de forma opcional, el

esquema al que pertenece.

Funciones Escalares


8/20

[=default]

Es un valor predeterminado para el parmetro. Si se define un

valor default, la funcin se puede ejecutar sin especificar un valorpara ese parmetro.

READONLY

Indica que el parmetro no se puede modificar en la definicin de

la funcin.

Return_data_type Es el valor devuelto

Funciones Escalares


9/20

-- Funcin para calcular un nmero elevado al cuboUSE Northwind

go

if OBJECT_ID ('dbo.fnCubo', 'FN') is not null

drop function fnCubo

go

create function dbo.fnCubo(@Numero float)

returns float

AS

Begin

return (@Numero * @Numero * @Numero)

Endgo

-- Ejecucin de la funcin fnCubo

USE Northwind

go

select dbo.fnCubo(5)

go

Funciones Escalares


10/20

Funciones de tabla en lnea

Devuelven la salida de un tipo de dato TABLE Sintaxis:

CREATE FUNCTION [schema_name.]funcion_name ([{@parameter_name

[AS][Type_schema_name.]parameter_data_type [=default] [READONLY]} [,

n])

RETURNS TABLE

[WITH [,n]] [AS]

RETURN [(] select_stmt [)]

[;]


11/20

TABLE Especifica que el valor devuelto de la funcin con valores de

tabla es una tabla. Slo se pueden pasar constantes y@local_variables a las funciones con valores de tabla.

En las funciones insertadas con valores de tabla, el valor

devuelto de TABLE se define mediante una nica instruccinSELECT. Las funciones insertadas no tienen variables deretorno asociadas.

Funciones de tabla en lnea


12/20

Funciones de tabla de mltiples sentencias

Son similares a los store procedures; exceptoque devuelven una tabla.

Sintaxis:CREATE FUNCTION [schema_name.]funcion_name

([{@parameter_name[AS]

[Type_schema_name.]parameter_data_type [=default] [READONLY]}[, n])

RETURNS @return_variable TABLE

[WITH [,n]] [AS]BEGIN

function_body

END

[;]


13/20

SUM Devuelve el total de los valores de una columna

SUM ([ALL|DISTINCT] expression)

MAX Devuelve el valor mximo de una columna en una

tabla o conjunto de filas devuelto en una consulta

MAX ([ALL|DISTINCT] expression)

MIN Devuelve el valor mnimo de una columna en una

tabla o conjunto de filas devuelto en una consulta

MIN ([ALL|DISTINCT] expression)

Funciones Integradas


14/20


AVG Calcula el valor medio de una columna o en un

conjunto de filas, segun como se use.

AVG ([ALL|DISTINCT] expression)

COUNT Devuelve el nmero de filas de una consulta que

coincide con una clusula WHERE en concreto.

COUNT ({[[ALL|DISTINCT] expression]|*})


15/20


CONVERT y CAST Convierte un tipo de datos en otro de forma que

se pueda realizar ciertas operaciones.

CAST (expression AS data_type [(length)])

CONVERT(data_type [(length)], expression[,style])

GETDATE

Devuelve la fecha y hora actual del sistema

GETDATE()


16/20


DATEDIFF Compara y obtiene la diferencia entre elementos

de fecha, como das, semanas, minutos y horas.

DATEDIFF (datepart, startdate, endate)


17/20


DATEPART Devuelve un valor igual a la parte de la fecha que

haya especificado.

DATEPART (datepart, date)

Otras funciones: Substring, Left, Right, Upper

Charindex

Rtrim, Ltrim, Len, Replicate, Space Replace, Str, Char


18/20

Funciones del Sistema

ISNULL COALESCE

Similar a ISNULL, pero permite que una lista devalores sustituya al valor nulo.

COALESCE (expression [, ])

NEWID Crea un valor nico del tipo: uniqueidentifier

NEWID()


19/20


USER_ID Toma una cadena de nombre y devuelve el Id de

la base de datos para ese nombre.

USER_ID([user])

USER_NAMEUSER_NAME ( [ID])

DATALENGTH

Devuelve la longitud de los datos almacenados enuna variable o en una columna.

DATALENGTH(expression)


20/20


HOST_NAME Devuelve el nombre de la estacin de trabajo en

la que se encuentra actualmente ejecutando algunproceso determinado.

HOST_NAME()

funciones definidas por el usuario - sql

Documents