Chap06 - SQL 프로그래밍

Chap06 - SQL 프로그래밍

저번 포스팅인 Chap05 - 조인, JOIN에서는 INNER JOIN 과 OUTER JOIN 그리고 UNION/EXCEPT/INTERSECT에 대해 알아보았다. 이번 포스팅에서는 SQL 프로그래밍에 대해 알아보도록 하자.

T-SQL에서도 Python이나 Java와 같은 다른 프로그래밍 언어와 마찬가지로 IF∙∙∙ELSE.., WHILE, TRY...CATCH 등과 같은 프로그래밍이 가능하다.

이번 포스팅에서 이루어지는 실습은 Chap03-1.Transact-SQL 기본에서 생성한 sqlDB를 사용하기 때문에, 먼저 해당 링크를 통해 sqlDB를 생성해야 한다.

1. BEGIN∙∙∙END

Transact-SQL 문의 그룹을 실행할 수 있도록 일련의 Transact-SQL 문을 묶는 역할을 한다. BEGIN과 END는 흐름 제어 언어 키워드다. BEGIN...END의 구문은 다음과 같다.

BEGIN  
    { sql_statement | statement_block }   
END

2. IF ∙∙∙ ELSE..

IF...ELSE문은 Transact-SQL 문을 실행하기 위한 조건을 설정하며, IF문의 조건이 만족되는 경우 실행된다. ELSE 키워드는 선택적이며 IF 조건이 만족되지 않는 경우에 실행된다. IF...ELSE를 사용할 때에는 BEGIN...END로 쿼리문들을 묶어주는 것이 좋다. 그렇지 않을 경우에는 IF나 ELSE 조건이 하나의 Transact-SQL 문 실행에만 영향을 준다.

IF...ELSE문의 형식은 다음과 같다.

-- IF...ELSE
IF <boolean 표현식>
        BEGIN
                SQL 문장들1...
    END    
ELSE
        BEGIN
                SQL 문장들2...
END

아래의 쿼리는 IF...ELSE문을 사용하여 @var1의 값이 100인지 아닌지에 따라 다른 메시지를 출력하는 쿼리문이다.

 
DECLARE @var1 INT = 100  -- @var1 변수 선언 및 초기화
 
IF @var1 = 100  -- 만약 @var1이 100이라면,
    BEGIN
        PRINT '@var1이 100이다.'
    END
ELSE
    BEGIN
        PRINT '@var1이 100이 아니다.'
    END

3. CASE

IF문은 TRUE 또는 FALSE 값으로만 실행되기 때문에 두 가지 조건만 설정할 수 있는 반면, CASE는 여러가지 조건을 설정할 수 있으며, 여러가지 조건 중에 하나를 반환한다.

CASE 식에는 다음과 같이 두 가지 형식이 있다.

• 단순 CASE 식은 특정 식을 일련의 단순 식과 비교하여 결과를 결정한다.

• 검색된 CASE 식은 일련의 Boolean 식을 평가하여 결과를 결정한다.

-- Syntax for SQL Server and Azure SQL Database  
​
Simple CASE expression:   
CASE input_expression   
     WHEN when_expression THEN result_expression [ ...n ]   
     [ ELSE else_result_expression ]   
END   
Searched CASE expression:  
CASE  
     WHEN Boolean_expression THEN result_expression [ ...n ]   
     [ ELSE else_result_expression ]   
END

IF문과 CASE문의 차이를 알아보기 위해 다음의 예제를 통해서 살펴보도록 하자. 아래의 예제는 90점 이상은 A, 80점 이상은 B, 70점 이상은 C, 60점 ㅇ상은 D, 60점 미만은 F로 학점을 메기는 쿼리를 IF와 CASE를 통해 구현한 것이다.

먼저 IF문을 살펴보도록 하자.

DECLARE @point INT = 77, @grade NCHAR(1)
 
IF @point >= 90
    SET @grade = 'A'
ELSE 
    IF @point >= 80
        SET @grade = 'B'
    ELSE
        IF @point >= 70
            SET @grade = 'C'
        ELSE
            IF @point >= 60
                SET @grade = 'D'
            ELSE
                SET @grade = 'F'
 
PRINT N'취득점수==> ' + CAST(@point AS NCHAR(3))
PRINT N'학점==> ' + @grade

