오라클 DML, INSERT, SELECT, UPDATE, DELETE

DML

CRUD (필수 꼭외울 것)

INSERT - C (create)

SELECT - R (read)

UPDATE - U

DELETE - D

 

INSERT

데이터를 삽입할 때 사용

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

 

제대로 들어갔는지 확인 해본다

SELECT *
FROM dept2;

 

날짜의 경우 INSERT 할때 

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

위 코드를 이용하여 형식을 바꾼뒤 '2014-10-23' 으로 넣거나

요소 입력시 TO_DATE를 이용하여 입력하여야한다.

자동으로 입력하려면 SYSDATE를 요소에 입력한다.

TO_DATE('2014-10-23', 'YYYY-MM-DD')

 

 

음수값 입력하기

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);

SELECT * FROM t_minus;

NUMBER(3) - 정수자리를 3자리 (999) 로 지정

NUMBER(3, 2) - 정수자리를 3자리 (999) , 소수자리를 2자리 (0.99) 로 지정 

 

INSERT와 서브 쿼리를 사용하여 여러 행 입력하기

먼저 PROFESSOR 테이블을 복사하여 PROFESSOR3를 만든다

--INSERT와 서브쿼리를 사용하여 여러행 입력하기
CREATE TABLE professor3
AS
SELECT * FROM professor
WHERE 1 = 2; -- 아무데이터도 넣지 않기위해 무조건 틀린 조건이 나오도록 함

 

확인을 위해 select 해본다

SELECT *
FROM professor3;

의도 대로 데이터가 없는 테이블이 생성 되었다

 

 

서브쿼리로 여러건의 데이터를 한꺼번에 입력해본다

INSERT INTO professor3
SELECT * FROM professor;

 

professor3을 다시 확인해보면

SELECT *
FROM professor3;

자료들이 정상적으로 입력되어 있다

 

조건을 걸어서 입력하려면

INSERT INTO professor3
SELECT * FROM professor
WHERE PROFNO > 4000;

위와 같이 아래에 조건을 넣으면 된다 ( PROFNO가 4000번을 초과하는 행만 넣기)

 

 

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 -- 모든 profno, name를 가져오기
FROM professor;

 

또는 아래와 같이 WHERE를 써도 된다

INSERT ALL
INTO prof_3 VALUES (profno, name)
INTO prof_3 VALUES (profno, name)
SELECT profno, name
FROM professor
WHERE profno BETWEEN 3000 AND 3999;

 

 

UPDATE

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

문법

UPDATE table
SET column = value
WHERE 조건;

 

 

직급이 조교수인 교수들의 BONUS를 200만원으로 인상

UPDATE professor
SET bonus = 200
WHERE position = '조교수';

 

확인해본다

SELECT *
FROM professor;

 

 

차범철 교수의 직급과 동일한 직급을 가진 교수들 중 현재 급여가 250만원이 안되는 교수들의 급여를 15%인상

UPDATE professor
SET pay = pay * 1.15
WHERE position =
(
SELECT position
FROM professor
WHERE name = '차범철'
)
and pay < 250;

 

UPDATE 조인

일반적인 UPDATE가 아닌 다른 테이블과 조인을 하는 UPDATE에 대해서 살펴본다

 

일반적인 UPDATE

UPDATE emp
SET sal = (sal + sal*0.1)
WHERE JOB = 'CLERK';

 

 

일반적인 UPDATE 조인

UPDATE emp E
SET sal = (sal + sal*0.1)
WHERE EXISTS
(
    SELECT 1 FROM DEPT D
    WHERE D.LOC = 'DALLAS' AND E.DEPTNO = D.DEPTNO
);

#여기서 서브쿼리안에  E.DEPTNO를 쓸 수 있는 것은 
코드를 실행할 때 코드안의 모든 테이블을 가장 먼저 불러오기 때문이다
그렇기 때문에 서브쿼리 밖에 있는 emp E를 서브쿼리 안에서 쓸 수 있는 것

 

 

테스트를 위한 테이블 생성

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

 

 

부서 번호가 20인 사원 발령 부서 번호 99로 데이터를 INSERT

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

COMMIT;

 

 

EMP 테이블에 존재하지 않는 사원번호 2건을 INSERT

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

COMMIT;

 

 

잘못된 부서번호 99를 20으로 UPDATE

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
);

COMMIT;

 

SELECT *
FROM DEPT_HIST;

 

 

DELETE

데이터를 삭제할 때 사용

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

 

DELETE는 데이터를 삭제하는 것이지만, 눈에 보이지 않게만 할뿐 데이터는 저장소에 저장되어 있다.
특별한 툴(BBED 등)을 이용하여 복구 할 수 있다