用树形函数计算零件数

样例数据如下：

CREATE OR REPLACE VIEW t AS
 (SELECT 'B' a, 'A' b, 2 n FROM dual UNION ALL
  SELECT 'C' a, 'A' b, 4 n FROM dual UNION ALL
  SELECT 'D' a, 'B' b, 3 n FROM dual UNION ALL
  SELECT 'E' a, 'B' b, 3 n FROM dual UNION ALL
  SELECT 'F' a, 'B' b, 2 n FROM dual UNION ALL
  SELECT 'G' a, 'C' b, 4 n FROM dual UNION ALL
  SELECT 'H' a, 'C' b, 2 n FROM dual UNION ALL
  SELECT 'I' a, 'H' b, 5 n FROM dual);

要求

做 1个A ，需要 2个 B， 4个 C 
做 1个B ，需要 3个 D， 3个 E， 2个 F
做 1个C ，需要 4个 G， 2个 H
做 1个H ，需要 5个 I  

问  ：做 5个A一共需要多少个材料 ？

可以直接通过树形函数取得叶子节点信息

SQL> WITH x1 AS
  2   (SELECT a, b, n, ltrim(sys_connect_by_path(to_char(n), '*'), '*') AS cnt
  3      FROM t
  4     WHERE connect_by_isleaf = 1
  5     START WITH b = 'A'
  6    CONNECT BY (PRIOR a) = b)
  7  SELECT dbms_aw.eval_number(cnt) AS cnt, a FROM x1;
       CNT A
---------- -
         6 D
         6 E
         4 F
        16 G
        40 I
5 rows selected

这儿用了个小技巧，用dbms_aw.eval_number函数处理了树形函数的返回值，也可以用with语句，这时就不需要树形函数了

SQL> WITH x0(a,b,n) AS
  2   (SELECT a, b, n FROM t WHERE b = 'A'
  3    UNION ALL
  4    SELECT t.a, t.b, t.n * x0.n AS n FROM x0 INNER JOIN t ON t.b = x0.a)
  5  SELECT x0.n,x0.a FROM x0 LEFT JOIN t ON t.b = x0.a WHERE t.b IS NULL
  6   ORDER BY 2;
         N A
---------- -
         6 D
         6 E
         4 F
        16 G
        40 I
5 rows selected