[Oracle] 데이터 조작어 (DML : Data Manipulation Language)

 

 

공부했던 자료 정리하는 용도입니다.

재배포, 수정하지 마세요.

 

예제는 Oracle Database에서 기본으로 제공되는 SCOTT계정 데이터로 진행됩니다.

 

 

 

 

데이터 조작어(DML : Data Manipulation Language)

데이터 조작어로 부르는 DML(Data Manipulation Language)명령어는  SELECT 문으로 조회한 테이블에 데이터를 추가 · 변경 · 삭제할 때 사용하는 명령어로 이루어져 있다. 

 

 

    DML 명령어  

1.  INSERT  : 테이블 생성
2.  UPDATE  : 테이블 데이터 수정
3.  DELETE  : 테이블 데이터 삭제

 

1. INSERT문

INSERT INTO 테이블이름 [(열1, 열2, ... , 열N)]
VALUES (열1에 들어갈 데이터, 열2에 들어갈 데이터, ... , 열N에 들어갈 데이터);

•  INSERT INTO  : 새로운 데이터를 입력할 대상 테이블과 열을 입력(열은 선택요소)
•  VALUES  :  INSERT INTO 에서 지정한 테이블의 열 순서와 자료형에 맞는 입력 데이터를 지정

 

  특정 테이블에 데이터를 새로 추가할 때  INSERT 문을 사용한다.  INSERT INTO 절 뒤에 데이터를 추가할 테이블 이름을 명시하고, 해당 테이블의 열을 소괄호로 묶어 지정한 후  VALUES 절에는 지정한 열에 입력할 데이터를 작성한다.  INSERT 문에서 지정한 열 개수와 입력할 데이터 개수가 일치하지 않거나 자료형이 맞지 않는 경우, 또는 열 길이를 초과하는 데이터를 지정하는 경우에는 오류가 발생하므로 주의해야 한다.

   INSERT 문은 서브쿼리를 이용한 방식 외에도  ALL  또는  FIRST 등의 옵션으로 한 번에 여러 테이블을 대상으로 데이터를 추가하거나 특정 조건에 따라 다른 테이블의 데이터를 추가하는 등 다양하게 사용할 수 있다. (자세한 내용은 오라클 공식문서를 참조)

 

 

  ■ 열 지정 여부

-- ex1) 열을 지정 (주로 쓰이는 방식)
INSERT INTO DEPT_TEMP(DEPTNO, DNAME, LOC)
	      VALUES (50, 'DATABASE', 'SEOUL');
          
-- ex2) 열 지정 생략
INSERT INTO DEPT_TEMP
VALUES (60, 'NETWORK', 'BUSAN');

   INSERT 문에 지정하는 열은 선택 요소로 생략 가능하다. 열 지정을 생략하면 해당 테이블을 만들 때 설정한 열 순서대로 모두 나열되어 있다고 가정하고 데이터를 작성해야 하고, 열 개수나 자료형 및 길이도 반드시 맞춰 주어야 한다. ( INSERT 문을 작성할 때 조금 길더라도 명시적으로 모든 열을 작성해 놓는 방식이 선호된다. 다른 개발자들이 따로 자료를 찾지 않고  INSERT 문만 보아도 테이블에 포함된 열의 내용을 한눈에 알아볼 수 있기 때문이다.)

 

 

  ■ 테이블에 NULL 데이터 입력

-- ex1) NULL의 명시적 입력 (주로 쓰이는 방식)
INSERT INTO DEPT_TEMP(DEPTNO, DNAME, LOC)
	      VALUES (70, 'WEB', NULL);

-- ex2) ''로 NULL 입력
INSERT INTO DEPT_TEMP(DEPTNO, DNAME, LOC)
	      VALUES (80, 'MOBILE', '');
    
