这是在SQLSERVER中写的一个函数,怎么样才能在ORACLE实现同样的功能(select * from dbo.wbsLevel(2)) set ANSI_NULLS ON set QUOTED_IDENTIFIER ON goALTER FUNCTION [dbo].[wbsLevel](@id bigint) returns @tree_table table(sno int,id bigint,parentid bigint,levelflag int,subnum int,code varchar(100),name varchar(200),unit bigint) BEGIN declare @stack_table table( id bigint,parentid bigint,levelflag int,subnum int,code varchar(100),name varchar(200),unit bigint) declare @wbsid bigint declare @level int declare @sno intinsert into @stack_table select id,projectid,1 as levelflag,(select count(1) from t_pm_plan_wbs c where c.parentid = a.id),code,name,unit from t_pm_plan_wbs a where projectid=@id and parentid is null order by code descDECLARE tcursor CURSOR SCROLL FOR select id,levelflag from @stack_tableOPEN tcursor FETCH LAST from tcursor INTO @wbsid,@levelset @sno =0 while @@fetch_status = 0 begin set @sno = @sno + 1 insert into @tree_table select @sno,* from @stack_table where id = @wbsid delete from @stack_table where id = @wbsid insert into @stack_table select id,parentid,@level + 1 as levelflag,(select count(1) from t_pm_plan_wbs c where c.parentid = a.id) ,code,name,unit from t_pm_plan_wbs a where parentid =@wbsid order by code desc close tcursor OPEN tcursor FETCH LAST from tcursor INTO @wbsid,@level endclose tcursorRETURN END
oracle 中实现表值函数比 sql server 稍显复杂,要定义两个类型、一个函数:首先,定义一个与表记录对应的objcet 数据类型create or replace type xxx_o as object (xxxx number, ... )然后定义一个这个对象类型的集合类型create or replace type xxx_t as table of xxx_o最后定义函数返回指定的类型create or replace function ttt(...) return xxx_t ... ...函数的调用方法select * from table(ttt(...));
这是在SQLSERVER中写的一个函数,怎么样才能在ORACLE实现同样的功能(select * from dbo.wbsLevel(2))
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
goALTER FUNCTION [dbo].[wbsLevel](@id bigint)
returns @tree_table table(sno int,id bigint,parentid bigint,levelflag int,subnum int,code varchar(100),name varchar(200),unit bigint)
BEGIN
declare @stack_table table( id bigint,parentid bigint,levelflag int,subnum int,code varchar(100),name varchar(200),unit bigint)
declare @wbsid bigint
declare @level int
declare @sno intinsert into @stack_table
select id,projectid,1 as levelflag,(select count(1) from t_pm_plan_wbs c where c.parentid = a.id),code,name,unit
from t_pm_plan_wbs a
where projectid=@id and parentid is null
order by code descDECLARE tcursor CURSOR SCROLL FOR
select id,levelflag
from @stack_tableOPEN tcursor
FETCH LAST from tcursor INTO @wbsid,@levelset @sno =0
while @@fetch_status = 0
begin
set @sno = @sno + 1
insert into @tree_table select @sno,* from @stack_table where id = @wbsid delete from @stack_table where id = @wbsid insert into @stack_table
select id,parentid,@level + 1 as levelflag,(select count(1) from t_pm_plan_wbs c where c.parentid = a.id) ,code,name,unit
from t_pm_plan_wbs a
where parentid =@wbsid
order by code desc
close tcursor
OPEN tcursor
FETCH LAST from tcursor INTO @wbsid,@level
endclose tcursorRETURN
END
type xxx_o
as
object
(xxxx number,
...
)然后定义一个这个对象类型的集合类型create or replace
type xxx_t
as table of xxx_o最后定义函数返回指定的类型create or replace
function ttt(...)
return xxx_t
...
...函数的调用方法select * from table(ttt(...));