24.06.27(목) 오라클 수업 VIEW/SEQUENCE/SYNONYM

■ VIEW

- 하나 이상의 테이블이 있는 데이터를 논리적으로 처리하는 객체(OBJECT)
- 단지 SELECT문만 가지고 있는 객체이다.
- 간접 ACCESS(직접 테이블에 대해서 access하는 것이 아니다)을 제공한다.
- 뷰를 생성하려면 CREATE VIEW 시스템 권한이 있어야 한다.

테이블이 중요해서 직접 권한은 주지 않고 view를 통해서 간접적으로 볼 수 있도록 간접 access를 지원

SELECT * FROM user_ts_quotas;
SELECT * FROM user_sys_privs;
SELECT * FROM user_tab_privs;
SELECT * FROM role_sys_privs;
SELECT * FROM session_privs;

--뷰인지 테이블인지 확인하기
SELECT * FROM user_objects;
--내가 만든 view에 대한 정보 확인하기(user_view 딕셔너리)
SELECT * FROM user_views WHERE view_name = 'EMP_VIEW';


◎ view만들기(특정 테이블만 SELECT하도록 보여줄 수 있어)
-- OR REPLACE : 동일한 이름의 뷰가 있으면 지우고 생성하는 옵션
CREATE OR REPLACE VIEW hr.emp_view
AS
SELECT employee_id, last_name, first_name, email, job_id, department_id
FROM hr.employees;

--만든 뷰 확인하기
SELECT * FROM hr.emp_view;

-- 오류발생, 기존 view가 있어서
--ORA-00955: name is already used by an existing object
◎ CREATE 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 hr.emp_view;

◎ 내가 만든 view에 대한 정보 확인하기(user_view 딕셔너리)
SELECT * FROM user_views WHERE view_name = 'EMP_VIEW';

--뷰인지 테이블인지 확인하기
-- 내가 생성한 객체 정보 확인
SELECT * FROM user_objects;
-- 테이블 명은 같을 수 있어. object_id(DB안에 고유번호):처음 객체를 만들 때 번호, data_object_id(테이블을 재구성하게되면 object번호가 data_object_id가 만들어진다)는 같을 수 없어
-- view는 실제 저장공간은 사용하지 않아.(object_type이 null인 이유)
-- OBJECT_TYPE이 NULL이면 저장공간이 없는 객체이구나

※view가 작용하는 원리
SELECT * FROM user_objects
WHERE object_name IN ('EMPLOYEES','EMP_VIEW');
--이곳의 TEXT정보 사용하는거야
SELECT * FROM user_views WHERE view_name = 'EMP_VIEW';
--"SELECT employee_id, last_name, first_name, email, job_id, manager_id, department_id
--FROM hr.employees"

1. 제일 먼저 하는 게 문법 체크
2. 소유자가 객체를 가지고 있는지 체크
3. 객체에 대한 정보는 user_objects로 확인(객체에 대한 타입 확인) table인지 view인지
4. user_views에 가서 text를 가져다가 사용한다.


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

■james유저 만들기

[SYS]에서 만들기

유저 만들기(잘못된 버전!!)
CREATE USER james
IDENTIFIED BY oracle;

-- 절대 default_table에 system쓰면 안돼 디폴트 시스템 스페이스를 꼭 지정해주자!!!
SELECT * FROM dba_users;

◎유저 만들기(올바른 버전)
ALTER USER james
DEFAULT TABLESPACE users
QUOTA 10m ON users;

--디폴트 시스템 스페이스를 꼭 지정해주자!!!
SELECT * FROM dba_users;

--로그인하도록 권한
GRANT CREATE SESSION TO james;

--dba관점에서 어떤 시스템권한을 부여했는지
SELECT * FROM dba_sys_privs WHERE grantee='JAMNES';

[JAMES]입장

--권한 확인하기
SELECT * FROM user_sys_privs;

-- 내가 받은 객체 권한이 뭔지 체크
SELECT * FROM user_tab_privs;

