[SQL 고수되기] 열번째 이야기

출처 : 네이버지식인 노하우
http://kin.naver.com/knowhow/detail.nhn?d1id=8&dirId=8&docId=553302&qb=U1FM6rOg7IiY65CY6riwIC0g7Je067KI7Ke4IOydtOyVvOq4sA==&enc=utf8&section=kin&rank=1&search_sort=0&spq=0

이번 이야기에선 앞으로 많이 활용되어질 아르바이트생정보에 대해 만들어볼까합니다.

종종 발생하는 Data Migration 방식을 보여드리고저 하는데 이땐 우편번호 정보가 필요합니다.

전부 다 가지고 계신게 아니기에 그냥 간단하게 주조를 고정시키고 만드는 방법과

랜덤하게 주소를 만드는 방법 두가지로 만들겠습니다.

 

참고로 우편정보 필요하신분들은 하단 의견란에 글남기시면

메일로 우편번호 생성 스크립트 보내드리겠습니다.

 

그럼 전에 생성했던 EMP_INF 테이블구조부터 확인하죠

CREATE TABLE EMP_INF
(
    EMP_NO  CHAR    (8 ),  /* 사번:년(2)+월(2)+일번(4) */
    SNAME   VARCHAR2(50),  /* 이름   */
    PHONE   VARCHAR2(20),  /* 핸드폰 */
    ADDRESS VARCHAR2(100),  /* 주소 */
    CONSTRAINT EMP_INF_PK PRIMARY KEY (EMP_NO)
    USING INDEX TABLESPACE TS00 PCTFREE 10
    STORAGE ( INITIAL 5M )
)
TABLESPACE TS01
PCTUSED    40
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL        20M
            NEXT           20M
            MINEXTENTS       1
            MAXEXTENTS       2147483645
            PCTINCREASE      0
            FREELISTS        1
            FREELIST GROUPS  1
            BUFFER_POOL      DEFAULT
           )
NOLOGGING
NOCACHE
PARALLEL ( DEGREE DEFAULT INSTANCES DEFAULT )
NOMONITORING;

 

첫번째로 출퇴근부 정보를 기준으로 사번하고 이름 만들겠습니다.

INSERT INTO EMP_INF(EMP_NO,SNAME)
SELECT EMP_NO
      ,decode(emp_no,'10040001', '김길동'
                    ,'10040002', '이미자'
                    ,'10040003', '박영철'
                    ,'10040004', '최순이'
                    ,'10040005', '정철수'
                    ,'10040006', '강민수'
                    ,'10040007', '조철이'
                    ,'10040008', '윤미애'
                    ,'10040009', '장남수'
                    ,'10040010', '임남정') SNAME
FROM WORK_INF
GROUP BY EMP_NO
ORDER BY 1
;

COMMIT;
단순하죠. 그냥 사번별로 이름 지정했습니다.

 

사실 자음구분자로 동적 이름 수십만 이상 아니 그이상도 임의의 테스트용

데이터를 정합성있게 만들어낼수 있지만 그건 생략하겠습니다.

다음에 기회되면 데이터 일괄처리 방식에 대해 이야기해보겠습니다.

 

그럼 다음은 사번별로 핸드폰과 주소 함 만들어보겠습니다.

UPDATE EMP_INF a
SET (PHONE,ADDRESS) =
    (SELECT PHONE,ADDRESS
     FROM  (
             SELECT emp_no
                   ,'011-'||
                    RPAD(CEIL(DBMS_RANDOM.VALUE*10000),4,'0')||'-'||
                    RPAD(CEIL(DBMS_RANDOM.VALUE*10000),4,'0') phone
                   ,decode(rownum,1 ,'대구 북구 침산3동 650∼663'
,2 ,'대전 동구 하소동'
,3 ,'서울 서대문구 충정로3가 동아일보사건물'
,4 ,'서울 송파구 마천2동 200-(68∼76)'
,5 ,'서울 관악구 신림2동 104∼117'
,6 ,'서울 마포구 합정동 430∼445'
,7,'서울 동작구 노량진동'
,8 ,'서울 영등포구 신길4동 158∼212'
,9 ,'서울 성동구 도선동 신한넥스텔'
,10,'서울 강동구 둔촌2동 163∼169')
             FROM  EMP_INF
           ) b
     WHERE b.emp_no = a.emp_no )
;

COMMIT;

 

주소도 그냥 일련번호 순으로 임의로 지정했습니다.

그런데 전화번호는 조금 다르죠. '011-' 고정시켜놓고요 나머지 부분은 4자리-4자리 형식에 맞게

Random하게 생성시켰습니다. 유효한 데이터 생성을 위해 야깐에 기교를 부렸습니다.

그리고 반드시 COMMIT 잊지마십시오.

 

그럼 두번째 주소마저 랜덤하게 만들어 보겠습니다.

CREATE TABLE EMP_INF_TMP
AS
SELECT /*+ use_hash(a,b) */
       a.emp_no
      ,a.phone
      ,b.address
FROM  (
        SELECT emp_no
              ,'011-'||

               RPAD(CEIL(DBMS_RANDOM.VALUE*10000),4,'0')||'-'||

               RPAD(CEIL(DBMS_RANDOM.VALUE*10000),4,'0') phone
              ,CEIL(DBMS_RANDOM.VALUE*100000/(rownum*2)) seq
        FROM  EMP_INF
      ) a
     ,(
        SELECT row_number() over(order by zipcode) seq
              ,SIDO||' '||GUGUN||' '||DONG||' '||BUNJI address
        FROM  ZIP_MASTER
      ) b
WHERE b.seq = a.seq
;

 

UPDATE EMP_INF a
SET (PHONE,ADDRESS) =
    (SELECT PHONE,ADDRESS
     FROM  EMP_INF_TMP b
     WHERE b.emp_no = a.emp_no )
;

 

COMMIT;

 

DROP TABLE EMP_INF_TMP PURGE;

 

임의 테이블생성하고 적용하고 삭제하고. 불필요한 부분일수 있지만

일괄UPDATE질의시 유용하게 활용될수 있습니다.

그리고 중간 스크립트 중 힌트 쓰인부분있습니다.

자세한 내용은 차차 설명드릴것이니 그때 이해하시면 됩니다.

 

만들어진 EMP_INF 확인

SELECT *
FROM  EMP_INF

 

지난 이야기시간에도 말씀드렸지만 다시한번 말씀드리겠습니다.
쿼리를 잘 짜려면 테이블 구조만 가지고는 부족합니다.
유효한 정확한 데이터를 만들수 잇어야하고요(테스트라고 할지라도)
또 만들어진 데이터에 대해서도 내용 분석을 할 수 있어야합니다.

다시한번 우편정보 필요하신분들은 의견란에 남겨주세요 메일로 생성 스크립트 보내드릴께요
오늘 수고 많으셨습니다.