오라클 sql dbeaver / view 뷰 생성 옵션, 수정, 삭제

--오라클 객체--
--VIEW(뷰)
--SELECT를 실행한 결과 화면을 담는 객체
--조회한 SELECT문 자체를 저장하여 
--호출할 때마다 해당 쿼리를 실행하여 결과를 보여주는 객체 

--필요한 정보만 조회하고 다른것은 숨길 수 있음

--[사용방법]
--CREATE [OR REPLACE] VIEW 뷰이름 
--AS 서브쿼리(뷰를 통해서 확인할 SELECT쿼리)

CREATE OR REPLACE VIEW V_EMP
AS SELECT EMP_ID, EMP_NAME, DEPT_CODE FROM EMPLOYEE;

-- insufficient privileges에러.
--그냥 만들 수 없고, VIEW를 만들 수 있는 권한을 줘야함. 현재는 리소스랑 커넥트 권한밖에 없음
--cmd 접속, sqlplus, system계정으로 접근해서 
--grant create view to KH;

SELECT * FROM V_EMP;
--SELECT뒤의 쿼리문이 실행된다. 

--OR REPLACE 옵션을 부여하면 덮어씌우기가 가능하다 
--이미 만들어진 V_EMP가 있다면, CREATE VIEW를하면 '이미 존재한다'고 에러뜨지만
--CREATE OR REPLACE는 이미 존재한다면 덮어씌운다 

--뷰의 정보 확인
SELECT * FROM USER_VIEWS;
--TEXT 테이블로 쿼리문(AS SELECT EMP_ID, EMP_NAME, DEPT_CODE FROM EMPLOYEE)가 들어가있고
--TEXT_LENGTH 테이블로 48이 들어간다. 


CREATE OR REPLACE VIEW V_EMP(사번,이름,부서,직급)
AS SELECT EMP_ID, EMP_NAME, DEPT_CODE, JOB_CODE FROM EMPLOYEE;
--테이블별로 별칭( ) 부여 

SELECT * FROM V_EMP;

--실습1
--사번,이름,직급명, 부서명, 근무지역을 조회하고
--그결과를 V_RESULTTEST_EMP라는 뷰를 만들어
--뷰를 통해 그 결과를 조회
--1) 서브쿼리를 준비
SELECT EMP_ID, EMP_NAME, JOB_NAME
FROM EMPLOYEE E, JOB J
WHERE E.JOB_CODE=J.JOB_CODE;

--2 뷰에 대입 
CREATE OR REPLACE VIEW V_RESULT_EMP (사번,이름,직급명, 부서명, 근무지역)
AS SELECT EMP_ID, EMP_NAME, JOB_NAME, DEPT_TITLE, LOCAL_NAME
FROM EMPLOYEE 
LEFT JOIN JOB USING(JOB_CODE)
LEFT JOIN DEPARTMENT ON(DEPT_CODE=DEPT_ID)
LEFT JOIN LOCATION ON(LOCATION_ID=LOCAL_CODE);

SELECT * FROM V_RESULT_EMP; 

--실습2. 만들어진 VIEW를 활용하여 사번이 205번인 직원정보 조회 

SELECT * 
FROM V_RESULT_EMP 
WHERE 사번='205';

COMMIT;

UPDATE EMPLOYEE 
SET EMP_NAME='정중앙'
WHERE EMP_ID='205';
--VIEW는 결과를 가진게 아니라 SELECT문을 저장하므로 
--변경된 값이 VIEW에서도 함께 조회가 된다 

--뷰 삭제 
DROP VIEW V_RESULT_EMP;

--확인
SELECT * FROM USER_VIEWS;

--뷰에는 연산 결과도 포함하여 저장이 가능하다
--사번 이름 성별 근무년수 조회 
--1) 서브쿼리 

SELECT * FROM EMPLOYEE;

SELECT EMP_ID, EMP_NAME,
DECODE(SUBSTR(EMP_NO,6,1),1,'남성','여성'),
EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM HIRE_DATE)
FROM EMPLOYEE;

SELECT EMP_NO FROM EMPLOYEE;