--내가 받은 권한이 뭐가 있을까
SELECT * FROM session_privs;
SELECT * FROM role_sys_privs;

--권한이 없어서 조회 불가능 
SELECT * FROM hr.emp_view;


[HR]입장
--james에게 select 권한 주기
GRANT SELECT ON hr.emp_view TO james;

-- 내가 받은 객체 권한, 내가 부여한 객체 권한 확인
SELECT * FROM user_tab_privs;

[JAMES]입장
--권한 받고 가능해짐.
SELECT * FROM hr.emp_view;

[HR]입장
--james에게 select 권한 회수
REVOKE SELECT ON hr.emp_view FROM james;

-- 내가 받은 객체 권한, 내가 부여한 객체 권한 확인
SELECT * FROM user_tab_privs;

[JAMES]입장
--확인 불가능
SELECT * FROM hr.emp_view;

--오류 컬럼 이름 써줘야 해
--ORA-00998: must name this expression with a column alias
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;

-- alias 붙여서 다시 만들기
CREATE OR REPLACE VIEW hr.emp_view
AS
SELECT employee_id, last_name||' '||first_name name, email, job_id, manager_id department_id
FROM hr.employees;

SELECT * FROM hr.emp_view;

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

내가 쓴 답
CREATE OR REPLACE VIEW hr.sal_view
AS
SELECT d.department_name, SUM(e.salary) sum_sal, ROUND(AVG(e.salary),2) avg_sal
FROM employees e,departments d
WHERE e.department_id = d.department_id(+)
GROUP BY d.department_name
ORDER BY avg_sal desc; -- 부서이름이 null인 값 존재

쌤의 답

☆inline view를 이용해서 하는 게 성능에 좋아

--1번 과정
SELECT department_id, SUM(salary) sum_sal, AVG(salary) avg_sal
FROM hr.employees
GROUP BY department_id;
--2번 과정
SELECT d.department_name, sum_sal, avg_sal
FROM(SELECT department_id, SUM(salary) sum_sal, AVG(salary) avg_sal
        FROM hr.employees
        GROUP BY department_id) e, hr.departments d
WHERE e.department_id = d.department_id;

--3번 과정
CREATE OR REPLACE VIEW hr.dept_sum_sal
AS
SELECT d.department_name, sum_sal, avg_sal
FROM(SELECT department_id, SUM(salary) sum_sal, AVG(salary) avg_sal
        FROM hr.employees
        GROUP BY department_id) e, hr.departments d
WHERE e.department_id = d.department_id;

-- 권한 부여하기
GRANT SELECT ON hr.dept_sum_sal TO james;

--뷰 권한 확인
SELECT * FROM user_tab_privs;

◎알리아스를 변경할 수도 있어(권한에 문제 없어)
--변경1
CREATE OR REPLACE VIEW hr.dept_sum_sal
AS
SELECT d.department_name 부서이름, sum_sal 총액급여, avg_sal 급여평균
FROM(SELECT department_id, SUM(salary) sum_sal, AVG(salary) avg_sal
        FROM hr.employees
        GROUP BY department_id) e, hr.departments d
WHERE e.department_id = d.department_id;

--변경2
CREATE OR REPLACE VIEW hr.dept_sum_sal
(부서이름, 총액급여, 평균급여)
AS
SELECT d.department_name, sum_sal, avg_sal
FROM(SELECT department_id, SUM(salary) sum_sal, AVG(salary) avg_sal
        FROM hr.employees
        GROUP BY department_id) e, hr.departments d
WHERE e.department_id = d.department_id;

--통화표현 부여
◎ 'l999,999'  통화가치 번호 L999,999
CREATE OR REPLACE VIEW hr.dept_sum_sal
(부서이름, 총액급여, 평균급여)
AS
SELECT d.department_name, sum_sal, avg_sal
FROM(SELECT department_id, to_char(SUM(salary),'l999,999') sum_sal, ROUND(AVG(salary)) avg_sal
        FROM hr.employees
        GROUP BY department_id) e, hr.departments d
