根据科目计算父科目ID，并递归累计求父科目的金额

最新推荐文章于 2020-12-16 14:50:06 发布

_miccretti

最新推荐文章于 2020-12-16 14:50:06 发布

阅读量435

点赞数

文章标签：数据库

通常情况下，我们会从外部系统或者其他数据源得到以下树形结构的数据，并需要对其进行处理

其中，需要做的处理包括

1.计算每个科目的父科目ID，即PARENT_ID;

2.计算每个科目的ITEM_LEVEL;

3.判断每个节点是否叶子节点；

4.计算父科目的金额。

建表如下

create table CUX.CUX_TEST
(
  account_id   number,
  parent_id    number,
  account_code varchar2(30),
  item_level   number,
  leaf_flag    varchar2(1),
  amount       number
);

导入数据

insert into cux.cux_test (ACCOUNT_ID, PARENT_ID, ACCOUNT_CODE, ITEM_LEVEL, LEAF_FLAG, AMOUNT)
values (1, null, '1', null, null, 0);
insert into cux.cux_test (ACCOUNT_ID, PARENT_ID, ACCOUNT_CODE, ITEM_LEVEL, LEAF_FLAG, AMOUNT)
values (2, null, '1.1', null, null, 0);
insert into cux.cux_test (ACCOUNT_ID, PARENT_ID, ACCOUNT_CODE, ITEM_LEVEL, LEAF_FLAG, AMOUNT)
values (3, null, '1.1.1', null, null, 0);
insert into cux.cux_test (ACCOUNT_ID, PARENT_ID, ACCOUNT_CODE, ITEM_LEVEL, LEAF_FLAG, AMOUNT)
values (4, null, '1.1.1.1', null, null, 200);
insert into cux.cux_test (ACCOUNT_ID, PARENT_ID, ACCOUNT_CODE, ITEM_LEVEL, LEAF_FLAG, AMOUNT)
values (5, null, '1.1.1.2', null, null, 100);
insert into cux.cux_test (ACCOUNT_ID, PARENT_ID, ACCOUNT_CODE, ITEM_LEVEL, LEAF_FLAG, AMOUNT)
values (6, null, '1.1.2', null, null, 0);
insert into cux.cux_test (ACCOUNT_ID, PARENT_ID, ACCOUNT_CODE, ITEM_LEVEL, LEAF_FLAG, AMOUNT)
values (7, null, '1.1.2.1', null, null, 80);
insert into cux.cux_test (ACCOUNT_ID, PARENT_ID, ACCOUNT_CODE, ITEM_LEVEL, LEAF_FLAG, AMOUNT)
values (8, null, '1.1.3', null, null, 50);
insert into cux.cux_test (ACCOUNT_ID, PARENT_ID, ACCOUNT_CODE, ITEM_LEVEL, LEAF_FLAG, AMOUNT)
values (9, null, '2', null, null, 0);
insert into cux.cux_test (ACCOUNT_ID, PARENT_ID, ACCOUNT_CODE, ITEM_LEVEL, LEAF_FLAG, AMOUNT)
values (10, null, '2.1', null, null, 0);
insert into cux.cux_test (ACCOUNT_ID, PARENT_ID, ACCOUNT_CODE, ITEM_LEVEL, LEAF_FLAG, AMOUNT)
values (11, null, '2.1.1', null, null, 40);
insert into cux.cux_test (ACCOUNT_ID, PARENT_ID, ACCOUNT_CODE, ITEM_LEVEL, LEAF_FLAG, AMOUNT)
values (12, null, '2.1.2', null, null, null);
insert into cux.cux_test (ACCOUNT_ID, PARENT_ID, ACCOUNT_CODE, ITEM_LEVEL, LEAF_FLAG, AMOUNT)
values (13, null, '2.1.2.1', null, null, null);

处理数据：

先处理前三步，即：

1.计算每个科目的父科目ID，即PARENT_ID;

2.计算每个科目的ITEM_LEVEL;

3.判断每个节点是否叶子节点；

-- Created on 2018/1/27 by ADMINISTRATOR 
DECLARE

  CURSOR cur_data IS
    SELECT * FROM cux.cux_test ct ORDER BY ct.account_id;

  l_item_level      NUMBER;
  l_parent_id       NUMBER;
  l_delimiter_count NUMBER;
  l_parent_code     cux.cux_test.account_code%TYPE;

