MSSQL 프로시저 작성하기

- MSSQL은 T-SQL (Transact-SQL)이라는 자체 문법 사용.

- DECLARE로 변수를 선언하고, SET 또는 SELECT로 값을 할당.

- 조건문은 IF ... ELSE를, 반복문은 WHILE을 주로 사용

 

예외 처리 문법 차이

MSSQL: BEGIN TRY ... END TRY와 BEGIN CATCH ... END CATCH를 사용해 예외를 처리.

에러 메시지는 ERROR_MESSAGE()와 같은 내장 함수로 가져온다.

 

1. CREATE PROC DELETE_BOARDGETTO_LOG 이 DELETE_BOARDGETTO_LOG 라는 이름의 저장 프로시저를 만들겠다.

2. AS 정의하고,

3. 여러 문장들을 뭉텅이로 관리할 수 있게 BEGIN, END으로 문장을 감싼다. 

3-1. 여기서, 프로시저, 함수, 트리거의 본문이나 IF, WHILE 같은 제어문 안에서 여러 문장을 쓰고 싶을 때 이 BEGIN...END 블록이 필수.

 

프로시저들의 문장들을 종종 보면 SET NOCOUNT ON;가 BEGIN안에 포함되어 있는 것을 알 수 있다.

이 SET NOCOUNT ON는 무엇이냐,

이 저장 프로시저를 만들어 호출할 때 결과값들이 출력되는 데 그 결과값에서 만약 10개 행의 값이 나왔다는 거를 알려주는 아웃풋을 생략시켜주는 것이다.  결과 값이 많으면 많을수록, 즉 은 쿼리 실행 후 영향을 받은 행의 개수를 알려주는 메시지를 생략하는 명령어. 예를 들어, UPDATE 문을 실행했을 때, (10 rows affected)와 같은 메시지가 출력되는데, 이 메시지를 나오지 않게 막아준다.

 

이 값을 생략하는 이유

• 성능 최적화: 프로시저가 수십, 수백 개의 쿼리를 반복적으로 실행할 때, 매번 영향을 받은 행 개수 메시지를 서버에서 클라이언트로 보내는 과정 자체가 불필요한 네트워크 트래픽을 유발한다. 이 때 SET NOCOUNT ON;을 사용하면 이 과정을 생략해 전반적인 성능을 높일 수 있습니다. 특히 대규모 데이터 처리 작업에서 효과가 크다.
• 불필요한 출력 제거: 프로시저를 호출하는 주체가 사람이 아닌 애플리케이션일 경우, 이 메시지는 오히려 혼란을 주거나 오류의 원인이 될 수 있음. 애플리케이션은 필요한 최종 결과값만 받아야 하는데, 중간에 불필요한 메시지가 끼어들면 프로그램이 제대로 동작하지 않을 수 있기 때문.

즉, 프로시저의 호출 목적은 특정 작업을 수행하는 것이며, 결과값이 많으면 많을수록 성능 저하가 발생할 수 있기 때문에 불필요한 출력을 제어하여 효율을 높이는 것.

 

SET NOCOUNT ON;는 "rows affected" 메시지가 출력되는 것을 막는 명령어이다.

 

의미로 이해하자면, NOCOUNT -> 카운트하지 않겠다. 

여기서 COUNT는 SQL이 쿼리 실행 후 돌려주는 몇 개의 행이 영향을 받는데 그 메세지를 보여주지 않도록 설정하는 것이다.

 

상황을 가정해보자,

"1 row affected", "5 rows affected" 같은 메지시가 계속 뜨면 화면 가독성도 물론이요, 네트워크 부하도 늘어난다.

알림 설정을 끄는 것과 같다고 생각하면 된다.

 

 

이어서

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

set transaction isolation level read uncommitted이거.

- 가장 낮은 수준의 격리(Isolation): 트랜잭션 간의 간섭을 최소화하는 다른 격리 수준들과 달리, READ UNCOMMITTED는 간섭을 가장 많이 허용.

 