WHERE e.department_id = d.department_id;

[HR]세션

--샘플 테이블 만들기
CREATE TABLE hr.emp_temp
AS
SELECT employee_id id, last_name name, salary sal
FROM hr.employees
WHERE department_id = 20; 

--확인하기
SELECT * FROM hr.emp_temp;
SELECT * FROM user_objects;
SELECT * FROM user_tab_privs;

--view만들기
CREATE OR REPLACE VIEW hr.emp_temp_view
AS
SELECT * FROM hr.emp_temp;

--view에서 DML할 수 있는 권한 주기 james에게
GRANT SELECT, INSERT, UPDATE, DELETE ON hr.emp_temp_view To james;
--확인
SELECT * FROM user_tab_privs;

[JAMES]세션
--본인이 받은 객체 권한 확인
SELECT * FROM user_tab_privs;

--조회도 가능
desc hr.emp_temp_view

--삽입하기
INSERT INTO hr.emp_temp_view(id, name, sal)
VALUES(300,'james',1000);

COMMIT;

[HR]세션
--james가 본인 정보 commit 후 확인(james정보가 들어옴)
--view를 통해서 DML이 가능하다!
SELECT * FROM hr.emp_temp;

[JAMES]세션
--업데이트하기
UPDATE hr.emp_temp_view
SET sal = 2000
WHERE id=300;

COMMIT;

[HR]세션
--변경 확인할 수 있다.

[JAMES]
--삭제하기
DELETE FROM hr.emp_temp_view WHERE id=300;

COMMIT;

조인, sum 등이 들어가면 --> 복합뷰

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

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

◎WITH READ ONLY 옵션 : 단순뷰이지만 DML 작업을 불허 할 수 있다.
CREATE OR REPLACE VIEW hr.emp_temp_view
AS
SELECT * FROM hr.emp_temp
WITH READ ONLY;

[JAMES]
--read only view로 생성 돼서 오류발생
--SQL 오류: ORA-42399: cannot perform a DML operation on a read-only view
DELETE FROM hr.emp_temp_view WHERE id=201;

[HR]
--샘플 테이블 생성
CREATE TABLE hr.emp_temp
AS
SELECT employee_id id, last_name name, salary sal, department_id dept_id
FROM hr.employees

◎ WITH CHECK OPTION : CHECK 제약조건 생성하는 절, 조건식은 WHERE절이 조건식이 된다.
CREATE OR REPLACE VIEW hr.emp_temp_view
AS
SELECT * FROM hr.emp_temp WHERE dept_id = 20
WITH CHECK OPTION CONSTRAINT emp_temp_view_ck;

-확인 constraint_type V(view)
SELECT * FROM user_constraints
WHERE table_name = 'EMP_TEMP_VIEW';
WHERE department_id IN(20,30); 

--INSERT하기위해서 컬럼 이름들 보자
desc hr.emp_temp_view

--수행가능
INSERT INTO hr.emp_temp_view(id, name, sal, dept_id)
VALUES(300, 'ORACLE', 1000, 20);
SELECT * FROM hr.emp_temp_view;

-- CHECK제약조건에 걸려서 INSERT불가, 20번 부서코드로만 입력해야 한다.
INSERT INTO hr.emp_temp_view(id, name, sal, dept_id)
VALUES(400, 'IBM', 2000, 30);

--update문장 : check제약조건 오류 발생, CHECK제약조건에 위반되었기 때문에 이 뷰를 통해서는 부서 코드는 20번 부서코드로만 수행해야 한다.
UPDATE hr.emp_temp_view
SET dept_id = 30
WHERE id = 201;

--삭제는 수행이 된다.
DELETE hr.emp_temp_view WHERE id=201;

표현식: 컬럼명 그대로 사용하지 않고 함수나, 문자열 연산자나, 산술 연산자를 사용해여 컬럼명을 변형한 것

