데이터베이스 질문과 답변

오라클 쿼리문장을 프로시저에서 VARCHAR2로 연결 연결하여 만들었는데 쿼리내용이 크면 문제가 되네요 1 5 905

by 메로나 [PL/SQL] [2022.01.19 17:06:48]

SELECT 시 컬럼이 특정기간에 따라 변동이 되어야 되어서, VARCHAR2로쿼리를 붙이고 붙이고 했는데요

 

 

 

CREATE OR REPLACE PROCEDURE TEST_PROCEDURE
AS

    -- 특정기간의 일자만큼 가져오는 커서 (12월~1월이면 61개의 행이 YYYYMMDD 로 가져옴)
    CURSOR CURDT(p_from_date IN DATE, p_to_date IN DATE)
    IS
        SELECT 
            cal_dt AS date_YYYYMMDD
        FROM CALENDER
        WHERE 1=1
            AND create_date >= p_from_date
            AND create_date < p_to_date
        ;       

    sql_query CLOB;
    sql_text VARCHAR2(32767);    

BEGIN 


sql_query := sql_query || 'SELECT ';


FOR ref_data IN CURDT(p_from_date, p_to_date) 
LOOP

    IF n_index = 0 THEN
            sql_query := sql_query || '    MAX(u' || ref_data.date_YYYYMMDD || ') AS u' || ref_data.date_YYYYMMDD;
            sql_query := sql_query || '    , MAX(c' || ref_data.date_YYYYMMDD || ') AS c' || ref_data.date_YYYYMMDD;
    ELSE
        
            sql_query := sql_query || '    , MAX(u' || ref_data.date_YYYYMMDD || ') AS u' || ref_data.date_YYYYMMDD;
            sql_query := sql_query || '    , MAX(c' || ref_data.date_YYYYMMDD || ') AS c' || ref_data.date_YYYYMMDD;
    END IF;
    
            
    n_index := n_index + 1;

END LOOP ;

sql_query := sql_query || '    , rk ';
sql_query := sql_query || 'FROM ~~~';
sql_query := sql_query || '    ~~~~~~~~~';
sql_query := sql_query || 'GROUP BY rk ';
sql_query := sql_query || 'ORDER BY rk ASC ';

-- 쿼리를 출력창에 찍어봄
FOR i IN 0..CEIL(DBMS_LOB.GETLENGTH(sql_query)/4000)
LOOP
    DBMS_OUTPUT.PUT_LINE(DBMS_LOB.SUBSTR(sql_query, 4000, (4000 * i) + 1));
END LOOP;


sql_text := DBMS_LOB.SUBSTR(sql_query, DBMS_LOB.GETLENGTH(sql_query), 1);
OPEN p_cursor FOR sql_text;
.....................


이까지 만들어서 돌려보면 쿼리 내용이 출력창에 보이는데 특정 기간에 따라 SELECT문의 컬럼수가 달라지게 되던데요.

    SELECT MAX(u20220101) AS u20220101
         , MAX(c20220101) AS c20220101
         , MAX(u20220102) AS u20220102
         , MAX(c20220102) AS c20220102

    ...............
그걸 툴에서 돌려보면 값이 잘 나옵니다.
그런데 저 CLOB타입의 sql_query 를 VARCHAR2로 가져오와서 OPEN 커서를 해볼려고 하니 안되네요

sql_text := DBMS_LOB.SUBSTR(sql_query, DBMS_LOB.GETLENGTH(sql_query), 1); 제 생각에는 이부분에서 문제가 되는거 같습니다.

PL/SQL에서는 VARCHAR2가 32767까지되는걸로 알지만 아마두 sql_query는 CLOB라텍스트가 다 들어갔고,  해당 쿼리가 그 이상이 되어서 안되는거 같아 sql_text로 못가져오는건지 짤려서 가져오는건지... 저 줄에서 문제가 되는거 같습니다.

방법 없을까요?    


END;
 

by 마농 [2022.01.19 17:29:35]

쿼리를 간결하게 줄여 보세요.
완성된 쿼리를 보여주시면 간결하게 줄여 드릴께요.

by 메로나 [2022.01.19 18:53:20]

