VIEW

ROWNUM : 검색행에 순차적으로 행번호를 제공하는 키워드

• 오늘 배우는 것 중에 제일 중요 : 원하는 범위의 행만 검색할 수 있도록

SELECT EMPNO,ENAME,SAL FROM EMP;
SELECT ROWNUM,EMPNO,ENAME,SAL FROM EMP;

질의 결과 - 오른쪽이 ROWNUM 적용 

ROWNUM 키워드 대신 ROW_NUMBER() 순위함수를 이용하여 행번호를 반환받아 검색 - OVER() 분석함수 사용

SELECT ROW_NUMBER() OVER(ORDER BY SAL DESC) NUM,EMPNO,ENAME,SAL FROM EMP;

질의 결과

EMP 테이블에서 모든 사원의 사원번호,사원이름,급여를 급여로 내림차순 정렬하여 검색하되 행번호를 제공하여 검색하고 검색된 행번호가 5보다 작은 행들만 검색

--원도우 함수(그룹함수,순위함수,순서함수 등)를 WHERE 구문에서 사용할 경우 에러 발생
SELECT ROW_NUMBER() OVER(ORDER BY SAL DESC) NUM,EMPNO,ENAME,SAL FROM EMP WHERE ROW_NUMBER() OVER(ORDER BY SAL DESC)<5;

--조건식에서 컬럼의 별칭을 사용할 경우 에러 발생
SELECT ROW_NUMBER() OVER(ORDER BY SAL DESC) NUM,EMPNO,ENAME,SAL FROM EMP WHERE NUM<5;

EMP 테이블에서 모든 사원의 사원번호,사원이름,급여를 급여로 내림차순 정렬하여 EMP_VIEW 뷰로 생성

CREATE OR REPLACE VIEW EMP_VIEW AS SELECT EMPNO,ENAME,SAL FROM EMP ORDER BY SAL DESC;
SELECT * FROM EMP_VIEW;

--다른 검색대상을 테이블(뷰)의 모든 컬럼과 같이 표현하고자 할 경우 테이블(뷰).* 형식으로 표현 가능
--EMP_VIEW 뷰에 행번호를 제공하여 검색하되 행번호가 5보다 작은 행들만 검색
-- >> ROWNUM 키워드는 WHERE 구문의 조건식에서 사용 가능
SELECT ROWNUM,EMP_VIEW.* FROM EMP_VIEW WHERE ROWNUM<5;

스크립트 출력, 질의 결과 

*인라인 뷰(INLINE VIEW) : SELECT 명령에서 FROM 구문에 서브쿼리를 사용하여 일시적으로 생성되어 사용되는 뷰

SELECT EMPNO,ENAME,SAL FROM EMP;--테이블을 이용하여 검색
SELECT EMPNO,ENAME,SAL FROM (SELECT * FROM EMP);--인라인 뷰를 이용하여 검색
SELECT EMPNO,ENAME,SAL FROM (SELECT * FROM EMP WHERE DEPTNO=10);

--인라인 뷰에 없는 컬럼을 검색할 경우 에러 발생
SELECT EMPNO,ENAME,SAL,DEPTNO FROM (SELECT EMPNO,ENAME,SAL FROM EMP);

질의 결과 - 인라인뷰 이용

인라인 뷰에도 별칭을 설정하여 검색 가능 - 인라인 뷰의 별칭을 이용하여 모든 컬럼 표현

SELECT ROWNUM,TEMP.* FROM (SELECT EMPNO,ENAME,SAL FROM EMP ORDER BY SAL DESC) TEMP;

인라인 뷰를 이용하면 ROWNUM 키워드의 별칭을 조건식에서 사용 가능

SELECT * FROM (SELECT ROWNUM RN,TEMP.* FROM (SELECT EMPNO,ENAME,SAL FROM EMP ORDER BY SAL DESC) TEMP) WHERE RN=10;

질의 결과

