SQL - DAY 14

BUMSOO·2024년 6월 27일

VIEW

하나 이상의 테이블이 있는 데이터를 논리적으로 처리하는 객체(OBJECT)

단지 SELECT문만 가지고 있는 객체이다.

간접 ACCESS(즉 직접 테이블에 대해서 ACCESS하는 것이 아니다) 제공한다.

뷰를 생성하려면 CREATE VIEW 시스템 권한이 있어야 한다.
SELECT * FROM user_sys_privs; -- DBA로 부터 받은 시스템권한 확인
SELECT * FROM role_sys_privs; -- ROLE을 통해서 받은 시스템 권한 확인
SELECT * FROM session_privs; -- 위에 두개를 UNION해서 권한 확인
VIEW를 통해서 DML을 사용하면 실제 테이블에 영향을 줌

INSERT, UPDATE, DELETE...

뷰의 종류

단순 뷰

복합 뷰

VIEW 생성

OR REPLACE : 동일한 이름의 뷰가 있으면 지우고 생성하는 옵션

CREATE OR REPLACE VIEW hr.emp_view
AS
SELECT employee_id, last_name, first_name, email,job_id,manager_id, department_id
FROM hr.employees;

VIEW 삭제

DROP VIEW 소유자.뷰이름

DROP VIEW hr.emp_view;

내가 생성한 VIEW 정보확인

SELECT * FROM user_views WHERE view_name = '뷰이름';

내가 생성한 객체 정보 확인

SELECT * FROM user_objects;

OBJECT_ID, DATA_OBJECT_ID 는 DB상에 주민번호 같은 존재
OBJECT_ID = 처음 객체를 만들었을때 번호
DATA_OBJECT_ID = 테이블을 재구성 하게 되면 OBJECT_ID 가 새롭게 바뀌는 번호
DATA_OBJECT_ID가 NULL인 경우는 실제 데이터가 없는 경우
STATUS : 객체의 사용 가능 여부 확인
- VALID - 사용가능 객체
- INVALID - 사용불가능 객체
```
   
```

SELECT * FROM hr.emp_view 의 실행계획
user_objects에서 뷰가 맞는지 확인한 뒤 user_views에서 SELECT 문을 가져와서 뷰를 보여줌.

[문제62]

부서이름별, 총액급여, 평균급여를 조회하는 dept_sum_sal 뷰를 생성한 후 james 유저에게 SELECT 할 수 있는 권한을 부여해주세요.

<풀이> - 인라인뷰를 활용한 조인방법

CREATE OR REPLACE VIEW hr.dept_sum_sal
AS
SELECT a.department_name AS 부서이름, sum_sal AS 총액급여, avg_sal AS 평균급여
FROM hr.departments a,
            (SELECT department_id, sum(salary) sum_sal, avg(salary) avg_sal
                FROM hr.employees
                GROUP BY department_id) b
WHERE a.department_id = b.department_id

GRANT SELECT ON hr.dept_sum_sal TO james;

뷰를 수정할 때는 DROP VIEW를 하지 말고 CREATE OR REPLACE를 하도록 하자.

단순 뷰

뷰를 통해서 DML 작업을 수행할 수 있다.
뷰안에 SELECT문이 테이블에 하나만 있는 경우, 즉 조인 문장이 없는 경우
함수를 사용하지 않는 경우
단순뷰 이지만 표현식이 있는 컬럼에는 INSERT,UPDATE 수행할 수 없다.
단순뷰 이지만 선택되지 않은 컬럼에 NOT NULL제약조건이 있는 경우 INSERT 할 수 없다.
WITH READ ONLY : 단순뷰 이지만 DML 작업을 불허 할 수 있다.

복합 뷰

뷰를 통해서 DML 작업을 수행할 수 없다.
그룹함수 사용한 경우
조인문장이 있는 뷰
복합뷰이지만 DML작업을 수행하려면 트리거로 구현해야한다.

WITH CHECK OPTION

CHECK 제약조건 생성하는 절, 조건식은 WHERE절이 조건식

CREATE OR REPLACE VIEW hr.emp_temp_view
AS
SELECT * FROM hr.emp_temp
WHERE dept_id = 20 -- dept_id는 무조건 20만 들어가야 한다.
WITH CHECK OPTION CONSTRAINT emp_temp_view_ck;

INSERT INTO hr.emp_temp_view(id,name,sal,dept_id) VALUES (300,'oracle',1000,20); -- INSERT 수행
INSERT INTO hr.emp_temp_view(id,name,sal,dept_id) VALUES (400,'oracles',1000,30); 
-- INSERT 오류발생, CHECK 제약조건에 위반 되었기 때문에 이뷰를 통해서는 20번 부서 코드만 입력해야한다.


UPDATE hr.emp_temp_view
SET dept_id = 30 -- UPDATE오류 발생, CHECK 제약조건 위반
WHERE id = 201;