위의 쿼리문에서도 알 수 있듯이, IF문을 사용해서 구현하게 되면, IF문을 중첩해서 사용해야지 구현할 수 있기때문에 쿼리문이 복잡해져 가독성이 떨어진다. 이러한 경우에 CASE문을 이용해 편리하고, 간결하게 구현할 수 있다.

DECLARE @point INT = 77, @grade NCHAR(1)
 
SET @grade = 
    CASE
        WHEN (@point >= 90) THEN 'A'
        WHEN (@point >= 80) THEN 'B'
        WHEN (@point >= 70) THEN 'C'
        WHEN (@point >= 60) THEN 'D'
        ELSE 'F'
    END
 
PRINT N'취득점수==> ' + CAST(@point AS NCHAR(3))
PRINT N'학점==> ' + @grade

3.1 SELECT 문에서의 CASE문

CASE문의 활용은 SELECT 문에서도 많이 사용된다. 아래의 예제는 sqlDB의 buyTbl(구매 테이블)에 구매액(price*amount)이 1,500 이상인 고객은 '최우수고객', 1,000이상인 고객은 '우수고객', 1 이상인 고객은 '일반고객'으로 출력하는 것을 CASE문을 이용한 예제이다.

USE sqlDB;
 
SELECT U.userid, U.name, SUM(price*amount) AS [총구매액],
    CASE
        WHEN (SUM(price*amount) >= 1500) THEN N'최우수고객'
        WHEN (SUM(price*amount) >= 1000) THEN N'우수고객'
        WHEN (SUM(price*amount) >= 1) THEN N'일반고객'
        ELSE N'유령고객'
    END AS [고객등급]
FROM buyTbl B
    RIGHT OUTER JOIN userTbl U
    ON B.userID = U.userID
GROUP BY U.userID, U.name
ORDER BY SUM(price*amount) DESC;

4. WHILE / BREAK / CONTINUE

4.1 WHILE

WHILE문은 다른 프로그래밍 언어의 WHILE과 같은 개념이다. SQL 문 또는 문 블록의 반복 실행을 위한 조건을 설정한다. SQL문은 지정된 조건이 true인 한 반복적으로 실행되며, WHILE 루프 내의 SQL문 실행은 BREAK와 CONTINUE 키워드를 사용하여 루프 내에서 제어할 수 있다.

WHILE <Boolean 식>
BEGIN
        SQL 명령문들 
        { sql_statement | statement_block | BREAK | CONTINUE } 
END

아래의 예제는 1에서 100까지의 값을 모두 더하는 것을 WHILE을 이용해 구한 예제이다.

DECLARE @i INT = 1  -- 1에서 100까지 증가할 변수
DECLARE @hap BIGINT = 0  -- 더한 값을 누적할 변수
 
WHILE (@i <= 100)
BEGIN
    SET @hap += @i  -- @hap의 원래의 값에 @i를 더해서 다시 @hap에 더해줌
    SET @i += 1     -- @i의 원래의 값에 1을 더해서 다시 @i에 넣음
END
 
PRINT @hap

4.2 CONTINUE / BREAK

CONTINUE문은 WHILE 루프를 다시 시작하는 역할을 하며, CONTINUE 키워드 다음에 나오는 SQL문은 모두 무시된다. CONTINUE는 항상은 아니지만 IF 문에 의해 실행되는 경우가 많다.

BREAK문은 WHILE 문의 가장 안쪽의 루프 또는 WHILE 루프 내부의 IF ELSE 문을 종료한다. 루프의 끝을 표시하는 END 키워드 다음에 표시되는 모든 SQL문은 실행된다. BREAK는 IF 문에 의해 시작되는 경우가 많습니다.

다음의 예제는 1에서 100까지 중에서 7의 배수는 제외하고, 합계(@hap)가 1,000이 넘으면 멈추는 것을 CONTINUE문과 BREAK문을 가지고 구현한 예제이다.

DECLARE @i INT = 1  -- 1에서 100까지 증가할 변수
DECLARE @hap BIGINT = 0  -- 더한 값을 누적할 변수
 
