select * from authors
--多语句表值函数
use pubs
go
create function fuc_authors
()
returns @table_author_temp table
(
au_id smallint,
ai_lname varchar(40)
)
as
begin
insert into @table_author_temp
select au_id,au_lname from authors
return
end
drop function fuc_authors
exec sp_helptext fuc_authors
alter function fuc_authors
(
@fname varchar(20)='Dean'
)
returns @table_author_temp table
(
au_id varchar(40),
au_lname varchar(40)
)
as
begin
insert into @table_author_temp
select au_id,au_lname from authors where au_fname=@fname
return
end
go
select * from fuc_authors(default)
--内联表值函数
create function inline_fuc_authors
(
@fname varchar(20)='Dean'
)
returns table
as
return
(
select au_id,au_lname from authors where au_fname=@fname
)
go
select * from dbo.inline_fuc_authors(default)
drop function inline_fuc_authors
--标量值函数
create function scalar_fuc_authors
(
@fname varchar(40)='Dean'
)
returns varchar
as
begin
declare @ret varchar(40)
set @ret=(select au_id from authors where au_fname=@fname)
return @ret
end
go
alter function scalar_fuc_authors
(
@fname varchar(40)='Dean'
)
returns varchar(40)
as
begin
declare @ret varchar(40)
set @ret=(select au_id from authors where au_fname=@fname)
return @ret
end
go
--此处执行一定要添加架构名‘dbo.’
select dbo.scalar_fuc_authors('White')
表值函数和标值函数使用脚本范例
最新推荐文章于 2020-11-23 09:42:29 发布