讨论如何开发Transact-SQL与CLR用户定义函数

--创建Transact-SQL用户定义函数
--创建一个用来计算应缴税额的用户定义函数
create function dbo.udf_Tax
(@income money)
returns money
as
begin
 declare @tax money
 set @tax=@income * (case 
                     when (@income >=6000) then 0.35
                     when (@income >=5000) then 0.25
                     when (@income >=4000) then 0.2
                     when (@income >=2000) then 0.15
                     else 0.01 end)
  return @tax
end
go

--查询员工的应缴税额
select 姓名,目前薪水,dbo.udf_Tax(目前薪水) as 应缴税额 from 飞狐工作室 order by 2 desc
go

--删除dbo.udf_Tax函数
drop function dbo.udf_Tax
go

--创建一个能返回重视完整日期的用户定义函数
alter function dbo.udf_FullChineseDate
(@Today datetime)
returns nvarchar(60)
as
begin
 return N'公元 ' + datename(yy,@Today) + N'年 '+ datename(mm,@Today) + N'月 ' + datename(dd,@Today) + N'号 ' + 
 (case when datepart(hh,@Today)>12 then N'下午 ' + cast(datepart(hh,@Today-12) as nvarchar(2)) else N'上午 ' + datename(hh,@Today) end) + N'点 ' +
 datename(mi,@Today) + N'分 ' + datename(ss,@Today) + N'秒,'+ datename(dw,@Today)
end
go

--根据传入的日期来获取中式完整日期
select 现在的时间是 = dbo.udf_FullChineseDate(GETDATE())
go

--删除dbo.udf_FullChineseDate用户定义函数
drop function dbo.udf_FullChineseDate
go

--查询末各部门中介于特定年龄区间的员工数据
alter function dbo.duf_GetPserson
(@youngage int,
 @oldage int,
 @department varchar(10),
 @currentdate datetime
 )
 returns @ResultTable Table(姓名 nvarchar(10) null,性别 nvarchar(2) null,婚姻 nvarchar(4) null,年龄 int null,电话号码 nvarchar(12) null,部门 nvarchar(10) null)
 as
 begin
  if @youngage <18
   insert @ResultTable values(N'未成年','xx',N'不知',18,'0000000000',N'不收未成年')
  else
   --将数据添加到table变量@ResultTable
   insert @ResultTable
     select 姓名,
            性别 = (case 员工性别 when 0 then N'女' when 1 then N'男' end),
            婚姻 = (case 婚姻状况 when 0 then N'未婚' when 1 then N'已婚' end),
            年龄 = datediff(yy,出生日期,@currentdate),电话号码,部门 from 飞狐工作室 where datediff(yy,出生日期,@currentdate) between @youngage and @oldage and 部门 = @department
            return
 end
 go
 
 declare @mydate datetime;
 set @mydate = GETDATE();
 
 --查询用户定义函数dbo.duf_GetPserson所返回的表
 select * from dbo.duf_GetPserson(12,23,'营销部',@mydate)
 
 --删除dbo.duf_GetPserson用户定义函数
 drop function dbo.duf_GetPserson
 
 --创建一个用户定义函数，返回每一个部门中薪水最高或最低的前3名
 create function dbo.udf_GetTop3Salary
 (@TopOrBottom varchar(6) = 'Top')
 --这个Table变更用来保存个部门薪水最高或最低的前3名
 returns @SalaryTableVariable table(姓名 nvarchar(10),目前薪水 money,部门 nvarchar(10))
 as
 begin
   declare @counter int,@Number int;
   declare @Department nvarchar(10);
   
   --变量@counter作为计数器
   set @counter=0;
   
   --该table用来保存数据表中的所有的部门名称
   declare @DepartmentTableVariable table(编号 int identity(1,1) not null,部门 nvarchar(10))
   
   --将部门数据存入table变量中
   insert @DepartmentTableVariable
    select distinct 部门 from 飞狐工作室
    
   --变量@Number用来储存部门的数目
   select @Number=count(*) from @DepartmentTableVariable
   
   --该循环能够依序将各个部门薪水最高或最低的前3名存入table变量中
   while (@counter < @Number)
    begin 
      set @counter = @counter +1
      select @Department=部门 from @DepartmentTableVariable where 编号=@counter
      
      if @TopOrBottom='Top'
        insert @SalaryTableVariable
          select top 3 姓名,目前薪水,部门 from 飞狐工作室
           where 部门=@Department order by 目前薪水 desc
      else
        insert @SalaryTableVariable
          select top 3 姓名,目前薪水,部门 from 飞狐工作室
            where 部门=@Department order by 目前薪水 asc
    end
    return
 end
 go
 
 --查询出各部门薪水最高的前3名
 select * from dbo.udf_GetTop3Salary('Top');
 go
 
 --查询出各部门薪水最低的前3名
 select * from dbo.udf_GetTop3Salary('Bottom')
 go
 
 --删除函数
 drop function dbo.udf_GetTop3Salary
 go
 
