참조: where구에 case문을 써 보자

지금까지는 oracle 독자 함수인 decode를 많이 사용했는데

이제는 표준 sql문에 case문이 있으니

나도 서서히 case문을 사용하는 편으로 바꿔야겠다.

아래의 두 가지 사용방법이 있다.

▶ if-else 와 같은 사용법

SELECT

CASE
WHEN ROUND_METHOD <= 0 THEN FLOOR(c_data.BASE_AMOUNT * RATE)
WHEN ROUND_METHOD >= 2 THEN CEIL(c_data.BASE_AMOUNT * RATE)
WHEN ROUND_METHOD > 0 THEN ROUND(c_data.BASE_AMOUNT * RATE, ROUND_DIGIT)
END
INTO l_UNEMPLOYMENT_INSURANCE
FROM ACCOUNT_M_002
WHERE PERIOD_VALIDITY_FROM <= SYSDATE
AND PERIOD_VALIDITY_TO >= SYSDATE
;

▶ switch 와 같은 사용법

SELECT

CASE ROUND_METHOD
WHEN 0 THEN FLOOR(c_data.BASE_AMOUNT * RATE)
WHEN 1 THEN CEIL(c_data.BASE_AMOUNT * RATE)
WHEN 2 THEN ROUND(c_data.BASE_AMOUNT * RATE, ROUND_DIGIT)
END
INTO l_UNEMPLOYMENT_INSURANCE
FROM ACCOUNT_M_002
WHERE PERIOD_VALIDITY_FROM <= SYSDATE
AND PERIOD_VALIDITY_TO >= SYSDATE
;

▶ 메모

1. else는 생략 가능하지만, 생략되었다면 NULL을 리턴한다.

2. microsoft ACCESS에서는 지원하지 않는다.

3. switch 와 같은 사용법은 if-else 와 같은 사용법의 생략 스타일이라고 볼수 있다.

그러므로 아마도 if-else 와 같은 사용법만 알아두면 될 것 같다.

4. when 조건평가에 있어서, 참이 되는 when이 발견되면 그 밑에 더 정확한 when이 있다고 하더라도

더 이상 처리를 하지 않기에 이 점을 의식하여 case문이 작성할 필요가 있다.

5. performance에 관한 얘기

http://blog.naver.com/json2811/90080666948

CASE 컬럼명 | 표현식 WHEN 조건식1 THEN 결과1
WHEN 조건식2 THEN 결과2
.......
WHEN 조건식n THEN 결과n
ELSE 결과
END

조건문과 조건문 사이에는 콤마(,) 를 사용하지 않는다.
CASE 문은 반드시 END 로 끝내야 한다.
CASE 표현식은 ANSI SQL 형식도 지원한다.
결과 부분은 NULL 을 사용해서는 안된다.

---

ex>

하나의 컬럼으로 여러가지 값을 비교하고 싶다면

SELECT CASE 'ABC' WHEN 'ABC' THEN 'OK_STRING!!'

WHEN 'BBC' THEN 'NO_STRING!!'

ELSE 'NO_STRING!!'

END AS 별명별명
FROM dual

여러조건을 비교하고 싶다면

SELECT CASE WHEN 'A'<'B' THEN 'B WIN!!'

WHEN 'C'<'D' THEN 'D_WIN!!'

ELSE 'NO_STRING!!'

END AS 별명별명

FROM dual

이런식으로 사용하면 된다.

비교할때에 소문자하고 대문자하고 비교하면 비교('a'와 'A'는 다르다.)

한마디로 대소문자 비교한다는 말이다.

근데 'a'>'A'이다.. 소문자가 더 크게나온다.

문자열이 어케 되는지는 모르겠다..

보통 코딩을 할 경우 조건문을 사용 해서 처리하는 부분들이 많이있다.

Oracle에서는 Case 구문을 사용하는데

Case when 조건 then 조건문에 맞을 경우 수행 when 조건 then 수행 else 아닐경우 end as 명칭

이렇게 사용한다.

예를 들면 다음과 같다.

SELECT CODE_TYPE, CODE, CODE_NAME, DESCRIPTION, CREATEDATE, MODIFYDATE,
CASE WHEN PARENT_SEQ = '142' AND SUBSTR(CODE,0,1) = 'F' THEN '1'
WHEN PARENT_SEQ = '142' AND SUBSTR(CODE,0,1) = 'H' THEN '2'
ELSE PARENT_SEQ
END AS PARENT_SEQ
, SEQ
FROM ORABPEL.CODE_T

