mssql中的标量值函数和表函数的用法

标量函数　　 所谓标量函数指的就是此函数只返回一个值。表值函数返回值是一个表。聚合函数是在select语句中使用的，用来聚合一个结果集， 类似于Sum()或是Count()等内置的函数，而且真正的自定义聚合函数目前只能用CLR来实现。 创建标量值函数: create FUNCTION [dbo].[mytest]() RETURNS varchar(60) AS BEGIN declare @uu varchar(60) set @uu='中国历史' return @uu END 调用方法: print dbo.mytest() 带参数的: create FUNCTION mytest2( @u int ) RETURNS varchar(60) AS BEGIN declare @uu varchar(60) set @uu=case when @u=1 then '中国历史111' when @u=0 then '中国历史000' end return @uu END 调用方法1: print dbo.mytest2(1) 调用方法2: declare @ui varchar(30) select @ui=dbo.mytest2(1) select @ui as '测试' 参考: 6.5 用户定义的函数用户自己定义的函数可以分为如下三个类别： （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')