- 성능 최적화: 데이터를 읽기 위해 잠금을 기다릴 필요가 없으므로, 대량의 데이터에 대한 읽기 작업 속도가 매우 빠름.

 

즉, 트랜잭션의 격리 수준을 READ UNCOMMITTED로 설정하겠다는 것. 

즉즉, 다른 트랜잭션이 아직 커밋(저장)하지 않은 임시 데이터도 읽는 것을 허용한다.

 

이렇게 왜 한다고? 잠금을 기다릴필요 없어 조회 속도가 빨라지지만, 더티리드라고하는 나중에 취소될 수 있는 잘못된 데이터를 읽을 위험이 있다.

 

하나씩 이해해보자, READ UNCOMMITTED의 의미

이 설정은 가장 낮은 수준의 격리 수준, 더티 리드라고 불리는 현상을 허용한다.

- 더티 리드: 어떤 트랜잭션이 데이터를 수정하고 아직 저장하지 않았는데, 다른 트랜잭션이 그 변경된 데이터를 읽는 상황.

이걸 왜 쓰냐, 그 이유는 성능향상 때문이다. -> 실시간 현황 보고서나 대략적인 데이터를 빠르게 조회해야하는 분석 쿼리에 적합.

 

WITH (NOLOCK)의 역할

- 이건은 잠금 회피. NOLOCK 힌트는 SQL Server가 데이터를 읽을 때 공유 잠금을 설정하지 않도록한다. 

일반적으로 데이터를 읽을 때 다른 트랜잭션이 동시에 그 데이터를 수정하지 못하도록 잠금이 걸린다.

하지만 NOLOCK을 사용하면 이 잠금을 무시하고 데이터를 바로 읽을 수 있다.

 

이렇게하면 뭐가 좋을까, 기다리지 않기 때문에 대량의 데이터를 빠르게 조회할 수 있다.

 

그럼 내가 아직 잘 몰라서, 프로시저를 작성할 때 처음부터 트랜잭션을 시작하고 종료한다는 명령어를 선언해야하는건가? 

-> 아니다. SQL Server는 기본적으로 자동 커밋모드로 동작하지않는다. 

즉, BEGIN TRANSACTION으로 트랜잭션을 시작하지 않으면 각 개별 DML문이 자체적인 소규모 트랜잭션으로 처리되고 즉시 커밋된다.

 

BEGIN TRANSACTION, COMMIT, ROLLBACK 이 명시적으로 사용되지않는다면 트랜잭션의 원자성을 보장받기 어렵다.

이것을 명시적으로 넣지않는다면 각각 DML문들이 독립적인 트랜잭션으로 실행되거나, 프로시저가 호출되는 환경에 따라 다르게 동작할 수 있다.

 

여기서 한번 더 말하자면 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED는

"전체 잠금은 아냐!", 읽기 작업을 실행할 시, 다른 트랜잭션의 잠금을 무시하고 데이터를 읽겠어!"의미다.

데이터를 읽을 때 잠금 대기 시간을 줄여 성능을 높이기 위한 설정인거다.

 

BEGIN TRY는 오류 처리를 위한 코드 블록의 시작을 의미한다.

시작이 있으면 끝이 있듯, BEGIN TRY는 BEGIN TRY의 짝꿍.

 

BEGIN TRY와 짝을 이루는 END TRY 사이에는 코드에서 문제가 발생하면 BEGIN CATCH 블록으로 제어가 넘어간다.

-> 이는 프로그램이 갑자기 멈추는 대신, 발생한 오류를 잡아내서 적절하게 처리할 수 있게 해주는 아주 중요한 기능이다.

 

그리고 이 BEGIN과 END쌍은 SQL문장을 하나의 논리적인 코드 블록으로 묶어주는 역할을 한다.

프로시저, 함수, 트리거의 본문, 그리고 IF나 WHITE같은 제어문 안에서 여러 줄의 코드를 실행하고 싶을 때, BEGIN... END 블록이 필수적이다.

 

