CASE문으로 동적쿼리를 없애자 - UI입력 패턴에 따른 사용법 T-SQL



개발자가 쿼리문은 잘 다뤄야 한다는것은 두말하면 잔소리 입니다 !!!
개발에 입문 하거나 중급 이하의 개발자들의 가장 큰 실수 중 하나가 집합연산을 코딩으로 루프를 돌려가며 작성하는것에 익숙해 있다는 것입니다.
물론 쿼리로 할 수 없는 연산은 직접 루프 돌려가며 해야 되겠죠.
문제는 배운게 도둑질이라고 개발을 시작하면서 먼저 익힌게 루프를 돌려 로직을 짜는 습관이 들어서 집합연산의 편리함을 모른다는 것입니다. 집합 연산에 익숙하면 개발시간을 50%이상 단축 시킬 수 있습니다.(믿거나 말거나)

아래 내용을 이해하시고 기본적인 쿼리문 작성을 쉽게 하시려면
필자가 배포하는 UXSql 솔루션을 다운로드 받아 설치 후 프로젝트를 하나 만들고 쿼리디자이너 파일을 만들면 쿼리를 쉽게 작성 할 수 있습니다. UXSql솔루션의 쿼리작성기 이니 참고 바랍니다. 사용법은 설치후 실행하면 메뉴얼 참고!

아래에 설명하는 내용들을 열심히들 자신의 것으로 소화시켜두시고 항상 응용하시기 바랍니다.
TSQL을 기반으로 설명합니다.



* 대부분 동적쿼리를 사용하는 경우 
- WHERE절 조건에 따른 조회의 경우라는 것은 쿼리를 좀 작성 해보신 분들은 잘 아실것입니다.

예) str = 'select * from where '
     if 조건
        str = str + 'a=' + var
     else 
        str = str + 'b=' + var
대략 이런식이겠죠

그나마 동적쿼리를 안쓰는 경우는 ( TSQL의 경우만 )
IF 조건
   SELECT ...
   WHERE a = 'a'
ELSE
   SELECT ...
   WHERE b = 'a'
이런 식으로 동일한 select문이 반복되게 작성을 하게 돼죠


* 이제부터 아래 내용을 보시고 위같이 작성들 하지 마시길 

- 먼저 임시테이블을 만듭니다. ( 물리적인 테이블을 만들어도 상관 없습니다 )
DECLARE @UserTable TABLE( USER_KEY INT, USER_ID NVARCHAR(50), USER_NAME NVARCHAR(50), USER_TYPE INT)
INSERT INTO  @UserTable SELECT 1,'GAIA','가이아',0
INSERT INTO  @UserTable SELECT 2,'GILDONG','홍길동',1
INSERT INTO  @UserTable SELECT 3,'DOLAI','바람돌이',2
INSERT INTO  @UserTable SELECT 4,'HANBAL','한마리',2

1. 단순 조건 : UI의 필드값이 있을 때만 조회 해야 되는경우
( UI필드와 DB필드가 1:1 )

-사용자명과 사용자 타입을 UI에서 입력 파라미터로 넘어왔다고 가정

DECLARE @USER_NAME VARCHAR(50),@USER_TYPE INT 
SET @USER_NAME = '가이'
SET @USER_TYPE = 1

- 다음과 같이 좌측 CASE문 = 우측 CASE문을 이용 합니다.
SELECT * FROM  @UserTable
WHERE  CASE WHEN @USER_NAME !='' THEN USER_NAME ELSE '' END
              LIKE CASE WHEN @USER_NAME !='' THEN @USER_NAME + '%' ELSE '' END
AND CASE WHEN @USER_TYPE != -1 THEN USER_TYPE ELSE 1 END
        = CASE WHEN @USER_TYPE != -1 THEN @USER_TYPE ELSE 1 END

-- 설명 : 좌변 CASE ~ END = 우변 CASE ~ END
-- @USER_NAME !='' 이면 USER_NAME LIKE @USER_NAME + '%' 이되고
-- @USER_NAME ='' 이면  '' LIKE ''  이 됨
-- @USER_TYPE관련 두번째 조건은 척~ 보면 아실듯. 이렇게 계속 붙여가기만 하면 됩니다.
-- 조건절이 길어져서 복잡하다고 보시면 안되고 한라인 한라인이 하나의 조건을 처리한다 라고 생각하면 됩니다.
-- 단순히 USER_NAME  = CASE~END문으로 하면 되지 않느냐? 라고 하시는 분들도 있겠지만 이 경우는 UI에서 필수 검색 필드 일때에만 해당합니다.
--USER_NAME  = CASE WHEN @USER_NAME !='' THEN @USER_NAME ELSE USER_NAME END  이렇게 하면 되지않느냐?
--라고 하실 수도 있지만 이렇게 하면 USER_NAME이 NULL인 경우 검색이 안됩니다.(인덱스타는 문제도 있을듯)
--> 이것만해도 동적쿼리의 대부분은 없앨 수 있겠죠?
 

