sql server function

szs

一、.返回数值的用户自定义函数（标量函数）
create function 函数名称
(
?参数1 类型定义1,
?参数2 类型定义2,
?...
)
returns 返回值类型
with encryption
as
begin
?函数体代码?
?return 返回值
end
--/////
create function fun001()
returns varchar(20)
as
begin
?return 'Welcome to China'
end
--select getdate()
select dbo.fun001()

--jisuan(n)=1+2+3+...+n
create function jisuan(@n int)
returns int
as
begin
?declare @s int,@i int
?set @s=0
?set @i=1
?while (@i<=@n)
?begin
set @s=@s+@i
set @i=@i+1
?end
?return @s
end

select dbo.jisuan(100)
select dbo.jisuan(10)

--统计城市获取人数
create function fun002(@city varchar(20))
returns int
as
begin
?declare @count int
?select @count=count(*) from student
where city=@city
?return @count
end

select dbo.fun002('北京')

select distinct city from student
select distinct city,dbo.fun002(city) as num from student
print dbo.fun002('北京')

--(1)通过函数完成：返回两个整数的最大值
create function fun003(@a int,@b int)
returns int
as
begin
declare @s int
?if (@a>@b)
?set @s=@a
?else
?set @s=@b
return @s
end
--函数中最后一条语句必须是返回语句。
select dbo.fun003(34,5656)
select dbo.fun003(34,3)
--(2)通过函数完成：返回三个整数的最大值
/*
a b c
--fun003(fun003(a,b),c)
a>b
?a c
else
?b c
*/
create function fun004(@a int,@b int,@c int)
returns int
as
begin?
?return dbo.fun003(dbo.fun003(@a,@b),@c)
end
select dbo.fun004(23,5,2323)
select dbo.fun004(23,5232,23)
select dbo.fun004(2334,-5232,23)
create function fun005(@a int,@b int,@c int)
returns int
as
begin
?declare @s int
?if (@a > @b)
?begin
--返回a c 最大值
if (@a > @c)
set @s=@a
else
set @s=@c
?end
?else
?begin
--返回b c最大值
if (@b > @c)
set @s=@b
else
set @s=@c
?end
return @s
end

select dbo.fun005(23,5,2323)
select dbo.fun005(23,5232,23)
select dbo.fun005(2334,-5232,23)
--通过姓名统计总分

create function fun006(@name varchar(20))
returns numeric(10,2)
with encryption
as
begin
declare @s numeric(10,2)
if exists(select * from student where name=@name)
begin
?if exists(select * from relation where stu_no in(select stu_no from student where name=@name))
?begin
select @s=sum(mark) from relation where stu_no in (select stu_no from student where name=@name)
?end
?else
?begin
set @s=-2
?end
end
else
begin
?set @s=-1
end
return @s?
end
?
select dbo.fun006('abc')
select dbo.fun006('张三')
select dbo.fun006('陈刚')
select name,dbo.fun006(name) as summark from student
select name,dbo.fun006(name) as summark from student
where dbo.fun006(name) >= 0
--根据课程名称统计平均分
create function fun111(@c_name varchar(20))
returns numeric(5,2)
with encryption
as
begin
declare @s numeric(5,2)
?if exists(select * from course where c_name=@c_name)
?begin
if exists()
begin
end
else
begin
nd
?end
?else
?begin
set @s=-1
?end
return @s
end
?

二、内联（单语句）的返回表的用户自定义函数
create function 函数名称
(
?参数1 类型定义1,
?参数2 类型定义2,
?...
)
returns table
with encryption
as
return select语句

--按照城市获取学生信息
create function fun007(@city varchar(20))
returns table
with encryption
as
return
select * from student where city=@city
select * from dbo.fun007('北京')
select * from fun007('北京')
?
--按照姓名统计此人选修的课程基本信息
create function fun008(@name varchar(20))
returns table
with encryption
as
return
select * from course where c_no in(
select c_no from relation where stu_no in(
select stu_no from student where name=@name))

select * from fun008('陈刚')
select * from fun008('张三')
select * from fun008('abc')

--按照课程名称统计学修这门课程的学生信息
create function fun113(@c_name varchar(20))
returns table
with encryption
as
return
select * from student where stu_no in(
select stu_no from relation where c_no in(select c_no from course where c_name=@c_name))
?
select * from fun113('网站设计')
?
三、.多语句的返回表的用户自定义函数
create function 函数名称
(
?参数1 类型定义1,
?参数2 类型定义2,
?...
)
returns @return_variable table (定义)
[with encryption]
[as]
begin
function_body
return
end
--按照城市统计给城市的学生的姓名 性别 和城市

create function fun009(@city varchar(20))
returns @tab table(
name varchar(20),
sex char(2),
city varchar(20)
)
with encryption
as
begin
?insert into @tab(name,sex,city) select name,sex,city from student where city=@city
?return
end

select * from fun009('北京')
?

--统计全体学生的姓名和总分
create function fun010()
returns @tab table(
name varchar(20),
summark numeric(10,2)
)
with encryption
as
begin
declare @name varchar(20)
declare @summark numeric(10,2)
declare cur cursor for select name from student where stu_no in(select stu_no from relation)
open cur
fetch cur into @name
while (@@fetch_status=0)
begin
?select @summark=sum(mark) from relation where stu_no in (
?select stu_no from student where name=@name?)
?insert into @tab(name,summark) values(@name,@summark)
?fetch cur into @name
end
close cur
deallocate cur
return
end

select * from fun010()