advanced data base
TRANSCRIPT
-
8/10/2019 Advanced data base
1/15
User-defined
functions
Advanced topics
in SQL Server
-
8/10/2019 Advanced data base
2/15
User-defined functions
Purpose: Encapsulate processing logic prescribed by the user;specify a set of instructions used to process table data and otherqueryable objects or variables in order to return a result in form ofa table or a scalar value.
Represent stand-alone objects that can be reused (called)
inside definitions of other SQL Server objects by specifyingfunction name and input parameters, if applicable.
May be called inside views, stored procedures and otherfunctions, which helps reducing code volume and leads tobetter structured code.
Recommended when a certain block of SQL code must beused by multiple queries /views / procedures / functions.
May be used to produce virtual tables that can be queriedsimilarly to database tables.
-
8/10/2019 Advanced data base
3/15
Types of user-defined functions
Scalar-valued functions
o Return a single value represented by a scalar data type,either built-in or user-defined
Table-valued functionso Return a set of records which emulate a virtual table
o Depending on function definition:
Inline functions: return a table as a result of a single SQLstatement without requiring explicit definition of that
table;
Multi-statement functions: require explicit definition ofthe resulting table.
-
8/10/2019 Advanced data base
4/15
Scalar-valued functions
Return a single value
Accept multiple parameters
Consist of 2 parts:
o Function headerSpecifies function name, parameter list
and the data type of the result;
o Function bodyDefines the internal logic responsible forproducing the result; it ends with the RETURN commandwhich provides the value or the expression representingthe result.
May be called inside SQL statements in clausessuch as SELECT, WHERE , GROUP BY, HAVING orORDER BY.
-
8/10/2019 Advanced data base
5/15
Scalar-valued functions
Creation: CREATE FUNCTIONfunction_name ([@parameter_1 AS data_type],
. . . [@parameter_n AS data_type])RETURNSdata_typeAS
BEGIN
SQL commandsRETURNvalue/expression_to_return
END
Alteration: ALTER FUNCTION ...
Removal: DROP FUNCTION ...
-
8/10/2019 Advanced data base
6/15
Scalar-valued functions - Example
-
8/10/2019 Advanced data base
7/15
Table-valued user defined functions
A.INLINE functions return a table, without explicitly defining its structure
a single SELECT statement is the only acceptedcontents
BEGIN .ENDstructures are not allowed each field in the source query must have an explicit
name
the columns returned by the function are provided by
the SELECT statement on which it relies The ORDER BY clause is only allowed when
accompanied by TOP
-
8/10/2019 Advanced data base
8/15
Table-valued user defined functions
A. INLINE functionsThe syntax:
CREATE FUNCTION function_name ( [@parameter_1 AS data_type],
[@parameter_n AS data_type] )
RETURNS TABLEAS
RETURN(SQL_SELECT_Statement)
-
8/10/2019 Advanced data base
9/15
INLINE functions - Example
-
8/10/2019 Advanced data base
10/15
Table-valued functions
B. MULTI-STATEMENT functions:
return a table as a result
may encapsulate complex processing logic
the structure of the resulting table must be explicitlydefined
-
8/10/2019 Advanced data base
11/15
Table-valued functions
B. MULTI-STATEMENT functionsThe syntax:
CREATE FUNCTIONfunction_name([@parameter_1 AS date_type],
. . . [@parameter_n AS data_type])
RETURNS@result_variable TABLE (field_1 data_type,
. . . field_N data_type)
AS
BEGIN
SQL statements / commands
RETURN
END
bl l d f l
-
8/10/2019 Advanced data base
12/15
Table-valued functions - Example
-
8/10/2019 Advanced data base
13/15
Table variables
Allocate memory space to store record sets definedas virtual tables.
Allow temporary storage of data organized inmultiple predefined fields so that it can be used forvarious processing operations.
Enable efficient data processing if subjected torelational algebra operators.
May by subjected to SELECT queries & support JOINoperator relative to other tables.
May be used in INSERT, UPDATE or DELETE statements.
-
8/10/2019 Advanced data base
14/15
Table variables
Declare@TABLE_VARIABLE_NAME TABLE(
Field_Name1 Data_Type_1,Field_Name2 Data_Type_2,
Field_NameN Data_Type_N,)
Insert into@TABLE_VARIABLE_NAME
(Field_Name1, Field_Name2, )Values (Value1, Value2, )
----------------------------------------------------------------------------
Select *From @TABLE_VARIABLE_NAME
Where
-
8/10/2019 Advanced data base
15/15
Table variables Example