ORACLE DATA insert/update/delete

ORACLE DATA INSRET/UPDATE/DELETE에 대해 알아보도록 하겠습니다. 앞선 포스팅에서 설명한 것처럼 데이터베이스의 기본적으로 데이터가 저장되는 곳은 테이블입니다. 그럼 어떤 식으로 데이터를 저장, 수정, 삭제를 하는지 살펴보도록 할게요.

ORACLE DATA INSERT

테이블의 데이터 INSERT 하는 방법은 다음과 같습니다.

INSERT INTO 테이블명 (칼럼1, 칼럼2..... 칼럼10)
VALUES(칼럼1값, 칼럼2값, .... 칼럼10값);

가장 일반적으로 데이터를 삽입(INSERT)하는 방법입니다. 테이블의 모든 칼럼에 데이터를 저장하는 경우 테이블명 옆에 대상 칼럼의 정보는 생략가능합니다. 특정 칼럼만 INSERT를 하는 경우에는 꼭 테이블명 옆에 칼럼 정보를 명시해줘야 합니다. VLAUES 쪽에 지정되는 칼럼 값들도 칼럼 타입, 개수가 동일해야 합니다. 그리고 특정 칼럼만 INSERT 하는 경우 유의할 점이 있습니다. NOT NULL이나 UNIQUE값은 필수로 들어가야 오류가 발생하지 않습니다. 예를 들어보겠습니다.

-- 테이블을 선언합니다.
CREATE TABLE TBL_CUST (
    CUST_NO  NUMBER(10) PRIMARY KEY,
    CUST_NM  VARCHAR2(20) NOT NULL,
    AGE NUMBER(3),
    INS_DATE DATE DEFAULT SYSDATE
) TABLESPACE CUST_TABLESPACE;

-- 데이터를 삽입합니다.
INSERT INTO TBL_CUST(CUST_NO, CUST_NM, AGE, INS_DATE)
VALUES(1, '홍길동', 24, SYSDATE);

-- 테이블의 전체컬럼을 저장할땐 대상 컬럼명을 생략할 수 있습니다.
INSERT INTO TBL_CUST
VALUES(1, '홍길동', 24, SYSDATE);

-- 필수로 저장되어야 하는 컬럼만 저장할 수 있습니다.
-- 이 경우 INS_DATE는 DEFAULT값이 있으므로 현재 날짜가 저장됩니다.
INSERT INTO TBL_CUST(CUST_NO, CUST_NM)
VALUES(1, '홍길동');

-- CUST_NM NOT NULL 제약조건 때문에 오류가 발생합니다.
INSERT INTO TBL_CUST(CUST_NO, AGE)
VALUES(1, 24);

테이블의 데이터를 조회해서 바로 데이터 삽입도 가능합니다. 이 경우는 여러 가지 응용도 가능합니다.

-- 테이블을 선언합니다.
CREATE TABLE TBL_CUST (
    CUST_NO  NUMBER(10) PRIMARY KEY,
    CUST_NM  VARCHAR2(20) NOT NULL,
    AGE NUMBER(3),
    INS_DATE DATE DEFAULT SYSDATE
) TABLESPACE CUST_TABLESPACE;

CREATE TABLE TBL_USER (
    USER_NO NUMBER(10) PRIMARY KEY,
    USER_NM VARCHAR2(20) NOT NULL,
    INS_DATE DATE DEFAULT SYSDATE
) TABLESPACE USER_TABLESPACE; 

-- 방법1. TBL_USER테이블의 특정 데이터를 조회해 USER_NM을 가지고 TBL_CUST에 데이터를 삽입할 수 있습니다.
INSERT INTO TBL_CUST(CUST_NO, CUST_NM, AGE, INS_DATE)
SELECT 2, USER_NM, 30, SYSDATE
  FROM TBL_USER
 WHERE USER_NM = '홍길동';
 
 -- 방법2. 여러개의 텍스트 데이터를 한꺼번에 TBL_USER 테이블에 INSERT하는 방법입니다.
 -- DUAL로 가상ROW를 만들어 UNION ALL로 데이터를 합쳐서 INSERT합니다.
 INSERT INTO TBL_USER(USER_NO, USER_NM)
 SELECT 1, '정도현' FROM DUAL UNION ALL
 SELECT 2, '유영선' FROM DUAL UNION ALL
 SELECT 3, '김명준' FROM DUAL;

ORACLE DATA UPDATE

테이블의 데이터 UPDATE 하는 방법은 다음과 같습니다.

UPDATE 테이블명 
   SET 칼럼1 = 칼럼1값, 칼럼2 = 칼럼2값 ....
 WHERE 조건;

테이블에 변경할 칼럼을 SET구문에 지정하고 WHERE조건으로 업데이트할 대상을 지정합니다. WHERE조건은 생략가능하지만 전체 데이터를 대상으로 칼럼 정보를 변경하게 되기 때문에 이점 유의 해야 합니다. 또한, SET절에 기본키 값을 변경하게 될 경우 데이터 무결성 오류가 발생할 수 있습니다. 다음은 UPDATE 문 예제입니다.

-- 고객테이블에 홍길동이름을 가진 사람을 전민은으로 전부 변경하는 업데이트문 입니다.
UPDATE TBL_CUST
   SET CUST_NAME = '전민은'
     , UPD_DATE = SYSDATE
 WHERE CUST_NAME = '홍길동';

