oracle 的树状查询 效率问题:一个树状结构,每个节点上都对应有一个value,最底层的子节点value 值是手工填报的,然后根据查询出来的子节点的value一级一级的节点往上相加,从而得到每级节点的value值现在只有400条数据,但是用循环要算出来执行完就需要1分钟多。效率比较低,大家有没有效率高点的方法1
-1.1
-1.1.1
-1.2
-1.2.1
-1.2.1.1
-1.2.2
-1.2.3
-1.3....
-1.1
-1.1.1
-1.2
-1.2.1
-1.2.1.1
-1.2.2
-1.2.3
-1.3....
解决方案 »
- 我有一需求,但不知道该如何设计表机构,而且界面也不知如何设计??在线求教。。
- 不想grouping的欄位,但卻要select進去,如何寫呢?
- 请问:PROC 编译C++文件命令:proc CODE=CPP CPP_SUFFIX=cpp iname=filename.pc parse=none 之后的编译命令??
- 怎么在客户端通过P_SQL客户端备份服务器上的Oracle数据库?
- 求ado连接oracle数据库的连接字串,不正确的不要给我
- pdm文件导入
- 查询速度问题!!!!!!!!
- 关于ORACLE8的安装问题.
- 为何在radhat7.3下oracle817数据库启动没起来啊???大家帮个忙啊!急急!!!
- 如何删除主键?
- 执行oemctl start oms后出现以下错误 急啊!!!!!!
- 一个问题大家一起探讨一下!听听各位大哥的高见!
FUNCTION GET_ThisFactValue(pConDtlStruID In number,pFacthBathID In Number) RETURN NUMBER
is
vThisFactValue NUMBER(26,2);
vConDtlStruID number;
CURSOR c_2 IS SELECT con_dtl_stru_id
FROM c2cm_con_dtl_stru
WHERE upper_con_dtl_stru_id = pConDtlStruID ;
BEGIN
/* select sum(e.STD_VALUE) into vThisFactValue from c2cm_eng_fact_v e
where e.con_dtl_stru_id=pConDtlStruID
and e.eng_fact_batch_id =pFacthBathID ;*/
vThisFactValue := nvl(GET_ThisFactValue2(pConDtlStruID,pFacthBathID),0);
open c_2;
fetch c_2 into vConDtlStruId;
while c_2%found loop
vThisFactValue :=vThisFactValue + nvl(GET_ThisFactValue(vConDtlStruId,pFacthBathID),0);
fetch c_2 into vConDtlStruId;
end loop; RETURN vThisFactValue;
EXCEPTION WHEN OTHERS THEN
RETURN 0;
END;
--获取本级完成值
FUNCTION GET_ThisFactValue2(pConDtlStruID In number,pFacthBathID In Number) RETURN NUMBER
is
vThisFactValue number;
begin
select sum(e.STD_VALUE) into vThisFactValue from c2cm_eng_fact e
where C2CM_ENG_P.GET_CON_BILL_MESS(e.CON_DTL_TO_ID,'CON_DTL','N')=pConDtlStruID
and e.eng_fact_batch_id =pFacthBathID ;
return vThisFactValue;
end;
不明白再说,希望对你有帮助。select *
from (select level as lv,
rpad(' ', level * 2, ' ') ||
ltrim(sys_connect_by_path(SUB_TABLE.rn, '-'), '-') as path,
connect_by_isLeaf AS isLeaf
from (
select rn, lag(rn) over(order by rn) next
from (select 'A' || rownum rn from dual connect by rownum < 5)
union all
select rn, lag(rn, 1, 'A1') over(order by rn) next
from (select 'A1' || rownum rn
from dual
connect by rownum < 5)
union all
select rn, lag(rn) over(order by rn) next
from (select 'B' || rownum rn
from dual
connect by rownum < 10)
union all
select rn, lag(rn) over(order by rn) next
from (select 'C' || rownum rn from dual connect by rownum < 8)) sub_table
start with sub_table.next is null
connect by prior sub_table.rn = sub_table.next) TABLEA
;