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 语句)
     --注，不可以有修改数据的操作
     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

注意：

函数一般用作查询使用，不能用于数据修改；

如果 内嵌表值函数可达到目的，则尽量不要使用多语句表值函数