Oracle SQL(7)

YangJiWon·2021년 1월 13일
dboraclesql
0

DB

목록 보기
7/12

DML

  • DB의 테이블에 새로운 데이터를 저장하거나 삭제 또는 수정 및 병합할 때 사용하는 데이터 조작어를 의미한다.

DML 및 TCL문 종류

SQL 종류명령문
Data Manipulation Language
(DML:데이터 조작어)		INSERT(데이터 입력)
UPDATE(데이터 수정)
DELETE(데이터 삭제)
MEGRE(데이터 병합)
Transaction Control Language
(TCL:트랜잭션 처리어)		COMMIT(트랜잭션 작업 반영)
ROLLBACK(트랜잭션 작업 취소)
SAVEPOINT(트랜잭션내 책갈피 설정)

단일행 INSERT

  • 테이블에 데이터를 저장하기 위한 데이터 조작어이다.
  • 한 번에 하나의 행을 테이블에 저장하는 단일 행 INSERT 방법과 서브쿼리를 이용하여 한꺼번에 여러 행을 동시에 저장하는 다중 행 INSERT 방법이 있다.
--문법
INSERT INTO 테이블명 [(컬럼명, 컬럼명2, ...)]
VALUES(값, 값2, ...);
  • 기본키 컬럼은 중복된 값을 허용하지 않으므로 데이터 입력할 때 주의해야 한다.

NULL 포함한 INSERT

  • 데이터를 저장하는 시점에서 해당 컬럼 값을 모르거나 확정되지 않았을 경우에는 해당 값에 NULL 값을 저장해야 한다.

1) 묵시적 방법

  • 자동으로 컬럼 값에 NULL 값을 저장하는 방법으로서 INTO절에서 해당 컬럼을 생략하면 된다.
  • 생략된 컬럼에는 자동으로 NULL값이 저장되기 때문에 NOT NULL 제약 조건이 설정된 컬럼인 경우에는 사용이 불가능하다.
INSERT INTO dept(deptno, dname)
VALUES(70, '인사');
  • INTO 절에 LOC 컬럼이 생략되었기 때문에 자동으로 NULL 값이 저장된다.

2) 명시적 방법

  • VALUES 절의 컬럼값에 NULL 또는 ''(빈문자열)을 직접 지정하는 것이다.
INSERT INTO dept(deptno, dname, loc)
VALUES(80, '인사', NULL);

INSERT 사용 시 에러 발생 경우

1) INTO 절에 명시된 컬럼의 수와 VALUES 절에 명시된 컬럼 값의 개수가 일치하지 않는 경우에 에러가 발생한다.

INSERT INTO dept(deptno, dname, loc)
VALUES (11, '인사');
  • 컬럼의 수와 값의 수가 일치하지 않아 에러가 발생한다.

2) INTO 절에서 컬럼명을 생략하는 경우에는 반드시 VALUES 절에서 테이블의 모든 컬럼 값을 누락하지 않고 순서대로 지정해야 된다. 만약 누락되어 있으면 에러가 발생한다.

INSERT INTO dept
VALUES(12, '인사');
  • dept의 컬럼 수는 deptno, dname, loc로 3개인데 값의 개수는 2개라서 오류가 발생한다.

3) INTO 절에 컬럼명의 데이터 타입과 VALUES 절의 컬럼값의 데이터 타입이 일치하지 않으면 에러가 발생한다.

INSERT INTO (deptno, dname, loc)
VALUES('개발', 13, '인사');
  • deptno 컬럼은 NUMBER타입이기 떄문에 수치 데이터만 가능한데 '개발'이라는 문자열이 매칭이 되어 데이터 타입 오류가 발생한다.

4) VALUES 절의 컬럼 값 지정 시 반드시 리터럴 형식에 맞춰서 설정해야 한다. 문자와 날짜 리터럴은 ''로 묶어야하고 수치 리터럴은 ''없이 사용한다.

INSERT INTO dept(deptno, dname, loc)
VALUES('개발', 14, 인사);
  • 인사는 문자형식이기 때문에 '인사'로 표현해야 한다. ''을 생략하면 인사를 리터럴로 인식하지 않고 식별자로 인식하기 때문에 오류가 발생한다.

복수행 INSERT

-- 문법
INSERT INTO 테이블명[(컬럼명, 컬렴명2 ...)]
Subquery;
  • VALUES 절을 사용하는 대신에 서브쿼리를 이용하면 하나의 INSERT문을 사용하여 한꺼번에 여러 행을 생성할 수 있다.
  • 주의할 점은 INTO 절에서 지정한 컬럼의 개수와 데이터 타입이 서브 쿼리를 수행한 결과와 반드시 동일해야 한다.
  • 존재하는 테이블을 사용하여 새로운 테이블을 생성하는 방법은 다음과 같이 서브쿼리를 사용하며 각 단어의 첫 글자만 따서 CTAS라고 부른다.
