데이터베이스에서 트랜잭션(Transaction)이란 논리적인 일의 단위를 말합니다. 개념적으로 설명하기에는 추상적이고 모호한 면이 많아 실례를 들어 설명하는 편이 훨씬 이해하기에 빠를 것입니다.

아래 그림을 보세요.

사용자가 INSERT 문을 사용해 데이터를 삽입하고, UPDATE 문으로 데이터를 갱신하고, DELETE 문으로 데이터를 삭제하였다고 합시다. 만약 이 모든 과정이 오류없이 수행되었다면 지금까지 실행한 모든 작업을 ‘실제로’ 수행하라는 명령을 내릴 수 있는데 이 때의 명령이 바로 ‘COMMIT’ 명령입니다. ‘COMMIT’ 명령을 주기 전까지의 모든 작업은 ‘ROLLBACK’ 명령으로 원상태로 되돌릴 수 있는 것입니다.
만약 INSERT 작업을 한 다음 ‘SAVEPOINT A’라는 명령을 실행하였다면 나중에 ‘ROLLBACK A’라는 명령을 통해 INSERT 작업을 한 그 위치로 되돌아 올 수 있는 것이죠. 그 전에 ‘COMMIT’ 명령을 실행하지 않았다면 말입니다.
이와 같이 COMMIT 명령으로 하나의 작업이 성공적으로 끝났을 때 우리는 트랜잭션이 성공적으로 수행되었다고 말합니다.

트랜잭션 제어를 위한 명령어(Transaction Coltrol Language)에는 다음과 같은 것들이 있습니다.

• COMMIT

• SAVEPOINT
• ROLLBACK

하나 하나 살펴볼까요?

• COMMIT은 저장되지 않은 모든 데이터를 데이터베이스에 저장하고 현재의 트랜잭션을 종료하라는 명령입니다.

• SAVEPOINT [이름]는 현재까지의 트랜잭션을 특정 이름으로 지정하라는 명령입니다.
• ROLLBACK [TO  SAVEPOINT 이름]저장되지 않은 모든 데이터 변경 사항을 취소하고 현재의 트랜잭션을 끝내라는 명령입니다. 만약 이전에 SAVEPOINT로 지정한 이름이 있으면 그 위치까지 되돌아 갑니다.

이와 같이 COMMIT 또는 ROLLBACK 명령으로 직접 트랜잭션을 완성하거나 취소할 수도 있지만,
다음과 같은 경우에는 자동으로 트랜잭션이 종료됩니다.

• DDL(CREATE, ALTER, DROP) 명령어를 실행할 때

• DCL(GRANT, REVOKE) 명령어를 실행할 때
• Deadlock같은 특정 Error를 만날 때
• SQL*Plus를 종료할 때

위와 같은 경우에는 COMMIT나 ROLLBACK 문이 없어도 트랜잭션이 종료됩니다.

그럼, 정리하는 의미로 다음과 같이 작업을 할 경우 어떠한 결과가 나타날지 한번 생각해 보세요.

1. SQL*Plus시작

2. SELECT * FROM EMP;
3. UPDATE EMP
   SET JOB_CODE = ‘AA’
   WHERE EMP_NUM = ‘10001′;

4. SAVEPOINT update_point;
5. DELETE FROM EMP;
6. ROLLBACK TO update_point;
7. DELETE FROM EMP
   WHERE JOB_CODE = ‘SS’;

8. COMMIT;
9. INSERT INTO CUSTOMER
   VALUES (‘103′,  손병목 , NULL, NULL, SYSDATE);

이상 동주 아빠 손병목이었습니다.

UPDATE, INSERT에 이어 이번 시간에는 행(레코드)을 삭제하는 명령인 DELETE 명령에 대해 알아보겠습니다.

• DELETE는 특정 행(레코드)를 삭제할 때 사용하며, DELETE 문의 형식은 다음과 같습니다.

  DELETE FROM table_name
  WHERE search condition(s);

• 위의 문장을 보면 아시겠지만 DELETE 문에는 특정 컬럼 이름은 기술하지는 않습니다. 즉 행 전체가 아닌 하나의 행(레코드) 중에서 일부 컬럼만 삭제할 수는 없습니다.
• 위에서 WHERE 절을 생략하면 어떻게 될까요?
  테이블의 모든 행이 삭제됩니다.

• 그러나 DELETE 명령으로 데이터(행)만 삭제할 수 있지, 테이블 구조는 삭제할 수 없습니다. 테이블 자체를 삭제하려면 DELETE가 아닌 DROP 명령을 사용합니다.

다음과 같은 테이블이 있다고 가정해 봅시다.
그리고 노란색으로 표시된 10003, 10006, 10007번 레코드, 즉 JOB_CODE가 ‘SS’인 레코드를 삭제해 볼까요.

DELETE
FROM EMP
WHERE JOB_CODE = ‘SS’;

쉽죠?

그럼, 이번에는 조금 고차원(?)적인 문제 하나.
다음과 같은 EMP 테이블에서, EMP_SKILL 테이블의 EMP_NUM을 가지고 있지 않은 테이블을 모두 삭제하려면 어떻게 해야할까요?

이럴 땐, SUBSELECT 문을 써야겠죠.
직접 한번 생각해 보신 다음, 정답을 보시려면 아래 빈 칸을 마우스로 드래그해 보세요.

DELETE
FROM EMP
WHERE EMP_NUM NOT IN
    (SELECT EMP_NUM
     FROM EMP_SKILL );

이상 동주 아빠 손병목이었습니다.

지난 시간에 UPDATE 문에 대해 알아보았습니다.
이번 시간에도 역시 DML의 하나인 INSERT 문에 대해 알아보기로 하겠습니다.

1. INSERT문의 형식과 특징

   INSERT INTO table_name (column_one, column_two, …)
           VALUES (expression_one, expression_two,…);

   • INTO 다음에 오는 테이블에 VALUE의 값을 삽입하는 역할을 합니다.
     이때 INTO 절 다음에 기술하는 column명은 생략할 수 있습니다.

   • VALUES 절에는 상수(CONSTANT) 값, 널(NULL) 값, SYSDATE 등을 쓸 수 있습니다.

   

   위와 같은 CUSTOMER 테이블에 노란 색 부분(‘104′, ‘윤지수’, 등록날짜)의 데이터를 삽입하려면 다음과 같이 씁니다.
   
   INSERT INTO CUSTOMER ( CUST_NO, NAME, MILEAGE, CODE, REG_DATE)
           VALUES (‘104′, ‘윤지수’, NULL, NULL, SYSDATE);
   
   여기서 주의할 것은 MILEAGE와 CODE에는 아무 값도 넣지 않았습니다. 이럴 때 NULL을 사용합니다.
   그리고 REG_DATE에는 위의 데이터를 입력한 날짜를 집어넣기 위해 SYSDATE를 사용하였습니다.

2. INSERT 문에서 SUBSELECT 사용

   위의 예는 데이터를 직접 입력할 경우에 해당되는 것입니다.
   만약 데이터를 입력받을 테이블이 있을 경우 이 테이블의 어떤 값을 다른 테이블로 입력할 경우에는 어떻게 할까요?
   바로 VALUE 절에 SELECT 문을 이용하여 조건에 맞는 데이터를 골라 다른 테이블에 직접 입력할 수도 있습니다.
   이 경우 두 테이블의 칼럼 형식은 같아야 되겠죠.

   자, 그럼 아래 COSTOMER 테이블에서 마일리지(MILEAGE)가 10,000 이상인 고객 정보를 VIP 테이블에 입력하려면 어떻게 할까요?

   
   ↓
   

   INSERT INTO VIP
           SELECT *
           FROM CUSTOMER
           WHERE MILEAGE >= 10000;

   

   여기서 주의해야할 것은,

   • SUBSELECT 문의 WHERE 절을 생략하면 테이블의 모든 데이터가 백업됩니다.(일부러 데이터를 백업할 때 이런 식으로 사용합니다.)

   • INSERT 문에서 SUBSELECT를 쓸 경우 VALUES와 ( )를 사용할 수 없습니다.

이상 동주 아빠 손병목이었습니다.

지금까지 SELECT 문에 대해 장시간에 걸쳐 실습해 보았습니다.
이번 시간부터는 UPDATE, INSERT, DELETE 문에 대해 차례대로 알아 보겠습니다.
UPDATE, INSERT, DELETE 문을 보통 DML – Data Manipulation Language라고 합니다. 우리말로 데이터 조작어라고 번역할 수 있습니다.

SQL 명령어의 분류에 대해서는 이미 제19강 SQL과 SQL*Plus에서 다룬 적이 있습니다. 혹시 기억이 안나시면 참조하시기 바랍니다.

오늘은 DML, 그 첫시간으로 UPDATE 명령에 대해 알아 보겠습니다.

1. UPDATE 형식과 특징

   UPDATE table_name
   SET column_one = expression, column_two = expression, …
   WHERE search condition(s);

   • UPDATE는 테이블의 행(레코드) 값을 ‘변경’할 때 사용합니다.

   • 하나의 UPDATE 문으로 하나의 테이블 또는 뷰(View)만 수정할 수 있습니다.
     예를 들어 하나의 뷰(View)라고 해도 여러 개의 테이블로부터 만들어진 뷰라면 UPDATE 문을 쓸 수 없습니다.

   • SET절에서 원하는 컬럼에 특정 값을 대입합니다. 이때 변경을 원하는 컬럼만 표시하면 됩니다.
   • 테이블의 프라이머리 키는 UPDAGE할 수 없습니다.
     단, 프라이머리 키라고 해도 다른 테이블에서 FK(외래키)로 참조하지 않으면 UPDATE할 수 있습니다.

   • WHERE에서 조건을 지정합니다.
     만약 WHERE절이 없으면 테이블의 모든 행(레코드)를 UPDATE합니다.
2. 실습을 위한 테이블 만들기

   이번 시간 실습을 위해 다음과 같은 CLASS 테이블과 CUSTOMER 테이블을 만들어 둡시다.
   테이블을 만들기 위한 명령어인 CREATE에 대해서는 다음 시간에 다룹니다. 드디어!

   CREATE TABLE CLASS
   ( CODE CHAR(3) PRIMARY KEY,
     DESCRIPTION  VARCHAR2(30));
   INSERT INTO CLASS VALUES (‘F01′, ‘FIRST’);
   INSERT INTO CLASS VALUES (‘B01′, ‘BUSINESS’);
   INSERT INTO CLASS VALUES (‘E01′, ‘ECONOMY’);

   CREATE TABLE CUSTOMER
   ( CUST_NO CHAR(3) PRIMARY KEY,
     NAME VARCHAR2(10) NOT NULL,
     MILEAGE NUMBER,
     CODE CHAR(3),
     REG_DATE DATE NOT NULL);
   INSERT INTO CUSTOMER VALUES (‘100′, ‘김철수’, 10000, ‘F01′, ‘01/01/01′);
   INSERT INTO CUSTOMER VALUES (‘101′, ‘이윤정’, 2000, ‘E01′, ‘01/03/05′);
   INSERT INTO CUSTOMER VALUES (‘102′, ‘박준서’, 5000, ‘B01′, ‘01/02/28′);
   INSERT INTO CUSTOMER VALUES (‘103′, ‘김종현’, 12000, ‘F01′, ‘01/08/15′);

3. 실습 1

   다음과 같은 EMP 테이블에서 EMP_NUM이 10001인 레코드의 JOB_CODE를 “SS”로 변경해 봅시다.

   

   UPDATE EMP
   SET JOB_CODE = ‘SS’
   WHERE EMP_NUM = ‘10001′;

4. 실습 2

   다음과 같은 CUSTOMER 테이블과 CLASS 테이블이 있다고 할 때,
   DESCRIPTION이 “FIRST”인 CODE에 해당하는 사람의 마일리지를 50% 추가해 봅시다.
   두 개의 테이블이 있고, 서로 참조해야 하기 때문에 SubSelect 문을 사용해야 되겠죠?

   

   UPDATE CUSTOMER
   SET MILEAGE = MILEAGE * 1.5
   WHERE CODE = ( SELECT CODE
           FROM CLASS
           WHERE DESCRIPTION = ‘FIRST’);

비교적 쉬운 내용입니다.
실습 2에서 다시 나온 SubSelect 문은 실무에서 매우 유용하게 사용됩니다. 꼭 숙지하도록 해 주시기 바랍니다.

이상 월요일 아침, 예쁜 동주의 빨리 낫기를 빌며
동주 아빠 손병목이었습니다.

SUBSELECT 마지막 시간입니다.
SUBSELECT의 결과 값이 하나라도 있으면 실행되는 EXIST에 관한 용례를 살펴보고,
SUBSELECT문을 FROM 절과 HAVING 절에서 사용할 때의 특징에 대해서도 알아보겠습니다.

1. EXIST

   WHERE 절에서 EXIST를 사용하면,
   SUBSELECT 문의 결과가 True이면 SELECT 문을 수행하고 그렇지 않으면 수행하지 않습니다.

   SELECT EMP_NUM, LAST_NAME, FIRST_NAME, JOB_CODE
   FROM EMP
   WHERE EXISTS
           ( SELECT *
           FROM EMP_SKILL
           WHERE AVAILABLE = ‘YES’);

   

   EMP_SKILL 테이블에서 AVAILABLE의 값이 ‘YES’인 레코드가 존재하면, EMP 테이블의 EMP_NUM, LAST_NAME, FIRST_NAME, JOB_CODE를 표시하라는 뜻입니다.

2. FROM 절에서 SUBSELECT 사용하기

   SUBSELECT 문은 WHERE 절 뿐만 아니라  FROM 절에서도 사용할 수 있습니다.
   WHERE 절에서 사용하는 것과 결과는 동일하지만
   TABLE에 데이터가 많을 때 사용하면 효과적입니다.
   그 이유는, FROM 절에서 SUBSELECT는 VIEW와 같은 역할을 하기 때문입니다.(VIEW에 대해서는 몇 강좌 후에 다룰 예정입니다.)

   SELECT EMP_NUM, SKILL_NAME
   FROM ( SELECT EMP_NUM, SKILL_NAME, AVAILABLE
           FROM SKILL A, EMP_SKILL B
           WHERE A.SKILL_NO = B.SKILL_NO)
   WHERE AVAILABLE = ‘YES’;

3. HAVING 절에서 SUBSELECT 사용하기

   앞서 배운 적이 있지요? HAVING 절은 GROUP을 만드는 조건을 지정할 때 사용합니다.(제28강 오라클 데이터 검색 – ORDER BY, GROUP BY를 보세요.)
   사용법은 WHERE 절에서 사용할 때와 같습니다.

   SELECT DEPT_NO, AVG(AMOUNT)
   FROM DEPT_SALES
   WHERE AMOUNT > 100
   GROUP BY DEPT_NO
   HAVING AVG(AMOUNT) >
           ( SELECT AVG(AMOUNT)
           FROM DEPT_SALES);

   

일요일이 몇 시간 남지 않았네요.
이상 동주 아빠 손병목이었습니다.