(1)선수금테이블(BEFAMT)
일련번호(SEQ_NO/PK), 발생일(GEN_DT), 시작일(STR_DT), 종료일(END_DT), 선수금액(BEF_AMT)
(2)월별매출테이블(MONSAL)
일련번호(SEQ_NO/PK/FK), 적용월(APP_MON/PK), 매출금액(SAL_AMT)
SQL>
CREATE TABLE ORA9.BEFAMT (
2 SEQ_NO VARCHAR2(4) NOT NULL
3 , GEN_DT VARCHAR2(8) NOT NULL
4 , STR_DT VARCHAR2(8) NOT NULL
5 , END_DT VARCHAR2(8) NOT NULL
6 , BEF_AMT NUMBER(10) NOT NULL
7 )
8 TABLESPACE ORA9;
테이블이 생성되었습니다.
SQL>
COMMENT ON TABLE ORA9.BEFAMT IS '선수금';
주석이 생성되었습니다.
SQL>
COMMENT ON COLUMN ORA9.BEFAMT.SEQ_NO IS '일련번호';
주석이 생성되었습니다.
SQL>
COMMENT ON COLUMN ORA9.BEFAMT.GEN_DT IS '발생일';
주석이 생성되었습니다.
SQL>
COMMENT ON COLUMN ORA9.BEFAMT.STR_DT IS '시작일';
주석이 생성되었습니다.
SQL>
COMMENT ON COLUMN ORA9.BEFAMT.END_DT IS '종료일';
주석이 생성되었습니다.
SQL>
COMMENT ON COLUMN ORA9.BEFAMT.BEF_AMT IS '선수금액';
주석이 생성되었습니다.
SQL>
ALTER TABLE ORA9.BEFAMT ADD (
2 CONSTRAINT BEFAMT_PK PRIMARY KEY ( SEQ_NO )
3 USING INDEX TABLESPACE ORA9 );
테이블이 변경되었습니다.
SQL>
CREATE TABLE ORA9.MONSAL (
2 SEQ_NO VARCHAR2(4) NOT NULL
3 , APP_MON VARCHAR2(6) NOT NULL
4 , SAL_AMT NUMBER(10) NOT NULL
5 )
6 TABLESPACE ORA9;
테이블이 생성되었습니다.
SQL>
COMMENT ON TABLE ORA9.MONSAL IS '월별매출';
주석이 생성되었습니다.
SQL>
COMMENT ON COLUMN ORA9.MONSAL.SEQ_NO IS '일련번호';
주석이 생성되었습니다.
SQL>
COMMENT ON COLUMN ORA9.MONSAL.APP_MON IS '적용월';
주석이 생성되었습니다.
SQL>
COMMENT ON COLUMN ORA9.MONSAL.SAL_AMT IS '매출금액';
주석이 생성되었습니다.
SQL>
ALTER TABLE ORA9.MONSAL ADD (
2 CONSTRAINT MONSAL_PK PRIMARY KEY ( SEQ_NO,APP_MON )
3 USING INDEX TABLESPACE ORA9 );
테이블이 변경되었습니다.
SQL>
ALTER TABLE ORA9.MONSAL
2 ADD CONSTRAINT MONSAL_FK1 FOREIGN KEY ( SEQ_NO )
3 REFERENCES ORA9.BEFAMT ( SEQ_NO ) ;
테이블이 변경되었습니다.
SQL>
INSERT INTO ORA9.BEFAMT VALUES('0001','20080501','20080520','20080925',5000000);
1 개의 행이 만들어졌습니다.
SQL>
INSERT INTO ORA9.BEFAMT VALUES('0002','20080601','20080615','20080805',3800000);
1 개의 행이 만들어졌습니다.
SQL>
INSERT INTO ORA9.BEFAMT VALUES('0003','20080701','20080712','20081020',6780000);
1 개의 행이 만들어졌습니다.
SQL>
INSERT INTO ORA9.BEFAMT VALUES('0004','20080501','20080507','20080718',2890000);
1 개의 행이 만들어졌습니다.
SQL>
INSERT INTO ORA9.BEFAMT VALUES('0005','20080801','20080827','20081210',4250000);
1 개의 행이 만들어졌습니다.
SQL>
INSERT INTO ORA9.BEFAMT VALUES('0006','20080301','20080316','20080630',3860000);
1 개의 행이 만들어졌습니다.
SQL>
INSERT INTO ORA9.BEFAMT VALUES('0007','20080401','20080420','20080804',4300000);
1 개의 행이 만들어졌습니다.
SQL>
INSERT INTO ORA9.BEFAMT VALUES('0008','20080701','20080713','20081001',2540000);
1 개의 행이 만들어졌습니다.
SQL>
INSERT INTO ORA9.BEFAMT VALUES('0009','20080201','20080227','20080625',8200000);
1 개의 행이 만들어졌습니다.
SQL>
INSERT INTO ORA9.BEFAMT VALUES('0010','20080901','20080907','20081111',3000000);
1 개의 행이 만들어졌습니다.
SQL>
COMMIT;
커밋이 완료되었습니다.
SQL>
TRUNCATE TABLE ORA9.MONSAL;
테이블이 잘렸습니다.
SQL>
SELECT *
2 FROM ORA9.MONSAL;
선택된 레코드가 없습니다.
SQL>
CREATE OR REPLACE PROCEDURE MONSAL_INFO
2 ( P_WORK_MON IN VARCHAR2)
3
4 IS
5
6 BEGIN
7
8 INSERT INTO ORA9.MONSAL ( SEQ_NO, APP_MON, SAL_AMT )
9 SELECT X.SEQ_NO
10 ,SUBSTR ( P_WORK_MON, 1, 4 ) || Y.NO2,
11 (
12 DECODE ( Y.NO2, SUBSTR ( X.END_DT, 5, 2 ),
13 SUBSTR ( X.END_DT, 7, 2 ),
14 TO_CHAR ( LAST_DAY ( TO_DATE ( X.END_DT, 'YYYYMMDD' ) ) ,'DD' )
15 )
16 \- / ( 빼기 ) 각 해당월의 일수 산정 /
17 DECODE ( Y.NO2, SUBSTR ( X.STR_DT, 5, 2 ), SUBSTR ( X.STR_DT, 7, 2 ), '01' ) + 1
18 )
19 \* / ( 곱하기 ) 각 해당월의 매출금액 산정 ( 각 해당월의 일수 * 일 단위의 금액 ) /
20 (
21 X.BEF_AMT
22 / / ( 나누기 ) 일 단위의 금액 산정 ( 선수금 / 시작일과 종료일 사이의 일수 ) /
23 ( TO_DATE ( X.END_DT, 'YYYYMMDD' ) - TO_DATE ( X.STR_DT, 'YYYYMMDD' ) + 1 )
24 )
25 FROM ORA9.BEFAMT X, ORA9.COPY_T Y
26 WHERE X.GEN_DT LIKE P_WORK_MON || '%'
27 AND Y.NO2 BETWEEN SUBSTR ( X.STR_DT, 5, 2 ) AND SUBSTR ( X.END_DT, 5, 2 );
28
29 COMMIT;
30
31 END MONSAL_INFO;
32 /
프로시저가 생성되었습니다.
SQL>
EXECUTE MONSAL_INFO('200805');
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL>
SELECT *
2 FROM ORA9.MONSAL;
SEQ_NO APP_MON SAL_AMT
======= ======== =======
0001 200805 426357
0001 200806 1162791
0001 200807 1162791
0001 200808 1162791
0001 200809 968992
0004 200805 989726
0004 200806 1227260
0004 200807 712603
8 개의 행이 선택되었습니다.
SQL>
SELECT *
2 FROM ORA9.BEFAMT
3 WHERE GEN_DT LIKE '200805%';
SEQ_NO GEN_DT STR_DT END_DT BEF_AMT
======= ======= ======= ======= =======
0001 20080501 20080520 20080925 5000000
0004 20080501 20080507 20080718 2890000
2 개의 행이 선택되었습니다.
SQL>
SELECT ( 426357 /*5월*/ + 1162791 /*6월*/ + 1162791 /*7월*/
2 + 1162791 /*8월*/ + 968992 /*9월*/ ) "사원 0001의 매출"
3 FROM SYS.DUAL;
사원 0001의 매출
=============
4883722
SQL>
SELECT ( 989726 /*5월*/ + 1227260 /*6월*/ + 712603 /*7월*/ ) "사원 0004의 매출"
2 FROM SYS.DUAL;
사원 0004의 매출
=============
2929589
\*\* 분할된 로우의 '매출금액' 의 합과 '선수금' 테이블의 '선수금액' 의 차이에 대한 보정이 생략하기로 함 \*\*
(1) 프로시저를 작성 후 작업하고자 하는 '월' 을 입력하여 프로시저를 호출한다.(여기서는 2007년 5월)
(2) '선수금' 테이블의 처리 대상 로우들에 있는 '시작일' 의 '월' 과 '종료일' 의 '월' 을 'COPY_T' 테이블의 'NO2' 와 'BETWEEN' 으로 조인을 하게 되면
그 개월 수만큼의 로우가 복제된다. ( 27 행 )
(3) 만약 복제된 로우의 '월' 이 시작월과 같다면 '시작일' 의 '일자' 를 취하고 그렇지 않다면 '01' 을 일자로 취한다. ( 17 행 )
(4) 복제된 로우의 '월' 이 종료월과 같다면 '종료일' 의 일자를 취하고 그렇지 않으면 해당월의 '말일' 을 취한다. ( 12 ~ 14 행 )
(5) 이 두 일자의 차이에 하루를 더하면 구하고자 하는 각 해당월의 '일수' 가 된다. ( 12 ~ 15 행 \- 17 행 )
(6) 여기에 선수금액을 '시작일' 과 '종료일' 사이의 '일수' 를 구하여 나누면 일 단위의 금액이 된다. ( 21 행 / 23 행 )
(7) 마지막으로 각 해당월의 일수와 일 단위 금액을 곱하면 그 달의 매출금액이 된다. ( 11 ~ 18 행 \* 20 ~ 24 행)
이때 복제된 각각의 로우에 있는 NO2 값은 각 로우의 '월'을 의미한다.
(1)테이블스키마
1)매출집계 테이블
CREATE TABLE SALE_SUMM2 (
YYMM VARCHAR2 (6),
AMT NUMBER (9) ) ;
2)매출원가 테이블
CREATE TABLE SALE_WONGA (
YYMM VARCHAR2 (6),
AMT NUMBER (9)) ;
3)계정별집계 테이블
CREATE TABLE SLIP_SUMM (
YYMM VARCHAR2 (6),
ACCOUNT VARCHAR2 (3),
AMT NUMBER (9) ) ;
(2)테이블 생성/인서트
SQL> CREATE TABLE SALE_SUMM2 (
2 YYMM VARCHAR2 (6),
3 AMT NUMBER (9) ) ;
테이블이 생성되었습니다.
SQL> INSERT INTO SALE_SUMM2 VALUES ('199701', 46300) ;
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO SALE_SUMM2 VALUES ('199702', 32820) ;
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO SALE_SUMM2 VALUES ('199703', 54620) ;
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO SALE_SUMM2 VALUES ('199704', 48580) ;
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO SALE_SUMM2 VALUES ('199705', 63720) ;
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO SALE_SUMM2 VALUES ('199706', 57560) ;
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO SALE_SUMM2 VALUES ('199707', 46300) ;
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO SALE_SUMM2 VALUES ('199708', 32820) ;
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO SALE_SUMM2 VALUES ('199709', 54620) ;
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO SALE_SUMM2 VALUES ('199710', 48580) ;
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO SALE_SUMM2 VALUES ('199711', 82430) ;
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO SALE_SUMM2 VALUES ('199712', 157560) ;
1 개의 행이 만들어졌습니다.
SQL> COMMIT;
커밋이 완료되었습니다.
SQL> CREATE TABLE SALE_WONGA (
2 YYMM VARCHAR2 (6),
3 AMT NUMBER (9)) ;
테이블이 생성되었습니다.
SQL> INSERT INTO SALE_WONGA VALUES ('199701', 38172) ;
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO SALE_WONGA VALUES ('199702', 23814) ;
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO SALE_WONGA VALUES ('199703', 43802) ;
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO SALE_WONGA VALUES ('199704', 36510) ;
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO SALE_WONGA VALUES ('199705', 50694) ;
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO SALE_WONGA VALUES ('199706', 42552) ;
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO SALE_WONGA VALUES ('199707', 38172) ;
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO SALE_WONGA VALUES ('199708', 23814) ;
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO SALE_WONGA VALUES ('199709', 43802) ;
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO SALE_WONGA VALUES ('199710', 36510) ;
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO SALE_WONGA VALUES ('199711', 65156) ;
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO SALE_WONGA VALUES ('199712', 142552) ;
1 개의 행이 만들어졌습니다.
SQL> COMMIT;
커밋이 완료되었습니다.
SQL> CREATE TABLE SLIP_SUMM (
2 YYMM VARCHAR2 (6),
3 ACCOUNT VARCHAR2 (3),
4 AMT NUMBER (9) ) ;
테이블이 생성되었습니다.
SQL> INSERT INTO SLIP_SUMM VALUES ('199701', '211', 969) ;
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO SLIP_SUMM VALUES ('199701', '212', 872) ;
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO SLIP_SUMM VALUES ('199701', '213', 415) ;
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO SLIP_SUMM VALUES ('199701', '214', 160) ;
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO SLIP_SUMM VALUES ('199701', '215', 289) ;
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO SLIP_SUMM VALUES ('199701', '221', 358) ;
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO SLIP_SUMM VALUES ('199701', '222', 97) ;
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO SLIP_SUMM VALUES ('199701', '229', 265) ;
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO SLIP_SUMM VALUES ('199702', '211', 1150) ;
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO SLIP_SUMM VALUES ('199702', '212', 786) ;
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO SLIP_SUMM VALUES ('199702', '213', 545) ;
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO SLIP_SUMM VALUES ('199702', '214', 80) ;
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO SLIP_SUMM VALUES ('199702', '215', 142) ;
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO SLIP_SUMM VALUES ('199702', '221', 280) ;
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO SLIP_SUMM VALUES ('199702', '222', 34) ;
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO SLIP_SUMM VALUES ('199702', '229', 182) ;
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO SLIP_SUMM VALUES ('199703', '211', 1280) ;
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO SLIP_SUMM VALUES ('199703', '212', 1045) ;
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO SLIP_SUMM VALUES ('199703', '213', 674) ;
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO SLIP_SUMM VALUES ('199703', '214', 120) ;
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO SLIP_SUMM VALUES ('199703', '215', 381) ;
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO SLIP_SUMM VALUES ('199703', '221', 492) ;
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO SLIP_SUMM VALUES ('199703', '222', 68) ;
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO SLIP_SUMM VALUES ('199703', '229', 416) ;
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO SLIP_SUMM VALUES ('199704', '211', 1060) ;
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO SLIP_SUMM VALUES ('199704', '212', 840) ;
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO SLIP_SUMM VALUES ('199704', '213', 715) ;
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO SLIP_SUMM VALUES ('199704', '214', 280) ;
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO SLIP_SUMM VALUES ('199704', '215', 478) ;
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO SLIP_SUMM VALUES ('199704', '221', 586) ;
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO SLIP_SUMM VALUES ('199704', '222', 35) ;
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO SLIP_SUMM VALUES ('199704', '229', 523) ;
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO SLIP_SUMM VALUES ('199705', '211', 1230) ;
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO SLIP_SUMM VALUES ('199705', '212', 922) ;
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO SLIP_SUMM VALUES ('199705', '213', 508) ;
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO SLIP_SUMM VALUES ('199705', '214', 220) ;
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO SLIP_SUMM VALUES ('199705', '215', 512) ;
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO SLIP_SUMM VALUES ('199705', '221', 664) ;
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO SLIP_SUMM VALUES ('199705', '222', 52) ;
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO SLIP_SUMM VALUES ('199705', '229', 712) ;
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO SLIP_SUMM VALUES ('199706', '211', 1149) ;
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO SLIP_SUMM VALUES ('199706', '212', 1260) ;
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO SLIP_SUMM VALUES ('199706', '213', 620) ;
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO SLIP_SUMM VALUES ('199706', '214', 110) ;
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO SLIP_SUMM VALUES ('199706', '215', 259) ;
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO SLIP_SUMM VALUES ('199706', '221', 433) ;
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO SLIP_SUMM VALUES ('199706', '222', 80) ;
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO SLIP_SUMM VALUES ('199706', '229', 849) ;
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO SLIP_SUMM VALUES ('199707', '211', 969) ;
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO SLIP_SUMM VALUES ('199707', '212', 872) ;
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO SLIP_SUMM VALUES ('199707', '213', 215) ;
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO SLIP_SUMM VALUES ('199707', '214', 60) ;
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO SLIP_SUMM VALUES ('199707', '215', 189) ;
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO SLIP_SUMM VALUES ('199707', '221', 358) ;
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO SLIP_SUMM VALUES ('199707', '222', 70) ;
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO SLIP_SUMM VALUES ('199707', '229', 2265) ;
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO SLIP_SUMM VALUES ('199708', '211', 1150) ;
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO SLIP_SUMM VALUES ('199708', '212', 786) ;
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO SLIP_SUMM VALUES ('199708', '213', 345) ;
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO SLIP_SUMM VALUES ('199708', '214', 80) ;
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO SLIP_SUMM VALUES ('199708', '215', 42) ;
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO SLIP_SUMM VALUES ('199708', '221', 280) ;
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO SLIP_SUMM VALUES ('199708', '222', 24) ;
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO SLIP_SUMM VALUES ('199708', '229', 2282) ;
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO SLIP_SUMM VALUES ('199709', '211', 1280) ;
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO SLIP_SUMM VALUES ('199709', '212', 1045) ;
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO SLIP_SUMM VALUES ('199709', '213', 574) ;
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO SLIP_SUMM VALUES ('199709', '214', 120) ;
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO SLIP_SUMM VALUES ('199709', '215', 281) ;
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO SLIP_SUMM VALUES ('199709', '221', 492) ;
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO SLIP_SUMM VALUES ('199709', '222', 48) ;
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO SLIP_SUMM VALUES ('199709', '229', 1542) ;
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO SLIP_SUMM VALUES ('199710', '211', 1060) ;
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO SLIP_SUMM VALUES ('199710', '212', 840) ;
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO SLIP_SUMM VALUES ('199710', '213', 615) ;
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO SLIP_SUMM VALUES ('199710', '214', 180) ;
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO SLIP_SUMM VALUES ('199710', '215', 378) ;
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO SLIP_SUMM VALUES ('199710', '221', 586) ;
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO SLIP_SUMM VALUES ('199710', '222', 35) ;
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO SLIP_SUMM VALUES ('199710', '229', 1523) ;
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO SLIP_SUMM VALUES ('199711', '211', 2004) ;
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO SLIP_SUMM VALUES ('199711', '212', 452) ;
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO SLIP_SUMM VALUES ('199711', '213', 401) ;
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO SLIP_SUMM VALUES ('199711', '214', 172) ;
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO SLIP_SUMM VALUES ('199711', '215', 312) ;
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO SLIP_SUMM VALUES ('199711', '221', 733) ;
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO SLIP_SUMM VALUES ('199711', '222', 45) ;
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO SLIP_SUMM VALUES ('199711', '229', 1712) ;
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO SLIP_SUMM VALUES ('199712', '211', 1149) ;
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO SLIP_SUMM VALUES ('199712', '212', 1260) ;
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO SLIP_SUMM VALUES ('199712', '213', 520) ;
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO SLIP_SUMM VALUES ('199712', '214', 110) ;
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO SLIP_SUMM VALUES ('199712', '215', 157) ;
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO SLIP_SUMM VALUES ('199712', '221', 433) ;
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO SLIP_SUMM VALUES ('199712', '222', 68) ;
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO SLIP_SUMM VALUES ('199712', '229', 11849) ;
1 개의 행이 만들어졌습니다.
SQL> COMMIT;
커밋이 완료되었습니다.
(3)결과표
총매출액:4
매출원가:4
직접비:4
간접비:2
NO1:원시로우
NO2:LINE=3
NO3:
NO4:
1)총매출액->매출집계 테이블에서 년도를 주어 검색하고 월별로 금액을 가져온다.
2)매출원가->매출원가 테이블에서 년도를 주어 검색하고 월별로 금액을 가져온다.
3)매출총이익->총매출액-매출원가
4)~10)->직접비로서 계정별집계 테이블에서 구한다
(4)쿼리문
<김강환>
아래의 이화식 쿼리문과의 차이점이 보이는가?
이는 단순히 누구의 쿼리가 긴가,누구의 쿼리가 더 간단/복잡한 가의 문제가 아니다.
나의 경우 T1에서 무조건 SELECT를 한 후 최종 쿼리에서 모든 계산을 하기 때문에 복잡해 진다.
그러나 이화식 쿼리의 경우 맨 처음 테이블에서 가져올 때부터 할 수 있는 가공을 다 하고 가져온다.
따라서 맨 나중의 쿼리문에서는 간단하게 계산되어 질 수 있는 것이다.
SQL> SELECT DECODE(NO,1,LINE,2,DECODE(LINE,1,3,2,3,4,11,5,11,6,11,7,11,8,11,9,11,10,11,13,14),3,12,4,14) LINE
2 ,SUM(DECODE(NO,1,TOT,2,DECODE(LINE,1,TOT,2,-TOT,DECODE(LINE,8,-TOT,13,-TOT,TOT)),3,DECODE(LINE,1,TOT,2,-TOT,8,TOT,-TOT),4,DECODE(LINE,1,TOT,2,-TOT,8,TOT,-TOT))) TOT
3 ,SUM(DECODE(NO,1,TOT,2,DECODE(LINE,1,MON1,2,-MON1,DECODE(LINE,8,-MON1,13,-MON1,MON1)),3,DECODE(LINE,1,MON1,2,-MON1,8,MON1,-MON1),4,DECODE(LINE,1,MON1,2,-MON1,8,MON1,-MON1))) MON1
4 ,SUM(DECODE(NO,1,TOT,2,DECODE(LINE,1,MON2,2,-MON2,DECODE(LINE,8,-MON2,13,-MON2,MON2)),3,DECODE(LINE,1,MON2,2,-MON2,8,MON2,-MON2),4,DECODE(LINE,1,MON2,2,-MON2,8,MON2,-MON2))) MON2
5 ,SUM(DECODE(NO,1,TOT,2,DECODE(LINE,1,MON3,2,-MON3,DECODE(LINE,8,-MON3,13,-MON3,MON3)),3,DECODE(LINE,1,MON3,2,-MON3,8,MON3,-MON3),4,DECODE(LINE,1,MON3,2,-MON3,8,MON3,-MON3))) MON3
6 ,SUM(DECODE(NO,1,TOT,2,DECODE(LINE,1,MON4,2,-MON4,DECODE(LINE,8,-MON4,13,-MON4,MON4)),3,DECODE(LINE,1,MON4,2,-MON4,8,MON4,-MON4),4,DECODE(LINE,1,MON4,2,-MON4,8,MON4,-MON4))) MON4
7 ,SUM(DECODE(NO,1,TOT,2,DECODE(LINE,1,MON5,2,-MON5,DECODE(LINE,8,-MON5,13,-MON5,MON5)),3,DECODE(LINE,1,MON5,2,-MON5,8,MON5,-MON5),4,DECODE(LINE,1,MON5,2,-MON5,8,MON5,-MON5))) MON5
8 ,SUM(DECODE(NO,1,TOT,2,DECODE(LINE,1,MON6,2,-MON6,DECODE(LINE,8,-MON6,13,-MON6,MON6)),3,DECODE(LINE,1,MON6,2,-MON6,8,MON6,-MON6),4,DECODE(LINE,1,MON6,2,-MON6,8,MON6,-MON6))) MON6
9 ,SUM(DECODE(NO,1,TOT,2,DECODE(LINE,1,MON7,2,-MON7,DECODE(LINE,8,-MON7,13,-MON7,MON7)),3,DECODE(LINE,1,MON7,2,-MON7,8,MON7,-MON7),4,DECODE(LINE,1,MON7,2,-MON7,8,MON7,-MON7))) MON7
10 ,SUM(DECODE(NO,1,TOT,2,DECODE(LINE,1,MON8,2,-MON8,DECODE(LINE,8,-MON8,13,-MON8,MON8)),3,DECODE(LINE,1,MON8,2,-MON8,8,MON8,-MON8),4,DECODE(LINE,1,MON8,2,-MON8,8,MON8,-MON8))) MON8
11 ,SUM(DECODE(NO,1,TOT,2,DECODE(LINE,1,MON9,2,-MON9,DECODE(LINE,8,-MON9,13,-MON9,MON9)),3,DECODE(LINE,1,MON9,2,-MON9,8,MON9,-MON9),4,DECODE(LINE,1,MON9,2,-MON9,8,MON9,-MON9))) MON9
12 ,SUM(DECODE(NO,1,TOT,2,DECODE(LINE,1,MON10,2,-MON10,DECODE(LINE,8,-MON10,13,-MON10,MON10)),3,DECODE(LINE,1,MON10,2,-MON10,8,MON10,-MON10),4,DECODE(LINE,1,MON10,2,-MON10,8,MON10,-MON10))) MON10
13 ,SUM(DECODE(NO,1,TOT,2,DECODE(LINE,1,MON11,2,-MON11,DECODE(LINE,8,-MON11,13,-MON11,MON11)),3,DECODE(LINE,1,MON11,2,-MON11,8,MON11,-MON11),4,DECODE(LINE,1,MON11,2,-MON11,8,MON11,-MON11))) MON11
14 ,SUM(DECODE(NO,1,TOT,2,DECODE(LINE,1,MON12,2,-MON12,DECODE(LINE,8,-MON12,13,-MON12,MON12)),3,DECODE(LINE,1,MON12,2,-MON12,8,MON12,-MON12),4,DECODE(LINE,1,MON12,2,-MON12,8,MON12,-MON12))) MON12
15 FROM
16 (
17 SELECT 1 LINE
18 ,SUM(AMT) TOT
19 ,MIN(DECODE(SUBSTR(YYMM,5),'01',AMT)) MON1
20 ,MIN(DECODE(SUBSTR(YYMM,5),'02',AMT)) MON2
21 ,MIN(DECODE(SUBSTR(YYMM,5),'03',AMT)) MON3
22 ,MIN(DECODE(SUBSTR(YYMM,5),'04',AMT)) MON4
23 ,MIN(DECODE(SUBSTR(YYMM,5),'05',AMT)) MON5
24 ,MIN(DECODE(SUBSTR(YYMM,5),'06',AMT)) MON6
25 ,MIN(DECODE(SUBSTR(YYMM,5),'07',AMT)) MON7
26 ,MIN(DECODE(SUBSTR(YYMM,5),'08',AMT)) MON8
27 ,MIN(DECODE(SUBSTR(YYMM,5),'09',AMT)) MON9
28 ,MIN(DECODE(SUBSTR(YYMM,5),'10',AMT)) MON10
29 ,MIN(DECODE(SUBSTR(YYMM,5),'11',AMT)) MON11
30 ,MIN(DECODE(SUBSTR(YYMM,5),'12',AMT)) MON12
31 FROM SALE_SUMM2
32 WHERE YYMM LIKE '1997%'
33 UNION ALL
34 SELECT 2 LINE
35 ,SUM(AMT) TOT
36 ,MIN(DECODE(SUBSTR(YYMM,5),'01',AMT)) MON1
37 ,MIN(DECODE(SUBSTR(YYMM,5),'02',AMT)) MON2
38 ,MIN(DECODE(SUBSTR(YYMM,5),'03',AMT)) MON3
39 ,MIN(DECODE(SUBSTR(YYMM,5),'04',AMT)) MON4
40 ,MIN(DECODE(SUBSTR(YYMM,5),'05',AMT)) MON5
41 ,MIN(DECODE(SUBSTR(YYMM,5),'06',AMT)) MON6
42 ,MIN(DECODE(SUBSTR(YYMM,5),'07',AMT)) MON7
43 ,MIN(DECODE(SUBSTR(YYMM,5),'08',AMT)) MON8
44 ,MIN(DECODE(SUBSTR(YYMM,5),'09',AMT)) MON9
45 ,MIN(DECODE(SUBSTR(YYMM,5),'10',AMT)) MON10
46 ,MIN(DECODE(SUBSTR(YYMM,5),'11',AMT)) MON11
47 ,MIN(DECODE(SUBSTR(YYMM,5),'12',AMT)) MON12
48 FROM SALE_WONGA
49 WHERE YYMM LIKE '1997%'
50 UNION ALL
51 SELECT DECODE(ACCOUNT,'211',4,'212',5,'213',6,'214',7,'215',8,'221',9,'222',10,13) LINE
52 ,SUM(AMT) TOT
53 ,MIN(DECODE(SUBSTR(YYMM,5),'01',AMT)) MON1
54 ,MIN(DECODE(SUBSTR(YYMM,5),'02',AMT)) MON2
55 ,MIN(DECODE(SUBSTR(YYMM,5),'03',AMT)) MON3
56 ,MIN(DECODE(SUBSTR(YYMM,5),'04',AMT)) MON4
57 ,MIN(DECODE(SUBSTR(YYMM,5),'05',AMT)) MON5
58 ,MIN(DECODE(SUBSTR(YYMM,5),'06',AMT)) MON6
59 ,MIN(DECODE(SUBSTR(YYMM,5),'07',AMT)) MON7
60 ,MIN(DECODE(SUBSTR(YYMM,5),'08',AMT)) MON8
61 ,MIN(DECODE(SUBSTR(YYMM,5),'09',AMT)) MON9
62 ,MIN(DECODE(SUBSTR(YYMM,5),'10',AMT)) MON10
63 ,MIN(DECODE(SUBSTR(YYMM,5),'11',AMT)) MON11
64 ,MIN(DECODE(SUBSTR(YYMM,5),'12',AMT)) MON12
65 FROM SLIP_SUMM
66 WHERE YYMM LIKE '1997%'
67 GROUP BY DECODE(ACCOUNT,'211',4,'212',5,'213',6,'214',7,'215',8,'221',9,'222',10,13)
68 ) T1,COPY_T T2
69 WHERE T2.NO <=(CASE WHEN LINE=1 THEN 4
70 WHEN LINE=2 THEN 4
71 WHEN LINE BETWEEN 4 AND 10 THEN 4
72 WHEN LINE=13 THEN 2
73 ELSE 1 END)
74 GROUP BY DECODE(NO,1,LINE,2,DECODE(LINE,1,3,2,3,4,11,5,11,6,11,7,11,8,11,9,11,10,11,13,14),3,12,4,14);
LINE TOT MON1 MON2 MON3 MON4 MON5 MON6 MON7 MON8 MON9 MON10 MON11 MON12
==== === ==== ===== ==== ==== ===== ===== ==== ===== ===== ====== ====== ======
1 725910 725910 725910 725910 725910 725910 725910 725910 725910 725910 725910 725910 725910
2 585550 585550 585550 585550 585550 585550 585550 585550 585550 585550 585550 585550 585550
3 140360 8128 9006 10818 12070 13026 15008 8128 9006 10818 12070 17274 15008
4 14450 14450 14450 14450 14450 14450 14450 14450 14450 14450 14450 14450 14450
5 10980 10980 10980 10980 10980 10980 10980 10980 10980 10980 10980 10980 10980
6 6147 6147 6147 6147 6147 6147 6147 6147 6147 6147 6147 6147 6147
7 1692 1692 1692 1692 1692 1692 1692 1692 1692 1692 1692 1692 1692
8 3420 3420 3420 3420 3420 3420 3420 3420 3420 3420 3420 3420 3420
9 5695 5695 5695 5695 5695 5695 5695 5695 5695 5695 5695 5695 5695
10 656 656 656 656 656 656 656 656 656 656 656 656 656
11 36200 2582 2733 3298 3038 3084 3393 2355 2623 3278 2938 3495 3383
12 104160 5546 6273 7520 9032 9942 11615 5773 6383 7540 9132 13779 11625
13 24120 24120 24120 24120 24120 24120 24120 24120 24120 24120 24120 24120 24120
14 80040 5281 6091 7104 8509 9230 10766 3508 4101 5998 7609 12067 -224
14 개의 행이 선택되었습니다.
<이화식>
SQL> SELECT NO
2 /\*
3 1) NO-LINE=1:공헌이익의 매출원가에 해당/경상이익의 매출원가에 해당
4 2) NO-LINE=3:경상이익의 직접비에 해당
5 3) 나머지의 경우 그대로 출력한다
6 */
7 ,SUM(TOT*DECODE(NO-LINE,1,-1,3,-1,1) ) TOT
8 ,SUM(W01*DECODE(NO-LINE,1,-1,3,-1,1)) "1 월"
9 ,SUM(W02*DECODE(NO-LINE,1,-1,3,-1,1)) "2 월"
10 ,SUM(W03*DECODE(NO-LINE,1,-1,3,-1,1)) "3 월"
11 ,SUM(W04*DECODE(NO-LINE,1,-1,3,-1,1)) "4 월"
12 ,SUM(W05*DECODE(NO-LINE,1,-1,3,-1,1)) "5 월"
13 ,SUM(W06*DECODE(NO-LINE,1,-1,3,-1,1)) "6 월"
14 ,SUM(W01*DECODE(NO-LINE,1,-1,3,-1,1)) "7 월"
15 ,SUM(W02*DECODE(NO-LINE,1,-1,3,-1,1)) "8 월"
16 ,SUM(W03*DECODE(NO-LINE,1,-1,3,-1,1)) "9 월"
17 ,SUM(W04*DECODE(NO-LINE,1,-1,3,-1,1)) "10 월"
18 ,SUM(W05*DECODE(NO-LINE,1,-1,3,-1,1)) "11 월"
19 ,SUM(W06*DECODE(NO-LINE,1,-1,3,-1,1)) "12 월"
20 FROM
21 (
22 /*공헌이익(12),경상이익(14)를 제외한 모든 라인이 구해졌다.*/
23 SELECT LINE, SUM(AMT) TOT,
24 SUM(DECODE(MM,'01',AMT)) W01, SUM(DECODE(MM,'02',AMT)) W02,
25 SUM(DECODE(MM,'03',AMT)) W03, SUM(DECODE(MM,'04',AMT)) W04,
26 SUM(DECODE(MM,'05',AMT)) W05, SUM(DECODE(MM,'06',AMT)) W06,
27 SUM(DECODE(MM,'07',AMT)) W07, SUM(DECODE(MM,'08',AMT)) W08,
28 SUM(DECODE(MM,'09',AMT)) W09, SUM(DECODE(MM,'04',AMT)) W10,
29 SUM(DECODE(MM,'11',AMT)) W11, SUM(DECODE(MM,'06',AMT)) W12
30 FROM
31 (
32 /\*
33 Y.NO*LINE=6 인 경우는 NO=2,LINE=3인 경우로 매출총이익속의 매출원가이므로
34 음수로 하여 나중에 합을 한 경우 자연스럽게 빼기가 된다.
35 이 결과 총매출액,매출원가,매출총이익이 구해졌다.
36 */
37 SELECT Y.NO LINE, MM, SUM(AMT*DECODE(Y.NO*LINE,6,-1,1) ) AMT
38 FROM
39 (
40 /*총매출액 구하기*/
41 SELECT '1' LINE, SUBSTR(S2.YYMM ,5,2) MM, SUM(AMT) AMT
42 FROM SALE_SUMM2 S2
43 WHERE S2.YYMM LIKE '1997%'
44 GROUP BY SUBSTR(S2.YYMM,5,2)
45 UNION ALL
46 /*매출원가 구하기*/
47 SELECT '2' LINE, SUBSTR(SW.YYMM ,5,2) MM, SUM(AMT) AMT
48 FROM SALE_WONGA SW
49 WHERE SW.YYMM LIKE '1997%'
50 GROUP BY SUBSTR(SW.YYMM ,5,2)
51 ) X, COPY_T Y
52 /\*
53 X의 LINE 값은 1 혹은 2를 갖는다. 따라서 이것은 (1,3) OR (2,3)의 개념이지 <=3의 개념이 아니다
54 LINE=1의 경우 총매출액이며 LINE=2는 매출원가로서 매출총이익을 구하기 위해 복제를 하고
55 라인번호 3을 부여하고 있다.
56 */
57 WHERE Y.NO IN (LINE, 3)
58 AND Y.NO <= 14 /*무슨 의미일까?*/
59 GROUP BY Y.NO, MM
60 UNION ALL
61 /\*
62 Y.NO*LINE=88 인 경우는 NO=8,LINE=11인 경우로써 직접비의 계를 구할 때
63 8은 영업외수익으로 비용이 아닌 수익이므로 음수로 더해 주어야 한다.
64 이로써 직접비,간접비,직접비계가 구해졌다
65 */
66 SELECT Y.NO LINE, MM, SUM(AMT*DECODE(Y.NO*LINE,88,-1,1) ) AMT
67 FROM
68 (
69 /*직접비 계정은 4~10, 나머지 계정은 간접비(13)로 한다*/
70 SELECT DECODE(SUBSTR(SL.ACCOUNT ,1,3),'211',4, '212',5,
71 '213',6, '214',7, '215',8, '221',9, '222',10, 13) LINE,
72 SUBSTR(SL.YYMM,5,2) MM, SUM(AMT) AMT
73 FROM SLIP_SUMM SL
74 WHERE SL.YYMM LIKE '1997%'
75 AND SL.ACCOUNT BETWEEN '211' AND '229'
76 GROUP BY DECODE(SUBSTR(SL.ACCOUNT,1,3),'211',4, '212',5,
77 '213',6, '214',7, '215',8, '221',9, '222',10, 13),
78 SUBSTR(SL.YYMM,5,2)
79 ) X, COPY_T Y
80 /\*
81 직접비(4~10)의 경우 원시로우와 직접비계 두번의 복제가 필요하다.
82 이중 계로 사용될 것은 라인번호 11을 부여한다.
83 그리고 13의 경우 간접비로서 이는 한번만 읽어도 되므로 DECODE()문에서
84 NULL을 반환하여 복제가 되지 않도록 한다
85 */
86 WHERE Y.NO IN ( LINE,DECODE(LINE,13,NULL,11) ) AND Y.NO <=14
87 GROUP BY Y.NO, MM
88 )
89 GROUP BY LINE
90 ) X, COPY_T Y
91 WHERE
92 /\*
93 1) 라인3,11은 공헌이익을 구하기 위해 12로 복제하고
94 2) 라인 3,11,13은 경상이익을 구하기 위해 14로 복제하고
95 3)나머지는 그대로 읽으면 되므로 라인번호 그대로 한번만 복제한다
96 */
97 Y.NO IN (LINE, DECODE(LINE,3,12, 11,12), DECODE(LINE,3,14,11,14,13,14))
98 AND Y.NO <= 14
99 GROUP BY Y.NO ;
LINE TOT MON1 MON2 MON3 MON4 MON5 MON6 MON7 MON8 MON9 MON10 MON11 MON12
==== === ==== ===== ==== ==== ===== ===== ==== ===== ===== ====== ====== ======
1 725910 46300 32820 54620 48580 63720 57560 46300 32820 54620 48580 63720 57560
2 585550 38172 23814 43802 36510 50694 42552 38172 23814 43802 36510 50694 42552
3 140360 8128 9006 10818 12070 13026 15008 8128 9006 10818 12070 13026 15008
4 14450 969 1150 1280 1060 1230 1149 969 1150 1280 1060 1230 1149
5 10980 872 786 1045 840 922 1260 872 786 1045 840 922 1260
6 6147 415 545 674 715 508 620 415 545 674 715 508 620
7 1692 160 80 120 280 220 110 160 80 120 280 220 110
8 3420 289 142 381 478 512 259 289 142 381 478 512 259
9 5695 358 280 492 586 664 433 358 280 492 586 664 433
10 656 97 34 68 35 52 80 97 34 68 35 52 80
11 36200 2582 2733 3298 3038 3084 3393 2582 2733 3298 3038 3084 3393
12 104160 5546 6273 7520 9032 9942 11615 5546 6273 7520 9032 9942 11615
13 24120 265 182 416 523 712 849 265 182 416 523 712 849
14 80040 5281 6091 7104 8509 9230 10766 5281 6091 7104 8509 9230 10766
14 개의 행이 선택되었습니다.