KH 52일차

KH 정보교육원 [ Java ]

KH 52일차 - View

monimoni 2022. 5. 11. 22:31

-- ------------------------------------------------------

-- 1. View

-- ------------------------------------------------------

-- 가. 물리적인 테이블 or 다른 View를 기반으로 하는 논리적인 테이블(*)

-- 나. 물리적인 테이블처럼, 실제 데이터를 저장하지 않음

-- 다. 사용자는 마치 진짜 테이블을 사용하는 것과 동일하게 사용

-- 라. 기본테이블(Base table):

-- 뷰의 기반의 되는 물리적인 테이블

-- 마. 목적

-- a. 데이터를 선택적으로 보여줄 수 있음(**)

-- b. 데이터에 대한 접근을 제한할 수 있음(접근제한) (*)

-- c. 테이블 컬럼 중, 보안과 관련된 민감한 데이터를 가진

-- 컬럼들에 대한 접근을 제한 -> 보안 강화

-- d. 검색위한 복잡한 쿼리를 단순쿼리로 변경 (*)

-- 일반적으로 조인쿼리문은 복잡성을 가짐

-- 매번 필요시, 같은 조인쿼리를 사용하지 않고, 뷰로 작성

-- + View는 가상의 뷰로, 실제로는 쿼리에 해당한다.

-- ------------------------------------------------------

-- Basic syntax:

-- CREATE [OR REPLACE] VIEW 뷰이름 [ (alias1, alias2, ...) ]

-- AS

-- Sub-query

-- [ WITH CHECK OPTION [ CONSTRAINT 제약조건명 ] ]

-- [ WITH READ ONLY [ CONSTRAINT 제약조건명 ] ] ;

-- * (alias1, alias2, ...):

-- a. 서브쿼리가 반환한 컬럼들에 대한 별칭(Alias) 지정 (*)

-- b. 생략가능:

-- 기본테이블(Base tables)의 컬럼명 또는 서브쿼리의 SELECT절의 컬럼별칭 사용

-- * Sub-query:

-- a. Join, Set, Complicated DQL문장 등의 정의 가능

-- * View 의 수정:

-- a. 테이블처럼, ALTER 문을 사용하지 않음

-- b. 새로운 View를 재생성하여, 기존 View를 덮어쓰는 방식으로 처리

-- c. CREATE OR REPLACE 명령 사용

-- 기존 View가 존재하면 덮어쓰고, 없으면 새로이 생성 의미

-- ALTER View가 아니다.

-- * WITH READ ONLY 제약조건

-- a. Read-only mode (읽기전용) 모드로 변경

-- b. View 데이터에 대한 DML 작업불가

-- * WITH CHECK OPTION 제약조건

-- a. 특정 조건과 일치해야 동작하게 함

-- ------------------------------------------------------

-- 1-1. View 가 필요한 이유 예시

-- ------------------------------------------------------

-- (1) 20번 부서에 속한 사원들의 정보검색을 위한 복잡한 Join 쿼리문

-- 매번 사원정보 검색을 위해, 아래의 Join 쿼리를 수행해야 한다.

SELECT

empno,

ename,

d.dname,

d.deptno

FROM

emp e JOIN dept d

ON e.deptno = d.deptno -- 동등조인

WHERE

e.deptno = 20;

-- ANSI JOIN으로 조인하여 부서번호가 20번인 부서의 해당 컬럼들을 출력하고 있다.

-- 해당 JOIN은 INNER가 생략된 INNER JOIN에 해당한다.

-- 서브쿼리로도 작성할 수 있다. (서브쿼리를 하는 것이 더 간단하다.)

-- (2) View 는 위의 복잡한 쿼리를, 매우 단순한 SQL문으로 처리할 수

-- 있도록 지원한다. (위의 Join쿼리를 View로 정의)

-- CREATE VIEW 권한 필요

-- 현재의 SCOTT 계정은 View를 생성할 권한이 없음 -> 관리자가 권한을 부여해야 함

-- ORA-01031: insufficient privileges

CREATE OR REPLACE 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;

