1.사례

• 고객이 해약을 하여 정산을 거쳤을 때 고객에게 지불할 보증금반환금/청구해야 할 위약금/기기철거비용이 하나의 로우에 있다고 가정

• 필요한 다른 처리가 완료된 후 이 값들을 다른 계정과목을 갖는 별도의 로우로 생성

2.테이블 설계

(1)전표테이블(CHIT)

고객번호(CUST_NO/PK),계정과목(ACOUNT/PK),금액(AMT),생성일자(CRE_DT)

(2)계약정산테이블(CONSET)

고객번호(CUST_NO/PK),상품명(PRO_NM),보증금반환금(DEP_AMT),위약금(PEN_AMT),기기철거비(DEM_AMT),계약일(CON_DT),해약일(CAN_DT)

3.CREATE TABLE

CREATE TABLE ORA9.CHIT (
2 CUST_NO VARCHAR2(4) NOT NULL
3 , ACOUNT VARCHAR2(4) NOT NULL
4 , AMT NUMBER(10) NULL
5 , CRE_DT VARCHAR2(8) NOT NULL
6 )
7 TABLESPACE ORA9;

테이블이 생성되었습니다.

COMMENT ON TABLE ORA9.CHIT IS '전표';
주석이 생성되었습니다.

COMMENT ON COLUMN ORA9.CHIT.CUST_NO IS '고객번호';
주석이 생성되었습니다.

COMMENT ON COLUMN ORA9.CHIT.ACOUNT IS '계정과목';
주석이 생성되었습니다.

COMMENT ON COLUMN ORA9.CHIT.AMT IS '금액';
주석이 생성되었습니다.

COMMENT ON COLUMN ORA9.CHIT.CRE_DT IS '생성일자';
주석이 생성되었습니다.

ALTER TABLE ORA9.CHIT ADD (
2 CONSTRAINT CHIT_PK PRIMARY KEY ( CUST_NO,ACOUNT )
3 USING INDEX TABLESPACE ORA9 );

테이블이 변경되었습니다.

CREATE TABLE ORA9.CONSET (
2 CUST_NO VARCHAR2(4) NOT NULL
3 , PRO_NM VARCHAR2(2) NOT NULL
4 , DEP_AMT NUMBER(10) NULL
5 , PEN_AMT NUMBER(10) NULL
6 , DEM_AMT NUMBER(10) NULL
7 , CON_DT VARCHAR2(8) NOT NULL
8 , CAN_DT VARCHAR2(8) NOT NULL
9 )
10 TABLESPACE ORA9;

테이블이 생성되었습니다.

COMMENT ON TABLE ORA9.CONSET IS '계약정산';
주석이 생성되었습니다.

COMMENT ON COLUMN ORA9.CONSET.CUST_NO IS '고객번호';
주석이 생성되었습니다.

COMMENT ON COLUMN ORA9.CONSET.PRO_NM IS '상품명';
주석이 생성되었습니다.

COMMENT ON COLUMN ORA9.CONSET.DEP_AMT IS '보증금반환금';
주석이 생성되었습니다.

COMMENT ON COLUMN ORA9.CONSET.PEN_AMT IS '위약금';
주석이 생성되었습니다.

COMMENT ON COLUMN ORA9.CONSET.DEM_AMT IS '기기철거비';
주석이 생성되었습니다.

COMMENT ON COLUMN ORA9.CONSET.CON_DT IS '계약일';
주석이 생성되었습니다.

COMMENT ON COLUMN ORA9.CONSET.CAN_DT IS '해약일';
주석이 생성되었습니다.

ALTER TABLE ORA9.CONSET ADD (
2 CONSTRAINT CONSET_PK PRIMARY KEY ( CUST_NO )
3 USING INDEX TABLESPACE ORA9 );

테이블이 변경되었습니다.

4.INSERT

INSERT INTO ORA9.CONSET VALUES('0001','A',1000000,100000,50000,'20001121','20010304');
1 개의 행이 만들어졌습니다.

INSERT INTO ORA9.CONSET VALUES('0002','B',2000000,200000,0,'19990123','20030602');
1 개의 행이 만들어졌습니다.

INSERT INTO ORA9.CONSET VALUES('0003','C',5600000,560000,50000,'20070123','20090403');
1 개의 행이 만들어졌습니다.

