，oracle/SQLServer语法的函数、视图转为DB2语法的脚本

--1: 创建函数F_ConcatedTagname--drop function F_ConcatedTagname;create function F_ConcatedTagname(    @ItemNumber numeric(9)    ) returns varchar(8000) as begindeclare @ret varchar(8000)select @ret = '' select @ret = @ret + tagname + ';' from xwcmdoctag where docid = @ItemNumber and taggingnum>0return @retendgo--2: 创建函数F_GetRecId--drop function dbo.F_GetRecIdcreate function F_GetRecId(@docid numeric(9),@chnlid numeric(9)) returns  numeric(9)as begindeclare @ret  numeric(9)select @ret = 0select @ret=recid from wcmchnldoc where chnlId = @chnlid and docid= @docidreturn @retendgo--3: 创建函数F_GetModal--drop function dbo.F_GetModalcreate function F_GetModal(@docid numeric(9),@chnlid numeric(9)) returns  numeric(9)as begindeclare @ret  numeric(9)select @ret = 0select @ret=modal from wcmchnldoc where chnlId = @chnlid and docid= @docidreturn @retendgo--4: 创建函数F_GetDocOrderPri--drop function dbo.F_GetDocOrderPricreate function F_GetDocOrderPri(@docid numeric(9),@chnlid numeric(9)) returns  numeric(9)as begindeclare @ret  numeric(9)select @ret = 0select @ret=docorderpri from wcmchnldoc where chnlId = @chnlid and docid= @docidreturn @retendgo--5: 创建函数F_GetStatus-- drop function dbo.F_GetStatuscreate function F_GetStatus (@docid numeric(9),@chnlid numeric(9)) returns  numeric(9)as begindeclare @ret  numeric(9)select @ret = 0select @ret=docstatus from wcmchnldoc where chnlId = @chnlid and docid= @docidreturn @retendgo--6: 创建视图--drop view V_WCMDocumentAndTags;create view V_WCMDocumentAndTagsasSELECT WCMDOCUMENT.*,kmdoctags.DocTags AS DocTags,dbo.F_GetStatus(WCMDOCUMENT.DocId,WCMDOCUMENT.DocChannel) as docstatus1,dbo.F_GetRecId(WCMDOCUMENT.DocId,WCMDOCUMENT.DocChannel) as RecId,dbo.F_GetDocOrderPri(WCMDOCUMENT.DocId,WCMDOCUMENT.DocChannel) as DocorderPri,dbo.F_GetModal(WCMDOCUMENT.DocId,WCMDOCUMENT.DocChannel) as ModalFROM WCMDOCUMENT LEFT OUTER JOIN        (SELECT docid, DocTags =dbo.F_ConcatedTagname(docid)         FROM xwcmdoctag         GROUP BY docid) kmdoctags ON       WCMDOCUMENT.DOCID = kmdoctags.docidWHERE (WCMDOCUMENT.DOCSTATUS > 0)Go

--1: 创建函数F_ConcatedTagnamecreate or replace function  F_ConcatedTagname(f_DocId in NUMBER) return varchar2 isv_Result varchar2(8000);v_TagName varchar2(255);CURSOR c_DocTag IS SELECT tagname FROM xwcmdoctag where docid=f_DocId and taggingnum>0;begin  OPEN c_DocTag;    LOOP      FETCH c_DocTag INTO v_TagName;      EXIT WHEN  c_DocTag%NOTFOUND;      v_Result := CONCAT(v_Result,nvl(v_TagName,''));             v_Result := CONCAT(v_Result,';');          END LOOP;  CLOSE c_DocTag;  return nvl(v_Result,'');end F_ConcatedTagname;--2: 创建函数F_GetRecIdcreate or replace function F_GetRecId(Idocid in numeric,Ichnlid in numeric) return numeric is Result numeric(9); begin select recid into Result from wcmchnldoc where chnlId = Ichnlid and docid= Idocid;return(Result); end F_GetRecId;--3: 创建函数F_GetModalcreate or replace function F_GetModal(Idocid in numeric,Ichnlid in numeric) return numeric is Result numeric(9); begin select modal into Result from wcmchnldoc where chnlId = Ichnlid and docid= Idocid;return(Result); end F_GetModal;--4: 创建函数F_GetDocOrderPricreate or replace function F_GetDocOrderPri(Idocid in numeric,Ichnlid in numeric) return numeric is Result numeric(9); begin select docorderpri into Result from wcmchnldoc where chnlId = Ichnlid and docid= Idocid;return(Result); end F_GetDocOrderPri;--5: 创建函数F_GetStatuscreate or replace function F_GetStatus(Idocid in numeric,Ichnlid in numeric) return numeric is Result numeric(9); begin select docstatus into Result from wcmchnldoc where chnlId = Ichnlid and docid= Idocid;return(Result); end F_GetStatus;--6: 创建视图--drop view V_WCMDocumentAndTags;create view V_WCMDocumentAndTagsasSELECT WCMDOCUMENT.*,kmdoctags.DocTags AS DocTags,F_GetStatus(WCMDOCUMENT.DocId,WCMDOCUMENT.DocChannel) as docstatus1,F_GetRecId(WCMDOCUMENT.DocId,WCMDOCUMENT.DocChannel) as RecId,F_GetDocOrderPri(WCMDOCUMENT.DocId,WCMDOCUMENT.DocChannel) as DocorderPri,F_GetModal(WCMDOCUMENT.DocId,WCMDOCUMENT.DocChannel) as ModalFROM WCMDOCUMENT ,        (SELECT docid,F_ConcatedTagname(docid) DocTags         FROM xwcmdoctag         GROUP BY docid) kmdoctags where WCMDOCUMENT.DOCID = kmdoctags.docid(+)  and WCMDOCUMENT.DOCSTATUS > 0;