WHILE (@i <= 100)
BEGIN
    IF (@i % 7 = 0)
        BEGIN
            PRINT N'7의 배수: ' + CAST(@i AS NCHAR(3))
            SET @i += 1
            CONTINUE
        END
 
    SET @hap += @i
    IF (@hap > 1000) BREAK
    
    SET @i += 1
END
 
PRINT N'합계=' + CAST(@hap AS NCHAR(10))

5. GOTO

GOTO문은 실행 흐름을 지정한 위치로 무조건 이동하게 하는 역할을 한다. GOTO 다음에 이어지는 Transact-SQL 문을 건너뛰고 지정된 레이블에서 처리를 계속 이어간다. 따라서, GOTO문은 실행 흐름을 강제로 깨는 것이기 때문에 꼭 필요할 때 이외에는 사용하지 않는 것이 좋다. GOTO 구문의 형식은 다음과 같다.

Define the label:   
label:   
Alter the execution:  
GOTO label

다음의 예제는 1에서 100까지 더한값이 1,000이 넘을 경우 endprint레이블로 GOTO를 이용해 강제로 이동시키는 예제이다.

DECLARE @i INT = 1
DECLARE @hap BIGINT = 0
 
WHILE (@i <= 100)
BEGIN
    IF (@hap > 1000) GOTO endprint
    SET @hap += @i
    SET @i += 1
END
 
endprint:  -- 레이블
PRINT N'합계=' + CAST(@hap AS NCHAR(10))

6. WAITFOR

WAITFOR문은 코드의 실행을 일시정지하는 역할을 한다. WAITFOR문은 다음과 같이 WAITFOR DELAY와 WAITFOR TIME이 있다.

• WAITFOR DELAY : 지정된 시간만큼 일시정지 시키는 역할을 하며, 최대 24시간까지 설정할 수 있다.

• WAITFOR TIME : 지정된 시각에 실행시키는 역학을 한다.

WAITFOR문의 형식은 다음과 같다.

WAITFOR   
{  
    DELAY 'time_to_pass'   
      | TIME 'time_to_execute'   
      | [ ( receive_statement ) | ( get_conversation_group_statement ) ]   
        [ , TIMEOUT timeout ]  
}

아래의 예제는 WAITFOR문의 간단한 예제이다.

BEGIN
    WAITFOR DELAY '00:00:05';
    PRINT N'5초간 멈춘 후 진행되었음';
END

7. TRY/CATCH, RAISEERROR, THROW

7.1 TRY/CATCH

TRY/CATCH는 SQL Server 2005부터 포함된 기능이며, 다른 프로그래밍 언어에 있는 기능과 유사하다. TRY/CATCH의 구문은 다음과 같다.

BEGIN TRY
        원래 사용하던 SQL 문장들
END TRY
BEGIN CATCH
        만약 BEGIN ... TRY에서 오류가 발생하면 처리할 일들
END CATCH

아래의 예제는 sqlDB에서 userTbl에 이미 존재하는 'LSG'라는 아이디를 INSERT를 통해 추가한다고 하자. userID는 PK로 지정되어 있기 때문에, 같은 ID를 입력받으면 오류가 발생한다. 이러한 것을 TRY/CATCH문을 통해 구현해 보도록 하자.

USE sqlDB;
 
BEGIN TRY
    INSERT INTO userTbl VALUES('LSG', '이상구', 1988, '서울', NULL,
            NULL, 170, GETDATE())
    PRINT N'정상적으로 입력되었다.'
END TRY
BEGIN CATCH
    PRINT N'오류가 발생했다.'
END CATCH

T-SQL에서는 오류의 상태를 쉽게 파악할 수 있는 함수를 다음과 같이 제공한다([링크] 참고).

• ERROR_NUMBER()는 오류 번호를 반환한다.

• ERROR_SEVERITY()는 심각도를 반환한다.

• ERROR_STATE()는 오류 상태 번호를 반환한다.

• ERROR_PROCEDURE()는 오류가 발생한 저장 프로시저 또는 트리거의 이름을 반환한다.

• ERROR_LINE()은 오류를 발생시킨 루틴 내의 줄 번호를 반환한다.

• ERROR_MESSAGE()는 오류 메시지의 전체 텍스트를 반환한다.

다음의 예제는 위에서 살펴본 예제에 오류 정보를 제공하는 함수를 이용해 오류의 원인을 파악하는 예제이다.