CREATE TABLE 테이블명 [(컬럼명, 컬럼명2)]
AS
Subquery;
-- 테이블 제약조건은 가져오지 않는다.

CREATE TABLE mydept
AS
SELECT * FROM dept
WHERE 1 = 2;

INSERT INTO mydept
SELECT deptno, dname, loc
FROM dept;

다중 테이블 다중행 INSERT

  • 한 번의 INSERT시 여러 테이블에 복수 데이터를 저장하는 방법을 살펴보도록 한다.
  • INSERT ALL이라고 부른다.
-- 문법
INSERT ALL
	[WHEN 조건식 THEN]
    	INTO 테이블1 VALUES (컬럼명, 컬럼명2, .... , 컬럼명n)
    [WHEN 조건식2 THEN]
    	INTO 테이블2 VALUES (컬럼명, 컬럼명2, .... , 컬럼명n)
Subquery;

INSERT ALL
    INTO sal_history VALUES (empno, hiredate, sal)
    INTO mgr_history VALUES (empno, mgr, sal)
    SELECT empno, hiredate, sal, mgr
    FROM emp;
    
INSERT ALL
    WHEN sal < 2500 THEN
        INTO sal_history VALUES(empno, hiredate, sal)
    WHEN sal > 2500 THEN
        INTO mgr_history VALUES(empno, mgr, sal)
    SELECT empno, hiredate, sal, mgr
    FROM emp;

INSERT ALL
    WHEN sal > 3000 THEN
        INTO sal_history VALUES (empno, ename, hiredate, sal)
    WHEN mgr = 7698 THEN
        INTO mgr_history VALUES (empno, ename, mgr)
SELECT empno, ename, hiredate, sal, mgr
FROM emp;
  • 서브쿼리를 실행한 결과가 INTO절에서 지정한 테이블1과 테이블2에 자동으로 INSERT가 된다.
  • WHEN 절은 생략할 수 있으며 생략하면 무조건 INSERT ALL이라고 부르고 WHEN 절이 있으면 조건식이 TRUE인 경우에만 INSERT 되기 때문에 조건 INSERT ALL이라고 부른다.

조건 INSERT FIRST문

  • WHEN절에 지정된 조건이 중복되는 경우에 처음 조건에 일치하는 테이블에만 저장되고 이후에는 조건이 일치해도 테이블에 저장되지 않는 방법이다.
INSERT FIRST
    WHEN sal = 800 THEN
        INTO sal_history VALUES(empno, hiredate, sal)
    WHEN sal < 2500 THEN
        INTO mgr_history VALUES(empno, mgr, sal)
    ELSE
        INTO test_history VALUES(empno, sal)
SELECT empno, hiredate, sal, mgr
FROM emp;
  • 월급이 800이면 첫 번째 WHEN 절에 지정된 조건도 만족하고 두 번째 WHEN절에 지정된 조건도 만족하게 된다. 첫 번째 조건에 만족하면 이후에는 조건이 일치해도 테이블에 저장하지 않도록 하는 방법이 INSERT FIRST문이다.

UPDATE

  • 테이블에 저장된 데이터를 수정하기 위해서 사용되며 한 번에 여러 개의 행들을 변경할 수 있다.
UPDATE 테이블명
SET 컬럼명=변경할값[, 컬럼명1=변경할값]
[WHERE 조건식];

UPDATE dept
SET dname= '경리과', loc = '부산'
WHERE deptno = 90;

UPDATE dept
SET dname = '경리과'
WHERE dname LIKE '인사%';

UPDATE copy_dept
SET dname = (SELECT dname
FROM dept
WHERE deptno = 10),
loc = (SELECT loc
FROM dept
WHERE deptno = 20)
WHERE deptno = 60;

DELETE

  • 저장된 데이터를 삭제하기 위해서 사용되며 한 번에 여러 개의 행들을 삭제할 수 있다.
--문법
DELETE FROM 테이블명
[WHERE 조건식];

DELETE FROM dept
WHERE deptno =91;

DELETE FROM emp
WHERE deptno IN (
SELECT deptno
FROM dept
WHERE dname = '경리과');

MERGE

  • 구조가 같은 2개의 테이블을 비교하여 하나의 테이블로 합치기 위한 데이터 조작어이다.
  • WHEN 절의 조건절에서 대상 테이블에 해당 행이 이미 존재하면 UPDATE가 실행되고 존재하지 않으면 INSERT가 실행된다.
MERGE INTO 테이블1 별칭
USING (테이블명2|뷰|서브쿼리) 별칭
ON (조인조건)
WHEN MATCHED THEN
 UPDATE SET
  컬럼명 = 값,
  컬럼명1 = 값1
 [WHERE 조건식]
 [DELETE WHERE 조건식]
