원본 PL-SQL은 올려주세요

일단 SELECT로 가능한지 확인먼저 하시는게 순서같습니다.

2중 For 문 대신 단순 조인 쿼리로 가능 할 것 같네요.

일반 접근 목록을 가져오는 쿼리이지만, 데이터 입력의 유형을 살펴보니 조금 특이한게,

부모 노드가 허용된 상태에서 몇개의 자식 노드를 허용하지 않게 하면(sel ins upt del = 0,0,0,0),

데이터는 허용되지 않은 자식 노드를 제외한 모든 자식을 알아서 포함 시켜야 합니다.

이게 일반적인 방식인지는 모르겠지만, 이렇게 데이터가 입력되고 있습니다. 

이러한 특성 때문에, NOT IN 과 IN 이 혼재 되어 있고, 조인으로 해결하지 못했습니다.

이 특성을 조인으로 극복한다면, 조인에 대해서 새롭게 배우는 계기가 될 것 같습니다.

원본 쿼리는 이렇습니다.

FOR x IN (SELECT usr_id, usr_kr FROM USR WHERE usr_sts = 1 ORDER BY usr_id) LOOP
    FOR y IN (
    SELECT x.usr_kr, test_parent_elm_id , test_elm_id, test_dsc, 'Individual' as Perm_Grp FROM (       
    SELECT a.test_parent_elm_id , a.test_elm_id, a.test_dsc 
      FROM test_elm a, test_elm_usr b
     WHERE b.usr_id = x.usr_id AND 
           a.test_elm_id = b.test_elm_id AND
           a.test_parent_elm_id IS NULL AND
           (b.sel_checked + b.ins_checked + b.updt_checked + b.dlt_checked ) > 0       
    UNION
    SELECT test_parent_elm_id, test_elm_id, test_dsc
      FROM test_elm 
     WHERE test_elm_id NOT IN (
                SELECT a.test_elm_id
                  FROM test_elm a, test_elm_usr b
                 WHERE b.usr_id = x.usr_id AND 
                       a.test_elm_id = b.test_elm_id AND 
                       a.test_parent_elm_id IS NOT NULL AND
                       (b.sel_checked + b.ins_checked + b.updt_checked + b.dlt_checked ) = 0 
                )
           AND test_parent_elm_id        
           IN (       
           SELECT a.test_elm_id
      FROM test_elm a, test_elm_usr b
     WHERE b.usr_id = x.usr_id AND 
           a.test_elm_id = b.test_elm_id AND
           a.test_parent_elm_id IS NULL AND
           (b.sel_checked + b.ins_checked + b.updt_checked + b.dlt_checked ) > 0)
    ) CONNECT BY test_parent_elm_id = prior test_elm_id
      START WITH test_parent_elm_id is null
      ORDER SIBLINGS BY test_elm_id ) LOOP
      
      DBMS_OUTPUT.Put_Line( x.usr_kr || ',' || y.test_parent_elm_id || ',' || y.test_elm_id || ',' || y.test_dsc || ',' || y.Perm_grp);
      
    END LOOP;
END LOOP;
END;

asktom 에서 저와 비슷한 경우를 물어본 글을 발견했습니다.

이 글에서는 파이프로 출력을 내보내는 방법을 보여주고 있네요.

조인으로 만일 안되면, 파이프 방법으로 시도해 보겠습니다.

http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:4254028121337

1. 쿼리의 의도를 설명해 주세요?
  - 왜 복잡하게 IN 과 NOT IN 을 사용하고 UNION 까지 해야만 하는지?
  - 계층쿼리를 왜 사용하는지?
  - x 의 usr 과 y 의 test_elm_usr 은 다른 테이블인가요?

SELECT *
  FROM (SELECT x.usr_id
             , x.usr_kr
             , a.test_parent_elm_id
             , a.test_elm_id
             , a.test_dsc
             , 'Individual' AS Perm_Grp
          FROM usr          x
             , test_elm     a
             , test_elm_usr b
         WHERE x.usr_id = b.usr_id
           AND a.test_elm_id = b.test_elm_id
           AND x.usr_sts = 1
           AND (b.sel_checked + b.ins_checked + b.updt_checked + b.dlt_checked ) > 0
        )
 START WITH test_parent_elm_id IS NULL
 CONNECT BY PRIOR usr_id = usr_id
        AND PRIOR test_elm_id = test_parent_elm_id
        AND LEVEL <= 2
 ORDER SIBLINGS BY usr_id, test_elm_id
;

답글 감사합니다. 샘플 데이터 없이, 쿼리만 보고 다시 작성하는게 가능하다니, 놀랍네요.

UNION 앞의 SELECT 는 접근 허용된 부모 노드만을 가져오는거라 특별한거는 없고,

