SQL函数

函数是用于对封装经常进行的逻辑的子例程

类型：(标量值函数、标值函数、聚合函数)

标量函数（返回当个数据值）

内嵌标值函数（返回一个结果集)

多语句标值函数（返回一个结果集）

标量函数的创建

Create Function 函数名(参数名,数据类型)

Returns 数据类型

as

begin

主题语句(Return

end

标量值函数：

标量值型自定义函数的最大特点是返回一个单值，即标量值，需要注意的是，在创建一个标量值函数是需要显示第使用begin 和end 关键字来定义函数体

问题：根据指定的类别ID,获取Products表中的所有给类别产品的种类

use NorthWind

go

if exists(select * from dbo.sysobjects where id=object_id(N'[dbo].[GetProductsBySortID]') and xtype in(N'FN',N'TF',N'AF'))

/*FN--标量函数，TF---标值函数,AF---聚合函数*/

drop function [dbo].[GetProductsBySortID]

go

create function dbo.GetProductsBySortID(@sortid int)

retunrs int

as

begin

reutrn(select count(*) from dbo.procucts where ProductSortID=@sortid)

end

end

调用:select dbo.GetProductsBySortID(1)

 

 

 

 

use NorthWind

go

if exists(select * from dbo.sysobjects where id=object_id(N'[dbo].[FunMergeCharField]') and xtype in(N'FN',N'TF',N'IF',N'AF'))

drop function [dbo].[FunMergeCharField]

go

create function dbo.FunMergeCharField(@ProductSortid int)

returns nvarchar(4000)

as

being

declare @ProductNames Nvarchar(4000)

set @ProductNames=''

select @ProductNames=@ProductNames+','+dbo.Products.ProductName from dbo.Products where dbo.Products.ProductSortID=@ProductsSortID

return(substring(@ProductNames,2,4000))

end

go

调用：

select ProductSortID as 产品类别ID,dbo.FunMergeCharField(ProductSortID) as 所有产品名称 from dbo.Products group by ProductSortID

 

表值函数

如果returns 子句指定Table，则函数为表值函数

表值函数分为内嵌表值函数（行内函数)和多语句表值函数

  A、如果returns子句指定的Table不附带列的列表，则该函数为内嵌表值函数（内嵌表值函数只有一个select语句)

B、如果returns子句指定的Table类型带有列及其数据类型，则该函数是多语句表值函数（注意下面实例中的returns部分）

多语句函数的主体中运行使用以下语句，未在下面的列表中列出的语句不能用在函数主体中

1、赋值语句

2、控制流语句

3、DECLARE语句，该语句定义函数局部的数据变量和游标

4、SELECT语句，该语句包含带有表达式的选择列表，其中的表达式将值赋予函数的局部变量

5、游标操作，该操作引用在函数中声明、打开、关闭和释放的局部游标，只允许使用以into子句想局部变量赋值的fetch语句；不允许使用将数据返回到客户端的Fetch语句

6、INSERT、UPDATE和DELETE语句，这些语句修改函数的局部table变量

7、Execute语句调用扩展存储过程

内嵌表值函数实例

user NorthWind

go

if exists(select * from dbo.sysobjects where id=object_id(N'[dbo].[GetAllProducts]') and xtype IN (N'FN',N'TF',N'IF',N'AF'))

/*FN--标量函数,TF---表值函数,IF---内联表函数,AF---聚合函数*/

/*上面如果确切知道是一个内联表值函数，则可以使用 if object_id(N'dbo.GetAllProducts',N'IF') in not null*/

drop function [dbo].[GetAllProducts]

go

create function dbo.GetAllProducts()

returns table

as

return select * from Products

go

调用:select * from dbo.getAllProducts()

多语句表值函数实例

根据指定的产品类型ID查询出相关的信息

..........................

create function dbo.GetProductsBySortID(@ProductSortID int)

return @myTable Table (ID int Primary key,ProductName Nvarchar(50) not null,SortName nvarchar(50))

as

begin

insert into @myTable select dbo.Products.ID,dbo.Products.ProductName,dbo.ProductSort.SortName from dbo.Products left outer join dbo.ProductSort on dbo.Products.ProductSortID=dbo.ProductSort.ID where dbo.Products.ProductSortID=@ProductSortID

return

end

go

调用:select * from dbo.GetProductsBySortID(6)

 

聚合函数

可以理解成对结果的某列上的所有数据进行操作并返回一个唯一的结果的一个函数，例如sum(ID)是对ID列求和，AVG(price)是对price列求平均

 

系统函数

除了上面三种用户自定义的函数以外，SQL SERVER还有系统函数，一下列举了部分常用系统函数。

日期函数

GetDate() 返回但却系统日期和时间

DateName(日期元素，日期)返回指定日期的名称，返回字符串

DatePart(日期元素，日期) 返回表示指定日期的指定日期部分的整数

DateDiff(日期元素,日期1，日期2)返回两个日期间的差值并转换为指定日期元素的形式

Year(日期) 返回年份(整数)

Month(日期) 返回月份(整数)

Day(日期) 返回天(整数)

系统综合函数

Cast(表达式 as 数据类型） 将表达式显示装换为另一中数据类型

Convert(数据类型,表达式) 将表达式装换为某一数据类型

安全函数

user 返回当前用户的数据库用户名

User_id(['User']) 返回数据库用户的表示号

User_Name([id]) 返回数据库的用户名

游标函数：游标函数用来返回游标信息

@@Cursor_rows 返回所打开的游标中的记录行数

@@Fetch_Status 返回被Fetch语句执行的最后的游标的状态

配置函数

@@Max_Connection 返回SQL Server实例允许同时进行最大用户连接数

@@Remserver返回远程SQL SERVER数据库服务器在登录记录中显示的名称

@@ServerName返回运行SQL SERVER的本地服务器的名称

@@ServiceName返回SQL SERVER重在其下允许的注册表项的名称，若当前实例为默认实例，则@@ServiceName 返回MSSqlServer

@@SPID 返回当前用户进程的会话id

@@Version 返回当前的SQL SERVER安装的版本，日期和处理器类型

 

函数与存储过程的区别

存储过程

1、功能强大，限制少

2、不能直接引用返回值

3、使用select语句返回记录集

子定义函数：

1、诸多显示，有许多语句不能使用，许多功能不能实现

2、可以直接引用返回值

3、用表变量返回记录集

注意：

子定义函数有且只有一个返回值，就像普通的函数一样，可以直接在表达式中嵌入调用，存储过程没有返回值，也可以有任意个输出参数，必须单独调用，存储过程中可以直接使用字定义函数