2. 응용조건 1 : UI입력에서 필드 선택에 따라  조건에서  DB필드가 달라지는경우

UI 입력필에 구분 combo박스 하나와 텍스트 박스 하나가가 있어 검색 할 필드를 선택하여 검색하는 경우
DECLARE @FieldType INT, @FieldValue VARCHAR(50)   --입력파라미터로 넘어왔다고 가정
SET @FieldType = 1 -- 0: 아이디검색, 1: 이름검색
SET @FieldValue = '가이'

SELECT * FROM @UserTable
WHERE  CASE WHEN @FieldType = 0 THEN USER_ID ELSE  '' END
             LIKE CASE WHEN @FieldType = 0 THEN @FieldValue + '%' ELSE  '' END
AND CASE WHEN @FieldType = 1 THEN USER_NAME ELSE  '' END
    LIKE CASE WHEN @FieldType = 1 THEN @FieldValue + '%' ELSE  '' END
--> 이런식으로 콤보박스에 나열된 숫자만큼  붙이면 게임 끝~~


3. 응용조건 2 :  UI입력에서 체크박스가 여러개 있는경우 AND 조건으로 처리를 요하는경우( 일반적인경우)

--이경우는 보통 카테고리성 필드를 조회 할 때 이용 (@UserTable의 USER_TYPE)
--@UserTable의 USER_TYPE : 0-관리자 , 1 - 운영자, 2-일반사용자 라고 할 때
--UI구성에서 3개의 카테고리가 체크박스로 선택 할 수 있게 구성되어 있을 때
--> 보통 고객의 요구사항은 AND로 검색되게 해 달라 라고 함, 이때의 쿼어리~~
--각각 체크되었는지 유무가 입력 파라미터로 넘어온다고 가정
DECLARE @USER_TYPE_ADMIN BIT,@USER_TYPE_OPERATOR BIT,@USER_TYPE_USER BIT
SET @USER_TYPE_ADMIN = 1
SET @USER_TYPE_OPERATOR = 1
SET @USER_TYPE_USER = 0

SELECT * FROM @UserTable
WHERE 1=1
 AND CASE WHEN @USER_TYPE_ADMIN = 1 THEN USER_TYPE ELSE 0 END =  CASE WHEN @USER_TYPE_ADMIN = 1 THEN 0 ELSE 0 END 
 --AND CASE WHEN @USER_TYPE_OPERATOR = 1 THEN USER_TYPE ELSE 0 END =  CASE WHEN @USER_TYPE_OPERATOR = 1 THEN 1 ELSE 0 END 
-- 현재는 관리자만 조회하라는 것이지만 주석을 풀 경우 관리자이면서 운영자인 사람을 조회하라는 것이 되겠죠?
-- 보통 유저 하나에 권한이 여러개 되게 테이블을 쪼개야 맞는구조이지만 여기는 테스트인지라~ 알아서들 해석하시길

4. 응용조건 3 : 응용조건 2에서 OR조건으로 처리를 요하는경우

-- 고객이 관리자와 운영자 모두 조회되게 해 달라고 할때.
--> 응용조건 2에서 OR로만 바꾸면 끝

5. 응용조건 4 : 응용조건 2,3 이 혼합되어야 하는경우
--> 극히 드물긴 한데 이런것도 요구하는 곳이 있음. 딱 한곳 있었음. 적절히 AND와 OR을 섞으면 겜오바 


[ 여기에서 부터는 예전에 작성했던 글이라 중복된 부분도 있습니다만 알아서들 필터링 해서 보시길~~ ]

 프로시져 내에서 쿼리를 작성하다보면 넘어온 입력 파라미터 때문에 동적쿼리를 작성하는 경우가 많은데|
예를 들면 IF @param = '1' @qry = @qry + 'field =' +@field 뭐 이런식으로...
이 글에서는 Case문을 이용하여 동적쿼리를 사용하지 않는 나름 제가 작성하는 방법을 적어봅니다. 이런식으로 사용하시는 분들은 참고만~~

간단히 다음과 같은 임시테이블을 기준으로 설명을해 갑니다.
declare @tblTest Table(intField INT, varField varchar(100))
insert into @tblTest values (1,'동해물과')
insert into @tblTest values (2,'백두산이')
insert into @tblTest values (3,'마르고')
insert into @tblTest values (4,'닳도록')
insert into @tblTest values (5,'UFO?가')
insert into @tblTest values (6,'보우하사')