조건식에서 별칭을 사용할 경우 에러 발생 (ROWNUM 키워드에 별칭을 사용)

SELECT ROWNUM RN,TEMP.* FROM (SELECT EMPNO,ENAME,SAL FROM EMP ORDER BY SAL DESC) TEMP WHERE RN=10;

*핵심+가독성 제일 좋은 방법-얘만 알아도 된다!

EMP 테이블에서 모든 사원의 사원번호,사원이름,급여를 급여로 내림차순 정렬하여 검색하되 검색행에 행번호를 제공하여 검색하고 행번호가 6부터 10 범위의 행들만 검색

SELECT * FROM (SELECT ROWNUM RN,TEMP.* FROM 
	(SELECT EMPNO,ENAME,SAL FROM EMP ORDER BY SAL DESC) TEMP) WHERE RN BETWEEN 6 AND 10;
    
질의 결과    

EMP 테이블에서 사원번호가 7844인 사원의 사원번호,사원이름,검색행 직전행의 사원번호와 사원이름,검색행 직후행의 사원번호와 사원이름를 사원번호로 오름차순 정렬하여 검색.
단, 직전행 또는 직후행이 없는 경우 사원번호는 0, 사원이름은 NULL로 검색

--WHERE 구문이 먼저 실행되어 검색되므로 직전행과 직후행이 존재하지 않아 직전행과 직후행의 컬럼값 대신 0과 NULL 검색

SELECT EMPNO,ENAME,LAG(EMPNO,1,0) OVER(ORDER BY SAL) BEFORE_EMPNO,LAG(ENAME,1,NULL) OVER(ORDER BY SAL) BEFORE_ENAME
    ,LEAD(EMPNO,1,0) OVER(ORDER BY SAL) AFTER_EMPNO,LEAD(ENAME,1,NULL) OVER(ORDER BY SAL) AFTER_ENAME FROM EMP WHERE EMPNO=7844;  
    
질의 결과    

*인라인 뷰를 이용하여 모든 사원을 검색한 후 원하는 행에 대한 검색 결과 제공

SELECT * FROM (SELECT EMPNO,ENAME,LAG(EMPNO,1,0) OVER(ORDER BY SAL) BEFORE_EMPNO,LAG(ENAME,1,NULL) OVER(ORDER BY SAL) BEFORE_ENAME
    ,LEAD(EMPNO,1,0) OVER(ORDER BY SAL) AFTER_EMPNO,LEAD(ENAME,1,NULL) OVER(ORDER BY SAL) AFTER_ENAME FROM EMP) WHERE EMPNO=7844;
    
질의 결과 - 인라인 뷰 이용    

*시퀀스(SEQUENCE) : 숫자값(정수)을 저장하여 자동 증가되는 값을 제공하는 객체

시퀀스 생성
형식)CREATE SEQUENCE 시퀀스명 [START WITH 초기값][INCREMENT BY 증가값] [MAXVALUE 최대값][MINVALUE 최소값][CYCLE][CACHE 갯수]

• START WITH 초기값 : 시퀀스 객체에 저장된 초기값 설정 - 생략 : NULL값 자동
• INCREMENT BY 증가값 : 자동 증가되는 숫자값 설정 - 생략 : 1씩 증가
• MAXVALUE 최대값 : 시퀀스 객체에 저장할 수 있는 최대값 설정 - 생략 : 숫자값으로 표현될 수 있는 최대값
• MINVALUE 최소값 : 시퀀스 객체에 저장할 수 있는 최소값 설정 - 생략 : 1
• CYCLE : 시퀀스 객체에 저장값이 최대값을 초과한 경우 최소값부터 다시 제공되도록 반복하는 기능
• CACHE 갯수 : 임시 저장공간에 자동 증가값을 미리 생성하여 제공할 수 있는 갯수를 설정하는 기능 제공 - 생략 : 20

