0206 오라클 DB 입문 - 데이터 조작어, 트랜잭션 제어와 세션

1. 데이터 조작어

* 정의

0203 오라클 DB 입문 - 조인(JOIN), 서브쿼리, DML

- DML : 이전 정리 참고

- DDL : 움직이려면 TCL을 적용시켜야 함

0) 테이블 생성, 확인, 제거 

--
--Data Manipulation Language (DML) 조작어
--TCL (TRANSACTION CONTROL LANGUAGE) : COMMIT(최종변경) / ROLLBACK(취소)을 함께 실행
--실습데이터 원본 복사
--Data Definition Language (DDL) 정의어
--실행을 하면 자동으로 COMMIT

--생성
CREATE TABLE DEPT_TEMP
AS SELECT * FROM DEPT;

--확인
SELECT * FROM DEPT_TEMP;

--버리기
DROP TABLE DEPT_TEMP;

--생성
CREATE TABLE EMP_TEMP10
AS SELECT * FROM EMP;

--확인
SELECT * FROM EMP_TEMP10;

생성된 테이블

--데이터 추가 INSERT
INSERT INTO DEPT_TEMP (DEPTNO,DNAME,LOC)
VALUES(50,'DATABASE','SEOUL');
--
SELECT * FROM DEPT_TEMP;
--데이터 타입 확인하기
DESC DEPT_TEMP;

추가된 테이블

* VARCHAR2 : 가변문자열

 

1) INSERT 행과 열의 데이터 수가 다를 경우

--INSERT문 오류 발생의 경우
--1) 열 개수와 데이터 개수 불일치
COMMIT;
INSERT INTO DEPT_TEMP (DEPTNO,DNAME,LOC)
VALUES (60,'NETWORK');
--오류 수정
INSERT INTO DEPT_TEMP (DEPTNO,DNAME)
VALUES (60,'NETWORK');
SELECT * FROM DEPT_TEMP;
ROLLBACK;

INSERT INTO DEPT_TEMP (DEPTNO,DNAME,LOC)
VALUES (60,'NETWORK','BUSAN','WRONG');

--VALUE의 값이 더 많을 경우 에러 발생
INSERT INTO DEPT_TEMP (DEPTNO,DNAME,LOC)
VALUES (60,'NETWORK','BUSAN','WRONG');

--정상적인 값 입력
INSERT INTO DEPT_TEMP (DEPTNO,DNAME,LOC)
VALUES (60,'NETWORK','BUSAN');
SELECT * FROM DEPT_TEMP;
ROLLBACK;

데이터 타입 입력값의 행과 열이 맞지 않을 경우

정상 실행된 결과 값

1) INSERT 행과 열의 데이터 타입이 다를 경우

--2) 데이터 타입 불일치
INSERT INTO DEPT_TEMP(DEPTNO,DNAME,LOC)
VALUES ('WRONG','NETWORK','BUSAN');
--오류 수정
INSERT INTO DEPT_TEMP(DEPTNO,DNAME,LOC)
VALUES (60,'NETWORK','BUSAN');
SELECT * FROM DEPT_TEMP;
ROLLBACK;

정상적인 실행 값

3) 데이터 입력 범위 벗어남

--3) 데이터 입력 범위 벗어남
INSERT INTO DEPT_TEMP(DEPTNO,DNAME,LOC)
VALUES (600,'NETWORK','BUSAN');
--오류 수정
INSERT INTO DEPT_TEMP(DEPTNO,DNAME,LOC)
VALUES (60,'NETWORK','BUSAN');
SELECT * FROM DEPT_TEMP;
ROLLBACK;

* 테이블 전체열에 추가할 때는 열 지정 없이 입력 가능

--테이블 전체열에 추가할 때는 열 지정 없이 입력 가능
ROLLBACK;
INSERT INTO DEPT_TEMP
VALUES (60, 'NETWROK','BUSAN');
SELECT * FROM DEPT_TEMP;

열만 추가된 테이블 결과 값

4) NULL 데이터 추가하기

