表值函数和标值函数使用脚本范例

来源：互联网发布：php考试系统手机端编辑：程序博客网时间：2024/04/30 20:02

select * from authors--多语句表值函数use pubsgocreate function fuc_authors()returns @table_author_temp table(    au_id smallint,    ai_lname varchar(40))asbegin    insert into @table_author_temp        select au_id,au_lname from authors    returnenddrop function fuc_authorsexec sp_helptext fuc_authorsalter 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  endgo  select * from fuc_authors(default)--内联表值函数create function inline_fuc_authors(    @fname varchar(20)='Dean')returns tableasreturn(    select au_id,au_lname from authors where au_fname=@fname)goselect * from dbo.inline_fuc_authors(default)drop function inline_fuc_authors--标量值函数create function scalar_fuc_authors(    @fname varchar(40)='Dean')returns varcharasbegin    declare @ret varchar(40)    set @ret=(select au_id from authors where au_fname=@fname)    return @retendgoalter function scalar_fuc_authors(    @fname varchar(40)='Dean')returns varchar(40)asbegin    declare @ret varchar(40)    set @ret=(select au_id from authors where au_fname=@fname)    return @retendgo--此处执行一定要添加架构名‘dbo.’select dbo.scalar_fuc_authors('White')

0 0