WHEN NOT MATCHED THEN
 INSERT (컬럼 목록)
 VALUES (값 목록)
 [WHERE 조건식];
 
 MERGE INTO p_total total
USING pt_01 p01
ON (total.판매번호 = p01.판매번호)
WHEN MATCHED THEN
 UPDATE SET total.제품번호 = p01.제품번호
WHEN NOT MATCHED THEN
 INSERT VALUES (p01.판매번호, p01.제품번호, p01.수량, p01.금액);
 
MERGE INTO p_total total
USING pt_02 p02
ON (total.판매번호 = p02.판매번호)
WHEN MATCHED THEN
 UPDATE SET total.제품번호 = p02.제품번호
WHEN NOT MATCHED THEN
 INSERT VALUES (p02.판매번호, p02.제품번호, p02.수량, p02.금액);

## 트랜잭션 - DB의 논리적인 작업 단위로서 분리될 수 없는 한 개 이상의 데이터베이스 조작을 의미한다. - 하나의 트랜잭션에는 하나 이상의 SQL문장이 포함될 수 있으며 트랜잭션의 대상이 되는 SQL문은 DML 문이다. ### COMMIT - DML문에 의해서 실행되었으나 실제로 저장되지 않은 모든 데이터를 DB에 저장하고 현재의 트랜잭션을 종료하는 명령어이다. - 즉, 트랜잭션 내의 모든 개별적인 작업들이 정상적으로 처리되어 DB에 모두 반영되도록 **확정**한다는 의미이다. ### SAVEPOINT - 진행중인 트랜잭션을 특정 이름으로 지정하는 명령어로서 책갈피 기능이라고 할 수 있다. - 지정된 이름에 해당하는 상태로 실행된 DML 작업을 취소시킬 수 있다. ### ROLLBACK [TO SAVEPOINT 이름] - 커밋되지 않은 모든 데이터의 변경 사항을 취소하고 현재의 트랜잭션을 종료하는 명령어이다. - 즉, 트랜잭션으로 인한 하나의 묶음처리가 시작되기 이전의 상태로 복구되는 것을 의미한다. - TO SAVEPOINT 키워드를 사용하면 SAVEPOINT로 지정한 위치까지만 변경사항을 취소시킬 수도 있다.

테이블을 생성하거나 수정, 삭제하는 명령어인 DDL(Data Definition Language)문은 자동으로 COMMIT된다.


트랜잭션의 성질

원자성(Atomicity)

  • 트랜잭션의 연산은 DB에 모두 반영되든지 아니면 전혀 반영되지 않아야 한다.
  • 트랜잭션 내의 모든 명령은 반드시 완벽히 수행되어야 하며, 모두가 완벽히 수행되지 않고 어느하나라도 오류가 발생하면 트랜잭션 전부가 취소되어야 한다.

일관성(Consistency)

  • 트랜잭션이 실행을 성공적으로 완료하면 언제나 일관성 있는 DB 상태로 변환한다.
  • 시스템이 가지고 있는 고정요소는 트랜잭션 수행 전과 트랜잭션 수행 완료 후의 상태가 같아야 한다.

독립성(Isolation)

  • 둘 이상의 트랜잭션이 동시에 병행 실행되는 경우 어느 하나의 트랜잭션 실행 중에 다른 트랜잭션의 연산이 끼어들 수 없다.
  • 수행 중인 트랜잭션은 완전히 완료될 때까지 다른 트랜잭션에서 수행 결과를 참조할 수 없다.

Durability(영속성)

  • 성공적으로 완료된 트랜잭션의 결과는 시스템이 고장나더라도 영구적으로 반영되어야 한다.

트랜잭션 종료 전의 진행중인 데이터 상태

  1. 트랜잭션 내의 모든 데이터 변경 사항은 트랜잭션이 종료되기 전까지는 모두 임시적이다. 따라서 데이터를 변경 전 상태로 복구할 수 있다.
  2. 변경된 행은 내부적으로 Lock이 걸리게 되어 해당 사용자를 제외한 나머지 사용자는 해당 행들을 변경할 수 없다. 트랜잭션을 COMMIT 또는 ROLLBACK으로 종료시켜야 Lock이 해제된다.
  3. 데이터를 변경한 현재 사용자는 SELECT문을 이용하여 변경된 데이터를 확인할 수 있으나 다른 사용자는 현재 사용자에 의해 변경된 데이터 결과를 확인할 수 없다. 이것을 읽기 일관성이라고 한다.

Lock

profile
YangJiWon
데이터데이터데이터!!
이전 포스트

Oracle SQL(6)

다음 포스트

Oracle SQL(8)

0개의 댓글