创建用户自定义函数 SQL

太过爱你忘了你带给我的痛 2022-03-27 08:40 284阅读 0赞

<table style="table-layout:fixed;"> 
 <tbody> 
 <tr> 
 <td> 
 <div> 
 //创建用户自定义函数------标量函数 
 
 <div> 
 create function dbo.bmrs(@bmh as int) 
 returns int 
 as 
 begin 
 declare @bmrs int 
 select @bmrs=count(工号) from 销售人员 where 部门号=@bmh 
 return @bmrs 
 end 
 go 
 exec sp_help&nbsp;&nbsp;销售人员 
 go 
 //标量函数的调用 
 declare @bmrs int 
 select @bmrs=dbo.bmrs(2) 
 select '2号部门人数'=@bmrs 
 go 
 select '2号部门人数 '+convert(varchar(4),dbo.bmrs(3)) 
 select 部门经理,部门名称,实际人数=dbo.bmrs(部门号) from 部门信息 
 go 
 select 部门经理,部门名称,实际人数=dbo.bmrs(部门号) from 部门信息 where 部门号=2 
 //创建内嵌表值函数 
 ----------a.订单号,销售人员=b.姓名,客户=c.姓名,供应商=d.名称,a.数量,a.总金额 
 ------------以部门名称作为输入参数，创建部门订单内嵌表值函数 
 select * from 部门信息 
 select * from 订单信息 
 select * from 客户信息 
 select * from 供应商信息 
 select * from 销售人员 
 go 
 ----------创建内嵌表值函数 
 create function dbo.bmdd(@bmmc as varchar(10)) 
 returns table 
 as 
 return( select a.订单号,销售人员=b.姓名,客户=c.姓名,供应商=d.名称,a.总金额,a.数量 
 from 订单信息 as a inner join 销售人员 as b on a.销售工号=b.工号 inner join 客户信息 as c 
 on a.客户编号=c.员工编号 inner join 供应商信息 as d on a.货品编码=d.编码 inner join 部门信息 as e 
 on b.部门号=e.部门号 where 部门名称=e.部门名称) 
 go 
 ----------内嵌表值函数的调用 
 select * from dbo.bmdd('销售部') 
 --------------创建内嵌表值函数的语法格式 
 create function 函数所有者.函数名称(标量参数 as 标量参数数据类型) 
 returns table 
 as 
 return (select语句) 
 -----------创建视图的语法格式 
 create view 视图名 as select语句 
 ----------------用create function语句创建函数。 
 函数名在数据库汇总必须唯一，用户定义函数可以有输入参数并返回值，但是没有输出参数。 
 create function 所有者.函数名(标量参数 as 标量数据类型) 
 returns 标量参数数据类型 
 as 
 begin 
 函数体 
 return 变量//标量表达式 
 end 
 ----------多语句表值函数的返回值是表，有函数体，在函数体中可以使用表变量。 
 创建多语句表值函数语法格式： 
 create function [所有者名称.]函数名称(@参数名称 as 标量数据类型) 
 returns @表变量 table 表的定义 
 as 
 begin 
 函数体 
 return 
 end 
 go 
 说明：表(table)变量是一种特殊的数据类型，可用于函数、存储过程和批处理中，用于存储结果集以供以后处理。该数据类型主要用于临时存储一组行，这些行将作为表值函数的结果集返回。 
 table变量的行为类似于局部变量，其作用域为声明该变量的函数、存储过程或批处理，其生命周期在定义table变量的 
 函数、存储过程呢过和批处理结束时将自动清楚table变量。 
 表(table)变量的声明语法：declare @local_variable table(表的定义) 
 /*根据订单信息表查询得出每个销售人员所开的订单数。查询的结果包括销售人 
 员工号和订单数。下面，我们采用表变量的方式将查询的结果集保存下来，以便程序使用。*/ 
 declare @ordernum 
 table(销售工号 int, 
 订单数 int) 
 ---使用insert....select语句向表变量中插入记录 
 insert @ordernum 
 select 销售工号,订单数=count(销售工号) from 订单信息 group by 销售工号 
 select * from @ordernum 
 go 
 /*在market数据库中，创建一个多语句表值函数，它可以查询指 
 定部门每个销售人员的订单数，该函数接收输入的部门号，通过查询“订单信息”表返回销售人员的工号、姓名和订单数。*/ 
 ---创建多语句表值函数 
 create function dbo.info(@bmh as int) 
 returns @xsry table(工号 int primary key, 
 姓名 varchar(10), 
 订单数 int) 
 as 
 begin 
 declare @ordernum 
 table(销售工号 int,订单数 int) 
 insert @ordernum select 销售工号,订单数=count(销售工号) from 订单信息 group by 销售工号 
 insert @xsry select a.工号,a.姓名,b.订单数 from 销售人员 as a left join @ordernum as b on a.工号=b.销售工号 
 where a.部门号=@bmh 
 return 
 end 
 go 
 </div> 
 </div></td> 
 </tr> 
 </tbody> 
</table>