[SQL] 6. DML

DML

테이블안의 데이터들을 관리하는 명령어

INSERT

테이블에 새로운 데이터를 입력할 때 사용하는 명령어 숫자 값 이외에는 홑따옴표 써야함.

(1) 1행씩 입력하기

INSERT INTO table (col1, co2, ...,)
VALUES (value1, value2, ...,) ;

dept2 테이블에 새로운 부서정보 입력

SCOTT>INSERT INTO dept2 (dcode, dname, pdept, area)
  2  VALUES (9000, 'temp_1', 1006, 'Temp Area');

모든 컬럼에 데이터를 입력할 경우에는 테이블명 뒤에 컬럼 이름 생략가능.

NULL 값 입력

데이터 입력시 컬럼에 값을 입력하지 않으면 자동으로 null 값이 들어감. (값에 null 입력해도됨)

음수 값 입력 테스트 (양수와 동일함)

SCOTT>CREATE TABLE t_minus
  2  ( no1 NUMBER,
  3    no2 NUMBER(3),       // 정수 3자리.
  4    no3 NUMBER(3,2));  // 소수점이하 2자리까지.

Table created.

SCOTT>INSERT INTO t_minus VALUES(1, 1, 1);

1 row created.

SCOTT>INSERT INTO t_minus VALUES(1.1, 1.1, 1.1);

1 row created.

SCOTT>INSERT INTO t_minus VALUES(-1.1, -1.1, -1.1);

1 row created.

SCOTT>SELECT * FROM t_minus;

       NO1        NO2        NO3
---------- ---------- ----------
         1          1          1
       1.1          1        1.1
      -1.1         -1       -1.1

(2) 서브쿼리를 사용해 여러행 입력하기

SCOTT>CREATE TABLE professor3
  2  AS
  3    SELECT * FROM professor
  4    WHERE 1 = 2;            //  데이터는 입력안되도록

Table created.

SCOTT>SELECT * FROM professor3;

no rows selected

SCOTT>INSERT INTO professor3
  2  SELECT * FROM professor;   // professor3과 컬럼의 개수, 데이터 형이 동일해야함.

16 rows created.

위처럼 서브쿼리를 사용해 여러 데이터를 가져와 입력하는 방법을 현업에서는 ITAS 라고 부름. 조건도 사용가능

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

예제

다른 테이블의 데이터 가져와서 입력

Professor 테이블에서 교수번호가 1000 번 에서 1999번까지 인 교수의 번호와 교수이름은 prof_3 테이블에 입력하고 교수번호가 2000 번에서 2999 번까지 인 교수의 번호와 이름은 prof_4 테이블에 입력.

SCOTT>CREATE TABLE prof_3
  2  ( profno  NUMBER,
  3    name    VARCHAR2(25));

Table created.

SCOTT>CREATE TABLE prof_4
  2  ( profno  NUMBER,
  3    name    VARCHAR2(25));

Table created.

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

다른 테이블에 동시에 같은 데이터 입력

prof_3 과 prof_4 테이블의 데이터를 TRUNCATE 로 삭제한 후 Professor 테이블에서 교수번호가 3000번 에서 3999 번인 교수들의 교수 번호와 이름을 prof_3테이블과 prof_4 테이블에 동시에 입력

SCOTT>TRUNCATE TABLE prof_3;

Table truncated.

SCOTT>TRUNCATE TABLE prof_4;

Table truncated.

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

UPDATE

UPDATE table
SET column = value
WHERE 조건;

예제

1) Professor 테이블에서 직급이 조교수(assistant professor) 인 교수들의 BONUS 를 200 만원으로 인상하시오.

SCOTT>UPDATE professor
  2  SET bonus = 200
  3  WHERE position = 'assistant professor';

2) Professor 테이블에서 'Sharon Stone' 교수의 직급과 동일한 직급을 가진 교수들 중 현재 급여가 250 만원이 안 되는 교수들의 급여를 15% 인상하시오.

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

DELETE

DELETE FROM table
WHERE 조건;

예제

Dept2 테이블에서 부서번호(DCODE)가 9000 번에서 9999 번 사이인 매장들을 삭제하시오.

SCOTT>DELETE FROM dept2
  2  WHERE dcode >= 9000 AND dcode <= 9999;