-- 권한이 주어지지 않으면

-- CREATE VIEW 권한 부여 (***)

-- GRANT <부여할 시스템 권한명> TO <권한을 부여할 계정명>

GRANT CREATE VIEW TO scott;

-- 권한 설정은 sysdba 계정인 admin에서 실행해야 한다. (일반계정은 불가능)

-- ------------------------------------------------------

-- CREATE VIEW 시스템 권한 할당받은 후, View 생성 재시도

-- View 생성시, 컬럼별칭(Alias)를 사용하지 않으면,

-- 기본테이블(Base tables)의 컬럼명 또는 서브쿼리의 SELECT절에 사용된 컬럼별칭이 사용된다.

DROP VIEW emp_view;

-- ------------------------------------------------------

-- CREATE VIEW emp_view AS

CREATE OR REPLACE VIEW emp_view AS

SELECT

empno as eno, -- OK : 컬럼별칭(column alias) 사용가능

empno, -- 기본테이블(base table) 컬럼

ename,

d.dname,

d.deptno

FROM

emp e JOIN dept d

ON e.deptno = d.deptno

WHERE

e.deptno = 20;

ORDER BY -- OK: ORDER BY 절이 사용가능하다!!

1 DESC;

SELECT *

FROM emp_view;

-- ------------------------------------------------------

CREATE OR REPLACE VIEW emp_view (col1, col2, col3, col4) AS

SELECT

empno as eno, -- OK : 컬럼별칭(column alias) 사용가능? Yes!!!

ename,

d.dname,

d.deptno

FROM

emp e JOIN dept d

ON e.deptno = d.deptno

WHERE

e.deptno = 20

ORDER BY -- OK : ORDER BY 절이 사용가능한가? Yes!!!

1 DESC;

DESC emp_view;

-- ------------------------------------------------------

-- emp_view 뷰(view) 구조 확인

DESC emp_view; -- 생성된 View 객체의 스키마 확인

-- emp_view 뷰(view)에서 데이터 검색

SELECT

FROM

emp_view;

-- ------------------------------------------------------

-- (3) 보안강화 : 데이터에 대한 접근제한을 위한 View

-- 기본테이블(Base table)에 저장된 특정 컬럼의 데이터를

-- 보호할 목적으로 View 사용가능 (***)

-- emp 테이블에서 월급(sal) 컬럼은 민감한 정보 저장 -> 접근제한필요

CREATE VIEW emp_view2 AS

SELECT

empno,

ename,

job,

mgr,

hiredate,

comm,

deptno

FROM

emp;

-- 이렇게 작성했을 시에는 모든 컬럼을 공개하겠다는 의미이다.

-- emp_view 뷰(view) 구조 확인

DESC emp_view2; -- 생성된 View 객체의 스키마 확인

SELECT

FROM

emp_view2;

-- emp_view2 뷰(view)에서 데이터 확인

-- ------------------------------------------------------

-- 1-2. View 객체정보 관리 데이터 딕셔너리(Data Dictionary)

-- ------------------------------------------------------

-- View 를 사용한 SELECT 문 실행시, USER_VIEWS 데이터 사전에

-- 저장된 해당 View 객체의 Sub-query 문(TEXT 컬럼)이 실행되어

-- 결과값 반환

-- ------------------------------------------------------

-- USER_VIEWS 데이터 사전

DESC user_views;

SELECT

view_name, -- 생성된 View의 이름

text, -- View 생성시 지정한 서브쿼리문 (***)

read_only -- 읽기전용여부(WITH READ ONLY 제약조건 적용시)

FROM

user_views;

-- user_views 데이터 사전의 칼럼들을 뽑아내었다.

-- 확인해보면 생성한 뷰의 별칭과, 해당 뷰의 쿼리 내용을 알려준다.(뷰는 가상의 테이블이지만 사실상 쿼리)

-- ------------------------------------------------------

-- 1-3. View 객체의 수정(ALTER)

-- ------------------------------------------------------

-- 가. 테이블 객체의 수정 -> ALTER TABLE 문 사용