보통 조건절에서 Case문을 사용하는 경우는
where Field1 = Case @input = '' Then Field1 Else '값' End 와 같은 식으로 우변에만 사용하는 경우가 많은데 이렇게 되면 예와 같이 @input이 넘어오지 않은 경우에 Field1을 우변에도 써줘야 하는 경우가 생기지요. 우변이 Nullable하지 않은경우는 별문제 없는데 Nullable한 경우에 NULL값에 대해서는 select를 못하더군요.
그래서 조금더 응용하여 좌변까지 적용 할 수 있는 방법을 소개합니다.

조건절에서 사용되는 여러가지 경우별로 나누어 보면
1. 값에 해당하는 레코드를 찾는 경우 ( = 비교연산 )
Case When @key is null Then -1 else intField end = Case When @key is null Then -1 else @key end
중간에 = 을 기준으로 좌,우가 나눠지고
@Key값이 null이 아니면(값이 넘어온경우) 왼쪽은 위테이블의 intFiled가 오른쪽은 넘어온값(@Key)으로 되는 intField = @key 가되고
값이 null이면(값이 안넘어온경우)는 -1 = -1 이 되겠죠

Declare @key INT
SET @key = 4
Select * from @tblTest
Where case when @key  is null  then -1 else intField end = case when @key  is null  then -1 else @key end
 
 != 연산자의 경우는 좌변 NULL대신 1 우변 NULL대신 2를 넣어주면 1 != 2 가되어 문제없겠죠? (아무값이나 서로 다른것을 넣어시면 됩니다)
문자열의 경우도 간단하니깐 해보세요?

2. 범위에 해당하는 레코드를 찾는경우(>,< 연산자의 경우)
 Declare @From INT,@To INT
SET @From = 4
SET @To = 6
Select * from @tblTest
Where Case When @From Is Null Then  1  else intField end >=Case When @From Is Null Then 0 else @From end
    And Case When @To Is Null Then -1 else intField end <= Case When @To Is Null Then -1 else @To end
이렇게 두번 사용하면 됩니다.. 간단하죠?
위에 null값을 조건에 맞게 아무값이나 넣어주면 됩니다.

3. 여러 구분자값이 넘어왔을때 ( IN 연산자 )를 처리하는 부분...(요놈이 쪼매 처리할 부분이 있습니다.)
  이 경우 넘어온 구분값을 파싱해서 Table로 만들어야죠...(네이버 같은데 찾아보면 많으니깐 찾아보세용, 제글중에도 어디보면 있어요)
여기서는 @tblTest이 또한 코드 값들을 파싱하여 넣었다고 가정합니다.
Declare @key INT
SET @key = 4
Select * from @tblTest where  Case When @key is null Then -1 else intField end  IN ( case @key when null then null else (select intField from @tblTest)  end )
이런 쿼리가 될거 같지요? 하지만 안된답니다.
실행해보면 "하위 쿼리 앞에 =, !=, <, <=, >, >= 등이 오거나 하위 쿼리가 하나의 식으로 사용된 경우에는 여러 값을 반환할 수 없습니다." 요런 에러를 내뱉는데
한마디로 Case문에서 값이와야할 부분에 식이왔다는 거죠
그래서 다음과 같이 IN절에서 Select가 먼저오게 바꾸면

Select * from @tblTest
where  Case When @key is null Then -1 else intField end  IN ( select case @key when is null then null else intField  end from  @tblTest  )
이런 쿼리가 만들어지죠~~~ 자.. 왼쪽절은 알테고 오른쪽절은 @Key값이 안넘어오면 Null을 Select해버리면 된다는 것이지요. 물론 중복되어 null만 Select될텐데 이럴떄 쓰는 좋은 키워드 있죠 --> distinct
참고로 Distinct를 너무 남발하시는 분들도 꽤나 있던데.... Distinct해야될 데이타가 많으면 엄청 오래걸리니깐 잘 사용 하세요...
추가로 @tblTest 테이블에는 넘어온 분류 코드만 들어있으므로 @Key는 Select Count(*) from @tblTest 로 건수가 되겠죠...
세세한 테스트를 본인이 직접 해봐야 자신의 것이 됩니다. 


4. LIKE 연산자 처리부분
Declare @Value varchar(50)
Set @Value = '물과'
Select * from @tblTest
where  Case When @Value is null Then '' else varField end LIKE Case When varField is null Then '' else '%' + @Value + '%' end
위의 내용을 이해했다면 like는 껌이죠



5. 마지막으로 정렬 필드는 요렇게
DECLARE @OrderBy varchar(10)
SET @OrderBy = 'LastName'
...
ORDER BY (CASE @OrderBy WHEN 'LastName' THEN LastName WHEN 'Title' THEN Title END) ASC