--2)뷰
CREATE OR REPLACE VIEW V_EMP(사번,이름,성별,근무년수)
AS 
SELECT EMP_ID, EMP_NAME,
DECODE(SUBSTR(EMP_NO,6,1),1,'남성','여성'),
EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM HIRE_DATE)
FROM EMPLOYEE;

SELECT * FROM V_EMP;

--뷰를 통해 데이터 삽입, 수정, 삭제
CREATE OR REPLACE VIEW V_JOB
AS SELECT * FROM JOB;
 

--삽입
INSERT INTO V_JOB VALUES('J8','인턴');


SELECT * FROM V_JOB;
SELECT * FROM JOB;

--VIEW에 추가한 것이 JOB 테이블에도 추가되어있음

--수정
UPDATE V_JOB SET JOB_NAME='알바' WHERE JOB_CODE='J8';

SELECT * FROM V_JOB;
SELECT * FROM JOB;

--삭제
DELETE FROM V_JOB WHERE JOB_CODE='J8';

SELECT * FROM V_JOB;
SELECT * FROM JOB;

--뷰에서도 데이터 추가, 수정, 삭제가 가능하지만
--불가능한 경우도 있다.
--1. 뷰에 정의되지 않은 컬럼을 수정할 경우 
--2. 뷰에서 보이지 않는 컬럼 중 NOT NULL 제약조건을 가진 컬럼이 있을 경우 
--3. 산술 연산이 적용된 컬럼 
--4. JOIN을 통해 여러 테이블을 참조할 경우 (예외: 기본키가 하나 있을 때)
--5. DISTINCT를 사용하여 실제 데이터 내용이 명확하지 않을 경우 
--6. 그룹함수나 GROUP BY 구문을 사용하여 조회한 쿼리일 경우 

CREATE OR REPLACE VIEW V_JOB
AS SELECT JOB_CODE FROM JOB;

SELECT * FROM V_JOB;
--V_JOB에 JOB_CODE만 가져왔고(1개의 칼럼), JOB에는 2개의 칼럼값이 있음. 

INSERT INTO V_JOB
VALUES('J8','인턴');
--TOO MANY VALUES 에러. 1개 칼럼만 가지고 있는데 2개 칼럼이 안들어간다.

--산술 표현일 경우 
CREATE OR REPLACE VIEW V_EMP_SAL
AS
SELECT EMP_ID, EMP_NAME, SALARY,
(SALARY+SALARY*NVL(BONUS,0))*12 연봉
FROM EMPLOYEE;

SELECT * FROM V_EMP_SAL;

INSERT INTO V_EMP_SAL VALUES(999,'홍길동',3000000,40000000);
--에러 virtual column not allowed here

--JOIN을 통해 VIEW의 정보를 수정하는 경우
CREATE OR REPLACE VIEW V_JOIN_EMP
AS 
SELECT EMP_ID, EMP_NAME, DEPT_TITLE
FROM EMPLOYEE
LEFT JOIN DEPARTMENT ON (DEPT_CODE=DEPT_ID);

SELECT * FROM V_JOIN_EMP;


INSERT INTO V_JOIN_EMP VALUES(911,'이길동','기술지원부');
--에러 cannot modify more than one base table through a join view

--수정
UPDATE V_JOIN_EMP SET DEPT_TITLE='기술지원부' WHERE EMP_ID=218;
--에러  cannot modify a column which maps to a non key-preserved table


--DISTINCT 를 사용하는 경우 
CREATE OR REPLACE VIEW V_DEPT_EMP
AS
SELECT DISTINCT DEPT_CODE FROM EMPLOYEE;

INSERT INTO V_DEPT_EMP 
VALUES('D0');
--data manipulation operation not legal on this view 에러 

--그룹함수, GROUP BY 
CREATE OR REPLACE VIEW V_GROUP_DEPT
AS
SELECT DEPT_CODE 부서, SUM(SALARY) 합, TRUNC(AVG(SALARY),-4) 평균
FROM EMPLOYEE
GROUP BY DEPT_CODE;
--에러: must name ~ 별칭을 정해야함
--함수식을 정할때 별칭을 꼭 지정해줘야 함 


