[SQL고수되기] 일곱번째 이야기

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

CREATE TABLE시에 Tablespace부분 문제 생기실수도 있으실듯 없어서리..

제가 깜밖했네요 이렇게 만들시면 됩니다.

 

CONN SYSTEM;

 

CREATE TABLESPACE TS00
DATAFILE 'D:\oracle\product\10.2.0\oradata\oracle\TS00.dbf'
SIZE 1000M AUTOEXTEND OFF
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT MANUAL
;

CREATE TABLESPACE TS01
DATAFILE 'D:\oracle\product\10.2.0\oradata\oracle\TS01.dbf'
SIZE 2000M AUTOEXTEND OFF
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT MANUAL
;

반드시 dba권한 계정으로 conn하시고요 Datafile 경로는 바꾸시면됩니다.

 

어제 논리Entity 이야기 시작했는데 좀 이려웠엇는지 모르겠네요

 

오늘은 어제 봤던 데이터 한번 만들어 볼겁니다.

테이블을 분석해! 라고 하면 대부분 어떤가요. 테이블명 확인하고 컬럼확인하고

PK,Index등등 대부분 물리적인 정보만 확인하는게 다 일겁니다.

DBA가 아닌이상 무의미한 해석이죠. 그렇다고 이게 필요없다는 얘기는 아닙니다.

구지 분석하지 말고 그냥 ERD만 옆에 떡하니 붙어놔도 될일이라는 뜻이죠.

가장 중요한건 컬럼별로 내용분석이 가장 중요합니다. 어떤 제약사항으로 만들어 졌으면

자리수마다 어떤 특징은 없는지. 또 어떤 형태로 저장되어 있는지 등등 그테이블에 들어있는

정보들의 형태나 관계성 이중성 Row간의 관계(Join) 등의 분석이 잘 되어있는 사람은 확실히 처리하는 능력이나 시간이 월등하게 차이가 날수있습니다.

 

그럼 직접 정보를 만들어 보겠습니다.

제가 임의로 만들 정보를 10명의 아르바이트 생의 4월 출퇴근현황부를 만들것입니다.

추가적으로 월이 바뀌면 자동 잔여수당에 대한 처리가 되도록 할것입니다.

 

출퇴근부 테이블 스키마:

사번    : CHAR(8), 년도(yy)+월(mm)+일련번호(4)

출근일자: CHAR(8), 년월일

출근일시: DATE, 년월일시분초

퇴근일시: DATE, 년월일시분초

수당    : NUMBER(12)

 

회사내에 10명의 아르바이트생이 있고 출근은 9시까지이며 퇴근은 오후 6시이다. 

시간외 근무시간이 2시간 이상이면 추가로 1만원 고정지급한다.(내맘대로결정^^)

또, 휴일(토,일) 출근하여 8시간(+1시간 점심) 이상 근무시 수당으로 10만원 고정

지급한다.(이런 아르바이트면 바로 달려가겠음 ㅎㅎ)

Entity를 만들어 보겠습니다.

 

1)4월 데이터 생성

SELECT rownum

FROM  ALL_OBJECTS
WHERE ROWNUM <= to_char(LAST_DAY(ADD_MONTHS(sysdate,-1)),'dd')

 

2)4월 10명데이터 확장

* ADD_MONTHS(날짜,증감수) : 잘짜 데이터의 월증감

* LAST_DAY(날짜)          : 해당일자의 마지막날짜(말일)

* TO_CHAR(날짜,형식)      : 날짜의 형식별 문자표현

  -> to_char(LAST_DAY(ADD_MONTHS(sysdate,-1)),'dd') : 마지막일자(=일수)

* CEIL(실수값)            : 무조건 올림 (<->Trunc)

* LPAD(값,자리수,채울값)  : 자리수 채우기

SELECT rownum
      ,LPAD(ceil(rownum/to_char(LAST_DAY(ADD_MONTHS(sysdate,-1)),'dd')),4,'0')
FROM  ALL_OBJECTS
WHERE ROWNUM <= to_char(LAST_DAY(ADD_MONTHS(sysdate,-1)),'dd') * 10

 

3)10명의사원의 사번만들기

