안녕하세요!
오늘은 SQL 을 더욱 효율적으로 쓸 수 있는 ROWNUM와 문자열 관련 함수를 가져왔습니다.
먼저 ROWNUM 부터 알아보겠습니다.
오늘 내용에 앞서 이전 내용들이 기억나지 않으시다면 아래 버튼을 눌러 복습하고 오시기 바랍니다!!
ROWNUM
SELECT 순서대로 행 번호를 부여해주는 가상 컬럼
WHERE 절에서 사용 가능
사용 용도 : 페이징 처리(네이버 카페에서 게시글 리스트를 한 화면에 제한적인 갯수로 조회(15개씩)
한 페이지에 모든 건수를 다 표시하려면 조회 시간도 오래 걸릴뿐더러 엄청나게 렉이 걸리겠죠??
ROWNUM 은 사용할 수 있는 형태가 정해져 있습니다.
1. ROWNUM이 1 일때
2. ROWNUM이 N보다 작거나 같은 경우, 작은 경우
3. ROWNUM이 1보다 크거나 같고 N보다 작거나 같은 경우
이에 반대로 사용할 수 없는 형태도 있는데요
1. ROWNUM이 1이 아닐때
2. ROWNUM이 N보다 크거나 같은 경우, 큰 경우
그러면 우리는 사용할 수 없는 형태를 더 주의깊게 봐야겠죠?
그럼 사용할 수 있는 형태와 사용할 수 없는 형태의 예시를 보겠습니다.
********** 사용할 수 있는 형태 **********
********** 사용할 수 없는 형태 **********
사용할 수 있는 형태의 결과를 보시면 ROWNUM 이라는 컬럼이 생성되면서
각 행에 번호를 부여 해준 것을 볼 수 있고,
사용할 수 없는 형태는 결과가 없겠죠?
그럼 ROWNUM과 ORDER BY의 관계에 대해서 얘기해보도록 하죠.
먼저, SQL에서 SELECT 구문의 실행 순서는
FROM => WHERE => SELECT => ORDER BY
순서로 실행이 됩니다.
그렇다면 순번을 지정해주는 ROWNUM과
정렬을 해주는 ORDER BY를 함께 쓰면 어떻게 될까요?
우선 기본 emp 테이블입니다.
위를 보시면 ROWNUM 의 순서가 뒤죽박죽 인것을 볼 수 있습니다.
제가 SELECT => ORDER BY 라고 했죠?
그래서 SELECT 에서 ROWNUM 이 먼저 적용된 상태에서 ORDER BY 가 적용됐기 때문입니다.
IN-LINE VIEW
ROWNUM의 결과를 정렬 이후에 반영하고 싶은 경우에는 IN-LINE VIEW 라는 것을 사용합니다.
여기서 VIEW 는 DBMS 에 저장되어 있는 SQL이고,
IN-LINE 은 직접 기술 했다, 어딘가 저장을 한게 아니라 그 자리에 직접 기술한다는 의미를 갖고있습니다.
좀 더 설명을 하겠습니다.
IN-LINE VIEW 는 SELECT 절에 * 만 단독으로 사용하지 않고 콤마를 통해
다른 임의 컬럼이나 expression 을 표기한 경우 * 앞에 어떤 테이블(뷰)에서 온 것인지
한정자(테이블 이름, view 이름)를 붙여줘야 한다.
table, view 에도 별칭을 부여할 수 있는데 SELECT 절처럼 AS 키워드는 사용하지 않는다.
무슨말인지 이해 가시나요?
아닐것 같아 바로 예시 가겠습니다 ㅎ
IN-LINE VIEW 를 적용한 모습입니다.
아까와 차이점이 보이시나요?
아까처럼 ROWNUM과 ORDER BY를 같이 사용했는데 정렬이 잘 되었네요.
FROM 절 안에 SELECT 구문을 하나의 테이블로 생각하고 사용하면 되는데요,
그 정렬된 테이블의 별칭을 a 로 지정해 주었고,
그 후에 a 라는 테이블을 ROWNUM 을 통해 순번을 지정해준 것입니다.
그럼 응용 해보겠습니다.
다음은 11 페이지부터 20 페이지 까지 조회하는 쿼리 입니다.
하지만 이 쿼리는 작동되지 않습니다.
왜?
앞에서 말씀드렸듯이 ROWNUM 의 특성으로 1페이지부터 읽는 형태가 아니기 때문입니다.
다음과 같은 쿼리로 바꾸어 줘야 합니다.
ROWNUM 의 값을 별칭을 통해 새로운 컬럼으로 만들고 해당 SELECT SQL을 IN-LINE VIEW로 만들어서
외부에서 ROWNUM에 부여한 별칭을 통해 페이지 처리를 해야합니다.
위와 같이 rn 이라는 별칭을 통해 비로소 1페이지 부터가 아니더라도 조회가 가능해 진것이죠.
페이징 처리에 대해서 조금 설명하면서 바인딩 변수에 대해 설명하겠습니다.
위의 쿼리는 요구사항이 10페이지 씩 보이는 것입니다.
보시면 11~20 페이지를 조회하고자 BETWEEN 11 AND 20 이라고 표현했죠?
그렇다면 1페이지는 1~10, 2페이지는 11~20, 3페이지는 21~30, ....
그렇다면 n 페이지는?
BETWEEN (n-1) * 10 + 1 AND n * 10
이라고 일반화하여 표현할 수 있겠습니다.
여기에서 n을 페이지 번호라고 하고, 10을 페이지크기 라고 할 수 있겠네요.
그렇다면 이 페이지와 페이지 크기는 요구사항에 따라서 변하겠죠?
이때 이 변하는 함수를 매번 수정하기 귀찮기 때문에 사용하는것이 바로
'바인딩 변수' 라고합니다.
바인딩 변수는 콜론( : ) 을 변수명앞에 붙여서 사용할 수 있는데요,
위의 식은 아래와 같이 쓸 수 있습니다.
BETWEEN (:page-1) * :pageSize + 1 AND :page * :pageSize
이것을 위의 쿼리에 다시 적용해보면
쿼리를 실행시키면 다음과 같은 바인드 값을 입력해주는 창이 나타납니다.
pageSize 와 page를 각각 선택해서 원하는 값을 넣어줄 수 있죠.
계속해서 값이 변하는 경우에 사용하면 편리하겠죠?
함수
1. 문자열 관련 함수
다음으로 함수에 대해서 설명하도록 하겠습니다.
아, 함수에 대해서 설명하기 전에 우리가 짠 쿼리를 테스트 해볼 수 있는 dummy 컬럼을 소개해 드리겠습니다.
오라클 sys 계정에 존재하는 하나의 행, 하나의 컬럼을 갖는 테이블을 dual 테이블 이라고 하는데요,
함수 실행(테스트)나 시퀀스 실행, merge 구문, 데이터 복제 등등 여러가지를 할 수 있습니다.
LENGTH 함수 테스트를 통해 보여드리겠습니다.
LENGHT 함수 테스트 쿼리와 결과값인데요,
결과를 보면 LENGTH 함수가 어떤건지 추측할 수 있습니다.
이것 말고도 여러가지 문자열 함수들이 있는데 설명보다는 직접 보는게 빠를것 같아 바로 보여드리겠습니다.
다음과 같이 쓸 수 있으며 그 결과값들을 나열해 봤습니다.
설명은 하나씩 보면서 추측해 보는 것으로 충분할것 같아 생략 하겠습니다.^^
(*****절대 귀찮은거 아님 주의*****)
2. 숫자 관련 함수
다음으로 숫자 관련 함수를 설명해 드리도록 하고 마치겠습니다.
오라클에서 숫자 관련 함수는 ROUND, TRUNC, MOD 가 있습니다.
1. ROUND(숫자, 반올림 기준자리) : 반올림 함수
2. TRUNC(숫자, 내림 기준자리) : 내림 함수
3. MOD(피제수, 제수) : 나머지 값을 구하는 함수
바로 예시를 보시죠.
ROUND 함수를 사용한 결과입니다.
반올림의 기준자리는 아래와 같은 기준으로 나눠집니다.
다음으로 TRUNC 함수 사용입니다.
TRUNC 함수는 그냥 내림 함수라서 쉽게 사용하실 수 있을것 같네요 ㅎㅎ
마지막으로 MOD 함수인데요,
JAVA 에서는 나머지를 구할 때 '%' 를 사용하죠?
MOD 함수의 특징은 연산자가 없다는 것입니다.
아! 그리고 진짜 마지막으로 간단하게 하나 소개해 드리고 마치겠습니다.
오늘의 날짜를 알 수 있는 SYSDATE 라는 함수 입니다.
간단하게 dual 로 사용하면 편리합니다.
다음시간에 많이 쓸 테니 기억해두세요!ㅎ
너무 급하게 마무리 하는 느낌(?)이지만 절대 귀찮은건 아닙니다^^7
오늘도 끝까지 봐주셔서 감사합니다.
다음에 또 좋은 내용 가져오겠습니다!
'프로그래밍 > 데이터베이스' 카테고리의 다른 글
| [SQL 기초 - 6장] DECODE, 그룹함수(GROUP BY, HAVING 절) (0) | 2024.12.06 |
|---|---|
| [SQL 기초 - 5장] 오라클 SQL 날짜(2), 숫자 표기법 / 조건문 (0) | 2024.12.03 |
| [SQL 기초 - 3장] 오라클 WHERE 절 기본 및 심화 (feat. ORDER BY) (0) | 2024.11.27 |
| [SQL 기초 - 2장] 오라클 SELECT 구문을 배워보자!! (0) | 2024.11.25 |
| [SQL 기초 - 1.5장] 오라클에 실습 데이터 넣기 (0) | 2024.11.22 |
