login
회원가입 IDPW찾기










home > 정보마당 > 사이버강의실
제 목 [오라클기초실무] Quiz1.
글쓴이 yjan 등록일 2009-03-30 첨부      
내 용 수업시간에 ROWNUM 에 대해서 배우셨죠 몇가지 실습도 하셨는데...관련된 추가 설명과
관련된 쿼즈를 낼께요~~
(조금어려울수 있으나...자세한 설명은 수업시간에~~)


-- ROWNUM 에 대한 설명....

ROWNUM이란 어떤 테이블 내에 물리적으로 저장되어 있는 컬럼이 아니라
CURRVAL,NEXTVAL, ROWID, LEVEL 등과 같이 모든 SQL에 그대로 삽입해서 사용할 수 있는 가상(Pseudo)의 컬럼이다. SQL이 실행되는 과정에서 발생하는 일련번호이므로 각 SQL수행시마다 같은 로우라 하더라도 서로 다른 ROWNUM을 가질 수 있다. 그러므로 이러한 내부적인 값을 조건절에 사용하여 우리가 원하는 만큼만 처리가 수행되도록 할 수 있다. 이 방법 역시 전체를 처리하지 않고 일부만 처리하도록 유도하는 방법이므로 하나의 부분범위처리라고 말할 수 있다.


SQL 실행과정의 어느 특정 부분에서 ROWNUM이 결정되어지므로 정확히 알지 못하고 사용하면 우리가 원하지 않는 결과가 추출될 수도 있다.

인덱스를 통해 엑세스한 테이블의 로우들 중에 다른 조건을 확인하여 만족하는 로우들만 ROWNUM이 부여되어 운반단위로 보내진다는 것을 알 수 있다.

그러므로 운반단위가 채워지거나 ROWNUM이 10보다 커지는 순간 수행을 멈춘다. 만약 엑세스는 했으나 조건을 만족하는 로우가 없다면 끝까지 ROWNUM <= 10을 만족하지 못했으므로 처리는 전체범위가 끝날 때까지 계속될 것이다.

ROWNUM이 비록 SQL단위로 생성되는 값이지만 \'ORDER BY\'가 수행되기 전에 이미 WHERE 절에 있는 조건을 만족하는 로우마다 부여되어 임시공간에 저장된다. 그러므로 \'ORDER BY\'를 수행하기 전에 이미 \'ROWNUM<=10\'이 적용되어 단 10건만 정렬하여 운반단위로 보낸다. 이것은 우리가 원하는 결과가 아닐 뿐만 아니라 최종적으로 추출된 로우들의 ROWNUM은 순차적으로 나타나지도 않는다. 이와 같이 전체 범위처리를 하는 경우는 최종적으로 추출되는 로우의 순서와 ROWNUM이 일치하지 않으며 잘못된 결과를 추출한다는 사실을 명심하기 바란다.


그러므로 추출되는 로우 중에 10번째 로우를 찾기 위해 \'...WHERE ROWNUM=10\'을 요구했다면 이 조건을 만족하는 로우는 결코 추출될 수가 없다. 원래의 조건을 모두 만족하여 ROWNUM이 \'1\'이 될 수 있었다 하더라도 조건에 있는 \'ROWNUM=10\'에 의해서 무시되어 ROWNUM은 아직 \'1\'이 되지도 못했다. 그러므로 ROWNUM이 \'1\'이 되지 않고서는 영원히 ROWNUM은 \'10\'이 될 수 없으므로 이러한 조건을 만족하는 로우는 결코 찾을 수가 없다.



SQL내에 ORDER BY 나 GROUP BY가 있다면 상황은 전혀 달라진다. 우리가 어떤 조건에 맞는 데이터를 엑세스하여 정렬한 수 10건만 추출하기 위해서 다음과 같은 SQL을 수행시킨다면 우리가 원하는 결과를 얻을 수 없다.



-- 추가내용 참조는 제 블로그로 http://yjan.tistory.com/entry/기초실무-오라클-ROWNUM



2. QUIZ


고정된 형태의 화면에서 항상 정확히 10 등까지의 데이터만 필요한 경우이다.
다음과 같은 EMP 테이블과 데이터가 있다.



SELECT ROWNUM, SAL, ENAME, JOB, EMPNO
FROM EMP
ORDER BY SAL DESC, ENAME;