--创建一个会返回每一个部门薪水最高或倒数前N名的多表达式表值用户定义函数
create function dbo.udf_GetTopNSalary
(@TopN int=3,@TopOrBottom varchar(6) = 'Top')
--这个Table变量用来保存各部门薪水最高或最低的前几名
returns @SalaryTableVariable table(姓名 nvarchar(10),目前薪水 money,部门 nvarchar(10))
as
begin
 declare @counter int,@Number int,@StartNo int;
 declare @Department nvarchar(10);
 
 --变量@counter作为计数器
 set @counter=0;
 
 --这个table变量用来保存部门数据
 declare @DepartmentTableVariable table
 (编号 int identity(1,1) not null,部门 nvarchar(10))
 
 --将部门数据存入table变量中
 insert @DepartmentTableVariable
   select distinct 部门 from 飞狐工作室
   
 --变量@Number用来储存部门的数目
 select @Number=count(*) from @DepartmentTableVariable
 
 --这个table变量用来保存前100 percent的数据
 declare @Top100PercentVariable table
 (编号 int identity(1,1) not null,姓名 varchar(10),目前薪水 money,部门 varchar(10))
 
 --此循环能够依序将各个部门薪水最高或最低的前几名存入table变量中
 while (@counter < @Number)
 begin
   set @counter=@counter +1;
   select @Department=部门 from @DepartmentTableVariable where 编号 = @counter
   
   if @TopOrBottom='Top'
     insert @Top100PercentVariable
      select 姓名,目前薪水,部门 from 飞狐工作室 where 部门=@Department order by 目前薪水 desc
   else
     insert @Top100PercentVariable
      select 姓名,目前薪水,部门 from 飞狐工作室 where 部门=@Department order by 目前薪水 asc
   
   set @StartNo=@@rowcount;
   
   delete @Top100PercentVariable where 编号 > @TopN
   
   INSERT @SalaryTableVariable
     select 姓名,目前薪水,部门 from @Top100PercentVariable
   
   delete @Top100PercentVariable
   set @TopN = @TopN + @StartNo
 end
 
 return
end
go

--查询出各部门薪水最高的前5名
select * from dbo.udf_GetTopNSalary(5,'Top')
go

--删除用户定义函数
drop function dbo.udf_GetTopNSalary
go

--创建Transact-SQL内嵌套值用户定义函数
--创建一个内嵌表值用户定义函数
create function dbo.udf_EmployeeByDepartmentAge
(@department nvarchar(10),
 @today datetime,
 @lowerage int,
 @higherage int)
returns table
as
return 
(select 姓名,电话号码,家庭住址,部门,datediff(yy,出生日期,@today) as 年龄 from 飞狐工作室 where 部门=@department and datediff(yy,出生日期,@today) between @lowerage and @higherage)
go

declare @Today datetime;
set @Today=GETDATE();

--调用用户定义函数dbo.udf_EmployeeByDepartmentAge
select * from dbo.dbo.udf_EmployeeByDepartmentAge('信息部',@Today,30,40)

--删除函数
drop function dbo.udf_EmployeeByDepartmentAge
go