SQL Server

Row Number를 매기는 방법

SELECT * FROM ( SELECT ROW_NUMBER() OVER(ORDER BY idx) rownum, * FROM page_table ) page_table WHERE rownum BETWEEN 1 AND 20

문자열의 시작 위치 찾기

SELECT CHARINDEX('찾을문자열A','지정문자열B')
SELECT CHARINDEX('찾을문자열A','지정문자열B',숫자C) -- 이건 C 위치에서부터 B에서 A를 찾으라는 뜻

DML Trigger

설명

• Data Manipulation Language (DML) Triggers are special kind of Stored Procedure or an operation that gets executed automatically when a DML operation like INSERT, UPDATE OR DELETE is fired on a Table or View

예시

--Create Demo Database
CREATE DATABASE SqlHintsDMLTriggerDemo
GO
USE SqlHintsDMLTriggerDemo
GO

--Create Customer Table
CREATE TABLE Customer 
( CustomerId INT IDENTITY (1, 1) NOT NULL ,
  FirstName NVARCHAR(50), LastName NVARCHAR(50))
GO

--Create Customer Trigger
CREATE TRIGGER ExampleTrigger
ON Customer
FOR INSERT
AS
BEGIN
     PRINT 'AFTER Trigger ExampleTrigger executed!'
END

Trigger의 종류

1. AFTER Triggers
   • AFTER Triggers are executed after the DML statement completes but before it is committed to the database
   • AFTER Triggers if required can rollback it’s actions and source DML statement which invoked it
   • On a Table with a AFTER trigger, the trigger will be executed once the triggering DML action completes and before it is committed
   • A table can multiple AFTER triggers and for the same trigger action too we can have multiple triggers
   • 예시

     --Create an AFTER Trigger AfterTriggerExample1 for the DML INSERT, UPDATE and DELETE OPERATION on the Customer table by executing the following script
     CREATE TRIGGER AfterTriggerExample1
     ON Customer
     FOR INSERT, UPDATE, DELETE
     AS
     BEGIN
     PRINT 'AFTER Trigger AfterTriggerExample1 executed!'
     END
     

2. INSTEAD OF Triggers
   • INSTEAD OF Triggers are the triggers which gets executed automatically in place of triggering DML (i.e. INSERT, UPDATE and DELETE) action
   • If we are inserting a record and we have a INSTEAD OF trigger for INSERT then instead of INSERT whatever action is defined in the trigger that gets executed
   • A table can have one INSTEAD of Trigger for each Triggering DML actions INSERT, UPDATE and DELETE
   • 예시

     --Create an INSTEAD OF Trigger on the Customer Table for the INSERT DML operation by executing the following script
     CREATE TRIGGER INSTEADOFTriggerExample 
     ON Customer
     INSTEAD OF INSERT
     AS
     BEGIN
      PRINT('Instead of trigger is Executed!')
     END
     

MSSQL Stored procedure의 각종 함수 예시

선언과 초기화

-- 선언
DECLARE @HTL DATETIME

-- 초기화
SET @HTL=GETDATE()

조건문 관련

-- 조건문
IF 조건
    실행문
ELSE
    실행문

-- 실행문이 2개 이상인 조건문
IF 조건
    BEGIN
        실행문 1
        실행문 2
        ...
    END

-- 조건문의 조건
SELECT * FROM [테이블명] WHERE [칼럼명] = [조건] OR 칼럼명 = [조건1] OR 칼럼명 = [조건2] OR 칼럼명 = [조건3] --OR
-- IN을 통해 여러 OR 조건이 있는 sql 문장을 OR와 동일하게 사용 가능하다
SELECT * FROM [테이블명] WHERE [칼럼명] IN =( [조건1],[조건2],[조건3] ) --IN

-- case문(JOIN한 것과 같은 결과)
SELECT A, B, C, (
    CASE
        WHEN B=10
            THEN 'b'
        WHEN B=20
            THEN 'c'
        ELSE
            'd'
    END) AS K
    FROM ALPHA