BEGIN

  --更新科目层级关系
  --更新 cux_test 中的parent_id,item_level,leaf_flag
  FOR cc IN cur_data LOOP
    --先将所有科目都默认为叶子节点，之后再更新为非叶子节点
    UPDATE cux.cux_test ct
       SET ct.leaf_flag = 'Y'
     WHERE ct.account_id = cc.account_id;
  
    /*l_delimiter_count := nvl(length(regexp_replace(cc.account_code,
                          '[0-9]')),
    0);*/
    --利用分隔符数量+1
    l_item_level := nvl(length(regexp_replace(cc.account_code, '[0-9]')), 0) + 1;
  
    IF l_item_level = 1 THEN
      l_parent_id := 0;
      UPDATE cux.cux_test ct
         SET ct.leaf_flag = 'N'
       WHERE ct.account_id = cc.account_id;
    ELSE
      --非一级科目寻找父级科目CODE
      l_parent_code := substr(cc.account_code,
                              1,
                              instr(cc.account_code, '.', -1) - 1);
      --父科目ID
      SELECT ct.account_id
        INTO l_parent_id
        FROM cux.cux_test ct
       WHERE ct.account_code = l_parent_code;
    
      --更新父科目对应的叶子标记
      UPDATE cux.cux_test ct
         SET ct.leaf_flag = 'N'
       WHERE ct.account_id = l_parent_id;
    
    END IF;
  
    UPDATE cux.cux_test cct
       SET cct.item_level = l_item_level, cct.parent_id = l_parent_id
     WHERE cct.account_id = cc.account_id;
  
  END LOOP;
  COMMIT;

END;

最后处理

4.计算父科目的金额。

贡献一个比较有效的针对这种父子结构的求和累计函数

该计算方式是会把叶子节点和父节点本身的值都计算进去

CREATE OR REPLACE FUNCTION recursive_amount_add(root_id IN NUMBER)
  RETURN NUMBER IS
    /*根据实际情况，判断是否需要做nvl(amount,0)处理，此函数对于子节点为空的仍能优雅的处理*/
    /*此处需要使用UNION ALL，否则当叶子节点金额相等时就不会重复计算了*/
  total NUMBER;
BEGIN
  SELECT SUM(amount)
INTO total
    FROM ((SELECT ct.amount
             FROM cux.cux_test ct
            WHERE ct.account_id = root_id) UNION ALL
          (SELECT recursive_amount_add(ct.account_id) amount
             FROM cux.cux_test ct
            WHERE ct.parent_id = root_id));
  RETURN total;
END;

参考：Recursive sum of values in an hierarchical table in Oracle 10g

如果仅仅需要计算叶子节点之和

CREATE OR REPLACE FUNCTION recursive_amount_add(root_id IN NUMBER)
  RETURN NUMBER IS
  /*根据实际情况，判断是否需要做nvl(amount,0)处理，此函数对于子节点为空的仍能优雅的处理*/
  total NUMBER;
BEGIN
  SELECT SUM(amount)
    INTO total
    FROM ((SELECT ct.amount
             FROM cux.cux_test ct
            WHERE ct.account_id = root_id
              AND ct.leaf_flag = 'Y') UNION ALL
          (SELECT recursive_amount_add(ct.account_id) amount
             FROM cux.cux_test ct
            WHERE ct.parent_id = root_id));
  RETURN total;
END;

查询结果如下：

SELECT ct.*, recursive_amount_add(ct.account_id) recursive_sum FROM cux.cux_test ct

注意：如果需要直接更新amount的值，不能直接使用该函数进行update（在递归过程中amount的值已经改变，函数失效）,需将其作为查询结果集再进行更新。

方式1：直接使用Loop循环：

BEGIN
  FOR cc IN (SELECT ct.account_id,
                    recursive_amount_add(ct.account_id) recursive_sum
               FROM cux.cux_test ct) LOOP
    UPDATE cux.cux_test ct
       SET ct.amount = cc.recursive_sum
     WHERE ct.account_id = cc.account_id;
  END LOOP;
  COMMIT;
END;

方式2：使用数组bulk collect,(多一种方法，可能运用到其他场景)

bulk collect的更多用法，在我的另一篇文章略有简述：使用Bulk Binding批量绑定的模式高效处理ORACLE大量数据

DECLARE
  TYPE recursive_record IS RECORD(
    account_id    NUMBER,
    recursive_sum NUMBER);
  TYPE recursive_type IS TABLE OF recursive_record;
  recur_tab recursive_type;

BEGIN

  SELECT ct.account_id, recursive_amount_add(ct.account_id) recursive_sum
    BULK COLLECT
    INTO recur_tab
    FROM cux.cux_test ct;

  FOR i IN recur_tab.first .. recur_tab.last LOOP
    UPDATE cux.cux_test ct
       SET ct.amount = recur_tab(i).recursive_sum
     WHERE ct.account_id = recur_tab(i).account_id;
  
  END LOOP;
  recur_tab.delete;

  COMMIT;

END;