UNION 뒤의 SELECT 의 서브 쿼리 2개가 문제인데, 위 댓글에서도 언급한 것처럼

어플리케이션에서 입력이 들어오는 데이터가 특이합니다.

가령, 부모 노드를 허용하고, 자식 노드의 허용은 개별적으로 하나도 안되어 있어도, 다 가져와야 합니다.

그리고, 부모 노드를 허용하고, 자식 노드의 일부만 허용했다가 허용 중지하면, 그 노드가 테이블에 남아있기 때문에,

그 노드를 제거하고, 나머지 모든 자식 노드를 가져와야 합니다.

마찬가지로, 부모 노드가 허용 중지되면, 이 부모 노드가 테이블에 남아있기 때문에, 이 부모 노드도 제외해야 합니다.

왜 데이터가 이렇게 들어오는지는 이해는 하겠는데, 이렇게 기록이 되는 바람에 NOT IN 과 IN 으로 조건별로

걷어내는 작업하게 되었습니다.

위에 올려주신 쿼리는 일반적인 접근 목록은 잘 가져옵니다. 하지만, 자식 노드가 한개만 해지된 경우는,

테이블 test_elm_usr 에 해지된 노드 기록만 있고 허용된 자식 노드가 기록이 되어있지 않기 때문에, 

이 부분을 가져오지 못합니다.

혹시 이런 방식의 접근 목록 관리가 일반적인지, 저희가 좀 다르게 로직이 구성된건지 궁금합니다.

계층 쿼리는 UNION 앞과 뒤의 합한 결과가 부모노드, 자식 노드가 섞여 있어서,

부모노드와 자식노드를 순서대로 묶기 위해서 사용했습니다.

예, x 의 usr 과 다릅니다. y 의 test_elm_usr 은 부모/자식 노드 상관없이 각 사용자에게 할당된 노드 고유 번호만을 갖고 있습니다.

즉, 부모노드,자식노드가 이 테이블에 있더라도, 그 자식이 어느날 해지가 되면, 이 자식노드는 테이블에 남아있지만,

이 테이블에 없는 나머지 자식노드도 모두 가져와야 합니다. 부모노드가 허용되어 있기 때문이죠.

이런 논리를 이해는 하지만 쿼리는 조인으로 불가능한 것처럼 보입니다. 물론, 저의 눈에만 불가능하게 보일 수도 있지만요.

혹시라도 가능하다면, 정말 한수 크게 배울 것 같습니다.

WITH usr(usr_id, usr_kr, usr_sts) AS
(
          SELECT 1, 'a', 1 FROM dual
UNION ALL SELECT 2, 'b', 1 FROM dual
UNION ALL SELECT 3, 'c', 0 FROM dual
)
, test_elm(test_elm_id, test_parent_elm_id, test_dsc) AS
(
          SELECT 10, null, 'Oracle'  FROM dual
UNION ALL SELECT 20,   10, 'Club'    FROM dual
UNION ALL SELECT 30,   10, '.com'    FROM dual
UNION ALL SELECT 40, null, 'Gurubee' FROM dual
UNION ALL SELECT 50,   40, '.net'    FROM dual
)
, test_elm_usr(usr_id, test_elm_id, sel_checked, ins_checked, updt_checked, dlt_checked) AS
(
          SELECT 1, 10, 1, 1, 1, 1 FROM dual
UNION ALL SELECT 1, 40, 1, 1, 1, 1 FROM dual
UNION ALL SELECT 2, 10, 1, 0, 0, 0 FROM dual
UNION ALL SELECT 2, 20, 0, 0, 0, 0 FROM dual
UNION ALL SELECT 2, 40, 1, 0, 0, 0 FROM dual
UNION ALL SELECT 2, 50, 0, 1, 0, 0 FROM dual
UNION ALL SELECT 3, 40, 1, 0, 0, 0 FROM dual
)
SELECT *
  FROM (SELECT a.usr_id
             , a.usr_kr
             , b.test_parent_elm_id
             , b.test_elm_id
             , b.test_dsc
             , 'Individual' AS Perm_Grp
             , SIGN(c.sel_checked + c.ins_checked + c.updt_checked + c.dlt_checked) checked
          FROM usr a
         CROSS JOIN test_elm b
          LEFT OUTER JOIN test_elm_usr c
            ON a.usr_id      = c.usr_id
           AND b.test_elm_id = c.test_elm_id
         WHERE a.usr_sts = 1
        )
 START WITH test_parent_elm_id IS NULL
        AND checked = 1
 CONNECT BY PRIOR usr_id = usr_id
        AND PRIOR test_elm_id = test_parent_elm_id
        AND NVL(checked, 1) != 0
 ORDER SIBLINGS BY usr_id, test_elm_id
