sql server 函数与视图的使用

--需要设置的检索字段: ExpertName，ExpertDomain，hitCountsData，WorkHistory，EduHistory，ProjectHistory，Award，ResearchPaper，ResearchPaperMonograph，ExpertAllInfo
--其中字段 ExpertAllInfo 为document类型，别的检索字段都为phrase类型

--1. 获取专家工作经历
-- drop function F_GetWorkHistory

create function F_GetWorkHistory(
   @ExpertId numeric(9)
)
returns varchar(8000)
as
begin
    declare @ret varchar(8000)
    select @ret = ''
      select @ret = @ret + isnull(workOrgnization,'') + ' ' + isnull(WorkPost,'') + ';' from xwcmworkHistory
      where objid = @ExpertId and objtype = 2045598757
    if(@ret is null)
 return ''
    return @ret
end
Go

--2.获取专家学习经历
-- drop function F_GetEduHistory
create function F_GetEduHistory(
   @ExpertId numeric(9)

)
returns varchar(8000)
as
begin
    declare @ret varchar(8000)
    select @ret = ''
      select @ret = @ret + isnull(EduSchool,'') + ' ' + isnull(ProfessionalName,'') + ';' from xWCMEduHistory
      where objid = @ExpertId and objtype = 2045598757
    if(@ret is null)
 return ''
    return @ret
end
Go

--3.获取专家项目经历
-- drop function F_GetProjectHistory
create function F_GetProjectHistory(
   @ExpertId numeric(9)

)
returns varchar(8000)
as
begin
    declare @ret varchar(8000)
    select @ret = ''
      select @ret = @ret + isnull(ProjectName,'') + ' '+ isnull(Post,'') + ' ' + isnull(Orgnization,'') + ' ' + isnull(Production,'') + ';' from xWCMProjectHistory
      where objid = @ExpertId and objtype = 2045598757
    if(@ret is null)
 return ''
    return @ret
end
Go

--4.获取专家获奖情况
-- drop function F_GetAward
create function F_GetAward(
   @ExpertId numeric(9)

)
returns varchar(8000)
as
begin
    declare @ret varchar(8000)
    select @ret = ''
      select @ret = @ret + isnull(AwardTitle,'') + ';' from XWCMAward
      where objid = @ExpertId and objtype = 2045598757
    if(@ret is null)
 return ''
    return @ret
end
Go

 

--5.获取专家论文
-- drop function F_GetResearchPaper
create function F_GetResearchPaper(
   @ExpertId numeric(9)
)
returns varchar(8000)
as
begin
    declare @ret varchar(8000)
    select @ret = ''
      select @ret = @ret + isnull(researchPaperTitle,'') + ' ' + isnull(Author,'') + ' ' + isnull(Summary,'') + ' ' + isnull(PeriodicalName,'') + ' '
                         + isnull(PublishCompany,'') + ' ' + isnull(PeriodTime,'') + ' ' + isnull(Content,'') + ';' from xWCMresearchPaper
       where ResearchPaperId in (select ResearchPaperId from xWCMresearchPaperUser
            where objid = @ExpertId and objtype = 2045598757
       ) and researchPaperType = 0
      
    if(@ret is null)
 return ''
    return @ret
end
Go

--6.获取专家专著
--drop function F_GetResearchPaperMonograph
create function F_GetResearchPaperMonograph(
   @ExpertId numeric(9)
)
returns varchar(8000)
as
begin
    declare @ret varchar(8000)
    select @ret = ''
      select @ret = @ret + isnull(researchPaperTitle,'') + ' ' + isnull(Author,'') + ' ' + isnull(Summary,'') + ' ' + isnull(PeriodicalName,'') + ' '
                         + isnull(PublishCompany,'') + ' ' + isnull(PeriodTime,'') + ' ' + isnull(Content,'') + ';' from xWCMresearchPaper
       where ResearchPaperId in (select ResearchPaperId from xWCMresearchPaperUser
            where objid = @ExpertId and objtype = 2045598757
       ) and researchPaperType = 1
      
    if(@ret is null)
 return ''
    return @ret
end
Go

--7.专家的全部信息
--drop function F_GetExpertAllInfo
create function F_GetExpertAllInfo(
   @ExpertId numeric(9)
)
returns varchar(8000)
as
begin
    declare @ret varchar(8000)
    select @ret = ''
      select @ret = @ret + isnull(ExpertName,'') + '||' + isnull(ExpertDomain,'') + '||'      
       + dbo.F_GetWorkHistory(@expertid) + '||' + dbo.F_GetEduHistory(@expertid) + '||'
       + dbo.F_GetProjectHistory(@expertid) + '||' + dbo.F_GetAward(@expertid) + '||'
       + dbo.F_GetResearchPaper(@expertid) + '||' + dbo.F_GetResearchPaperMonograph(@expertid)      
      from XWCMExpert  where  ExpertId = @ExpertId
    return @ret
end
Go

--8. 创建视图
--drop view V_CoWorkPlatFormExpert
create view V_CoWorkPlatFormExpert
as
select * ,dbo.F_GetWorkHistory(XWCMExpert.Expertid) as WorkHistory,dbo.F_GetEduHistory(XWCMExpert.Expertid) as EduHistory,
  dbo.F_GetProjectHistory(XWCMExpert.Expertid) as ProjectHistory,dbo.F_GetAward(XWCMExpert.Expertid) as Award,
  dbo.F_GetResearchPaper(XWCMExpert.Expertid) as ResearchPaper,dbo.F_GetResearchPaperMonograph(XWCMExpert.Expertid) as ResearchPaperMonograph,
  dbo.F_GetExpertAllInfo(XWCMExpert.Expertid) as ExpertAllInfo
from XWCMExpert