--NULL 데이터 추가하기
INSERT INTO DEPT_TEMP (DEPTNO,DNAME,LOC)
VALUES (70, 'WEB',NULL);
SELECT * FROM DEPT_TEMP;
--BLANK 데이터 추가하기
INSERT INTO DEPT_TEMP (DEPTNO,DNAME,LOC)
VALUES (80, 'MOBILE', '');
SELECT * FROM DEPT_TEMP;
ROLLBACK;
--실무에서 특히 개발자와 협업할 때는 BLANK보다 NULL을 이용하는 것이 비어있는 값이라는 의미가 더 명확함
--NULL 값을 입력할 열을 제외하고 데이터 추가하기 (암시적 입력)
INSERT INTO DEPT_TEMP (DEPTNO,LOC)
VALUES(90, 'INCHEON');
SELECT * FROM DEPT_TEMP;

 

 

INSERT INTO DEPT_TEMP (DEPTNO,LOC) VALUES(90, 'INCHEON');

5) 테이블 생성하기 - 날짜 데이터 입력

--날짜 데이터 입력하기
--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);
SELECT * FROM EMP_TEMP;

--YYYY-MM-DD
INSERT INTO EMP_TEMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
VALUES(9999,'성춘향','MANAGER',9999 ,'2001-01-05',4000,NULL, 20);
SELECT * FROM EMP_TEMP;

 

홍길동이 대표이기 떄문에 MGR값은 없음

6) TO_DATE 함수 사용

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

--오류 수정 : 대신 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/YYYY'),4000,NULL,20);
SELECT * FROM EMP_TEMP;

7) 서브쿼리를 이용하여 한번에 입력하기

--서브쿼리를 이용하여 한번에 입력하기
--주의할 점 : VALUES 생략, 열의 순서와 데이터타입 일치해야 함
-- 700 이랑 1200 사이에 있는 급여를 받는 사원을 추가해라
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 E.SAL BETWEEN S.LOSAL AND S.HISAL
AND S.GRADE = 1;
SELECT * FROM EMP_TEMP;

8) SYSDATE 이용

----현재 날짜 입력 SYSDATE
INSERT INTO EMP_TEMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
VALUES(3111,'심청이','MANAGER',9999,SYSDATE, 4000, NULL,30);

9) UPDATE, SET (부서 이름을 일괄적으로 변경)

--일괄적으로 변경
UPDATE DEPT_TEMP2
SET LOC = 'SEOUL';
SELECT * FROM DEPT_TEMP2;
--취소
ROLLBACK;
SELECT * FROM DEPT_TEMP2;

10) 조건을 걸어서 UPDATE

--조건으로 일부분 변경
--먼저 조건이 맞는지 확인
SELECT * FROM DEPT_TEMP2 WHERE DEPTNO = 40;
--변경
UPDATE DEPT_TEMP2
SET DNAME = 'DATABASE',
LOC = 'SEOUL'
WHERE DEPTNO = 40;
SELECT * FROM DEPT_TEMP2;

11) 서브쿼리를 이용하여 변경

--서브쿼리를 이용하여 변경
--서브쿼리

SELECT DNAME, LOC
  FROM DEPT
 WHERE DEPTNO = 40;

--먼저 조건이 맞는지 확인

SELECT *
  FROM DEPT_TEMP2
 WHERE DEPTNO = 40;

--변경

UPDATE DEPT_TEMP2
   SET (DNAME, LOC) =
           (SELECT DNAME, LOC
              FROM DEPT
             WHERE DEPTNO = 40)
 WHERE DEPTNO = 40;

SELECT * FROM DEPT_TEMP2;

40번 부서의 데이터베이스, 서울을 오퍼레이션, 보스톤으로 바꾼 결과 값

12) 서브쿼리를 조건으로 사용하여 변경

--서브쿼리를 조건으로 사용하여 변경
--서브쿼리

SELECT DEPTNO
  FROM DEPT_TEMP2
 WHERE DNAME = 'OPERATIONS';

--먼저 조건이 맞는지 확인