SELECT 
    MAX(u202201) AS u202201
    , MAX(c202201) AS c202201
    , MAX(u202202) AS u202202
    , MAX(c202202) AS c202202
    , MAX(u202203) AS u202203
    , MAX(c202203) AS c202203
    , MAX(u202204) AS u202204
    , MAX(c202204) AS c202204
    , MAX(u202205) AS u202205
    , MAX(c202205) AS c202205
    , MAX(u202206) AS u202206
    , MAX(c202206) AS c202206
    , MAX(u202207) AS u202207
    , MAX(c202207) AS c202207
    , MAX(u202208) AS u202208
    , MAX(c202208) AS c202208
    , MAX(u202209) AS u202209
    , MAX(c202209) AS c202209
    , MAX(u202210) AS u202210
    , MAX(c202210) AS c202210
    , MAX(u202211) AS u202211
    , MAX(c202211) AS c202211
    , MAX(u202212) AS u202212
    , MAX(c202212) AS c202212
    , rk
FROM   
(
    SELECT 
        DECODE(date_group,'202201', ent_user, NULL) AS u202201
         , DECODE(date_group,'202201', wr_count, NULL) AS c202201
         , DECODE(date_group,'202202', ent_user, NULL) AS u202202
         , DECODE(date_group,'202202', wr_count, NULL) AS c202202
         , DECODE(date_group,'202203', ent_user, NULL) AS u202203
         , DECODE(date_group,'202203', wr_count, NULL) AS c202203
         , DECODE(date_group,'202204', ent_user, NULL) AS u202204
         , DECODE(date_group,'202204', wr_count, NULL) AS c202204
         , DECODE(date_group,'202205', ent_user, NULL) AS u202205
         , DECODE(date_group,'202205', wr_count, NULL) AS c202205
         , DECODE(date_group,'202206', ent_user, NULL) AS u202206
         , DECODE(date_group,'202206', wr_count, NULL) AS c202206
         , DECODE(date_group,'202207', ent_user, NULL) AS u202207
         , DECODE(date_group,'202207', wr_count, NULL) AS c202207
         , DECODE(date_group,'202208', ent_user, NULL) AS u202208
         , DECODE(date_group,'202208', wr_count, NULL) AS c202208
         , DECODE(date_group,'202209', ent_user, NULL) AS u202209
         , DECODE(date_group,'202209', wr_count, NULL) AS c202209
         , DECODE(date_group,'202210', ent_user, NULL) AS u202210
         , DECODE(date_group,'202210', wr_count, NULL) AS c202210
         , DECODE(date_group,'202211', ent_user, NULL) AS u202211
         , DECODE(date_group,'202211', wr_count, NULL) AS c202211
         , DECODE(date_group,'202212', ent_user, NULL) AS u202212
         , DECODE(date_group,'202212', wr_count, NULL) AS c202212
         , rk
    FROM   
    (
            SELECT 
                 /*+index(sb , BOARD_PK)*/
                 sb.code
                 , TRUNC(TO_CHAR(sb.ent_date, 'YYYYMM')) AS date_group
                 , sb.ent_user
                 , COUNT(sb.ent_user) AS wr_count
                 , RANK() OVER (PARTITION BY TRUNC(TO_CHAR(sb.ent_date, 'YYYYMM')) ORDER BY sb.ent_user ASC) AS RK
            FROM BOARD sb
            WHERE 1=1
                AND sb.code = '14A31'
                AND sb.ent_user LIKE '%' || '' || '%'
            GROUP BY sb.code
                 , TRUNC(TO_CHAR(sb.ent_date, 'YYYYMM'))
                 , sb.ent_user
            HAVING COUNT(sb.ent_user) >= 1
            ORDER BY sb.code
                 , TRUNC(TO_CHAR(sb.ent_date, 'YYYYMM')) ASC
                 , sb.ent_user ASC 
    ) 
)
GROUP BY rk
ORDER BY rk ASC 

 

실쿼리입니다. 상위 MAX부분과 서브의 DECODE()부분 열의 갯수가 달라져야 됩니다. 그래서 procedure에서 돌면서 문자열 합치기하면서 MAX()부분과 DECODE()부분 추가 된 것입니다.

 

 

ENT_DATE                 CODE        ENT_USER       WR_COUNT
2021/01/01 10:03:00      14A31      kwondk**    3261
2021/01/02 19:09:00      14A31      kwondk**    1490
2021/01/02 20:56:00      14A31      kwondk**    3112
2021/01/04 22:45:00      14A31      d85d***     489
2021/01/05 14:38:00      14A31      mixwdc**    1104
2021/01/07 18:28:00      14A31      chuundk**   417
2021/01/07 18:36:00      14A31      azazd***    598
..........

이렇게 일자별로 년말까지 있습니다.

