10. 시퀀스/뷰

시퀀스란?

- 숫자를 자동으로 생성하는 객체.

- 행을 구분하기 위한 고유값으로 기본키를 사용.

- 기본키는 중복되지 않는 유일한 값을 가져야 하는데 직접 값을 생성하는 것은 부담일 수 있어서 시퀀스를 이용해 기본키를 지정.

 

 

​CREATE 통하여 시퀀스 생성

-- 형식
CREATE SEQUENCE 시퀀스이름
[START WITH 시작값]
[INCREMENT BY 증가값]
[MAXVALUE 최댓값 | NOMAXVALUE]
[MINVALUE 최솟값 | NONMINVALUE]
[CYCLE | NOCYCLE] 
  -- 최댓값 도달시 순환 여부 / NOCYCLE은 증가 완료 시 에러 유발
[CACHE | NOCACHE]
  -- 메모리상 시퀀스값 관리 여부(기본값: 20) / 연속된 번호를 얻어야 한다면 NOCACHE가 좋음
  
-- 예시
-- 1. 시퀀스 생성
CREATE SEQUENCE seq_empno
START WITH 1000
INCREMENT BY 1
CACHE 20;

-- 2. 시퀀스 확인(시퀀스 현재 값을 알아내기 위한 방법)
--    - CURRBAL: 현재 값 반환
--    - NEXTVAL: 현재 시퀀스 값 다음 값 반환 
--      → CURRVAL에 새로운 값이 할당되기 위하다면 NEXTVAL로 새로운 값 생성
--        즉, NEXTVAL로 새로운 값을 생성한 후 이 값을 CURRVAL에 대체 
SELECT seq_empno.NEXTVAL FROM dual; --→ 실행할 때마다 1씩 증가 
SELECT seq_empno.CURRVAL FROM dual; --→ 마지막으로 nextval한 값 조회

* NEXTVAL, CURRCAL 사용 가능한 경우

: 서브쿼리가 아닌 SELECT, INSERT문의 SELECT, INSERT문의 VALUE, UPDETE문의 SET

​

* NEXTVAL, CURRCAL 사용 불가한 경우

: VIEW의 SELECT, DISTINCT키워드가 있는 SELECT, GROUP BY/HAVING/ORDER BY절이 있는 SELECT

CREATE TABLE, ALTER TABLE의 DEFAULT 값

 

 

INSERT문에서 시퀀스 사용

INSERT INTO emp_table(empno, ename) VALUES(seq_empno.nextval, 'TEST');

 

 

시퀀스 수정

START WITH 값을 제외한 다른 설정들을 수정할 수 있다.

-- 형식
ALTER SEQUENCE 시퀀스명
[INCREMENT BY 증가값]
[MAXVALUE 최댓값 | NOMAXVALUE]
[MINVALUE 최솟값 | NONMINVALUE]
[CYCLE | NOCYCLE]
[CACHE | NOCACHE] 

-- 예시
ALTER SEQUENCE seq_empno
INCREMENT BY 10
CACHE 40;

 

---

View

1. View란?

- 물리적 테이블에 근거한 논리적 가상 테이블

- 데이터가 없고 테이블에 대한 SQL만 저장, 사용자가 view에 접근하면 SQL이 수행되어 결과 가져옴

- 여러 테이블에 대한 검색결과를 뷰 하나로 검색, 복잡하고 긴 쿼리문을 view로 정의하면 접근 단순화 가능

​

① 사용자에게 접근 허용된 자료만 제한적으로 보여주기 위해 하나 이상의 기본테이블로부터 유도된 이름을 갖는 가상테이블.

② 저장장지內 물리적으로 존재하지는 않지만 사용자에게 있는 것처럼 간주됨.

③ 데이터 보정작업, 처리과정 시험 등 임시적 작업을 위한 용도로 활용.

④ 조인문의 사용 최소화로 사용상 편의를 최대화.

​

2. View의 특징

① 기본테이블로부터 유도된 테이블로, 기본테이블과 같은 형태의 구조를 사용하며 조작도 거의 같음.

* 기본테이블: 실질적 데이터를 저장하고 있은 물리적인 테이블

② 가상테이블이기 때문에 물리적으로 구현되어 있지 않음.

③ 데이터의 논리적 독립성 제공.

④ 필요한 데이터만 뷰로 정의하여 처리할 수 있기 때문에 관리가 용이하며 명령문이 간단해짐.

⑤ 뷰를 통해서만 데이터에 접근하게 하며, 뷰에 나타나지 않는 데이터를 안전하게 보호하는 효율적 기법으로 사용 가능.

⑥ 기본테이블의 기본키를 포함한 속성(열)의 집합으로 뷰를 구성해야만 삽입, 삭제, 갱신, 연산이 가능.

⑦ 일단 정의된 뷰는 다른 뷰를 정의할 때 기초가 될 수 있음.

⑧ 뷰가 정의된 기본테이블, 뷰를 삭제하면 그 테이블이나 뷰를 기초로 정의된 다른 뷰도 자동 삭제.

​

3. View의 장단점

(장점)

① 논리적 데이터 독립성 제공

② 동일한 데이터에 대해 동시에 여러 사용자의 상이한 응용이나 요구 지원.

③ 사용자의 데이터 관리가 간단해짐

④ 접근 제어를 통한 자동 보안이 제공됨.

​

(단점)

① 독립적인 인덱스를 가질 수 없음.

② ALTER VIEW문을 사용할 수 없음 = 뷰의 정의를 변경할 수 없음.

