T-SQL编程 —— 用户自定义函数(内嵌表值函数)

内嵌表值函数

接上 《T-SQL编程 —— 用户自定义函数(标量函数)》

http://www.cnblogs.com/viusuangio/p/6212072.html

 

  内嵌表值函数可以用于实现参数化视图(查询),例如有一个查询其定义语句如下:

1 use Student
2 GO
3 --查询笔试成绩几个的同学
4 select s.stuno ,s.stuname,s.brithday,e.written,e.lab
5 from StuInfo s left outer join exam e
6 on s.stuno = e.stuno
7 where e.written >=60

  若希望设计更加通用的程序,让用户能够指定查询内容,可将 where e.written >= 60 替换为 where e.written >=@para, @para用于传递参数,但是视图查询不支持在wherea语句中指定搜索条件参数,为解决这样的一个问题,可以使用内嵌值函数。

  内嵌表值函数的定义:

create function [owner_name.] function_name
([{@parameter_name [as] scalar_parameter_data_type [default]}[,n..]])
returns table [with encryption] [as]
return [(select_stmt)]

  其中各个选项的含义如下:

  • owner_name:数据库所有者名
  • function_name:用户定义函数名,函数名必须符合标识符规则,对其所有者来说,该用户在数据库中必须是唯一的。
  • @parameter_name:用户定义函数的形参名, create function 语句中可以申明一个或多个参数,用@符号作为第一个字符来指定形参名 ,每个函数的参数局部作用于该函数。
  • scalar_parameter_data_type:参数的数据类型,可为系统支持的基本标量类型,不能为 timestamp 类型、用户定义数据类型、非标量类型(如 cursor tabel)
  • default:指定默认值。
  • returns table:此句仅包含关键字table ,表示此函数返回一个表。
  • with 子句指出了创建函数的选项,如果指出了 encryption 参数,则创建的函数是被加密的,函数定义的文本将以不可读的形式存储在 syscomments 表中,任何人都不能查看该函数的定义,包括函数的创建者和系统管理员。
  • return [(select_stmt)]:内嵌表值函数的函数体仅有一个return语句,并通过参数 select_stmt 指定的 select 语句返回内嵌值。

  例如:在student数据库(假设已经存在)创建一个查询(一般是一个视图),如果在此查询的基础上定义一个内嵌表值函数。

 1 /*创建视图查询,但不能带参数(如下面注释句)*/
 2 create view Veam as
 3 select s.stuno,s.stuname,s.brithday,e.written,e.lab
 4 from StuInfo s left outer join exam e
 5 on s.stuno = e.stuno
 6 --where writtrn >=60
 7 
 8 
 9 
10 /*改用内嵌表函数可以解决,传入参数,返回带参数的查询*/
11 create function getExams(@written float)
12 returns table as
13 return(
14     select s.stuno,s.stuname,s.brithday,e.written,e.lab
15     from StuInfo s left outer join exam e
16     on s.stuno=e.stuno
17     where written >= @written
18 )

  内嵌表值函数的调用:

  内嵌表值函数只能通过 select 语句调用,内嵌表值函数调用的时候,可以仅仅只使用函数名。

  例如,调用 getExams()函数,查询笔试成绩大于等于70的记录。

 

1 select * from dbo.getExams(70) 

 

posted @ 2016-12-22 18:25  木栩  阅读(2874)  评论(0编辑  收藏  举报