Use of functions and views in Oracle

Functions are the same as functions and views in SQL Server classification.

-- Search fields to be set: expertname, expertdomain, hitcountsdata, workhistory, eduhistory, projecthistory, award, researchpaper, researchpapermonograph, expertallinfo
-- The expertname and expertdomain fields are of the phrase type, and other retrieval fields are of the document type.

 

-- 1. obtain expert experience
-- 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. obtain expert learning experience
-- 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_1_sionalname ,''));
V_result: = Concat (v_result ,';');

End loop;
Close c_eduhistory;
Return nvl (v_result ,'');
End f_geteduhistory;

-- 3. obtain expert project experience
-- 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. obtain experts' Awards
-- 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. obtain expert papers
-- 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. obtain expert Monographs
-- 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. All expert information
-- 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. Create a view
-- Drop view v_coworkplatformexico
Create view v_coworkplatformexico
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;