SQLSERVER2005中的表值函数在ORACLE中怎么写啊?大侠们,帮帮忙.
解决方案 »
- pl/sql 杀死未关闭的session
- 用存储过程来实现 数据迁移 的问题
- 救命:表或视图不存在(单独执行时没有问题,一放入过程就出现问题了)
- exists的用法 例子
- 请问:使用多线程查询数据库oralce,每个线程与oracle一个连接,而我的程序运行时间久了,oracle就会出现监听失败!请高手指点一下,谢谢
- 菜鸟问题:exp 出来的文件不能大于2G吗,还是有参数可设,希大虾指点
- 当ORACLE中抛出“表或视图不存在”的错误的时候,有没有什么办法知道到底是那张表或视图不存在啊?
- 高手请进
- toad恢复数据库的问题
- 求问怎么查询子表的前两行数据,rownum<3
- 触发器调用存储过程中参数问题
- oracle10g 函数怎么样返回一个表?急
create or replace type test_typ as object
(
id number,
val varchar2(20)
)
/
create or replace type test_tbl_typ as table of test_typ
/
from table(cast(B as A))create or replace type CTest as object
(
tid varchar2(6),
tname varchar2(20)
)
/
create or replace type tabCtest is table of cTest/
create or replace function myTest return tabctest
is
Result tabctest := tabctest();
begin
Result := tabctest();
Result.extend;
Result(Result.count) := CTEST(NULL,NULL);
Result(Result.count).tid := '1';
Result(Result.count).tname := 'name1';Result.extend;
Result(Result.count) := CTEST(NULL,NULL);
Result(Result.count).tid := '2';
Result(Result.count).tname := 'name2';return(Result);
end myTest;
/select *
from table(cast(myTest as tabctest))
能不能把Result代替一个临时表,进行insert,delete,update?求救,谢谢!
一般是返回单个值,不然你在select func() from table的时候,返回多个数据值,就不符合关系型数据库结构了.
ALTER FUNCTION [dbo].[getWBSTreeProj] (@projectId bigint)
returns @tree_table table(
id bigint,parentid bigint,leafflag int,releval int,
qty numeric(15,4),price numeric(15,4),
code varchar(100),name varchar(200),unit bigint,taskindex bigint,
projectid bigint,orgid bigint
)
BEGIN --根据projectId找最层的WBS节点
insert into @tree_table
select a.id,a.parentid,1,0,a.qty,a.price,a.code,a.name,a.unit,a.taskindex,a.projectid,a.dutyorgid
from t_pm_plan_wbs a
where a.projectid = @projectId
and a.id not in (select parentid from t_pm_plan_wbs where parentid is not null group by parentid) --反推出全树
declare @releval int
set @releval = 0
while @releval < 20 and @@rowcount > 0
begin
delete from @tree_table where id in ( select parentid from @tree_table a where releval = @releval) insert into @tree_table
select a.id,a.parentid,0, @releval + 1,a.qty,
(select sum(b.qty * b.price) / a.qty from @tree_table b where b.parentid = a.id ) price,
a.code,a.name,a.unit,a.taskindex,a.projectid,a.dutyorgid
from t_pm_plan_wbs a
where a.id in ( select parentid from @tree_table a where releval = @releval) set @releval = @releval + 1
end RETURN
END