sql 表值函数与标量值函数

写sql存储过程经常需要调用一些函数来使处理过程更加合理,也可以使函数复用性更强,不过在写sql函数的时候可能会发现,有些函数是在表值函数下写的有些是在标量值下写的,区别是表值函数只能返回一个表,标量值函数可以返回基类型。

举个例子,当用户删除一个节点的时候,是需要将当前节点下的所有子节点都删掉,如果程序只传一个当前节点,那就需要写一个函数来得到当前节点下的所有子节点,这些子节点的信息就可以放到一个表中返回。

ALTER FUNCTION testGetSubNodes

(

-- Add the parameters for the function here

@nodeId int

)

RETURNS

@t TABLE

(

-- Add the column definitions for the TABLE variable here

id bigint identity(1,1) not null,

nodeIds int ,

nodeName varchar(500)

)

AS

BEGIN

-- Fill the table variable with the rows for your result set

insert into @t values(@nodeId,'header');

while exists(

select nodeid from dbo.Tree where parentid

in (select nodeIds from @t) and nodeid not in(select nodeIds from @t))

begin

insert into @t select nodeid, nodename from dbo.Tree where parentid

in (select nodeIds from @t)

end

RETURN

END

这个函数的主要功能就是返回当前节点下的所有子节点,在存储过程中写

select * from testGetSubNodes(nodeId)就可以返回表中的数据了。

再写一个标量值函数

ALTER FUNCTION [dbo].[testGetSubNodes_]

(

@nodeId int

)

RETURNS int

AS

BEGIN

declare @nodeCount int

select @nodeCount=5 from MenuTree

return @nodeCount

END

这个函数很简单返回一个整型值,然后就可以在存储过程中调用了,不过调用的方式有所不同,象上面的表值函数调用是不需要所有者的,只要写函数名称就可以,对于标量值函数来说,是需要加上所有者的,比如所有者是dbo

select dbo.testGetSubNodes_,这样就可以返回5,如果不加dbo,那sql会不认识这个函数。

Sql server 的表值函数是返回一个Table类型,table类型相当与一张存储在内存中的一张虚拟表。

实现表值函数很简单:
下面是一个不带输入参数的表值函数

create function tvpoints()
returns table
as
return
(
select *from tb_users
);

这个表值函数数查询所有用户表的数据

对于多语句表值函数,在 BEGIN...END 语句块中定义的函数体包含一系列 Transact-SQL 语句,

这些语句可生成行并将其插入将返回的表中。

以下示例创建了一个表值函数.

create function tvpoints()
returns @pointstable (xfloat,
y
float)
as begin
insert @pointsvalues(1,2);
insert @pointsvalues(3,4);
return;
end

查询表值函数跟查询普通表一样
select * from tvpoints()
返回的是一张表

带输入参数的表值函数

create function tvpoints2(@xAS int,@yas int)
returns @pointstable (xfloat,
y
float)
as begin
insert @pointsvalues(@x,@y);
return;
end
上一篇:51nod算法马拉松28-a


下一篇:《CLR.via.C#第三版》第二部分第13章节 接口 读书笔记(七)