[6장] DML

 

 

 

- 명령어 분류

  * DML(Data Manipulation Language) : INSERT(입력) , UPDATE(변경) , DELETE(삭제) , MERGE(병합)
  * DDL(Data Definition Language) : CREATE (생성) , ALTER (수정) ,TRUNCATE (잘라내기) , DROP (삭제)
  * DCL(Data Control Language) : GRANT (권한 주기) , REVOKE (권한 뺏기)
  * TCL( Transaction Control Language): COMMIT (확정) , ROLLBACK (취소)
  * SELECT : 어떤 분류에서는 DQL (Data Query Language) 라고 하기도 함

 

 

1. INSERT

 

 (1) INSERT를 사용하여 단일 행 입력하기

 

[문법]  INSERT INTO table [(column1, column2,……)]

           VALUES (value 1 , value 2,….) ;

 

예제 1) Dept2 테이블에 아래와 같은 내용으로 새로운 부서 정보를 입력하세요.

            * 부서번호 : 9000        * 부서명 : 특판1팀        * 상위부서 : 영업부        * 지역 : 임시지역

 

SCOTT>  INSERT INTO dept2(dcode , dname , pdept ,area )       2        VALUES (9000 , '특판1팀','영업부','임시지역') ;

 

 

* 모든 컬럼에 데이터를 넣을 경우에는 아래와 같이 테이블 이름 뒤에 컬럼 이름 생략 가능

 

SCOTT>  INSERT INTO dept2       2        VALUES (9000 , '특판1팀','영업부','임시지역') ;

 

 

 

예제 2) 아래 정보를 professor 테이블에 입력하세요.(날짜 데이터 입력하기)

             * 교수번호 : 5001      * 교수이름 : 김설희      * ID : Love_me      * POSITION : 정교수
             * PAY : 510             * 입사일 : 2011년 11월 14일

 

SCOTT>  INSERT INTO professor (profno , name , id , position , pay , hiredate)       2        VALUES (5001,'김설희','Love_me','정교수',510,'2011-11-14');

 

 

날짜 형식이 유닉스(리눅스)용 오라클(DD-MON-YY)과 윈도용 오라클(YYYY-MM-DD)이 다릅니다.
유닉스(리눅스)용 오라클에서는 날짜 형식을 미리 변경 후 위와 같이 입력하던지 아니면 날짜 부분에 TO_DATE 함수를 사용하여 TO_DATE('2011-11-14', 'YYYY-MM-DD') 형식으로 입력해야 함

 

* 날짜 형식을 변경하는 명령어

 

SQL> ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD:HH24:MI:SS' ;

 

 

 

예제 3) Null 값 입력하기

* 자동 NULL 값 입력하기 : 데이터를 입력할 때 컬럼에 값을 안 주면 자동으로 NULL 값이 들어감
* 수동 NULL 값 입력하기 : 데이터부분에 NULL 값을 적어주면 됨

 

 

 

 (2) INSERT를 사용하여 여러 행 입력하기

 

SCOTT>  Create TABLE professor2         // Pofessor2 테이블 생성       2        AS SELECT * FROM professor ;

 

SCOTT>  INSERT INTO professor2       2        SELECT * FROM Professor ;

 

 

 

 (3) INSERT ALL을 이용한 여러 테이블에 여러 행 입력하기

 

예제 1) 다른 테이블에 한꺼번에 데이터 입력하기

 

SCOTT>  INSERT ALL       2        INTO p_01 (no , name)       3        VALUES (1,'AAA')       4        INTO p_02 (no , name)       5        VALUES(2,'BBB')       6        SELECT * FROM dual ;

 

 

 

예제 2) 다른 테이블의 데이터를 가져와서 입력하기

  Professor 테이블에서 교수번호가 1000번에서 1999번까지인 교수의 번화와 교수의 이름은 p_01테이블에

  입력하고 교수번호가 2000번에서 2999번까지인 교수의 번호와 이름은 p_02테이블에 입력하세요.

 

