Oracle SQL(10)

YangJiWon·2021년 1월 18일

View oracle sequence sql

DB

목록 보기

10/12

뷰(view)

물리적인 테이블 또는 다른 뷰를 기반으로 하는 논리적인 테이블이다.
논리적인 테이블인 이유는 실제 데이터를 저장하고 있지 않으나 사용자는 테이블을 사용하는 것과 동일하게 뷰를 사용할 수 있기 때문이다.
뷰에 대한 기반이 되는 물리적인 테이블을 기본 테이블이라고 한다.

사용하는 목적

DB에서 선택적으로 데이터를 보여줄 수 있기 때문에 DB에 대한 접근을 제한할 수 있다. (보안강화)
결과를 검색하기 위한 복잡한 질의를 단순한 질의로 변경할 수 있다.

--문법
CREATE [OR REPLACE] VIEW 뷰이름 [(alias[, alias] ...)]
AS
서브 쿼리
[WITH CHECK OPTION [ COSTRAINT 제약조건명]]
[WITH READ ONLY [CONSTRAINT 제약조건명]]

CREATE VIEW emp_view
AS
SELECT empno, ename, sal, hiredate
FROM emp
WHERE deptno = 10;

CREATE OR REPLACE 명령어는 뷰가 존재하면 덮어쓰고 존재하지 않으면 새로 생성된다.

앞에 생성했던 뷰의 정보를 출력하기 위해서는 데이터 사전 SQL문을 생성한다.
SELECT *
FROM user_views;

단순 뷰

하나의 기본 테이블에 대해서 정의한 뷰로서 기본적으로 INSERT, UPDATE, DELETE와 같은 DML문이 실행 가능하다.
단순 뷰에 대해서 실행한 DML문의 처리 결과는 실제로 기본 테이블에 반영이 된다.

CREATE VIEW emp_view3 (사원번호, 이름, 월급)
AS
SELECT empno, ename, sal
FROM emp
WHERE deptno = 20;

-- 뷰를 생성하면서 함수를 사용하는 경우에는 반드시 컬럼 병칭을 지정해야 한다.
-- 별칭을 지정하지 않으면 에러 메시지가 출력되면서 에러가 발생된다.
CREATE VIEW emp_view4
AS
SELECT deptno, SUM(sal)
FROm emp
GROUP BY deptno;

-- 이렇게 되면 에러 없이 뷰가 생성된다.
CREATE VIEW emp_view4
AS
SELECT deptno, SUM(sal) 총합
FROm emp
GROUP BY deptno;

-- 기본테이블도 같이 삭제된다.
DELETE FROM emp_view5
WHERE deptno = 10;

단순 뷰의 DML 불가
모든 상황에서 단순 뷰의 DML 작업이 가능한 것은 아니다. 단순 뷰가 그룹함수, GROUP BY, DISTINCT 같은 표현식을 포함한 경우에는 DML 작업이 불가능하다.

복합 뷰

복합 뷰는 두개 이상의 기본 테이블에 대해서 정의한 뷰이다.
조인을 사용할 경우 쉽게 복합 쿼리를 진행할 수 있다.

WITH CHECK OPTION

테이블은 데이터의 무결성을 보장하기 위해서 기본 키 및 NOT NULL 제약조건 등을 설정한다.
마찬가지로 뷰도 WHERE = 328에 만족하는 데이터만 INSERT 또는 UPDATE가 가능하도록 제약조건을 설정할 수 있다.

CREATE OR REPLACE VIEW emp_view6
AS
SELECT empno, ename, sal, deptno
FROM emp
WHERE deptno = 30
WITH CHECK OPTION;

-- WITH CHECK OPTION의 제약조건 때문에 에러가 발생된다.
-- 부서번호가 30인 조건으로만 변경을 할 수 있다.
UPDATE emp_view6
SET deptno = 40
WHERE empno = 7521;

WITH READ ONLY

뷰를 통한 DML 작업은 불가능하도록 설정하는 방법이다.

CREATE OR REPLACE VIEW dept_view
AS
SELECT * FROM DEPT
WHERE deptno = 10
WITH READ ONLY;

-- WITH READ ONLY 제약조건으로 DML 실행이 되지 않는다.
DELETE FROM dept_view
WHERE deptno = 10;

뷰 삭제

생성된 뷰를 삭제하는 문법은 이렇다.

DROP VIEW 뷰이름;

시퀀스

시퀀스 객체는 호출될 때 마다 유일한 숫자를 생성하는 오라클 객체로서 테이블의 특정 컬럼값을 넘버링하기 위해서 사용된다.
대표적으로 게시판의 글 번호가 순차적인 넘버링값이 필요한 경우이다.
여러 사용자들이 공유하는 DB 객체로서, 호출 될 때마다 중복되지 ㅇ낳은 고유한 숫자를 리턴하는 객체이다.

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

