Oracle 사이클을 사용하여 각 ID에 대해 전체 경로 탐색

Oracle 사이클을 사용하여 각 ID에 대해 전체 경로 탐색

ID, prev, current 및 next node(정렬되지 않음)로 구성된 입력 데이터가 있습니다.나는 각 ID의 첫 페이지와 마지막 페이지 사이의 경로를 찾아야 하며, 이 경로는 모든 노드를 포함합니다.예: 내 입력 데이터가 다음과 같은 경우: 첫 번째 열은 ID, 두 번째 열은 prev_node, 세 번째 열은 현재 노드, 네 번째 열은 다음 노드입니다.Prev_node는 시작 값이 비어 있고 다음 노드는 마지막 값이 비어 있습니다.

인풋

id prevcurrent nextabc 11 afefabg보트 1대1 bcd1 bg1 bop1cda1cbg1일1페1 eff수수료 1개1 f f f f f f f f f f ff1 f f fa1 fab1g hi히지 1개1hji1 i ji1 아이크아이킬1 ji1킬로1l men1l na1 mnaab 1개1 op q1pqr1qrs첫번째1stu1tv1uvw1 v w x1wx1 ab

출력은 -와 같이 현재 노드의 경로여야 합니다.

신분증현재1 a1 b1c1d1 a1e1층1e1층1층1층1 a1 b1 b1g1시간1i1j1j1i1i1k1l1m1n1n1 a1 b1o1p1q1r1초1t1u1v1w1x

여기에 비슷한 데이터를 가진 ID가 많이 있을 것입니다. 하나의 ID(1)만 보여주었습니다.또한 저는 실제로 200-500자 길이의 문자열이 될 알파벳을 사용했습니다.SQL 접근법을 거의 수정하지 않고 시도했는데 ID가 100개 이하의 행을 가지면 잘 작동하지만 더 많은 행(긴 문자열을 숫자로 변환한 후에도)에 대한 문자열 연결 오류가 발생합니다.누구든지 그에 대한 강력한 절차 기반 접근 방식을 제안해 줄 수 있습니까?몇 번 시도해봤는데 아이디가 300줄 이상 안 되네요.
아래 코드에서 가끔 발생하는 오류는 " 문자열 연결 결과가 너무 깁니다."입니다.

나의 암호

create or replace procedure pathing 
as 
  type varr is table of varchar(4000);
  visit varr;

  t number;
  --v varchar2(40);
  fp varchar2(1000);
  np varchar2(1000);

  type stype is  record(fp varchar2(1000),np varchar2(1000),t number);
  type sinput is table of stype;
  iarray sinput;

begin

  select id  
  bulk collect into visit 
  from table_source 
  group by id 
  order by count(1) desc;

  delete from table_final;
  commit;

  for k in visit.first .. visit.last loop

    delete from table_temp;
    commit;

    insert into table_temp
    select distinct prev_pg, page_id, next_pg, visit(k)  
    from table_source  
    where visit_id = visit(k) 
    order by prev_pg desc;

    commit;

    insert into table_final 
    WITH t_n AS ( 
      SELECT prev_pg, page_id, next_pg, rownum n FROM table_temp
    ),
    t_br AS (
      SELECT 
        prev_pg,
        page_id,
        '<' || listagg(n, '|<') within GROUP(ORDER BY n) || '|' br,
        COUNT(0) cnt
    FROM 
      t_n
    GROUP BY 
      prev_pg, page_id
    ),
    t_mp AS (
      SELECT 
        '|' || listagg(list) within GROUP(ORDER BY NULL) list
      FROM ( 
        SELECT REPLACE(br, '<') list FROM t_br WHERE cnt > 1
      )
    ),
    t_path(step, page_id, next_pg, used) AS ( 
        SELECT 1, page_id, next_pg, '' 
        FROM t_n 
        WHERE prev_pg is null
      UNION ALL
        SELECT 
         step + 1,
         t_br.page_id,
         t_n.next_pg,
         CASE
           WHEN instr(list, '|' || n || '|') = 0
            THEN used
            ELSE used || n || '|'
          END
       FROM 
         t_mp,
         t_path
         JOIN t_br
           ON next_pg = t_br.page_id AND t_path.page_id = prev_pg
         JOIN t_n
           ON n = regexp_substr(br, '^(<(' || used || '0)\|)*(<(\d+))?', 1, 1, '', 4)
    ) cycle step
    SET is_cycle TO 'Y' DEFAULT 'N'
    SELECT 
      page_id,
      next_pg,
      step,
      visit(k)  
    FROM t_path 
    ORDER BY 1;

    commit;

  end loop;
end;

제 예에 대해 자세히 설명합니다. - ID 1을 예로 든 경우와 같이 각 ID의 전체 경로를 이동하고자 합니다.ID 1의 경우 현재, 이전, 다음 값 세트가 있습니다.따라서 우리는 이 값들을 이용하여 경로를 찾아야 합니다.예를 들어 ID 1의 경우 prev 열이 비어 있으므로 경로가 'a'로 시작합니다.그러면 a의 다음 값이 bi인 것을 볼 수 있습니다. 즉 전류는 a이고 다음 값은 b이므로 id 1의 모든 행에서 a의 이전 값과 b의 전류 값을 검색합니다. 같은 점에서 행의 다음 값을 취하고 이 과정을 반복합니다.예를 들어 여기서 prev a, current band next는 c이므로 다음 null과 마주칠 때까지 전체 경로를 다시 검색합니다.

계층적 쿼리 절을 통한 솔루션은 까다로워 보이지만, 대안으로 기존 PL/SQL 코드를 사용하되 " 문자열 연결 결과가 너무 깁니다"를 방지하기 위해 VARCHAR 필드를 CLOB로 변경해야 합니다.

당신은 당신의 목표를 달성하기위해 당신의 절차에서 다음의 쿼리를 사용할 수 있습니다.

select id,current into v_your_output_collection   from nodes 
where id = :vId
start with prev_node is null 
connect by 
NOCYCLE
prior next_node= current and  prior current = prev_node

Oracle 10g 이상에서 작동하는

언급URL : https://stackoverflow.com/questions/19535554/full-path-traversed-for-each-id-with-cycle-oracle