TN - HDB - 0004 - 润乾迁移HANA递归sql解决方案

润乾迁移HANA递归sql解决方案

By hujue

1. 概述

递归语句是数据库中用于解决父子节点关系的组织结构数据，在oracle及informix中都是采用start with … connect by …的递归语句进行处理；

而在HANA 1.0.33中还支持的start with … connect by …，在1.0.7000版本中已经不被支持，而在润乾报表数据集以及存储过程中大量使用了该递归语句进行处理，这就需要自行进行处理。

2. 解决方案

2.1. 解决方案说明

在该方案中，特别为使用TF_RPT_HN_STAT_BSHZQK进行递归查询而定制了hana函数FUNC_TF_RPT_HN_STAT_BSHZQK，该函数只能查询TF_RPT_HN_STAT_BSHZQK数据表，输入参数为 issue，rpt_id，和dept_id，另外，该函数只能从上到下进行查询。

输入参数如下： ? issue：格式为’120130000’，’320131200’ ，’620131212’等，用于对数据表进行预先筛选； ? rpt_id：格式为’HDY01’等，用于对数据表进行预先筛选； ? dept_id：用于递归sql的起始dept_id

函数调用后，返回一个table类型的数据集，在hana中，table类型数据集可以作为sql中from后的数据来源。

返回table类型字段如下：

? dept_id：nvarchar(100)，单位编码，来源于BSHZQK数据表dept_id字段 ? dept_name：nvarchar(100)，单位名称，来源于BSHZQK数据表dept_id字段 ? px：int，单位排序，来源于BSHZQK数据表px字段

? CONNECT_BY_LEVEL：int，单位级别，在函数中生成，用于标识单位的层级 ? CONNECT_BY_ISLEAF：int，是否叶子节点，是=1，否=0，预留 ? CONNECT_BY_PATH：varchar(5000)，dept_id结合_形成的path

? CONNECT_BY_SORT：varchar(5000)，px结合_形成的sort，用于实现按目录树排序

2.2. SQL调用示例 HANA select * from FUNC_TF_RPT_HN_STAT_BSHZQK ('320131200','HDY01','10000000') ORACLE select * from (select * from TF_RPT_HN_STAT_BSHZQK t where t.issue='320131200' and t.rpt_id='HDY01') a start with a.dept_id = '10000000' connect by prior a.dept_id=a.dept_mng_id order siblings by a.px 如表格中示例所示，'320131200','HDY01'这个2个参数是用于对BSHZQK数据表的预先筛选，'10000000'是用于确定start单位编码

需要注意的是，FUNC_TF_RPT_HN_STAT_BSHZQK(issue_,rpt_id_,dept_id_)中，issue_和rpt_id_不能是表达式，只能是参数，如下

2.3. 润乾调用示例 ? 润乾示例

hana递归示例.raq http://pan.http://www.wodefanwen.com//s/1hqr8NvY

? 数据集SQL设置

? 参数设置

以上润乾调用方法有点特别，关键还是在hana该函数参数不能是表达式，只能对参数在润乾中预先处理。

3. 函数代码

create function FUNC_TF_RPT_HN_STAT_BSHZQK(IN issue_ NVARCHAR(100),IN rpt_id_ NVARCHAR(100),IN root_id_ NVARCHAR(100)) RETURNS table(dept_id nvarchar(100),dept_name nvarchar(100),px int,CONNECT_BY_LEVEL int,CONNECT_BY_ISLEAF int,CONNECT_BY_PATH varchar(5000),CONNECT_BY_SORT varchar(5000)) LANGUAGE SQLSCRIPT AS begin DECLARE node_level INT := 1; DECLARE isleaf INT := 0; DECLARE node_count int := 1; declare px_length int ; declare ss nvarchar(100):= ' issue=''' || :issue_ || ''' and rpt_id='''|| :rpt_id_ ||''''; tmp1 = APPLY_FILTER(TF_RPT_HN_STAT_BSHZQK,:ss) ; select length(max(px)) into px_length from :tmp1; tmp2 = select dept_id,dept_name, :node_level as dept_level, px, to_varchar(dept_id) || ',' as CONNECT_BY_PATH, lpad('1',px_length,'0') || '_' as CONNECT_BY_SORT from :tmp1 where dept_id = :root_id_; tmp3 = select dept_id,dept_name, :node_level as dept_level, px, to_varchar(dept_id) || ',' as CONNECT_BY_PATH, lpad('1',px_length,'0') || '_' as CONNECT_BY_SORT from :tmp1 where dept_id = :root_id_; while node_count > 0 do node_level := node_level + 1; select count(1) into node_count from :tmp2; tmp2 = select a.dept_id,a.dept_name, :node_level as dept_level, a.px, b.CONNECT_BY_PATH || to_varchar(a.dept_id) || ',' as CONNECT_BY_PATH, b.CONNECT_BY_SORT || lpad(a.px,px_length,'0') || '_' as CONNECT_BY_SORT from :tmp1 a , :tmp2 b where a.dept_mng_id = b.dept_id ; tmp3 = select dept_id,dept_name,dept_level,px,CONNECT_BY_PATH,CONNECT_BY_SORT from :tmp3 union all select dept_id,dept_name,dept_level,px,CONNECT_BY_PATH,CONNECT_BY_SORT from :tmp2 ; end while; RETURN select dept_id,dept_name,px,dept_level as CONNECT_BY_LEVEL,null as CONNECT_BY_ISLEAF,CONNECT_BY_PATH,CONNECT_BY_SORT from :tmp3 order by CONNECT_BY_SORT; end;

本文来源：https://www.bwwdw.com/article/owop.html