php: Detailed explanation of SQL Server user-defined function types

Regarding SQL Server user-defined functions, there are two types: scalar functions and table-valued functions (inline table-valued functions, multi-statement table-valued functions).

Off topic, some friends may not know where SQL Server user-defined functions should be written. Here is a brief reminder. In Microsoft SQL Server Management Studio, expand the specific needs Create a database with SQL Server user-defined functions (that is, each user-defined function is only useful for a specific database), then find the Programmability option, and then expand and find Function option, in the specific function options, you can right-click and select as shown in the figure below to add.

The so-called scalar functionTo put it simply, the returned result is just a scalar. For me, The returned result is a value of one type.

The writing method is as follows:

CREATE FUNCTION <Scalar_Function_Name, sysname, FunctionName> 
(
    -- Add the parameters for the function here
    <@Param1, sysname, @p1> <Data_Type_For_Param1, , int>
)
RETURNS <Function_Data_Type, ,int>
AS
BEGIN
    -- Declare the return variable here
    DECLARE <@ResultVar, sysname, @Result> <Function_Data_Type, ,int>

    -- Add the T-SQL statements to compute the return value here
    SELECT <@ResultVar, sysname, @Result> = <@Param1, sysname, @p1>

    -- Return the result of the function
    RETURN <@ResultVar, sysname, @Result>

CREATE FUNCTION GetSum
(
    @firstNum int,
    @secondNum int
)
RETURNS int
AS
BEGIN
    -- Declare the return variable here
    DECLARE @result int

    -- Add the T-SQL statements to compute the return value here
    SELECT @result=@firstNum+@secondNum

    -- Return the result of the function
    RETURN @result

Digression: Let’s take a look at the above writing method. For SQL Server, we declare The way to declare a variable is to use @variable name, and compared to programming, the way SQL Server declares is a joke to us, it is the variable first and then the type. The method of passing parameters or not is actually the same as our programming method. If there are parameters, the method is as follows:

CREATE FUNCTION GetSum
(
    @firstNum int,
    @secondNum int
)

If there are no parameters, just keep the parentheses. It is consistent with our understanding of function writing.

CREATE FUNCTION GetSum
(
)

As for the return method, this is different from the way we program. The return type of the SQL Server function is not placed in front of the function name, but after the function name brackets. And the return type of the function needs to use the return keyword

RETURNS instead of RETURN.

For functions, of course there is also a so-called function body. The same goes for scalar functions. Its function body is included in:

AS
BEGIN
    -- 函数体
END

If you need to declare a variable in the function body, you need to use the

DECLARE keyword to declare it. The return in the function body is the keyword RETURN.

Okay, we have finished giving examples of scalar functions. To save them into the database, you need to click

in the Microsoft SQL Server Management Studio tool to perform the operation. After this, you can query the results in the Query window just like the query table data.

The usage method is easy to understand, but it should be noted that the

object name of [dbo] cannot be omitted, and () should not be missing after the [GetSum] function. Strangely enough, for table-valued functions, the object name [dbo] can be executed correctly without writing it.

select [dbo].[GetSum]()

Compared to scalar functions that only return a scalar value, inline table-valued functions return table data. Of course, table data is of Table type.

Writing is as follows:

CREATE FUNCTION <Inline_Function_Name, sysname, FunctionName> 
(    
    -- Add the parameters for the function here
    <@param1, sysname, @p1> <Data_Type_For_Param1, , int>, 
    <@param2, sysname, @p2> <Data_Type_For_Param2, , char>
)
RETURNS TABLE 
AS
RETURN 
(
    -- Add the SELECT statement with parameter references here
    SELECT 0
)
GO

CREATE FUNCTION [GetMoreThanSalary]
(    
    @salary int
)
RETURNS TABLE 
AS
RETURN 
(
    SELECT [FName],[FCity],[FAge],[FSalary] FROM [Demo].[dbo].[T_Person] Where [FSalary] > @salary
)

Digression: The content mentioned above for scalar functions will not be repeated here. The table structure returned by the inline table function is determined by the SELECT statement in the function body.

For scalar functions, the function body is included in the following structure.

AS
BEGIN
    -- 函数体
END

But for inline table-valued functions, the structure of the function body is as follows. Inline table-valued functions only execute one SQL statement and return the Table result.

AS
RETURN
    -- 函数体
END

The way to execute table-valued functions is as follows:

select [FName],[FCity],[FAge],[FSalary] from [dbo].[GetMoreThanSalary](8000)

It can be seen that this execution method is the same as the execution method of ordinary tables. Table-valued functions are actually equivalent to a virtual table stored in memory space.

Multi-statement table-valued functions and inline table-valued functions are both table-valued functions, and the results they return are all of Table type. As the name suggests, the multi-statement table-valued function can create Table type data through multiple statements. This is different from the inline table-valued function. The return result of the inline table-valued function is determined by the SELECT statement in the function body. Multi-statement table-valued functions require specifying a specific Table type structure. In other words, the returned Table has already defined which fields to return. So it can support the execution of multiple statements to create Table data.

CREATE FUNCTION <Table_Function_Name, sysname, FunctionName> 
(
    -- Add the parameters for the function here
    <@param1, sysname, @p1> <data_type_for_param1, , int>, 
    <@param2, sysname, @p2> <data_type_for_param2, , char>
)
RETURNS 
<@Table_Variable_Name, sysname, @Table_Var> TABLE 
(
    -- Add the column definitions for the TABLE variable here
    <Column_1, sysname, c1> <Data_Type_For_Column1, , int>, 
    <Column_2, sysname, c2> <Data_Type_For_Column2, , int>
)
AS
BEGIN
    -- Fill the table variable with the rows for your result set
    
    RETURN 
END
GO

ALTER FUNCTION DemoFun
(

)
RETURNS 
@result TABLE 
(
    name nvarchar(20),
    city nvarchar(20),
    age int,
    salary int
)
AS
BEGIN
    -- Fill the table variable with the rows for your result set
    insert into @result(name, city, age, salary)
    select FName,FCity,FAge,FSalary from dbo.T_Person where FSalary>8000
    insert into @result(name, city, age, salary) values
    ('测试','China', 1, 0)
    RETURN 
END
GO

题外话：可以看得出，多语句表值函数的返回结果是定义好表结构的虚拟表。这又跟标量函数一样了吧，只不过标量函数是返回一种类型的标量值而已。而且在多语句表值函数里面，你也会发现最后一句是RETURN。告诉执行程序，多语句表值函数已经执行完成。函数体结构跟标量函数的结构一样。对于类型放在变量后面这种方式确实需要好好转换一下观念。

RETURNS 
<@Table_Variable_Name, sysname, @Table_Var> TABLE 
(
    -- Add the column definitions for the TABLE variable here
    <Column_1, sysname, c1> <Data_Type_For_Column1, , int>, 
    <Column_2, sysname, c2> <Data_Type_For_Column2, , int>
)

内容倒是不多，但是要熟练使用的话，还是需要在项目中多加使用才行。网上有一些网友总结出来的常用自定义函数大家可以收集积累，就像做项目一样，好的方法要形成所谓的开发库，帮助我们在下一个项目中复用。节省我们的开发时间，提高我们的工作效率。

The above is the detailed content of php: Detailed explanation of SQL Server user-defined function types. For more information, please follow other related articles on the PHP Chinese website!