pl sql 질문입니다. 0 5 787

by 사비타 [2017.12.27 11:11:52]


tcc_0504_subs 라는 테이블에

STRT_TEL_NO와 END_TEL_NO 값이 있는데

만약 STRT_TEL_NO 값이 050353440000 이고

END_TEL_NO 값이 050353449999 라면

STRT_TEL_NO 값을 1씩 증가해서 END_TEL_NO 까지

새로 만든 테이블의 특정컬럼에 로우단위로 넣고 싶은데

아래와 같이 틀을 잡고 고민해보고 찾아봐도

도무지 방법이 안떠올라 고수분들께 여쭤봅니다.

 

 CURSOR CSOR IS SELECT STRT_TEL_NO,END_TEL_NO,EFT_END_DT,EFT_STRT_DT,SUBS_STAT,MNG_DEPT,MNG_EMP,CUST_ID,SVC_CD,LWR_LV_CUST_ID FROM tcc_0504_subs WHERE STRT_TEL_NO = TEL_ID;
  --사용할 변수 선언
  B_STRT_TEL_NO tcc_0504_subs.STRT_TEL_NO%TYPE; --테이블.컬럼%TYPE (해당 테이블의 컬럼 타입과 동일하게 선언하겠다는 표현)
  B_END_TEL_NO tcc_0504_subs.END_TEL_NO%TYPE;
  B_EFT_END_DT tcc_0504_subs.EFT_END_DT%TYPE;
  B_EFT_STRT_DT tcc_0504_subs.EFT_STRT_DT%TYPE;
  B_SUBS_STAT tcc_0504_subs.SUBS_STAT%TYPE;
  B_MNG_DEPT tcc_0504_subs.MNG_DEPTR%TYPE;
  B_MNG_EMP tcc_0504_subs.MNG_EMP%TYPE;
  B_CUST_ID tcc_0504_subs.CUST_ID%TYPE;
  B_SVC_CD tcc_0504_subs.SVC_CD%TYPE;
  B_LWR_LV_CUST_ID tcc_0504_subs.LWR_LV_CUST_ID%TYPE;

BEGIN

  OPEN CSOR; --커서 열기
    LOOP --루프 시작
      FETCH CSOR INTO B_STRT_TEL_NO, B_EFT_END_DT, B_EFT_STRT_DT, B_SUBS_STAT, B_MNG_DEPT, B_MNG_EMP, B_CUST_ID, B_SVC_CD, B_LWR_LV_CUST_ID; --커서에서 가져와 위에 선언한 변수에 삽입 (갯수 일치)
      EXIT WHEN CSOR%NOTFOUND; --LOOP를 빠져나오는 조건
        INSERT INTO tmp_tcc_0504_subs( --백업 테이블에 입력
          TEL_NO,
          EFT_END_DT,
          EFT_STRT_DT,
          SUBS_STAT,
          MNG_DEPT,
          MNG_EMP,
          CUST_ID,
          SVC_CD,
          LWR_LV_CUST_ID,
        ) VALUES (
          B_STRT_TEL_NO,
          B_EFT_END_DT,
          B_EFT_STRT_DT,
          B_SUBS_STAT,
          B_MNG_DEPT,
          B_MNG_EMP,
          B_CUST_ID,
          B_SVC_CD,
          B_LWR_LV_CUST_ID
        );
      COMMIT; --커밋
    END LOOP; --루프 종료
  CLOSE CSOR; --커서 닫기

END;

 

by 우리집아찌 [2017.12.27 12:56:28]
-- 테이블 스키마에 맞춰서 사용하세요.
INSERT INTO tmp_tcc_0504_subs
WITH tcc_0504_sub AS (
SELECT  '050353440000' STRT_TEL_NO , '050353449999 ' AS END_TEL_NO 
     ,  1 VAL1 , 2 VAL2
  FROM DUAL
)
SELECT LPAD(STRT_TEL_NO + LEVEL -1 , 12 , '0' ) , A.*
  FROM tcc_0504_sub A
 WHERE STRT_TEL_NO  = '050353440000'
CONNECT BY LEVEL <= ( END_TEL_NO - STRT_TEL_NO )


 


by 사비타 [2017.12.27 14:27:07]

답변감사합니다..그런데

부하도 많이 걸리고 해서 건바이 건으로 실시간으로 들어가는

pl sql로 해야될거 같네요 ㅠㅠ


by 우리집아찌 [2017.12.27 14:30:06]

INSERT문은 예시고

PL-SQL로 하시려면 SELECT 문만 CURSOR에서 넣어서 쓰시면됩니다.