SELECT rownum
      ,to_char(LAST_DAY(ADD_MONTHS(sysdate,-1)),'yymm')||
       LPAD(ceil(rownum/to_char(LAST_DAY(ADD_MONTHS(sysdate,-1)),'dd')),4,'0') emp_no
FROM  ALL_OBJECTS
WHERE ROWNUM <= to_char(LAST_DAY(ADD_MONTHS(sysdate,-1)),'dd') * 10

 

4) 츨근일자 만들기

* row_number() over(partition by 기준컬럼 Order by 정렬순서) : 기준컬럼별 정렬순서에

  순차적으로 Serial 부여(=일련번호)
...

      ,to_char(LAST_DAY(ADD_MONTHS(sysdate,-1)),'yyyymm')||
       lpad(row_number() over(partition by ceil(rownum/to_char(LAST_DAY(

ADD_MONTHS(sysdate,-1)),'dd'))
order by rownum),2,'0') attn_ymd
...

5) 출근일시만들기

* DBMS_RANDOM.VALUE : 1보다 작은 Ramdom 실수 반환

  -> round(DBMS_RANDOM.VALUE,2)/24 : 1시간보다 작은 분값

: 출퇴근시간을 다르게하기위해

: 운없으면 지각도 할 수 있음

....

      ,to_date(to_char(LAST_DAY(ADD_MONTHS(sysdate,-1)),'yyyymm')||
       lpad(row_number() over(partition by ceil(rownum/to_char(LAST_DAY(

ADD_MONTHS(sysdate,-1)),'dd'))
order by rownum),2,'0')||
            '082000','yyyymmddhh24miss')+

       (round(DBMS_RANDOM.VALUE,2)/24) CHECK_IN
....

 

6)퇴근일시만들기

....

,to_date(to_char(LAST_DAY(ADD_MONTHS(sysdate,-1)),'yyyymm')||
 lpad(row_number() over(partition by ceil(rownum/to_char(LAST_DAY(

ADD_MONTHS(sysdate,-1)),'dd'))
order by rownum),2,'0')||
         '180000','yyyymmddhh24miss')+(round(DBMS_RANDOM.VALUE,2)/24)+
 trunc(DBMS_RANDOM.VALUE+0.49)*2/24 check_out
....

 

7) 주말정보생성 및 보너스 임의선정

* TRUNC(실수값) : 무조건 버림 ( <-> CEIL )

* Case When 비교 THEN true일때설정값 ELSE false일때설정값 end

....

,trunc(DBMS_RANDOM.VALUE+0.49) bonus_chk
,case when to_char(to_date(to_char(LAST_DAY(ADD_MONTHS(sysdate,-1)),'yyyymm')||
           lpad(row_number() over(partition by ceil(rownum/to_char(LAST_DAY(

ADD_MONTHS(sysdate,-1)),'dd'))
order by rownum),2,'0')||
           '082000','yyyymmddhh24miss')+

           (round(DBMS_RANDOM.VALUE,2)/24),'d') in ('1','7')
      then '1'
      else '0'
 end holly_job
....

 

8) 1~7까지 생성 Entity기준 완성쿼리

SELECT SNO /* 사번 */
,ATTN_YMD /* 출근일자 */
      ,decode(holly_job,'0',check_in,decode(bonus_chk,1,check_in))   check_in   /* 출근일시 */
      ,decode(holly_job,'0',check_out,decode(bonus_chk,1,check_out)) check_out  /* 퇴근일시 */
