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