在整個BOM中，動態地指定修改其中某一層，則其子項隨其修改項做出對應變更。
| EMPLOYEE_ID | MANAGER_ID | NAME | CREATE_DATE | LEVEL |
| 100 | NULL | King | 2000-12-12 | 1 |
| 148 | 100 | Cambrault | 2009-01-01 | 2 |
| 172 | 148 | Bates | 2010-11-01 | 3 |
| 169 | 148 | Bloom | 2011-08-21 | 3 |
| 170 | 148 | Fox | 2009-03-01 | 3 |
| 173 | 148 | Kumar | 2009-06-11 | 3 |
| 168 | 148 | Ozer | 2009-10-07 | 3 |
| 171 | 148 | Smith | 2012-05-01 | 3 |
| 102 | 100 | DeHaan | 2012-01-01 | 2 |
| 103 | 102 | Hunold | 2009-12-03 | 3 |
| 105 | 103 | Austin | 2011-01-01 | 4 |
| 104 | 103 | Ernst | 2011-11-01 | 4 |
| 107 | 103 | Lorentz | 2012-11-01 | 4 |
| 106 | 103 | Pataballa | 2009-01-01 | 4 |
| 147 | 100 | Errazuriz | 2010-11-23 | 2 |
| 166 | 147 | Ande | 2010-12-14 | 3 |
| 167 | 147 | Banda | 2009-09-01 | 3 |
--create tablecreate table kol_employees_tmp(employee_id number(5) not null,name varchar2(50) not null,manager_id number(5) ,create_date date)--insert databegininsert into kol_employees_tmp(employee_id, name, manager_id, create_date) values(172,'Bates',148,to_date('2010-11-01', 'YYYY-MM-DD'));insert into kol_employees_tmp(employee_id, name, manager_id, create_date) values(169,'Bloom',148,to_date('2011-08-21', 'YYYY-MM-DD'));insert into kol_employees_tmp(employee_id, name, manager_id, create_date) values(173,'Kumar',148,to_date('2009-06-11', 'YYYY-MM-DD'));insert into kol_employees_tmp(employee_id, name, manager_id, create_date) values(168,'Ozer',148,to_date('2009-10-07', 'YYYY-MM-DD'));insert into kol_employees_tmp(employee_id, name, manager_id, create_date) values(100,'King',null,to_date('2009-01-01', 'YYYY-MM-DD'));insert into kol_employees_tmp(employee_id, name, manager_id, create_date) values(148,'Cambrault',100,to_date('2009-01-01', 'YYYY-MM-DD'));insert into kol_employees_tmp(employee_id, name, manager_id, create_date) values(170,'Fox',148,to_date('2009-03-01', 'YYYY-MM-DD'));insert into kol_employees_tmp(employee_id, name, manager_id, create_date) values(147,'Errazuriz',100,to_date('2010-11-23', 'YYYY-MM-DD'));insert into kol_employees_tmp(employee_id, name, manager_id, create_date) values(167,'Banda',147,to_date('2009-09-01', 'YYYY-MM-DD'));insert into kol_employees_tmp(employee_id, name, manager_id, create_date) values(103,'Hunold',102,to_date('2009-12-03', 'YYYY-MM-DD'));insert into kol_employees_tmp(employee_id, name, manager_id, create_date) values(166,'Ande',147,to_date('2010-12-14', 'YYYY-MM-DD'));insert into kol_employees_tmp(employee_id, name, manager_id, create_date) values(171,'Smith',148,to_date('2012-05-01', 'YYYY-MM-DD'));insert into kol_employees_tmp(employee_id, name, manager_id, create_date) values(102,'DeHaan',100,to_date('2012-01-01', 'YYYY-MM-DD'));insert into kol_employees_tmp(employee_id, name, manager_id, create_date) values(107,'Lorentz',103,to_date('2012-11-01', 'YYYY-MM-DD'));insert into kol_employees_tmp(employee_id, name, manager_id, create_date) values(106,'Pataballa',103,to_date('2009-01-01', 'YYYY-MM-DD'));insert into kol_employees_tmp(employee_id, name, manager_id, create_date) values(105,'Austin',103,to_date('2011-01-01', 'YYYY-MM-DD'));insert into kol_employees_tmp(employee_id, name, manager_id, create_date) values(104,'Ernst',103,to_date('2011-11-01', 'YYYY-MM-DD'));end;
--create temp tablewith kol_employees_tmp as(select '2010-11-01' create_date, 172 employee_id, 148 manager_id, 'Bates' name from dualunion allselect null create_date, 169 employee_id, 148 manager_id, 'Bloom' name from dualunion allselect null create_date, 173 employee_id, 148 manager_id, 'Kumar' name from dualunion allselect null create_date, 168 employee_id, 148 manager_id, 'Ozer' name from dualunion allselect '2000-04-04' create_date, 100 employee_id, null manager_id, 'King' name from dualunion allselect null create_date, 148 employee_id, 100 manager_id, 'Cambrault' name from dualunion allselect null create_date, 170 employee_id, 148 manager_id, 'Fox' name from dualunion allselect '2010-11-23' create_date, 147 employee_id, 100 manager_id, 'Errazuriz' name from dualunion allselect '2009-09-01' create_date, 167 employee_id, 147 manager_id, 'Banda' name from dualunion all--select '2009-12-03' create_date, 103 employee_id, 102 manager_id, 'Hunold' name from dualselect '2013-12-03' create_date, 103 employee_id, 102 manager_id, 'Hunold' name from dualunion allselect null create_date, 166 employee_id, 147 manager_id, 'Ande' name from dualunion allselect null create_date, 171 employee_id, 148 manager_id, 'Smith' name from dualunion allselect '2012-01-01' create_date, 102 employee_id, 100 manager_id, 'DeHaan' name from dualunion allselect null create_date, 107 employee_id, 103 manager_id, 'Lorentz' name from dualunion allselect '2009-01-01' create_date, 106 employee_id, 103 manager_id, 'Pataballa' name from dualunion all--select '2011-01-01' create_date, 105 employee_id, 103 manager_id, 'Austin' name from dualselect '2018-01-01' create_date, 105 employee_id, 103 manager_id, 'Austin' name from dualunion allselect null create_date, 104 employee_id, 103 manager_id, 'Ernst' name from dual),condition_list as(select emp.employee_id, emp.manager_id, emp.name, emp.create_date, level level_idfrom kol_employees_tmp empwhere 1=1and emp.create_date is not null--start with emp.employee_id = :p_root_item -- itme nostart with emp.employee_id = 100 -- itme noconnect by prior emp.employee_id = emp.manager_id),redo_employees as(select t2.employee_id, t2.manager_id, t2.name, t2.create_date, t2.parent_create_date, t2.level_idfrom( select t1.employee_id, t1.manager_id, t1.name, t1.create_date, t1.parent_create_date, t1.level_id, --t1.path, --t1.isleaf, row_number() over(partition by t1.employee_id, t1.manager_id order by t1.parent_create_date desc) maxover_seq from( select emp.employee_id, emp.manager_id, emp.name, emp.create_date, connect_by_root(emp.create_date) parent_create_date, level level_id --sys_connect_by_path(emp.employee_id || decode(emp.manager_id,null, '', '*' || emp.manager_id), '/') path, --connect_by_isleaf isleaf from kol_employees_tmp emp where 1=1 --and emp.employee_id in( 102, 105, 106, 147, 167,172) -- start with emp.employee_id = 100 start with emp.employee_id in(select cl.employee_id from condition_list cl) --in( 102, 105, 106, 147, 167,172, 103) connect by prior emp.employee_id = emp.manager_id --order siblings by emp.name ) t1 ) t2where 1=1and t2.maxover_seq = 1---------------------union allselect ket2.employee_id, ket2.manager_id, ket2.name, ket2.create_date, ket2.create_date parent_create_date, level level_idfrom kol_employees_tmp ket2--start with ket2.employee_id = decode( (select count(1) from condition_list where employee_id = :p_root_item), 0, :p_root_item, '-1') -- itme nostart with ket2.employee_id = decode( (select count(1) from condition_list where employee_id = 100), 0, 100, '-1') -- itme noconnect by ket2.employee_id not in(select cl.employee_id from condition_list cl) --in( 102, 105, 106, 147, 167,172, 103) -- break leveland prior ket2.employee_id = ket2.manager_id )--query process tablesselect emp.employee_id, emp.manager_id, emp.name, emp.create_date, emp.parent_create_date, level level_idfrom redo_employees emp--start with emp.employee_id = :p_root_item -- itme nostart with emp.employee_id = 100 -- itme noconnect by prior emp.employee_id = emp.manager_idorder siblings by emp.name;