좀더 일반적인 응용 - 필드별로 ASC, DESC도 적용되게
DECLARE @OrderBy varchar(10). @Sort varchar(10)
SET @OrderBy = 'LastName'
SET @Sort = 'asc'
...
ORDER BY (CASE WHEN  @OrderBy = 'LastName' AND @Sort ='asc'   THEN LastName ELSE null END) ASC
                , (CASE WHEN  @OrderBy = 'LastName' AND @Sort ='desc'   THEN LastName ELSE null END) DESC

이렇게 하면 LastName 필드에 asc, desc 까지 적용됩니다^^


위 5가지 경우만으로도 동적쿼리로 작성할 부분을 엄청나게 줄일 수 있죠..
동적쿼리를 아예 없앨 수 있답니다.

참고)[ WHERE문의 경우 CASE문 대신 다음과 같은 조건식을 이용할 수도 있다.] 

@KEYWORD = '' OR COMMENT LIKE '%' + @KEYWORD  +  '%'

- 단, 이경우는 입력파라미터와 DB필드명이 1:1로 매칭되는 경우에 한정된다.
- 다음과 같은 경우 대처하기가 어렵다. 
- 키워드를 하나 받아 여러 필드들을 동시에 조건을 걸어야 하는 경우

WHERE

AND ( @KEYWORD = '' OR TITLE LIKE '%' + @KEYWORD  +  '%' )

AND ( @KEYWORD = '' OR NAME LIKE '%' + @KEYWORD  +  '%' )


이렇게 되면 키워드 값이 존재하면? TITLE과 NAME둘다 존재하는 경우를 검색하게 되어 아무것도 조회되지 않는다.


참고) 실행계획의 문제점이 발생하므로 기본키는 어지간하면 필수값으로 넘어가게 하는게 좋겠죠? ㅎㅎ

index seek를 않고 index풀스켄을 한다는 단점이 존재.
http://zzizim.egloos.com/2908918

[인덱스 seek를 안타서 case문 쓰면 안되겠네?]

두려워 말라~  

대용량 시스템에서 보통 조회는 어떠한가?

돼~한민국 에서는 초창기 개발에서 검색필드 평균 5개 이상씩이다. 

다시 말해 조회 할 수 있는 모든 필드에 대해서는 조회 되게 해 달라고 한다.

이말은 무슨말? 실재 인덱스 seek를 탈 확률은 동적쿼리로 짜더라도 그리 많지 않다는 것이다.

유저가 인덱스seek를 타게 조회하는 방법을 알리도 만무하다.

어차피 대용량 시스템에서는 index seek를 타게 조회 기간을 제한하던지 다른 광구책을 고려해야만 한다는 것이다.


결론적으로 필자는 CASE문 적극 사용하길 권장한다.


[ 대용량 DB에서의 대안... ]

조회조건에 필수값을 가지게 하자. (예, 기간을 두고 기간내에서 데이타가 조회되게 하자) 

즉,  충분히 작은 Set이 index seek를 타게 필수값으로 정하여 조회되게하자.



언젠가는 SQLServer 구문분석기가 case구문에 대해 index seek를 타야 되는것을 인식할때가 오겠쥐? 안올까? ㅎㅎ




여기부터는 추가적으로 필자가 작성한 예들....입니다. 참고들만 하세요


[ 기준기간 from~to, 비교기간 from~to 에서 비교기간이 있을 수도 있고 없을 수도 있는경우... ]

-- 여러 경우를 다 해봤지만 단순히 아래같이 작성하면 성능도 나오고 깔끔합니다.

WHERE 

( (SA.STAT_DATE >= @BASE_FROM AND SA.STAT_DATE <=  @BASE_TO)

 OR  (SA.STAT_DATE >= @COMPARE_FROM AND SA.STAT_DATE <=  @COMPARE_TO)

)



핑백

  • 객체지향 디자인패턴으로 비지니스를 설계하자 : 5. 목록 UI노드 설명 2016-02-05 14:27:08 #

    ... SELECT문장을 만든 후 노드 상단의 "UI설정생성"버튼을 클릭하면 속성창의 검색필드와 조회필드들이 생성됩니다. WHERE문의 CASE문에 대한 이해가 필요하면 여기를 참고하세요 2. 검색필드들 - 검색UI설정 실행 시 상단에 위치할 검색 UI 입력항목들을 설정합니다. 쿼리의 where조건들에 해당하는 항목들에 해당하는 설정을 ... more

  • Business into software : 쿼리 시작부터 끝까지~ 2017-03-02 14:52:27 #

    ... 1:N의 데이타를 컬럼으로 쉽게 데이타를 가공할 수 있음!!! ( 행을 컬럼으로 변환하는 Case문의 응용에 해당 ) *** Case문에 대한 더 많은 응용은 여기를 참고! *** 행열 변환에 관련된 더 상세한 내용은 여기를 참고 ! ( TSQL 예이지만 MySql에서도 거의 작동하는 쿼리문들임 ) 5.2.2 회원정보 접속 ... more



구글광고