- EXEC 명령어는 같은 데이터베이스 내에 있는 저장 프로시저를 호출하여 실행하는 용도로 사용.

 

EXEC 프로시저_이름 @매개변수1_이름 = 값1, @매개변수2_이름 = 값2;

 

명령문 중 SELETE TOP 1 * FROM 테이블 WITH (NOLOCK) ORDER BY 행 DESC은

내림차순으로 조회할건데 

TOP은 SQL Server의 예약어로,

TOP -> 쿼리 결과의 상위 N개 행만 반환하도록 지정하는 명령어이다.

다른 데이터베이스 시스템에서 LIMIT이나 ROWNUM과 유사한 기능이다.

 

TOP 1의 의미는 

TOP: 상위 결과를 가져오겠다는 명령이고

1 : 가져올 행의 개수란 뜻이다.

여기서 주의할 점은 TOP은 반드시 ORDER BY와 함께 사용해야 의미가 있다.

정렬 기준으로 가장 위가 무엇인지 명확해지기 때문이다.

 

IIF는 조건에 따라 두 값 중 하나를 반환하는 함수.

이 코드 IIF(@ID1 > @ID2, @ID1, @ID2)는 다음과 같은 역할을 한다.

• @ID1 > @ID2: 이 부분이 조건입니다. @ID1이 @ID2보다 큰지 확인.
• @ID1: 조건이 **참(True)**일 때 반환될 값.
• @ID2: 조건이 **거짓(False)**일 때 반환될 값.

즉, @ID1과 @ID2 중 더 큰 값을 @ID 변수에 할당하는 명령어.

 

 

왜 IIF를 사용할까?

이 함수는 IF...ELSE 문과 동일한 기능을 하지만, 코드를 한 줄로 간결하게 표현할 수 있다는 장점이 있다.

 

IIF를 사용한 경우:

SET @ID = IIF(@ID1 > @ID2, @ID1, @ID2);

 

 

IF...ELSE로 풀어 쓴 경우:

IF @ID1 > @ID2
BEGIN
    SET @ID = @ID1;
END
ELSE
BEGIN
    SET @ID = @ID2;
END

또 궁금한 건 프로시저 사용할 때만하는건가 아니면 원래 SQL공통적으로 다 사용하는건가

-> IIF는 SQL Server에서 공통적으로 사용할 수 있는 내장 함수.

-> 프로시저에서만 사용하는 것이 아님.

-> 프로시저 외에도 SELECT 문이나 UPDATE 문 등 다양한 SQL 문에서 사용가능.

 

WHILE 1 = 1은 **무한 루프(infinite loop)**를 만드는 SQL 구문.

-> 무한 루프를 사용하는 것은 자칫 위험할 수 있지만, 효율적으로 사용하기 위해서 루프 안에 IF@@ROWCOUNT=0 BREAK와 같은 탈출 조건이 있기 때문이다.

 

또한 알아야할 점이 BEGIN TRY를 사용하면 반드시 END TRY로 짝을 맞춰야한다는 것, 

그리고 BEGIN TRY와 BEGIN은 서로 다른 역할을 하는 명령어이다.

 

BEGIN TRY와 END TRY는 오류 처리 블록을 형성한다. 

BEGIN TRY는 "오류 처리를 위한 코드 블록을 시작하겠다"라는 의미를 가진다.

반면 BEGIN은 단순히 "여러개의 SQL 문장을 하나의 논리적인 덩어리(블록)으로 묶겠다"는 의미로 사용된다.

즉, '오류 처리'라는 특별한 역할이 더해진 명령어이다.

글고 반드시 BEGIN TRY를 사용한다면 반드시 END TRY와 BEGIN ... END CATC를 함께 사용해야한다.

한세트임.

 

BEGIN은 IF나 WHITE같은 제어문, 프로시저, 함수 본문을 정의할 때 여러 문장을 묶기 위해 사용된다.

 

