【SQL】自定义函数（标量函数、表值函数）

•SQL自定义函数：标量函数和表值函数

–标量函数：标量函数是对单一值操作，返回单一值

–表值函数：返回表类型的数据。表值函数的返回类型是一个表，因此，可以像使用表一样使用表值函数。

标量函数

CREATE FUNCTION dbo.F3_GetMax( @a INT, --函数的两个参数 @b INT ) RETURNS INT --返回类型为INT AS BEGIN DECLARE @max INT; IF(@a>=@b) BEGIN SET @max = @a; END ELSE BEGIN SET @max = @b END RETURN @max; --最后return返回@max中的值 END --调用函数 SELECT dbo.F3_GetMax(2,3);

表值函数

CREATE FUNCTION F3_GetEmployeeSalary( @EmployeeID VARCHAR(50) --参数 ) RETURNS TABLE --返回类型为表 AS RETURN( SELECT * FROM dbo.TEmployee WHERE EmployeeID = @EmployeeID --通过一条sql查询语句获取表中数据 )   --函数调用 SELECT * FROM F3_GetEmployeeSalary('1')

题目一：用标量函数实现可以通过班级、性别查询总人数

实现代码：

if exists(select * from sysobjects where name='dbo.peopleCount') drop function dbo.peopleCount go CREATE FUNCTION dbo.peopleCount( @class nvarchar(100), --函数的两个参数 @sex nvarchar(100) ) RETURNS INT --返回类型为INT AS BEGIN DECLARE @peopleCount INT; select @peopleCount = count(*) from student s1 where s1.Sclass=@class and s1.Ssex=@sex; RETURN @peopleCount; --最后return返回@max中的值 END select dbo.peopleCount('一班','男') 总人数;

 运行结果：

题目二：用表值实现可以通过科目，查询该科目下不同性别学生的总分和平均分

实现代码：

if exists(select * from sysobjects where name='GetTotalAndAvg') drop function GetTotalAndAvg go CREATE FUNCTION GetTotalAndAvg( @subject VARCHAR(50) --参数 ) RETURNS TABLE --返回类型为表 AS RETURN( SELECT @subject 课程,s1.Ssex 性别,sum(s2.Sgrade)总分,avg(s2.Sgrade)平均分 FROM student s1 join score s2 on s1.Sno=s2.Sno WHERE s2.Ssubject = @subject group by s1.Ssex --通过一条sql查询语句获取表中数据 ) --函数调用 SELECT * FROM dbo.GetTotalAndAvg('语文')

 运行结果：