데이터베이스 질문과 답변

동적쿼리 insert관련 질문드립니다. 6

by 위동충 [PL/SQL] dynamic query 동적쿼리 변수insert [2018.12.27 10:34:27] 

CREATE OR REPLACE PROCEDURE SP_CHECK_TEST_02
(
    P_LOG_ID        IN VARCHAR2                 := ''                                  
    ,C_TYPE         IN VARCHAR2                 := 'D'                                 
    ,COL_NAME       IN VARCHAR2                 := ''
    ,BSTART_DATE    IN VARCHAR2                 := ''                    
    ,BEND_DATE      IN VARCHAR2                 := ''                    
    ,WRITER         IN VARCHAR2                 := ''
) 
IS

    /* 사용자정의 변수 */
    i                   NUMBER := 0;    
    V_QUERY             VARCHAR2(4000);
    
    /* array */   

    TYPE TB_TABLE IS TABLE OF OLAP.CHECK_OLAP_TABLE_CM%ROWTYPE;    
    V_AR_TB_TABLE TB_TABLE := TB_TABLE();
   
    TYPE CUR_CHECK IS REF CURSOR;
    V_C_CUR_CHECK CUR_CHECK;    
    
    TYPE CUR_T IS REF CURSOR;
    V_C_CUR_T CUR_T;    
  
  
  --  DECLARE
    
    CURSOR CUR_TABLE IS
      select SVC, TB_NM, SALE_DT, POS_AMT, HPC_AMT, HPC_S_AMT, HPC_U_AMT,USE_FLAG,ETL_DATE from olap.CHECK_OLAP_TABLE_CM;
      
     VR_DEP CUR_TABLE%ROWTYPE;


BEGIN

    OPEN CUR_TABLE;
    
    LOOP
            i := i + 1;
                                  
            V_AR_TB_TABLE.EXTEND;
 
			FETCH CUR_TABLE INTO V_AR_TB_TABLE(i).SVC ,V_AR_TB_TABLE(i).TB_NM, V_AR_TB_TABLE(i).SALE_DT, V_AR_TB_TABLE(i).POS_AMT, V_AR_TB_TABLE(i).HPC_AMT, V_AR_TB_TABLE(i).HPC_S_AMT, V_AR_TB_TABLE(i).HPC_U_AMT, V_AR_TB_TABLE(i).USE_FLAG, V_AR_TB_TABLE(i).ETL_DATE;
            EXIT WHEN CUR_TABLE%NOTFOUND;
       
       V_QUERY:=  'INSERT /*+ APPEND */ INTO   OLAP.CHECK_OLAP_TABLE_TMP (';
       V_QUERY:=  V_QUERY || 'SVC,TB_NM,SALE_DT,COMPANY_CD,COMPANY_NM,POS_AMT,HPC_AMT,HPC_S_AMT,HPC_U_AMT,ETL_DATE)';
       V_QUERY:=  V_QUERY || ' SELECT  /*+ FULL(T01) PARALLEL(8) */';   
       V_QUERY:=  V_QUERY || '   '''||V_AR_TB_TABLE(i).SVC ||''' AS SVC';
       V_QUERY:=  V_QUERY || ' , '''||V_AR_TB_TABLE(i).TB_NM || ''' AS TB_NM';  
       V_QUERY:=  V_QUERY || ' , REPLACE('||V_AR_TB_TABLE(i).SALE_DT || ',''-'','''') AS SALE_DT';  
       V_QUERY:=  V_QUERY || ' , T01.COMPANY_CD AS COMPANY_CD';   
       V_QUERY:=  V_QUERY || ' , T01.COMPANY_NM AS COMPANY_NM';  
       V_QUERY:=  V_QUERY || ' , SUM('||V_AR_TB_TABLE(i).POS_AMT || ') AS POS_AMT';  
       V_QUERY:=  V_QUERY || ' , SUM('||V_AR_TB_TABLE(i).HPC_AMT || ') AS HPC_AMT';  
       V_QUERY:=  V_QUERY || ' , SUM('||V_AR_TB_TABLE(i).HPC_S_AMT || ') AS HPC_S_AMT';  
       V_QUERY:=  V_QUERY || ' , SUM('||V_AR_TB_TABLE(i).HPC_U_AMT || ') AS HPC_U_AMT';   
       V_QUERY:=  V_QUERY || ' , SYSDATE AS ETL_DATE';  
       V_QUERY:=  V_QUERY || ' FROM  '||V_AR_TB_TABLE(i).TB_NM||' T01';
       V_QUERY:=  V_QUERY || ' WHERE REPLACE('||V_AR_TB_TABLE(i).SALE_DT|| ',''-'','''') = ''20181222''';
       V_QUERY:=  V_QUERY || ' AND  T01.COMPANY_CD IN (''PC00'',''SL00'',''BR00'')';
       V_QUERY:=  V_QUERY || ' GROUP BY '||V_AR_TB_TABLE(i).SALE_DT||',COMPANY_CD,COMPANY_NM;';
       V_QUERY:=  V_QUERY || ' COMMIT;';  
          
       DBMS_OUTPUT.PUT_LINE(V_QUERY);  
         
      END LOOP;
    
    /* 실행 */
    
      EXECUTE IMMEDIATE V_QUERY using V_AR_TB_TABLE(i);
    
    CLOSE CUR_TABLE;
    
    V_AR_TB_TABLE.DELETE;

    EXCEPTION
        WHEN OTHERS THEN

            DBMS_OUTPUT.PUT_LINE('ERR CODE    : ' || TO_CHAR(SQLCODE));
            DBMS_OUTPUT.PUT_LINE('ERR MESSAGE : ' || SQLERRM);

             RAISE_APPLICATION_ERROR(-20001, '자식 프로시저 에서 ERROR가 발생되어 프로시저가 종료되었습니다.');

            ROLLBACK;

