[Oracle] 의사 컬럼 (Pseudo Column)

💡의사 컬럼

---

의사 컬럼(Pseudo Column)은 실제 컬럼이 아닌데 컬럼처럼 행동하는 객체이다.

 

객체의 종류

SELECT
	name, buseo,		--1. 컬럼(속성)
	100,				--2. 상수
	substr(name, 2),	--3. 함수
	rownum				--4. 의사컬럼
FROM tblInsa;

1. 컬럼(속성)은 읽기(Output) 전용으로, 객체(레코드)의 특성에 따라 다른 값을 가진다.
2. 상수는 Output 전용으로, 모든 레코드가 동일한 값을 가지므로 아주 특별한 경우가 아니고서는 사용하지 않는다. 모두 같은 값을 가지고 있는 컬럼을 만드는 경우가 거의 없기 때문이다.
3. 함수는 쓰기와 읽기(Input/Output)가 모두 되며, 객체의 특성에 따라 다른 값을 가진다. 대부분 함수의 인자값은 조작이 가능한 컬럼이다.
4. 의사컬럼은 일반 컬럼처럼 Input은 없고 Output만 있는 특징이 있다. 함수처럼 매개변수를 전달하지 않으며, 돌려주기만 한다.

 

💡rownum

---

rownum은 행 번호로, 일련번호를 돌려준다.

rownum은 from절을 실행할 때 각 레코드에 rownum을 할당하며, where절이 실행되면 상황에 따라 rownum이 재계산된다.

 

시퀀스 객체와 외관상으로는 비슷하지만 상관이 없기 때문에 헷갈리지 않도록 한다.

시퀀스는 연결되는 번호를 생성하는 오브젝트이고, rownum은 현재 테이블의 행 번호를 데이터로 가져오는 역할을 한다는 차이가 있다.

 

rownum은 오라클 전용이다.

MS-SQL에는 대체제로 top n, MySQL에는 대체제로 limit n, m이 있다.

 

rownum의 사용

SELECT name, buseo, rownum FROM tblInsa WHERE rownum <= 5;

SELECT name, buseo, rownum FROM tblInsa WHERE rownum = 5;
SELECT name, buseo, rownum FROM tblInsa WHERE rownum > 5 AND rownum <= 10;

rownum <= 5 조건은 실행이 되지만, rownum = 5, rownum > 5 AND rownum <= 10 조건은 실행이 안 되는 것을 확인할 수 있다.

이는 rownum의 성질을 알아야만 이해할 수 있다.

 

rownum의 성질

--실행O
SELECT name, buseo, rownum	--3. 소비: 1에서 만든 rownum을 가져온다.
FROM tblInsa				--1. 생성: FROM절이 실행되는 순간 모든 레코드 rownum 할당
WHERE rownum = 1;			--2. 조건

--실행X
SELECT name, buseo, rownum	--3. 소비
FROM tblInsa				--1. 생성
WHERE rownum = 5;			--2. 조건

rownum = 5 조건을 가지고 테이블에서 비교를 하는데, 조건에 만족을 못 하면 결과셋에서 완전히 제외한다.

앞에서 where절이 rownum에 영향을 미친다고 했다. rownum = 5 조건을 만족하지 못했던 1에서 4까지의 데이터가 탈락하면서 index가 계속해서 다시 매겨진다.
rownum은 1이 포함이 되면서 쌓이는 형태이기 때문에 결국 rownum = 5 조건을 만족하는 데이터가 하나도 없게 되는 것이다.

 

ORDER BY 사용

SELECT name, buseo, basicpay, rownum
FROM tblInsa							--1. rownum 할당
ORDER BY basicpay desc;					--2. 정렬

basicpay로 정렬을 했을 때, rownum이 뒤섞인 것을 확인할 수 있다.

이러한 결과가 나오는 이유는 rownum이 from절에서 실행될 때 만들어졌기 때문이다.

