sql函数

用户自己定义的函数可以分为如下三个类别：

（1）       返回值为可更新表的函数，这样的函数称为内嵌表值函数；

（2）       返回不可更新表的函数，这样的函数称为多语句表值函数；

（3）       返回标量值的函数

注意：用户自定义函数不支持输出函数。

6.5.1 标量函数的定义

Create function owname_name, function_name

(@parameter_name as data_type,….)

Returns data_type

[with encryption | schemabinding.]

[as]

Begin

       …

End

例子：/*计算全体学生某们功课的平均成绩*/

create function average

(@cnum char(20))

returns int

as

begin      

       declare @aver int

       select @aver=

       (select avg(score) from xs_kc where kc_id=@cnum group by kc_id)

       return @aver

end

6.5.2标量函数的调用

（1）在select语句中调用

例子：/*在select语句中调用*/

declare @course1 char(20)

declare @aver1 int

select @course1='101'

select @aver1=dbo.average(@course1)

select @aver1 as '101课程的平均成绩'

（2）利用EXEC语句执行

例子：/*exec语句中调用*/

declare @course1 char(20)

declare @aver1 int

select @course1='101'

exec @aver1=dbo.average @course1

select @aver1 as '101课程的平均成绩'

例子：/*新建course表*/

use xscj

create table course

(cno int

,cname nchar(20)

,credit int

,aver as (dbo.average(cno))

)

6.5.3 内嵌表值函数

内嵌表值函数可用于实现参数化视图。

/*内嵌表值函数的定义*/

create function fn_view1

(@para nvarchar(30))

returns table

as

return

       (

       select stu_id,stu_name from xscj.dbo.xs where stu_major=@para

       )

/*内嵌表值函数的调用*/

select * from fn_view1(N'计算机')

6.5.4 多语句表值函数

内嵌表值函数和多语句表值函数都返回表，二者不同之处在于：内嵌表值函数没有函数主体，返回的表是单个select语句的结果集；而多语句表值函数在begin…end块中定义的函数主体包含T-SQL语句，这些语句可生成行并将行插入至表中，最后返回表。

例子：/*创建返回table的函数，通过学号作为实参调用该函数，可显示该学生各门功课的成绩和学分*/

Create function score_table

(@student_id char(6))

returns @score table

(xs_id char(6)

,xs_name char(8)

,kc_name char(16)

,cj tinyint

,xf tinyint

)

as

begin

       insert @score

       select s.stu_id,s.stu_name,p.kc_name,p.kc_credit,o.score

       from xs as s

       inner join xs_kc as o on (s.stu_id=o.stu_id)

       inner join kc as p on (o.kc_id=p.kc_id)

       where s.stu_id=@student_id

       return

end

/*多语句表值函数的调用*/

select * from score_table('001103')