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

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

没有办法，小生之前没有接触过DB2语法，我这里有两套SQL脚本，一个是SQLServer的，一个是Oracle的，都是完成一样的事情，有没有大牛帮忙转换一下呢，谢谢啦

SQLServer的

SQL code--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

Oracle的

SQL code--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;