위의 구문을 해석해보면 parent_seq 가 142이면서 code의 첫번째 값이 F 인경우 1을 출력,

parent_seq가 142이면서 code의 첫번째 값이 H인경우 2를 출력,

이도 저도 아닐경우 그대로 출력

출력한 값은 parent_seq라 명칭을 붙인다.

라고 해석 할 수 있다.

[오라클] SQL - UNION에 관한 SQL 규칙

Head First SQL 책에 있는 UNION에 관한 내용을 보다가 이 내용을 참고로 다시 정리해 봅니다.

1. UNION에서 ORDER BY 사용

- UNION에서 ORDER BY가 여러 번 나오면 해석하지 못한다는 에러가 발생한다.

- UNION은 문장의 끝에 한 개의 ORDER BY만을 받아 들일 수 있다.

이유는 UNION이 여러 SELECT문의 결과를 합친 후 결과를 만들기 때문이다.

예제 1) 두 select문에 order by 가 모두 있을 경우 에러가 발생한다.

select 10 as a1, 20 as a2 from dual order by a1 union select 30 as a3, 40 as a4 from dual order by a1

예제 2) 두 select문에 order by 가 마지막 select 문에만 있어야 한다.

select 10 as a1, 20 as a2 from dual union select 30 as a3, 40 as a4 from dual order by a1

- ORDER BY 절에 컬럼명은 첫번째 질의(select문)의 컬럼명만 사용이 가능하다.

예제 3) 이런 경우는 두번째 select문의 컬럼명을 사용했기 때문에 에러가 발생한다.

select 10 as a1, 20 as a2 from dual union select 30 as a3, 40 as a4 from dual order by a3

예제 4) 첫번째 select문의 컬럼명을 사용하였기에 정상 처리

select 10 as a1, 20 as a2 from dual union select 30 as a3, 40 as a4 from dual order by a1

2. 각 SELECT문의 열(컬럼) 수는 같아야 한다.

- 첫번째 SELECT문에서 두 열을 선택하고 두번째에서는 하나의 열을 선택 할 수 없다.

예제) 위 내용처럼 각 SELECT문의 열의 수가 다를 경우 에러가 발생한다.

select 10 as a1, 20 as a2 from dual union select 10 as a3 from dual

- 컬럼 수가 일치하지 않아 에러가 발생할 경우 강제로 일치 시키는 방법은 상수값 또는 NULL을 사용한다.

select 10 as a1, 20 as a2 from dual union select 10 as a3, NULL from dual

3. 각 SELECT문에 표현식과 집계 함수도 같아야 한다.

4. SELECT문의 순서는 중요하지 않다. 순서는 결과에 영향을 미치지 않는다

보기 1) select 10 as a1, 20 as a2 from dual union select 30 as a1, 40 as a2 from dual

보기 2) select 30 as a1, 40 as a2 from dual union select 10 as a1, 20 as a2 from dual

보기1)과 보기2)는 SELECT문의 순서가 달라도 결과는 같다는 뜻이다.

5. UNION의 결과에서 중복값은 하나로 나오는 것이 디폴트이다.

중복값이 모두 나오게 하려면 UNION ALL를 쓰면 된다.

6. 열의 데이터 타입은 같거나 서로 변환 가능한 값이어야 한다.

여기서 좀더 생각할 것은 데이터 타입만 같으며 되며 데이터 사이즈까지 같을 필요는 없다는 것이다.

예를 들어 같은 열에서 하나는 varchar2(100) 이고 다른 하나는 varchar2(200)인 경우라도 문제가 없다.

7. 컬럼명은 일치하지 않아도 된다. 그래서 강제로 컬럼명을 맞춘다고 해도 무조건 순서대로 컬럼명을 맞추기 때문에 이점을 유념하고 있어야 한다. 컬럼명이 다를 경우 첫번째 질의에 있는 컬럼명이 헤딩된다.

예제 1) 컬럼 순서를 바꾸고 강제로 alias를 준다고 해도 sum(a1)은 "50"이 아니라 "40"이 나온다.

select sum(a1), sum(a2) from ( select 10 as a1, 20 as a2 from dual union select 30 as a2, 40 as a1 from dual )

