oracle中的函數與視圖的使用

功能與sql server分類中的函數與視圖的使用一致

--需要設定的檢索欄位: ExpertName，ExpertDomain，hitCountsData，WorkHistory，EduHistory，ProjectHistory，Award，ResearchPaper，ResearchPaperMonograph，ExpertAllInfo
--其中欄位 ExpertName和ExpertDomain 為phrase類型，別的檢索欄位都為document類型

 

--1. 擷取專家工作經曆
-- drop function F_GetWorkHistory
create or replace function   "XHPORTAL".F_GetWorkHistory(
f_ExpertId in NUMBER) return clob is
v_Result clob;
v_workOranization varchar2(255);
v_workPost varchar2(255);
CURSOR c_WorkHistory IS SELECT workOrgnization,workPost FROM xwcmWorkHistory WHERE objid = f_ExpertId and objtype = 2045598757;
begin
  OPEN c_WorkHistory; 
  LOOP
      FETCH c_WorkHistory INTO v_workOranization,v_workPost;
      EXIT WHEN  c_WorkHistory%NOTFOUND;
      v_Result := CONCAT(v_Result,nvl(v_workOranization,''));      
      v_Result := CONCAT(v_Result,' ');       
      v_Result := CONCAT(v_Result,nvl(v_workPost,''));       
      v_Result := CONCAT(v_Result,';');
      
  END LOOP;
  CLOSE c_WorkHistory;
  return nvl(v_Result,'');
end F_GetWorkHistory;

--2.擷取專家學習經曆
-- drop function F_GetEduHistory
create or replace function  "XHPORTAL".F_GetEduHistory(
f_ExpertId in NUMBER) return clob is
v_Result clob;
v_EduSchool varchar2(255);
v_ProfessionalName varchar2(510);
CURSOR c_EduHistory IS SELECT EduSchool,ProfessionalName FROM xWCMEduHistory WHERE objid = f_ExpertId and objtype = 2045598757;
begin
  OPEN c_EduHistory; 
  LOOP
      FETCH c_EduHistory INTO v_EduSchool,v_ProfessionalName;
      EXIT WHEN  c_EduHistory%NOTFOUND;
      v_Result := CONCAT(v_Result,nvl(v_EduSchool,''));      
      v_Result := CONCAT(v_Result,';');
      v_Result := CONCAT(v_Result,nvl(v_ProfessionalName,''));      
      v_Result := CONCAT(v_Result,';'); 
    
  END LOOP;
  CLOSE c_EduHistory;
  return nvl(v_Result,'');
end F_GetEduHistory;

--3.擷取專家項目經曆
-- drop function F_GetProjectHistory
create or replace function  "XHPORTAL".F_GetProjectHistory(
f_ExpertId in NUMBER) return clob is
v_Result clob;
v_ProjectName varchar2(255);
v_Post varchar2(255);
v_Orgnization varchar2(150);
v_Production varchar2(150);
CURSOR c_ProjectHistory IS SELECT ProjectName,Post,Orgnization,Production FROM xWCMProjectHistory WHERE objid = f_ExpertId and objtype = 2045598757;
begin
  OPEN c_ProjectHistory; 
  LOOP
      FETCH c_ProjectHistory INTO v_ProjectName,v_Post,v_Orgnization,v_Production;
      EXIT WHEN  c_ProjectHistory%NOTFOUND;
      v_Result := CONCAT(v_Result,nvl(v_ProjectName,''));      
      v_Result := CONCAT(v_Result,' ');    
      v_Result := CONCAT(v_Result,nvl(v_Post,''));      
      v_Result := CONCAT(v_Result,' ');
      v_Result := CONCAT(v_Result,nvl(v_Orgnization,''));      
      v_Result := CONCAT(v_Result,' ');
      v_Result := CONCAT(v_Result,nvl(v_Production,''));      
      v_Result := CONCAT(v_Result,';');
  END LOOP;
  CLOSE c_ProjectHistory;
  return nvl(v_Result,'');
end F_GetProjectHistory;

--4.擷取專家獲獎情況
-- drop function F_GetAward
create or replace function  "XHPORTAL".F_GetAward(
f_ExpertId in NUMBER) return clob is
v_Result clob;
v_AwardTitle varchar2(2000);
CURSOR c_Award IS SELECT AwardTitle FROM XWCMAward WHERE objid = f_ExpertId and objtype = 2045598757;
begin
  OPEN c_Award; 
  LOOP
      FETCH c_Award INTO v_AwardTitle;
      EXIT WHEN  c_Award%NOTFOUND;
      v_Result := CONCAT(v_Result,nvl(v_AwardTitle,''));      
      v_Result := CONCAT(v_Result,';');    
    
  END LOOP;
  CLOSE c_Award;
  return nvl(v_Result,'');
end F_GetAward;

 

--5.擷取專家論文
-- drop function F_GetResearchPaper
create or replace function  "XHPORTAL".F_GetResearchPaper(
f_ExpertId in NUMBER) return clob is
v_Result clob;
v_ResearchPaperTitle varchar2(1000);
v_Author varchar2(2000);
v_Summary varchar2(2000);
v_PeriodicalName varchar2(2000);
v_PublishCompany varchar2(2000);
v_PeriodTime varchar2(2000);
v_Content varchar2(2000);

