SQL SERVER 之自定义函数（标量、表值）

最新推荐文章于 2024-08-08 10:53:57 发布

Stodger0216

最新推荐文章于 2024-08-08 10:53:57 发布

阅读量500

点赞数

分类专栏： SQL

本文链接：https://blog.csdn.net/Stodger0216/article/details/102859613

版权

SQL 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

我这人不喜欢写一些理论知识，我就简单粗暴直接上代码

创建表请访问：https://blog.csdn.net/Stodger0216/article/details/102840744，我就不再写创建表的代码了

标量：

/*创建标量函数*/
create function GetStuCount(@StuClass nvarchar(30), @StuSex nvarchar(10))
returns int
as
begin
	declare @StuCount int;
	if(@StuClass is not null and @StuClass <> '' and @StuSex is not null and @StuSex <> '')
		begin
			set @StuCount = (select count(*) from tb_student st where st.Sclass = @StuClass and st.Ssex = @StuSex);
		end
	else
		begin
			set @StuCount = 0;
		end
	return @StuCount;
end

/*执行标量函数*/
select dbo.GetStuCount('一班', '男') StuCount;

结果：

表值

/*创建表值函数*/
create function GetStuTotalAndAvg(@StuCourse nvarchar(30))
returns @returnTable table(
	Scourse nvarchar(30),
	Ssex nvarchar(10),
	courseTotal decimal(10,2),
	courseAvg decimal(10,2)
)
as
begin
	if(@StuCourse is not null and @StuCourse <> '')
		begin
			insert into @returnTable select sc.Scourse, st.Ssex, sum(sc.Sscore) courseTotal, avg(sc.Sscore) courseAvg from tb_student st, tb_score sc where sc.Scourse = @StuCourse and sc.Sno = st.Sno group by sc.Scourse,st.Ssex;
		end
	return
end

/*执行表值函数*/
select * from GetStuTotalAndAvg('数学');

结果：