SCOTT>  INSERT ALL       2        WHEN profno BETWEEN 1000 AND 1999 THEN       3            INTO p_01 VALUES(profno,name)       4        WHEN profno BETWEEN 2000 AND 2999 THEN       5            INTO p_02 VALUES(p       6        SELECT profno,name       7        FROM professor  ;

 

 

 

예제 3) 다른 테이블에 동시에 같은 데이터 입력하기

  Professor 테이블에서 교수번호가 3000번에서 3999번인 교수들의 교수 번호와 이름은 p_01테이블과

  p_02테이블에 동시에 입력하세요.

 

SCOTT>  INSERT ALL       2            INTO p_01 VALUES (       3            INTO p_02 VALUES (profno,name)       4        SELECT profno,name       5        FROM professor       6        WHERE profno BETWEEN 3000 AND 3999 ;

 

 

 

2. UPDATE

 

 : UPDATE 문장은 기존 데이터를 다른 데이터로 변경할 때 사용하는 방법

 

[문법]  UPDATE  table

           SET  column = value

           WHERE  조건 ;

 

 

예제 1) Professor 테이블에서 직급이 조교수 인 교수들의 BONUS 를 100 만원으로 인상하세요.

 

SCOTT>  UPDATE professor       2       SET bonus = 100       3       WHERE position ='조교수' ;

 

 

 

예제 2) Professor 테이블에서 차범철 교수의 직급과 동일한 직급을 가진 교수들 중 현재 급여가 250 만원이 안 되는

            교수들의 급여를 15% 인상하세요.

 

SCOTT>  UPDATE professor       2       SET pay = pay * 1.15       3       WHERE position = ( SELECT position       4                                    FROM professor       5                                    WHERE name = '차범철')       6       AND pay < 250 ;

 

※ UPDATE 문장에서 WHERE 절을 누락시키지 않도록 각별히 주의

 

 

 

 

 

3. DELETE

 

 : DELETE 문장은 데이터를 삭제하는 구문

 

[문법]  DELETE  FROM  table

           WHERE  조건 ;

 

 

예제 1) Dept2 테이블에서 부서번호(DCODE)가 9000 번에서 9100 번 사이인 매장들을 삭제하세요.

 

SCOTT>  DELETE FROM dept2       2        WHERE dcode between 9000 and 9100 ;

 

 

-  DELETE 문은 해당 데이터가 사용하고 있던 파일의 저장공간 (익스텐트 –extent)은 반납하지 않고

    데이터만 삭제하는 구문입니다.

-  사실 데이터는 삭제되지 않고 해당 블록에 그대로 남아 있으며 특별한 툴(BBED 등)을 이용하면 DELETE 된 데이터도

    전부 복구할 수 있는 방법도 있습니다. 여기서 중요한 건 데이터가 DELETE 되더라도 저장공간을 반납 하지 않기 때문에

    용량이 줄어들 지 않는다는 뜻입니다. 예를 들어 100만 건 데이터가 있는 테이블의 용량이 100MB 였는데 1만 건 만 남기고

    DELETE 한다고 해서 용량이 1MB가 되지는 않는다는 점입니다

 

- 만약 모든 데이터가 지워졌는데 용량까지 줄이고 싶다면 REORG 작업을 별도로 해줘야 한다.

 

