PROSAGA码农传奇-plsql-Oracle SQL / PLSQL：基于货币拆分和求值的分层查询

<div class =“post-text”itemprop =“text”>
  
    恐怕你把所有人搞糊涂了:)
  
  
    虽然你的要求部分难以理解，但我认为如果我不得不处理这样的任务，我会做一件事。我会写从树的任何部分到叶子的递归函数计算成本。
  
  
    以下是与您类似的数据演示：
  
   <pre>
 <code>
 select prod.*, level, sys_connect_by_path(seq, '->') path, 
 calc_cost(comp) total
 from prod connect by prior comp = part
 start with base = 1;

SEQ PART COMP        QTY       COST CURR       BASE AFO RFO   LEVEL PATH             TOTAL
------ ---- ---- ---------- ---------- ---- ---------- --- --- ------- ----------- ----------
     1 A    A1            5                          1               1 ->1                850
     2 A1   A11           3                          0 B             2 ->1->2             114
     3 A11  A111          4          2 EUR           0     B;D       3 ->1->2->3            8
     4 A11  A112          2         15 EUR           0               3 ->1->2->4           30
     5 A1   A12           8          7 EUR           0               2 ->1->5              56
    11 B    B1            5                          1               1 ->11               870
    12 B1   B11           3                          0               2 ->11->12           174
    13 B11  B111          4         12 GBP           0               3 ->11->12->13        48
    14 B11  B112          2          5 GBP           0               3 ->11->12->14        10

</code>
 </pre>
  
    柱
     <code>
 total
 </code>
     包含组件成本，例如
     <code>
 B1
 </code>
     它是
     <code>
 5 * (3 * (4 * 12 + 2 * 5))
 </code>
     是的
     <code>
 870
 </code>
    。
  
  
    函数和示例数据如下所示：
  
   <pre>
 <code>
 create or replace function calc_cost(i_comp in varchar2) return number is
 v_qty number := 0;
 v_cost number := 0;
begin
 select qty, cost into v_qty, v_cost from prod where comp = i_comp;
 if v_cost is null then 
 select sum(calc_cost(comp)) into v_cost from prod where part = i_comp;
 end if;
 return v_qty * v_cost;
exception when no_data_found then 
 return 0;
end;

</code>
 </pre>
  
    数据：
  
   <pre>
 <code>
 create table prod(seq, part, comp, qty, cost, curr, base, afo, rfo) as (
 select 1, 'A', 'A1', 5, null, null, 1, null, null from dual union all
 select 2, 'A1', 'A11', 3, null, null, 0, 'B', null from dual union all
 select 3, 'A11', 'A111', 4, 2, 'EUR', 0, null, 'B;D' from dual union all
 select 4, 'A11', 'A112', 2, 15, 'EUR', 0, null, null from dual union all
 select 5, 'A1', 'A12', 8, 7, 'EUR', 0, null, null from dual union all
 select 11, 'B', 'B1', 5, null, null, 1, null, null from dual union all
 select 12, 'B1', 'B11', 3, null, null, 0, null, null from dual union all
 select 13, 'B11', 'B111', 4, 12, 'GBP', 0, null, null from dual union all
 select 14, 'B11', 'B112', 2, 5, 'GBP', 0, null, null from dual );

</code>
 </pre>
  
    您没有指定是否可以使用不同的货币
     <code>
 part
 </code>
     /
     <code>
 component
 </code>
     如果是这样，将如何输出。无论如何，您可以找到这些货币并独立计算每种货币。你需要在函数中添加第二个参数并编写类似的东西
     <code>
 where part = i_comp and curr = i_curr or curr is null
 </code>
    。
  
  
    也是为了
     <code>
 ask_for_option
 </code>
     /
     <code>
 remove_for_option
 </code>
     你可以处理它们
     <code>
 case when
 </code>
    。
  
  
    我看到你在这个问题上付出了很多努力但是在目前的问题形式中，很难回答得更好。您应该提供样本数据，而不仅仅是图像，并根据用户选择向我们准确显示您期望的输出。
  
  
    但我希望这个功能可以帮助你解决问题。我假设如果
     <code>
 cost
 </code>
     不是null然后我们在leaf中，否则函数递归地查找子组件。
  
  <HR />
  
    的
      编辑：
    </强>
  
  <BLOCKQUOTE>
    
      让我们说seq = 14是欧元而不是英镑
    
  </BLOCKQUOTE>
   <pre>
 <code>
 update prod set curr = 'EUR' where seq = 14;

</code>
 </pre>
  
    正如我所说，确切的解决方案取决于您需要的输出。如果您知道所有可能的货币，那么您可以修改功能来处理货币并显示如下所示的成本：
  
   <pre>
 <code>
 create or replace function calc_cost(i_comp in varchar2, i_curr in varchar2) 
 return number is

v_qty number := 0;
  v_cost number := 0;
begin

select qty, cost into v_qty, v_cost from prod 
    where comp = i_comp and (curr = i_curr or curr is null);

if v_cost is null then 
    select sum(calc_cost(comp, i_curr)) into v_cost 
      from prod where part = i_comp and (curr = i_curr or curr is null);
  end if;
  return v_qty * nvl(v_cost, 0);
exception when no_data_found then 
  return 0;
end;

</code>
 </pre>
  <HR />
   <pre>
 <code>
 select seq, part, comp, qty, cost, curr, 
 calc_cost(comp, 'EUR') eur, calc_cost(comp, 'GBP') gbp
 from prod 
 connect by part = prior comp
 start with part = 'B';

SEQ PART COMP        QTY       COST CURR        EUR        GBP
 ----- ---- ---- ---------- ---------- ---- ---------- ----------
    11 B    B1            5                        150        720
    12 B1   B11           3                         30        144
    13 B11  B111          4         12 GBP           0         48
    14 B11  B112          2          5 EUR          10          0

</code>
 </pre>
  
    部分
     <code>
 B
 </code>
     费用150欧元和720英镑。
  
  
    您可以在有趣的数据部分找到所有不同的货币，将它们与您的表连接并以这种方式调用函数。结果将是每个
     <code>
 seq
 </code>
     你可以获得与不同货币一样多的行数。然后你可以使用
     <code>
 listagg()
 </code>
     并将现值作为
     <code>
 150 EUR; 720 GBP
 </code>
     在一个牢房里。
  
  
    您还可以创建一些类型对象并修改函数以返回元组表（currency，cost_in_this_currency）。问题是你想如何显示数据。或者您可以将值转换为通用货币，但为此您需要每日比率表。
  
</DIV>