SELECT *
  FROM DEPT_TEMP2
 WHERE DEPTNO = (SELECT DEPTNO
                   FROM DEPT_TEMP2
                  WHERE DNAME = 'OPERATIONS');

--변경

UPDATE DEPT_TEMP2
   SET LOC = 'SEOUL'
 WHERE DEPTNO = (SELECT DEPTNO
                   FROM DEPT_TEMP2
                  WHERE DNAME = 'OPERATIONS');

SELECT * FROM DEPT_TEMP2;

수정된 40번 부서

13) DELETE를 이용하여 TABLE 삭제

--데이터 삭제하기 DELETE
--EMP 테이블 복사
CREATE TABLE EMP_TEMP2
AS SELECT * FROM EMP;
SELECT * FROM EMP_TEMP2;
--데이터 일부삭제
--확인
SELECT * FROM EMP_TEMP2 WHERE JOB = 'MANAGER';
--삭제
DELETE FROM EMP_TEMP2
WHERE JOB = 'MANAGER';
SELECT * FROM EMP_TEMP2;
ROLLBACK;

삭제 전

삭제할 사원들의 정보

삭제 후 (MANAGER가 사라진 모습)

14) 서브쿼리를 이용하여 데이터 일부 삭제

--서브쿼리를 이용하여 데이터 일부 삭제
--확인
SELECT E.EMPNO
FROM EMP_TEMP2 E, SALGRADE S
WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL AND S.GRADE = 3 AND DEPTNO = 30;
SELECT * FROM EMP_TEMP2 WHERE EMPNO IN (7499,7844);
--삭제
DELETE FROM EMP_TEMP2
WHERE EMPNO IN (SELECT E.EMPNO FROM EMP_TEMP2 E, SALGRADE S
WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL
AND S.GRADE = 3
AND DEPTNO = 30);
SELECT * FROM EMP_TEMP2;

확인1 GRADE 3, DEPTNO 3에 있는 사원 정보

확인2 (전체 열 확인)

삭제된 결과 값

15) 테이블 전체 삭제

SELECT * FROM EMP_TEMP2;
DELETE FROM EMP_TEMP2;
SELECT * FROM EMP_TEMP2;

16) 다중 INSERT 하는 방식 (287P)

--다중 INSERT 하는 방식
INSERT ALL 
INTO CHAP10HW_DEPT VALUES (50,'ORACLE','BUSAN')
INTO CHAP10HW_DEPT VALUES (60,'SQL','ILSAN')
INTO CHAP10HW_DEPT VALUES (70,'SELECT','INCHEON')
INTO CHAP10HW_DEPT VALUES (80,'DML','BUNDANG')
INTO CHAP10HW_DEPT VALUES (50,'ORACLE','BUSAN')
SELECT * FROM DUAL;
SELECT * FROM CHAP10HW_DEPT;

결과 값

2. 트랜젝션 제어와 세션

* 정의

트랜잭션

- 거래, 매매, 처리(과정)

- 더 이상 분할할 수 없는 최소 수행 단위 // UPDATE, COMMIT / ROLLBACK

DDL : 자동 커밋

 

1) DML과 TCL (ROLLBACK)

--트랜잭션
--294P
--트랜잭션 제어 언어(Transaction Control Language, TCL)
--부서 테이블 복사
CREATE TABLE DEPT_TCL
AS SELECT * FROM DEPT;
SELECT * FROM DEPT_TCL;
--취소
--DML임
INSERT INTO DEPT_TCL VALUES(50, 'DATABASE','SEOUL');
SELECT * FROM DEPT_TCL;
UPDATE DEPT_TCL SET LOC = 'BUSAN' WHERE DEPTNO = 40;
SELECT * FROM DEPT_TCL;
DELETE FROM DEPT_TCL WHERE DNAME = 'RESEARCH';
SELECT * FROM DEPT_TCL;
ROLLBACK;
SELECT * FROM DEPT_TCL;

기본값

DML의 모습 (ROLLBACK 하기 전/업데이트 된 모습)

DML의 모습 (DELETE가 적용된 모습) // 트랜젝션의 범위

