摘要:本文主要讲解了SQL Server数据库函数与视图的使用,希望读罢此文可以对您学习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
本文由职坐标整理并发布,希望对同学们有所帮助。了解更多数据库知识请关注SQL Server频道!
您输入的评论内容中包含违禁敏感词
我知道了
请输入正确的手机号码
请输入正确的验证码
您今天的短信下发次数太多了,明天再试试吧!
我们会在第一时间安排职业规划师联系您!
您也可以联系我们的职业规划师咨询:
版权所有 职坐标-一站式IT培训就业服务领导者 沪ICP备13042190号-4
上海海同信息科技有限公司 Copyright ©2015 www.zhizuobiao.com,All Rights Reserved.
沪公网安备 31011502005948号