• SQL Server 自定义函数


    简介

       SQL Server自定义函数分为三种类型:标量函数(Scalar Function)、内联表值函数(Inline Function)、多语句表值函数(Multi-Statement Function)

             标量函数:标量函数返回一个确定类型的标量值,返回值类型为除TEXT、NTEXT、IMAGE、CURSOR、TIMESTAMP和TABLE类型外的其它数据类型。函数体语句定义在BEGIN-END语句内,在 RETURNS 子句中定义返回值的数据类型,并且函数的最后一条语句必须为Return语句

       内联表值函数:内联表值型函数以表的形式返回一个返回值,即它返回的是一个表。内联表值型函数没有由BEGIN-END 语句括起来的函数体。其返回的表是由一个位于 RETURN 子句中的 SELECT 命令从数据库中筛选出来。内联表值型函数功能相当于一个参数化的视图


    多语句表值函数:多语句表值函数可以看作标量函数和内联表值函数的结合体。它的返回值是一个表,但它和标量型函数一样有一个用 BEGIN-END 语句括起来的函数体,返回值的表中的数据是由函数体中的语句插入的。由此可见,它可以进行多次查询,对数据进行多次筛选与合并,弥补了内联表值函数的不足

    标量函数

    创建标量函数的语法:

     create function [函数的所有者].函数名(标量参数 [as] 标量参数类型 [=默认值])
     returns 标量返回值类型
     begin
         函数体(即 Transact-SQL 语句)
         return 变量/标量表达式
     end

    案例:将字符串‘001.002.003.004’按照指定分隔符进行分割,返回分割后的个数

    create function dbo.Fun_GetStrListLeng
    (
    	@originlStr varchar(500), --要分割的字符串
    	@split		varchar(10)  --分隔符
    )
    returns int
    as
    begin
    	declare @location int,--定义起始位置
    			@start    int,--定义从第几个开始
    			@length	  int;--定义变量,用于接收计算元素的个数
    	set @originlStr=ltrim(rtrim(@originlStr))--去掉左右两边的空格
    	set @location=charindex(@split,@originlStr) --分割符号在字符串中第一次出现的位置(索引从1开始计数)
    	set @length=1
    	while @location<>0
    	begin
    		set @start=@location+1
    		set @location=charindex(@split,@originlStr,@start)
    		set @length=@length+1
    	end
    	return @length
    end
    执行用户自定义标量函数:
    select dbo.Fun_GetStrListLeng('001.002.003.004.005','.') --返回5

        创建函数时指定了函数所有者,那么调用的时候也必须指定函数的所有者。(一般都为 dbo)

    调用自定义函数时如果想不传入参数而使用默认值,那么必须使用 default 关键字。如果自定义函数的参数没有默认值,那么会返回 null。

    内联表值函数

    创建内联表值函数的语法:

     create function [函数的所有者].函数名(标量参数 [as] 标量参数类型 [=默认值])
     returns table
     [with {Encryption | Schemabinding }]
     [as]
     return(单个 SELECT 语句,确定返回的表的数据。)

    案例:查询指定学号的学生的选课情况(包括学号、姓名、课程号和成绩),然后调用该函数查询某位学生的选课情况

    create function dbo.Fun_GetList(@学号 char(5))
    returns table
    return(
        select student.sno,student.sname,course.cno,score.degree
        from student,course,score
        where student.sno=score.sno and score.cno=course.cno
        and student.sno=@学号
    )

    调用语法:

    select * from dbo.Fun_GetList('2001')

    多语句表值函数

    创建多语句函数的语法:

     create function [函数的所有者].函数名(标量参数 [as] 标量参数类型 [=默认值])
     returns @表变量 table 表的定义(即列的定义和约束)
     begin
         函数体(即 Transact-SQL 语句)
         return
     end

    案列:将字符串‘001.002.003.004’按照指定分隔符进行分割,然后返回

     alter FUNCTION [dbo].[Fun_SplitStr]
     (
         @originalStr      VARCHAR(8000), --要分割的字符串
         @split varchar(100)  --分隔符号
     )
     RETURNS @temp TABLE(Result VARCHAR(100))
     AS
     BEGIN
         DECLARE @result AS VARCHAR(100);   --定义变量用于接收单个结果
         SET @originalStr = @originalStr + @split ;
         WHILE (@originalStr <> '')
         BEGIN
             SET @result = LEFT(@originalStr, CHARINDEX(@split, @originalStr) -1) ;
             INSERT @temp VALUES(@result) ;
             --STUFF()函数用于删除指定长度的字符,并可以在指定的起点处插入另一组字符。
             SET @originalStr = STUFF(@originalStr, 1, CHARINDEX(@split, @originalStr), '');
         END
         RETURN
     END
    
    
    

    调用方式跟内联函数相同

    适用范围

    1.      只查询,不修改数据库的状态(修改、删除表中记录等)

    2.      结果集需要通过递归等方法得到时,可以使用函数,函数比较灵活

    3.      结果集需要直接被引用时,可以使用函数。需要对结果集进行再加工(指放在select语句中等),可以使用函数,函数可以嵌在select等sql语句中。

    注意事项:

    用户自定义函数不能用于执行一系列改变数据库状态的操作

    在编写自定义函数时需要注意的:

    对于标量函数:

    1.      所有的入参前都必须加@

    2.      create后的返回,单词是returns,而不是return

    3.      returns后面的跟的不是变量,而是返回值的类型,如:int,char等。

    4.      在begin/end语句块中,是return。

    内嵌表值函数:

    1.      只能返回table,所以returns后面一定是TABLE

    2.      AS后没有begin/end,只有一个return语句来返回特定的记录。

    多语句表值函数:

    1.      returns后面直接定义返回的表类型,首先是定义表名,表明前面要加@,然后是关键字TABLE,最后是表的结构。

    2.      在begin/end语句块中,直接将需要返回的结果insert到returns定义的表中就可以了,在最后return时,会将结果返回。

    3.      最后只需要return,return后面不跟任何变量。


    参考:

    https://www.cnblogs.com/Brambling/p/6686947.html

  • 相关阅读:
    别人走的路--2
    win7下80端口被(Pid=4)占用的解决方法
    实习第一天原来是配置环境
    api接口大全
    java计算两个日期之间相隔的天数
    【转】overload与override的区别
    Overload和Override的区别?
    浅析Java中的final关键字
    JAVA中的finalize()方法
    封装
  • 原文地址:https://www.cnblogs.com/studydp/p/9119817.html
Copyright © 2020-2023  润新知