SELECT * FROM V_GROUP_DEPT;

INSERT INTO V_GROUP_DEPT 
VALUES('D10',5000000,50000000);
--virtual column not allowed here 에러

UPDATE V_GROUP_DEPT
SET 부서='D10'
WHERE 부서='D5';
-- data manipulation operation not legal on this view 에러

DELETE FROM V_GROUP_DEPT WHERE 부서='D6';
-- data manipulation operation not legal on this view 에러

--아예 수정작업이 안되게 할 수 있음 , 뷰 생성시에 설정 가능 

--VIEW 생성시 설정 가능한 옵션
--1. OR REPLACE : 기존에 동일한 이름의 뷰가 있을 경우 덮어 씌우고 없을 경우 새로 만들겠다 
--2. FORCE / NO FORCE : 서브쿼리에 사용된 테이블이 존재하지 않아도 뷰를 강제로 생성 
--3. WITH CHECK/ READ ONLY : 
-- CHECK: 옵션을 설정한 컬럼의 값을 바꾸지 못하게 막는 설정
--  READ ONLY : 뷰에 사용된 어떠한 칼럼도 뷰를 통해 변경하지 못하게 막는 설정 


--FORCE: 존재하지 않는 테이블이라도 뷰를 강제로 생성
CREATE OR REPLACE VIEW V_EMP 
AS SELECT T_CODE, T_NAME, T_CONTENT FROM TEST_TABLE;
--실행하면 table or view does not exist 

CREATE OR REPLACE FORCE VIEW V_EMP 
AS SELECT T_CODE, T_NAME, T_CONTENT FROM TEST_TABLE;
--FORCE 추가하면 만들어지긴 함 

SELECT * FROM V_EMP;
--존재하는게 없어서 view "KH.V_EMP" has errors 

SELECT * FROM USER_VIEWS;
--에서는 VIEW가 만들어진 것을 확인할 수 있음 

--NO FORCE: 생성하려는 뷰의 테이블이 존재하지 않는다면 뷰를 생성하지 않겠다. 
CREATE OR REPLACE /*NOFORCE*/ VIEW V_EMP 
AS SELECT T_CODE, T_NAME, T_CONTENT FROM TEST_TABLE;
--기본값이 NO FORCE인것 

--WITH CHECK : 뷰에 존재하는 칼럼을 추가하거나 수정하지 못하게 막는 뷰의 옵션 
--제한적으로 테이블의 정보를 제공하기 위해 VIEW를 생성해서 제공하는 옵션 

CREATE OR REPLACE VIEW V_EMP 
AS
SELECT * FROM EMPLOYEE 
WITH CHECK OPTION;

INSERT INTO V_EMP 
VALUES(888,'홍길동','101010-1234567','park@kh.or.kr',
'01028384848','D1','J7','S1',8000000,0.1,200,SYSDATE,NULL,DEFAULT);
-- 에러

COMMIT;

SELECT * FROM V_EMP;

--CHECK옵션으로 다른 수정은 막아도 delete는 가능 
DELETE FROM V_EMP WHERE EMP_ID='900';

 ROLLBACK;

--WITH READ ONLY : 데이터의 입력, 수정, 삭제를 모두 막는 옵션 

CREATE OR REPLACE VIEW V_EMP 
AS
SELECT * FROM EMPLOYEE 
WITH READ ONLY;

INSERT INTO V_EMP 
VALUES(888,'홍길동','101010-1234567','park@kh.or.kr',
'01028384848','D1','J7','S1',8000000,0.1,200,SYSDATE,NULL,DEFAULT);
--에러 Explicit column default is not supported for modifying views

DELETE FROM V_EMP WHERE EMP_ID = '900';
--에러  cannot perform a DML operation on a read-only view

--SEQUENCE 시퀀스 
--1,2,3,4,5 .. 의 형식으로
--숫자 데이터를 자동으로 카운터하는 객체
 