INSERT INTO ORA9.CONSET VALUES('0004','A',1000000,100000,50000,'20050405','20060330');
1 개의 행이 만들어졌습니다.

INSERT INTO ORA9.CONSET VALUES('0005','G',0,50000,0,'20080601','20090403');
1 개의 행이 만들어졌습니다.

INSERT INTO ORA9.CONSET VALUES('0006','H',2500000,250000,70000,'20090104','20090221');
1 개의 행이 만들어졌습니다.

INSERT INTO ORA9.CONSET VALUES('0007','E',0,50000,30000,'20090217','20090407');
1 개의 행이 만들어졌습니다.

INSERT INTO ORA9.CONSET VALUES('0008','G',1000000,100000,0,'20040523','20090403');
1 개의 행이 만들어졌습니다.

INSERT INTO ORA9.CONSET VALUES('0009','D',2300000,230000,50000,'20090101','20090325');
1 개의 행이 만들어졌습니다.

INSERT INTO ORA9.CONSET VALUES('0010','B',3000000,300000,80000,'20030402','20040521');
1 개의 행이 만들어졌습니다.

COMMIT;
커밋이 완료되었습니다.

5. 결과

TRUNCATE TABLE ORA9.CHIT;

테이블이 잘렸습니다.

SELECT *
2 FROM ORA9.CHIT;

선택된 레코드가 없습니다.

INSERT INTO CHIT(CUST_NO,CRE_DT,ACOUNT,AMT)
2 SELECT x.CUST_NO /*전표테이블.고객번호*/
3 ,TO_CHAR (SYSDATE, 'YYYYMMDD') /*전표테이블.생성일자*/
4 ,DECODE ( y.NO2, 1, '1234', 2, '5678', 3, '9876') /*전표테이블.계정과목*/
5 ,DECODE ( y.NO, 1, x.DEP_AMT, 2, x.PEN_AMT, 3, x.DEM_AMT) /*전표테이블.금액*/
6 FROM ORA9.CONSET x, ORA9.COPY_T y
7 WHERE x.CAN_DT = '20090403' /*전표로 출력할려는 해약일*/
8 AND y.NO <= 3;

9 개의 행이 만들어졌습니다.

SELECT \*
2 FROM ORA9.CHIT;

CUST_NO ACOUNT AMT CRE_DT
======== ====== ======= =======

9 개의 행이 선택되었습니다.

SELECT \*
2 FROM ORA9.CONSET
3 WHERE CAN_DT = '20090403';

CUST_NO PRO_NM DEP_AMT PEN_AMT DEM_AMT CON_DT CAN_DT
======= ======= ======= ======= ======== ======= =======

3 개의 행이 선택되었습니다.

6. 주석

두 개의 테이블은 연결고리가 존재하지 않는다.

결과는 당연히 카테시안 곱만큼의 조인결과가 생성될 것이다.

입력받은 날짜에 해약한 고객들마다 3개씩 로우가 생성 되고 필요한 가공을 통해 입력된다.

7.보완

• 반환할 보증금, 청구할 위약금, 기기철거비가 없는 경우, 즉 금액이 0인 경우에는 전표를 발생시키지 않는다.

TRUNCATE TABLE ORA9.CHIT;

테이블이 잘렸습니다.

SELECT *
2 FROM ORA9.CHIT;

선택된 레코드가 없습니다.

INSERT INTO CHIT(CUST_NO,CRE_DT,ACOUNT,AMT)
2 SELECT x.CUST_NO /*전표테이블.고객번호*/
3 ,TO_CHAR (SYSDATE, 'YYYYMMDD') /*전표테이블.생성일자*/
4 ,DECODE ( y.NO2, 1, '1234', 2, '5678', 3, '9876') /*전표테이블.계정과목*/
5 ,DECODE ( y.NO, 1, x.DEP_AMT, 2, x.PEN_AMT, 3, x.DEM_AMT) /*전표테이블.금액*/
6 FROM ORA9.CONSET x, ORA9.COPY_T y
7 WHERE x.CAN_DT = '20090403' /*전표로 출력할려는 해약일*/
8 AND y.NO IN ( DECODE(x.DEP_AMT, 0, NULL, 1)
9 ,DECODE(x.PEN_AMT, 0, NULL, 2)
10 ,DECODE(x.DEM_AMT, 0, NULL, 3));

