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

--1: 创建函数F_ConcatedTagname
--drop function F_ConcatedTagname;
create function F_ConcatedTagname(
    @ItemNumber numeric(9)    
) 
returns varchar(8000) 
as 
begin
declare @ret varchar(8000)
select @ret = '' 
select @ret = @ret + tagname + ';' from xwcmdoctag 
where docid = @ItemNumber and taggingnum>0
return @ret
end
go


--2: 创建函数F_GetRecId
--drop function dbo.F_GetRecId
create function F_GetRecId(@docid numeric(9),@chnlid numeric(9)) 
returns  numeric(9)
as 
begin
declare @ret  numeric(9)
select @ret = 0
select @ret=recid from wcmchnldoc 
where chnlId = @chnlid and docid= @docid
return @ret
end
go

--3: 创建函数F_GetModal
--drop function dbo.F_GetModal
create function F_GetModal(@docid numeric(9),@chnlid numeric(9)) 
returns  numeric(9)
as 
begin
declare @ret  numeric(9)
select @ret = 0
select @ret=modal from wcmchnldoc 
where chnlId = @chnlid and docid= @docid
return @ret
end
go

--4: 创建函数F_GetDocOrderPri
--drop function dbo.F_GetDocOrderPri
create function F_GetDocOrderPri(@docid numeric(9),@chnlid numeric(9)) 
returns  numeric(9)
as 
begin
declare @ret  numeric(9)
select @ret = 0
select @ret=docorderpri from wcmchnldoc 
where chnlId = @chnlid and docid= @docid
return @ret
end
go
--5: 创建函数F_GetStatus
-- drop function dbo.F_GetStatus
create function F_GetStatus (@docid numeric(9),@chnlid numeric(9)) 
returns  numeric(9)
as 
begin
declare @ret  numeric(9)
select @ret = 0
select @ret=docstatus from wcmchnldoc 
where chnlId = @chnlid and docid= @docid
return @ret
end
go

--6: 创建视图
--drop view V_WCMDocumentAndTags;
create view V_WCMDocumentAndTags
as
SELECT 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 Modal
FROM WCMDOCUMENT LEFT OUTER JOIN
        (SELECT docid, DocTags =dbo.F_ConcatedTagname(docid)
         FROM xwcmdoctag
         GROUP BY docid) kmdoctags ON 
      WCMDOCUMENT.DOCID = kmdoctags.docid
WHERE (WCMDOCUMENT.DOCSTATUS > 0)
Go

--1: 创建函数F_ConcatedTagname
create or replace function  F_ConcatedTagname(
f_DocId in NUMBER) return varchar2 is
v_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_GetRecId
create 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_GetModal
create 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_GetDocOrderPri
create 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_GetStatus
create 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_WCMDocumentAndTags
as
SELECT 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 Modal
FROM WCMDOCUMENT ,
        (SELECT docid,F_ConcatedTagname(docid) DocTags
         FROM xwcmdoctag
         GROUP BY docid) kmdoctags 
where WCMDOCUMENT.DOCID = kmdoctags.docid(+)  and WCMDOCUMENT.DOCSTATUS > 0;