1. 테이블 생성 ​SCOTT>CONN / AS SYSDBA ; SYS> CREATE TABLE scott.test01 (no NUMBER, name VARCHAR2(20),                                                      addr VARCHAR2(20)); ​   2. 위에서 생성했던 SCOTT.TEST01 테이블에 데이터를 추가 SYS> BEGIN    2    FOR i IN 1..1000 LOOP    3       INSERT INTO scott.test01    4       VALUES (i , DBMS_RANDOM.STRING('A',19)    5                       , DBMS_RANDOM.STRING('B',19) );    6       END LOOP;    7       COMMIT;    8    END;    9    / PL/SQL procedure successfully completed. 3. 데이터건수와 테이블 용량을 측정 SYS> SELECT COUNT(*) FROM SCOTT.TEST01;     COUNT(*) ------------             1000        ← 1000 건이 입력 ​ SYS> SELECT SUM(BYTES)/1024/1024 MB    2    FROM DBA_SEGMENTS    3    WHERE OWNER='SCOTT'    4    AND SEGMENT_NAME='TEST01';            MB ---------            28       ← 용량은 여전히 28MB ​ 4. 1000 건의 데이터 중 300 건만 삭제합니다. SYS> DELETE FROM SCOTT.TEST01    2   WHERE no BETWEEN 1 AND 300 ; 300 rows deleted.​   SYS> COMMIT; Commit complete. SYS> SELECT COUNT(*) FROM SCOTT.TEST01;     COUNT(*) ------------             700   SYS> SELECT SUM(BYTES)/1024/1024 MB    2    FROM DBA_SEGMENTS    3    WHERE OWNER='SCOTT'    4    AND SEGMENT_NAME='TEST01';            MB ---------            28       ← 용량은 여전히 28MB ​ ​ 5. 테이블 REORG(리오그) 작업을 합니다.    여러 가지 방법이 있지만 여기서는 테이블스페이스를 이동시키는 방법을 사용  SYS> SELECT TABLE_NAME,TABLESPACE_NAME    2    FROM DBA_TABLES    3    WHERE TABLE_NAME='TEST01';   TABLE_NAME        TABLESPACE_NAME ------------    ----------------         TEST01                           USERS        ← 현재 USERS 테이블스페이스에 위치   ​SYS> ALTER TABLE SCOTT.TEST01 MOVE TABLESPACE USERS; Table altered. 위 문장은 테이블을 다른 장소로 옮기는 명령어지만 현재 테이블 스페이스 이름만 적어주면 용량이 줄어듭니다. ​   ​SYS> SELECT SUM(BYTES)/1024/1024 MB    2    FROM DBA_SEGMENTS    3    WHERE OWNER='SCOTT'    4    AND SEGMENT_NAME='TEST01';             MB ---------            .0625       ← 용량이 현저하게 줄어든 것을 확인할 수 있음 ​ ​SYS> SELECT COUNT(*) FROM SCOTT.TEST01 ;     COUNT(*) ------------             700           ← 데이터는 700 건 있습니다. ​

 

 

위 DELETE 후 테이블 용량을 줄이는 방법은 주의하실 사항이 몇 가지 있습니다.
해당 테이블에 인덱스나 뷰 등이 생성되어 있다면 부가적인 작업이 더 있으니 보다 자세한 사항 은 운영 매뉴얼을 참고

★★ 'DELETE 를 수행해도 테이블 용량은 줄어들지 않는다!'

 

 

 

4. MERGE

 

 : MERGE란 여러 테이블의 데이터를 합치는 병합을 의미, 문법이 다소 복잡 ㅜ.ㅜ

 

[문법]  MERGE INTO Table1
   2      USING Table2
   3      ON ( 병합 조건절 )
   4      WHEN MATCHED THEN
   5         UPDATE SET 업데이트 내용
   6         DELETE WHERE 조건
   7      WHEN NOT MATCHED THEN
   8         INSERT VALUES(컬럼 이름) ;

 

 

 

예제 1) 일별 판매 테이블인 pt_01과 pt_02 테이블이 있고 판매 집계 테이블인 p_total 테이블이 있습니다.

            매일 새벽에 일별 판매 테이블과 판매 집계테이블을 MERGE 한다고 가정하고  MERGE 하는 SQL

 

 

- MERGE 작업 전 테이블 조회 :

 

SCOTT> SELECT * FROM pt_01 ; ​    판매번호       제품번호        수량         금액 --------- --------- -------- -------   12010101           1000            1            500   12010102           1001            1            400   12010103           1003            1            300

SCOTT> SELECT * FROM pt_02 ; ​    판매번호       제품번호        수량         금액 --------- --------- -------- -------   12010201           1004            1            600   12010202           1000            1            500   12010203           1005            1            700

 

SCOTT> SELECT * FROM p_total ;   no rows selected

 

 

 

- MERGE 작업 QUERY 1 (pt_01 테이블과 p_total 테이블 병합)

 