CURSOR c_ResearchPaper IS SELECT researchPaperTitle,Author,Summary,PeriodicalName,PublishCompany,PeriodTime,Content from xWCMresearchPaper
    where  ResearchPaperId in (select ResearchPaperId from xWCMresearchPaperUser
            where objid = f_ExpertId and objtype = 2045598757
       ) and researchPaperType = 0;
begin
  OPEN c_ResearchPaper; 
  LOOP
      FETCH c_ResearchPaper INTO v_ResearchPaperTitle,v_Author,v_Summary,v_PeriodicalName,v_PublishCompany,v_PeriodTime,v_Content;
      EXIT WHEN  c_ResearchPaper%NOTFOUND;
      v_Result := CONCAT(v_Result,nvl(v_ResearchPaperTitle,''));      
      v_Result := CONCAT(v_Result,' ');
      v_Result := CONCAT(v_Result,nvl(v_Author,''));      
      v_Result := CONCAT(v_Result,' ');
      v_Result := CONCAT(v_Result,nvl(v_Summary,''));      
      v_Result := CONCAT(v_Result,' ');
      v_Result := CONCAT(v_Result,nvl(v_PeriodicalName,''));      
      v_Result := CONCAT(v_Result,' ');
      v_Result := CONCAT(v_Result,nvl(v_PublishCompany,''));      
      v_Result := CONCAT(v_Result,' ');
      v_Result := CONCAT(v_Result,nvl(v_PeriodTime,''));      
      v_Result := CONCAT(v_Result,' ');
      v_Result := CONCAT(v_Result,nvl(v_Content,''));      
      v_Result := CONCAT(v_Result,';');
    
  END LOOP;
  CLOSE c_ResearchPaper;
  return nvl(v_Result,'');
end F_GetResearchPaper;

--6.擷取專家專著
--drop function F_GetResearchPaperMonograph
create or replace function  "XHPORTAL".F_GetResearchPaperMonograph(
f_ExpertId in NUMBER) return clob is
v_Result clob;
v_ResearchPaperTitle varchar2(1000);
v_Author varchar2(225);
v_Summary varchar2(500);
v_PeriodicalName varchar2(500);
v_PublishCompany varchar2(500);
v_PeriodTime varchar2(100);
v_Content varchar2(2000);

CURSOR c_ResearchPaper IS SELECT researchPaperTitle,Author,Summary,PeriodicalName,PublishCompany,PeriodTime,Content from xWCMresearchPaper
    where  ResearchPaperId in (select ResearchPaperId from xWCMresearchPaperUser
            where objid = f_ExpertId and objtype = 2045598757
       ) and researchPaperType = 1;
begin
  OPEN c_ResearchPaper; 
  LOOP
      FETCH c_ResearchPaper INTO v_ResearchPaperTitle,v_Author,v_Summary,v_PeriodicalName,v_PublishCompany,v_PeriodTime,v_Content;
      EXIT WHEN  c_ResearchPaper%NOTFOUND;
      v_Result := CONCAT(v_Result,nvl(v_ResearchPaperTitle,''));      
      v_Result := CONCAT(v_Result,' ');
      v_Result := CONCAT(v_Result,nvl(v_Author,''));      
      v_Result := CONCAT(v_Result,' ');
      v_Result := CONCAT(v_Result,nvl(v_Summary,''));      
      v_Result := CONCAT(v_Result,' ');
      v_Result := CONCAT(v_Result,nvl(v_PeriodicalName,''));      
      v_Result := CONCAT(v_Result,' ');
      v_Result := CONCAT(v_Result,nvl(v_PublishCompany,''));      
      v_Result := CONCAT(v_Result,' ');
      v_Result := CONCAT(v_Result,nvl(v_PeriodTime,''));      
      v_Result := CONCAT(v_Result,' ');
      v_Result := CONCAT(v_Result,nvl(v_Content,''));      
      v_Result := CONCAT(v_Result,';');
    
  END LOOP;
  CLOSE c_ResearchPaper;
  return nvl(v_Result,'');
end F_GetResearchPaperMonograph;

--7.專家的全部資訊
--drop function F_GetExpertAllInfo
create or replace function  "XHPORTAL".F_GetExpertAllInfo(
f_ExpertId in NUMBER) return clob is
v_Result clob;
v_ExpertName varchar2(255);
v_IsMale numeric(9);
v_IdentityId varchar2(100);
v_organization varchar2(500);
v_ExpertTitle varchar2(255);
v_EduBackGround varchar2(255);
v_ExpertPost varchar2(255);
v_ExpertDomain varchar2(2000);
v_Tel varchar2(255);
v_mobile varchar2(255);
v_address varchar2(255);
v_NativePlace varchar2(510);
v_PoliticalStatus varchar2(510);
v_SocialWork varchar2(2000);
v_TutorType varchar2(200);
v_Department varchar2(510);
v_Remark varchar2(1000);