-- 나. View 객체의 수정 -> CREATE OR REPLACE 문 사용

-- 기존에 View 가 존재하면 Overwrite, 없으면 Create

-- 다. 기존의 View 객체 수정시, CREATE OR REPLACE VIEW 문을

-- 사용하지 않고, CREATE VIEW 문을 사용하면 오류발생

-- ------------------------------------------------------

-- 기존 View 객체의 수정

-- CREATE VIEW emp_view2 AS -- ORA-00955: name is already used by an existing object

CREATE OR REPLACE VIEW emp_view2 AS

SELECT

empno,

ename,

job,

mgr,

comm,

deptno

FROM

emp;

-- 기존의 emp_view2 뷰를 OR REPLACE를 통해 수정하였다.

-- ------------------------------------------------------

-- 1-4. View 의 종류 (2가지)

-- ------------------------------------------------------

-- 가. 단순 View (= Simple View)

-- a. 하나의 기본 테이블(Base table) 에 대해 정의한 View (*)

-- b. By defaujlt, View 에 대해 DML 문장 실행 가능

-- c. View 에 대한 DML 문장의 처리결과는,

-- 실제 기본테이블(Base table)에 반영됨

-- d. 새로 생성되는 View에 대해서 별칭(Alias)를 사용하지

-- 않으면, 기본테이블(Base table)의 컬럼명을 상속하거나

-- 서브쿼리(Sub-query)의 SELECT절에 기술된 컬럼별칭(column alias) 상속

-- 나. 복합 View (= Complex View)

-- a. 2개 이상의 기본테이블(Base table)에 대해 정의한 뷰 (*)

-- b. 2개 이상의 테이블을 Join해서 사용할 경우, 뷰로 생성

-- ------------------------------------------------------

-- (1) Simple View 생성

-- ------------------------------------------------------

-- 20번 부서의 사원들의 정보를 가진 View 객체 생성

-- View 이름 뒤에, 컬럼별칭 목록을 지정하면, 지정된 별칭으로

-- View 의 컬럼명이 지정됨

-- 기본 테이블의 컬럼명을 상속받지 않고, 명시한 컬럼별칭(alias)

-- 으로 출력

CREATE VIEW emp_view3 (사원번호, 이름, 월급) AS -- Alias 사용

SELECT

empno,

ename,

sal

FROM

emp

WHERE

deptno = 20;

-- 해당 컬럼들을 지정한 별칭으로 결과셋으로 보여준다. (**)

-- ------------------------------------------------------

-- (1-1) 예시: 부서별 급여총합 계산 (SUM 집계함수 사용) (***)

-- ------------------------------------------------------

-- (*주의*) View 생성시, 함수를 사용하는 경우에는 **반드시**

-- 컬럼별칭(Column Alias) 를 지정하지 않으면, 오류발생

-- ------------------------------------------------------

-- SUM함수 이용한 뷰(view) 생성 에러

-- ORA-00998: must name this expression with a column alias

CREATE OR REPLACE VIEW emp_view4 (부서번호, 급여 ) AS

SELECT

deptno,

sum(sal) AS 총합

FROM

emp

GROUP BY

deptno;

-- 값을 만들어내는 식인 연산식은 해당 컬럼에 별칭을 지어야 실행이 된다.(***)

-- 위처럼 ( )를 사용하지 않아도, 위와 같이 as로 별칭을 지정해줄 수 있다.

-- 또한 ( )를 통해 뷰와 컬럼의 별칭을 수정할 수도 있다.

DESC emp_view4;

-- 별칭이 잘 지어졌는지 확인

-- ------------------------------------------------------

-- (1-2) Simple View 데이터에 대한 DML 수행

-- ------------------------------------------------------

-- 가. Simple View 객체로 생성시, 데이터에 대한 DML 가능

-- 나. 실제로는 기본테이블(Base table)에 DML 변경반영

-- 다. DML 수행이 불가능한 경우: (******)

-- a. Simple View 에,

-- GROUP BY / 집계함수 / DISTINCT 키워드가 사용된

