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

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

阅读量：6081 次

发布时间：2019-06-20

本文共 5761 字，大约阅读时间需要 19 分钟。

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

其中，需要做的处理包括

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;

参考：

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

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的更多用法，在我的另一篇文章略有简述：

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;

转载地址：http://oukwa.baihongyu.com/

你可能感兴趣的文章