oracle迁移父子数据

最新推荐文章于 2021-04-10 14:05:22 发布

凤舞飘伶

最新推荐文章于 2021-04-10 14:05:22 发布

阅读量309

点赞数

分类专栏： oracle

本文链接：https://blog.csdn.net/woshaguayi/article/details/102513980

版权

oracle 专栏收录该内容

64 篇文章 3 订阅

订阅专栏

现有需求如下，业务组织单元表中id字段数据在另外一个系统全部重复，但需要将此业务单元组织导入另一系统

业务组织单元表Isc_Specialorg_Unit

表中存在ID字段为子节点数据，parent_id为父节点数据，orgpath为组织路径

现在做如下操作：

1.创建临时表

create table test as 
        select * 
         from Isc_Specialorg_Unit t

2.在test表中增加三列id1，parent_id1,orgpath1

alter table test add (id1 varchar(32));
alter table test add (parent_id1 varchar(32));
alter table test add (or1 varchar(32));

3.更新id1列为随机数据

update test set id1=SYS_GUID()

4.根据表test在创建临时表test1

create table test1 as  select * from test t

select 'create table test1 as select '|| wm_concat(column_name) || ' from test' sqlStr   
       from user_tab_columns where table_name='test';

5.根据id列，parent_id列，id1，更新parent_id1列的值为id1的值

merge into test k
using (select a.id1,a.id from test1 a) kk
on (k.parent_id=kk.id)
when matched then update set k.parent_id1=kk.id1

6.将test中id列和parent_id列删掉

alter  table test drop column id;
alter  table test drop column parent_id;

7.修改id1为id，pranent_id1为parent_id,获取组织路径并创建表test3

create table test3 as 
select distinct a.id ,sys_connect_by_path(id,'/') orgpath 
             from test a 
   start with a.id='1'
       connect by prior  a.id=a.parent_id

7.根据test3表将组织路径保存至test的orgpath中

merge into test b
using 
(
select a.id,a.orgpath from test3 a
) kk
on (b.id=kk.id)
when matched then update set b.orgpath=kk.orgpath

8.导出表test为sql文件，删掉表 drop test;drop test1;drop test3导入另一系统数据库

备注：

select ... from <TableName>

　　where <Conditional-1>

　　start with <Conditional-2>

　　connect by <Conditional-3>

connect by中的条件就表示了父子之间的连接关系，比如 connect by id=prior pid

LEVEL伪列表示树的深度（或叫高度）

其中 connect by 与 start with 语句摆放的先后顺序不影响查询的结果，[where 条件1]可以不需要，若是出现则要放在 connect by 与 start with 语句之前，否则出错

[where 条件1]、[条件2]、[条件3]各自作用的范围都不相同：

[where 条件1]

是在根据“connect by [条件2] start with [条件3]”选择出来的记录中进行过滤，是针对单条记录的过滤，不会考虑树的结构（最后的过滤）；

[ connect by 条件2]

指定构造树的条件，以及对树分支的过滤条件，在这里执行的过滤会把符合条件的记录及其下的所有子节点都过滤掉；

[ start with 条件3]

限定作为搜索起始点的条件，如果是自上而下的搜索则是限定作为根节点的条件，如果是自下而上的搜索则是限定作为叶子节点的条件；

要根据connect by 从上到下还是从下到上，来确定起始节点，可能是叶节点，也可能是父节点，这些开始节点可以是多个，并且包含这些节点。

层级查询语句（hierarchical query）中，where子句先被执行，再执行CONNECT BY子句以及其附属子句

1、这里说的节点指的是层级查询语句（hierarchical query）中from子句里的表的每一数据行。

2、层级查询语句（hierarchical query）中，CONNECT BY子句是必选的，而START WITH子句是可选的，START WITH子句是用来修饰CONNECT BY子句的，若该子句被省略，则表示所有满足查询条件（即where子句里的条件）的行作为根节点

3、prior关键字放在CONNECT BY子句中。其中用PRIOR表示上一条记录，比如 CONNECT BY PRIOR id = parent_id就是说上一条记录的id 是本条记录的parent_id，即本记录的父亲是上一条记录，prior放在connect by子句连接条件里的哪一边，哪一边就是父节点的编号值的来源，而connect by子句连接条件里等号另一边就是记录一数据行其对应的父节点的编号值

START WITH 子句和CONNECT BY子句是两个相互独立的子句，即并没有规定START WITH 子句出现的列就是要为CONNECT BY子句里那个带有关键字prior的列，START WITH 子句出现的列可以来自表里的任何列，也就是说START WITH 子句出现的列可以没有在START WITH 子句里出现，因为START WITH 子句的作用就是根据START WITH 子句的限定条件来筛选出哪些数据行作为根节点而已
start with子句不是一个独立的子句，即start with子句是一个不能单独出现在SQL语句的子句，必须在SQL语句中出现了connect by子句后，才能出现

当connect by子句的条件里出现的字段是普通字段，不是伪列字段rownum或是level时，connect by子句的条件里的表达式必须要有prior运算符放在一个字段的前面；当connect by子句的条件里出现的是伪列字段rownum或是level时，connect by子句的条件里的表达式不用出现prior运算符