v_WorkHistory clob;
v_EduHistory clob;
v_ProjectHistory clob;
v_Award clob;
v_ResearchPaper clob;
v_ResearchPaperMonograph clob;

CURSOR c_WorkHistory is select F_GetWorkHistory(f_ExpertId) from dual;
CURSOR c_EduHistory is select F_GetEduHistory(f_ExpertId) from dual;
CURSOR c_ProjectHistory is select F_GetProjectHistory(f_ExpertId) from dual;
CURSOR c_Award is select F_GetAward(f_ExpertId) from dual;
CURSOR c_ResearchPaper is select F_GetResearchPaper(f_ExpertId) from dual;
CURSOR c_ResearchPaperMonograph is select F_GetResearchPaperMonograph(f_ExpertId) from dual;

CURSOR c_ExpertAllInfo IS SELECT ExpertName,IsMale,IdentityId,organization,ExpertTitle,EduBackGround,
        ExpertPost,ExpertDomain,Tel,mobile,address,NativePlace,PoliticalStatus,SocialWork,TutorType,Department,Remark from XWCMExpert
        where  ExpertId = f_ExpertId;
begin
  OPEN c_ExpertAllInfo; 
  LOOP
     
      FETCH c_ExpertAllInfo INTO v_ExpertName,v_IsMale,v_IdentityId,v_organization,v_ExpertTitle,v_EduBackGround,v_ExpertPost,
            v_ExpertDomain,v_Tel,v_mobile,v_address,v_NativePlace,v_PoliticalStatus,v_SocialWork,v_TutorType,v_Department,v_Remark;
      EXIT WHEN  c_ExpertAllInfo%NOTFOUND;
 
      v_Result := CONCAT(v_Result,nvl(v_ExpertName,''));      
      v_Result := CONCAT(v_Result,'||');     
      v_Result := CONCAT(v_Result,nvl(v_ExpertDomain,''));      
      v_Result := CONCAT(v_Result,'||');         
  END LOOP;
  CLOSE c_ExpertAllInfo;
 
 
    Open c_WorkHistory;
    loop
      fetch c_WorkHistory into v_WorkHistory;
      exit when c_WorkHistory%NOTFOUND;
      v_Result := CONCAT(v_Result,nvl(v_WorkHistory,''));      
      v_Result := CONCAT(v_Result,'||');
    END LOOP;
    CLOSE c_WorkHistory;
   
    Open c_EduHistory;
    loop
      fetch c_EduHistory into v_EduHistory;
      exit when c_EduHistory%NOTFOUND;
      v_Result := CONCAT(v_Result,nvl(v_EduHistory,''));      
      v_Result := CONCAT(v_Result,'||');
    END LOOP;
    CLOSE c_EduHistory;
   
    Open c_ProjectHistory;
    loop
      fetch c_ProjectHistory into v_ProjectHistory;
      exit when c_ProjectHistory%NOTFOUND;
      v_Result := CONCAT(v_Result,nvl(v_ProjectHistory,''));      
      v_Result := CONCAT(v_Result,'||');
    END LOOP;
    CLOSE c_ProjectHistory;
   
    Open c_Award;
    loop
      fetch c_Award into v_Award;
      exit when c_Award%NOTFOUND;
      v_Result := CONCAT(v_Result,nvl(v_Award,''));      
      v_Result := CONCAT(v_Result,'||');
    END LOOP;
    CLOSE c_Award;
   
    Open c_ResearchPaper;
    loop
      fetch c_ResearchPaper into v_ResearchPaper;
      exit when c_ResearchPaper%NOTFOUND;
      v_Result := CONCAT(v_Result,nvl(v_ResearchPaper,''));      
      v_Result := CONCAT(v_Result,'||');
    END LOOP;
    CLOSE c_ResearchPaper;
 
    Open c_ResearchPaperMonograph;
    loop
      fetch c_ResearchPaperMonograph into v_ResearchPaperMonograph;
      exit when c_ResearchPaperMonograph%NOTFOUND;
      v_Result := CONCAT(v_Result,nvl(v_ResearchPaperMonograph,''));      
      v_Result := CONCAT(v_Result,'||');
    END LOOP;
    CLOSE c_ResearchPaperMonograph;
 
  return nvl(v_Result,'');
end F_GetExpertAllInfo;

--8. 建立視圖
--drop view V_CoWorkPlatFormExpert
create view V_CoWorkPlatFormExpert
as
select XWCMExpert.* ,"XHPORTAL".F_GetWorkHistory(XWCMExpert.Expertid) as WorkHistory,"XHPORTAL".F_GetEduHistory(XWCMExpert.Expertid) as EduHistory,
  "XHPORTAL".F_GetProjectHistory(XWCMExpert.Expertid) as ProjectHistory,"XHPORTAL".F_GetAward(XWCMExpert.Expertid) as Award,
  "XHPORTAL".F_GetResearchPaper(XWCMExpert.Expertid) as ResearchPaper,"XHPORTAL".F_GetResearchPaperMonograph(XWCMExpert.Expertid) as ResearchPaperMonograph,
  "XHPORTAL".F_GetExpertAllInfo(XWCMExpert.Expertid) as ExpertAllInfo
from XWCMExpert;