ROWNUM 데이터는 ORDER BY에 의해 소트 가 수행되기 전에 붙여지므로 결과는 다음과 같이 나타난다.

ROWNUM SAL ENAME JOB EMPNO
9 5000 KING PRESIDENT 7839
13 3500 FORD ANALYST 7902
8 3000 SCOTT ANALYST 7788
4 2975 JONES MANAGER 7566
6 2850 BLAKE MANAGER 7698
7 2450 CLARK MANAGER 7782
2 1600 ALLEN SALESMAN 7499
10 1500 TURNER SALESMAN 7844
14 1300 MILLER CLERK 7934 <--- 9번째 로우 9위
5 1250 MARTIN SALESMAN 7654 <--- 10번째 로우 10위 : CASE 2
3 1250 WARD SALESMAN 7521 <--- 11번째 로우 10위 : CASE 1
11 1100 ADAMS CLERK 7876 <--- 12번째 로우 12위
12 950 JAMES CLERK 7900
1 800 SMITH CLERK 7369




14 rows selected.





-- 요구사항



위의 데이터를 이용하여 순위를 나타내는 컬럼과 함께 10위 까지 나오는 SQL문과 (위의 샘플 데이터 경우 WARD, 11 번째 로우) 정확히 10번째 로우까지만 나오는 (위의 샘플 데이터 경우 MARTIN, 10 번째 로우) SQL문을 작성하라.
즉, 다음과 같은 결과가 나오게 하라.


ROWNUM SAL ENAME JOB EMPNO
1 5000 KING PRESIDENT 7839
2 3500 FORD ANALYST 7902
3 3000 SCOTT ANALYST 7788
4 2975 JONES MANAGER 7566
5 2850 BLAKE MANAGER 7698
6 2450 CLARK MANAGER 7782
7 1600 ALLEN SALESMAN 7499
8 1500 TURNER SALESMAN 7844
9 1300 MILLER CLERK 7934
10 1250 MARTIN SALESMAN 7654
11 1250 WARD SALESMAN 7521


-- 추가내용 및 참조는 제 블로그로 http://yjan.tistory.com/entry/기초실무-Quiz-정확히-10위까지만-구하라






--중복데이터 삭제
CREATE TABLE ADDRBOOK(
JUMIN VARCHAR2(14) NOT NULL,
NAME VARCHAR2(20),
ADDR VARCHAR2(50),
TEL VARCHAR2(20)
);
INSERT INTO ADDRBOOK VALUES('111111-1111111','홍길동','서울 강남구 역삼동','02-333-4567');
INSERT INTO ADDRBOOK VALUES('333333-4444444','가길동','서울 강남구 대치동','02-567-7654');
INSERT INTO ADDRBOOK VALUES('111111-1111111','홍길동','서울 강남구 역삼동','02-333-4567');
COMMIT;
SELECT JUMIN, NAME FROM ADDRBOOK;

DELETE FROM ADDRBOOK A WHERE ROWID > (SELECT MIN(ROWID) FROM ADDRBOOK B WHERE A.JUMIN = B.JUMIN);
ROLLBACK;
DELETE FROM ADDRBOOK A WHERE ROWID < (SELECT MAX(ROWID) FROM ADDRBOOK B WHERE A.JUMIN = B.JUMIN);
DELETE FROM ADDRBOOK A
WHERE ROWID IN
(SELECT ROWID
FROM (SELECT ROW_NUMBER()
OVER(PARTITION BY B.JUMIN ORDER BY B.JUMIN) ROW_NUM
FROM ADDRBOOK B)
WHERE ROW_NUM > 1);

SELECT ROWID, ENAME FROM EMP;

SELECT ENAME FROM EMP WHERE ROWID = 'AAAM3SAAEAAAAIVAAD';

SELECT ROWNUM,ENAME,SAL FROM EMP WHERE SAL>= 2900;
서울 구로구 구로동 197-10 이앤씨벤처드림타워2차 12층 1202, 1209호 (주)오엔제이시스템즈,오라클자바교육학원
대표자명 : 이 종 철 | 사업자번호 : 107-86-88432 | 전화:070-7555-4760~1 | 팩스 :02-837-4760
통신판매업 신고번호 : 제2009-서울구로-0447호
Copyright ⓒ oraclejava.co.kr All rights reserved.