[MS SQL]6.TRANSACTION,변수와 흐름 제어

TRANSACTION

2개의 테이블을 동시에 입력해야할때 모종의 이유로 2개중 한 개만 입력에 성공하고 나머지는 입력이 안된다면 문제가 발생할수있음

EX) 게임에서 아이템 거래를할때 한곳에서는 아이템을 지우고 다른곳에서는 아이템을 추가해야하는데 어느 한쪽이라도 실행이 되지않으면 문제가됨

 

3가지 명령어를 보면

BEGIN TRAN

COMMIT

ROLLBACK

아래의 COMMIT과 ROLLBACK은 BEGIN TRAN을 선언하지 않으면 사용할수 없다.

 

기존의 accounts 테이블에 값을 집어넣을때 아래와 같이 명령어를 입력했었다.

INSERT INTO accounts VALUES (1,'amugae',100,GETUTCDATE())

 

이 명령어는 자동으로 COMMIT을 해주기때문에 문제 발생시 잘못된 결과를 불러올수있다.

그렇기 때문에

BEGIN TRAN;
	INSERT INTO accounts VALUES (2,'amugae',100,GETUTCDATE())
ROLLBACK;

BEGIN TRAN;
	INSERT INTO accounts VALUES (2,'amugae',100,GETUTCDATE())
COMMIT;

BEGIN TRAN을 사용해서 성공/실패 여부를 받아서 COMMIT할지 ROLLBACK할지를 결정한다.

위의 코드는 단순히 INSERT에 성공하면 ROLLBACK하거나 COMMIT하게 만들어놓은 동작이지만 TRY CATCH구문과 IF를 같이 사용한다면 "INSERT시도시 실패했을때 ROLLBACK 해주세요"라는 실행 흐름을 만들수있다.

 

여기서 @@TRANCOUNT는 TRAN 구문의 수에따라 +된다.즉 INSERT INTO accounts VALUES (1,'amugae',100,GETUTCDATE())
INSERT INTO accounts VALUES (2,'amugae2',100,GETUTCDATE())

 

이 두개가 실행중이니 2개가 된다. @@TRANCOUNT는 COMMIT 혹은 ROLLBACK이 실행되어야 감소된다.

그러므로 예외가 발생했을때 @@TRANCOUNT가 감소되지 않은채로 CATCH구문으로 이동하기 때문에 이것이 정상적이지 않은 상황이란것을 알릴수있다.

BEGIN TRY
	BEGIN TRAN;
		INSERT INTO accounts VALUES (1,'amugae',100,GETUTCDATE())
			INSERT INTO accounts VALUES (2,'amugae2',100,GETUTCDATE())
			COMMIT
END TRY
BEGIN CATCH
	IF @@TRANCOUNT > 0 --현재 활성하된 트랜젝션 반환
		ROLLBACK
END CATCH

 

한 가지 더 주의해야할점은 TRAN안에는 원자적으로 실행될 애들만 넣은것이 좋다,

BEGIN TRAN내부에 오래 걸리는 작업을 수행시키면 COMMIT  or ROLLBACK을 만나기 전까진 다른 쿼리에서 해당 테이블에 대한 접근을 제한하기 때문이다.

 

예를 들어 여기서 위의 2줄만 실행시키고 COMMIt을 실행시키지 않은채로 다른 쿼리에서 accounts테이블에 대한 접근을 시도하면

BEGIN TRAN;
	INSERT INTO accounts VALUES (1,'amugae',100,GETUTCDATE())



COMMIT

 

 

USE GameDB;

SELECT *
FROM accounts

계속해서 처리중이라는 문구만뜬채로 COMMIT or ROLLBACK을 기다릭게 된다.

 

 

변수와 흐름과 제어

MS SQL도 변수를 지정해서 값을 넣어주는것이 가능하다.

정의 동시에 초기화하는 방법과

DECLARE @i AS INT = 10;

 

정의 후 SET을 이용해서 값을 넣어줄수도있다.

DECLARE @j AS INT;
SET @j = 10;

 

이제 테이블에서 해당 조건에 맞은 값을 변수에 저장하고 출력해보자

 

아래의 코드는 INNER JOIN을 수행하고 가장높은 salary인 선수의 firstName과 lastName을 저장하는 코드이다.

DECLARE @firstName AS NVARCHAR(15);
DECLARE @lastName AS NVARCHAR(15);

--SQL SERVER에서 지원하는 문법

SELECT TOP 1 @firstName = p.nameFirst, @lastName = p.nameLast
FROM players AS p
		INNER JOIN salaries AS s
			ON p.playerID = s.playerID
ORDER BY s.salary DESC

SELECT @firstName,@lastName

 

BATCH

다음 주제를 소개하기전에 잠시 배치(batch)에 대한 짧은 설명을 하자면 하나의 묶음으로 분류뒤고 실행되는 명령어 집합

을 지정하는 명령어로 GO를 사용해서 지정이가능하다. 이를 이용해서 변수의 유효범위 설정이 가능하다.

 

무슨 말이냐하면 '@i'라는 이름으로 변수를 만들면 다시 '@i'라는 이름의 변수는 선언이 불가능하지만

DECLARE @i AS INT = 10;

GO
DECLARE @i AS INT = 10;

 

GO를 이용해 유효범위를 분리시켰기때문에 '@i'라는 이름으로 다시 변수 선언이 가능하다.

 

흐름 제어

IF,ELSE

WHILE

두 문구는 프로그래밍 언어를 배웠다면 다 아는 내용이므로 자세하게 설명할 필요는 없으니 생략하고 차이점만 말하자면

IF @i = 10
	PRINT('BINGO');
ELSE
	PRINT('NO!')

 

위 코드는  IF또는 ELSE조건을 만족시 PRINT를 수행하도록 만들어져있다.

여기서 만약 PRINT를 여러줄 출력하고싶다면 단순히 한줄을 더 넣는것이 아니라 BEGIN과 END를 사용해서 감싸줘야한다.

 

IF @i = 10
BEGIN
	PRINT('BINGO');
	PRINT('BINGO');
END
ELSE
	PRINT('NO!')

 

WHILE도 마찬가지로 아래의 코드는 @i변수가 10보다 작거나 같으면 while문을 실행하게 되어있다.

DECLARE @i AS INT = 0;
WHILE @i <= 10
BEGIN
	PRINT @i
	SET @i = @i + 1;
END

 

 

테이블 변수

특이하게도 임시로 사용할 테이블을 변수로 만들어서 저장하는게 가능하다. 만들어진 임시 테이블은 tempdb라는 데이터 베이스에 저장된다고한다.

 

아래의 코드는 @test라는 임시 테이블 변수를 만들어서 형식을 지정해주고, 조건에 부합하는 데이터를 INSERT시키는 작업을 수행한다.

DECLARE @test TABLE
(
	name VARCHAR(50) NOT NULL,
	salary INT NOT NULL
);

INSERT INTO @test
SELECT p.nameFirst + ' ' + p.nameLast, s.salary
FROM players AS p
	INNER JOIN salaries AS s
	ON p.playerID = s.playerID


SELECT *
FROM @test;