几个子查询还可以合并，没时间作比较了。
OPEN p_result FOR
WITH vw_category AS
(SELECT c.*
,SYS_CONNECT_BY_PATH(category_id, '/') AS connection_path
,level
FROM category c
START WITH category_id = :p_category_id
CONNECT BY PRIOR category_id = parent_id
)
,vw_quantity AS
(SELECT product.category_id
,SUM(quantity) AS quantity
FROM vw_category
INNER JOIN product
ON vw_category.category_id = product.category_id
GROUP BY product.category_id
)
,vw_all_quantity AS
(SELECT vw_category.*
,vw_quantity.quantity AS quantity
FROM vw_category
LEFT JOIN vw_quantity
ON vw_category.category_id = vw_quantity.category_id
)
SELECT v1.*
,(SELECT SUM(quantity) FROM vw_all_quantity v2 WHERE v2.connection_path||'/' LIKE v1.connection_path||'/%') AS quantity
FROM vw_all_quantity v1;