만약 where절 이후에 rownum 번호를 매기고 싶다면, Sub Query를 사용하는 별도의 과정이 필요하다.

 

Sub Query 사용

SELECT name, buseo, basicpay, rownum, rnum
FROM (SELECT name, buseo, basicpay, rownum AS rnum
			FROM tblInsa
			ORDER BY basicpay DESC);

서브 쿼리를 사용했을 때 정렬이 되는 이유는 서브 쿼리의 rownum과 바깥의 rownum이 서로 다른 것이기 때문이다.
바깥쪽의 from절도 별도의 rownum을 만든다는 것을 기억하도록 하자. 이때 안쪽의 rownum을 출력하려면 반드시 Alias로 별칭을 붙여야 한다.

🥷🥷(펑💨)

🥷나는 서브 쿼리의 rownum! 🥷나는 바깥의 rownum! 이중 진짜 rownum은 서브 쿼리의 rownum이다!

 

⭐원하는 범위 추출 공식

---

SELECT name, buseo, basicpay, rnum, rownum
FROM (SELECT name, buseo, basicpay, rownum AS rnum		--2.
			FROM (SELECT name, buseo, basicpay
				FROM tblInsa
					ORDER BY basicpay DESC))			--1.
						WHERE rnum BETWEEN 5 AND 10;

1. 내가 원하는 순서대로 정렬한다.
2. 1을 서브쿼리로 묶는다. + rownum(rnum)
3. 2를 서브쿼리로 묶는다. + 위의 rnum 사용

위의 공식을 사용하여 한 번에 이름순으로 20명씩 보는 페이징 작업을 해보도록 하자.

 

1단계(정렬하기)

SELECT * FROM tblAddressBook ORDER BY name ASC;

2단계(서브쿼리로 묶기)

--실행X
SELECT * FROM SELECT * FROM tblAddressBook ORDER BY name ASC);

--실행O (all(*)과 컬럼을 함께 사용하기 위해서 테이블 이름을 붙여야 한다.)
SELECT a.*, rownum AS rnum FROM (SELECT * FROM tblAddressBook ORDER BY name ASC) a;

이때의 rownum(rnum)이 3단계에서 사용된다.

3단계(서브쿼리로 한 번 더 묶기)

SELECT * FROM (SELECT a.*, rownum AS rnum FROM (SELECT * FROM tblAddressBook ORDER BY name ASC) a) WHERE rnum BETWEEN 1 AND 20;
SELECT * FROM (SELECT a.*, rownum AS rnum FROM (SELECT * FROM tblAddressBook ORDER BY name ASC) a) WHERE rnum BETWEEN 21 AND 40;
SELECT * FROM (SELECT a.*, rownum AS rnum FROM (SELECT * FROM tblAddressBook ORDER BY name ASC) a) WHERE rnum BETWEEN 1981 AND 2000;

2단계에서 만든 rownum(rnum)을 조건으로 사용하였다.

그런데 코드 길이가 길어지면 길이를 줄이는 방법으로 뷰(view)를 활용할 수 있다고 했다.

 

[Oracle] 뷰와 뷰 사용 문서 (View)

뷰에 대해서는 위 글을 참고하도록 하자.

 

뷰 활용

CREATE OR REPLACE VIEW vwAddressBook
AS
SELECT a.*, rownum AS rnum FROM (SELECT * FROM tblAddressBook ORDER BY name ASC) a;

SELECT * FROM vwaddressBook WHERE rnum BETWEEN 1 AND 20;
SELECT * FROM vwaddressBook WHERE rnum BETWEEN 21 AND 40;
SELECT * FROM vwaddressBook WHERE rnum BETWEEN 1981 AND 2000;

보통은 뷰를 만들어서 쓰는 경우보다 풀어서 쓰는 방법을 사용하지만 이는 견해 차이이다.

구문을 조금이라도 줄여서 간편하게 보려는 경우, 지금처럼 이름을 주어서 뷰를 활용할 수 있다.