,0 TIME_ALLOW /* 수당 */
FROM  (
        SELECT rownum
              ,to_char(LAST_DAY(ADD_MONTHS(sysdate,-1)),'yymm')||
               LPAD(ceil(rownum/to_char(LAST_DAY(ADD_MONTHS(sysdate,-1)),'dd')),4,'0') sno
              ,to_char(LAST_DAY(ADD_MONTHS(sysdate,-1)),'yyyymm')||
               lpad(row_number() over(partition by ceil(rownum/to_char(LAST_DAY(

ADD_MONTHS(sysdate,-1)),'dd'))
order by rownum),2,'0') attn_ymd
              ,to_date(to_char(LAST_DAY(ADD_MONTHS(sysdate,-1)),'yyyymm')||
                       lpad(row_number() over(partition by ceil(rownum/to_char(LAST_DAY(

ADD_MONTHS(sysdate,-1)),'dd'))
order by rownum),2,'0')||
                       '082000','yyyymmddhh24miss')+(round(DBMS_RANDOM.VALUE,2)/24) CHECK_IN
              ,to_date(to_char(LAST_DAY(ADD_MONTHS(sysdate,-1)),'yyyymm')||
                       lpad(row_number() over(partition by ceil(rownum/to_char(LAST_DAY(

ADD_MONTHS(sysdate,-1)),'dd'))
order by rownum),2,'0')||
                       '180000','yyyymmddhh24miss')+(round(DBMS_RANDOM.VALUE,2)/24)+
               trunc(DBMS_RANDOM.VALUE+0.49)*2/24 check_out
              ,trunc(DBMS_RANDOM.VALUE+0.49) bonus_chk
              ,case when to_char(to_date(to_char(LAST_DAY(ADD_MONTHS(sysdate,-1)),'yyyymm')||
                         lpad(row_number() over(partition by ceil(rownum/to_char(LAST_DAY(

ADD_MONTHS(sysdate,-1)),'dd'))
order by rownum),2,'0')||
'082000','yyyymmddhh24miss')+(round(DBMS_RANDOM.VALUE,2)/24),'d')

                         in ('1','7')
                    then '1'
                    else '0'
               end holly_job
        FROM  ALL_OBJECTS
        WHERE ROWNUM <= to_char(LAST_DAY(ADD_MONTHS(sysdate,-1)),'dd') * 10
      )
;

RSLT:

9) 등록(INSERT)

INSERT INTO WORK_INF(EMP_NO,ATTN_YMD,CHECK_IN,CHECK_OUT,TIME_ALLOW)

SELECT SNO /* 사번 */
      ,ATTN_YMD /* 출근일자 */
      ,decode(holly_job,'0',check_in,decode(bonus_chk,1,check_in))   check_in   /* 출근일시 */
      ,decode(holly_job,'0',check_out,decode(bonus_chk,1,check_out)) check_out  /* 퇴근일시 */
      ,0 TIME_ALLOW /* 수당 */
FROM  (
        SELECT rownum
              ,to_char(LAST_DAY(ADD_MONTHS(sysdate,-1)),'yymm')||
               LPAD(ceil(rownum/to_char(LAST_DAY(ADD_MONTHS(sysdate,-1)),'dd')),4,'0') sno
              ,to_char(LAST_DAY(ADD_MONTHS(sysdate,-1)),'yyyymm')||
               lpad(row_number() over(partition by ceil(rownum/to_char(LAST_DAY(

ADD_MONTHS(sysdate,-1)),'dd'))
order by rownum),2,'0') attn_ymd
              ,to_date(to_char(LAST_DAY(ADD_MONTHS(sysdate,-1)),'yyyymm')||
                       lpad(row_number() over(partition by ceil(rownum/to_char(LAST_DAY(

ADD_MONTHS(sysdate,-1)),'dd'))
order by rownum),2,'0')||
                       '082000','yyyymmddhh24miss')+(round(DBMS_RANDOM.VALUE,2)/24) CHECK_IN
              ,to_date(to_char(LAST_DAY(ADD_MONTHS(sysdate,-1)),'yyyymm')||
                       lpad(row_number() over(partition by ceil(rownum/to_char(LAST_DAY(

ADD_MONTHS(sysdate,-1)),'dd'))
order by rownum),2,'0')||
                       '180000','yyyymmddhh24miss')+(round(DBMS_RANDOM.VALUE,2)/24)+
               trunc(DBMS_RANDOM.VALUE+0.49)*2/24 check_out
              ,trunc(DBMS_RANDOM.VALUE+0.49) bonus_chk
              ,case when to_char(to_date(to_char(LAST_DAY(ADD_MONTHS(sysdate,-1)),'yyyymm')||
                         lpad(row_number() over(partition by ceil(rownum/to_char(LAST_DAY(

ADD_MONTHS(sysdate,-1)),'dd'))
order by rownum),2,'0')||
'082000','yyyymmddhh24miss')+(round(DBMS_RANDOM.VALUE,2)/24),'d')

                         in ('1','7')
                    then '1'
                    else '0'
               end holly_job
        FROM  ALL_OBJECTS
        WHERE ROWNUM <= to_char(LAST_DAY(ADD_MONTHS(sysdate,-1)),'dd') * 10
      )