--
--CREATE SEQUENCE 시퀀스명
--INCREMENT BY 숫지 : 다음 값에 대한 증감 수치, 생략시에는 1씩 증가
--INCREMENT BY 5 : 5씩 증가
--INCREMENT BY -5: 5씩 감소
--[START WITH 숫자] : 시작값, 생략시 1부터
--[MAXVALUE 숫자 | NOMAXVALUE]: 최대값설정
--[MINVALUE 숫자 | NOMINVALUE]: 최소값설정
--[CYCLE | NOCYCLE] : 값의 순환여부(1~100..1~100)
--[CACHE 크기 | NOCACHE] : 값을 미리 구해 놓고 다음 값을 반영할때 사용하는 설정 
--기본값 20BYTE

CREATE SEQUENCE SEQ_EMPID
START WITH 300
INCREMENT BY 5
MAXVALUE 310
NOCYCLE
NOCACHE;

--NEXTVALUE통해 값을 하나씩 가져올 수 있음
SELECT SEQ_EMPID.NEXTVAL FROM DUAL; 
--결과값:300
--다시한번더 실행하면 305 
--그다음은 310
--그다음은 에러. 최대값을 넘어서서. 

--현재시퀀스 확인
SELECT SEQ_EMPID.CURRVAL FROM DUAL;


--시퀀스 설정 변경하기 
ALTER SEQUENCE SEQ_EMPID
--START WITH 320
INCREMENT BY 10
MAXVALUE 400
NOCYCLE
NOCACHE;
--초기값 START값 변경은 안된다. 변경하고싶으면 다시 만들어야함 

SELECT SEQ_EMPID.NEXTVAL FROM DUAL; 

--시퀀스 삭제하기
DROP SEQUENCE SEQ_EMPID;

--시퀀스를 활용하여 데이터 추가
CREATE SEQUENCE SEQ_EID
START WITH 300
MAXVALUE 10000
NOCYCLE
NOCACHE;

--데이터추가
INSERT INTO EMPLOYEE
VALUES(SEQ_EID.NEXTVAL, '예길동',
'121212-1234567','cgd@or.kr','01034251325','D2','J7','S1',
5000000,0.1,200,SYSDATE,NULL,DEFAULT);

SELECT * FROM EMPLOYEE 
WHERE EMP_NAME = '예길동';

INSERT INTO EMPLOYEE
VALUES(SEQ_EID.NEXTVAL, '예긴동',
'121242-2234567','Tgd@or.kr','01032151325','D5','J2','S3',
5000000,0.1,200,SYSDATE,NULL,DEFAULT);

SELECT * FROM EMPLOYEE 
WHERE EMP_NAME = '예긴동';

--NEXTVAL 때문에 EMP_ID가 301,302,303, 이런식으로 순차적으로 들어간다 


--CYCLE / CACHE 
--CYCLE : 시퀀스의 값이 최소값 OR 최대값에 도달했을 때 다시 반대의 값부터 시작하는 옵션
CREATE SEQUENCE SEQ_CYCLE 
START WITH 200
INCREMENT BY 10
MAXVALUE 230 
MINVALUE 15 
CYCLE 
NOCACHE;

SELECT SEQ_CYCLE.NEXTVAL FROM DUAL;
SELECT SEQ_CYCLE.NEXTVAL FROM DUAL;
SELECT SEQ_CYCLE.NEXTVAL FROM DUAL; 
SELECT SEQ_CYCLE.NEXTVAL FROM DUAL;--최대값 도달!
--위에는 NOCYCLE이었으므로 최대값 넘으면 에러였는데 여기서는 CYCLE 즉 순환한다

--CACHE / NOCACHE
--CACHE : 컴퓨터가 다음값에대한 연산들을 그때그때 수행하지않고 미리 계산해놓는것
--NOCACHE: 컴퓨터가 수행할 값을 그때그떄 처리하는 것 


CREATE SEQUENCE SEQ_CACHE
START WITH 100
CACHE 20
NOCYCLE;

SELECT SEQ_CACHE.NEXTVAL FROM DUAL;

SELECT * FROM USER_SEQUENCES;