[MS-SQL] 헷갈리는 SQL 문법 정리 / CURSOR,IF EXISTS,IF EXISTS,STUFF,MERGE,WITH,PIVOT,트랜잭션,저장프로시저

CURSOR 문

데이터 작업을 할 때 테이블에서 집합 단위가 아닌 행 단위로 처리를 해야할 때

--커서 사용 선언 
DECLARE 커서명 CURSOR FOR  --DECLARE: 커서를 정의,관련된 선언을 하는 명령

--커서 사용을 위한 오픈 
OPEN 커서명   --OPEN : 커서가 질의 결과의 첫 번째 튜플을 포인트 하도록 설정하는 명령
FETCH NEXT FROM 커서명 INTO @변수 --FETCH : 질의 결과의 튜플들 중 현재의 다음 튜플로 커서를 이동시키는 명령
--커서를 이용하여 한행씩 실행
WHILE @@FETCH_STATUS = 0 
BEGIN 
--반복 작업 수행
FETCH NEXT FROM 커서명 INTO @변수 --다음 ROW로 이동
END
CLOSE 커서명
DEALLOCATE 커서명

refernce

• SQL 관련 블로그 https://datalibrary.tistory.com/129?category=975443
• [MS-SQL] Mssql Cursor(커서) 사용하기 2 - (mssql 커서(cursor) 응용) (tistory.com)

---

IF EXISTS문

IF EXISTS | IF NOT EXISTS(SELECT문) ** –조건 결과 값이 있을 때 | 없을 때** BEGIN '실행할 쿼리' END 테이블이 존재하면 실행되는 조건문

---

STUFF 문

문자열을 자리수 만큼 특정문자로 치환하고 문자열로 리턴 STUFF
(문자열, 시작위치, 길이, 치환할 문자)
XML로 리턴된 결과의 첫번재 문자 ','을 빈문자로 치환하고 문자열로 변환

SET @이름 = SELECT DISTINCT STUFF((SELECT ',' +  A.ENAME 
     								FROM (SELECT VAL  		 												FROM 테이블 A) A      
                                     FOR XML PATH('') ),1,1,'')AS Name
		)

*FOR XML PATH:쿼리의 결과 데이터를 XML 형태로 표현 FOR XML PATH
('row element명')

---

MERGE 문

MERGE INTO [TABLE / VIEW] - 테이블의 조건에 따라 update 또는 insert할 테이블 혹은 뷰
USING [TABLE / VIEW / DUAL] - 비교할 대상 테이블 혹은 뷰 (위 테이블과 동일할 경우 DUAL을 사용)
ON [조건] - UPDATE 와 INSERT 처리할 조건문 (조건이 일치하면 UPDATE / 불일치 시 INSERT)
WHEN MATCHED THEN
UPDATE SET
[COLUMN1] = [VALUE1],
[COLUMN2] = [VALUE2],
...
(DELETE [TABLE] WHERE [COLUMN 1] = [VALUE 1] AND ...) - UPDATE 뿐만 아니라 DELETE 구문도 사용 가능
WHEN NOT MATCHED THEN
INSERT (COLUMN1, COLUMN2, ...)
VALUES (VALUE1, VALUE2, ...)

reference

• https://mine-it-record.tistory.com/261 [나만의 기록들:티스토리]
• https://gent.tistory.com/371

---

WITH문

WITH 변수명 AS(임시 테이블에 사용될 쿼리문 -SELECT 문)

WITH(NOLOCK):테이블이 잠겨있어도 선행작업의 결과와 관계없이 바로 SELECT문 수행하여 결과 반환 , 프로시저 내에서 사용되는 SELECT 문에서 WITH (NOLOCK)을 사용하기 위해서는 각 문장마다 삽입할 필요없이 프로시저 시작 부분에 다음 문장을 추가해 주면 된다. SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

refernce

• https://roadrunner.tistory.com/238 [삶의 조각들:티스토리]

---

PIVOT 문

PIVOT: 행을 열로 변환해주는 함수, 통계 추출에 사용

SELECT *
FROM ( 피벗할 쿼리문 ) AS result
PIVOT ( 그룹합수(집계컬럼) FOR 피벗대상컬럼 IN ([피벗컬럼값] ... ) AS pivot_result

---

Transaction 트랜잭션

하나의 논리적 작업 단위로 수행되는 일련의 작업

트랜잭션 성공-> 작업 결과가 커밋되고 DB 영구적 반영 COMMIT TRAN
트랜잭션 실패-> 오류가 발생하여 일련의 작업 내역 모두 취소 ROLLBAXK TRAN

BEGIN TRAN
 	실행할 쿼리 

	IF(@@Error <> 0) GOTO _QuitTran
									
COMMIT TRAN    						
GOTO _SaveTran

_QuitTran:
IF @@TRANCOUNT > 0 
BEGIN
	ROLLBACK TRAN   
END
SET @RCD = @@TRANCOUNT
RETURN

_SaveTran:
SET @RCD = @@TRANCOUNT
RETURN

ALTER PROCEDURE [dbo].[SP_BPS_TODO_REG]
@FLAG			 VARCHAR(50)        = '',
@SEQ_NO          INT    = 0,
@CONTENTS        VARCHAR(200)       = '', 
@RCD	  		 INT OUTPUT


AS
SET NOCOUNT ON   
    BEGIN TRAN
       
        IF(@FLAG = 'Reg')
        BEGIN
            INSERT INTO TODO_LIST
                (
                    CONTENTS,
                    WRITE_DATE
                 )
                 VALUES
                 (
                    @CONTENTS,
                    GETDATE()
                 )
                IF(@@Error <> 0) GOTO _QuitTran

        END
        ELSE IF(@FLAG = 'Mod')
        BEGIN  
            UPDATE TODO_LIST
               SET WRITE_DATE = GETDATE(),
                   CONTENTS = @CONTENTS                 
             WHERE SEQ_NO = @SEQ_NO

             IF(@@Error <> 0) GOTO _QuitTran
        END

              
    COMMIT TRAN
    GOTO _SaveTran

    _QuitTran:
    IF @@TRANCOUNT > 0 
    BEGIN
	    ROLLBACK TRAN
    END
    SET @RCD = @@TRANCOUNT
    RETURN

    _SaveTran:
    SET @RCD = @@TRANCOUNT
    RETURN

SET NOCOUNT OFF

• @@TRANCOUNT 활성 트랜잭션이 있는 것 (진행 중인 것)
  BEGIN TRANSACTION 문은 @@TRANCOUNT를 1씩 늘립니다. @@TRANCOUNT에 영향을 주지 않는 ROLLBACK TRANSACTION savepoint_name을 제외한 ROLLBACK TRANSACTION은 @@TRANCOUNT를 0으로 줄입니다. COMMIT TRANSACTION 또는 COMMIT WORK는 @@TRANCOUNT를 1씩 줄입니다.
• SAVE TRAN 은 트랜잭션 내 저장점을 생성
• 저장점이란 트랜잭션의 일부가 조건에 따라 취소될 경우 트랜잭션이 되돌아 갈 수 있는 위치

Stored Procedure 저장 프로시저

CREATE PROCEDURE 프로시저명
AS
BEGIN
SET NOCOUNT ON
SET NOCOUNT OFF
END