VIEW가 참조하는 테이블이 삭제된 경우

종속관계를 보여주는 명령어
SELECT * FROM user_dependencies WHERE referenced_name = '주 이름'

객체정보를 보여주는 명령어
SELECT * FROM user_objects WHERE object_name = '객체 이름'

주종 테이블이 삭제가 되면 뷰는 INVALID 상태가 되어버림.
테이블이 recyclebin에 있으면 복구 후 연결 가능

SEQUENCE

자동 일련번호를 생성하는 객체

SEQUENCE 객체를 생성하려면 CREATE SEQUENCE 시스템 권한이 필요하다.
CREATE SEQUENCE 시퀀스이름;

SELECT * FROM user_sequences;

sequence_name.nextval

가상컬럼, 현재 사용 가능한 번호를 리턴해준다

INSERT INTO hr.seq_test(id) VALUES(id_seq.nextval); -- 시퀀스 번호 1
INSERT INTO hr.seq_test(id) VALUES(id_seq.nextval); -- 시퀀스 번호 2
INSERT INTO hr.seq_test(id) VALUES(id_seq.nextval); -- 시퀀스 번호 3

SELECT * FROM hr.seq_test;

SELECT * FROM user_sequences WHERE sequence_name = 'ID_SEQ';

ROLLBACK; -- 롤백

SELECT * FROM hr.seq_test;

INSERT INTO hr.seq_test(id) VALUES(id_seq.nextval); -- 시퀀스 번호 4
INSERT INTO hr.seq_test(id) VALUES(id_seq.nextval); -- 시퀀스 번호 5
INSERT INTO hr.seq_test(id) VALUES(id_seq.nextval); -- 시퀀스 번호 6

SELECT * FROM hr.seq_test;

sequence_name.currval

가상컬럼, 현재 사용한 번호를 리턴해준다.

SELECT id_seq.currval FROM dual;

sequence 삭제

DROP SEQUENCE 시퀀스이름;

DROP SEQUENCE id_seq;

sequence 옵션

MAXVALUE, INCREMENT BY , NOCYCLE, NOCACHE

CREATE SEQUENCE id_seq
START WITH 1 -- 기본값
MAXVALUE 3 -- 기본값 10**27(PL/SQL),POWER(10,27)
INCREMENT BY 1 -- 기본값
NOCYCLE -- 기본값, CYCLE 할 시 1~3 을 계속 반복함
NOCACHE; -- CACHE 20 기본값

SELECT * FROM user_sequences WHERE sequence_name = 'ID_SEQ';

INSERT INTO hr.seq_test(id) VALUES(id_seq.nextval);
INSERT INTO hr.seq_test(id) VALUES(id_seq.nextval);
INSERT INTO hr.seq_test(id) VALUES(id_seq.nextval);
INSERT INTO hr.seq_test(id) VALUES(id_seq.nextval); -- 오류발생, MAXVLAUE값 까지 생성했기 대문에 SEQUENCE번호를 생성 할 수 없다.

sequence 수정

START WITH절을 제외하고 다른 옵션들은 수정할 수 있다.
ALTER SEQUENCE 시퀀스이름 옵션

ALTER SEQUENCE id_seq
MAXVALUE 1000 
INCREMENT BY 2 
NOCYCLE 
CACHE 30;

오류발생

UPDATE hr.seq_test
SET id = id_seq.nextval
WHERE id = id.seq.currval; -- 오류발생 where 절 currval, nextval 사용할 수 없다.

SYNONYM(동의어)

긴객체 이름을 짧은 이름으로 사용하는 객체이다.

해당 객체 안에서만 사용 가능

SYNONYM을 생성하려면 CREATE SYNONYM 시스템 권한이 있어야 한다.
CREATE SYNONYM 동의어 FOR 기존이름;

SYNONYM을 확인 하는 명령어
SELECT * FROM user_synonyms WHERE table_name = '기본테이블 이름';

모든 유저들이 사용할 수 있는 SYNONYM을 생성하려면 CREATE PUBLIC SYNONYM 시스템 권한이 있어야 한다.

PUBLIC SYNONYM을 만들게 되면 ALL_SYNONYMS라는 딕셔너리 뷰에서 확인해야 한다.

아무리 PUBLIC SYNONYM이라도 해당 SYNONYM에 연결되어 있는 실제 테이블에 권한이 없으면 오류 발생

SYNONYM 삭제

DROP SYNONYM 시노임 이름;

PUBLIC SYNONYM 삭제

PUBLIC SYNONYM은 DROP PUBLIC SYNONYM이라는 시스템 권한을 받아야지만 삭제가 가능하다. 따라서 SYS로 부터 권한을 부여 받은뒤 삭제를 진행해야 한다.

GRANT DROP PUBLIC SYNONYM TO 객체;

DROP PUBLIC SYNONYM 이름;

BUMSOO

이전 포스트

SQL - DAY 13

다음 포스트