自定义函数----sqlserver

cultureSun

发布于 2023-05-18 14:22:09

95600

代码可运行

文章被收录于专栏：cultureSun学安全cultureSun学安全

运行总次数：0

代码可运行

简介

和C语言、Java、Python等一样，数据库也可以自定义函数，同样可以传参，拥有返回值。在工作中可能会遇到一些业务，需要反复执行某些sql，可以自定义一个函数，非常的方便。当然，还有很多业务可以用自定义函数快速完成。

讲解

函数分类

标量函数标量函数返回一个确定类型的标量值，其返回值类型为除TEXT、NTEXT、IMAGE、CURSOR、TIMESTAMP和TABLE类型外的其它数据类型。函数体语句定义在BEGIN-END语句内。在 RETURNS 子句中定义返回值的数据类型，并且函数的最后一条语句必须为Return语句。
内联表值函数内联表值型函数以表的形式返回一个返回值，即它返回的是一个表。内联表值型函数没有由BEGIN-END 语句括起来的函数体。其返回的表是由一个位于 RETURN 子句中的 SELECT 命令从数据库中筛选出来。内联表值型函数功能相当于一个参数化的视图。
多语句表值函数多语句表值函数可以看作标量函数和内联表值函数的结合体。它的返回值是一个表，但它和标量型函数一样有一个用 BEGIN-END 语句括起来的函数体，返回值的表中的数据是由函数体中的语句插入的。由此可见，它可以进行多次查询，对数据进行多次筛选与合并，弥补了内联表值函数的不足。

创建语句

--标量函数
CREATE FUNCTION function_name(@parameter_name parameter_data_type) 
--CREATE FUNCTION 函数名称（@参数名 参数的数据类型）
RETURNS date_type   --返回返回值的数据类型，注意是 RETURNS
[WITH ENCRYPTION]  --如果指定了 encryption 则函数被加密
[AS]
BEGIN
  function_body --函数体(即 Transact-SQL 语句)
  RETURN  表达式;

--内联表值函数
CREATE FUNCTION function_name(@parameter_name parameter_data_type) 
--CREATE FUNCTION 函数名称（@参数名 参数的数据类型）
RETURNS table    --返回一个表（类似视图，无须指定列）
[WITH ENCRYPTION]  --如果指定了 encryption 则函数被加密
[AS]
RETURN  一条查询SQL语句

--多语句表值函数
CREATE FUNCTION function_name(@parameter_name parameter_data_type) 
--CREATE FUNCTION 函数名称（@参数名 参数的数据类型）
RETURNS @Table_Variable_Name table (Column_1 culumn_type,Column_2 culumn_type)    
--RETURNS @表变量 table 表的定义(即列的定义和约束)
[WITH ENCRYPTION]  --如果指定了 encryption 则函数被加密
[AS]
BEGIN
     函数体(即 Transact-SQL 语句)
 RETURN

SQL

修改语句

个人没有遇到相关需要修改函数的业务场景。只需把创建语句的 create 改为 alter 即可。

删除语句

drop function 函数名

实例

--示例表test
CREATE TABLE test(
    column_one VARCHAR(128) null,
    column_two VARCHAR(128) null,
    column_three VARCHAR(128) NULL
)

--插入数据
INSERT INTO test VALUES('column_one_1','column_two_1','column_three_1');
INSERT INTO test VALUES('column_one_2','column_two_2','column_three_2');
INSERT INTO test VALUES('column_one_3','column_two_3','column_three_3');

--查询一下数据
SELECT * FROM test;

--创建标量函数scalar_valued_functions
CREATE FUNCTION scalar_valued_functions(@in varchar(128))
    RETURNS VARCHAR(128)
AS
    BEGIN
        DECLARE @out VARCHAR(128)
        SELECT @out=column_two FROM test WHERE column_one=@in
        RETURN @out
    END

--调用函数，注意加上模式名(dbo)，查询结果（column_two_2）
SELECT dbo.scalar_valued_functions('column_one_2');

--删除标量函数scalar_valued_functions。
DROP FUNCTION dbo.scalar_valued_functions;

--创建多参数标量函数，
CREATE FUNCTION scalar_valued_functions(@in varchar(128), @var2 VARCHAR(128))
    RETURNS VARCHAR(128)
AS
    BEGIN
        DECLARE @out VARCHAR(128)
        SELECT @out=column_two,@var2=column_three FROM test WHERE column_one=@in
        RETURN @out+@var2
    END

--调用多参数标量函数scalar_valued_functions，查询结果(column_two_2column_three_2)
SELECT dbo.scalar_valued_functions('column_one_2','');

--创建内联表值函数inline_table_values_function
CREATE FUNCTION inline_table_values_function(@var VARCHAR(128))
    RETURNS TABLE
AS
    RETURN SELECT column_two,column_three FROM test WHERE column_one LIKE @var

--调用内联表值函数，查询结果(test表第二列和第三列)
SELECT * FROM inline_table_values_function('column_one%');

--创建多语句表值函数multi_statement_table_value_function
CREATE FUNCTION multi_statement_table_value_function(@var VARCHAR(128))
    RETURNS @table_test TABLE(first_column VARCHAR(128),second_column VARCHAR(128))
AS
    BEGIN
    INSERT INTO @table_test(first_column,second_column) SELECT column_two,column_three FROM test WHERE column_one LIKE @var
    RETURN
    END

--调用多语句表值函数，查询结果(test表第二列和第三列)
SELECT * FROM multi_statement_table_value_function('column_one%');

SQL