首页 » ORACLE 9i-23ai, PostgreSQL/GaussDB » Migrate Oracle to PostgreSQL (系)： start with connect by prior order siblings by
Migrate Oracle to PostgreSQL (系)： start with connect by prior order siblings by
我和我的团队一直在做oracle到国产新创的工程，在oracle数据库中如加载菜单或上下关系的记录时，使用一种start with connect by prior的关键字提供分层查询和遍历方法， 但是对于基于Postgresql的数据库中需要改写，可以借用通用表表达式(CTE)的方法with RECURSIVE（）, 同时对于排序时ORDER SIBLINGS BY可以对分层查询分组排序。这里简单的演示。
造数
create table RECURSIVE_TEST(
EMP_ID int,
MANAGER_ID int,
EMP_NAME varchar2(30)
);
Table created.
insert into recursive_test values
(1 ,0 ,'MANAGER1'),
(2 ,0 ,'MANAGER2'),
(3 ,0 ,'MANAGER3'),
(4 ,0 ,'MANAGER4'),
(5 ,1 ,'emp1'),
(6 ,3 ,'emp2'),
(7 ,4 ,'emp3'),
(8 ,2 ,'emp4'),
(9 ,2 ,'emp5'),
(10 ,3 ,'emp6'),
(11 ,4 ,'emp7'),
(12 ,3 ,'emp8'),
(13 ,4 ,'emp9'),
(14 ,2 ,'emp10'),
(15 ,2 ,'emp11'),
17 (16 ,1 ,'emp12');
16 rows created.
SQL> select * from recursive_test;
EMP_ID MANAGER_ID EMP_NAME
---------- ---------- ------------------------------
1 0 MANAGER1
2 0 MANAGER2
3 0 MANAGER3
4 0 MANAGER4
5 1 emp1
6 3 emp2
7 4 emp3
8 2 emp4
9 2 emp5
10 3 emp6
11 4 emp7
12 3 emp8
13 4 emp9
14 2 emp10
15 2 emp11
16 1 emp12
16 rows selected.
Start with connect by
Start with connect by 在oracle是一种递归查询， 在postgresql中对应WITH RECURSIVE(xx union all xx)的CTE递归查询。
Oracle
SQL>select level,t.* from recursive_test t
start with
MANAGER_ID = '0'
connect by
MANAGER_ID = prior EMP_ID order by emp_id;
LEVEL EMP_ID MANAGER_ID EMP_NAME
---------- ---------- ---------- ------------------------------
1 1 0 MANAGER1
1 2 0 MANAGER2
1 3 0 MANAGER3
1 4 0 MANAGER4
2 5 1 emp1
2 6 3 emp2
2 7 4 emp3
2 8 2 emp4
2 9 2 emp5
2 10 3 emp6
2 11 4 emp7
2 12 3 emp8
2 13 4 emp9
2 14 2 emp10
2 15 2 emp11
2 16 1 emp12
16 rows selected.
Postgresql
postgres=# WITH RECURSIVE T AS
postgres-# (
postgres(# SELECT
postgres(# 1 AS level,
postgres(# A.EMP_ID
postgres(# ,A.MANAGER_ID
postgres(# ,A.EMP_NAME
postgres(# FROM RECURSIVE_TEST A
postgres(# WHERE MANAGER_ID = 0
postgres(# UNION ALL
postgres(# SELECT
postgres(# t.level + 1 AS level,
postgres(# A.EMP_ID
postgres(# ,A.MANAGER_ID
postgres(# ,A.EMP_NAME
postgres(# FROM RECURSIVE_TEST A, T
postgres(# WHERE A.MANAGER_ID = T.EMP_ID
postgres(# )
postgres-# SELECT * FROM T order by emp_id;
level | emp_id | manager_id | emp_name
-------+--------+------------+----------
1 | 1 | 0 | MANAGER1
1 | 2 | 0 | MANAGER2
1 | 3 | 0 | MANAGER3
1 | 4 | 0 | MANAGER4
2 | 5 | 1 | emp1
2 | 6 | 3 | emp2
2 | 7 | 4 | emp3
2 | 8 | 2 | emp4
2 | 9 | 2 | emp5
2 | 10 | 3 | emp6
2 | 11 | 4 | emp7
2 | 12 | 3 | emp8
2 | 13 | 4 | emp9
2 | 14 | 2 | emp10
2 | 15 | 2 | emp11
2 | 16 | 1 | emp12
(16 行记录)
ORDER SIBLINGS BY 子句
ORDER SIBLINGS BY 子句仅在分层查询中有效。可选的 SIBLINGS 关键字指定一种顺序，即首先对父行进行排序，然后对层次结构中每个级别的每个父行的子行进行排序。
Oracle
SQL> select level,t.* from recursive_test t
start with
MANAGER_ID = '0'
connect by
MANAGER_ID = prior EMP_ID order siblings by emp_id
;
LEVEL EMP_ID MANAGER_ID EMP_NAME
---------- ---------- ---------- ------------------------------
1 1 0 MANAGER1
2 5 1 emp1
2 16 1 emp12
1 2 0 MANAGER2
2 8 2 emp4
2 9 2 emp5
2 14 2 emp10
2 15 2 emp11
1 3 0 MANAGER3
2 6 3 emp2
2 10 3 emp6
2 12 3 emp8
1 4 0 MANAGER4
2 7 4 emp3
2 11 4 emp7
2 13 4 emp9
16 rows selected.
Postgresql
postgres=# WITH RECURSIVE T AS
postgres-# (
postgres(# SELECT
postgres(# 1 AS level,
postgres(# EMP_ID as top_id,
postgres(# A.EMP_ID
postgres(# ,A.MANAGER_ID
postgres(# ,A.EMP_NAME
postgres(# FROM RECURSIVE_TEST A
postgres(# WHERE MANAGER_ID = 0
postgres(# UNION ALL
postgres(# SELECT
postgres(# t.level + 1 AS level,
postgres(# t.top_id,
postgres(# A.EMP_ID
postgres(# ,A.MANAGER_ID
postgres(# ,A.EMP_NAME
postgres(# FROM RECURSIVE_TEST A, T
postgres(# WHERE A.MANAGER_ID = T.EMP_ID
postgres(# )
postgres-# SELECT * FROM T order by top_id,emp_id;
level | top_id | emp_id | manager_id | emp_name
-------+--------+--------+------------+----------
1 | 1 | 1 | 0 | MANAGER1
2 | 1 | 5 | 1 | emp1
2 | 1 | 16 | 1 | emp12
1 | 2 | 2 | 0 | MANAGER2
2 | 2 | 8 | 2 | emp4
2 | 2 | 9 | 2 | emp5
2 | 2 | 14 | 2 | emp10
2 | 2 | 15 | 2 | emp11
1 | 3 | 3 | 0 | MANAGER3
2 | 3 | 6 | 3 | emp2
2 | 3 | 10 | 3 | emp6
2 | 3 | 12 | 3 | emp8
1 | 4 | 4 | 0 | MANAGER4
2 | 4 | 7 | 4 | emp3
2 | 4 | 11 | 4 | emp7
2 | 4 | 13 | 4 | emp9
(16 行记录)
还有更多异构数据迁移问题隐藏在项目中，如果您有国产化改造咨询需求，可以考虑联系我们（www.anbob.com首页的联系方式）
微信扫一扫，打赏作者吧～
目前这篇文章还没有评论(Rss)