;

점점 비슷하게 나오는 결과물을 보니, 진짜 가능도 하겠구나 라는 생각이 듭니다.

쿼리의 신세계를 보는듯한.

정확한 쿼리를 위해 마농님의 WITH 로 생성된 데이터 보다는 현재 회사의 디비 중 일부를 발췌해서 WITH 로 만들어 보겠습니다.

다시 오겠습니다.

정말 대단한 내공이십니다.

네 맞습니다. 데이터를 보여드렸다면, 더 쉬웠을 수도 있었을텐데, 여기에 테이블 데이터를 올려놓을 수도 없고..

저도 좀 답답했습니다. 이런 장황한 설명해도, 찰떡 같이 이해하시고, 쿼리를 만들어 내시는 것에 감탄하고 있습니다.

결과를 보고, 없는 부모 노드도 가져오길래, 왜 그런가 하고 테이블을 들여다 보니, 저희 테이블 데이터에 모순이

있는 것을 발견했습니다. 부모노드의 고유번호가 자식 노드의 고유번호로 사용된 경우가 몇개 발견 되었습니다.

부모 노드 14 는 성공, 18 도 잘 가다가, 자식 노드 105 를 만나니 그걸 부모노드로 인식해서

105 밑에 차식 노드 401,402, 등을 가져오더군요. 근데 자식노드 401, 402는 부모노드 고유값 105를 가지면

안되는 애들인데.. 왜 이런 값이 테이블에 정의되어 있는지 모르겠네요. 어플/디비를 설계하고 개발한 사람이 실수를 한 듯 합니다.

만일 이러한 오류 데이터만 없다면, 이 쿼리는 정확한 데이터를 가져올꺼라고 보여집니다.

참고로 위의 쿼리 결과를 보면,

위에 부모 노드 14 에서 164만 신기하게 빼고 보여주네요. 

164만 테이블에 기록되어 있고 0, 0, 0, 0 허용이 해지 되어 있습니다.

이건 어떤 원리? 명령어로 구현이 되는건가요? 크로스 조인인가요?

위의 쿼리를 나눠서 분석해 보겠지만,

조금 원리를 설명해 주시면 감사하겠습니다.

마농님의 쿼리 덕분으로 데이터의 오류도 찾아냈습니다.

제가 작성한 루프는 결과값 나오는데 6,7초 정도 걸리는데,

마농님 쿼리는 실행하자마자 결과값이 나오네요.

둘다 대략 4000 행 정도인데...모 제꺼는 루프 돌면서 하나씩 찍어내니..ㅠㅠㅠ

가져다 쓰고 싶은데 노드 고유값들이 정의된 탓에 지우지도 못하고..찜찜하네요.

들여다 보니, 어플에서 존재하지도 않는 부모 노드들인데... 아마 초기에 정의하고 쓰지 않은듯..

원본 루프의 경우 2레벨 까지만 가지고 오고 있습니다.
첫 답변에서도 2레벨 까지로 제한 거는 조건 주었구요.
마지막 답변에서는 일부러 2레벨 제한을 풀어 봤습니다.
그래서 3,4,레벨 까지 조회가 되는 거구요.
2레벨까지 원하는지 3,4레벨도 원하는지 명확하게 하시구요.
필요없는 오류 자료를 삭제하기 어렵다면?
Connect by 조건절을 추가해 빼주시면 됩니다.

○ 2레벨 까지만 원한다면?
   AND LEVEL <= 2
○ 부모가 105 인걸 제외하려면
   AND test_parent_elm_id != 105
○ ID 401 ~ 407 인걸 제외하려면
   AND test_elm_id NOT IN (401, 402, 403, 404, 405, 406, 407)

1. 대상자별 모든 노드에 대한 자료를 만들어 냅니다.
  - 대상자 : a.usr_sts = 1
  - cross join
2. 아우터 조인을 통해 권한여부(checked) 항목을 만들어 냅니다.
  - 1 : 권한 있음
  - 0 : 권한 삭제
  - NULL : 권한 설정 안됨
3. 계층 쿼리를 전개합니다.
4. 계층 시작 조건(Start with)
  - 최상위 노드 : test_parent_elm_id IS NULL
  - 권한 있는 노드 : checked = 1
5. 계층 전개 조건(Connect By ~)
  - 같은 유저 안에서 : PRIOR usr_id = usr_id (중요)
  - 부모와 자식이 연결되고 : PRIOR test_elm_id = test_parent_elm_id
  - 권한 삭제된 노드는 제외 : NVL(checked, 1) != 0
  - 2레벨까지만 가져오기 : LEVEL <= 2
6. 계층 정렬
  - ORDER SIBLINGS BY usr_id, test_elm_id