SQL - 뷰, 시퀀스, 시노님

뷰(View)

• 테이블의 특정 컬럼 보호
• 복잡한 SQL문을 단순화
• 문법

CREATE [OR REPLACE] VIEW 뷰이름 [(alias[,alias] ...)]
AS 
SUBQUERY
[WITH CHECK OPTION [CONSTRAINT 제약조건명]]
[WITH READ ONLY [CONSTRAINT 제약조건명] ];

 

• 복잡한 SQL문을 단순화 하는 예시

-- 복잡한 SQL문
SELECT empno,ename, d.dname, d.deptno
FROM emp e JOIN dept d
ON e.deptno = d.deptno
WHERE e.deptno = 20;

-- VIEW를 이용해 단순화
CREATE VIEW emp_view
AS
SELECT empno,ename, d.dname, d.deptno
FROM emp e JOIN dept d
ON e.deptno = d.deptno
WHERE e.deptno = 20;       
-- → 에러 : 스캇 계정은 뷰를 생성하는 권한이 없다.

 

위의 문장을 SCOTT 계정에서 실행하면 다음과 같이 ‘권한이 불충분합니다’ 라는 에러가 발생된다.

기본적으로 SCOTT 계정에는 뷰(View)를 생성할 수 있는 권한이 없기 때문이다.

 

SCOTT계정에 뷰(View)를 생성할 수 있는 CREATE VIEW 권한을 설정하기 위하여
관리자 계정으로 로그인하고
다음과 같이 권한 할당 명령어를 실행시킨다.

-- 관리자 계정에서 실행 
GRANT create view 
TO scott;

 

VIEW를 다시 실행하면 이제는 실행이 된다 !

 

 

•  (2) 테이블의 특정 컬럼 보호 목적 예시

-- emp의 sal이 매우 민감한 컬럼이라고 가정
CREATE VIEW emp_view2
AS
SELECT empno,ename,job,mgr,hiredate,comm,deptno
FROM emp; 

select *
from emp_view2;    -- 사용자가 sal이 빠진 정보를 볼 수 있다.

 

• 특징
  • VIEW를 DML 작업 할 수 있다. 원본 테이블이 변경됨.
  • 실제로는 DML 작업이 불가능하도록  with read only 제약조건을 지정한다.
  • user_views 메타 정보 확인 => view에는 base table 관련 subquery만 저장되어 있음. (실제 데이터는 base table에 있다.)

-- DML 불가능하도록 읽기모드 뷰 생성
CREATE OR REPLACE VIEW copy_emp_view2
AS
SELECT *
FROM copy_emp   -- base table
WITH READ ONLY;  -- DML 불가. 특별한 경우 아니면 VIEW 생성시 WITH READ ONLY로 생성해주기! ★★


DELETE FROM copy_emp_view2
WHERE deptno = 20;   -- WITH READ ONLY가 붙었기 때문에 에러 발생

             

 

 

1. 뷰 수정

 

• CREATE OR REPLACE 문을 사용
• 뷰(View)가 존재하면 덮어쓰기가 되고 없으면 새로 생성이 된다.

CREATE OR REPLACE VIEW emp_view2
AS
SELECT empno,ename,job
FROM emp;

 

 

 

2. 뷰 삭제

• 뷰의 삭제는 뷰에 대한 기본 테이블에는 어떠한 영향도 미치지 않는다.
• 따라서 기본 테이블 의 데이터 손실 없이 뷰가 삭제된다.

DROP VIEW 뷰이름;

 

 

 

---

 

 

시퀀스(sequence)

• 호출될 때 마다 자동으로 유일한 숫자를 생성하는 오라클 객체
• 테이블의 특정 컬럼값을 넘버링(numbering)하기 위해서 사용
• 대표적으로 게시판의 글 번호가 순차적인 넘버링값이 필요한 경우

