Database/MSSQL Server

[MSSQL] BEGIN ... END

안녕하세요.

이 글은 제가 예전에 작성하였던 네이버 블로그의 글을 다시 올린 글입니다. 다소 기존의 글보다 내용의 퀄리티가 떨어질 수 있으니 이점 유의해주시기 바랍니다. 이 글을 작성 하였을 당시의 연차가 1년 차여서 글 쓰는 실력도 없었고 배우면서 정리던 단계라서 많이 부족합니다.

이 글을 읽기 전에 트랜잭션의 개념을 알아두면 정말 좋습니다. RDB에서 트랜잭션이란 작업의 단위를 뜻 합니다. RDB에서는 트랜잭션을 통해서 작업이 묶이고 해당 트랜잭션의 작업은 모두 성공 또는 모두 실패해야 합니다. 그렇기에 Begin End 구문을 통해서 작업을 묶어줘야 합니다. 관련하여 잘 정리된 블로그를 한번 읽어보시고 이 글을 읽으면 많은 도움이 됩니다.

https://csj000714.tistory.com/623

[DB] RDB 트랜잭션 (feat. 트랜잭션 격리 수준)

💡 본 문서는 'RDB 트랜잭션'에 대해 정리해놓은 글입니다. RDB는 관계가 중요하기에 일련의 과정 중 일부만 성공하면 안되기에 트랜잭션을 통해 관리합니다. 이러한 트랜잭션의 특성 및 격리성

csj000714.tistory.com

1) 테스트 준비하기

과거의 글이므로 글이 조금 이상합니다. 이 테스트는 MSSQL 2008에서 수행하였습니다.

프로시저를 만들다 보면 BEGIN... END 구문을 자주 접할 수 있습니다. (요즘은 잘 모르겠네요...?)

하지만 그냥 아무 이유 없이 BEGIN...END 를 사용 하는 인원이 많은데요.

어떤 경우에 BEGIN ... END로 묶어야 할까요?

프로시저를 만들다 보면 BEGIN ... END가 많은데 다음과 같은 상황이 있다고 가정해 봅시다.

예제 테이블 만들기

CREATE TABLE CLOTHING_BEY_LOG
(
 IDX                INT IDENTITY(1,1)
,UserID              VARCHAR(20)
,Phone              VARCHAR(13)
,Product_Num         INT
,Product_Name         VARCHAR(30)
,Regdate            DATETIME
CONSTRAINT PK_CLOTHING_BEY_LOG PRIMARY KEY (IDX)
)
GO

CREATE TABLE COMPUTER_BUY_LOG
(
 IDX                INT IDENTITY(1,1)
,UserID             VARCHAR(20)
,Phone              VARCHAR(13)
,Product_Num         INT
,Product_Name         VARCHAR(30)
,Regdate            DATETIME
CONSTRAINT PK_COMPUTER_BUY_LOG PRIMARY KEY (IDX)
)
GO

CREATE TABLE PRODUCT
(
 Product_Num        INT
,Product_Name       VARCHAR(30)
,Product_Price      INT
CONSTRAINT PK_PRODUCT PRIMARY KEY (Product_Num)
)
GO

CREATE TABLE USE_Money
(
 UserID             VARCHAR(20)
,Product_Num         INT
,USE_Money          INT
CONSTRAINT PK_USE_Money PRIMARY KEY (UserID,Product_Num)
)
GO

각각의 테이블의 역할은 다음과 같습니다.

1. CLOTHING_BEY_LOG 유저가 의류상품을 구매하면 상품 로그를 남김
2. COMPUTER_BUY_LOG 유저가 컴퓨터 상품을 구매하면 상품 로그를 남김
3. PRODUCT 상품에 관한 정보가 들어 있음
4. USE_Money 유저가 특정 상품을 구매하는데 총얼마를 구매하였는지 보여줌

보통 CLOTHING_BEY_LOG, COMPUTER_BUY_LOG를 합쳐서 타입으로 구분하여 나눠줍니다. 하지만 이 경우는 테스트를 위해서 2개의 테이블로 나누었습니다.

테스트 데이터 입력

테스트를 위해서 2건의 상품을 입력합니다. 한 개는 의류 상품이고 한개는 컴퓨터 상품입니다.

