SQL笔记07

函数

--创建函数
create function fun1(@cno varchar(13))
returns int
as
begin
	declare @avgscore int
	select @avgscore=avg(grade) from sc where cno=@cno
	return @avgscore
end
--dbo.xxx
select dbo.fun1('20201')

alter function fun2(@mno int)
returns @snoSname table(
	sno varchar(13),
	sname varchar(30)
)
as
begin
	declare @sno varchar(13)
	declare @sname varchar(30)
	select @sno=sno,@sname=sname from stu where mno=@mno
	insert into @snoSname(sno,sname) select sno,sname from stu where mno=@mno
	return 
end
select * from dbo.fun2(1)

create function fun3(@mno int)
returns @mSc table(
	sno varchar(13),
	cno varchar(13),
	grade decimal(5,2)
)
as
begin	
	insert into @mSc select stu.sno,cno,grade from major,stu,sc where major.mno=stu.mno and  sc.sno=stu.sno and stu.mno=@mno
	return 
end
select * from fun3(1)