6. DML (Data Manipulation Language)

[기타SQL]/oracle SQL

6. DML (Data Manipulation Language)

JJ* 2014. 8. 28. 21:55

6장. DML(Data Manipulation Language).

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)	COMMINT(확정), ROLLBACK(취소)
SELECT	어떤 부류에서는 DQL(Data Query Language)라고도 합니다

1. INSERT

1) 단일행 입력하기

[문법]

INSERT INTO TABLE명 (칼럼1, 칼럼2,...) → 모든 칼럼에 데이터를 넣을 경우에는 칼럼이름 생략가능.

VALUES (값1, 값2,...) ;

* 리눅스용 오라클에서는 날짜형식이 DD-MON-YY 형태이고,

윈도용 오라클에서는 날짜형식이 YYYY-MM-DD 형태이다.

[날짜형식을 변경하는 명령어] : ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD:HH24:MI:SS' ;

* 음수 입력할때에도 양수와 동일하다.

- no1은 number형 사용할때 자리수를 지정하지 않았고, no2는 정수부 자리수로 3자리 지정했고, no3은 소수점이하 자리수까지 지정했다. 그래서 no1과 no3은 동일하게 정수와 소수와 음수까지 이상없이 입력되었지만, no2는 소수점이하가 표시되지 않았다.

2) 서브 쿼리를 사용하여 여러행 입력하기(ITAS 이타스)

[문법]

INSERT INTO 테이블명2

SELECT * FROM 복사할테이블명

→ 복사할 테이블과 테이블2의 칼럼개수와 데이터형이 동일해야 한다. (ITAS 인타스 라고부름)

- 테이블을 생성할 때 5번행 조건을 사용하여 professor 테이블의 데이터는 입력되지 않도록 복사해온뒤, ITAS를 이용해 원하는 데이터를 한꺼번에 가져와서 입력하였다.

3) INSERT ALL 이용하여 여러테이블에 여러행 입력하기 (잘 안씀)

2. UPDATE : 기존 데이터를 다른 데이터로 변경할 때.

[문법]

UPDATE 테이블명

SET 칼럼명 = 값

WHERE 조건 ;

3. DELETE : 데이터 삭제.

[문법]

DELETE FROM 테이블명

WHERE 조건 ;

→ delete해도 데이터는 삭제되지 않고 해당 블록에는 그대로 남아있으며, 특별한 툴 (BBED 등)을 이용하면 delete된 데이터도 전부 복구할 수 있다. 그래서 delete한 후 테이블의 크기를 확인해 보면 크기가 줄어들 지 않고 그대로이다.

Test 1. 테이블의 데이터 삭제 후 용량 확인하기

Step 1. 테스트용 테이블을 생성하고 대량의 데이터를 입력.

SCOTT>CONN    /   AS    SYSDBA ;
SYS> CREATE TABLE scott.test01 (no NUMBER, name VARCHAR2(20), addr VARCHAR2(20));

SYS>BEGIN
   2      FOR    i    IN    1..500000    LOOP
   3         INSERT    INTO    scott.test01
   4       VALUES(i, DBMS_RANDOM.STRING('A',19) ,
   5                 DBMS_RANDOM.STRING('Q',19) );
   6    END LOOP;
   7     COMMIT;
   8 END;
   9 /

PL/SQL procedure successfully completed.

SYS>SELECT COUNT(*) FROM SCOTT.TEST01;

COUNT(*)
--------------
     500000   <- 50만 건의 데이터가 확인됩니다.

Step 2. 테이블의 크기를 측정

SYS>ANALYZE TABLE scott.test01 COMPUTE STATISTICS ;
Table analyzed.

SYS>SELECT    SUM(BYTES)/1024/1024 MB
   2 FROM   DBA_SEGMENTS
   3 WHERE   OWNER='SCOTT'
   4 AND    SEGMENT_NAME='TEST01';

        MB
---------------
         28    <- 테이블 크기가 28 MB 로 확인됩니다.

SYS>SELECT table_name, num_rows, blocks, empty_blocks
   2 FROM dba_tables
3 WHERE owner=’SCOTT’
4 AND   table_name='TEST01';

TABLE_NAME    NUM_ROWS    BLOCKS      EMPTY_BLOCKS
-------------- ------------- -----------   ------------------
TEST01               500000           3520                64

NUM_ROWS 는 데이터 건 수이고 BLOCKS 는 사용중인 Block 의 개수며  EMPTY_BLOCKS는 빈 블록 개수

SYS> SELECT   COUNT(DISTINCT DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid) ||
   2                             DBMS_ROWID.ROWID_RELATIVE_FNO(rowid)) "실사용 블록수"
   3 FROM    scott.test01 ;

실사용 블록수
-------------------
          3447

Step 3. 데이터 삭제 후 테이블 크기를 확인합니다.

SYS>DELETE FROM SCOTT.TEST01;