INSERT INTO DBO.PRODUCT VALUES (1001,'코트(의류)',1000)
INSERT INTO DBO.PRODUCT VALUES (2001,'노트북(컴퓨터)',100000)

유저가 구매가 가능하도록 프로시저를 만들어 보도록 합니다.

프로시저 생성

CREATE PROC  USER_BUY
 @Type              TINYINT                    -- 1번의류,2번컴퓨터
,@UserID            VARCHAR(20)
,@Phone             VARCHAR(13)
,@Product_Num       INT
AS

       SET NOCOUNT ON
       
       -- 상품정보를담아올변수선언
       DECLARE @Product_Price     INT
       DECLARE @Product_Name      VARCHAR(30)

       -- 상품정보가져오기
       SELECT @Product_Name = Product_Name , @Product_Price = Product_Price
       FROM DBO.PRODUCT WITH(NOLOCK)
       WHERE Product_Num = @Product_Num

       -- 트랜잭션으로처리내용묶음
       BEGIN TRAN
       BEGIN TRY

       -- 의류구매유저정보INSERT
       IF @Type = 1

             INSERT INTO DBO.CLOTHING_BEY_LOG ( UserID, Phone, Product_Num, Product_Name, Regdate)
             VALUES (@UserID,@Phone,@Product_Num,@Product_Name,GETDATE())

             -- 유저사용금액+
                    UPDATE DBO.USE_Money
                    SET USE_Money = USE_Money + @Product_Price
                    WHERE UserID = @UserID

             IF @@ROWCOUNT = 0
             
                    INSERT INTO DBO.USE_Money VALUES (@UserID,@Product_Num,@Product_Price)

       -- 컴퓨터구매유저정보INSERT
       ELSE IF @Type = 2

             INSERT INTO DBO.COMPUTER_BUY_LOG ( UserID, Phone, Product_Num, Product_Name, Regdate)
             VALUES (@UserID,@Phone,@Product_Num,@Product_Name,GETDATE())      

             -- 유저사용금액+
                    UPDATE DBO.USE_Money
                    SET USE_Money = USE_Money + @Product_Price
                    WHERE UserID = @UserID

             IF @@ROWCOUNT = 0

                    INSERT INTO DBO.USE_Money VALUES (@UserID,@Product_Num,@Product_Price)

       COMMIT TRAN
             RETURN 0
       END TRY
       
       BEGIN CATCH
             ROLLBACK TRAN
             RETURN 1
       END CATCH
GO

2) 테스트해보기

유저가 1번 상품 즉, 의류에서 코트를 구매한다고 생각해 보겠습니다.

DECLARE @VAL INT
EXEC @VAL = USER_BUY 1,'Joo Hyoung Kwon','010-1234-5678',1001
SELECT @VAL

'Joo Hyoung Kwon' 유저가 1번 코트를 구매하였습니다. 데이터를 확인해보겠습니다.

SELECT * FROM CLOTHING_BEY_LOG
SELECT * FROM COMPUTER_BUY_LOG
SELECT * FROM PRODUCT
SELECT * FROM USE_Money

위의 SELECT 문의 순서와 실행결과 순서가 같습니다.

1번에 CLOTHING_BEY_LOG 테이블에는 로그가 잘 남았다.

2번에 COMPUTER_BUY_LOG 테이블에는 정상적으로 로그가 남지 않았다.

3번에 PRODUCT의 Product_price (상품가격)대로 3번 USE_Money에 USE_Money(사용금액)이 잘 남았나 봤는데 2000원이다.

무엇이 잘못되었을까요? 문제는 IF문이 인식하는 범위입니다.

IF문은 @Type = 1에 조건에 걸리고 다음 구문입니다.

INSERT INTO DBO.CLOTHING_BEY_LOG ( UserID, Phone, Product_Num, Product_Name, Regdate)
VALUES (@UserID,@Phone,@Product_Num,@Product_Name,GETDATE())

INSERT 구문까지 밖에 인식하지 못하고 그다음에 구문은 조건에 같이 있는 구문이라고 생각하지 못합니다.

그러므로 다음 아래의 쿼리가 2번 실행되는 것입니다.

UPDATE DBO.USE_Money
SET USE_Money = USE_Money + @Product_Price
WHERE UserID = @UserID

이것을 방지하기 위해서 필요한 것이 바로 BEGIN... END 입니다.

