Shulou(Shulou.com)05/31 Report--

Editor to share with you the example analysis of SQL Function custom functions. I hope you will get something after reading this article. Let's discuss it together.

Catalogue

Background (already have stored procedures, why do you still use custom functions) Development History constitutes the scope of use, matters needing attention

Generate background (why use custom functions when you already have stored procedures)

The difference from a stored procedure (the meaning of existence):

1. Custom functions can be used directly in SQL statements such as select, but stored procedures are not. two。 Custom functions can call other functions, or they can call themselves (recursive) 3. You can use custom functions in table columns and CHECK constraints to implement special columns or constraints 4. 5. Custom functions cannot have any side effects. A function side effect is any permanent change to the state of a resource that has a scope outside the function, such as modifications to database tables. The only changes that can be made to statements in a function are changes to local objects on the function, such as local cursors or local variables. Actions that cannot be performed in a function include modifications to database tables, manipulation of local cursors that are not on the function, sending emails, trying to modify directories, and generating result sets that are returned to the user. Stored procedures do not have this restriction 5. 5. The function can return only one variable. Stored procedures can return multiple

History of development

User-defined functions are supported after SqlServer 2000.

Constitute

In SQL Server 2000, user-defined functions are divided into three types according to the form of function return values: scalar functions (Scalar Function), embedded table-valued functions (Inline Function), and multi-declared table-valued functions (Multi-Statement Function). Scalar functions operate on a single value and return a single value. Where expressions can be used, scalar functions can be used. Like left, getdate, etc., which we often use, are scalar functions. Scalar functions in system functions include: mathematical functions, date and time functions, string functions, data type conversion functions and other embedded table-valued functions: the function of embedded table-valued functions is equivalent to a parameterized view. It returns a table in which inline table-valued functions do not have a function body enclosed by a BEGIN-END statement. The returned table is filtered from the database by a SELECT command segment located in the RETURN clause.

Action

Multi-declared table-valued function: it can be regarded as a combination of scalar and embedded table-valued functions. Its return value is a table, but like a scalar function, it has a function body enclosed in a BEGIN-END statement, and the data in the table of the return value is inserted by the statement in the function body. Thus it can be seen that it can query many times and filter and merge the data many times, which makes up for the deficiency of inline table-valued functions.

Usage

SQL Server provides different command creation formats for three types of user-defined functions.

(1) the syntax for creating a scalar user-defined function (Scalar functions) is as follows:

The parameters are described as follows: owner_name: specifies the owner of the user-defined function. Function_name: specifies the name of the user-defined function. Database_name.owner_name.function_name should be unique. Parameter_name: defines the name of one or more parameters. A function can define up to 1024 parameters, each preceded by the "@" symbol. The scope of the parameter is the entire function. Parameters can only replace constants and cannot represent names, column names, or other database object names. User-defined functions do not support output parameters. Scalar_parameter_data_type: specifies the data type of a scalar parameter, which can be a data type other than TEXT, NTEXT, IMAGE, CURSOR, TIMESTAMP, and TABLE. Scalar_return_data_type: specifies the data type of the scalar return value, which can be a data type other than TEXT, NTEXT, IMAGE, CURSOR, TIMESTAMP, and TABLE. Scalar_expression: specifies the scalar-valued expression returned by a scalar user-defined function. Function_body: specifies a series of Transact-SQL statements that determine the return value of the function. ENCRYPTION: encryption option. Have SQL Server encrypt the declaration about CREATE FUNCTION in the system table to prevent user-defined functions from being published (Publish) as part of SQL Server replication. SCHEMABINDING: the plan binding option binds a user-defined function to the database object it references. If this option is specified, the database object involved in the function cannot be deleted or modified from now on unless the function is deleted or removed. It should be noted that the database object to be bound must be in the same database as the function.

(2) create an inline table-valued user custom function (Inline Table-valued Functions)

The syntax is as follows:

The parameters are described as follows:

TABLE: specifies that the return value is a table.

Select-stmt: a single SELECT statement that determines the data of the returned table.

The remaining parameters are the same as scalar user-defined functions.

(3) create a multi-declaration table-valued user-defined function

The syntax is as follows:

The parameters are described as follows:

Return_variable: a variable of type TABLE that stores and accumulates rows of data in the returned table. The remaining parameters are the same as scalar user-defined functions.

The following Transact-SQL statements are allowed in the function body of a multi-declared table-valued user-defined function. Assignment statements (Assignment statements); process control statements (Control-of-Flow statements); DECLARE statements that define variables and cursors within a function; SELECT statements; INSERT, UPDATE, and DELETE statements that edit table variables defined in a function Cursor operations such as declaring cursors, opening cursors, closing cursors, releasing cursors are allowed in the function, and for reading cursors, the FETCH statement is not allowed to return data to the client unless the INTO clause is used in the FETCH statement to assign a value to a variable. This external uncertainty function (Non-deterministic functions) cannot be used in user-defined functions. The so-called uncertain functions refer to those functions that use the same call parameters and get different return values at different times. These functions are shown in Table 13-3 (global variables can also be considered a function).

Scope of application

1. Only query, do not modify the status of the database (modify, delete records in the table, etc.)

two。 When the result set needs to be obtained by recursion, the function can be used, and the function is more flexible.

3. You can use functions when the result set needs to be referenced directly. The result set needs to be reprocessed (in the middle of the select statement), functions can be used, and functions can be embedded in sql statements such as select.

Note:

User-defined functions cannot be used to perform a series of operations that change the state of the database

Note when writing custom functions:

For scalar functions:

1. All entries must be preceded by @

2. For the return after create, the word is returns, not return

3. Returns is not followed by a variable, but the type of return value, such as int,char, etc.

4. In the begin/end statement block, it is return.

Embedded table-valued functions:

1. Only table can be returned, so returns must be followed by TABLE

2. There is no begin/end after AS, only a return statement to return a specific record.

Multi-statement table-valued function

1. The returned table type is directly defined after returns. First, the table name is defined, indicating that the @ is preceded by the keyword TABLE, and finally the table structure.

two。 In the begin/end statement block, you can simply insert the result that needs to be returned to the table defined by returns. At the end of the return, the result will be returned.

3. In the end, all you need is return,return without any variables.

Question: the custom function can not modify the database, but it can call the stored procedure, so in the custom function to call a stored procedure with the operation of modifying the database, can this custom function be executed?

A: custom functions can only call extended stored procedures, but subsequent versions of SQL Server 2008 will remove this feature and no longer support extended stored procedures, so you should avoid using extended stored procedures in development. Therefore, it can be concluded that in actual development, the function will not call the stored procedure, so it is impossible to modify the database.

After reading this article, I believe you have some understanding of "sample Analysis of SQL Function Custom functions". If you want to know more about it, you are welcome to follow the industry information channel. Thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.