500000 rows deleted.

SYS>COMMIT;
Commit complete.

SYS>SELECT COUNT(*) FROM SCOTT.TEST01;

COUNT(*)
-------------
          0 <- 모든 데이터가 전부 삭제됨이 확인됩니다.

SYS>SELECT SUM(BYTES)/1024/1024 MB
   2 FROM DBA_SEGMENTS
   3 WHERE OWNER='SCOTT'
   4 AND SEGMENT_NAME='TEST01';

        MB
--------------
         28    <- 용량은 변함없이 그대로 입니다.

SYS>SELECT COUNT(DISTINCT DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid) ||
   2                            DBMS_ROWID.ROWID_RELATIVE_FNO(rowid)) "실사용 블록수"
   3      FROM    scott.test01 ;

실사용 블록수
-----------------
             0 <- 실제 사용하는 블록은 하나도 없다는 것을 알 수 있습니다.
                 즉 실제 데이터가 들어 있는 블록은 없는데 있는 것으로 생각하고 용량을 사용하고 있다는 뜻.

결과 : DELETE 는 데이터만 지우고 용량은 줄이지 못한다.

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

[테이블의 데이터 DELETE 후 Table Reorg(재구성) 하기]

- 9i 부터 가장 많이 사용되는 방법인 테이블스페이스를 이동시키는 방법을 사용.

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.
→ 위 문장은 테이블을 다른 장소로 옮기는 명령어지만 현재 테이블 스페이스 이름만 적어주면 용량이 줄어든다.

**주의사항

해당 테이블에 인덱스나 뷰 등이 생성되어 있다면 부가적인 작업이 더 있으니 자세한 사항은 "오라클 관리 실무" 책을 참고.

4. MERGE : 여러 테이블의 데이터를 합치는 병합.

: table1 테이블과 table2 테이블의 내용을 합쳐서 table1에 모으는 것. 이때 기준은 3행의 조건이 됨.

- 3행의 조건이 만족한다면, table1에 있던 해당내용은 table2의 내용으로 update 또는 delete가 수행되며, 만약 조건이 만족하지 않는다면 table2의 내용이 table1에 신규로 insert 됩니다.

** 주의점 : MERGE구문이 수행될 때 집계테이블(table1)의 데이터와 신규테이블(table2)의 데이터를 비교해서 확인하므로 집계테이블에 데이터가 많아질수록 MERGE작업의 수행속도가 늦어지는 특징이 있다. 그래서 3행의 조건절에 인덱스가 잘 만들어져 있어야 한다.(잘못된 인덱스가 만들어지거나 인덱스가 아예 없으면 MERGE작업의 속도는 아주 늦어진다.)

→ MERGE구문에서 서브쿼리를 사용할 때 바인드 변수의 값을 사용하는 것은 지원이 되지 않는다.

5. TRANSACTION 관리하기★★★

- 트랜잭션이란? : 논리적인 작업 단위(여러가지 DML작업들을 하나의 단위로 묶어 둔 것.)

- 모든DML이 성공해야 해당 트랜잭션이 성공하는 것이고, 만약 1개의 DML이라도 실패하면 전체가 실패하게 된다. (대학교와 같은 과정) 즉, 모든 트랜잭션은 크기가 다를 수 있다.

- TRANSACTION 과정은 DML로 시작하고, DDL,TCL,DCL로 끝난다.

: insert, update, delete를 치는순간 transaction이 시작되고, commit이나 rollback 명령을 수행하면 끝난다.

* COMMIT : 트랜잭션 내의 작업의 결과를 확정.(저장)

* ROLLBACK : 트랜잭션 내의 모든 명령어들을 취소.

- DDL명령(create table..)을 치면 자동으로 오라클이 commit명령을 수행한다.

(commit을 따로 치지 않아도 자동저장되어, 그 후 rollback명령을 치면 명령어가 안먹힌다.)

- DML작업을 한 후에는 반드시 COMMIT이나 ROLLBACK 명령을 수행해야 작업이 마무리가 된다.

[연습문제]
1. Dept2 테이블에 아래와 같은 내용으로 새로운 부서 정보를 입력하세요.
* 부서번호 : 9010
* 부서명 : temp_10
* 상위부서 : 1006
* 지역 : temp area

2. Dept2 테이블에 아래와 같은 내용으로 특정 컬럼에만 정보를 입력하세요
* 부서번호 : 9020
* 부서명 : temp_20
* 상위부서 : Business Department ( 1006 번 부서 )

3. professor 테이블에서 profno 가 3000 번 이하의 교수들의 profno , name , pay 를 가져와서 professor4 테이블에 한꺼번에 입력하는 쿼리를 쓰세요. ( ITAS 방법을 사용하세요)

4. Professor 테이블에서 'Sharon Stone' 교수의 BONUS 를 100 만원으로 인상하세요.

저작자표시 변경금지