-- 경우에는, DML 변경 불가!!!! (책)

-- b. DISTINCT 키워드 사용 테스트결과 ->

-- DML 변경가능!!!( 실제 테스트 **)

-- ------------------------------------------------------

CREATE VIEW emp_view5 AS

SELECT

empno,

ename,

sal,

deptno

FROM

emp;

-- 심플뷰에서 가능한 것들 확인을 위한 뷰 생성

-- 단순 뷰(Simple View) 데이터에 대한 DELETE 문

DELETE FROM emp_view5

WHERE deptno = 10;

-- emp_view5 뷰(view)에서 데이터 검색

SELECT

count(*)

FROM

emp_view5

WHERE

deptno = 10;

-- 결과를 확인하여 실제로 삭제시켰는지 확인해보기 (결과 0)

-- 기본 테이블(Base table)에서 데이터 검색

SELECT

count(*)

FROM

emp

WHERE

deptno = 10;

-- 결과를 확인하여 실제로 삭제시켰는지 확인해보기 (결과 0)

ROLLBACK;

-- vscode는 자동으로 commit을 날리기에 조심해야 한다.(**)

SET AUTOCOMMIT OFF;

-- 이를 통해서 자동으로 commit되는 것을 종료할 수 있다.

-- ------------------------------------------------------

CREATE OR REPLACE VIEW emp_view5 AS

SELECT

DISTINCT -- DISTINCT 사용해봄

empno,

ename,

sal,

deptno

FROM

emp;

-- DISTINCT 키워드를 사용하여, 뷰를 수정해 보았다. ( DISTINCT가 정상작동되고 있고, 잘 지워지고 있음을 알 수 있다. )

-- 단순 뷰(Simple View) 데이터에 대한 DELETE 문

DELETE FROM emp_view5

WHERE deptno = 20;

-- ------------------------------------------------------

CREATE OR REPLACE VIEW emp_view5 (deptno, 부서원수)AS

SELECT

deptno,

count(*)

FROM

emp

GROUP BY

deptno;

-- count 함수를 사용하여 각 부서의 직원수를 구하는 뷰로 수정하였다.

-- 단순 뷰(Simple View) 데이터에 대한 DELETE 문

-- 에러발생: ORA-01732: data manipulation operation not legal on this view

DELETE FROM emp_view5

WHERE deptno = 20;

-- 오류가 발생하면서 삭제가 안되고 있음을 알 수 있다.

-- 원래 데이터가 GROUP BY로 묶어져 있지도 않기에 삭제가 불가능하다.

COMMIT;

ROLLBACK;

-- ------------------------------------------------------

-- (2) Complex View 생성

-- ------------------------------------------------------

CREATE OR REPLACE VIEW complex_view AS

SELECT

t2.department_name AS 부서명,

count(t1.employee_id) AS 인원수

FROM

employees t1,

departments t2

WHERE

t1.department_id = t2.department_id -- 동등조인조건

GROUP BY

t2.department_name;

-- 해당 테이블은 SCOTT계정이 아니라 HR계정으로 접속해야 한다.

SET AUTOCOMMIT ON;

-- ------------------------------------------------------

-- (3) WITH CHECK OPTION 제약조건

-- ------------------------------------------------------

-- 가. 테이블은 무결성을 유지하기 위해, PK/NN 등의 제약조건 설정

-- 나. 마찬가지로, View도 WHERE 조건을 만족하는 데이터만,

-- INSERT/UPDATE 가 가능하도록 제약조건 설정가능

-- 다. 뷰에 대한 DML 작업이 수행되는 것을, WHERE 조건에 일치하는

-- 데이터만 변경가능하도록 제약하는 방법!!!

-- ------------------------------------------------------

-- 30번 부서의 사원정보출력 Simple View

CREATE OR REPLACE VIEW emp_view6 AS

SELECT

empno,

ename,

sal,

deptno

FROM

emp

WHERE

deptno = 30;

-- 부서번호가 30번인 부서의 칼럼을 출력하였다.

UPDATE emp_view6

SET

deptno = 40

WHERE

empno = 7499;