6 개의 행이 만들어졌습니다.

SELECT *
2 FROM ORA9.CHIT;

CUST_NO ACOUNT AMT CRE_DT
======== ====== ======= =======

6 개의 행이 선택되었습니다.

SELECT \*
2 FROM ORA9.CONSET
3 WHERE CAN_DT = '20090403';

CUST_NO PRO_NM DEP_AMT PEN_AMT DEM_AMT CON_DT CAN_DT
======= ======= ======= ======= ======== ======= =======

0 50000 0 20080601 20090403

0 20040523 20090403

3 개의 행이 선택되었습니다.

8. 실습 : 나열될 칼럼을 여러 레코드로 생성

(1)아래와 같은 테이블이 있는 때 특정 부서(AA)의 모든 입출금 내역을 검색하라

<예적금원장>

CREATE TABLE ACCOUNT(
MANAGE_NUM VARCHAR2(10),
DEPT_COD VARCHAR2(10)
)

<입금내역>
CREATE TABLE INPUT(
MANAGE_NUM VARCHAR2(10),
INPUT_DATE VARCHAR2(8),
AMT NUMBER
)

<출금내역>
CREATE TABLE OUTPUT(
MANAGE_NUM VARCHAR2(10),
OUTPUT_DATE VARCHAR2(8),
AMT NUMBER
)

(2) 테이블 생성/INSERT

SQL> CREATE TABLE ACCOUNT(
2 MANAGE_NUM VARCHAR2(10),
3 DEPT_COD VARCHAR2(10)
4 );

테이블이 생성되었습니다.

SQL> INSERT INTO ACCOUNT VALUES('111','AA');
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO ACCOUNT VALUES('222','AA');
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO ACCOUNT VALUES('333','AA');
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO ACCOUNT VALUES('444','BB');
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO ACCOUNT VALUES('555','CC');
1 개의 행이 만들어졌습니다.

SQL> CREATE TABLE INPUT(
2 MANAGE_NUM VARCHAR2(10),
3 INPUT_DATE VARCHAR2(8),
4 AMT NUMBER
5 );

테이블이 생성되었습니다.

SQL> INSERT INTO INPUT VALUES('111','20060301',1000);
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO INPUT VALUES('111','20060302',1000);
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO INPUT VALUES('222','20060303',1000);
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO INPUT VALUES('333','20060304',1000);
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO INPUT VALUES('333','20060304',1000);
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO INPUT VALUES('555','20060301',1000);
1 개의 행이 만들어졌습니다.

SQL> CREATE TABLE OUTPUT(
2 MANAGE_NUM VARCHAR2(10),
3 OUTPUT_DATE VARCHAR2(8),
4 AMT NUMBER
5 );

테이블이 생성되었습니다.

SQL> INSERT INTO OUTPUT VALUES('111','20060301',1000);
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO OUTPUT VALUES('111','20060302',1000);
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO OUTPUT VALUES('111','20060303',1000);
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO OUTPUT VALUES('222','20060301',1000);
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO OUTPUT VALUES('444','20060301',1000);
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO OUTPUT VALUES('555','20060301',1000);
1 개의 행이 만들어졌습니다.

SQL> CREATE TABLE TAB9(
2 COL1 VARCHAR2(10),
3 AMT NUMBER
4 );

테이블이 생성되었습니다.

SQL> INSERT INTO TAB9 VALUES('A',10);
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO TAB9 VALUES('A',20);
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO TAB9 VALUES('B',30);
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO TAB9 VALUES('D',40);
1 개의 행이 만들어졌습니다.

SQL> CREATE TABLE TAB10(
2 COL1 VARCHAR2(10),
3 AMT NUMBER
4 );

테이블이 생성되었습니다.

SQL> INSERT INTO TAB10 VALUES('A',50);
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO TAB10 VALUES('C',60);
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO TAB10 VALUES('C',70);
1 개의 행이 만들어졌습니다.
SQL> INSERT INTO TAB10 VALUES('D',80);
1 개의 행이 만들어졌습니다.

(3)결과

SQL> SELECT COL1,SUM(AMT)
2 FROM(
3 SELECT COL1,AMT FROM TAB9
4 UNION ALL
5 SELECT COL1,AMT FROM TAB10
6 ) GROUP BY COL1;

COL1 SUM(AMT)
==== ========
A 80
B 30
C 130
D 120