-- ex3) NULL의 암시적 입력
INSERT INTO DEPT_TEMP (DEPTNO, LOC)	-- 특정 열을 INSERT문에서 제외시키면 자동으로 NULL이 입력된다.
		VALUES(90, 'INCHEON');

   INSERT 문으로 새로운 데이터를 추가할 때 특정 열에 들어갈 데이터가 확정되지 않았거나, 굳이 넣을 필요가 없는 데이터인 경우에는  NULL 을 사용한다.  NULL 을 명시적으로 입력해주는 방법과 대상 열을 생략하여 암시적으로  NULL 이 입력되도록 유도하는 방식이 있다. 보통  NULL 을 직접 명시해야 하는 경우에는  '' 를 이용한 방식보다  NULL 이라는 단어를 정확히 입력하는 방식이 선호된다. 데이터베이스에 익숙하지 않은 개발자가 보았을 때 공백 문자열이  NULL 로 들어간 건지 아닌지 헷갈릴 수 있기 때문이다.

 

 

  ■ 테이블에 날짜 데이터 입력

-- ex1) YYYY/MM/DD 형식의 문자열 데이터로 입력(YYYY-MM-DD도 가능)
INSERT INTO EMP_TEMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
               VALUES(9999, '홍길동', 'PRESIDENT', NULL, '2001/01/01', 5000, 1000, 10);
               
-- ex2) DD/MM/YYYY 순서로 데이터를 입력하면 오류 !
INSERT INTO EMP_TEMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
               VALUES(2111, '이순신', 'MANAGER', 9999, '07//01/2001', 4000, NULL, 20);
               
-- ex3) TO_DATE함수를 이용한 입력(주로 쓰이는 방식)
INSERT INTO EMP_TEMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
	      VALUES (2111, '이순신', 'MANAGER', 9999, TO_DATE('07/01/2001', 'DD/MM/YY'), 4000, NULL, 20);
    
-- ex4) SYSDATE를 이용한 입력(자주 쓰임)
INSERT INTO EMP_TEMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
	       VALUES(3111, '심청이', 'MANAGER', 9999, SYSDATE, 4000, NULL, 30);

  날짜 데이터는  YYYY/MM/DD , 또는  YYYY-MM-DD 형식으로 입력 가능하지만 순서를 바꿔서 입력하면 오류가 발생한다. 이는 오라클이 설치되어 있는 운영체제의 종류나 사용하는 기본 언어군에 따라 날짜 표기방식이 다르기 때문이다. 따라서 날짜 데이터를  INSERT 문으로 입력할 때는 문자열로 날짜를 입력하지 않고  TO_DATE 나  SYSDATE 같은 함수들을 이용해서 넣어 주는 것이 좋다.

 

위의 SQL문은 사용 환경 또는 설정에 따라  HIREDATE 열에 오전, 오후 시간이 함께 출력될 수 있다.

 

 

  ■ 서브쿼리를 이용한 데이터 추가

