DCMSSYSUTILS.docx
- 文档编号:28675848
- 上传时间:2023-07-19
- 格式:DOCX
- 页数:46
- 大小:23.29KB
DCMSSYSUTILS.docx
《DCMSSYSUTILS.docx》由会员分享,可在线阅读,更多相关《DCMSSYSUTILS.docx(46页珍藏版)》请在冰豆网上搜索。
DCMSSYSUTILS
CREATEORREPLACEPackageBodyIncrementUpdateSIDIis
--从T_SEQUENCE表中获取文章的ID,然后将新的ID更新回T_SEQUENCE表
FunctiongetArticleIDReturnNumber
as
newArticleIDt_article.article_id%Type;
Begin
Selectcurrent_valueIntonewArticleIDFromT_SEQUENCEWhereName='T_ARTICLE';
UpdateT_SEQUENCESetcurrent_value=newArticleID+1WhereName='T_ARTICLE';
ReturnnewArticleID;
EndgetArticleID;
--查询证券简称,如果改证券代码可以查到,就返回该编码对应的简称,否则返回null
FunctiongetSecuAbbr(codeVarchar2)ReturnVarchar2
as
secunamet_article_extinfo.secuabbr%TYPE;
Begin
secuname:
=Null;
ifcodeisnotnullthen
selectsecuabbrintosecunamefromDATACENTER.t_Pub_Secumainsecwheresec.secucode=codeANDsec.appearanceIN(1,6);
endif;
Returnsecuname;
Exception
WhenOthersThen
secuname:
=Null;
Returnsecuname;
EndgetSecuAbbr;
--查询INFODATA用户下查询经典内参的数据,导入到SIDI.t_article中
procedureAcquisitionREALTIMEARTICLE2
as
cursorcur_ResultSet
is
SELECT
a.articleidasSOURCE_UNIQUE,
a.stockcodeASGPDM,
a.titleasTITLE,
a.summaryasBRIEF,
a.authorasAUTHOR,
decode(a.contentmark,1,0,a.contentmark)astype,
2941Ascatalogid,
a.ishotasIS_HOT,
a.isnewasIS_HEAD,
3asSTATE,
a.keywordasKEYWORD,
a.adjunctpathasLINK_URL,
a.imagepathasPICTURE_URL,
a.importanceASIMPORTANT_LEVEL,
0asHITS,
a.secretASDEGREE,
r.OrganNameASORGANNAME,
r.OverallRatingASGRADE,
r.gradenameASGRADENAME,
r.basepriceASAIMPRICE1,
r.closepriceASCLOSEPRICE,
r.expectedincreaseASEXPECTRISE,
r.standby1asGRADEIDPRE,
r.standby2asGRADEIDNEXT,
r.standby3asVARYZ,
r.standby4AsGRADEID,
nvl(to_char(a.createdate,'yyyy-mm-ddhh24:
mi:
ss'),'2000-01-01')ASpublish_date,
nvl(to_char(r.statisticsdate,'yyyy-mm-ddhh24:
mi:
ss'),'2000-01-01')AScreate_date,
to_char(a.lastmodifytime,'yyyy-mm-ddhh24:
mi:
ss')ASMODIFIED_DATE,
'admin'ascreate_by,
'admin'asmodified_by,
to_char(Sysdate,'yyyy-mm-ddHH24:
mi:
ss')Aslastmodifytime,
r.OrganNameasSOURCE,
'DataCenter'Asgathersource,
'T_PART_REALTIME_ARTICLE'ASFROMTABLE,
a.typenameAsclassname,
a.industrycodeASINDUSTRY_TYPE,
'main'asSITENO,
'2F'ASCHANNEL,
txt.ContentasContent
fromDATACENTER.T_PART_REALTIME_ARTICLEa,DATACENTER.T_PART_RESEARCHREPORTr,DATACENTER.t_part_contenttxt
wherea.articleid=r.CorrelationID(+)andtxt.articleid(+)=a.articleid--Anda.lastmodifytime>to_date('2011-09-10','yyyy-mm-dd')
anda.articleid>(selectnvl(max(to_number(SOURCE_UNIQUE)),0)fromt_articleWHERECHANNEL='2F')And(a.sourcetable='DTCONTENT'anda.standby1='1F')
orderbya.articleid;
articleIDnumber;
hasCountnumber;
secuabbrTempnvarchar2(100);
begin
forv_rowsincur_ResultSetloop
selectcount(*)intohasCountfromt_articlewhereSOURCE_UNIQUE=v_rows.SOURCE_UNIQUE;
secuabbrTemp:
=IncrementUpdateSIDI.getSecuAbbr(v_rows.gpdm);
ifhasCount=0then
articleID:
=IncrementUpdateSIDI.getArticleID;
insertintot_article(article_id,SOURCE_UNIQUE,gpdm,title,brief,author,type,catalog_id,link_url,picture_url,
important_level,is_hot,is_head,state,hits,keyword,publish_date,create_date,modified_date,create_by,modified_by,
lastmodifytime,SOURCE,industry_type,siteno,gathersource,CHANNEL)
values(articleID,v_rows.SOURCE_UNIQUE,v_rows.gpdm,v_rows.title,v_rows.brief,
v_rows.author,v_rows.type,v_rows.catalogid,v_rows.link_url,v_rows.picture_url,v_rows.important_level,v_rows.is_hot,
v_rows.is_head,v_rows.state,v_rows.hits,v_rows.keyword,v_rows.publish_date,v_rows.create_date,v_rows.modified_date,
v_rows.create_by,v_rows.modified_by,v_rows.lastmodifytime,v_rows.ORGANNAME,v_rows.industry_type,v_rows.siteno,v_rows.gathersource,v_rows.channel);
insertintoT_ARTICLE_EXTINFO(article_id,degree,grade,gradename,aimprice1,closeprice,expectrise,classname,SECUABBR,GRADEIDNEXT,GRADEIDPRE,VARYZ,GRADEID)
values(articleID,v_rows.degree,v_rows.grade,v_rows.gradename,v_rows.aimprice1,v_rows.closeprice,v_rows.expectrise,v_rows.classname,secuabbrTemp
v_rows.gradeidnext,v_rows.gradeidpre,v_rows.Varyz,v_rows.gradeid);
ifv_rows.type=0then
insertintot_article_contentvalues(articleID,v_rows.Content);
endif;
endif;
IFcur_ResultSet%ROWCOUNTMOD100=0THEN
COMMIT;
ENDIF;
endloop;
COMMIT;
endAcquisitionREALTIMEARTICLE2;
--3
procedureAcquisitionREALTIMEARTICLE3
as
cursorcur_ResultSet
is
SELECT
a.articleidasSOURCE_UNIQUE,
a.stockcodeASGPDM,
a.titleasTITLE,
a.summaryasBRIEF,
a.authorasAUTHOR,
decode(a.contentmark,1,0,a.contentmark)astype,
2985Ascatalogid,
a.ishotasIS_HOT,
a.isnewasIS_HEAD,
3asSTATE,
a.keywordasKEYWORD,
a.adjunctpathasLINK_URL,
a.imagepathasPICTURE_URL,
a.importanceASIMPORTANT_LEVEL,
0asHITS,
a.secretASDEGREE,
r.OrganNameASORGANNAME,
r.OverallRatingASGRADE,
r.gradenameASGRADENAME,
r.basepriceASAIMPRICE1,
r.closepriceASCLOSEPRICE,
r.expectedincreaseASEXPECTRISE,
r.standby1asGRADEIDPRE,
r.standby2asGRADEIDNEXT,
r.standby3asVARYZ,
r.standby4AsGRADEID,
nvl(to_char(a.createdate,'yyyy-mm-ddhh24:
mi:
ss'),'2000-01-01')ASpublish_date,
nvl(to_char(r.statisticsdate,'yyyy-mm-ddhh24:
mi:
ss'),'2000-01-01')AScreate_date,
to_char(a.lastmodifytime,'yyyy-mm-ddhh24:
mi:
ss')ASMODIFIED_DATE,
'admin'ascreate_by,
'admin'asmodified_by,
to_char(Sysdate,'yyyy-mm-ddHH24:
mi:
ss')Aslastmodifytime,
r.OrganNameasSOURCE,
'DataCenter'Asgathersource,
'T_PART_REALTIME_ARTICLE'ASFROMTABLE,
a.typenameAsclassname,
a.industrycodeASINDUSTRY_TYPE,
'main'asSITENO,
'3F'ASCHANNEL,
txt.ContentasContent
fromDATACENTER.T_PART_REALTIME_ARTICLEa,DATACENTER.T_PART_RESEARCHREPORTr,DATACENTER.t_part_contenttxt
Wherea.articleid=r.CorrelationID(+)andtxt.articleid(+)=a.articleid--Anda.lastmodifytime>to_date('2011-09-10','yyyy-mm-dd')
anda.articleid>(selectnvl(max(to_number(SOURCE_UNIQUE)),0)fromt_articlewhereCHANNEL='3F')And(a.catalogid=10802Anda.sourcetable='DTCONTENT'anda.standby1<>'1F')
orderbya.articleid;
articleIDnumber;
hasCountnumber;
secuabbrTempnvarchar2(100);
begin
forv_rowsincur_ResultSetloop
selectcount(*)intohasCountfromt_articlewhereSOURCE_UNIQUE=v_rows.SOURCE_UNIQUE;
secuabbrTemp:
=IncrementUpdateSIDI.getSecuAbbr(v_rows.gpdm);
ifhasCount=0then
articleID:
=IncrementUpdateSIDI.getArticleID;
insertintot_article(article_id,SOURCE_UNIQUE,gpdm,title,brief,author,type,catalog_id,link_url,picture_url,
important_level,is_hot,is_head,state,hits,keyword,publish_date,create_date,modified_date,create_by,modified_by,
lastmodifytime,SOURCE,industry_type,siteno,gathersource,CHANNEL)
values(articleID,v_rows.SOURCE_UNIQUE,v_rows.gpdm,v_rows.title,v_rows.brief,
v_rows.author,v_rows.type,v_rows.catalogid,v_rows.link_url,v_rows.picture_url,v_rows.important_level,v_rows.is_hot,
v_rows.is_head,v_rows.state,v_rows.hits,v_rows.keyword,v_rows.publish_date,v_rows.create_date,v_rows.modified_date,
v_rows.create_by,v_rows.modified_by,v_rows.lastmodifytime,v_rows.ORGANNAME,v_rows.industry_type,v_rows.siteno,v_rows.gathersource,v_rows.CHANNEL);
insertintoT_ARTICLE_EXTINFO(article_id,degree,grade,gradename,aimprice1,closeprice,expectrise,classname,SECUABBR,GRADEIDNEXT,GRADEIDPRE,VARYZ,GRADEID)
values(articleID,v_rows.degree,v_rows.grade,v_rows.gradename,v_rows.aimprice1,v_rows.closeprice,v_rows.expectrise,v_rows.classname,secuabbrTemp
v_rows.gradeidnext,v_rows.gradeidpre,v_rows.Varyz,v_rows.gradeid);
ifv_rows.type=0then
insertintot_article_contentvalues(articleID,v_rows.Content);
endif;
endif;
IFcur_ResultSet%ROWCOUNTMOD100=0THEN
COMMIT;
ENDIF;
endloop;
COMMIT;
endAcquisitionREALTIMEARTICLE3;
--4
procedureAcquisitionREALTIMEARTICLE4
as
cursorcur_ResultSet
is
SELECT
a.articleidasSOURCE_UNIQUE,
a.stockcodeASGPDM,
a.titleasTITLE,
a.summaryasBRIEF,
a.authorasAUTHOR,
decode(a.contentmark,1,0,a.contentmark)astype,
2909Ascatalogid,
a.ishotasIS_HOT,
a.isnewasIS_HEAD,
3asSTATE,
a.keywordasKEYWORD,
a.adjunctpathasLINK_URL,
a.imagepathasPICTURE_URL,
a.importanceASIMPORTANT_LEVEL,
0asHITS,
a.secretASDEGREE,
r.OrganNameASORGANNAME,
r.OverallRatingASGRADE,
r.gradenameASGRADENAME,
r.basepriceASAIMPRICE1,
r.closepriceASCLOSEPRICE,
r.expectedincreaseASEXPECTRISE,
r.standby1asGRADEIDPRE,
r.standby2asGRADEIDNEXT,
r.standby3asVARYZ,
r.standby4AsGRADEID,
nvl(to_char(a.createdate,'yyyy-mm-ddhh24:
mi:
ss'),'2000-01-01')ASpublish_date,
nvl(to_char(r.statisticsdate,'yyyy-mm-ddhh24:
mi:
ss'),'2000-01-01')AScreate_date,
to_char(a.lastmodifytime,'yyyy-mm-ddhh24:
mi:
ss')ASMODIFIED_DATE,
'admin'ascreate_by,
'admin'asmodified_by,
to_char(Sysdate,'yyyy-mm-ddHH24:
mi:
ss')Aslastmodifytime,
r.OrganNameasSOURCE,
'DataCenter'Asgathersource,
'T_PART_REALTIME_ARTICLE'ASFROMTABLE,
a.typenameAsclassname,
a.industrycodeASINDUSTRY_TYPE,
'main'asSITENO,
'4F'ASCHANNEL,
txt.ContentasContent
fromDATACENTER.T_PART_REALTIME_ARTICLEa,DATACENTER.T_PART_RESEARCHREPORTr,DATACENTER.t_part_contenttxt
Wherea.articleid=r.CorrelationID(+)andtxt.articleid(+)=a.articleid--Anda.lastmodifytime>to_date('2011-09-10','yyyy-mm-dd')
anda.articleid>(selectnvl(max(to_number(SOURCE_UNIQUE)),0)fromt_articleWHERECHANNEL='4F')And(a.catalogid=20304Anda.sourcetable='SSYW')
orderbya.articleid;
articleIDnumber;
hasCountnumber;
secuabbrTempnvarchar2(100);
begin
forv_rowsincur_ResultSetloop
selectcount(*)intohasCountfromt_articlewhereSOURCE_UNIQUE=v_rows.SOURCE_UNIQUE;
secuabbrTemp:
=IncrementUpdateSIDI.getSecuAbbr(v_rows.gpdm);
ifhasCount=0then
articleID:
=IncrementUpdateSIDI.getArticleID;
insertintot_article(article_id,SOURCE_UNIQUE,gpdm,title,brief,author,type,catalog_id,link_url,picture_url,
important_level,is_hot,is
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- DCMSSYSUTILS
![提示](https://static.bdocx.com/images/bang_tan.gif)