USE sqlDB;
 
BEGIN TRY
    INSERT INTO userTbl VALUES('LSG', '이상구', 1988, '서울', NULL,
            NULL, 170, GETDATE())
    PRINT N'정상적으로 입력되었다.'
END TRY
BEGIN CATCH
    PRINT N'***오류가 발생했다***'
    PRINT N'오류 번호: '
    PRINT ERROR_NUMBER()
    PRINT N'오류 메시지: ' + ERROR_MESSAGE()
    PRINT N'오류 상태 코드: '
    PRINT ERROR_STATE()
    PRINT N'오류 심각도: '
    PRINT ERROR_SEVERITY()
    PRINT N'오류 발생 행번호: '
    PRINT ERROR_LINE()
    PRINT N'오류 발생 프로시저/트리거: '
    PRINT ERROR_PROCEDURE()
END CATCH

(0개 행이 영향을 받음)
***오류가 발생했다***
오류 번호: 
2627
오류 메시지: PRIMARY KEY 제약 조건 'PK__userTbl__CB9A1CDF5329D454'을(를) 위반했습니다. 개체 'dbo.userTbl'에 중복 키를 삽입할 수 없습니다. 중복 키 값은 (LSG     )입니다.
오류 상태 코드: 
1
오류 심각도: 
14
오류 발생 행번호: 
4
오류 발생 프로시저/트리거: 

7.2 RAISERROR / THROW

RAISERROR/THROW는 오류를 강제로 발생시키는 방법이며, 구문 형식은 각각 다음과 같다.

• RAISERROR

RAISERROR ( { msg_id | msg_str | @local_variable }  
    { ,severity ,state }  
    [ ,argument [ ,...n ] ] )  
    [ WITH option [ ,...n ] ]  

• THROW

THROW [ { error_number | @local_variable },  
        { message | @local_variable },  
        { state | @local_variable } ]   
[ ; ]  

아래의 예제는 RAISERROR/THROW를 사용해 오류를 강제로 발생시키는 간단한 예제이다.

RAISERROR (N'이건 RAISEERROR 오류 발생', 16, 1);
THROW 55555, N'이건 THROW 오류 발생', 1;

메시지 50000, 수준 16, 상태 1, 줄 1
이건 RAISEERROR 오류 발생
메시지 55555, 수준 16, 상태 1, 줄 2
이건 THROW 오류 발생

8. EXEC (동적 SQL)

EXEC 문장(또는 EXECUTE)은 SQL 문장을 실행시키는 역할을 한다. 아래의 예제는 EXEC문장에 대한 간단한 예제이다.

USE sqlDB;
 
DECLARE @sql VARCHAR(100)
SET @sql = 'SELECT * FROM userTbl WHERE userid = ''EJW'''
EXEC (@sql)

위의 쿼리는 'SELECT * FROM userTbl WHERE userid = ''EJW'''를 바로 실행하지 않고, 변수 @sql에 입력시켜 놓고, EXEC()함수로 실행한 예제이다. 이처럼 EXEC()을 이용해서 쿼리문을 실행하는 것을 동적 SQL이라고 한다.

다음의 예는 테이블을 생성할 때, 테이블의 이름이 myTbl2018_11_13과 같이 실행할때 마다 다른이름의 테이블이 생성되도록 동적 SQL을 이용해 구현한 예제이다.

USE sqlDB;
 
DECLARE @curDate DATE
DECLARE @curYear VARCHAR(4)
DECLARE @curMonth VARCHAR(2)
DECLARE @curDay VARCHAR(2)
DECLARE @sql VARCHAR(100)
 
SET @curDate = GETDATE()
SET @curYear = YEAR(@curDate)
SET @curMonth = MONTH(@curDate)
SET @curDay = DAY(@curDate)
 
SET @sql = 'CREATE TABLE myTbl' + @curYear + '_' + @curMonth + '_' 
                + @curDay
 
SET @sql += '(id INT, name NCHAR(10))'
 
EXEC(@sql)

9. 마무리

이번 포스팅에서는 IF...ELSE / CASE / WHILE / TRY...CATCH 등 다양한 SQL 프로그래밍에 대해 알아보았다. 다음 포스팅에서는 SQL Server의 데이터베이스 개체에 대해 알아보도록 하자.