START WITH n : 시퀀스 번호의 시작 값(n)을 지정할 때 사용된다.
INCREMENT BY n : 연속적인 시퀀스 번호의 증가치(n)를 지정할 때 사용된다.
MAXVALUE n : 시퀀스가 가질 수 있는 최대값을 지정한다.
MINVALUE n : 시퀀스가 가질 수 있는 최소값을 지정한다. CYCLE인 경우에는 새로 시작하는 값 역할을 한다.
CYCLE 옵션 : 지정된 시퀀스 값이 최대값까지 증가가 되면 START WITH 값부터 다시 시작하는 것이 아니라 MINVALUE 값부터 다시 시작한다. NO CYCLE은 최대보다 증가하게 되면 에러가 발생된다.
CACHE 옵션 : 성능향상을 위해서 메모리상의 시퀀스 값을 미리 만들어서 필요시 바로 제공하는 방법으로 생략 시 기본적으로 20개를 생성해서 관리한다. NOCACHE는 필요할 때마다 매번 시퀀스 값을 계산해서 반환한다.

성능면에서는 CACHE를 사용하는 것이 좋으나 DB를 종료하고 다시 사용할 경우에는 이전에 생성했던 시퀀스 값을 사용하지 못하게 되어 중간에 비어있는 넘버링이 될 수도 있다.

CREATE SEQUENCE emp_seq
INCREMENT BY 1
START WITH 100
MAXVALUE 9999
NOCACHE
NOCYCLE;

CREATE SEQUENCE dept_deptno_seq2
START WITH 100
INCREMENT BY -10
MAXVALUE 150
MINVALUE 10
CYCLE
NOCACHE;

-- INCREMENT가 음수면 CYCLE일 때 MAXVALUE로 바뀐다.
SELECT dept_deptno_seq2.NEXTVAL, dept_deptno_seq2.CURRVAL
FROM dual;

유저가 만든 시퀀스를 확인하는 방법
SELECT sequence_name
FROM user_sequences;

NEXTVAL, CURRVAL

시퀀스 값을 얻기 위해서는 반드시 시퀀스 객체를 호출해야 되는데, 시퀀스명.NEXTVAL 형식을 사용하면 지정된 시퀀스에서 순차적인 시퀀스 값을 얻어오게 된다.
NEXTVAL 값을 호출할 때마다 시퀀스 생성 시 지정했던 옵션들에 의해서 다음 시퀀스 값이 결정되어 반환된다.
현재 생성된 시퀀스 값을 조회하기 위해서는 스퀀스명.CURRVAL 형식을 사용하면 된다.
주의할 점은 반드시 NEXTVAL을 먼저 호출하고 나중에 CURRVAL을 호출해야 된다.

SELECT dept_deptno_seq.NEXTVAL, dept_deptno_deq.CURRVAL
FROM dual;

시퀀스 수정

시퀀스 수정은 ALTER SEQUENCE 문을 사용하여 증가치, 최대값, 최소값, CYCLE여부, 캐시 값을 변경할 수 있다.

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

ALTER SEQUENCE dept_deptno_seq3
INCREMENT BY 10
CYCLE
NOCACHE;

START WITH 옵션 변경 불가

-- START WITH 옵션 변경 불가때문에 에러 발생
ALTER SEQUENCE dept_deptno_seq3
START WITH 10
INCREMENT BY 10
CYCLE
NOCACHE;

테이블에 시퀀스 값 저장


CREATE TABLE dept06
(
deptno number(4) primary key,
dname varchar2(15),
loc varchar2(15)
);

CREATE SEQUENCE dept_deptno_seq4
INCREMENT BY 10
START WITH 10
NOCYCLE
NOCACHE;

INSERT INTO dept06  VALUES(dept_deptno_seq4.NEXTVAL, '개발', '서울');
INSERT INTO dept06  VALUES(dept_deptno_seq4.NEXTVAL, '인사', '서울');

SELECT * FROM dept06;

시퀀스 삭제

DROP SEQUENCE 시퀀스명;
  
DROP SEQUENCE dept_deptno_seq4;

동의어(synonym)

DB 객체에 대한 별칭으로서 객체에 대한 접근방법을 단순화 시킬 수 있다.
일반적으로 다른 사용자의 객체에 접근하기 위해서는 '스키마.객체'형식으로 반드시 스키마를 지정하여 객체의 소유자가 누구인지를 알려야 된다.
동의어를 사용하면 객체에 대한 접근을 단순화할 수 있고 보안 문제도 해결할 수 있다.

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

CREATE SYNONYM d_syn
FOR dept;

동의어는 PRIAVTE용과 PUBLIC용으로 구분되는데 PUBLIC은 모든 사용자가 사용할 수 있고 PRIVATE는 동의러를 만든 사용자만 사용이 가능하다.

동의어 삭제

DROP SYNONYM 시노님명;

YangJiWon

데이터데이터데이터!!

이전 포스트

Oracle SQL(9)

다음 포스트

Oracle SQL(10)

DB

뷰(view)

사용하는 목적

단순 뷰

복합 뷰

WITH CHECK OPTION

WITH READ ONLY

뷰 삭제

시퀀스

NEXTVAL, CURRVAL

시퀀스 수정

테이블에 시퀀스 값 저장

시퀀스 삭제

동의어(synonym)

동의어 삭제

Oracle SQL(9)

Oracle SQL(11)

0개의 댓글