-- 사원번호가 7499인 사원을 부서번호 40번로 변경한다.

-- 뷰뿐만이 아니라 원래 데이터 에서도 변경이 된다. (**)

SET AUTOCOMMIT OFF;

ROLLBACK;

-- ------------------------------------------------------

-- 30번 부서의 사원정보출력 Simple view with check option

-- 30번 부서의 사원만 변경(DML) 가능하도록 제약조건 설정

-- WITH CHECK OPTION 이용한 뷰(view) 생성

CREATE OR REPLACE VIEW emp_view6 AS

SELECT

empno,

ename,

sal,

deptno

FROM

emp

WHERE

deptno = 30

WITH CHECK OPTION;

-- 뷰의 where 조건을 깨트리는 변경은 받아들이지 않는다.

-- deptno의 값을 변경할 수 없다.(****)

-- 다른 컬럼의 값의 경우에는 변경할 수 있다. (**)

-- ------------------------------------------------------

-- (4) WITH READ ONLY 제약조건

-- ------------------------------------------------------

-- 가. 뷰에 대한 DML 작업을 불가능하게 함

-- View를 통한, 어떠한 기본 테이블(Base table) 변경도 불가능

-- 나. 읽기전용(read-only) 상태로 View 생성

-- 다. **제약조건의 제거** --> 다시 새로운 View 를 생성하여 대체

-- ------------------------------------------------------

-- WITH READ ONLY 이용한 뷰(view) 생성

CREATE OR REPLACE VIEW emp_view6 AS

SELECT

empno,

ename,

sal,

deptno

FROM

emp

WITH READ ONLY;

-- 모든 컬럼들의 값을 변경할 수 없다. (읽기 전용이다.) (***)

-- ------------------------------------------------------

-- 1-5. View 의 삭제

-- ------------------------------------------------------

-- 가. 기본 테이블(Base table)에는 영향을 주지 않음

-- 나. 기본 테이블(Base table) 손실 없이 삭제

-- 다. View의 삭제 --> 곧, USER_VIEWS 데이터 사전에 저장된

-- TEXT 컬럼에 저장되어 있는, 서브쿼리의 삭제

-- ------------------------------------------------------

-- Basic Syntax:

-- DROP VIEW <삭제할 View객체의 이름>;

-- ------------------------------------------------------

-- View 삭제

DROP VIEW emp_view6;

-- 원래의 테이블은 삭제되지 않는다.(*)

COMMIT;

-- ------------------------------------------------------

-- 2. Sequence 객체 : 순차번호(Sequential number) 생성기 ( 인조키, 대리키 )

-- ------------------------------------------------------

-- 가. 호출될 때마다, 자동으로 숫자를 생성하는 Oracle 객체

-- 나. 테이블의 특정 컬럼의 값을 Numbering 할 때 사용

-- 예: 웹 게시판의 글 번호

-- 다. 만일 시퀀스를 사용하지 않는다면, 직접 명시적으로,

-- 현재 컬럼의 가장 큰값(max) + 1 씩, 더 크게 설정하기

-- 위한 추가 작업이 필요(응용프로그램에서)

-- ------------------------------------------------------

-- Basic syntax: 아래 지정 옵션의 순서는 무관(But 가장 일반적)

-- CREATE SEQUENCE 시퀀스명

-- [ START WITH n ]

-- 순차번호의 시작값 지정. 생략시 1부터 시작

-- [ INCREMENT BY n ]

-- 연속적인 순차번호의 증가치 지정.

-- 음수도 가능 (감소치), 생략시 1씩 증가

-- [ MAXVALUE n | NOMAXVALUE ]

-- 시퀀스 객체가 생성할 수 있는 순차번호의 최대값 지정

-- [ MINVALUE n | NOMINVALUE ]

-- 시퀀스 객체가 생성할 수 있는 순차번호의 최소값 지정

-- 아래 CYCLE 옵션이 지정된 경우, 새로 시작하는 값 역할(최댓값에 도달하면 START로 가는 것이 아니라 MINVALUE로 온다. *)