--종속관계
참조를 당하는 오브젝트
참조를 하는 오브젝트

-- 종속관계
-- DROP하기 전에 참조하고있는 게 뭐가 있는지 확인 해야해(영향도 평가)
-- NAME 참조를 하는 객체 REFFENCED_NAME 참조 당하는 객체
SELECT * FROM user_dependencies WHERE referenced_name = 'EMP_TEMP';
SELECT * FROM user_dependencies WHERE referenced_name = 'EMPLOYEES';

--뷰와 테이블은 종속관계가 있을 수밖에 없는 객체야.

SELECT * FROM user_objects WHERE object_name IN('EMP_TEMP','EMP_TEMP_VIEW');

-- 참조 당하는 객체를 지워보고 view객체가 어떻게 되는지 살펴보자.
DROP TABLE hr.emp_temp PURGE;

--STATUS가 INVALID(실행 불가능)상태로 바뀐다.
SELECT * FROM user_objects WHERE object_name IN('EMP_TEMP','EMP_TEMP_VIEW');

SELECT * FROM hr.emp_temp_view;
ORA-04063: view "HR.EMP_TEMP_VIEW" has errors
04063. 00000 -  "%s has errors"
*Cause:    Attempt to execute a stored procedure or use a view that has
           errors.  For stored procedures, the problem could be syntax errors
           or references to other, non-existent procedures.  For views,
           the problem could be a reference in the view's defining query to
           a non-existent table.
           Can also be a table which has references to non-existent or
           inaccessible types.
*Action:   Fix the errors and/or create referenced objects as necessary.
232행, 18열에서 오류 발생

-view확인
SELECT * FROM user_views;

--VIEW 삭제
DROP VIEW hr.emp_temp_view;
SELECT * FROM user_views WHERE view_name = 'EMP_TEMP_VIEW';

--  hr.emp_temp_view 없어진 것 확인
-- view 삭제하면 권한도 자동적으로 없어짐.
SELECT * FROM user_tab_privs;
---------------------------------------------------------------------------------------------------------------------------------------------------------

■SEQUENCE 객체
- 자동일련번호를 생성하는 객체
- 중요한 거 말고 그냥 +1 표현만 하고싶다면 사용. ex) 게시판 번호
- SEQUENCE 객체를 생성하려면 CREATE SEQUENCE 시스템권한이 필요하다.
- INSERT VALUES에 사용가능하고, UPDATE 작업시 SET절에도 사용 가능,

SELECT * FROM session_privs;

--시퀀스 객체 만들기
CREATE SEQUENCE id_seq;

-- 시퀀스 확인하기
SELECT * FROM user_sequences WHERE sequence_name = 'ID_SEQ';

--샘플 테이블 생성
CREATE TABLE hr.seq_test(id number);

--자동으로 다음 사용 가능한 번호를 리턴
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);

결과 1,2,3

ROLLBACK을 해도 이전에 생성했던 번호는 없어짐. (영구 결번)

--ROLLBACK 후 다시 하면 이어서
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);

-- sequence_name.nextval : 가상컬럼, 현재 사용 가능한 번호를 리턴해준다.
-- sequence_name.currval : 가상컬럼, 현재 사용한 번호를 리턴해준다
SELECT id_seq.nextval FROM dual;

현재까지 사용한 시퀀스 번호를 알려줌.
SELECT id_seq.currval FROM dual;

SEQUENCE 삭제
DROP SEQUENCE 시퀀스 이름
DROP SEQUENCE id_seq;

--시퀀스 객체 만들기
CREATE SEQUENCE id_seq
START WITH 1 --기본값
MAXVALUE 3 --max값의 최댓값은 10의 27승 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);
SELECT * FROM hr.seq_test;

--maxvalue까지 다 썼기때문에 오류 발생(SEQUENCE번호생성 불가능), 주문이라면 주문 체결이 안됨.
--ORA-08004: sequence ID_SEQ.NEXTVAL exceeds MAXVALUE and cannot be instantiated
INSERT INTO hr.seq_test(id) VALUES(id_seq.nextval);