-- 중첩 case문 예시
SELECT  T1.STORE_ID
        ,T1.STORE_ADDR
        ,CASE WHEN T1.STORE_SIZE >= 100 THEN
                CASE WHEN T2.REGION_GD IN ('S') THEN 'High grade'
                     WHEN T2.REGION_GD IN ('A','B') THEN 'Mid Grade'
                     ELSE 'Low Grade'
                END
              WHEN T1.STORE_SIZE >= 50 THEN
                CASE WHEN T2.REGION_GD IN ('S', 'A') THEN 'High Grade'
                     WHEN T2.REGION_GD IN ('B') THEN 'Mid Grade'
                     ELSE 'Low Grade'
                END
             ELSE
                CASE WHEN T2.REGION_GD IN ('S', 'A', 'B') THEN 'High Grade'
                     ELSE 'Low Grade'
                END
        END STORE_SIZE_GD
FROM    SQL_TEST.MA_STORE T1
        ,SQL_TEST.CD_REGION T2
WHERE   T1.REGION_CD = T2.REGION_CD

-- DECODE
-- 조건에 따라 데이터를 다른 값이나 컬럼값으로 추출 할 수 있다
-- DECODE(VALUE, IF1, THEN1, IF2, THEN2...) 형태로 사용 할 수 있다
-- VALUE 값이 IF1일 경우에 THEN1 값을 반환하고, VALUE 값이 IF2일 경우에는 THEN2 값을 반환한다
-- DECODE 함수 안에 DECODE함수를 중첩으로 사용 할 수 있다
SELECT deptno, DECODE(deptno, 10 , 'ACCOUNTING' ,
                              20 , 'RESEARCH' ,
                              30 , 'SALES', 'OPERATIONS') name
FROM dept;

-- 결과
DEPTNO   NAME
------    ----------
    10    ACCOUNTING
    20    RESEARCH
    30    SALES
    40    OPERATIONS

반복문

-- 반복문
WHILE @A < 100
    BEGIN
        실행문
    END

문자열 치환

-- 문자열 치환
-- REPLACE
-- REPLACE(대상, 바꿀 문자, 바뀔 문자);
SELECT REPLACE('abc123abc', 'abc', '321');
--> 321123321

-- STUFF
-- STUFF(대상, 치환 시작 위치, 치환 문자 수, 바뀔 문자);
SELECT STUFF('abcdef', 2, 3, 'XXX');
--> aXXXef

JSON 객체 불러온 후, 해당 value의 값에 따라 다른 값 출력하기(UPDATE의 SET 안에서 쓸 때 유용함)

((SELECT CASE WHEN EXISTS (SELECT 1 FROM (SELECT * FROM OPENJSON(JSON_QUERY(dpd.dpc_led, '$.ledctrl_error'))
    WITH (signal1 NVARCHAR(MAX) '$.info.signal1', signal2 NVARCHAR(MAX) '$.info.signal2') 
    WHERE (
        (signal1 = 'ERROR' OR signal2 = 'ERROR')
        )) AS SIG_WARN) THEN 1 ELSE 0 END)
        +
        (SELECT CASE WHEN EXISTS (SELECT 1 FROM (SELECT * FROM OPENJSON(JSON_QUERY(dpd.dpc_led, '$.ledctrl_error'))
    WITH (signal1 NVARCHAR(MAX) '$.info.signal1', signal2 NVARCHAR(MAX) '$.info.signal2') 
    WHERE (
        (signal1 = 'ERROR' AND signal2 = 'ERROR') 
        OR (signal1 = 'NONE' AND signal2 = 'ERROR') 
        OR (signal1 = 'ERROR' AND signal2 = 'NONE')
        )) as SIG_ERR) THEN 1 ELSE 0 END))

ISNULL 함수

-- ISNULL(칼럼,칼럼이 NULL일경우 대체할 값)
ISNULL(Column,0)
(ISNULL(@PARAMETER,'')='')

트랜잭션 레벨 확인

SELECT CASE transaction_isolation_level 
WHEN 0 THEN 'Unspecified' 
WHEN 1 THEN 'ReadUncommitted' 
WHEN 2 THEN 'ReadCommitted' 
WHEN 3 THEN 'Repeatable' 
WHEN 4 THEN 'Serializable' 
WHEN 5 THEN 'Snapshot' END AS TRANSACTION_ISOLATION_LEVEL 
FROM sys.dm_exec_sessions 
where session_id = @@SPID

참조 링크

문자열의 시작 위치 찾기

Procedure 간단한 명령어들

CASE…WHEN…THEN, DECODE

DML Triggers

NULL값 함수 체크