;

 

10) 월별수당계산

    - 월초가되면 전달 급여액 계산전에 일별 수당계산을 한다.

    - 수당 계산 로직

      ① 시간외 근무시간이 2시간 이상이면 추가로 1만원 고정지급한다.

      ② 휴일(토,일) 출근하여 8시간(+1시간 점심) 이상 근무시 수당으로 10만원 고정지급

    - Entity 생성 : 사번,일자별(Group by)

      SELECT EMP_NO
            ,ATTN_YMD
            ,case when to_char(to_date(attn_ymd,'yyyymmdd'),'d') in ('1','7')
                  then case when ceil((CHECK_OUT-CHECK_IN)*24*60)-540 > 0

then 100000else 0 end
                  else case when ceil((CHECK_OUT-CHECK_IN)*24*60)-540-120 > 0

then 10000end else 0 end

             end time_allow
      FROM  WORK_INF

      WHERE attn_ymd like '201004%'

 

11) 수당적용

    UPDATE WORK_INF a

    SET TIME_ALLOW =

       (SELECT case when to_char(to_date(attn_ymd,'yyyymmdd'),'d') in ('1','7')
                    then case when ceil((CHECK_OUT-CHECK_IN)*24*60)-540 > 0

then 100000 else 0 end
                    else case when ceil((CHECK_OUT-CHECK_IN)*24*60)-540-120 > 0

then 10000 else 0 end

               end
        FROM  WORK_INF b

        WHERE a.emp_no= b.emp_no

        AND   a.attn_ymd = b.attn_ymd

        AND b.attn_ymd like '201004%')

 

  참고) 물론 Procedure나 Pro-C를 활용해서 Cursor ~ Fetch 방식으로 할수 도 있습니다.

        이와 관련해서 기회가 된다면 다른 이야기 주제로 처러방식에 대해 이야기할

        예정입니다,

 

  지금까지 살펴본 내용이 이해를 못하실수도 있습니다. 내용이 어려워서 그럴수도 있고

개념이 어려울수도 있습니다. 중요한 것은 이처럼 SELECT,INSERT,UPDATE,DELETE 질의 시

논리적으로 Entity를 접근해야 하는 경우가 거의 대부분입니다. 하지만 이건 문법이나 기술

로는 답을 낼수가 없습니다. 중요한건 업무적인 이해와 잘 만들어진 테이블하에서 만들어질

수 있는 부분이기에 모르시더라도 아 이렇게 전개되는구나 하고 인지하시면 좋습니다. 아니

인지하려고 노력하셨으면 좋겠습니다. 참고로 지금까지 한 쿼리는 복작하기만 하지 By Row

기준의 아주 단순한 쿼리라고 봐도 무방할것입니다. 집계라는지 부분범위처리 처럼 집합을

대상으로 새로운 식별자를 만들어내는 과정이 없기에 한번씩 따라해보시는 걸로 만족하시면

좋을듯합니다. 포기하지 마시고요. 그냥 읽어보지 마시고요. 꼭 해보시라는 뜻헤서 함수 정의

부터 내용 설명까지 단계별로 진행한 것이니 부디 꼭 해보시길 바랍니다.

  또, 경우가 되시는 분들은 EMP_INF나 PAY_INF에 대해서 위처럼 만들순 없지만 어떻게 만들어

질것인지 한번 고민해 보시는 것도 아주 많은 도움이 되리라 판단됩니다.

 

다음 이야기부턴 여러분들이 실제로 해보고 싶으셨던 퍄턴별 SELECT질의에 대해 구체적으로 사례를 통해 접근해보겠습니다.

 

수고하셨습니다. 비는 오지만 좋은 하루 되십시오.