프로시저에서 REPLACE()함수는 주로 동적 SQL을 만들 때 사용된다.

JS에서는 REPLACE()에서는 매개변수 안에 찾을 값과 바꿀 값을 넣어,

문자열에서 특정 부분을 찾아 다른 문자열로 바꿔주는 기능을 한다.

JS에서는 첫번째로 일치되는 항목만 교체한다. 즉, 가장 먼저 발견되는 한 개만 찾아서 바꿔준다.

그리고 프로시저의 REPLACE와 다른 점은 JS의 replace()는 SQL과 마찬가지로 두 개의 인자가 필수이며 세번째 인자는 받지않는다.

 

SQL의 REPLACE()함수와 비교한다면

SQL의 REPLACE()는

->세개의 인자가 필수이다.

-> 기본적으로 문자열 내의 모든 일치 항목을 찾아서 바꿔준다.

-> 주로 SELECT, UPDATE 문 등에서 사용된다.

 

 

그리고,

변수를 선언할 시 NVARCHAR는 SQL Server에서 사용되는 데이터 타입 중 하나로, 가변 길이 유니코드 문자열을 저장할 때 사용해요.

 

프로시저 명령어 중 TRUNCATE TABLE 테이블명

-> 타겟 테이블의 모든 데이터를 삭제하는 명령어이다. 초기화같은 것.

 

삭제하면 DELETE란 것도 있지않나? 둘의 차이점은 뭘까?

 

- TRUNCATE 와 DELETE 의 차이점

데이터베이스에서 테이블의 내용을 지우는 명령어론 TRUNCATE와 DELETE가 있다.

- TRUNCATE

   테이블의 모든 행을 한 번에 빠르게 삭제

   데이터 정의 언어(DDL)에 속하며, 롤백 불가능

 

- DELETE

  행 단위로 데이터를 삭제

  데이터 조작 언어(DML)에 속하며, where 절을 사용해 특정 조건에 맞는 행만 지우기 가능

  각 행의 삭제를 개별적으로 기록하기 때문에 'TRUNCATE' 보다 느리지만, 롤백 가능

 

즉, TRUNCATE는 테이블 전체를 빠르고 효율적으로 비울 때 사용하는 명령어이다.

 

CONVERT() 는 SQL server에서 데이터 타입을 변환 할 때 사용하는 함수,

"무엇을 변환하는지"에 대해서는 CONVERT() 함수 괄호 안에 있는 인자들이 결정한다.

 

- CONVERT(데이터타입, 표현식, [스타일]) 형태로 사용된다.

 

• 데이터타입: 변환하고자 하는 목표 데이터 타입. (예: VARCHAR(10), INT, DATETIME)
• 표현식: 변환할 원본 값 (예: GETDATE(), 숫자, 다른 컬럼)
• [스타일]: (선택 사항) 특히 날짜나 시간을 문자열로 변환할 때, 어떤 형식으로 변환할지 지정하는 숫자 코드. 

CONVERT(VARCHAR(10), DATEADD(MONTH, -1, GETDATE()), 23)

DATEADD함수: 날짜/시간 값에 특정 시간 간격을 더하거나 빼는 함수이다. 

 

 

SET은 변수에 값을 대입할 때 사용합니다. 프로시저 안에서 선언된 지역 변수에 새로운 값을 넣는 역할.

EXEC 는 다른 프로시저를 실행하거나 동적 SQL을 실행할 때 사용. 

 

예외처리 부분에서는,

BEGIN CATCH
	SET	@V_MAINT_VALUE	= ERROR_MESSAGE();
END CATCH;

 

코드에서 ERROR_MESSAGE() 함수는 따로 만들 필요가 없다.

ERROR_MESSAGE()는 SQL Server에서 이미 내장되어 있는 함수이기 때문에.

 

IIF는 간단한 IF...ELSE 논리를 한 줄로 표현하는 SQL Server의 함수.