INSERT INTO EMP_TEMP(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
    SELECT E.EMPNO, E.ENAME, E.JOB, E.MGR, E.HIREDATE, E.SAL, E.COMM, E.DEPTNO
      FROM EMP E, SALGRADE S
     WHERE SAL BETWEEN S.LOSAL AND S.HISAL
       AND S.GRADE = 1;

  앞에 나왔던  INSERT 문들은 모두 실행했을 때 한 행의 데이터만 추가되었지만  INSERT 문에 서브쿼리를 사용하면  SELECT 문으로 한 번에 여러 행의 데이터를 추가할 수 있다. 예를 들어 위의 SQL은  EMP (사원) 테이블에서  SALGRADE (급여등급)테이블을 참조하여  GRADE (등급)가  1 인 사원만을  EMP_TEMP 테이블에 넣는 문장이다.

 

실행결과 1

 

실행결과 2

한 번에  3 개의 행이 추가된 것을 확인할 수 있다.

 

서브쿼리를 사용할 때 주의해야 할 점   VALUES 절은 사용하지 않는다. 데이터가 추가되는 테이블의 열 개수와 서브쿼리의 열 개수가 일치해야한다. 데이터가 추가되는 테이블의 자료형과 서브쿼리의 자료형이 일치해야한다.   →   INSERT 의 대상이 되는 테이블의 열 개수와 자료형만 맞출 수 있으면  INSERT 문에서 사용하는 서브쿼리는 여러 개의 테이블을 조인한 결과일 지라도 열 이름에 상관없이 데이터 추가가 가능하다.

 

 

 

2. UPDATE문

UPDATE [변경할 테이블]
SET [변경할 열1]=[데이터], [변경할 열2]=[데이터], ... , [변경할 열N]=[데이터]
[WHERE 데이터를 변경할 대상 행을 선별하기 위한 조건];

•  UPDATE  : 데이터를 수정할 테이블을 지정
•  SET  : 변경할 열을 선택하고 변경할 데이터를 입력
•  WHERE (선택) : 테이블의 변경할 데이터 선별 조건식을 지정합니다.
                          생략할 경우 테이블 내 지정된 모든 열의 데이터가 변경됨
  

 

-- ex1) 데이터 전체 수정
UPDATE DEPT_TEMP2
   SET LOC = 'SEOUL'; -- 조건식을 생략할 경우 테이블 내의 지정된 모든 열의 데이터가 변경됨
    
-- ex2) 데이터 일부분만 수정
UPDATE DEPT_TEMP2
   SET DNAME = 'DATABASE',
       LOC = 'SEOUL'
WHERE DEPTNO = 40;	-- 수정 대상 행을 선별하기 위해 WHERE절과 조건식을 사용

  특정 테이블에 저장되어 있는 데이터 내용을 수정할 때  UPDATE 문을 사용한다.  UPDATE 키워드 이후에 변경할 테이블 이름을 지정하고  SET 절에  변경할 열 이름 = 변경할 데이터 를 지정한다.(여러 열일 경우  , 로 구분). 데이터를 변경해야 할 행이 정해져 있다면  WHERE 절 및 조건식을 추가하여 변경 대상 행을 지정할 수 있다. (조건식을 생략할 경우 테이블 내 지정된 모든 열의 데이터가 변경된다.)

 

 

  ■ 서브쿼리를 이용한 데이터 수정

-- ex1) 데이터 일부분 수정(열이 여러 개인 경우)
UPDATE DEPT_TEMP2
   SET (DNAME, LOC) = (SELECT DNAME, LOC	-- 변경할 열의 개수와 서브쿼리에서 지정한 열개수를 맞춰야 한다.
                         FROM DEPT
                        WHERE DEPTNO = 40)
WHERE DEPTNO = 40;

-- ex2) 데이터 일부분 수정(열 하나하나를 수정하는 경우)
UPDATE DEPT_TEMP2
SET DNAME = (SELECT DNAME
               FROM DEPT
              WHERE DEPTNO = 40),
    LOC = (SELECT LOC
             FROM DEPT
            WHERE DEPTNO = 40)
WHERE DEPTNO = 40;

-- ex3) WHERE절에 서브쿼리를 사용하여 데이터를 수정하는 경우
UPDATE DEPT_TEMP2
SET LOC = 'SEOUL'
WHERE DEPTNO = (SELECT DEPTNO
                  FROM DEPT_TEMP2
                 WHERE DNAME = 'OPERATIONS');

   INSERT 문과 같이  UPDATE 문에서도 서브쿼리를 활용할 수 있다. 주의할 점은 변경할 열의 개수에 따라 서브쿼리에서 지정한 열 개수도 함께 맞춰주어야 한다는 것이다.(자료형도 마찬가지로 서로 일치해야 한다.) 또한  UPDATE 문의  WHERE 절에도 서브쿼리를 사용할 수 있다.  WHERE 절에 조건식에 사용하는 서브쿼리는  SELECT 문의  WHERE 절에 서브쿼리를 사용하는 방식과 같다.

 

 

  ■ ROLLBACK (되돌리기)