3) 문제 해결하기

아래와 같이 프로시저에 BEGIN ... END을 입력하여 트랜잭션으로 묶어줄 수 있습니다.

CREATE PROC  USER_BUY
 @Type              TINYINT                    -- 1번의류,2번컴퓨터
,@UserID            VARCHAR(20)
,@Phone                    VARCHAR(13)
,@Product_Num INT
AS
       SET NOCOUNT ON

       -- 상품정보를담아올변수선언
       DECLARE @Product_Price     INT
       DECLARE @Product_Name      VARCHAR(30)

       -- 상품정보가져오기

       SELECT @Product_Name = Product_Name , @Product_Price = Product_Price
       FROM DBO.PRODUCT WITH(NOLOCK)
       WHERE Product_Num = @Product_Num

       -- 트랜잭션으로처리내용묶음
       BEGIN TRAN
       BEGIN TRY

       -- 의류구매유저정보INSERT
       IF @Type = 1

       BEGIN -- BEGIN 추가
             INSERT INTO DBO.CLOTHING_BEY_LOG ( UserID, Phone, Product_Num, Product_Name, Regdate)
             VALUES (@UserID,@Phone,@Product_Num,@Product_Name,GETDATE())

             -- 유저사용금액+
                    UPDATE DBO.USE_Money
                    SET USE_Money = USE_Money + @Product_Price
                    WHERE UserID = @UserID

             IF @@ROWCOUNT = 0
                    INSERT INTO DBO.USE_Money VALUES (@UserID,@Product_Num,@Product_Price)
       END    -- END 추가  

       -- 컴퓨터구매유저정보INSERT
       ELSE IF @Type = 2

       BEGIN -- BEGIN 추가
             INSERT INTO DBO.COMPUTER_BUY_LOG ( UserID, Phone, Product_Num, Product_Name, Regdate)
             VALUES (@UserID,@Phone,@Product_Num,@Product_Name,GETDATE())      

             -- 유저사용금액+
                    UPDATE DBO.USE_Money
                    SET USE_Money = USE_Money + @Product_Price
                    WHERE UserID = @UserID

             IF @@ROWCOUNT = 0
                    INSERT INTO DBO.USE_Money VALUES (@UserID,@Product_Num,@Product_Price)
       END    -- END 추가

       COMMIT TRAN
             RETURN 0
       END TRY

       BEGIN CATCH
             ROLLBACK TRAN
             RETURN 1
       END CATCH

GO

프로시저를 만들고 데이터를 전부 초기화해줍니다.

TRUNCATE TABLE CLOTHING_BEY_LOG
TRUNCATE TABLE USE_Money

다시 한번 구매를 합니다.

DECLARE @VAL INT
EXEC @VAL = USER_BUY 1,'Joo Hyoung Kwon','010-1234-5678',1001
SELECT @VAL

구매한 결과를 다시 한번 확인합니다.

테이블의 SELECT 순서는 위의 SELECT와 똑같습니다.

여기서 눈여겨볼 것은 마지막의 USE_Money 테이블입니다.

우리는 1개의 코트를 구매하였고, 그 기록이 잘 적립되었습니다.

이렇듯 BEGIN... END는 상당히 중요한 역할을 해줍니다.

프로세스가 만약에 1개만 동작한다면 (INSERT나 UPDATE가 둘 중 하나만 동작하면) 그렇게 할 필요는 없습니다.

하지만 여러 개의 액션이 일어나는 경우 BEGIN... END로 작업의 단위 (처리가 일어나는 단위)로 묶어 줘야 합니다.

출처

https://blog.naver.com/dba_burning/60205275811

Ms SQL_ Begin ... End

프로시저를 만들다 보면 BEGIN ... END 구문을 자주 접할 수 있다. 하지만 그...

blog.naver.com

저작자표시 비영리 변경금지

'Database > MSSQL Server' 카테고리의 다른 글

[MSSQL] NULL 비교 (2)	2023.12.12
The data types xml and nvarchar are incompatible in the add operator. (0)	2018.03.14
SQL Server 2016 - JSON parser (0)	2017.06.01
Partition Truncate Table (0)	2017.04.14
Alwayson Automatic synchronization Login(Alwayson 로그인 자동 동기화) ② (0)	2016.08.25

Contents

새소식

인기 검색어