版本：Oracle8I
实现下面功能，请问有什么高效一点的，不用临时表的方法谢谢！
（Oracle8I 里面有什么类似 sys_connect_by_path的函数？）
问题如下：
http://xxxtree.blog.sohu.com/22949969.html[转贴]BOM分解的过程累计运算方法标签： Oracle BOM
看到这个问题的讨论，就摘录下来。虽说看过后就得不难，倒有开拓思路之效。
BOM 结构如图：
A
B C
D E
F
具体单位用量详见表 Bom
Parent Child Usage
A B 2
A C 3
B D 2
B E 3
E F 5用 select P, C, U,
LTRIM(sys_connect_by_path(P, '->'), '->'),
LTRIM(sys_connect_by_path(U, '->'), '->')
from bom
START WITH P = 'A'
CONNECT BY PRIOR C = P
可以得到如下的结果：
P C U Path Qty
A B 2 A 2
B D 2 A->B 2->2
B E 3 A->B 2->3
E F 5 A->B->E 2->3->5
A C 3 A 3那位高手知道如何在sql里面将 qty 这一列乘起来或者直接用sql取出(如下表)：
P C U Path Qty
A B 2 A 2
B D 2 A->B 4
B E 3 A->B 6
E F 5 A->B->E 30
A C 3 A 3 方法一：对于这个问题， 我们是用一个函数实现的：CREATE OR REPLACE FUNCTION str_mult(p_str IN VARCHAR2) RETURN NUMBER
AS
l_str VARCHAR2(1000):=p_str||'*';
l_n NUMBER;
l_data NUMBER:=1;
BEGIN
IF instr(l_str,'*')=1 THEN
l_str:=SUBSTR(l_str,2);
END IF;
LOOP
l_n:=instr(l_str,'*');
EXIT WHEN (NVL(l_n,0)=0);
l_data:=l_data*TO_NUMBER(SUBSTR(l_str,1,l_n-1));
l_str:=SUBSTR(l_str,l_n+1);
END LOOP;
RETURN l_data;
END;如：
SQL> select * from test;PARE CHIL USAGE
---- ---- ----------
A B 2
A C 3
B D 2
B E 3
E F 5SQL> select Parent, Child, Usage,
2 LTRIM(sys_connect_by_path(Parent, '->'), '->') fpath,
3 LTRIM(sys_connect_by_path(Usage, '->'), '->') ausage,
4 str_mult(sys_connect_by_path(Usage, '*')) quate
5 from test
6 START WITH Parent = 'A'
7 CONNECT BY PRIOR Child = Parent;PARE CHIL USAGE FPATH AUSAGE QUATE
---- ---- ---------- ------------------------------ ------------------------------ ----------
A B 2 A 2 2
B D 2 A->B 2->2 4
B E 3 A->B 2->3 6
E F 5 A->B->E 2->3->5 30
A C 3 A 3 3 方法二：CREATE OR REPLACE FUNCTION GET_NUM(I_EXPRESSION IN VARCHAR2) RETURN NUMBER AS
TYPE CURSOR_REF_TYPE IS REF CURSOR;
MY_CURSOR CURSOR_REF_TYPE; V_NUM NUMBER;
V_SQL VARCHAR2(200);
BEGIN
V_SQL := 'SELECT ' || I_EXPRESSION || ' AS NUM FROM DUAL';
OPEN MY_CURSOR FOR V_SQL;
FETCH MY_CURSOR INTO V_NUM;
RETURN V_NUM;
END;
SELECT
PARENT,CHILD,USAGE,
LTRIM(SYS_CONNECT_BY_PATH(PARENT,'->'),'->') FPATH,
LTRIM(SYS_CONNECT_BY_PATH(USAGE,'->'),'->') AUSAGE,
GET_NUM(LTRIM(SYS_CONNECT_BY_PATH(USAGE,'*'),'*')) QUATE
FROM
TEST
START WITH PARENT = 'A'
CONNECT BY PRIOR CHILD = PARENT; 方法三：CREATE OR REPLACE FUNCTION GET_NUM(I_EXPRESSION IN VARCHAR2) RETURN NUMBER AS
TYPE CURSOR_REF_TYPE IS REF CURSOR;
MY_CURSOR CURSOR_REF_TYPE; V_NUM NUMBER;
V_SQL VARCHAR2(200);
BEGIN
EXECUTE IMMEDIATE 'SELECT ' || I_EXPRESSION || ' AS NUM FROM DUAL' INTO V_NUM;
RETURN V_NUM;
END;
SELECT
PARENT,CHILD,USAGE,
LTRIM(SYS_CONNECT_BY_PATH(PARENT,'->'),'->') FPATH,
LTRIM(SYS_CONNECT_BY_PATH(USAGE,'->'),'->') AUSAGE,
GET_NUM(LTRIM(SYS_CONNECT_BY_PATH(USAGE,'*'),'*')) QUATE
FROM
TEST
START WITH PARENT = 'A'
CONNECT BY PRIOR CHILD = PARENT; 方法四：V_SQL := 'SELECT :arg1 INTO :arg2 FROM DUAL';
EXECUTE IMMEDIATE V_SQL USING I_DISTANCE_ALL,OUT V_NUM; 本人从此处转贴：http://oraclespace.bokee.com/viewdiary.12977256.html
实现下面功能，请问有什么高效一点的，不用临时表的方法谢谢！
（Oracle8I 里面有什么类似 sys_connect_by_path的函数？）
问题如下：
http://xxxtree.blog.sohu.com/22949969.html[转贴]BOM分解的过程累计运算方法标签： Oracle BOM
看到这个问题的讨论，就摘录下来。虽说看过后就得不难，倒有开拓思路之效。
BOM 结构如图：
A
B C
D E
F
具体单位用量详见表 Bom
Parent Child Usage
A B 2
A C 3
B D 2
B E 3
E F 5用 select P, C, U,
LTRIM(sys_connect_by_path(P, '->'), '->'),
LTRIM(sys_connect_by_path(U, '->'), '->')
from bom
START WITH P = 'A'
CONNECT BY PRIOR C = P
可以得到如下的结果：
P C U Path Qty
A B 2 A 2
B D 2 A->B 2->2
B E 3 A->B 2->3
E F 5 A->B->E 2->3->5
A C 3 A 3那位高手知道如何在sql里面将 qty 这一列乘起来或者直接用sql取出(如下表)：
P C U Path Qty
A B 2 A 2
B D 2 A->B 4
B E 3 A->B 6
E F 5 A->B->E 30
A C 3 A 3 方法一：对于这个问题， 我们是用一个函数实现的：CREATE OR REPLACE FUNCTION str_mult(p_str IN VARCHAR2) RETURN NUMBER
AS
l_str VARCHAR2(1000):=p_str||'*';
l_n NUMBER;
l_data NUMBER:=1;
BEGIN
IF instr(l_str,'*')=1 THEN
l_str:=SUBSTR(l_str,2);
END IF;
LOOP
l_n:=instr(l_str,'*');
EXIT WHEN (NVL(l_n,0)=0);
l_data:=l_data*TO_NUMBER(SUBSTR(l_str,1,l_n-1));
l_str:=SUBSTR(l_str,l_n+1);
END LOOP;
RETURN l_data;
END;如：
SQL> select * from test;PARE CHIL USAGE
---- ---- ----------
A B 2
A C 3
B D 2
B E 3
E F 5SQL> select Parent, Child, Usage,
2 LTRIM(sys_connect_by_path(Parent, '->'), '->') fpath,
3 LTRIM(sys_connect_by_path(Usage, '->'), '->') ausage,
4 str_mult(sys_connect_by_path(Usage, '*')) quate
5 from test
6 START WITH Parent = 'A'
7 CONNECT BY PRIOR Child = Parent;PARE CHIL USAGE FPATH AUSAGE QUATE
---- ---- ---------- ------------------------------ ------------------------------ ----------
A B 2 A 2 2
B D 2 A->B 2->2 4
B E 3 A->B 2->3 6
E F 5 A->B->E 2->3->5 30
A C 3 A 3 3 方法二：CREATE OR REPLACE FUNCTION GET_NUM(I_EXPRESSION IN VARCHAR2) RETURN NUMBER AS
TYPE CURSOR_REF_TYPE IS REF CURSOR;
MY_CURSOR CURSOR_REF_TYPE; V_NUM NUMBER;
V_SQL VARCHAR2(200);
BEGIN
V_SQL := 'SELECT ' || I_EXPRESSION || ' AS NUM FROM DUAL';
OPEN MY_CURSOR FOR V_SQL;
FETCH MY_CURSOR INTO V_NUM;
RETURN V_NUM;
END;
SELECT
PARENT,CHILD,USAGE,
LTRIM(SYS_CONNECT_BY_PATH(PARENT,'->'),'->') FPATH,
LTRIM(SYS_CONNECT_BY_PATH(USAGE,'->'),'->') AUSAGE,
GET_NUM(LTRIM(SYS_CONNECT_BY_PATH(USAGE,'*'),'*')) QUATE
FROM
TEST
START WITH PARENT = 'A'
CONNECT BY PRIOR CHILD = PARENT; 方法三：CREATE OR REPLACE FUNCTION GET_NUM(I_EXPRESSION IN VARCHAR2) RETURN NUMBER AS
TYPE CURSOR_REF_TYPE IS REF CURSOR;
MY_CURSOR CURSOR_REF_TYPE; V_NUM NUMBER;
V_SQL VARCHAR2(200);
BEGIN
EXECUTE IMMEDIATE 'SELECT ' || I_EXPRESSION || ' AS NUM FROM DUAL' INTO V_NUM;
RETURN V_NUM;
END;
SELECT
PARENT,CHILD,USAGE,
LTRIM(SYS_CONNECT_BY_PATH(PARENT,'->'),'->') FPATH,
LTRIM(SYS_CONNECT_BY_PATH(USAGE,'->'),'->') AUSAGE,
GET_NUM(LTRIM(SYS_CONNECT_BY_PATH(USAGE,'*'),'*')) QUATE
FROM
TEST
START WITH PARENT = 'A'
CONNECT BY PRIOR CHILD = PARENT; 方法四：V_SQL := 'SELECT :arg1 INTO :arg2 FROM DUAL';
EXECUTE IMMEDIATE V_SQL USING I_DISTANCE_ALL,OUT V_NUM; 本人从此处转贴：http://oraclespace.bokee.com/viewdiary.12977256.html
解决方案 »
- 动态SQL执行过程中能否传入一个Cursor参数
- 怎么把两条sql语句查询的结果放在一起(子节点父节点分开查询)
- 弱弱的问个SQL时间格式转化的问题
- pl/sql里不能写select * ,'aa' from 表吗？
- pl/sqldeveloper数据导出excel表问题.在线等,急!!!!!
- 这两个语句的功能为什么不一样呢？
- 如何使oracle的分区功能打开？
- .net连接oracle的问题
- 求各位大大教我怎么用使用 Tkprof 分析 ORACLE 跟踪文件
- 数据库启动不了，报错ORA 48108 ORA48140 ORA48187
- c#中如何调用含有输出类型为table的oracle的存储过程？
- 求问一sql语句问题！
1. 如果是Oracle9i以后的数据库版本, 可以直接使用sys_connect_by_path函数, 这是最简单的方式.
SQL> col emp_name format a15
SQL> col job format a10
SQL> col scbp format a50
SQL> select ename emp_name,job,sys_connect_by_path(ename,'/') scbp
2 from emp
3 start with mgr is null
4 connect by prior empno = mgr
5 /EMP_NAME JOB SCBP
--------------- ---------- --------------------------------------------------
KING PRESIDENT /KING
JONES MANAGER /KING/JONES
SCOTT ANALYST /KING/JONES/SCOTT
ADAMS CLERK /KING/JONES/SCOTT/ADAMS
FORD ANALYST /KING/JONES/FORD
SMITH CLERK /KING/JONES/FORD/SMITH
BLAKE MANAGER /KING/BLAKE
ALLEN SALESMAN /KING/BLAKE/ALLEN
WARD SALESMAN /KING/BLAKE/WARD
MARTIN SALESMAN /KING/BLAKE/MARTIN
TURNER SALESMAN /KING/BLAKE/TURNER
JAMES CLERK /KING/BLAKE/JAMES
CLARK MANAGER /KING/CLARK
MILLER CLERK /KING/CLARK/MILLER 14 rows selected.SQL> spool off2. 使用table对象类型的数据显示. (From Tom).
SQL> create or replace type myArrayType as table of varchar2(30)
2 /Type created.SQL> select rpad('*',2*level,'*')|| ename emp_name, job,
2 cast( multiset( select job
3 from emp e2
4 start with empno = emp.empno
5 connect by prior mgr = empno) as MyArrayType) scbp
6 from emp
7 start with mgr is null
8 connect by prior empno = mgr
9 /EMP_NAME JOB SCBP
--------------- ---------- --------------------------------------------------
**KING PRESIDENT MYARRAYTYPE('PRESIDENT')
****JONES MANAGER MYARRAYTYPE('MANAGER', 'PRESIDENT')
******SCOTT ANALYST MYARRAYTYPE('ANALYST', 'MANAGER', 'PRESIDENT')
********ADAMS CLERK MYARRAYTYPE('CLERK', 'ANALYST', 'MANAGER', 'PRESID
ENT')
******FORD ANALYST MYARRAYTYPE('ANALYST', 'MANAGER', 'PRESIDENT')
********SMITH CLERK MYARRAYTYPE('CLERK', 'ANALYST', 'MANAGER', 'PRESID
ENT')
****BLAKE MANAGER MYARRAYTYPE('MANAGER', 'PRESIDENT')
******ALLEN SALESMAN MYARRAYTYPE('SALESMAN', 'MANAGER', 'PRESIDENT')
******WARD SALESMAN MYARRAYTYPE('SALESMAN', 'MANAGER', 'PRESIDENT')
******MARTIN SALESMAN MYARRAYTYPE('SALESMAN', 'MANAGER', 'PRESIDENT')
******TURNER SALESMAN MYARRAYTYPE('SALESMAN', 'MANAGER', 'PRESIDENT')
******JAMES CLERK MYARRAYTYPE('CLERK', 'MANAGER', 'PRESIDENT')
****CLARK MANAGER MYARRAYTYPE('MANAGER', 'PRESIDENT')
******MILLER CLERK MYARRAYTYPE('CLERK', 'MANAGER', 'PRESIDENT') 14 rows selected.SQL> spool off
3. Here is another 8i method, using a package by Solomon Yakobson: ( 理论上具体怎么说, 还不明白:-) 不过方法比较精巧. )SQL> @aaa.sql
SQL> CREATE OR REPLACE
2 PACKAGE Hierarchy
3 IS
4 TYPE BranchTableType IS TABLE OF VARCHAR2(4000)
5 INDEX BY BINARY_INTEGER;
6 BranchTable BranchTableType;
7 FUNCTION Branch(vLevel IN NUMBER,
8 vValue IN VARCHAR2,
9 vDelimiter IN VARCHAR2 DEFAULT CHR(0))
10 RETURN VARCHAR2;
11 PRAGMA RESTRICT_REFERENCES(Branch,WNDS);
12 END Hierarchy;
13 /Package created.SQL>
SQL> CREATE OR REPLACE
2 PACKAGE BODY Hierarchy
3 IS
4 ReturnValue VARCHAR2(4000);
5 FUNCTION Branch(vLevel IN NUMBER,
6 vValue IN VARCHAR2,
7 vDelimiter IN VARCHAR2 DEFAULT CHR(0))
8 RETURN VARCHAR2
9 IS
10 BEGIN
11 BranchTable(vLevel) := vValue;
12 ReturnValue := vValue;
13 FOR I IN REVERSE 1..vLevel - 1 LOOP
14 ReturnValue := BranchTable(I)|| vDelimiter || ReturnValue;
15 END LOOP;
16 RETURN ReturnValue;
17 END Branch;
18 END Hierarchy;
19 /Package body created.SQL> select rpad('*',2 * level ,'*') || ename emp_name,job,
2 hierarchy.branch (LEVEL, ename, '/') scbp
3 from emp
4 start with mgr is null
5 connect by prior empno = mgr
6 /EMP_NAME JOB SCBP
--------------- ---------- --------------------------------------------------
**KING PRESIDENT KING
****JONES MANAGER KING/JONES
******SCOTT ANALYST KING/JONES/SCOTT
********ADAMS CLERK KING/JONES/SCOTT/ADAMS
******FORD ANALYST KING/JONES/FORD
********SMITH CLERK KING/JONES/FORD/SMITH
****BLAKE MANAGER KING/BLAKE
******ALLEN SALESMAN KING/BLAKE/ALLEN
******WARD SALESMAN KING/BLAKE/WARD
******MARTIN SALESMAN KING/BLAKE/MARTIN
******TURNER SALESMAN KING/BLAKE/TURNER
******JAMES CLERK KING/BLAKE/JAMES
****CLARK MANAGER KING/CLARK
******MILLER CLERK KING/CLARK/MILLER 14 rows selected.SQL> spool off