stored procedures - Update a column with hierarchy structure in oracle -

i have service's table below:

create table service   id number,   name varchar2(50),   parentid number,   pccode varchar(50); 

and data of service's table:

id  name    parentid    pccode 1   test1   0           null 2   test2   0           null 3   test3   1           null 4   test4   1           null 5   test5   1           null 6   test6   2           null 7   test7   2           null 8   test8   2           null 9   test9   2           null 10  test10  3           null 11  test11  3           null 

and want update pccode column. dependent on id column , parentid column. example:

 id = 1, parentid = 0 => pccode = '001'  id = 2, parentid = 0 => pccode = '002'   id = 3, parentid = 1 => pccode = '001001'  id = 4, parentid = 1 => pccode = '001002'  ..... 

update

i have written 2 sp (2 way) update pccode's column, think it's not doing good.

sp1: (done)

create or replace  procedure service_hierarchy  cursor c_service select * service d;  cursor c_parents select k.parentid service k group k.parentid order k.parentid;  begin    obj1 in c_service          loop       update service e set e.pccode = lpad(to_char(rownum), 3, '0') e.parentid = service.parentid;     end loop;      obj2 in c_parents          loop       update service e set e.pccode = (select f.pccode service f f.id = obj2.parentid) || e.pccode e.parentid = obj2.parentid;     end loop;  end; 

sp2: error - it's done. returns result false. , don't know how work correctly.

create or replace  procedure service_hierarchy_2 ( parentid number := 0 )  cursor v_data select * service parentid = parentid;   begin      if (parentid = 0)          update service  set pccode = lpad(rownum , 3, '0')     parentid = parentid;     else         data in v_data         loop             update service  set pccode = (select 1 service id = parentid) || lpad(rownum , 3, '0')             parentid = parentid;              service_hierarchy_2(data.id);          end loop;     end if;  end; 

please me resolve sp2 or 1 way better 2 sp on top. thanks!

it looks you're setting column sorting, , don't need to. also, query set pccode want difficult.

oracle's hierarchical queries sort hierarchy if use order siblings by option. following query read hierarchy , order way believe want (i've added "path" value show full hierarchy branch each row, , how sorts correctly order siblings):

select   id,   name,   parentid,   sys_connect_by_path(id, '/') service start parentid = 0 connect prior id = parentid order siblings id; 

when ran query got following results:

id name     parentid  sys_connect_by_path(id,'/') -- -------- --------  ---------------------------  1 test1           0  /1  3 test3           1  /1/3 10 test10          3  /1/3/10 11 test11          3  /1/3/11  4 test4           1  /1/4  5 test5           1  /1/5  2 test2           0  /2  6 test6           2  /2/6  7 test7           2  /2/7  8 test8           2  /2/8  9 test9           2  /2/9