③ 뷰로 구성된 내용에 대한 삽입, 삭제, 갱신, 연산에 제약이 있음.

​

4. View의 사용 목적

: 복잡한 질의를 쉽게 만들어 줌

→ JOIN, GROUP BY와 같은 복잡한 쿼리를 VIEW로 저장하면 다음부터는 저장한 VIEW를 가져와 더 편리하게 사용할 수 있음.

 

 

View 사용하기 

 

1. 생성

CREATE VIEW [OR REPLACE] [FORCE/NOFORCE] 뷰이름[(속성이름[,속성이름])] 
AS 
subquery(SELECT문)
[WITH CHECK OPTION [CONSTRAINT 제약조건]]
[WITH READ ONLY];

- CREATE: 뷰 생성

- CREATE OR REPLACE: 뷰를 생성하지만 같은 이름의 뷰가 있으면 질의 변경

- FORCE: 기본테이블 유무에 상관 없이 뷰 생성(디폴트: NOFORCE)

- WITH CHECK OPTION: 제약조건에 맞는 데이터만 입력되거나 변경 - 해당 뷰를 통해 볼 수 있는 범위 내에서 UPDATE, INSERT

- WITH READ ONLY: SELECT만 가능한 뷰 생성(INSERT, UPDATE, DELETE 불가)

​

→ CREATE VIEW를 사용하여 만들 수도 있으나 이렇게 만든 뷰는 구조를 바꿀 때 뷰를 삭제하고 다시 만들어야 하지만,

CREATE OR REPLACE VIEW를 사용하면 새로운 뷰를 만들 수 있으며 기존 뷰가 존재하더라도 삭제하지 않고 새로운 구조의

뷰로 변경(REPLACE)가 가능하여 대부분 REPLACE를 사용..

 

-- 예제
-- 30번 부서 사원들의 사번, 이름, 부서번호를 자주 검색한다고 한다면 
-- 다음과 같은 SELECT를 여러번 입력해야 한다. 
SELECT empno, ename, deptno
FROM emp_table
WHERE deptno = 30;


-- 쿼리가 너무 길기 때문에 VIEW를 설정하여 간소화를 할 수 있다. 
CREATE VIEW emp_view30
AS
SELECT  empno, enmae, deptno
FROM emp_table
WHERE deptno = 30;

--사용 
SELECT * FROM emp_view30;

-- 생성된 뷰 구조 확인하기
DESC emp_view30;

** 만약 위를 실행하려 하는데 ORA-01031: insufficient privileges(권한 불충분) 오류가 뜬다면 권한을 설정해야 한다.

: 관리자 계정으로 접속: GRANT CREATE VIEW TO 사용자이름;

 

 

2. View 삭제

View는 ALTER문을 사용하여 변경할 수 없으므로 변경하고 싶으면 삭제한 후 재생성해야 함.

DROP VIEW 뷰이름 RESTRIC or CASCADE;

- RESTRIC: 뷰를 다른 곳에서 참조하고 있으면 삭제가 취소됨.

- CASCADE: 뷰를 참조하는 다른 뷰나 제약조건까지 모두 삭제됨.

 

3. Top - n 구하기

Top - n분석은 상위 n명의 정보를 조회하거나 날짜가 빠른 것을 구하는 데 사용된다.

 

 

 

ROWNUM 컬럼

데이터가 입력된 순서를 알려주는 가상 컬럼

 

예제1: ROWNUM 이해하기

① ROWNUM 칼럼값 출력.

SELECT  ROWID, ROWNUM, empno, ename, hiredate
FROM emp_table;

 

② 입사일을 기준으로 오름차순 정렬하는 쿼리에 ROWNUM 컬럼 출력

SELECT ROWNUM, empno, ename, hiredate
FROM emp_table
ORDER BY hiredate;

1. 결과를 보면 입사일을 기준으로 정렬을 하여도 순서는 바뀌었지만 해당 행의 ROWNUM 컬럼의 값은 바뀌지 않았다.

2. ROWNUM은 오라클 내부적으로 부여되어 INSERT문을 이용하여 입력하면 입력한 순서에 따라 1씩 증가하며

값이 지정되기 때문에 바뀌지 않는 것이다.

3. 정렬된 순서대로 ROWNUM의 컬럼값이 지정되려면 새로운 테이블 혹은 뷰로 새롭게 데이터를 저정하여야 한다.

 

예제2: ROWNUM컬럼으로 TON-N 구하기

① 입사일 기준으로 오름차순 정렬한 쿼리문으로 새로운 뷰 생성

CREATE OR REPLACE VIEW view_hire
AS
SELECT empno, ename, hiredate
FROM emp_table
ORDER BY hiredate;

② 입사일 기준으로 정렬을 하는 뷰에 ROWNUM 컬럼 함께 출력

SELECT ROWNUM, empno, ename, hiredate
FROM view_hire;

③ TOP-N, 입사일이 빠른 5명 확인하기

SELECT ROWNUM, empno, ename, hiredate
FROM view_hire
WHERE ROWNUM <= 5;

 

 

인라인 뷰

메인쿼리 SELECT문 FROM 절 내부에 사용된 서브쿼리

* 지금까지 뷰를 CREATE로 만들었다면, 인라인 뷰는 SQL문 내부에 뷰를 정의하고 테이블처럼 사용한다.

예제: 인라인 뷰를 사용하여 급여를 많이 받는 순서대로 3명을 출력

SELECT ROWNUM RANKING, empno, ename, sal
FROM (SELECT empno, ename, sal
      FROM emp_table
      ORDER BY sal DESC)
WHERE ROWNUM <= 3;