SQL 데이터베이스 객체-뷰(생성,수정,제거), WITH CHECK OPTION,WITH READ ONLY, 시퀀스(생성,수정,제거),인덱스(생성,제거), SYSNONYM(시노님)

데이터 베이스 객체

 

데이터베이스 객체란 오라클같은 DBMS가 데이터를 관리하기 위해 존재하는 논리적인 저장 구조

 

<데이터베이스 객체 종류>

테이블 : 기본 저장 단위이며 행으로 구성되어 있음, 데이터를 담고 있는 객체

 

뷰 : 하나 이상의 테이블에 있는 데이터의 부분 집합을 논리적으로 나타냄

(하나 이상의 테이블을 연결해 마치 테이블처럼 사용)

→ 즉, 가상테이블

 

시퀀스 : 숫자 값을 생성(일련번호 채번 시 사용)

 

인덱스 : 테이블에 있는 데이터를 빠르게 서칭하기 위한 객체(데이터 검색 query의 성능을 향상시킴)

 

동의어(SYNONYM) : 객체에 다른 이름(별칭)을 부여함

 

함수 : 특정 연산을 하고 값을 반환하는 객체

 

프로시저 : 함수와 비슷하나 값을 반환하지는 않음

 

패키지 : 용도에 맞게 함수나 프로시저를 하나로 묶어놓은 객체

 

---

뷰 생성하는 방법

 

CREATE [view_name] 

AS SELECT [field_name_1, field_name_2]

FROM [table_name]

WHERE[조건];

위의 방법대로 만들면 뷰가 생성되었다고 나옴.

DESC와 SELECT*FROM을 통해 데이터가 어떻게 들어갔는지 확인 가능.

 

 

뷰의 장점

1. 특정 사용자에게 테이블 전체가 아닌 필요한 필드만 보여줄 수 있음

2. 복잡한 쿼리를 단순화해서 사용할 수 있음

3. 쿼리 재사용 가능

 

 

뷰의 단점

1. 한번 정의된 뷰는 변경 불가능

2. 삽입, 삭제, 갱신 작업에 많은 제약이 있음

3. 자신만의 인덱스 가질 수 없음

 

---

alias를 이용한 view 생성 방법

(이전처럼 자신이 넣고 싶은 별명을 넣어주면 됨)

 데이터를 넣으면 위와 같은 결과로 나옴.

 

---

뷰 수정하는 방법

 

CREATE OR REPLACE [view_name] 

AS SELECT [field_name_1, field_name_2]

FROM [table_name]

WHERE[조건];

위에서 만든 emp10 view를 수정하였음.

 

복합 뷰

- 여러 개의 테이블을 한번에 참조할 수 있음

- table1, table2 의 원하는 컬럼 부분만 JOIN해서 사용할 수 있음

- 서브 쿼리 부분에 JOIN , 함수, GROUP BY, UNION, ON등을 사용해 뷰를 생성

: 함수 등을 사용할 경우에는 컬럼 별칭을 부여해야 함

 

위의 2사진 테이블(emp10과 emplp)을 조인해서 만들었음.

emp10의 manager_id와 emplp의 manager_id는 같되 department_id와 salary는 employess에서 가져옴.

 

---

 

 

뷰 DML 작업 수행 규칙

1. 행 제거할 수 없는 경우

 

- 그룹 함수

- GROUP BY 절

- DISTINCT 키워드

- Pseducolumn ROWNUM 키워드

 

2. 뷰의 데이터 수정할 수 없는 경우

 

- 그룹 함수

- GROUP BY 절

- DISTINCT 키워드

- Pseducolumn ROWNUM 키워드

- 표현식으로 정의되는 열

 

3. 뷰를 통해 데이터를 추가할 수 없는 경우

 

- 그룹 함수

- GROUP BY 절

- DISTINCT 키워드

- Pseducolumn ROWNUM 키워드

- 표현식으로 정의되는 열

- 뷰에서 선택되지 않은 기본 테이블의 NOT NULL 열

 

---

WITH CHECK OPTION

- 조건 컬럼값을 변경하지 못하게 하는 옵션. 즉, 조건에 사용된 컬럼 값은 뷰를 통해서 변경이 불가능함.

- VIEW를 만들 시 해당 제약조건 안에서만 삽입, 삭제, 수정이 가능함