CREATE SEQUENCE 시퀀스명
[ START WITH n]
[ INCREMENT BY n]
[ MAXVALUE n | NOMAXVALUE ]
[ MINVALUE n | NOMINVALUE ] 
[ CYCLE | NOCYCLE ]
[ CACHE n | NOCACHE

 

♦ START WITH n
시퀀스 번호의 시작값을 지정할 때 사용된다. 만일 10 부터 시작되는 시퀀스를 생성하려면 START WITH 10 이라고 지정하면 되고 생략시 1부터 시작된다.

♦ INCREMENT BY n
연속적인 시퀀스 번호의 증가치를 지정할 때 사용된다. 만일 2씩 증가하는 시퀀스를 생성하 려면 INCREMENT BY 2이라고 지정하면 된다. 음수값 설정도 가능하고 생략시 1씩 증가 된다.

♦ MAXVALUE n
시퀀스가 가질 수 있는 최대값을 지정한다.

♦ MINVALUE n
시퀀스가 가질수 있는 최소값을 지정한다. CYCLE인 경우에는 새로 시작하는 값 역할을 한 다.

♦ CYCLE 옵션
지정된 시퀀스 값이 최대값까지 증가가 완료되면 START WITH값부터 다시 시작하는 것이 아니고 MINVALUE 값부터 다시 시작된다. NOCYCLE은 증가가 완료되게 되면 에러가 발 생된다.

♦ CACHE 옵션
성능향상을 위해서 메모리상의 시퀀스 값을 미리 만들어서 필요시 바로 제공하는 방법으로 생략시 기본적으로 20개를 생성해서 관리한다. NOCACHE는 필요할 때마다 매번 시퀀스 값을 계산해서 반환한다. 성능 면에서는 CACHE 옵션을 사용하는 것이 좋으나 데이터베이 스를 종료하고 다시 사용할 경우에는 이전에 생성했던 시퀀스 값을 사용하지 못하게 되어 중간에 비어있는 넘버링이 될 수도 있다.

 

 

 

1. NEXTVAL 과 CURRVAL

• 시퀀스에서 순차적인 시퀀스 값을 가져오는 방법: 시퀀스명.nextval
• 현재 값 확인: 시퀀스명.currval
• 반드시 NEXTVAL을 먼저 호출하고 나중에 CURRVAL을 호출해야 된다.

SELECT dept_deptno_seq.NEXTVAL, dept_deptno_seq.CURRVAL 
FROM dual

 

 

 

 

2. 아무것도 지정하지 않는 경우 : 기본값 설정됨

• 초기값은 1로 시작
• 증가값 1
• 최대값은 1027 
• CYCLE여부는 N
• 성능향상을 위한 캐시값은 기본값으로 20이 설정

 CREATE SEQUENCE 시퀀스명;

 

 

 

 

3. 시퀀스 수정

 

• ALTER SEQUENCE문을 사용하여 증가치,최대값,최소값,CYCLE여부,캐시값 을 변경할 수 있다. 
• 시퀀스가 변경되면 다음 시퀀스 번호값부터 변경사항이 적용
• START WITH옵션은 변경이 불가능하기 때문에 필요시 시퀀스를 삭제하고 재 생성해야 된다.

ALTER SEQUENCE 시퀀스명
[ INCREMENT BY n]
[ MAXVALUE n | NOMAXVALUE ]
[ MINVALUE n | NOMINVALUE ] 
[ CYCLE | NOCYCLE ]
[ CACHE n | NOCACHE ]

 

 

 

4. 시퀀스 삭제

DROP SEQUENCE 시퀀스명;

 

 

 

---

 

 

 

3. 동의어(synonym)

• 데이터베이스 객체에 대한 별칭
• 객체에 대한 접근방법을 단순화
• 보안 문제 해결

CREATE [PUBLIC] SYNONYM 동의어
FOR 스키마.객체;

 

 

-- 동의어 삭제
DROP SYNONYM 시노님명;