oracle10g 函数怎样调用存储过程 返回值?急
解决方案 »
- 求高手:怎么把竖行的数据弄成横行的呢,用kettle工具
- sqlplus position 参数问题请教
- exp导出时出现ora-00904和ora-01003错误,不能成功导出
- 一个简单的SQL查询语句,但是……请各位大虾帮帮俺,谢谢。
- 还有一个问题,在linux下,要对oracle9i进行数据备份,一定要启动Intelligent Agent吗?还有其他方式吗?
- 郁闷,关于存储过程加密的问题!
- 求取ORACLE编程电子书(关于存储过程的编写以及触发器的编写等)
- execute immediate中是不是不能把表名作为参数传递的啊?
- 关于索引中的列顺序?
- 关系数据库设计
- liunx+oracle
- oralce存储过程参数如何得到java中list参数
这是在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(...));