by 마농 [2022.01.19 20:47:16]
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
-- 불필요한 군더더기 코드가 너무 많네요. --
SELECT MIN(DECODE(ym, '202101', u)) u202101, MIN(DECODE(ym, '202101', c)) c202101
     , MIN(DECODE(ym, '202102', u)) u202102, MIN(DECODE(ym, '202102', c)) c202102
     , MIN(DECODE(ym, '202103', u)) u202103, MIN(DECODE(ym, '202103', c)) c202103
     , MIN(DECODE(ym, '202104', u)) u202104, MIN(DECODE(ym, '202104', c)) c202104
     , MIN(DECODE(ym, '202105', u)) u202105, MIN(DECODE(ym, '202105', c)) c202105
     , MIN(DECODE(ym, '202106', u)) u202106, MIN(DECODE(ym, '202106', c)) c202106
     , MIN(DECODE(ym, '202107', u)) u202107, MIN(DECODE(ym, '202107', c)) c202107
     , MIN(DECODE(ym, '202108', u)) u202108, MIN(DECODE(ym, '202108', c)) c202108
     , MIN(DECODE(ym, '202109', u)) u202109, MIN(DECODE(ym, '202109', c)) c202109
     , MIN(DECODE(ym, '202110', u)) u202110, MIN(DECODE(ym, '202110', c)) c202110
     , MIN(DECODE(ym, '202111', u)) u202111, MIN(DECODE(ym, '202111', c)) c202111
     , MIN(DECODE(ym, '202112', u)) u202112, MIN(DECODE(ym, '202112', c)) c202112
  FROM (SELECT TO_CHAR(ent_date, 'yyyymm') ym
             , ent_user u
             , COUNT(*) c
             , ROW_NUMBER() OVER (PARTITION BY TO_CHAR(ent_date, 'yyyymm') ORDER BY ent_user) rn
          FROM board
         WHERE code = '14A31'
           AND ent_user LIKE '%' || '' || '%'
           AND ent_date >= TO_DATE('202101', 'yyyymm')
           AND ent_date < ADD_MONTHS(TO_DATE('202112', 'yyyymm'), 1)
         GROUP BY TO_CHAR(ent_date, 'yyyymm'), ent_user
        )
 GROUP BY rn
 ORDER BY rn
;

알리아스 sb : 굳이 사용할 필요 없음
AS : 생략 가능
TRUNC : 불필요하고 왜 사용하는지 모르겠는 틀린 코드
ASC : 생략 가능
, NULL : 불필요
RANK 보다는 ROW_NUMBER 가 의미상 맞음
날짜 기간 조건 줄 필요 있음
GROUP BY code : 불필요, 어차피 단일 조건에 최종 조회결과에 필요하지 않음
HAVING : 불필요, 결과에 영향이 없는 조건
ORDER BY : 서브쿼리 안에서의 정렬은 불필요
COUNT(컬럼명) : 굳이 컬럼명을 명시할 필요 없음
2단계 인라인뷰 : 한번에 가능
힌트 : 이 쿼리에 맞지 않는 힌트, 게시판의 PK 를 이용하는 쿼리가 아님
긴 컬럼명 : 짧은 알리아스 사용

by 마농 [2022.01.19 21:20:55]
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
-- 결과는 살짝 다르지만 더 간결하게 --
SELECT *
  FROM (SELECT TO_CHAR(ent_date, 'yyyymm') ym
             , ent_user u
             , COUNT(*) c
             , ROW_NUMBER() OVER (PARTITION BY TO_CHAR(ent_date, 'yyyymm') ORDER BY ent_user) rn
          FROM board
         WHERE code = '14A31'
           AND ent_user LIKE '%' || '' || '%'
           AND ent_date >= TO_DATE('202101', 'yyyymm')
           AND ent_date < ADD_MONTHS(TO_DATE('202112', 'yyyymm'), 1)
         GROUP BY TO_CHAR(ent_date, 'yyyymm'), ent_user
        )
 PIVOT (MIN(u) u, MIN(c) c
        FOR ym IN ( 202101
                  , 202102
                  , 202103
                  , 202104
                  , 202105
                  , 202106
                  , 202107
                  , 202108
                  , 202109
                  , 202110
                  , 202111
                  , 202112
                  ) )
 ORDER BY rn
;

 

by 메로나 [2022.01.19 21:57:15]

아...오늘 부족한 머리 너무 써서 두통이 좀 있었습니다... 

그런데...지금 마농님이 간결하게 해주신 2개의 쿼리 보고 좀 뭐랄까 충격상태입니다(*_*);;;

이렇게 간결해지다니 밑에것은 오라클11에서 도는데 결과는 모두 같네요

두통이 충격으로 인해 사라져버렸네요... 새로운 쇼크가 옵니다.

너무 존경스럽습니다 ㅠㅠ 진심입니다. 너무너무너무 감사합니다.

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입