我下面有一个递归函数，效果很好，但现在我发现有些数据不是唯一的，我需要一种方法来处理它。
FUNCTION calc_cost (model_no_ NUMBER,
revision_ NUMBER,
sequence_no_ IN NUMBER,
currency_ IN VARCHAR2)
RETURN NUMBER
IS
qty_ NUMBER := 0;
cost_ NUMBER := 0;
BEGIN
SELECT NVL (new_qty, qty), purch_cost
INTO qty_, cost_
FROM prod_conf_cost_struct_clv
WHERE model_no = model_no_
AND revision = revision_
AND sequence_no = sequence_no_
AND (purch_curr = currency_
OR purch_curr IS NULL);
IF cost_ IS NULL
THEN
SELECT SUM (calc_cost (model_no,
revision,
sequence_no,
purch_curr))
INTO cost_
FROM prod_conf_cost_struct_clv
WHERE model_no = model_no_
AND revision = revision_
AND (purch_curr = currency_
OR purch_curr IS NULL)
AND part_no IN (SELECT component_part
FROM prod_conf_cost_struct_clv
WHERE model_no = model_no_
AND revision = revision_
AND sequence_no = sequence_no_);
END IF;
RETURN qty_ * cost_;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
RETURN 0;
END calc_cost;
以下条件是此函数失败的地方...part_no in (select component_part...。
示例数据：
rownum., model_no, revision, sequence_no, part_no, component_part, level, cost, purch_curr, qty
1. 62, 1, 00, XXX, ABC, 1, null, null, 1
2. 62, 1, 10, ABC, 123, 2, null, null, 1
3. 62, 1, 20, 123, DEF, 3, null, null, 1
4. 62, 1, 30, DEF, 456, 4, 100, GBP, 1
5. 62, 1, 40, DEF, 789, 4, 50, GBP, 1
6. 62, 1, 50, DEF, 024, 4, 20, GBP, 1
7. 62, 1, 60, ABC, 356, 2, null, null, 2
8. 62, 1, 70, 356, DEF, 3, null, null, 3
9. 62, 1, 80, DEF, 456, 4, 100, GBP, 1
10. 62, 1, 90, DEF, 789, 4, 50, EUR, 1
11. 62, 1, 100, DEF, 024, 4, 20, GBP, 1
如果我要将以下值传递到函数参数中：model_no、修订sequence_no(忽略货币，因为它与问题无关)：
62, 1, 20
我希望它仅汇总第 4-6 行= 170，但它汇总第 4-6 行和第 9-11 = 340 行。
最终，此函数将在下面的 SQL 查询中使用：
SELECT LEVEL,
SYS_CONNECT_BY_PATH (sequence_no, '->') PATH,
calc_cost (model_no,
revision,
sequence_no,
'GBP')
total_gbp
FROM prod_conf_cost_struct_clv
WHERE model_no = 62
AND revision = 1
CONNECT BY PRIOR component_part = part_no
AND PRIOR model_no = 62
AND PRIOR revision = 1
START WITH sequence_no = 20
ORDER BY sequence_no
如您所见，这也会带来component_part = part_no问题 .
更新
除了提供的答案之外，我想我会扩展原始问题，以便也处理货币和数量元素。我已更新示例数据以包括货币和数量。
如果我要将以下值传递到函数参数中：model_no、修订、sequence_no、货币：
Input: 62, 1, 70, EUR
Expected Cost Output: 150
Input: 62, 1, 60, EUR
Expected Cost Output: 300
Input: 62, 1, 60, GBP
Expected Cost Output: 720
任何协助将不胜感激。
提前谢谢。
注意：如果您在运行MATCH_RECOGNIZE内容时遇到问题，可能是因为您运行的是(不太)旧版本的 SQL*Developer。 尝试最新版本或使用SQL*Navigator，TOAD或SQL*Plus。 问题是"？"字符，它混淆了SQL*Developer，因为这是JDBC用于绑定变量的字符。
您遇到了数据模型问题。 也就是说，prod_conf_cost_struct_cvl表中的子记录未显式链接到其父行。 这就是"DEF"子组件引起问题的原因。 如果没有显式链接，就无法干净地计算数据。
您应该更正此数据模型并向每条记录添加一个parent_sequence_no，以便(例如)您可以判断sequence_no80 是sequence_no70 的子项，而不是sequence_no20 的子项。
但是，由于我不能假设您有时间或权限来更改数据模型，因此我将按原样使用数据模型来回答该问题。
首先，让我们向示例数据添加QTY和PURCH_CURR。
with prod_conf_cost_struct_clv ( model_no, revision, sequence_no, part_no, component_part, lvl, cost, qty, purch_curr ) as
(
SELECT 62, 1, 00, 'XXX', 'ABC', 1, null, 1, 'GBP' FROM DUAL UNION ALL
SELECT 62, 1, 10, 'ABC', '123', 2, null, 1, 'GBP' FROM DUAL UNION ALL
SELECT 62, 1, 20, '123', 'DEF', 3, null, 1, 'GBP' FROM DUAL UNION ALL
SELECT 62, 1, 30, 'DEF', '456', 4, 100, 1, 'GBP' FROM DUAL UNION ALL
SELECT 62, 1, 40, 'DEF', '789', 4, 50, 1, 'GBP' FROM DUAL UNION ALL
SELECT 62, 1, 50, 'DEF', '024', 4, 20, 1, 'GBP' FROM DUAL UNION ALL
SELECT 62, 1, 60, 'ABC', '356', 2, null, 1, 'GBP' FROM DUAL UNION ALL
SELECT 62, 1, 70, '356', 'DEF', 3, null, 1, 'GBP' FROM DUAL UNION ALL
SELECT 62, 1, 80, 'DEF', '456', 4, 100, 1, 'GBP' FROM DUAL UNION ALL
SELECT 62, 1, 90, 'DEF', '789', 4, 50, 1, 'GBP' FROM DUAL UNION ALL
SELECT 62, 1, 100, 'DEF', '024', 4, 20, 1, 'GBP' FROM DUAL )
select * from prod_conf_cost_struct_clv;
+----------+----------+-------------+---------+----------------+-----+------+-----+------------+ | MODEL_NO | REVISION | SEQUENCE_NO | PART_NO | COMPONENT_PART | LVL | COST | QTY | PURCH_CURR | +----------+----------+-------------+---------+----------------+-----+------+-----+------------+ | 62 | 1 | 0 | XXX | ABC | 1 | | 1 | GBP | | 62 | 1 | 10 | ABC | 123 | 2 | | 1 | GBP | | 62 | 1 | 20 | 123 | DEF | 3 | | 1 | GBP | | 62 | 1 | 30 | DEF | 456 | 4 | 100 | 1 | GBP | | 62 | 1 | 40 | DEF | 789 | 4 | 50 | 1 | GBP | | 62 | 1 | 50 | DEF | 024 | 4 | 20 | 1 | GBP | | 62 | 1 | 60 | ABC | 356 | 2 | | 1 | GBP | | 62 | 1 | 70 | 356 | DEF | 3 | | 1 | GBP | | 62 | 1 | 80 | DEF | 456 | 4 | 100 | 1 | GBP | | 62 | 1 | 90 | DEF | 789 | 4 | 50 | 1 | GBP | | 62 | 1 | 100 | DEF | 024 | 4 | 20 | 1 | GBP | +----------+----------+-------------+---------+----------------+-----+------+-----+------------+
注意：您没有显示测试数据中如何表示多种货币，因此我在此答案中对该问题的处理可能不正确。
好的，所以我们需要做的第一件事是弄清楚parent_sequence_no的值(它确实应该在你的表格中 - 见上文)。 由于它不在您的表中，我们需要计算它。 我们将它计算为具有最高sequence_no小于当前行的行的sequence_no，并且具有比当前行少 1 的level(我称之为lvl以避免使用 Oracle 关键字)。
为了有效地找到这个值，我们可以使用MATCH_RECOGNIZE功能来描述每个子项的父行应该是什么样子。
我们将使用此新parent_sequence_no列的结果集称为corrected_hierarchy。
with prod_conf_cost_struct_clv ( model_no, revision, sequence_no, part_no, component_part, lvl, cost, qty, purch_curr ) as
(
SELECT 62, 1, 00, 'XXX', 'ABC', 1, null, 1, 'GBP' FROM DUAL UNION ALL
SELECT 62, 1, 10, 'ABC', '123', 2, null, 1, 'GBP' FROM DUAL UNION ALL
SELECT 62, 1, 20, '123', 'DEF', 3, null, 1, 'GBP' FROM DUAL UNION ALL
SELECT 62, 1, 30, 'DEF', '456', 4, 100, 1, 'GBP' FROM DUAL UNION ALL
SELECT 62, 1, 40, 'DEF', '789', 4, 50, 1, 'GBP' FROM DUAL UNION ALL
SELECT 62, 1, 50, 'DEF', '024', 4, 20, 1, 'GBP' FROM DUAL UNION ALL
SELECT 62, 1, 60, 'ABC', '356', 2, null, 1, 'GBP' FROM DUAL UNION ALL
SELECT 62, 1, 70, '356', 'DEF', 3, null, 1, 'GBP' FROM DUAL UNION ALL
SELECT 62, 1, 80, 'DEF', '456', 4, 100, 1, 'GBP' FROM DUAL UNION ALL
SELECT 62, 1, 90, 'DEF', '789', 4, 50, 1, 'GBP' FROM DUAL UNION ALL
SELECT 62, 1, 100, 'DEF', '024', 4, 20, 1, 'GBP' FROM DUAL )
-- Step 1: correct for your data model problem, which is the fact that child rows
-- (e.g., operations 30-50) are not *explicitly* linked to their parent rows (e.g.,
-- operation 20)
, corrected_hierarchy ( model_no, revision, parent_sequence_no, sequence_no, part_no, component_part, lvl, cost, qty, purch_curr ) AS
(
SELECT *
FROM prod_conf_cost_struct_clv c
MATCH_RECOGNIZE (
PARTITION BY model_no, revision
ORDER BY sequence_no desc
MEASURES (P.sequence_no) AS parent_sequence_no,
c.sequence_no AS sequence_no, c.part_no as part_no, c.component_part as component_part, c.lvl as lvl, c.cost as cost, c.qty as qty, c.purch_curr as purch_curr
ONE ROW PER MATCH
AFTER MATCH SKIP TO NEXT ROW
-- C => child row
-- S* => zero or more siblings or children of siblings that might be
-- between child and its parent
-- P? => parent row, which may not exist (e.g., for the root operation)
PATTERN (C S* P?)
DEFINE
C AS 1=1,
S AS S.lvl >= C.lvl,
P AS P.lvl = C.lvl - 1 AND P.component_part = C.part_no
)
ORDER BY model_no, revision, sequence_no )
SELECT * FROM corrected_hierarchy;
+----------+----------+--------------------+-------------+---------+----------------+-----+------+-----+------------+ | MODEL_NO | REVISION | PARENT_SEQUENCE_NO | SEQUENCE_NO | PART_NO | COMPONENT_PART | LVL | COST | QTY | PURCH_CURR | +----------+----------+--------------------+-------------+---------+----------------+-----+------+-----+------------+ | 62 | 1 | | 0 | XXX | ABC | 1 | | 1 | GBP | | 62 | 1 | 0 | 10 | ABC | 123 | 2 | | 1 | GBP | | 62 | 1 | 10 | 20 | 123 | DEF | 3 | | 1 | GBP | | 62 | 1 | 20 | 30 | DEF | 456 | 4 | 100 | 1 | GBP | | 62 | 1 | 20 | 40 | DEF | 789 | 4 | 50 | 1 | GBP | | 62 | 1 | 20 | 50 | DEF | 024 | 4 | 20 | 1 | GBP | | 62 | 1 | 0 | 60 | ABC | 356 | 2 | | 1 | GBP | | 62 | 1 | 60 | 70 | 356 | DEF | 3 | | 1 | GBP | | 62 | 1 | 70 | 80 | DEF | 456 | 4 | 100 | 1 | GBP | | 62 | 1 | 70 | 90 | DEF | 789 | 4 | 50 | 1 | GBP | | 62 | 1 | 70 | 100 | DEF | 024 | 4 | 20 | 1 | GBP | +----------+----------+--------------------+-------------+---------+----------------+-----+------+-----+------------+
现在，如果你愿意，你可以停在那里。 您需要做的就是在calc_cost函数中使用corrected_hierarchy逻辑，将
and part_no in (
select component_part
...
跟
and parent_sequence_no = sequence_no_
但是，正如@Def指出的那样，你真的不需要PL/SQL函数来完成你想要做的事情。
您似乎要做的是打印分层物料清单，其中包含每个项目的级别成本(级别成本是项目的直接和间接子组件的成本)。
下面是一个执行此操作的查询，将所有内容放在一起：
with prod_conf_cost_struct_clv ( model_no, revision, sequence_no, part_no, component_part, lvl, cost, qty, purch_curr ) as
(
SELECT 62, 1, 00, 'XXX', 'ABC', 1, null, 1, 'GBP' FROM DUAL UNION ALL
SELECT 62, 1, 10, 'ABC', '123', 2, null, 1, 'GBP' FROM DUAL UNION ALL
SELECT 62, 1, 20, '123', 'DEF', 3, null, 1, 'GBP' FROM DUAL UNION ALL
SELECT 62, 1, 30, 'DEF', '456', 4, 100, 1, 'GBP' FROM DUAL UNION ALL
SELECT 62, 1, 40, 'DEF', '789', 4, 50, 1, 'GBP' FROM DUAL UNION ALL
SELECT 62, 1, 50, 'DEF', '024', 4, 20, 1, 'GBP' FROM DUAL UNION ALL
SELECT 62, 1, 60, 'ABC', '356', 2, null, 1, 'GBP' FROM DUAL UNION ALL
SELECT 62, 1, 70, '356', 'DEF', 3, null, 1, 'GBP' FROM DUAL UNION ALL
SELECT 62, 1, 80, 'DEF', '456', 4, 100, 1, 'GBP' FROM DUAL UNION ALL
SELECT 62, 1, 90, 'DEF', '789', 4, 50, 1, 'GBP' FROM DUAL UNION ALL
SELECT 62, 1, 100, 'DEF', '024', 4, 20, 1, 'GBP' FROM DUAL )
-- Step 1: correct for your data model problem, which is the fact that child rows
-- (e.g., operations 30-50) are not *explicitly* linked to their parent rows (e.g.,
-- operation 20)
, corrected_hierarchy ( model_no, revision, parent_sequence_no, sequence_no, part_no, component_part, lvl, cost, qty, purch_curr ) AS
(
SELECT *
FROM prod_conf_cost_struct_clv c
MATCH_RECOGNIZE (
PARTITION BY model_no, revision
ORDER BY sequence_no desc
MEASURES (P.sequence_no) AS parent_sequence_no,
c.sequence_no AS sequence_no, c.part_no as part_no, c.component_part as component_part, c.lvl as lvl, c.cost as cost, c.qty as qty, c.purch_curr as purch_curr
ONE ROW PER MATCH
AFTER MATCH SKIP TO NEXT ROW
PATTERN (C S* P?)
DEFINE
C AS 1=1,
S AS S.lvl >= C.lvl,
P AS P.lvl = C.lvl - 1 AND P.component_part = C.part_no
)
ORDER BY model_no, revision, sequence_no ),
sequence_hierarchy_costs as (
SELECT model_no,
revision,
min(sequence_no) sequence_no,
purch_curr,
sum(h.qty * h.cost) hierarchy_cost
FROM corrected_hierarchy h
WHERE 1=1
connect by model_no = prior model_no
and revision = prior revision
and parent_sequence_no = prior sequence_no
group by model_no, revision, connect_by_root sequence_no, purch_curr )
SELECT level,
sys_connect_by_path(h.sequence_no, '->') path,
shc.hierarchy_cost
FROM corrected_hierarchy h
INNER JOIN sequence_hierarchy_costs shc ON shc.model_no = h.model_no and shc.revision = h.revision and shc.sequence_no = h.sequence_no and shc.purch_curr = h.purch_curr
WHERE h.model_no = 62
and h.revision = 1
START WITH h.sequence_no = 20
connect by h.model_no = prior h.model_no
and h.revision = prior h.revision
and h.parent_sequence_no = prior h.sequence_no;
+-------+----------+----------------+ | LEVEL | PATH | HIERARCHY_COST | +-------+----------+----------------+ | 1 | ->20 | 170 | | 2 | ->20->30 | 100 | | 2 | ->20->40 | 50 | | 2 | ->20->50 | 20 | +-------+----------+----------------+
您可以看到，如果一开始parent_sequence_no数据模型中，这将容易得多。
假设sequence_no列严格遵循树深的第一遍历，可以通过两种方式重建缺失的子/父关系。首先，我们可以为每个孩子找到一个父sequence_no，或者为父级的孩子找到一个开放的sequence_no间隔。使用 OP 中提供的数据(无货币列)
with prod_conf_cost_struct_clv (model_no, revision, sequence_no, part_no, component_part, lvl, cost) as
(
SELECT 62, 1, 00, 'XXX', 'ABC', 1, null FROM DUAL UNION ALL
SELECT 62, 1, 10, 'ABC', '123', 2, null FROM DUAL UNION ALL
SELECT 62, 1, 20, '123', 'DEF', 3, null FROM DUAL UNION ALL
SELECT 62, 1, 30, 'DEF', '456', 4, 100 FROM DUAL UNION ALL
SELECT 62, 1, 40, 'DEF', '789', 4, 50 FROM DUAL UNION ALL
SELECT 62, 1, 50, 'DEF', '024', 4, 20 FROM DUAL UNION ALL
SELECT 62, 1, 60, 'ABC', '356', 2, null FROM DUAL UNION ALL
SELECT 62, 1, 70, '356', 'DEF', 3, null FROM DUAL UNION ALL
SELECT 62, 1, 80, 'DEF', '456', 4, 100 FROM DUAL UNION ALL
SELECT 62, 1, 90, 'DEF', '789', 4, 50 FROM DUAL UNION ALL
SELECT 62, 1, 100, 'DEF', '024', 4, 20 FROM DUAL )
, hier as(
SELECT model_no, revision, sequence_no, part_no, component_part, lvl, cost
, (SELECT nvl(min(b.sequence_no), 2147483647/*max integer*/)
FROM prod_conf_cost_struct_clv b
WHERE a.lvl <> b.lvl-1
AND a.sequence_no < b.sequence_no) child_bound_s_n
, (SELECT max(b.sequence_no)
FROM prod_conf_cost_struct_clv b
WHERE a.lvl = b.lvl+1
AND a.sequence_no > b.sequence_no) parent_s_n
FROM prod_conf_cost_struct_clv a
)
SELECT model_no, revision, sequence_no,parent_s_n,child_bound_s_n, part_no, component_part, lvl, cost
FROM hier;
该行的孩子，例如SEQUENCE_NO = 20处于(SEQUENCE_NO, CHILD_BOUND_S_N)开放间隔(20, 60)。
MODEL_NO REVISION SEQUENCE_NO PARENT_S_N CHILD_BOUND_S_N PART_NO COMPONENT_PART LVL COST
62 1 0 20 XXX ABC 1
62 1 10 0 30 ABC 123 2
62 1 20 10 60 123 DEF 3
62 1 30 20 40 DEF 456 4 100
62 1 40 20 50 DEF 789 4 50
62 1 50 20 60 DEF 024 4 20
62 1 60 0 80 ABC 356 2
62 1 70 60 2147483647 356 DEF 3
62 1 80 70 90 DEF 456 4 100
62 1 90 70 100 DEF 789 4 50
62 1 100 70 2147483647 DEF 024 4 20
为了尽量减少对原始calc_cost函数的更改，第二种方法看起来更适合这里。所以，再次没有货币数据
CREATE FUNCTION calc_cost(
model_no_ number,
revision_ number,
sequence_no_ in number
--, currency_ in varchar2
) return number
is
qty_ number := 0;
cost_ number := 0;
lvl_ number := 0;
begin
select 1 /*nvl(new_qty, qty)*/, cost, lvl
into qty_, cost_, lvl_
from prod_conf_cost_struct_clv
where model_no = model_no_
and revision = revision_
and sequence_no = sequence_no_
--and (purch_curr = currency_ or purch_curr is null)
;
if cost_ is null then
select sum(calc_cost(model_no, revision, sequence_no/*, purch_curr*/)) into cost_
from prod_conf_cost_struct_clv
where model_no = model_no_
and revision = revision_
--and (purch_curr = currency_ or purch_curr is null)
and sequence_no > sequence_no_
and sequence_no < (SELECT nvl(min(b.sequence_no), 2147483647)
FROM prod_conf_cost_struct_clv b
WHERE lvl_ <> b.lvl-1
AND sequence_no_ < b.sequence_no);
end if;
return qty_ * cost_;
exception when no_data_found then
return 0;
end calc_cost;
并应用于上述数据
SELECT calc_cost(62,1,20) FROM DUAL;
CALC_COST(62,1,20)
170
在层次结构查询中使用
with hier as(
SELECT model_no, revision, sequence_no, part_no, component_part, lvl, cost
,(SELECT nvl(min(b.sequence_no), 2147483647)
FROM prod_conf_cost_struct_clv b
WHERE a.lvl <> b.lvl-1
AND a.sequence_no < b.sequence_no) child_bound_s_n
FROM prod_conf_cost_struct_clv a
)
select level, sys_connect_by_path(sequence_no, '->') path,
calc_cost(model_no, revision, sequence_no) total_gbp
from hier
where model_no = 62
and revision = 1
connect by sequence_no > prior sequence_no
and sequence_no < prior child_bound_s_n
and prior model_no = 62
and prior revision = 1
start with sequence_no = 20
order by sequence_no;
LEVEL PATH TOTAL_GBP
1 ->20 170
2 ->20->30 100
2 ->20->40 50
2 ->20->50 20
你真的需要这个函数吗？ 似乎您实际上正在寻找的是零件及其每个组件(以及递归的组件)的计算。 试试这个：
SELECT sub.root_part, sum(price) AS TOTAL_PRICE
FROM (SELECT CONNECT_BY_ROOT t.part_no AS ROOT_PART, price
FROM (SELECT DISTINCT model_no, revision, part_no, component_part, price
FROM prod_conf_cost_struct_clv
WHERE model_no = 62
AND revision = 1 )t
CONNECT BY PRIOR component_part = part_no
--START WITH part_no = '123'
) sub
GROUP BY sub.root_part;
我注释掉了 START WITH，但如果你真的在寻找那个 ID，你可以把它放回去。