ROWNUM을 이용한 페이징 출력, 중복제거 distinct 키워드, 문자열 자르기 SUBSTR 함수. 2021-07-29

우선 페이징 이라 함은, 한번에 테이블 중 몇개씩 끊어서 나열하는 것인지 기준에 따라 정렬시키는 방법이다. 이런 페이징을 하기 위해서는 테이블을 특정 기준에 따라 나눌수 있어야 한다. 그런데 일반적으로 생성한 테이블들을 보면 어떤 추가된 순서에 따라 숫자를 부여하는 칼럼을 사용자가 만들어놓지 않은 이상 그런 순차적으로 뚜렷하게 나눌수 있는 기준이 되는 칼럼을 찾기 어렵다.

예를 들면 저번에 내가 만들어놓은 이런 칼럼에서, 기준으로 잡을 것이 안보일 것이다. 이때 순차적으로 페이징할수 있는 기준을 만드는 것이 바로 ROWNUM을 이용하는 것이다. 바로 위 사진의 제일 왼쪽에 1 부터 4까지 붙여진 것이 있는데 이것은 오라클에서 자체적으로 select 문을 통해 결과집합을 생성하면서(where절 단계에서) 만들어서 해당 결과집합의 칼럼으로 붙여주는 것이다.

 

*핵심은 위와같은 ROWNUM이 과연 "언제" 만들어지냐는 것인데, SELECT 절에서 WHRER 절을 실행할 떄 ROWNUM이 생성된다.

즉 컴퓨터에서 작동하는 순서는 from->whrere 이므로, 만약 from절에서 ROWNUM이라는 이름을 갖춘 칼럼이 없다면, where 단계에서 바로  생성된다는 것이다.

 

 

이것을 이용하기 위해선 from 절에서 ROWNUM이라는 칼럼을 갖는 해당 테이블을 미리 출력해 놓는것이 필요하다. 왜냐? ROWNUM은 where절일떄 만들어지는데, 만약 출력하고자하는 범위가 1부터 시작하지않으면, 결과 집합 자체가 만들어지지 않기 때문이다.

where절이 실행되며, ROWNUM이 생성되는데, ROWNUM은 1부터 만들어진다. 그래서 해당 조건(1 초과의 수에서의 조건)을 만족하지 못하는 것이다.

쉽게 말하면 where절은 생성과 조건판단을 동시에 하기에 이런 일이 벌어지는 것이다.

즉, ROWNUM생성과 동시에 ROWNUM의 조건을 판단해서 테이블셋을 만든다.

 

그래서 아래와 같이 미래 ROWNUM을 갖춘 테이블을 뽑아놓는다.

select * from (select ROWNUM as NUM NOTICE.* from member) where NUM between 1 and 2;

  이러면 미리 ROWNUM NUM이라는 별칭으로 칼럼이 생긴 상태에서 다시 출력을 하는 것이기 때문에 문제없다.

 

이렇게 말이다. 그런데 이러면,

이렇게 출력된다. 괄호 안의 select 문으로 칼럼을 NUM 만 출력하려 했으니 당연한 결과이다. 그럼 나머지 칼럼들과 함께 출력하려면 어떻게 해야 할까?

select * from(select ROWNUM as NUM, NAME, PWD, EMAIL, PHONE, BIRTHDAY FROM MEMBER) WHERE NUM BETWEEN 2 AND 3;

이렇게 하면 된다.

결과

근데 이렇게 모든 칼럼명을 쿼리에 나열하니, 너무 복잡하다. 그래서 * 연산자를 써서 간략히 해보자. 사실 칼럼을 쓸떄는 MEMBER.NAME, MEMBER.PWD 이렇게 쓰는데, 이게 생략되어 있다. 그래서 MEMBER.* 이걸 추가하면 모든 칼럼이 복잡한 쿼리를 쓰지 않고도 출력할 수 있다.

select * from(select ROWNUM AS NUM, MEMEBER.* from member)where num between 2and 3;

이렇게 쓰면 전체가 출력된다.

 

그렇다면 이렇게 중복되는 칼럼에서 중복을 없애려면 어떻게 해야 할까? 바로 Select 문에서 Column을 제한하는 곳에서 distinct 라는 키워드를 추가하면 된다.

select distinct name from member;

 

결과

이렇게 중복값을 없애줄 수 있다.

 

이 전까지는 ROW를 제한하는 방식에 대해서는 많이 공부했지만 COLUMN을 제한하는 방법은 많이 공부하지 못했다. 우선 SELECT (칼럼 제한 조건) FROM where (ROW 제한 조건); 이렇게 select 를 통한 결과집합 출력 쿼리가 이루어 진다는 것은 알고 있다. 그렇다면, 칼럼제한 조건을 어떻게 쓰는 것일까?

 

가장 많이쓰는 함수는 문자열을 자르는 함수인데 가령, 이름과 전화번호 뒷자리를 출력 한다고 하자. 이전까지 우리가 배운 방법으로는 출력이 안되지만.

SELECT name,SUBSTR(phone,10) from member;

 이렇게 전화번호 010-9209-6429 중 뒤 4자리만 출력 할수 있다. SUBSTR 함수는 (칼럼명, 자르기 시작하는 자릿수) 이렇게 파라미터가 들어간다.

위 쿼리를 쓰면 이렇게 결과집합이 나온다. 

또 이 SUBSTR은 칼럼 제한 조건으로만 쓰일뿐만 아니라 ROW 제한 조건으로도 쓰인다. 예를 들면, 전화번호가 010 으로 시작하는 모든 테이블을 출력하려 할때,

select * from member where REGEXP_LIKE(phone,'^010-\d{4}-\d{4}$');
select * from member where phone like '010-%-%';
select * from member where SUBSTR(phone,1,3)= '010';

위 3가지 방법 모두 출력하고자 하는 ROW를 제한 할 수 있다. 동일한 결과가 나온다.

 

공부시간 3시간

순공시간 1시간.

 

오늘도 내가 직접 원하는 쿼리를 만들어보고 내가 원하는데로 결과집합을 출력을 해결해보니, 확실히 전보다 쿼리 작성능력이 발달된 느낌이다.