ROLLBACK;

   UPDATE 문 실행을 취소하기 위해  ROLLBACK 명령문을 사용할 수 있다.  ROLLBACK 명령어는 TCL(Transaction Control Language)명령어 중 하나로, 정해진 시점 이후에 실행된 DML 명령어( INSERT ,  UPDATE ,  DELETE )의 실행을 취소하는 명령어이다.

 

 

UPDATE 문 사용할 때 유의점     UPDATE 문과  DELETE 문은 이미 존재하는 데이터를 수정 · 삭제하므로  SELECT 나  INSERT 문에 비해 위험성이 큰 명령어이다. 데이터가 잘못 수정되는 일을 막기 위해서는  UPDATE 문을 실행하기 전에  UPDATE 문의  WHERE 절을 검증하는 작업이 반드시 필요하다. 해당  WHERE 절을  UPDATE 문에 넣어 실행하기 전에  SELECT 문에서 먼저 사용해 보는 것만으로도 간단하게 확인할 수 있다.

 

 

 

4. DELETE문

DELETE [FROM] [테이블 이름]
[WHERE 삭제할 대상 행을 선별하기 위한 조건식];

-- ex) 데이터의 일부분 삭제
DELETE FROM EMP_TEMP2
WHERE JOB = 'MANAGER'; -- 일부분만 삭제시 꼭 조건식을 지정해야함 !

•  DELETE ( FROM 은 선택) : 데이터를 삭제할 테이블을 지정   
•  WHERE (선택) : 테이블의 삭제할 데이터를 선별하는 조건식을 지정.
                          생략할 경우 테이블의 모든 데이터를 삭제

 

   DELETE 문은 테이블에 있는 데이터를 삭제할 때 사용한다.  DELETE FROM  또는  DELETE 키워드 뒤에 데이터를 삭제할 대상 테이블 이름을 지정하는데,  DELETE 문 역시  UPDATE 문과 마찬가지로 삭제 대상이 될 데이터를 선정하기 위해  WHERE 절 및 조건식을 지정할 수 있다. 조건식을 지정하지 않으면 테이블의 전체 데이터가 모두 삭제된다.

 

 

  ■ 서브쿼리를 이용한 데이터 삭제

DELETE FROM EMP_TEMP2
WHERE EMPNO IN (SELECT E.EMPNO	-- 서브쿼리의 결과 값이 여러개이므로 IN 연산자를 사용해야 한다.
                  FROM EMP_TEMP2 E, SALGRADE S
                 WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL
                   AND S.GRADE = 3
                   AND DEPTNO = 30);

   DELETE 문도  WHERE 절에 서브쿼리를 사용하는 것이 가능하다. 위의 SQL문은  GRADE (급여등급)이  3 등급(급여가  1401  ~  2000 사이)이고,  DEPTNO (부서 번호)가  30 번인 사원들만 삭제하는 예시이다.  SALGRADE 테이블을 조인한 서브쿼리의 결과 값을 활용하여  DELETE 문의  WHERE 절 조건식에 적용할 수 있다. 

 

 

  ■ 데이터 전체 삭제

DELETE FROM EMP_TEMP2;

   DELETE 문에  WHERE 절이 없으면 해당 테이블에 삭제 대상 데이터를 특정 짓지 않기 때문에 테이블의 모든 데이터가 삭제된다. (특별한 경우를 제외하고 잘 사용되지 않는다.)  DELETE 문은 기존에 존재하는 데이터를 삭제하는 명령어이므로 앞에서 말했던  UPDATE 문처럼 사용할 때 특별히 주의해야 한다.  DELETE 문이나  WHERE 절을 사용하면  WHERE 절의 조건식이 정확히 삭제할 대상을 선택하고 있는지  SELECT 문을 사용해서 반드시 검증을 거친 후에 실행하는 것이 좋다.