-- [ CYCLE | NOCYCLE ]

-- CYCLE일 경우 시퀀스 객체가 최대값(MAXVALUE) 까지 증가한 경우,

-- START WITH 값 부터 재시작 하는 것이 아니라, MINVALUE 값 부터 재시작.

-- ** NOCYCLE 은 최대값까지 도달하게 되면, 에러가 발생 **

-- [ CACHE n | NOCACHE ]

-- 성능향상을 위해, 메모리 상에 미리 순차번호를

-- 기본으로 20개까지 미리 생성하여 관리. ( 효율성 )

-- (*주의사항*) DB를 종료 했다가, 재시작하면, 이전에 미리

-- 생성하였었던, 메모리에 있는 최대 20개의 순차번호는 재사용불가(**)

-- 그렇기때문에 CACHE를 사용할 시에는 데이터가 다소 일관성있지 않게 나올수 있다.

-- 그럴지라도 CACHE를 사용하는 편이 좋다. (**)

-- NOCACHE는 , 필요할 때 마다, 매번 순차번호 계산하여 반환

-- ------------------------------------------------------

-- 2-1. 시퀀스 객체 생성

-- ------------------------------------------------------

-- 부서번호 자동생성 시퀀스 객체 생성

CREATE SEQUENCE dept_deptno_seq0

START with 10

INCREMENT BY 10

MAXVALUE 100

MINVALUE 5

CYCLE

NOCACHE;

-- CREATE SEQUENCE dept_deptno_seq0;만 실행했을 때에는 모든 옵션이 디폴트 값으로 돌아가게 된다.

-- 옵션을 작성하게 되면, 해당 옵션에 따라서 시퀀스가 생성되게 된다.

-- DESC는 테이블에서만 가능하기에 시퀀스는 보여주지 않는다.

-- 확인해보는 방법은 디벨로퍼에서 시퀀스를 확인해보면 된다.(*)

-- ------------------------------------------------------

-- 2-2. 시퀀스 객체의 NEXTVAL / CURRVAL 속성

-- ------------------------------------------------------

-- 가. 시퀀스 객체생성 -> 자동으로 순차번호가 생성되는 것이 아님

-- 나. 순차번호를 얻기 위해서는, *반드시 * 시퀀스 객체를 호출해야 함

-- 다. 시퀀스 객체의 호출형식:

-- "시퀀스객체명.NEXTVAL" -> 다음 순차번호 생성 및 획득

-- "시컨스객체명.CURRVAL" -> 현재 생성된 순차번호 조회

-- 라. (*주의할 점*)

-- 반드시 NEXTVAL 먼저 호출, 나중에 CURRVAL 호출해야 함 (***)

-- ------------------------------------------------------

-- 가장 간단한 순차번호 값 확인 방법 : DUAL (Dummy table) 사용

-- 증가를 위한 NEXTVAL과 CURRVAL 사용

SELECT

dept_deptno_seq0.NEXTVAL, -- 다음 번호 생성

dept_deptno_seq0.CURRVAL -- 그 후의 현재 번호

FROM

dual;

-- 더미 테이블에서 계속 다음 시퀀스를 보여준다.

-- 실행할수록 시퀀스의 번호가 증가하고 있음을 알 수 있다.

-- NEXTVAL 다음에 CURRVAL을 사용해야 한다. (**)

-- 음수 값을 이용한 시퀀스 생성

CREATE SEQUENCE dept_deptno_seq2

START WITH 100

INCREMENT BY -10

MAXVALUE 150

MINVALUE 10

CYCLE

NOCACHE;

-- 번호 100에서 -10씩 하는 시퀀스를 생성해 보았다.

SELECT

dept_deptno_seq2.NEXTVAL, -- 다음 번호 생성

dept_deptno_seq2.CURRVAL -- 그 후의 현재 번호

FROM

dual;

-- 이때에는 점점 감소하다가 최솟값에 도달하면 MAXVALUE로 돌아간다. (***)

-- ------------------------------------------------------

-- 2-3. USER_SEQUENCES 데이터 사전

