sql server 数据库 function用法

http://liujunbo107.blog.163.com/blog/static/4354124020101019114644512/

一、.返回数值的用户自定义函数（标量函数）
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()