8. UNION은 내부적으로 중복된 값을 제거하는 과정에서 SORT기능(정렬)이 작동하며,

UNION ALL은 중복여부 관계없이 전체가 보여주므로 SORT가 일어나지 않는다.

고로 UNION ALL이 빠르다.

오라클에서 rownum 은 쿼리가 실행될 때 결과 레코드에 번호를 나타내어 주는 필드이다.
물론 table을 만들 때 rownum을 만들어줄 필요는 없다.

어떠한 테이블이라도
"select rownum from boardtable" 의 형태로 쿼리를 날리면 레코드에 번호가 나타내어 주는 것을 볼 수 있을 것이다...

따라서 페이징을 위한 쿼리에서 우리는 rownum이 10 보다 크고 20보다 작은...
이런 식의 조건을 주어.. 원하는 범위의 레코드만 쿼리 할 수 있다..

select * from boardtable where rownum > 10 and rownum <= 20

그래서 위와 같이 쿼라하면 어떨까?
결과가 하나도 않나올 것이다.. 왜냐하면.. 쿼리가 실행되면 where 절 부분이 먼저 실행 될 것이고.. select 를 하기전에는 rownum이 없기 때문이다.
따라서, rownum을 긁어오는 쿼리를 한번 미리 해줄 필요가 잇다..

select * from (select rownum numrow , boardtable.* from boardtable) where numrow > 10 and numrow <= 20

order by 절을 사용하지 않는다면 이것만으로 우리는 행복할 수 있다.

select * from (select rownum numrow , boardtable.* from boardtable order by reg_date desc) where numrow > 10 and numrow <= 20

결과는 원하는 대로 되지 않음을 볼 수 있다.
이유는 아마도.... order by 가 rownum 값을 만드는데 영향을 못주는 것이라고 생각되는데.. 즉.. order by를 지정한다고 해도.. rownum은 최초의 order by 가 없는 상태로 만들어진다고 추측할 수 있다..
select rownum numrow , boardtable.* from boardtable order by reg_date desc
select rownum numrow , boardtable.* from boardtable
위의 두 쿼리는 실제로 rownum 값을 똑같이 만들어준다.. 물론 뿌리는 순서는 다르지만..

따라서 원하는대로 정렬을 할려면..

select rownum numrow , aa.* from ( select * from boardtable order by reg_date desc ) aa

위의 형태로 쿼리를 바꾸어 줘야 한다.
그런데 또 우리는 범위를 지정해야 하니까..

select rownum numrow , aa.* from ( select * from boardtable order by reg_date desc ) aa where numrow > 10 and numrow <= 20

하면 아무것도 안나오는 이유는 이미 설명했고..

select rownum numrow , aa.* from ( select * from boardtable order by reg_date desc ) aa where numrow > 10 and numrow <= 20

따라서...

select * from ( select rownum numrow , aa.* from ( select * from boardtable order by reg_date desc ) aa ) where numrow > 10 and numrow <= 20

위와 같이 해주면 되겟다..
그런데 좀 복잡하다.. 더구나 검색을 한번... 두번... 세번 을 하니...
데이타 양이 많다면 쿼리실행속도가 느려질 가능성이 있다..
물론 속도에 가장 큰 영향을 주는 것은 order by 이지만 ...
그래서 index를 만들고 hint 절을 써야할 이유가 생기는 것이다..
인덱스는 order by 를 하는 필드에 주면 된다..
따라서.. reg_date 필드에 인덱스를 주면 되고...
그러나 hint 절을 사용하려면 유니크 속성이 있어야 한다..
따라서.. 인덱스를 줄 때 reg_date 필드와 프라이머리 키 필드를 같이 걸어주면 유닉속성을 이용할 수 있다..

create unique index idx_board_reg_date on boardtable (reg_date, idx)

인덱스가 생성된다..

select /*+ index_desc(boardtable idx_board_reg_date) */ rownum, tvulist.* from tvulist

order by 없이 rownum이 제대로 긁어지나?

select * from (select /*+ index_desc(tvulist idx_board_reg_date) */ rownum as numrow, boardtable.* from boardtable ) where numrow > 10 and numrow <= 20

이제 위와 같이하면...
원하는 범위의 데이타를 가져올 수 있겠다..

만일 인덱스를 사용하지 않앗는데... 데이타가 튀어나오는 시간이 좀 늦는다 싶으면..
인덱스를 사용하라.. 행복할 것이다..