END;

 이런식으로 V_QUERY 이라는 변수 안에 insert 쿼리를 넣어서  EXECUTE IMMEDIATE 를 이용해서 

여러건의 데이터를 한 테이블에 집어넣으려 하는데 에러가 납니다. 스텝별로 실행해 보면 V_QUERY 이 변수 안에

null값이 라는 메시지가 뜨네요. 로그보면 변수안에 제대로 들어가는듯 합니다. EXECUTE IMMEDIATE 이 구문쪽에서

잘못된거 같은데 도통 모르겠네요. 동적쿼리를 잘 안써버릇하다보니까 저 문법이 정확한건지도 잘 모르겠고,,,

여러 고수님들 답변 부탁드립니다.

 

by 마농 [2018.12.27 11:10:14]

EXECUTE IMMEDIATE 사용하는데
 - USING 절이 불필요하게 사용되었구요.
 - 한문장만 실행해야 하는데, commit 까지 두문장이네요.
전반적으로 이상합니다.
 - 루프 밖에서 Insert 수행하네요? 마지막 한번만 수행되겠네요.
 - 파라미터로 받은 변수는 사용이 안되네요.

by 위동충 [2018.12.27 11:23:50]

- using절 삭제

- commit 삭제

- 루프안에서 insert수행

- 파라미터 변수는,,잘,,,

이런식으로 수정했는데 마찬가지 에러가 나네요. 전반적으로 어떤식으로 수정해야할지,,

감이 잘 안잡히네요 

하고자 하는게 여러 insert 문을 동적쿼리를 이용해서 루프돌리는 형태로 수행하고싶은데,

어떤부분을 수정해야할지 답변 부탁드립니다. 혹은 이런형태의 샘플이라도 있으면 부탁드릴게요.

아무리 구글링해도 적합한 샘플을 못찾고 있습니다. ㅠ

