以下是一个Sql Server的表值函数:CREATE function [dbo].[GetTopModelOfMenu](@SysMenuId varchar(50))
returns @ModelTable table(SysModelId varchar(50),Name varchar(200),DisplayOrder int)
as
begin
declare @SysModelId varchar(50)
declare @ParentSysModelId varchar(50)
insert into @ModelTable
select SysModelId,Name,DisplayOrder from SysModel where SysModelId in
(
select distinct SysModelId from SysMapping
where SysMappingId in
(
select SysMappingId from SysMenuMapping where SysMenuId=@SysMenuId and Browser='True'
)and convert(bit,IsVirtual)=0
)
declare myCursor cursor for
select SysModelId from @ModelTable
open myCursor
fetch next from myCursor into @SysModelId
while @@fetch_status=0
begin
select top 1 @ParentSysModelId=ParentSysModelId from SysModel where SysModelId=@SysModelId
if @ParentSysModelId is not null
begin
if not Exists(select * from @ModelTable where SysModelId=@ParentSysModelId)
insert into @ModelTable
select SysModelId,Name,DisplayOrder
from SysModel
where SysModelId=@ParentSysModelId
delete from @ModelTable where SysModelId=@SysModelId
end fetch next from myCursor into @SysModelId
end
close myCursor
deallocate myCursor
return
end现在要将该函数改写到Oracle中。并且使用方式类似于 select * from GetTopModelOfMenu(id).
函数中的convert改为to_number,bit改为int.
我一点都不会Oracle,上网查了相关资料,但还是不懂。特别是该函数中还有游标,全局临时表之类的。但现在急要。
望会的帮忙改下。
returns @ModelTable table(SysModelId varchar(50),Name varchar(200),DisplayOrder int)
as
begin
declare @SysModelId varchar(50)
declare @ParentSysModelId varchar(50)
insert into @ModelTable
select SysModelId,Name,DisplayOrder from SysModel where SysModelId in
(
select distinct SysModelId from SysMapping
where SysMappingId in
(
select SysMappingId from SysMenuMapping where SysMenuId=@SysMenuId and Browser='True'
)and convert(bit,IsVirtual)=0
)
declare myCursor cursor for
select SysModelId from @ModelTable
open myCursor
fetch next from myCursor into @SysModelId
while @@fetch_status=0
begin
select top 1 @ParentSysModelId=ParentSysModelId from SysModel where SysModelId=@SysModelId
if @ParentSysModelId is not null
begin
if not Exists(select * from @ModelTable where SysModelId=@ParentSysModelId)
insert into @ModelTable
select SysModelId,Name,DisplayOrder
from SysModel
where SysModelId=@ParentSysModelId
delete from @ModelTable where SysModelId=@SysModelId
end fetch next from myCursor into @SysModelId
end
close myCursor
deallocate myCursor
return
end现在要将该函数改写到Oracle中。并且使用方式类似于 select * from GetTopModelOfMenu(id).
函数中的convert改为to_number,bit改为int.
我一点都不会Oracle,上网查了相关资料,但还是不懂。特别是该函数中还有游标,全局临时表之类的。但现在急要。
望会的帮忙改下。
return integer as
vSysModelId varchar2(50);
vParentSysModelId varchar2(50);
cursor myCursor is
select SysModelId from ModelTable; icursor myCursor;
begin
insert into ModelTable
select SysModelId, Name, DisplayOrder
from SysModel
where SysModelId in
(select distinct SysModelId
from SysMapping
where SysMappingId in (select SysMappingId
from SysMenuMapping
where SysMenuId = SysMenuId
and Browser = 'True')
and convert(bit, IsVirtual) = 0); for icursor in myCursor loop
vSysModelId := icursor.SysModelId;
select max(ParentSysModelId)
into vParentSysModelId
from SysModel t
where SysModelId = vSysModelId;
insert into ModelTable
select SysModelId, Name, DisplayOrder
from SysModel
where SysModelId = vParentSysModelId;
delete from ModelTable where SysModelId = vSysModelId;
end loop;
return 0;
exception
when others then
rollback;
return 1;
end;
create type ModelTable as table of row_type1;create function GetTopModelOfMenu(SysMenuID varchar2)
return ModelTable pipelined as v row_type1;
is
SysModelID varchar2(50);
ParentSysModelId varchar2(50);
cursor myCursor is select SysModelId from ModelTable;
begin
insert into ModelTable
select SysModelId,Name,DisplayOrder from SysModel where SysModelId in
(
select distinct SysModelId from SysMapping
where SysMappingId in
(
select SysMappingId from SysMenuMapping where SysMenuId=SysMenuId and Browser='True'
)and to_number()=0
);
open myCursor;
Loop
fetch myCursor into SysModelId;
exit when myCursor%notfound;
select ParentSysModelId into ParentSysModelId from SysModel where SysModelId=SysModelId
if ParentSysModelId is not null
begin
if not Exists(select * from ModelTable where SysModelId=ParentSysModelId)
insert into ModelTable
select SysModelId,Name,DisplayOrder
from SysModel
where SysModelId=ParentSysModelId;
delete from ModelTable where SysModelId=SysModelId;
end;
end if;
end Loop;
close myCursor;
retrun;
end;写了下,估计有错语,你照这个改改吧