MERGE

여러 테이블의 데이터를 병합.

MERGE INTO table1
  USING table2
  ON (병합 조건절)
  WHEN MATCHED THEN
         UPDATE SET 업데이트내용
         DELETE  WHERE 조건
  WHEN NOT MATCHED THEN
         INSERT VALUES(컬럼명);

table1 테이블과 table2 데이터를 합쳐 table1(집계테이블)에 병합. 병합조건이 만족하면 UPDATE 또는 DELETE 수행, 만족 안하면 INSERT수행.
집계 테이블에 데이터가 많아질수록 merge 작업수행 속도는 늦어짐.(조건절에 index가 잘 만들어져 있어야 빨리 수행됨)

사용예

일별 사용 요금 테이블인 charge_01과 charge_02 테이블이 있고 집계 테이블인 ch_total 테이블이 있다. 매일 새벽에 일별 사용 요금 테이블과 요금 집계테이블을 MERGE 한다고 가정.

실습을 위해 두 테이블을 생성하고 데이터 입력.

SCOTT>CREATE TABLE charge_01
  2  ( u_date  VARCHAR2(6),
  3    cust_no NUMBER,
  4    u_time  NUMBER,
  5    charge  NUMBER ) ;

Table created.

SCOTT>CREATE TABLE charge_02
  2  ( u_date  VARCHAR2(6),
  3    cust_no NUMBER,
  4    u_time  NUMBER,
  5    charge  NUMBER ) ;

Table created.


SCOTT>INSERT INTO charge_01 VALUES('141001', 1000, 2, 1000);

1 row created.

SCOTT>INSERT INTO charge_01 VALUES('141001', 1001, 2, 1000);

1 row created.

SCOTT>INSERT INTO charge_01 VALUES('141001', 1002, 1, 500);

1 row created.

SCOTT>INSERT INTO charge_02 VALUES('141002', 1000, 3, 1500);

1 row created.

SCOTT>INSERT INTO charge_02 VALUES('141002', 1001, 4, 2000);

1 row created.

SCOTT>INSERT INTO charge_02 VALUES('141002', 1003, 1, 500);

1 row created.

SCOTT>COMMIT;

집계용 테이블 생성

SCOTT>CREATE TABLE ch_total
  2  ( u_date  VARCHAR2(6),
  3    cust_no NUMBER,
  4    u_time  NUMBER,
  5    charge  NUMBER ) ;

Table created.

MERGE 1 (charge_01 과 ch_total 병합)

SCOTT>MERGE INTO ch_total total
  2  USING charge_01 ch01
  3  ON (total.u_date = ch01.u_date)
  4  WHEN MATCHED THEN
  5     UPDATE SET total.cust_no = ch01.cust_no
  6  WHEN NOT MATCHED THEN
  7     INSERT VALUES (ch01.u_date, ch01.cust_no, ch01.u_time, ch01.charge);

3 rows merged.

MERGE 2 (charge_02 과 ch_total 병합)

SCOTT>MERGE INTO ch_total total
  2  USING charge_02 ch02
  3  ON (total.u_date = ch02.u_date)
  4  WHEN MATCHED THEN
  5     UPDATE SET total.cust_no = ch02.cust_no
  6  WHEN NOT MATCHED THEN
  7     INSERT VALUES (ch02.u_date, ch02.cust_no, ch02.u_time, ch02.charge);

3 rows merged.

결과 조회

U_DATE    CUST_NO     U_TIME     CHARGE
------ ---------- ---------- ----------
141001       1002          1        500
141001       1001          2       1000
141001       1000          2       1000
141002       1003          1        500
141002       1001          4       2000
141002       1000          3       1500

주의할점:
ON 절에 해당하는 조건이 중복되면 에러발생. 그래서 일반적으로 집계테이블 조건 컬럼에는 PK나 UNIQUE INDEX를 많이 설정함.

TRANSACTION

논리적인 작업단위.쉽게말하면 여러 DML 작업들을 하나의 단위로 묶어둔것.

트랜잭션의 시작은 DML, 완료는 TCL, DCL, DDL

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

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

[출처] 다양한 예제로 쉽게 배우는 오라클 SQL과 PL/SQL 서진수 저