-- ------------------------------------------------------

-- 가. 시퀀스 객체 정보를 저장하는 데이터 사전

-- 나. 돌이켜 보면,

-- a. USER_TABLES : 테이블에 대한 데이터 사전

-- b. USER_VIEWS : 뷰(View)에 대한 데이터 사전

-- c. USER_SEQUENCES : 시퀀스에 대한 데이터 사전

-- ------------------------------------------------------

DESC user_sequences;

-- DEPT_DEPTNO_SEQ2 시퀀스 데이터 사전 조회

SELECT

FROM

user_sequences

WHERE

sequence_name = 'DEPT_DEPTNO_SEQ2';

-- 해당 시퀀스의 이름은 대문자로 작성해야 한다.(***)

-- ------------------------------------------------------

-- 2-4. 시퀀스(Sequence) 객체의 수정

-- ------------------------------------------------------

-- 가. ALTER SEQUENCE 문 사용

-- 나. 증가치/최대값/최소값/CYCLE여부/캐시값 등 변경가능

-- "START WITH 시작값"은 변경불가!!! (******)

-- 다. 시퀀스 변경후, 다음 순차번호 생성부터 적용(*)

-- 라. "START WITH 시작값"의 변경을 꼭 해야한다면?

-- 기존 시퀀스를 삭제하고 재생성 해야한다!! (**)

-- ------------------------------------------------------

-- Basic syntax:

-- ALTER SEQUENCE 시퀀스명

-- [ INCREMENT BY n]

-- [ MAXVALUE n | NOMAXVALUE ]

-- [ MINVALUE n | NOMINVALUE ]

-- [ CYCLE | NOCYCLE ]

-- [ CACHE n | NOCACHE ]

-- * START WITH 값은 수정불가!!!

-- ------------------------------------------------------

-- 시퀀스 생성 (명시적으로 옵션 설정 안함***)

CREATE SEQUENCE dept_deptno_seq3;

-- 디폴트 값으로 시퀀스를 생성해 준다.

COMMIT;

-- DEPT_DEPTNO_SEQ3 시퀀스 수정

ALTER SEQUENCE dept_deptno_seq3

INCREMENT BY 10

CYCLE;

-- 디폴트값으로 지정된 시퀀스를 다음과 같은 조건으로 변경해 준다.

-- ------------------------------------------------------

-- 2-5. 테이블에 시퀀스 값(순차번호) 저장

-- ------------------------------------------------------

-- 가. 시퀀스는 테이블의 특정 컬럼값을 넘버링(numbering)하기

-- 위한 용도로 사용(*잊지 말 것!*)

-- 나. 따라서 테이블에 데이터를 저장하는 INSERT 문과 함께 사용

-- ------------------------------------------------------

CREATE TABLE dept06(

deptno NUMBER(4) PRIMARY KEY,

dname VARCHAR2(15),

loc VARCHAR2(15)

);

CREATE SEQUENCE dept_deptno_seq4

START WITH 10

INCREMENT BY 10

NOCYCLE;

-- 부서번호 자동생성용 시퀀스 객체를 생성하였다.

-- 시퀀스를 이용한 새로운 행 삽입(***)

INSERT INTO dept06(deptno, dname, loc )

VALUES (

dept_deptno_seq4.NEXTVAL,

'개발',

'서울'

);

-- 부서번호가 10씩 증가하여 하나씩 데이터가 들어간다.

COMMIT;

-- ------------------------------------------------------

-- 2-6. 시퀀스 객체 삭제

-- ------------------------------------------------------

-- 가. DROP SEQUENCE 문 사용

-- ------------------------------------------------------

-- Basic syntax:

-- DROP SEQUENCE <삭제할 시퀀스명>;

-- ------------------------------------------------------

DROP SEQUENCE dept_deptno_seq4;

-- ------------------------------------------------------

-- 3. Synonym (동의어) ( = 별칭 )

-- ------------------------------------------------------

-- 가. DB 객체에 대한 별칭 (**)

-- 나. DB 객체에 대한 접근방법을 단순화 시킴