- 뷰를 정의하는 서브 쿼리문에 WHERE절을 추가해 기본 테이블 중 특정 조건에 만족하는 로우(행)만으로 구성된 뷰 생성 가능

- 생성한 뷰에 삽입이나 수정 연산을 할 때 SELECT문에서 제시한 뷰의 정의 조건을 위반하면 수행되지 않도록 함

 

Constraint

- Constraint는 제약조건으로 틀린 데이터는 못들어오게 막고, 맞는 데이터만 들어오게 해주기 위해 사용.

- 데이터의 정확성과 일관성을 보장하기 위해 각 컬럼에 정의하는 규칙

- 딕셔너리에 저장됨

- 테이블 생성시 무결성 제약조건을 정의해 프로그래밍 과정을 줄여 

 

WITH CHECK OPTION 사용법

 

WITH CHECK OPTION {CONSTRAINT 제약조건이름};

* 제약 조건이름을 설정하지 않는 경우 중괄호 부분 생략 가능

이렇게 하면 

employee_id에 제약조건을 걸었기 때문에 with check option의 위배된다는 오류가 뜸.

 

 

WITH READ ONLY

- 기본 테이블의 어떤 컬럼에 대해서도 뷰를 통한 내용 수정을 불가능하게 만드는 옵션

위와 같이 WITH READ ONLY를 사용하면 읽기 전용 뷰에서는 DML 작업을 수행할 수 없다는 오류가 뜸.

 

---

뷰 제거하는 방법

: DROP VIEW 뷰이름

 

DROP VIEW empvu80를 실행시켰더니 삭제되었다고 출력됨.

 

---

시퀀스(Sequence)

- 연속적인 숫자 값을 자동으로 증감시켜서 값을 리턴하는 객체

INCREENT BY : 시퀀스 실행 시 증가시킬 값

START WITH : 시퀀스의 시작값 (MINVALUE과 같거나 커야 한다)

MINVALUE : 시퀀스가 시작되는 최소값

MAXVALUE : 시퀀스가 끝나는 최대값

NOCYCLE | CYCLE : NOCYCLE (반복안함)

                                    CYCLE(시퀀스의 최댓값에 도달 시 최솟값 1부터 다시 시작)

NOCACHE | CACHE : NOCACHE(사용안함)

                                     CACHE(캐시를 사용해 미리 값을 할당해놓아서 속도가 빠르며, 동시 사용자가 많을 경우 유리)

NOORDER | ORDER : NOORDER(사용안함)

                                     ORDER(요청 순서로 값을 생성하여 발생 순서를 보장하지만 조금의 시스템 부하가 있음

시퀀스를 증가시키는 식으로 최소값은 49, 최대값은 900, 시작값은  50으로 설정하여 1씩 증가하도록 설정.

NOCYCLE를 통해 반복안하게 하고 캐시도 설정하지 않고, ORDER도 사용하지 않았음.

(별도로 설정가능)

 

그렇다면 실행하는 방법은?

SELECT 시퀀스이름.NEXTVAL을 사용하여 일련번호를 생성할 수 있음

시퀀스를 실행할 때마다 값이 증가한다. (증가한 값을 다시 감소시킬 수 없다.)

 

SELECT 시퀀스이름. CURRVAL을 사용해 현재 시퀀스 순번을 출력합니다.

 CURRVAL은 NAEXVAL과 달리 여러번 실행해도 증가하지 않으며 현재 순번만 출력합니다.

 

INSERT문에서 순번을 매길 때도 유용하게 사용 가능함.

 

 

시퀀스 수정하는 방법

ALTER SEQUENCE + 테이블명 +  INCREMENT BY, START WITH 등을 사용+ 변경값;

 

 

시퀀스 제거하는 방법

DROP SEQUENCE + 시퀀스명을 통해 삭제 가능함.

 

 

INDEX 생성

-  오라클 서버 INDEX는 포인터를 사용해 행 검색 속도를 높일 수 있는 스키마 객체

사용법 : CREATE INDEX [인덱스명] ON [테이블명] (컬럼1, 컬럼2, 컬럼3 ...)

 

INDEX 제거

 

사용법 : DROP INDEX[인덱스명] ;

 

 

SYNONYM 사용법

CREATE SYNONYM +시노님 이름+ FOR 객체이름;