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 语句)
--注,不可以有修改数据的操作 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
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
注:删除函数
--删除函数 drop function 函数名称 drop function GetStudentByAge
注意:
函数一般用作查询使用,不能用于数据修改;
如果 内嵌表值函数可达到目的,则尽量不要使用多语句表值函数