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
升级会员 