--USER2 테이블 생성 : 번호(숫자형-PRIMARY KEY),이름(문자형),생년월일(날짜형)
CREATE TABLE USER2(NO NUMBER(2) PRIMARY KEY, NAME VARCHAR2(20),BIRTHDAY DATE);
DESC USER2;

--USER2 테이블의 NO 컬럼값으로 저장될 자동 증가값을 제공하기 위한 시퀀스 객체 생성
CREATE SEQUENCE USER2_SEQ;

--시퀸스 확인 - USER_SEQUENCES : 시퀀스 정보를 제공하는 딕셔너리
SELECT SEQUENCE_NAME,MAX_VALUE,MIN_VALUE,INCREMENT_BY FROM USER_SEQUENCES;

--시퀀스에 저장된 숫자값 확인 - 형식)시퀀스명.CURRVAL
--시퀀스에 NULL이 저장된 경우 숫자값을 확인하면 에러 발생
SELECT USER2_SEQ.CURRVAL FROM DUAL;

스크립트 출력, 질의 결과 - 테이블 생성, 시퀀스 객체, 시퀀스 확인 

시퀀스를 이용한 자동 증가값 제공 - 형식)시퀀스명.NEXTVAL

• 시퀸스에 NULL이 저장된 경우 시퀸스의 최소값을 제공하고 시퀀스는 제공된 숫자값으로 자동 변경
• 시퀀스에 숫자값이 저장된 경우 증가값만큼 더해진 숫자값을 제공하고 시퀀스는 제공된 숫자값으로 자동 변경

SELECT USER2_SEQ.NEXTVAL FROM DUAL;-- 1(최소값)이 검색되고 시퀸스 숫자값 변경
SELECT USER2_SEQ.CURRVAL FROM DUAL;--시퀸스 현재값 : 1
SELECT USER2_SEQ.NEXTVAL FROM DUAL;--증가값 : 1 >> 2가 검색되고 시퀸스 숫자값 변경
SELECT USER2_SEQ.CURRVAL FROM DUAL;--시퀸스 현재값 : 2

-*-USER2 테이블에 행 삽입 - 시퀸스의 자동 증가값을 NO 컬럼에 전달하여 저장 - 중복되지 않는 값 
INSERT INTO USER2 VALUES(USER2_SEQ.NEXTVAL,'홍길동','00/01/01');
INSERT INTO USER2 VALUES(USER2_SEQ.NEXTVAL,'임꺽정','01/12/31');
INSERT INTO USER2 VALUES(USER2_SEQ.NEXTVAL,'전우치','02/12/21');
SELECT * FROM USER2;
COMMIT;

질의 결과 - USER2

시퀀스 변경

형식)ALTER SEQUENCE 시퀀스명 {MAXVALUE|MINVALUE|INCREMENT BY} 변경값

USER2_SEQ 시퀀스의 최대값을 99, 증가값을 5로 변경

SELECT SEQUENCE_NAME,MAX_VALUE,MIN_VALUE,INCREMENT_BY FROM USER_SEQUENCES;
ALTER SEQUENCE USER2_SEQ MAXVALUE 99 INCREMENT BY 5;
SELECT SEQUENCE_NAME,MAX_VALUE,MIN_VALUE,INCREMENT_BY FROM USER_SEQUENCES;

--USER2 테이블에 행 삽입 - 시퀸스의 자동 증가값을 NO 컬럼에 전달하여 저장 - 중복되지 않는 값 
INSERT INTO USER2 VALUES(USER2_SEQ.NEXTVAL,'일지매','03/9/09'); --5 증가
SELECT * FROM USER2;
COMMIT;

질의 결과 - 최대값 변경전, 변경후 

시퀀스 삭제

형식)DROP SEQUENCE 시퀀스명

USER2_SEQ 시퀀스 삭제

DROP SEQUENCE USER2_SEQ;
SELECT SEQUENCE_NAME,MAX_VALUE,MIN_VALUE,INCREMENT_BY FROM USER_SEQUENCES;

질의 결과