10. DML

DML은 데이터를 넣고, 빼고, 변경하는 명령어를 알아보도록 한다.

 

1. INSERT

데이터를 테이블 내부에 넣는 명령어이다.

문법 :

INSERT INTO 테이블명(컬럼명)
VALUES(넣고싶은 데이터)

예시:

INSERT INTO PROFESSOR(PROFNO, NAME, ID, POSITION, PAY, HIREDATE)
VALUES(5001, '제임스', 'LOVE_ME','정교수',500,'14/10/23');

 

이 때, 날짜를 입력할 때 주의할 점이 있다.

오라클 18C는 날짜의 데이터 타입 형태 YY/MM/DD 든 YY-MM-DD든 어떤 형태로 넣어도 자동으로 넣어주는데, 낮은 버전의 오라클을 쓸 경우에는 날짜의 데이터 타입을 확인하고 넣도록 한다.

 

INSERT INTO DEPT2
VALUES(9000,'TEPT_1',1006,'TEPT AREA');

INSERT INTO DEPT2(DCODE, DNAME, PDEPT)
VALUES(9002,'TEPT_3',1006);

모든 값을 다 채워넣을 경우, 컬럼명은 생략해도 되지만 이 때 순서를 주의하도록 해야한다.

중간에 값을 채워넣지 않을 경우 반드시 컬럼명을 입력하고, 입력하지 않은 데이터는 NULL 값으로 출력된다.

 

또한 NUMBER의 경우,

CREATE TABLE T_MINUS
(NO1 NUMBER,
NO2 NUMBER(3),
NO3 NUMBER(3,2)
);
INSERT INTO T_MINUS
VALUES(1,1,1);
INSERT INTO T_MINUS
VALUES(1.1,1.1,1.1);
INSERT INTO T_MINUS
VALUES(-1.1,-1.1,-1.1);

NUMBER에 조건을 걸지 않으면 어떠한 숫자도 넣을 수 있지만 조건을 걸게 된 경우를 보자.

2번의 경우 정수를 세자리 수까지 반환하므로 소수점이 반환되지 않았고, 3번의 경우 소수점을 반환하기 때문에 소수점이 반환되었다.

 

다른 데이터에서 데이터를 가져오는 경우 INSERT ALL을 통해 한번에 여러 행을 입력할수도 있다. 

CREATE TABLE PROF_3(
PROFNO NUMBER,
NAME VARCHAR2(25)
);

CREATE TABLE PROF_4(
PROFNO NUMBER,
NAME VARCHAR2(25)
);

다음과 같이 표를 만들고,

INSERT ALL
WHEN PROFNO BETWEEN 1000 AND 1999 THEN
INTO PROF_3 VALUES(PROFNO, NAME)
WHEN PROFNO BETWEEN 2000 AND 2999 THEN
INTO PROF_4 VALUES(PROFNO, NAME)
SELECT PROFNO, NAME
FROM PROFESSOR;

조건을 사용해서 얼마든지 가져올 수 있다.

 

 

2. UPDATE

기존의 데이터를 다른 데이터로 변경할 때 사용한다.

문법:

UPDATE 테이블명
SET 컬럼명 = 값
WHERE 조건;

예시:

UPDATE PROFESSOR
SET BONUS =200
WHERE POSITION = '조교수';

또한 서브쿼리와 수식을 넣는 것도 가능하다.

UPDATE PROFESSOR
SET PAY=PAY*1.15
WHERE POSITION=(
SELECT POSITION 
FROM PROFESSOR
WHERE NAME='양선희')
AND PAY <250;

서브쿼리와 수식이 둘 다 적용된 것이 보인다.

 

 

3. DELETE

DELETE는 데이터를 삭제하는 구문이다.

문법:

DELETE FROM 테이블명
WHRER 조건;

예시:

DELETE FROM DEPT2
WHERE DCODE >=9000 AND DCODE <=9999;

 

 

4. UPDATE 조인

UPDATE 구문이 다른 테이블과 조인을 하는 경우를 말한다

일반적으로는,

SET EMP E
SET SAL = (SAL + SAL*0.15)
WHERE EXISTS
	(
    	SELECT 1 FROM DEPT D
        WHERE E.LOC='DALLAS' AND E.DEPTNO=D.DEPTNO
    );

다음의 형태를 취하는데, SET에 다른 테이블과 조인이 생길 경우 주의해야한다.

 

일단 테이블을 만들어보자.

INSERT INTO DEPT_HIST
SELECT EMPNO,1 APPOINTSEQNO, 99 DEPTNO, SYSDATE APPOINTDD
FROM EMP
WHERE DEPTNO=20;

INSERT INTO DEPT_HIST VALUES(9322,1,99,SYSDATE);
INSERT INTO DEPT_HIST VALUES(9314,1,99,SYSDATE);

위의 값은 미리 테이블에 있던 데이터를 가지고 온 것이고, 아래 값은 새로 데이터를 가지고 올 것이다.

 

이제 기존 20번 부서 사람들을 다시 20번 부서로 되돌리고자 한다.

이 때, 6번 7번 사람들은 기존 20번 부서가 아니기 때문에 업데이트 조인을 만든다.

주의해야 할 점이 여기 있는데,

UPDATE DEPT_HIST D
SET D.DEPTNO=(
SELECT E.DEPTNO FROM EMP E
WHERE E.EMPNO=D.EMPNO);

까지만 할 경우, 다음과 같은 오류가 생긴다

다음과 같이 6번 7번도 NULL값이 뜨며 같이 업데이트가 되므로, EXISTS 명령어를 이용해 테이블 내의 부서를 구분짓는다.

UPDATE DEPT_HIST D
SET D.DEPTNO=(
SELECT E.DEPTNO FROM EMP E
WHERE E.EMPNO=D.EMPNO)
WHERE EXISTS(
SELECT 1 FROM EMP E
WHERE E.EMPNO=D.EMPNO
);

 

- UPDATE JOIN 전체 코드

CREATE TABLE DEPT_HIST
(
EMPNO NUMBER(4),
APPOINTSEQNO NUMBER(4),
DEPTNO NUMBER(2),
APPOINTDD DATE
);

INSERT INTO DEPT_HIST
SELECT EMPNO,1 APPOINTSEQNO, 99 DEPTNO, SYSDATE APPOINTDD
FROM EMP
WHERE DEPTNO=20;

SELECT * FROM DEPT_HIST;

INSERT INTO DEPT_HIST VALUES(9322,1,99,SYSDATE);
INSERT INTO DEPT_HIST VALUES(9314,1,99,SYSDATE);

SELECT E.EMPNO, E.DEPTNO TOBE_DEPTNO, D.DEPTNO ASIS_DEPTNO
FROM EMP E, DEPT_HIST D
WHERE E.EMPNO=D.EMPNO;

UPDATE DEPT_HIST D
SET D.DEPTNO=(
SELECT E.DEPTNO FROM EMP E
WHERE E.EMPNO=D.EMPNO)
WHERE EXISTS(
SELECT 1 FROM EMP E
WHERE E.EMPNO=D.EMPNO
);