-- SEQUENCE 수정
ALTER SEQUENCE id_seq
MAXVALUE 100;

수정규칙
-- START WITH절을 제외하고 다른 옵션들은 수정할 수 있다.

ALTER SEQUENCE id_seq
MAXVALUE 1000 --max값의 최댓값은 10의 27승 10**27(PL/SQL), POWER(10,27)
INCREMENT BY 2 -- 기본값
NOCYCLE --기본값, CYCLE(1~3번 계속 돌게 됨)
NOCACHE -- CACHE 20 기본값 -- 성능에 영향이 있음.
;

-- 오류 발생 : WHERE절에는 currval, nextval 사용할 수 없다.
--SQL 오류: ORA-00904: "ID_SEQ_CURRBAL": invalid identifier
--00904. 00000 -  "%s: invalid identifier"
UPDATE hr.seq_test
SET id =id_seq.nextval
WHERE id-id_seq_currbal;


UPDATE hr.seq_test
SET id =id_seq.nextval
WHERE id = 9;

■ SYNONYM(동의어)
- 긴객체 이름을 짧은 이름으로 사용하는 객체이다.
- SYNONYM을 생성하려면 CREATE SYNONYM 시스템 권한이 있어야 한다.
- 모든 유저들이 사용할 수 있는 SYNONYM을 생성하려면 CREATE PUBLIC SYNONYM 시스템 권한이 있어야 한다.(DBA가 별도로 권한 줘야 해)
  본인이 가지고 있는 테이블이 아니니까 꼭 소유자.테이블 이름을 써야하는 게 불편해
  직접 본인이 마드는 것 보단 권한 부여할 때 시노님 객체도 같이 GRANT해주는 모양새로 많이 사용한다.

SELECT * FROM session_privs;

--시스템권한 확인 CREATE SYNONYM이 있다!
SELECT * FROM session_privs;


--시노님 만들기
CRETAE SYNONYM ec2 FOR hr.emp_copy_2024;

SELECT * FROM hr.ec2;

SELECT * FROM user_synonyms WHERE table_name = 'EMP_COPY_2024';

--james에게 권한 부여
GRANT SELECT ON hr.employees TO james;

[JAMES]
-확인하기
SELECT * FROM user_tab_privs;
SELECT * FROM all_synonyms WHERE table_name = 'EMPLOYEES' AND table_owner='HR';

[SYS]
-- hr에게 public synonym 권한 주기
GRANT CREATE PUBLIC SYNONYM TO hr;

[HR]
--sys에게 권한 받고 권한 확인
SELECT * FROM user_sys_privs;

-- PUBLIC SYNONYM 생성하기
CREATE PUBLIC SYNONYM emp_s FOR hr.employees;

--public을 만들었지만, 내가 만들었으면 나는 안 보임.
SELECT * FROM user_synonyms;

--PUBLUC synonym 정보 확인하기.
SELECT * FROM all_synonyms WHERE table_name = 'EMPLOYEES' AND table_owner='HR';

--권한 회수
REVOKE SELECT ON hr.employees FROM james;

SELECT * FROM all_synonyms WHERE table_name = 'EMPLOYEES' AND table_owner='HR';
SELECT * FROM user_synonyms;

--synonym삭제
DROP SYNONYM ec2;

--public synonym 삭제
--오류 발생: 지우는 것도 권한이 필요해! DROP PUBLIC SYNONYM 시스템 권한이 있어야 한다.
--ORA-01031: insufficient privileges
--01031. 00000 -  "insufficient privileges"
DROP PUBLIC SYNONYM emp_s;

--DROP PUBLIC SYNONYM 권한 없음
SELECT * FROM session_privs;

[SYS]
--DROP 권한 주기
GRANT DROP PUBLIC SYNONYM TO hr;

[HR]
--권한 확인
SELECT * FROM session_privs;
--DROP 성공
DROP PUBLIC SYNONYM emp_s;