Capitulo 5 – Lección 2: Programación de Microsoft SQL Server con funciones.

Las funciones son objetos programables que se utilizan para realizar cálculos que se pueden devolver a una aplicación que llama o integrarse en un conjunto de resultados. Las funciones pueden acceder a los datos y devolver los resultados,pero no pueden realizar ninguna modificación.

Funciones del sistema (para mas información clic AQUI)
SQL Server incluye una amplia gama de funciones que puede utilizar para realizar muchas operaciones. Las funciones integradas puede ser divididas en categorías, como se muestra en las siguientes tablas:
Función Descripción
Funciones de conjuntos de filas Devuelven un objeto que se puede usar como las referencias a tablas en una instrucción SQL. (Ejemplos. OPENDATASOURCE, OPENROWSET o Predicados)
Funciones de agregado Operan sobre una colección de valores y devuelven un solo valor de resumen. (Ejemplos: SUM, AVG, COUNT_BIG, y VAR.
Funciones de categoría Devuelven un valor de categoría para cada fila de una partición.(Ejemplos: RANK, DENSE_RANK o NTILE)
Funciones escalares (se describen a continuación) Operan sobre un valor y después devuelven otro valor. Las funciones escalares se pueden usar donde la expresión sea válida.

Categoría de la función Descripción
Funciones de configuración Devuelven información acerca de la configuración actual. (Ejemplos: @@VERSION, @@SERVERNAME, o @@LANGUAGE.)
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.(Ejemplo: @@FETCH_STATUS o @@CURSOR_ROWS.)
Funciones de tipo de datos Devuelven información sobre los valores de identidad y los valores de otros tipos de datos. (ejemplos: IDENTITY o DATALENGTH)
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. (Ejemplos. DATEADD, DATEPART, DATEDIFF, and GETDATE.)
Funciones matemáticas Realizan cálculos basados en valores de entrada proporcionados como parámetros a las funciones y devuelven valores numéricos. (Ejemplos: SIN, COS, TAN, LOG, PI, and ROUND.)
Funciones de metadatos Devuelven información acerca de la base de datos y los objetos de la base de datos. (Ejemplos: OBJECT_NAME, OBJECT_ID, DATABASEPROPERTYEX, and DB_NAME.)
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ón. (Ejemplo: PUBLISHINGSERVERNAME)
Funciones de seguridad Devuelven información acerca de usuarios y roles. (Ejemplos: CURRENT_USER, sys.fn_my_permissions o SYSTEM_USER.)
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. (Ejemplos: POS,CHARINDEX,SOUNDEX, REPLACE, STUFF, o RTRIM. )
Funciones del sistema Realizan operaciones y devuelven información acerca de valores, objetos y configuraciones de una instancia de SQL Server. (Ejemplos:sys.dm_db_index_physical_stats, fn_virtualfilestats o @@ERROR.)
Funciones estadísticas del sistema Devuelven información estadística acerca del sistema. (Ejemplos: fn_virtualfilestats, @@CONNECTIONS o @@TIMETICKS)
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. (Ejemplos: TEXTVALID o PATINDEX)
Funciones de desencadenador Devuelven información acerca de los desencadenadores. (Ejemplos: RIGGER_NESTLEVEL o UPDATE())
La mayoría de las funciones del sistema se almacenan en la base de datos mssqlsystemresource o en la base de datos master, o están disponibles en las bibliotecas de código que soporte el lenguaje T-SQL.

Funciones definidas por el usuario (Para mas información clic AQUI)
Usted puede crear sus propias funciones, conocidas como las funciones de usuario (UDF),y almacenar las funciones de cualquier base de datos en las que disponga de autorización.
Mientras que las funciones se utilizan para realizar los cálculos, una función no se le permite cambiar el estado de una base de datos o instancia de SQL Server. Las funciones no pueden hacer nada de lo siguiente:
  • Realizar una acción que cambia el estado de una instancia o una base de datos
  • Modificar datos en una tabla
  • Llamar a una función que tiene un efecto externo, como por ejemplo la función RAND
  • Crear o acceder a las tablas temporales
  • Ejecutar código de forma dinámica
Las funciones pueden devolver un valor escalar o una tabla de valor. Con valores de tabla puede ser de dos tipos diferente: en línea y de múltiples instrucciones.
El sintaxis general de una función escalar es
CREATE FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ][ type_schema_name. ] parameter_data_type 
    [ = default ] [ READONLY ] } [ ,...n ]])
RETURNS return_data_type
    [ WITH <function_option> [ ,...n ] ]
    [ AS ]
    BEGIN 
        function_body 
        RETURN scalar_expression
    END
[ ; ]

Una función en línea con valores de tabla contiene una sola instrucción SELECT que devuelve una tabla.
Debido a una función en línea con valores de tabla no realiza ninguna otra operación, el optimizador considera una función en línea con valores de tabla como una vista.
La sintaxis general de una función en línea con valores de tabla es
CREATE FUNCTION [ schema_name. ] function_name 
( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type 
    [ =default ] [ READONLY ] }  [ ,...n ] ])