by 마농 [2017.12.29 07:35:35]

부하가 많이 걸리는 것은...
COnnect By Level 을 이용한 행복제 방법은 dual 과 같이 1건의 자료에 적용하는 방법입니다.
다량의 건에 적용하면 큰일(?)납니다.
where 절을 주셨지만.
where 절보다 connect by 절이 먼저 수행됩니다.
where 절은 큰일(?)이 발생된 이후에 작동하니 느릴수밖에 없습니다.
다음과 같이 하시면 됩니다.
 

SELECT LPAD(strt_tel_no + lv - 1, LENGTH(strt_tel_no), '0') tel_no
     , eft_end_dt
     , eft_strt_dt
     , subs_stat
     , mng_deptr
     , mng_emp
     , cust_id
     , svc_cd
     , lwr_lv_cust_id
  FROM tcc_0504_sub
     , (SELECT LEVEL lv FROM dual CONNECT BY LEVEL <= 10000) b
 WHERE lv <= end_tel_no - strt_tel_no
;

 


by 그로밋 [2017.12.28 16:52:24]

하나씩 처리하면  그하나에 대해서는 부하가 덜걸릴지 모르지만, "우리집아찌" 첫번째 답처럼 하는것이, 전체적으로 보면 부하가 덜 걸릴 것입니다.

INSERT /*+ APPEND */ INTO tmp_tcc_0504_subs (
    TEL_NO,
    EFT_END_DT, EFT_STRT_DT, SUBS_STAT, MNG_DEPT, MNG_EMP, CUST_ID, SVC_CD, LWR_LV_CUST_ID)
SELECT LPAD(STRT_TEL_NO + LEVEL -1 , 12 , '0' ) AS TEL_NO, 
       A.EFT_END_DT, A.EFT_STRT_DT, A.SUBS_STAT, A.MNG_DEPT, A.MNG_EMP, A.CUST_ID, A.SVC_CD, A.LWR_LV_CUST_ID
  FROM tcc_0504_subs A
 WHERE STRT_TEL_NO  = '050353440000'
CONNECT BY LEVEL <= ( END_TEL_NO - STRT_TEL_NO );

혹시 "건바이 건"을 원하셨던 로직을 기초로 쓰면, 다음과 같이 하는 것도 하나의 방법입니다.

DECLARE

  TEL_ID VARCHAR2(12) := '050353440000';

  CURSOR cur_tel_info 
  IS
    SELECT STRT_TEL_NO, END_TEL_NO,
           EFT_END_DT, EFT_STRT_DT, SUBS_STAT, MNG_DEPT, MNG_EMP, CUST_ID, SVC_CD, LWR_LV_CUST_ID 
      FROM tcc_0504_subs
      WHERE STRT_TEL_NO = TEL_ID;
      
  each_tel_no NUMBER;
  
BEGIN

  FOR rec IN cur_tel_info LOOP
  
    -- 전화 번호를 첫 전화번호로 초기화 
    each_tel_no := TO_NUMBER(rec.STRT_TEL_NO);
  
    -- 전화 번호가 마지막 전화 번호가 될때까지 insert.
    WHILE each_tel_no <= TO_NUMBER(rec.END_TEL_NO) LOOP
    
      INSERT INTO tmp_tcc_0504_subs (
          TEL_NO, 
          EFT_END_DT, EFT_STRT_DT, SUBS_STAT, MNG_DEPT, MNG_EMP, CUST_ID, SVC_CD, LWR_LV_CUST_ID)
        VALUES (
          LPAD(TO_CHAR(each_tel_no), 12, '0'),
          rec.EFT_END_DT, rec.EFT_STRT_DT, rec.SUBS_STAT, rec.MNG_DEPT, rec.MNG_EMP, rec.CUST_ID, rec.SVC_CD, rec.LWR_LV_CUST_ID);
       
      -- commit을 자주 하는것은 좋지 않음.  예를 들어, 100으로 나눠 나머지가 99가 될때 마다 commit할수도 있읍니다.
      IF MOD(each_tel_no, 100) = 99 THEN
        COMMIT;
      END IF;
      
      -- 다음 전화 번호를 계산
      each_tel_no := each_tel_no + 1;

    END LOOP;  -- WHILE each_tel_no <= TO_NUMBER(rec.END_TEL_NO) LOOP
    
    COMMIT;  -- 남은 것들을 commit
    
  END LOOP;  -- FOR rec IN cur_tel_info LOOP
    
END;
/

"건바이 건" 으로 처리하는 중요 이유는, 어느 레코드의 insert에서 에러가 발생 하는가를 찾을 수 있기 때문 입니다.

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