by 마농 [2018.12.27 11:29:29]

DBMS_OUTPUT.PUT_LINE(V_QUERY);  의 결과를 보여주세요.

by 위동충 [2018.12.27 11:46:31] 

-------------------------[Start Time: 2018/12/24 20:06:23]-------------------------
SQL> INSERT /*+ APPEND */ INTO   OLAP.CHECK_OLAP_TABLE_TMP (SVC,TB_NM,SALE_DT,COMPANY_CD,COMPANY_NM,POS_AMT,HPC_AMT,HPC_S_AMT,HPC_U_AMT,ETL_DATE) SELECT  /*+ FULL(T01) PARALLEL(8) */   'OLAP' AS SVC , 'OL_F_CARD_SALE_RATE_D' AS TB_NM , REPLACE(SALE_DT,'-','') AS SALE_DT , T01.COMPANY_CD AS COMPANY_CD , T01.COMPANY_NM AS COMPANY_NM , SUM(TRX_AMT) AS POS_AMT , SUM(0) AS HPC_AMT , SUM(SAVE_PNT) AS HPC_S_AMT , SUM(USE_PNT) AS HPC_U_AMT , SYSDATE AS ETL_DATE FROM  OL_F_CARD_SALE_RATE_D T01 WHERE REPLACE(SALE_DT,'-','') = '20181222' AND  T01.COMPANY_CD IN ('PC00','SL00','BR00') GROUP BY SALE_DT,COMPANY_CD,COMPANY_NM; COMMIT;
INSERT /*+ APPEND */ INTO   OLAP.CHECK_OLAP_TABLE_TMP (SVC,TB_NM,SALE_DT,COMPANY_CD,COMPANY_NM,POS_AMT,HPC_AMT,HPC_S_AMT,HPC_U_AMT,ETL_DATE) SELECT  /*+ FULL(T01) PARALLEL(8) */   'OLAP' AS SVC , 'OL_F_HPC_DAILY_CLOSE_B' AS TB_NM , REPLACE(TRAN_DATE,'-','') AS SALE_DT , T01.COMPANY_CD AS COMPANY_CD , T01.COMPANY_NM AS COMPANY_NM , SUM(POS_SALE_AMT) AS POS_AMT , SUM(AUTH_AMT) AS HPC_AMT , SUM(AUTH_S_AMT) AS HPC_S_AMT , SUM(AUTH_U_AMT) AS HPC_U_AMT , SYSDATE AS ETL_DATE FROM  OL_F_HPC_DAILY_CLOSE_B T01 WHERE REPLACE(TRAN_DATE,'-','') = '20181222' AND  T01.COMPANY_CD IN ('PC00','SL00','BR00') GROUP BY TRAN_DATE,COMPANY_CD,COMPANY_NM; COMMIT;
INSERT /*+ APPEND */ INTO   OLAP.CHECK_OLAP_TABLE_TMP (SVC,TB_NM,SALE_DT,COMPANY_CD,COMPANY_NM,POS_AMT,HPC_AMT,HPC_S_AMT,HPC_U_AMT,ETL_DATE) SELECT  /*+ FULL(T01) PARALLEL(8) */   'OLAP' AS SVC , 'OL_F_HPC_DAILY_CLOSE_D' AS TB_NM , REPLACE(TRAN_DATE,'-','') AS SALE_DT , T01.COMPANY_CD AS COMPANY_CD , T01.COMPANY_NM AS COMPANY_NM , SUM(POS_SALE_AMT) AS POS_AMT , SUM(HPC_AUTH_AMT) AS HPC_AMT , SUM(HPC_SAV_AMT) AS HPC_S_AMT , SUM(HPC_USE_AMT) AS HPC_U_AMT , SYSDATE AS ETL_DATE FROM  OL_F_HPC_DAILY_CLOSE_D T01 WHERE REPLACE(TRAN_DATE,'-','') = '20181222' AND  T01.COMPANY_CD IN ('PC00','SL00','BR00') GROUP BY TRAN_DATE,COMPANY_CD,COMPANY_NM; COMMIT;
INSERT /*+ APPEND */ INTO   OLAP.CHECK_OLAP_TABLE_TMP (SVC,TB_NM,SALE_DT,COMPANY_CD,COMPANY_NM,POS_AMT,HPC_AMT,HPC_S_AMT,HPC_U_AMT,ETL_DATE) SELECT  /*+ FULL(T01) PARALLEL(8) */   'OLAP' AS SVC , 'OL_F_HPC_DAILY_CLOSE_P' AS TB_NM , REPLACE(TRAN_DATE,'-','') AS SALE_DT , T01.COMPANY_CD AS COMPANY_CD , T01.COMPANY_NM AS COMPANY_NM , SUM(0) AS POS_AMT , SUM(AUTH_AMT) AS HPC_AMT , SUM(AUTH_S_AMT) AS HPC_S_AMT , SUM(AUTH_U_AMT) AS HPC_U_AMT , SYSDATE AS ETL_DATE FROM  OL_F_HPC_DAILY_CLOSE_P T01 WHERE REPLACE(TRAN_DATE,'-','') = '20181222' AND  T01.COMPANY_CD IN ('PC00','SL00','BR00') GROUP BY TRAN_DATE,COMPANY_CD,COMPANY_NM; COMMIT;
INSERT /*+ APPEND */ INTO   OLAP.CHECK_OLAP_TABLE_TMP (SVC,TB_NM,SALE_DT,COMPANY_CD,COMPANY_NM,POS_AMT,HPC_AMT,HPC_S_AMT,HPC_U_AMT,ETL_DATE) SELECT  /*+ FULL(T01) PARALLEL(8) */   'OLAP' AS SVC , 'OL_F_HPC_DAILY_CL_STOR_TYPE' AS TB_NM , REPLACE(TRAN_DT,'-','') AS SALE_DT , T01.COMPANY_CD AS COMPANY_CD , T01.COMPANY_NM AS COMPANY_NM , SUM(SALE_AMT) AS POS_AMT , SUM(AUTH_AMT) AS HPC_AMT , SUM(AUTH_S_AMT) AS HPC_S_AMT , SUM(AUTH_U_AMT) AS HPC_U_AMT , SYSDATE AS ETL_DATE FROM  OL_F_HPC_DAILY_CL_STOR_TYPE T01 WHERE REPLACE(TRAN_DT,'-','') = '20181222' AND  T01.COMPANY_CD IN ('PC00','SL00','BR00') GROUP BY TRAN_DT,COMPANY_CD,COMPANY_NM; COMMIT;
INSERT /*+ APPEND */ INTO   OLAP.CHECK_OLAP_TABLE_TMP (SVC,TB_NM,SALE_DT,COMPANY_CD,COMPANY_NM,POS_AMT,HPC_AMT,HPC_S_AMT,HPC_U_AMT,ETL_DATE) SELECT  /*+ FULL(T01) PARALLEL(8) */   'OLAP' AS SVC , 'OL_F_HPC_DAILY_CL_ST_SA' AS TB_NM , REPLACE(TRAN_DT,'-','') AS SALE_DT , T01.COMPANY_CD AS COMPANY_CD , T01.COMPANY_NM AS COMPANY_NM , SUM(0) AS POS_AMT , SUM(AUTH_AMT) AS HPC_AMT , SUM(AUTH_S_AMT) AS HPC_S_AMT , SUM(AUTH_U_AMT) AS HPC_U_AMT , SYSDATE AS ETL_DATE FROM  OL_F_HPC_DAILY_CL_ST_SA T01 WHERE REPLACE(TRAN_DT,'-','') = '20181222' AND  T01.COMPANY_CD IN ('PC00','SL00','BR00') GROUP BY TRAN_DT,COMPANY_CD,COMPANY_NM; COMMIT;
INSERT /*+ APPEND */ INTO   OLAP.CHECK_OLAP_TABLE_TMP (SVC,TB_NM,SALE_DT,COMPANY_CD,COMPANY_NM,POS_AMT,HPC_AMT,HPC_S_AMT,HPC_U_AMT,ETL_DATE) SELECT  /*+ FULL(T01) PARALLEL(8) */   'OLAP' AS SVC , 'OL_F_HPC_DAILY_SEX_AGE' AS TB_NM , REPLACE(TRAN_DATE,'-','') AS SALE_DT , T01.COMPANY_CD AS COMPANY_CD , T01.COMPANY_NM AS COMPANY_NM , SUM(0) AS POS_AMT , SUM(HPC_AMT) AS HPC_AMT , SUM(SAVE_PNT) AS HPC_S_AMT , SUM(USE_PNT) AS HPC_U_AMT , SYSDATE AS ETL_DATE FROM  OL_F_HPC_DAILY_SEX_AGE T01 WHERE REPLACE(TRAN_DATE,'-','') = '20181222' AND  T01.COMPANY_CD IN ('PC00','SL00','BR00') GROUP BY TRAN_DATE,COMPANY_CD,COMPANY_NM; COMMIT;
INSERT /*+ APPEND */ INTO   OLAP.CHECK_OLAP_TABLE_TMP (SVC,TB_NM,SALE_DT,COMPANY_CD,COMPANY_NM,POS_AMT,HPC_AMT,HPC_S_AMT,HPC_U_AMT,ETL_DATE) SELECT  /*+ FULL(T01) PARALLEL(8) */   'OLAP' AS SVC , 'OL_F_POS_DAILY_ITEM' AS TB_NM , REPLACE(TRAN_DATE,'-','') AS SALE_DT , T01.COMPANY_CD AS COMPANY_CD , T01.COMPANY_NM AS COMPANY_NM , SUM(SALE_AMT) AS POS_AMT , SUM(0) AS HPC_AMT , SUM(0) AS HPC_S_AMT , SUM(0) AS HPC_U_AMT , SYSDATE AS ETL_DATE FROM  OL_F_POS_DAILY_ITEM T01 WHERE REPLACE(TRAN_DATE,'-','') = '20181222' AND  T01.COMPANY_CD IN ('PC00','SL00','BR00') GROUP BY TRAN_DATE,COMPANY_CD,COMPANY_NM; COMMIT;
INSERT /*+ APPEND */ INTO   OLAP.CHECK_OLAP_TABLE_TMP (SVC,TB_NM,SALE_DT,COMPANY_CD,COMPANY_NM,POS_AMT,HPC_AMT,HPC_S_AMT,HPC_U_AMT,ETL_DATE) SELECT  /*+ FULL(T01) PARALLEL(8) */   'OLAP' AS SVC , 'OL_F_POS_ITEM_M_CATEGORY_D' AS TB_NM , REPLACE(TRAN_DT,'-','') AS SALE_DT , T01.COMPANY_CD AS COMPANY_CD , T01.COMPANY_NM AS COMPANY_NM , SUM(0) AS POS_AMT , SUM(HPC_AMT) AS HPC_AMT , SUM(0) AS HPC_S_AMT , SUM(0) AS HPC_U_AMT , SYSDATE AS ETL_DATE FROM  OL_F_POS_ITEM_M_CATEGORY_D T01 WHERE REPLACE(TRAN_DT,'-','') = '20181222' AND  T01.COMPANY_CD IN ('PC00','SL00','BR00') GROUP BY TRAN_DT,COMPANY_CD,COMPANY_NM; COMMIT;
INSERT /*+ APPEND */ INTO   OLAP.CHECK_OLAP_TABLE_TMP (SVC,TB_NM,SALE_DT,COMPANY_CD,COMPANY_NM,POS_AMT,HPC_AMT,HPC_S_AMT,HPC_U_AMT,ETL_DATE) SELECT  /*+ FULL(T01) PARALLEL(8) */   'OLAP' AS SVC , 'OL_F_POS_PAY_DIV_D' AS TB_NM , REPLACE(TRAN_DATE,'-','') AS SALE_DT , T01.COMPANY_CD AS COMPANY_CD , T01.COMPANY_NM AS COMPANY_NM , SUM(SALE_AMT) AS POS_AMT , SUM(0) AS HPC_AMT , SUM(0) AS HPC_S_AMT , SUM(0) AS HPC_U_AMT , SYSDATE AS ETL_DATE FROM  OL_F_POS_PAY_DIV_D T01 WHERE REPLACE(TRAN_DATE,'-','') = '20181222' AND  T01.COMPANY_CD IN ('PC00','SL00','BR00') GROUP BY TRAN_DATE,COMPANY_CD,COMPANY_NM; COMMIT;
111111111111
ERR CODE    : -6535
ERR MESSAGE : ORA-06535: EXECUTE IMMEDIATE 에 있는 문장 문자열이 NULL 이거나 0 길이입니다

 