COMMIT, ROLLBACK

DML문 실행 후에는 반드시! 꼭! 무조건! COMMIT 또는 ROLLBACK 명령어를 수행해 줘야 합니다.COMMIT은 하나의 트렌젝션의 변경 사항을 영구적으로 기록하는 명령어입니다. 반대로 ROLLBACK은 실행 취소의 개념입니다. 키보드로 예를 들자면 COMMIT은 엔터키와 같은 기능입니다. ROLLBACK은 백스페이스키 정도로 볼 수 있겠습니다. 우리가 DML문을 실행하면 데이터베이스는 임시적으로 저장을 하게 됩니다. 내 세션에서는 변경이 된 데이터로 보이고 있지만 다른 사용자들에겐 아직 내가 변경하기 전의 데이터로 보이고 있습니다. 이 상태에서 COMMIT을 치는 순간 데이터는 영구적으로 저장이 되고 다른 사용자들에게도 변경된 데이터가 보이게 되는 것입니다. DML문을 실행하고 확인하던 중 잘못 실행됐다 싶으면 ROLLBACK으로 되돌릴 수 있습니다. COMMIT을 실행하기 전까지. DML문으로 데이터를 수정 후 COMMIT을 먼저 실행하고 ROLLBACK을 하면 되돌릴 수 없습니다. COMMIT전에는 신중하게 검토하고 COMMIT을 실행해야 합니다. 특정세션에서 데이터 변경이 생기면 내부적으로 테이블에 락이 걸립니다. 데이터의 무결성 때문에 중복으로 수정을 할 수 없도록 하기 위한 데이터베이스 방침입니다. 만약 DML문을 실행하고 COMMIT이나 ROLLBACK을 실행 안 하면 DML을 실행한 세션의 락이 풀릴 때까지 후순위로 들어오는 DML문은 대기를 하고 있게 됩니다. 빈번한 DML이 발생하는 테이블이라면 세션이 많이 쌓이고 시스템이 느려지게 되며 최악에는 데이터베이스가 뻗어버릴 수도 있습니다. DML문 실행 이후에는 반드시 종료메시지(COMMIT, ROLLBACK)를 실행해 주셔야 합니다.

-- 실행순서1
-- A사용자 UPDATE 후 커밋 누락
UPDATE TBL_CUST CUST_NM = '전민은'
WHERE CUST_NO = 1;

-- 실행순서2
-- B사용자 동일한 ROW UPDATE
UPDATE TBL_CUST CUST_NM = '허용재'
WHERE CUST_NO = 1;

B사용자는 A사용자의 트렌젝션이 끝날 때까지 기다리게 됩니다. A사용자가 COMMIT 하는 시점에 고객명은 '전민은'으로 적용이 되고 이어서 B사용자가 실행한 '허용재'가 실행되면서 최종적으로 고객명은 '허용재'가 적용이 됩니다. 이렇게 해당 레코드에 대해 락을 걸어 데이터의 무결성을 보장합니다.

ORACLE DATA MERGE

오라클 데이터 MERGE문은 INSERT와 UPDATE구문을 합쳐 놓은 것입니다. 특정조건(ON)에 데이터가 존재하는 경우 UPDATE구문을, 데이터가 존재하지 않을 경우 INSERT구문을 실행하게 됩니다. 구문은 아래와 같습니다.

MERGE INTO 대상테이블 
USING 소스테이블
ON ( 조건식 )
 WHEN MATCHED THEN
   UPDATE SET 컬럼1 = 컬럼1값, 컬럼2 = 컬럼2값, ...
 WHEN NOT MATCHED THEN
   INSERT (컬럼1, 컬럼2, ...) 
   VALUES (컬럼1값, 컬럼2값, ...)

MERGE문을 예를 들어보겠습니다.

MERGE INTO TBL_CUST_NEW C
USING TBL_CUST N
ON (C.CUST_NO = N.CUST_NO)
WHEN MATCHED THEN
  UPDATE SET C.CUST_NM = N.CUST_NAME
WHEN NOT MATCHED THEN
  INSERT (C.CUST_NO, C.CUST_NM) 
  VALUES (SEQ_CUST.NEXTVAL, N.CUST_NAME);

신규로 생성된 TBL_CUST_NEW 테이블, 기존 TBL_CUST 테이블을 비교해서 고객번호가 일치하면 고객명을 NEW테이블에 업데이트하고 고객번호가 없으면 NEW테이블에 INSERT를 할 수 있습니다. USING 절에는 서브쿼리로도 사용가능하므로 여러 가지 상황에서 유연하게 사용할 수 있습니다.

ORACLE DATA DELETE

ORACLE DATA DELETE는 불필요한 데이터를 삭제할 때 사용하는 구문입니다.

DELETE FROM 테이블명
WHERE 조건식;

DELETE 구문 예제입니다.

DELETE FROM TBL_CUST
WHERE CUST_NAME = '홍길동';

고객명이 홍길동인 사람의 데이터를 삭제하는 구문입니다. WHERE절은 옵션값으로 지정하지 않을 수도 있습니다. 다만 WHERE 옵션을 지정하지 않을 경우 테이블의 전체 데이터가 삭제되기 때문에 주의하셔야 합니다.

지금까지 데이터 삽입, 수정, 삭제에 대해 기본적인 내용을 알아봤습니다. 더 유익한 포스팅으로 또 찾아뵐게요