-- 다. 다른 사용자의 객체 접근 -> "스키마.객체" 형식으로 접근 ( ex. scott.dept06 (= 계정.테이블명) )

-- a. 반드시 스키마를 지정하여, 객체의 소유자가 누구인지 알려야 함

-- b. 스키마를 지정하지 않으면, 자신의 스키마(계정) 내에서 객체를

-- 찾음 --> 오류발생 가능

-- c. 객체이름이 길거나, 스키마를 직접 명시하여 보안에 문제

-- 발생가능

-- 라. 동의어(Synonym) 사용 시,

-- a. 객체 접근 단순화 가능

-- b. 보안문제도 해결 가능

-- 마. "스키마.객체"에 대한 별칭

-- a. 스키마 : 객체를 소유한 사용자명

-- b. 객체 : 동의어를 만들려는 DB객체 이름

-- 바. 구분 :

-- (1) private

-- a. 동의어를 만든 사용자만 사용가능

-- b. 일반 계정으로 생성/삭제 가능

-- (2) public

-- a. 모든 사용자가 사용가능

-- b. DBA권한 가진 관리자만이 생성/삭제 가능

-- ex) DUAL -> SYS.DUAL

-- 사. DB 객체의 소유권

-- a. 해당 객체를 생성한 사용자

-- b. 다른 사용자가 소유한 객체에 접근 -> 반드시 접근권한이 필요

-- ------------------------------------------------------

-- Basic Syntax:

-- CREATE [PUBLIC] SYNONYM 동의어

-- FOR 스키마.객체 ;

-- ------------------------------------------------------

-- 3-1. Synonym (동의어) 생성 및 활용

-- ------------------------------------------------------

-- HR 계정: employees 테이블에 대한,

-- select 객체권한을 scott 계정에 부여

-- ------------------------------------------------------

GRANT select

ON employees

TO scott;

-- HR계정으로 자신이 가지고 있는 테이블을 scott계정에게 권한을 부여한다.

-- ------------------------------------------------------

-- **SCOTT 계정**: HR계정 소유의 employees 테이블에, 스키마를 사용하여 접근가능

-- ------------------------------------------------------

SELECT

FROM

hr.employees;

-- 접근 권한이 있다라 하더라도, 계정명.을 작성하지 않으면 현재 계정에서 테이블을 찾게 된다.

-- ------------------------------------------------------

-- HR스키마를 지정하지 않고, employees 테이블에 접근하기 위해,

-- SCOTT 계정에서, Synonym(동의어) 생성.

-- ------------------------------------------------------

-- (*주의*) 일반계정인 SCOTT 계정은, Synonym 객체를 생성할 권한이 없음

-- SYSDBA 권한을 가지는 SYS계정으로, CREATE SYNONYM 시스템권한을

-- 우선 할당받아야 함.

-- ------------------------------------------------------

-- SYS 계정에서 수행

GRANT CREATE SYNONYM TO scott;

-- 이는 sys계정인 admin에서만 가능하다.(**)

-- scott 계정

CREATE SYNONYM emp_sysnonym

FOR hr.employees;

-- emp_sysnonym이라는 별칭을 생성하였기에, 이제 계정명.테이블명이 아닌 별칭으로도 데이터를 불려올 수 있다.(*)

-- 동의어를 이용한 테이블 접근

-- ------------------------------------------------------

-- (1) HR 스키마명과 employees 테이블명을 직접적으로

-- 명시하지 않기 때문에, 보안강화에 도움

-- (2) 좀 더 단순하게, 다른 사용자 객체접근이 가능

-- ------------------------------------------------------

SELECT

FROM

emp_sysnonym;

-- 정상적으로 사원 테이블이 출력되고 있음을 알 수 있다.

-- ------------------------------------------------------

-- 3-2. Synonym (동의어) 삭제

-- ------------------------------------------------------

-- Basic syntax:

-- DROP SEQUENCE <삭제할 시퀀스 객체명>;

-- ------------------------------------------------------

-- 동의어 삭제

DROP SYNONYM emp_sysnonym;

728x90