SCOTT> MERGE INTO p_total  total      2       USING pt_01  p01      3       ON (total.판매번호=p01.판매번호)      4       WHEN MATCHED THEN      5           UPDATE SET total.제품번호 = p01.제품번호      6       WHEN NOT MATCHED THEN      7           INSERT VALUES(p01.판매번호 , p01.제품번호 , p01.수량 , p01.금액) ;

 

 

 

- MERGE 작업 QUERY 2 (pt_02 테이블과 p_total 테이블 병합)

 

SCOTT> MERGE INTO p_total  total      2       USING pt_02  p02      3       ON (total.판매번호=p02.판매번호)      4       WHEN MATCHED THEN      5           UPDATE SET total.제품번호 = p02.제품번호      6       WHEN NOT MATCHED THEN      7           INSERT VALUES(p02.판매번호 , p02.제품번호 , p02.수량 , p02.금액) ;

 

 

 

- MERGE 작업 후 테이블 조회 :

 

SCOTT> SELECT * FROM  p_total  ; ​    판매번호       제품번호        수량         금액 --------- --------- -------- -------   12010102           1001            1            400 ​  12010103           1003            1            300 ​  12010101           1000            1            500   12010202           1000            1            500   12010201           1004            1            600   12010203           1005            1            700​

 

 

 

 

 

 

 

5. TRANSACTION 관리하기

 

 : 트랜잭션이란 논리적인 작업 단위를 의미, 쉽게 말하면 여러가지 DML 작업들을 하나의 단위로 묶어 둔 것을 의미

 

 - 해당 트랜잭션 내에 있는 모든 DML 이 성공해야 해당 트랜잭션이 성공하는 것이고

    만약1개의 DML 이라도 실패하면 전체가 실패

 

 - COMMIT : 트랜잭션 내의 작업의 결과를 확정하는 명령어

 - ROLLBACK : 트랜잭션 내의 모든 명령들을 취소하는 명령어

 - 즉, 사용자가 10개의 DML을 연속적으로 수행 한 후 COMMIT 을 수행하면 10개의 DML 모두내용이 더 이상 취소가 안되고

    확정. 10개의 DML 을 수행 한 후 ROLLBACK 를 수행하면 가장 마지막 DML 만 취소되는 것이 아니라

    해당 트랜잭션 내의 모든 (여기서는 10개) DML 이 취소가 됩니다.

 

 

 

6. DML ERROR LOGGING 하기

​ 

 : Oracle 10g R2 버전부터 DML 작업 시 장애가 날 경우 해당 장애 내용을 별도의 테이블에 기록해 주는 기능이 제공됩니다.
   이 기능은 DBMS_ERRLOG 패키지를 통해 구현하게 되며 이 패키지를 실행하면 DML 에러를 저장하는

   로그 테이블을 생성하게 됩니다.

  로그 테이블명은 기본값은 ERR$_SUBSTR(table_name,1,25) 로 설정되며 사용자가 해당 패키지를 실행 할 때

  다른 이름으로 지정 할 수 있습니다.

 

 