이렇게 나옵니다,. 저 쿼리 직접 실행하면 이상없이  insert되구요,,

by 마농 [2018.12.27 13:08:47] 
CREATE OR REPLACE PROCEDURE sp_check_test_02
IS
    tb_table   olap.check_olap_table_cm%ROWTYPE;
    v_query    VARCHAR2(4000);
    CURSOR cur_table IS
    SELECT * FROM olap.check_olap_table_cm
    ;
BEGIN
    OPEN cur_table;
    LOOP
        FETCH cur_table INTO tb_table;
        EXIT WHEN cur_table%NOTFOUND;
        v_query := 'INSERT INTO olap.check_olap_table_tmp'
                || '( svc, tb_nm, sale_dt, company_cd, company_nm'
                || ', pos_amt, hpc_amt, hpc_s_amt, hpc_u_amt, etl_date )'
                || 'SELECT :v_svc   AS svc'
                ||      ', :v_tb_nm AS tb_nm'
                ||      ', REPLACE(' || tb_table.sale_dt || ', ''-'') AS sale_dt'
                ||      ', company_cd'
                ||      ', company_nm'
                ||      ', SUM(' || tb_table.pos_amt   || ') AS pos_amt'
                ||      ', SUM(' || tb_table.hpc_amt   || ') AS hpc_amt'
                ||      ', SUM(' || tb_table.hpc_s_amt || ') AS hpc_s_amt'
                ||      ', SUM(' || tb_table.hpc_u_amt || ') AS hpc_u_amt'
                ||      ', sysdate AS etl_date'
                ||  ' FROM ' || tb_table.tb_nm 
                || ' WHERE ' || tb_table.sale_dt || ' = :v_sale_dt'
                ||   ' AND company_cd IN (''PC00'', ''SL00'', ''BR00'')'
                || ' GROUP BY ' || tb_table.sale_dt || ', company_cd, company_nm'
                ;
--      DBMS_OUTPUT.PUT_LINE(v_query);
        EXECUTE IMMEDIATE v_query USING tb_table.svc, tb_table.tb_nm, '2018-12-22';
        COMMIT;
    END LOOP;
    CLOSE cur_table;
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('ERR CODE    : ' || TO_CHAR(SQLCODE));
        DBMS_OUTPUT.PUT_LINE('ERR MESSAGE : ' || SQLERRM);
        RAISE_APPLICATION_ERROR(-20001, '자식 프로시저 에서 ERROR가 발생되어 프로시저가 종료되었습니다.');
        ROLLBACK;
END;
/

 

by 위동충 [2018.12.27 13:18:51]

너무 감사해서 뭐라 표현할 방법이 없네요. 

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