T-SQL Programming--user-defined functions (scalar functions)

User-defined Functions

When using SQL Server, in addition to its built-in functions, it allows the user to define functions as needed. A user-defined function can be divided into three categories based on the type of the value returned by the user-defined function:

• Functions that return values as updatable tables

If a user-defined function contains a single SELECT statement and the statement is updatable, the table returned by the function can also be updated, such as an inline table-valued function.

• Functions that return values that do not update tables

If a user-defined function contains more than one SELECT statement, the table returned by the function is not updatable. Such a function is called a multi-statement table-valued function.

• Functions that return scalar values

The return value of a user-defined function is a scalar value, and such a function is called a scalar function .

Here you need to explain that a user-defined function can accept 0 or more input parameters, the return value of a function can be a value, or it can be a table. User-defined functions do not support output functions;

Alter function can be used to modify user-defined functions, with drop function can delete user-defined functions (of course, can also be directly through the graphical interface operation to delete, but there is not much to be said);

definition and invocation of scalar functions

The syntax format for scalar function definitions is as follows:

1  Create function [owner_name]function_name2([{@parameter_name [ as]Scalar_parameter_date_type[=default]}[,... N]])3  returnsScalar_return_data_type[With encryption] [ as]4  begin5 function_body6      returnscalar_expression7  End

The meanings were as follows:

• owner_name : Database all names.
• function_name: User-defined function name, function name must conform to the identifier specification, and for its owner, the user name must be unique in the database.
• @parameter_name: The formal parameter name of the user-defined function, the CREATE FUNCTION statement can declare one or more parameters, with the @ sign as the first character to specify the parameter name, each function's parameters are local to the function.
• Scalar_parameter_data_type: The data type of the parameter, which can be a basic scalar type supported by the system, cannot be timestamp type, user-defined data type, non-scalar type (such as cursor and tabel).
• Default: Specify Defaults.
• The WITH clause indicates the option to create the function, and if the encryption parameter is indicated, the created function is encrypted and the text of the function definition is stored in an unreadable form in the syscomments table, and no one can see the definition of the function. Includes the creator of the function and the system administrator.
• A function question is defined between begin and end , and the function body must include a return statement to return a value. The Scalar_expression function returns the value of an expression.
• Scalar_return_data_type: The return type of a user-defined function, which can be a basic scalar type supported by SQL Server, except text, NTERXT, image, and timestamp.

Based on the above explanation, you might want to customize a function to do a statistic--

　　The average score of the student's test and written test in the Statistical institute .

　　Suppose you already have a exam table with a stuno field in the table for easy querying.

There is also a written test score field written and a pilot field lab, and there is already a record.

1 Create functionGetscore (@stuno varchar( -))2 returns  float  as3 begin4     Declare @Score float5     Set @Score = -1;6Selecet@Score =(IsNull(Written,0)+IsNull(Lab,0))/27      fromExam8     whereStuno= @stuno9     return @ScoreTen End

Calls to scalar functions:

When invoking a user-defined function, you must provide at least two parts of all names (owner. function name). There are two ways to invoke a user-defined function:

　　1. Called in the SELECT statement--

Owner_name.function_name (@parameter_name1, 2 ...)

A parameter can be an assignment of a local variable or an expression, for example: calling a user-defined function Getscore.

Select dbo.getscore ('123456789');

2. Execute with the EXEC statement

When invoking a custom function with a T-SQL EXECUTE statement, the identity order of the arguments can be different from the parameter identification order in the function definition in the form of a specific invocation:

Owner_name.function_name @parameter_name1, .... @parameter_name_n

Or

Owner_name.function_name @fparameter_name 1 = @aparameter_name1, .... @fparameter_name_n = @aparameter_name_n

The former argument order should be consistent with the formal parameters of the function definition, which can be inconsistent with the parameter newsletter of the function definition.

If the parameter of the function has a default value, the default keyword must be specified when calling the function to get the defaults, which differs from the parameters that have default values in the stored procedure, which means that the default value is used when the parameter is omitted.

T-SQL Programming--user-defined functions (scalar functions)