1. DBMS_ERRLOG 패키지를 수행해서 에러 로깅 테이블 DML_ERRORS 생성 ​SCOTT>SELECT * FROM dml_err_test ;       NO      NAME ------ -------        1        AAA        2        BBB       ​ ​이 테이블은 NO 컬럼에 Primary Key가 설정이 되어 있음 이 테이블에 NO 컬럼 값이 중복되는 값을 입력하게 되면 에러가 발생할 것입니다. 그 내용을 로깅하는 것을 테스트 하는 것입니다. 에러 내용을 로깅하기 위해 DBMS_ERRLOG 패키지를 아래와 같이 실행합니다. ​ ​생성된 에러를 기록하는 로그 테이블 이름 : DML_ERRORS SCOTT> BEGIN      2      DBMS_ERRLOG.CREATE_ERROR_LOG (      3      dml_table_name => 'DML_ERR_TEST',      4      err_log_table_name => 'DML_ERRORS' );      5      END ;      6      / ​ ​SCOTT> DESC dml_errors ;    Name                                                Null?             Type --------------------------- --------- -------------------  ORA_ERR_NUMBER$                                               NUMBER  ORA_ERR_MESG$                                                   VARCHAR2(2000)  ORA_ERR_ROWID$                                                 ROWID  ORA_ERR_OPTYP$                                                VARCHAR2(2)  ORA_ERR_TAG$                                                    VARCHAR2(2000)  NO                                                                        VARCHAR2(4000)  NAME                                                                    VARCHAR2(4000) ​ ​  2. DML_ERR_TEST 테이블에 에러를 발생하는 DML 을 수행 SCOTT> INSERT INTO dml_err_test      2      VALUES (1,'CCC')      3      LOG ERRORS INTO dml_errors('INSERT..RL=UNLIMITED')      4      REJECT LIMIT UNLIMITED ;         0 rows created.   3. 에러를 확인 create or replace procedure print_table( p_query in varchar2 ) AUTHID CURRENT_USER is      l_theCursor integer default dbms_sql.open_cursor;      l_columnValue varchar2(4000);      l_status integer;      l_descTbl dbms_sql.desc_tab;      l_colCnt number; begin      execute immediate      'alter session set           nls_date_format=''yyyy-mm-dd:hh24:mi:ss'' ';      dbms_sql.parse( l_theCursor, p_query, dbms_sql.native );      dbms_sql.describe_columns      ( l_theCursor, l_colCnt, l_descTbl );      for i in 1 .. l_colCnt loop           dbms_sql.define_column           (l_theCursor, i, l_columnValue, 4000);      end loop;      l_status := dbms_sql.execute(l_theCursor);      while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop           for i in 1 .. l_colCnt loop                dbms_sql.column_value                ( l_theCursor, i, l_columnValue );                dbms_output.put_line                ( rpad( l_descTbl(i).col_name, 30 )                || ': ' ||                l_columnValue );           end loop;           dbms_output.put_line( '-----------------' );      end loop;      execute immediate           'alter session set nls_date_format=''yyyy-mm-dd'' '; exception      when others then           execute immediate                'alter session set nls_date_format=''yyyy-mm-dd'' ';      raise; end; /       SCOTT>  exec print_table('SELECT * FROM dml_errors'); ORA_ERR_NUMBER$                   : 1 ORA_ERR_MESG$                       : ORA-00001: unique constraint (SCOTT.SYS_C0014256) violated ORA_ERR_ROWID$                     : ORA_ERR_OPTYP$                     : I ORA_ERR_TAG$                         : INSERT..RL=UNLIMITED NO                                             : 1 NAME                                         : CCC ----------------- PL/SQL procedure successfully completed.

 

 

 - 위의 예와 같이 DML 문장을 수행할 때 추가적인 옵션을 주어서 DML 문장 수행 시 에러가 발생 할 경우

    해당 에러를 별도의 테이블에 기록 할 수 있습니다.

    그러나 몇 가지 제한사항이 있습니다.

1. Constraint 중에 deffered constraints를 위반한 사항은 로깅되지 않습니다. 즉 immediate로 위반 사항은

    모두 기록되지만 commit 수행시에 일괄적으로 체크되는 경우는 기록되지 않습니다.
2. Direct Path 방식으로 데이터를 입력하는 INSERT나 MERGE일 경우에 Unique 제약조건 위반이나

    Index 조건 (Unique Index) 위반일 경우 기록되지 않습니다.
3. 모든 UPDATE나 MERGE일 경우에 Unique 제약조건 위반이나 Index 조건 (Unique Index) 위반일 경우

    기록되지 않습니다

 

이 기능은 PL/SQL 에서도 동일하게 사용 가능 합니다.

그러나 이 기능은 DML 작업에 부하를 주게 되어 전체적인 DML 작업의 성능이 저하된다는 단점이 있으니 성능이 중요한 DML 작업등이나 실시간 변경 량이 많은 테이블 등에는 충분한 테스트 를 거친 후 사용 여부를 결정하시길 바랍니다. 

 

 

 

 

 

서진수,『오라클 SQL과 PL/SQL』, 생능출판사, 2012, pp.192-212