012 数据库学习笔记--自定义函数

if object_id('GetQuaStatus','fn') is not null drop function GetQuaStatus
go
create function [dbo].GetQuaStatus(@TableStudiesQuaStatus varchar(10), @QuaStatus varchar(10))
returns nvarchar(10)
as
begin
 declare @res varchar(10)
 select @res = case 
                    when @TableStudiesQuaStatus = 0 then '未抽样'
                    when @TableStudiesQuaStatus = 1 then '待分配'
                    when @QuaStatus = 2 then '待评审'   
                    when @QuaStatus = 3 then '已评审'   
                    when @QuaStatus = 4 then '处理中'   
end
  return @res
end
go
 
 
if object_id('GetPassRate2','fn') is not null drop function GetPassRate2
go
create function [dbo].[GetPassRate2]( @num1 int, @num2 int)
returns nvarchar(10)
as
begin
 declare @res varchar(10)
 select @res = case when @num1 is null or @num2 is null then null
                    --when @num1 = 0 then '0%'
                    when @num1 = 0 then '100%'
                    when @num2 = 0 then null
                else concat(cast(cast(@num1  * 1.0 /@num2 as decimal(18,4)) * 100  as decimal(18,2)),'%')   end
                --else concat(cast(cast(@num1  * 1.0 /@num2 as float) * 100  as float),'%')   end
  return @res
end
go
select [dbo].GetPassRate2(1,2) as passrate,[dbo].GetPassRate2(0,1) as passrate,
[dbo].GetPassRate2(3,7) as passrate,[dbo].GetPassRate2(0,0) as passrate
go

自定义函数：根据自己的需要，自定义一些函数

分类：标量函数、内嵌表值函数、多声明表值函数

标量函数：
- 对单一值的操作，返回单一值;
- 包含 begin end
- 创建的时候，指定了函数所有体，调用时也必须指定函数所有者
- 调用时，如果函数中指定了默认值，调用的时候，可使用默认值default代替
- 在语法上 returns 之后为返回值类型，含begin end ；最后return 返回值
内嵌表值函数：
- 功能上相当于一个参数化的试图，返回的是一个表；
- 没有 begin end 包括起来
- returns 返回类型，只能是table
- as后面没有begin end ;只能是return (select 语句)
多声明表值函数：也称多语句表值函数
- 返回的也是一个表，包含 begin 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后面不跟任何变量。

标量函数：

--语法
create function [函数的所有者].函数名(标量参数 [as] 标量参数类型 [=默认值])
 returns 标量返回值类型
 begin
     函数体(即 Transact-SQL 语句)<br>     --注，不可以有修改数据的操作
     return 变量/标量表达式
 end

示例：　

--------------------标量函数--------------------
--示例一：按照指定分隔符进行分割，返回分割后的个数
create function GetStrCountBySplitchar
(
    @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
go
select dbo.GetStrCountBySplitchar('腹部彩超,腹部B超,腹部平扫',',') as 检查项目数量 --返回检查项目个数
go
 
--示例二  根据学生名称获取学生信息
--CAST(@student_age AS varchar)
--CONVERT(varchar,2) 
--标量函数，返回单一值 查询使用
create function GetStudentByName
(
    @student_name varchar(100) 
)
returns varchar(100)
-- with encryption
as
begin
    declare @student_sex varchar(100), @student_age int,@stuInfo varchar(200)
    select @student_sex = student_sex,@student_age=student_age from student where student_name=@student_name
    --select赋值  等价于set 
    select @stuInfo = @student_name +','+@student_sex+','+ CONVERT(varchar,@student_age)-- CAST(@student_age AS varchar)
    return @stuInfo 
end
go
 
--select * from [dbo].[student]
--调用自定义函数
select [dbo].[GetStudentByName]('kunkun') as 学生信息
go
 
--示例三 使用默认值
create function GetStudentByNameOrDefault
(
    @student_name varchar(100) ='kunkun'
)
returns varchar(100)
-- with encryption
as
begin
    declare @student_sex varchar(100), @student_age int,@stuInfo varchar(200)
    select @student_sex = student_sex,@student_age=student_age from student where student_name=@student_name
    --select赋值  等价于set 
    select @stuInfo = @student_name +','+@student_sex+','+ CONVERT(varchar,@student_age)-- CAST(@student_age AS varchar)
    return @stuInfo 
end
go
 
select * from [dbo].[student]
--调用自定义函数 + 使用默认值调用
select [dbo].GetStudentByNameOrDefault(default) as 学生信息

内嵌表值函数：

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

示例：

--------------------内联表值函数(类似于视图)--------------------
create function GetStudentNameByName(@student_name varchar(100))
returns table<br>as -- 可省略as
return(
    select student_id, student_name, student_age, student_sex
    from student where student_name = @student_name
)
go
--调用自定义函数 
select * from dbo.GetStudentNameByName('kunkun')

多声明表值函数：

多语句表值函数跟内联表值函数都是表值函数，它们返回的结果都是Table类型。多语句表值函数通过多条语句来创建Table类型的数据。

这里不同于内联表值函数，内联表值函数的返回结果是由函数体内的SELECT语句来决定。而多语句表值函数，则是需要指定具体的Table类型的结构。

也就是说返回的Table已经定义好要哪些字段返回。所以它能够支持多条语句的执行来创建Table数据。

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

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 
END

示例：

--------------------多语句表值函数--------------------
create function GetStudentByAge
(
    @student_age int
)
returns @tempTable table
(
    id int not null primary key,
    name varchar(100),
    age int,
    sex varchar(10)
)
as
begin
-------------------T-SQL 函数体-------------------
insert into @tempTable(id,name,age,sex) select student_id, student_name, student_age, student_sex from student where student_age = @student_age
return
 
end
go
 
--调用自定义函数 
select * from dbo.GetStudentByAge(18)
go