RETURNS TABLE
    [ WITH <function_option> [ ,...n ] ]
    [ AS ]
    RETURN [ ( ] select_stmt [ ) ]
[ ; ]

La sintaxis general de una instrucción múltiple con valores de tabla es
CREATE FUNCTION [ schema_name. ] function_name
 ( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type 
    [ =default ] [READONLY] } [ ,...n ]  ])

RETURNS @return_variable TABLE <table_type_definition>
    [ WITH <function_option> [ ,...n ] ]
    [ AS ]
    BEGIN 
        function_body 
        RETURN
    END
[ ; ]

El código dentro de cada función es necesario completarlo con una sentencia RETURN . Para una función escalar, que devuelven un solo valor. Para una función en línea con valores de tabla, se devuelve una instrucción SELECT. Para una función de instrucción múltiple con valores de tabla, que sólo incluyen la palabra clave RETURN al final de la función. Con la excepción de una función en línea con valores de tabla, todo el código dentro de la función se requiere ser incluido en un bloque BEGIN. . . END.

Independientemente del tipo de función, cuatro opciones se pueden especificar: ENCRYPTION, SCHEMABINDING , RETURNS NULL ON NULL INPUT / CALLED ON NULL INPUT, y EXECUTE AS. Las opciones ENCRYPTION, SCHEMABINDING, y EXECUTE AS también están disponibles para los procedimientos almacenados. Además, SCHEMABINDING se puede especificar para triggers y vistas. La opción ENCRYPTION se comporta igual independientemente del si va a crear un procedimiento almacenado, función, un desencadenador o vista.

La opción SCHEMABINDING se aplica para garantizar que no se puede quitar objetos dependientes.
Por ejemplo, si se va a crear una función que realiza un SELECT en la tabla Sales.SalesOrderHeader, por lo general se podría eliminar la tabla sin recibir un error. La próxima vez que se ejecuta la función, usted recibirá un error que la tabla Sales.SalesOrderHeader no existe. Para prevenir que objetos dependientes a un objeto programable se eliminen o alteren, se especifica la opción SCHEMABINDING. Si intenta quitar o modificar el objeto dependiente, SQL Server impide el cambio. Para eliminar o modificar un objeto dependiente, primero hay que quitar el objeto programable que depende del objeto que desea eliminar o modificar.
La opción SCHEMABINDING está disponible para Funciones y Vistas.
La opción que es única para una función es RETURNS NULL ON NULL INPUT /CALLED ON NULL INPUT. El valor predeterminado es CALLED ON NULL INPUT.En la configuración por defecto, si se especifica un parámetro NULL, la función todavía se llama y cualquier código dentro de la función se ejecuta. Si se especifica RETURNS NULL ON NULL INPUT, cuando se especifica NULL para el parámetro de entrada, la función no se ejecuta, y NULL es devuelto de inmediato a la rutina de llamada. Si usted tiene una función que debe ser ejecutada sólo si han pasado sin parámetros NULL, debe especificar la opción RETURNS NULL ON NULL INPUT para que pueda evitar la ejecución de código extraño.

Recuperar datos desde una función
Se recuperan datos de una función mediante una instrucción SELECT. Las funciones pueden usarse en cualquiera de los siguientes casos:
  • Una lista SELECT
  • Una cláusula WHERE
  • una expresión
  • Una restricción CHECK o DEFAULT
  • Una cláusula FROM con la función CROSS/OUTER APPLY
¿Cómo una función puede tener un impacto drástico en el rendimiento de las consultas que se ejecuta?.
Una función en la lista SELECT se utiliza para calcular una suma o realizar un cálculo en una o más columnas de las tablas en la cláusula FROM. Una función en la cláusula WHERE se utiliza para restringir a un conjunto de resultados basándose en los resultados de la función.
Las funciones se pueden anidar unas dentro de otras, siempre y cuando el valor de retorno de una función interna coincide con el parámetro de entrada de la función externa. Por ejemplo, una cadena común, el análisis de rutina podría contener código de la siguiente manera:

DATALENGTH(POS(CHARINDEX(REPLACE(...)))).

Funciones en el CHECK y restricciones DEFAULT se utilizan para extender los cómputos estáticos disponible. Por ejemplo, si desea validar el código de área para un número de teléfono en una lista de los códigos de área almacenado dentro de una tabla, puede utilizar una función para realizar la validación de que normalmente no sería posible debido a una restricción CHECK no acepta una SELECT.
Una función en línea con valores de tabla se comporte y es intercambiable con una vista.
Resumen de la lección
  • Puede crear funciones escalares, funciones en línea con valores de tabla, y con múltiples instrucciones con valores de tabla.
  • Con la excepción de la en línea con valores de tabla, el cuerpo de la función debe ser encerrado dentro de un bloque. BEGIN. . . END
  • Todas las funciones deben terminar con una sentencia RETURN .
  • Las funciones no se les permite cambiar el estado de una base de datos o de una instancia de SQL Server.

No hay comentarios:

Publicar un comentario