create or replace procedure proc_query_prior_department
is

Cursor department_list_nosub is
select dep.id,dep.name,dep.parent from department dep where  org_id=7;
union_mc varchar2(200);

begin

    delete from ma_department_role;
    commit;
    for varDep in department_list_nosub loop

       for cur in (select t.parent,t.name from department t start with t.id=varDep.id connect by prior t.parent =t.id order by t.id asc) loop
         if cur.parent is not null then

           if(union_mc is null) then
              union_mc := cur.name;
          else
              union_mc :=union_mc||'/'||cur.name;
           end if;
          end if;
       end loop;
       insert into ma_department_role(id,name)values(varDep.id,union_mc);
       union_mc:=null;
    end loop;
    commit;

end;

您可能感兴趣的文章: