1. TCL ( Transaction Control Language : 트랜잭션 제어어 )
1) 트랜잭션 개요
트랜잭션은 데이터베이스의 논리적 연산단위이다.
트랜잭션(TRANSACTION)이란 밀접히 관련되어 분리될 수 없는 한 개 이상의 데이터베이스 조작을 가리킨다.
하나의 트랜잭션에는 하나 이상의 SQL 문장이 포함된다.
트랜잭션은 분할할 수 없는 최소의 단위이다.
그렇기 때문에 전부 적용하거나 전부 취소한다.
즉, TRANSACTION은 ALL OR NOTHING의 개념인 것이다.
은행에서의 계좌이체 상황을 연상하면 트랜잭션을 이해하는데 도움이 된다.
계좌이체는 최소한 두 가지 이상의 작업으로 이루어져 있다.
우선 자신의 계좌에서 잔액을 확인하고 이체할 금액을 인출한 다음 나머지 금액을 저장한다.
그리고 이체할 계좌를 확인하고 앞에서 인출한 금액을 더한 다음에 저장하면 계좌이체가 성공한다.
계좌이체 사례
가. 100번 계좌의 잔액에서 10,000원을 뺀다.
나. 200번 계좌의 잔액에 10,000원을 더한다.
계좌이체라는 작업 단위는 이런 두 개의 업데이트가 모두 성공적으로 완료되었을 때 종료된다.
둘 중 하나라도 실패할 경우 계좌이체는 원래의 금액을 유지하고 있어야만 한다.
만약 어떠한 장애에 의해 어느 쪽이든 한 쪽만 실행했을 경우, 이체한 금액은 어디로 증발해 버렸거나 마음대로 증가하게 된다.
당연히 그런 일이 있어서는 안 되므로 이러한 경우에는 수정을 취소하여 원 상태로 되돌려야 한다.
이런 계좌이체 같은 하나의 논리적인 작업 단위를 구성하는 세부적인 연산들의 집합을 트랜잭션이라 한다.
이런 관점에서 데이터베이스 응용 프로그램은 트랜잭션의 집합으로 정의할 수도 있다.
올바르게 반영된 데이터를 데이터베이스에 반영시키는 것을 커밋(COMMIT), 트랜잭션 시작 이전의 상태로 되돌리는 것을 롤백(ROLLBACK)이라고 하며, 저장점(SAVEPOINT) 기능과 함께 3가지 명령어를 트랜잭션을 콘트롤하는 TCL(TRANSACTION CONTROL LANGUAGE)로 분류한다.
트랜잭션의 대상이 되는 SQL문은 UPDATE, INSERT, DELETE 등 데이터를 수정하는 DML 문이다.
트랜잭션의 특성
2) COMMIT
입력한 자료나 수정한 자료에 대해서 또는 삭제한 자료에 대해서 전혀 문제가 없다고 판단되었을 경우 COMMIT 명령어를 통해서 트랜잭션을 완료할 수 있다.
COMMIT이나 ROLLBACK 이전의 데이터 상태는 다음과 같다.
가. 단지 메모리 BUFFER에만 영향을 받았기 때문에 데이터의 변경 이전 상태로 복구 가능하다.
나. 현재 사용자는 SELECT 문장으로 결과를 확인 가능하다.
다. 다른 사용자는 현재 사용자가 수행한 명령의 결과를 볼 수 없다.
라. 변경된 행은 잠금(LOCKING)이 설정되어서 다른 사용자가 변경할 수 없다.
COMMIT 이후의 데이터 상태는 다음과 같다.
가. 데이터에 대한 변경 사항이 데이터베이스에 반영된다.
나. 이전 데이터는 영원히 잃어버리게 된다.
다. 모든 사용자는 결과를 볼 수 있다.
라. 관련된 행에 대한 잠금(LOCKING)이 풀리고, 다른 사용자들이 행을 조작할 수 있게 된다.
Oracle은 DML을 실행하는 경우 DBMS가 트랜잭션을 내부적으로 실행하며,
DML 문장 수행 후 사용자가 임의로 COMMIT 혹은 ROLLBACK을 수행해 주어야 트랜잭션이 종료된다.
(일부 툴에서는 AUTO COMMIT을 옵션으로 선택할 수 있다.)
하지만, SQL Server는 기본적으로 AUTO COMMIT 모드이기 때문에 DML 수행 후 사용자가 COMMIT이나 ROLLBACK을 처리할 필요가 없다.
DML 구문이 성공이면 자동으로 COMMIT이 되고 오류가 발생할 경우 자동으로 ROLLBACK 처리된다.
3) ROLLBACK
테이블 내 입력한 데이터나, 수정한 데이터, 삭제한 데이터에 대하여 COMMIT 이전에는 변경 사항을 취소할 수 있는데 데이터베이스에서는 롤백(ROLLBACK) 기능을 사용한다.
롤백(ROLLBACK)은 데이터 변경 사항이 취소되어 데이터의 이전 상태로 복구되며, 관련된 행에 대한 잠금(LOCKING)이 풀리고 다른 사용자들이 데이터 변경을 할 수 있게 된다.
ROLLBACK 후의 데이터 상태는 다음과 같다.
가. 데이터에 대한 변경 사항은 취소된다.
나. 이전 데이터는 다시 재저장된다.
다. 관련된 행에 대한 잠금(LOCKING)이 풀리고, 다른 사용자들이 행을 조작할 수 있게 된다.
COMMIT과 ROLLBACK을 사용함으로써 다음과 같은 효과를 볼 수 있다.
가. 데이터 무결성 보장
나. 영구적인 변경을 하기 전에 데이터의 변경 사항 확인 가능
다. 논리적으로 연관된 작업을 그룹핑하여 처리 가능
4) SAVEPOINT
저장점(SAVEPOINT)을 정의하면 롤백(ROLLBACK)할 때 트랜잭션에 포함된 전체 작업을 롤백하는 것이 아니라 현 시점에서 SAVEPOINT까지 트랜잭션의 일부만 롤백할 수 있다.
따라서 복잡한 대규모 트랜잭션에서 에러가 발생했을 때 SAVEPOINT까지의 트랜잭션만 롤백하고 실패한 부분에 대해서만 다시 실행할 수 있다. (일부 툴에서는 지원이 안 될 수 있음)
복수의 저장점을 정의할 수 있으며, 동일이름으로 저장점을 정의했을 때는 나중에 정의한 저장점이 유효하다.
다음의 SQL문은 SVPT1이라는 저장점을 정의하고 있다.
SAVEPOINT SVPT1;
저장점까지 롤백할 때는 ROLLBACK 뒤에 저장점 명을 지정한다.
ROLLBACK TO SVPT1;
위와 같이 롤백(ROLLBACK)에 SAVEPOINT 명을 부여하여 실행하면 저장점 설정 이후에 있었던 데이터 변경에 대해서만 원래 데이터 상태로 되돌아가게 된다.
SQL Server는 SAVE TRANSACTION을 사용하여 동일한 기능을 수행할 수 있다.
다음의 SQL문은 SVTR1이라는 저장점을 정의하고 있다.
SAVE TRANSACTION SVTR1;
저장점까지 롤백할 때는 ROLLBACK 뒤에 저장점 명을 지정한다.
ROLLBACK TRANSACTION SVTR1;
그림에서 보듯이 저장점 A로 되돌리고 나서 다시 B와 같이 미래 방향으로 되돌릴 수는 없다.
일단 특정 저장점까지 롤백하면 그 저장점 이후에 설정한 저장점이 무효가 되기 때문이다.
즉, ‘ROLLBACK TO A’를 실행한 시점에서 저장점 A 이후에 정의한 저장점 B는 존재하지 않는다.
저장점 지정 없이 “ROLLBACK”을 실행했을 경우 반영안된 모든 변경 사항을 취소하고 트랜잭션 시작 위치로 되돌아간다.
정리
해당 테이블에 데이터의 변경을 발생시키는 입력(INSERT), 수정(UPDATE), 삭제(DELETE) 수행시 그 변경되는 데이터의 무결성을 보장하는 것이 커밋(COMMIT)과 롤백(ROLLBACK)의 목적이다.
저장점(SAVEPOINT/SAVE TRANSACTION)은 “데이터 변경을 사전에 지정한 저장점까지만 롤백하라”는 의미이다.
Oracle의 트랜잭션은 트랜잭션의 대상이 되는 SQL 문장을 실행하면 자동으로 시작되고, COMMIT 또는 ROLLBACK을 실행한 시점에서 종료된다.
단, 다음의 경우에는 COMMIT과 ROLLBACK을 실행하지 않아도 자동으로 트랜잭션이 종료된다.
가. CREATE, ALTER, DROP, RENAME, TRUNCATE TABLE 등 DDL 문장을 실행하면 그 전후 시점에 자동으로 커밋된다.
DML 문장 이후에 커밋 없이 DDL 문장이 실행되면 DDL 수행 전에 자동으로 커밋된다.
나. 데이터베이스를 정상적으로 접속을 종료하면 자동으로 트랜잭션이 커밋된다.
다. 애플리케이션의 이상 종료로 데이터베이스와의 접속이 단절되었을 때는 트랜잭션이 자동으로 롤백된다.
SQL Server의 트랜잭션은 DBMS가 트랜잭션을 컨트롤하는 방식인 AUTO COMMIT이 기본 방식이다. 다음의 경우는 Oracle과 같이 자동으로 트랜잭션이 종료된다.
애플리케이션의 이상 종료로 데이터베이스(인스턴스)와의 접속이 단절되었을 때는 트랜잭션이 자동으로 롤백된다.
2. WHERE 절
1) WHERE 조건절 개요
자료를 검색할 때 SELECT 절과 FROM 절만을 사용하여 기본적인 SQL 문장을 구성한다면, 테이블에 있는 모든 자료들이 결과로 출력되어 실제로 원하는 자료를 확인하기 어려울 수 있다.
사용자들은 자신이 원하는 자료만을 검색하기 위해서 SQL 문장에 WHERE 절을 이용하여 자료들에 대하여 제한할 수 있다.
WHERE 절에는 두 개 이상의 테이블에 대한 조인 조건을 기술하거나 결과를 제한하기 위한 조건을 기술할 수도 있다.
SELECT [DISTINCT/ALL] 칼럼명 [ALIAS명] FROM 테이블명 WHERE 조건식;
2) 연산자의 종류
만일 이러한 연산에 있어서 연산자들의 우선순위를 염두에 두지 않고 WHERE 절을 작성한다면 테이블에서 자기가 원하는 자료를 찾지 못하거나, 혹은 틀린 자료인지도 모른 채 사용할 수도 있다.
실수하기 쉬운 비교 연산자와 논리 연산자의 경우 괄호를 사용해서 우선순위를 표시하는 것을 권고한다.
3) SQL 연산자
SQL 연산자는 SQL 문장에서 사용하도록 기본적으로 예약되어 있는 연산자로서 모든 데이터 타입에 대해서 연산이 가능한 4가지 종류가 있다.
LIKE의 사전적 의미는 ‘~와 같다’이다.
만약 “장”씨 성을 가진 선수들을 조회할 경우는 어떻게 할까?
이런 문제를 해결하기 위해서 LIKE 연산자에서는 와일드카드(WildCard)를 사용할 수 있다.
와일드카드(WildCard)란 한 개 혹은 0개 이상의 문자를 대신해서 사용하기 위한 특수 문자를 의미하며, 이를 조합하여 사용하는 것도 가능하므로 SQL 문장에서 사용하는 스트링(STRING) 값으로 용이하게 사용할 수 있다.
[예제] “장”씨 성을 가진 선수들의 정보를 조회하는 WHERE 절을 작성한다.
[답안] SELECT PLAYER_NAME 선수이름
FROM PLAYER
WHERE PLAYER_NAME LIKE '장%';
NULL(ASCII 00)은 값이 존재하지 않는 것으로 확정되지 않은 값을 표현할 때 사용한다.
따라서 어떤 값보다 크거나 작지도 않고 ‘ ’(공백, ASCII 32)이나 0(Zero, ASCII 48)과 달리 비교 자체가 불가능한 값인 것이다.
연산 관련 NULL의 특성은 다음과 같다.
가. NULL 값과의 수치연산은 NULL 값을 리턴한다.
나. NULL 값과의 비교연산은 거짓(FALSE)을 리턴한다.
다. 어떤 값과 비교할 수도 없으며, 특정 값보다 크다, 적다라고 표현할 수 없다.
따라서 NULL 값의 비교는 비교 연산자인 “=”, “>”, “>=”, “<”, “=”를 통해서 비교할 수도 없고,
만일 비교 연산을 하게 되면 결과는 거짓(FALSE)을 리턴하고,
수치 연산자(+,-,*,/ 등)를 통해서 NULL 값과 연산을 하게 되면 NULL 값을 리턴한다.
NULL 값의 비교 연산은 IS NULL, IS NOT NULL 이라는 정해진 문구를 사용해야 제대로 된 결과를 얻을 수 있다.
4) 논리연산자
논리 연산자는 비교 연산자나 SQL 비교 연산자들로 이루어진 여러 개의 조건들을 논리적으로 연결시키기 위해서 사용되는 연산자이다.
논리 연산자들이 여러 개가 같이 사용되었을 때의 처리 우선순위는 ( ), NOT, AND, OR의 순서대로 처리된다.
5) 부정연산자
비교 연산자, SQL 비교 연산자에 대한 부정 표현을 부정 논리 연산자, 부정 SQL 연산자로 구분할 수 있다.
6) ROWNUM, TOP 사용
가. ROWNUM
Oracle의 ROWNUM은 칼럼과 비슷한 성격의 Pseudo Column으로써 SQL 처리 결과 집합의 각 행에 대해 임시로 부여되는 일련번호이며, 테이블이나 집합에서 원하는 만큼의 행만 가져오고 싶을 때 WHERE 절에서 행의 개수를 제한하는 목적으로 사용한다.
1건의 행만 가져오고 싶을 때는
SELECT PLAYER_NAME
FROM PLAYER
WHERE ROWNUM = 1;
추가적인 ROWNUM의 용도로는 테이블 내의 고유한 키나 인덱스 값을 만들 수 있다.
UPDATE MY_TABLE SET COLUMN1 = ROWNUM;
나. TOP 절
SQL Server는 TOP 절을 사용하여 결과 집합으로 출력되는 행의 수를 제한할 수 있다.
TOP 절의 표현식은 다음과 같다.
TOP (Expression) [PERCENT] [WITH TIES]
Expression : 반환할 행의 수를 지정하는 숫자이다.
PERCENT : 쿼리 결과 집합에서 처음 Expression%의 행만 반환됨을 나타낸다.
WITH TIES : ORDER BY 절이 지정된 경우에만 사용할 수 있으며,
TOP N(PERCENT)의 마지막 행과 같은 값이 있는 경우 추가 행이 출력되도록 지정할 수 있다.
한 건의 행만 가져오고 싶을 때는
SELECT TOP(1) PLAYER_NAME FROM PLAYER;
데이터 전문가 자격증 SQLD
2. SQL 기본 및 활용
1) SQL 기본
가. 관계형 데이터베이스 개요
나. DDL
다. DML
라. TCL
마. WHERE 절
바. 함수( FUNCTION )
사. GROUP BY, HAVING 절
아. ORDER BY 절
자. 조인
중
라. TCL
마. WHERE 절
를 데이터 전문가 지식포털 DBGuide.net 을 바탕으로 정리, 요약했습니다.
데이터 전문가 지식포털 DBGuide.net
엔터티 속성 관계 식별자 데이터 모델의 이해 1. 엔터티의 개념 데이터 모델을 이해할 때 가장 명확하게 이해해야 하는 개념 중에 하나가 바로 엔터티(Entity)이다. 이것은 우리말로 실체, 객체라��
www.dbguide.net
해당 사이트에서 더욱 전문적인 데이터 관련 지식을 다루고 있으니, 꼭 한 번 확인하시면 좋을 것 같습니다!
'DB > 기본 개념' 카테고리의 다른 글
| [SQL 기본 및 활용] SQL 최적화 기본 원리 (0) | 2020.09.04 |
|---|---|
| [SQL 기본 및 활용] SQL 기본 - 3 (0) | 2020.09.02 |
| [SQL 기본 및 활용] SQL 기본 - 1 (0) | 2020.09.01 |
| [데이터 모델링의 이해] 데이터 모델과 성능 - 2 (0) | 2020.09.01 |
| [데이터 모델링의 이해] 데이터 모델과 성능 - 1 (0) | 2020.08.31 |