DML의 모습 (ROLLBACK 후) // 트랜젝션의 범위

2) DML과 TCL (COMMIT)

--최종변경
INSERT INTO DEPT_TCL VALUES(50, 'NETWORK','SEOUL');
SELECT * FROM DEPT_TCL;
UPDATE DEPT_TCL SET LOC = 'BUSAN' WHERE DEPTNO = 20;
SELECT * FROM DEPT_TCL;
DELETE FROM DEPT_TCL WHERE DEPTNO = 40;
SELECT * FROM DEPT_TCL;
COMMIT;
SELECT * FROM DEPT_TCL;

DML의 모습 (DELETE 전) // 트랜젝션의 범위

COMMIT이 완전 적용된 모습

3) 읽기 일관성 - 토드, SQLPLUS에서 볼 경우

--읽기 일관성
--토드, SQLPLUS 각자
SELECT * FROM DEPT_TCL;
--토드
COMMIT;
--토드, SQLPLUS 각자
DELETE FROM DEPT_TCL
WHERE DEPTNO = 50;

COMMIT 전 토드에서만 적용

COMMIT을 안했기에 완전히 적용되지 않은 모습

COMMIT을 했을 때 SQLPLUS까지 적용된 모습

4) 수정 중인 데이터 접근을 막는 LOCK

* LOCK 종류

- 행 레벨 록 : 특정 행 조작

- 테이블 레벨 록 : 전체 행 조작

--토드
UPDATE DEPT_TCL SET LOC = 'SEOUL'
WHERE DEPTNO = 30;
--SQLPLUS
UPDATE DEPT_TCL SET DNAME = 'DATABASE'
WHERE DEPTNO = 30;

멈춰 있는 상태 (COMMIT 전)

TOAD에서 COMMIT을 적용했을 때

적용된 모습

적용된 모습

3. 데이터 정의어

* 정의

 

1) 새로 만드는 테이블 - 제약조건 부여

--데이터 정의어 (Data Definition Language, DDL)
--자동으로 COMMIT 됨
--자로형을 정의하여 테이블 생성
CREATE TABLE EMP_DDL(
EMPNO NUMBER(4),
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2)
);
DESC EMP_DDL;

새로 생긴 EMP_DDL 테이블

 

2) 열과 행 복사

--기존 테이블의 열과 행을 복사하여 테이블 생성
CREATE TABLE DEPT_DDL
AS SELECT * FROM DEPT;
DESC DEPT_DDL;
SELECT * FROM DEPT_DDL;

복사된 테이블

3) 테이블의 열과 일부 행을 복사하여 테이블 생성

--기존 테이블의 열만 복사하여 테이블 생성 (AS를 쓰면 가능)
CREATE TABLE EMP_DDL_30
AS SELECT * FROM EMP 
WHERE DEPTNO = 30;
SELECT * FROM EMP_DDL_30;

DEPTNO 30번 추가

4) 기존 테이블의 열만 복사하여 테이블 생성

--기존 테이블의 열만 복사하여 테이블 생성 (AS를 쓰면 가능)
CREATE TABLE EMPDPET_DDL
AS SELECT E.EMPNO, E.ENAME, E.JOB, E.MGR, E.HIREDATE,
E.SAL,E.COMM,D.DEPTNO,D.DNAME,D.LOC
FROM EMP E, DEPT D
WHERE 1 !=1;
SELECT * FROM EMPDPET_DDL;
--테이블 버리기
DROP TABLE EMPDPET_DDL;

실행값

 

 

 

 

4. 팁

1) NULL 데이터 추가하기 : 개발자랑 대화할 때는 ''와 같은 BLANK라고 이야기하는 것보다 NULL이라고 이야기하기

2) 실무

- 복사본을 통해서 데이터 테스트하기

- COMMIT 습관 들이지 않기 (최종적으로)

-  데이터 조작어 : 데이터 조작시 COMMIT 이나 ROLLBACK을 실행해야 함

- 영어는 30BYTE, 한글 15BYTE

- 항상 만드는건 DEPT, EMP 순서로