[Day +42]DDL, VIEW
210820 금
DDL
3. 컬럼, 제약조건, 테이블 "이름" 변경
SELECT * FROM DEPT_COPY;
--컬럼명 변경(RENAME COLUMN 컬럼명 TO 컬럼명)
ALTER TABLE DEPT_COPY
RENAME COLUMN DEPT_TITLE TO DEPT_NAME;2) 제약조건명 변경 (RENAME CONSTRAINT 제약조건명 TO 제약조건명)
2-1) 변경 전 USER_FOREGINKEY 테이블 제약 조건 조회
SELECT UC.CONSTRAINT_NAME 이름, UC.CONSTRAINT_TYPE 유형, UCC.COLUMN_NAME 컬럼명,
UC.R_CONSTRAINT_NAME 참조, UC.DELETE_RULE 삭제규칙
FROM USER_CONSTRAINTS UC
JOIN USER_CONS_COLUMNS UCC ON(UC.CONSTRAINT_NAME = UCC.CONSTRAINT_NAME)
WHERE UC.TABLE_NAME = 'USER_FOREIGNKEY';
2-2) 제약조건명이 따로 부여되지 않은 USER_PWD의 NOT NULL 조건명 변경
ALTER TABLE USER_FOREIGNKEY
RENAME CONSTRAINT SYS_C007148 TO UF_USERPWD_NN;
2-3) 변경 후 USER_FOREGINKEY 테이블 제약 조건 조회 => 이름 변경됨
SELECT UC.CONSTRAINT_NAME 이름, UC.CONSTRAINT_TYPE 유형, UCC.COLUMN_NAME 컬럼명,
UC.R_CONSTRAINT_NAME 참조, UC.DELETE_RULE 삭제규칙
FROM USER_CONSTRAINTS UC
JOIN USER_CONS_COLUMNS UCC ON(UC.CONSTRAINT_NAME = UCC.CONSTRAINT_NAME)
WHERE UC.TABLE_NAME = 'USER_FOREIGNKEY';
3) 테이블명 변경(RENAME [테이블명] TO 변경명)
ALTER TABLE DEPT_COPY
RENAME TO DEPT_TEST;
SELECT * FROM DEPT_COPY; --> 이름 바뀌어서 조회 안됨
SELECT * FROM DEPT_TEST; --> 이거는 됨
4. 테이블 삭제
DROP TABLE DEPT_TEST;
--참조 되고 있는 테이블의 경우 삭제 불가능한 상황도 있음
DROP TABLE DEPT_TEST CASCADE CONSTRAINTS; --종속된 제약 조건 함께 삭제한다
VIEW
: SELECT 쿼리의 실행 결과를 화면에 저장한 논리적 가상 테이블 실제 테이블과는 다르게 실질적 데이터를 저장하고 있진 않지만 사용자는 테이블을 사용하는 것과 동일하게 사용 가능
-실질적인 데이터를 저장하고 있는 것은 아님. 그러나 사용자는 테이블을 사용하는 것과 동일하게 사용 가능(조회, 수정, 삭제)
<정리>
-SELECT 쿼리 실행의 결과 화면을 저장한 객체
-논리적인 가상 테이블
-실질적으로 데이터를 저장하고 있지 않음
-테이블을 사용하는 것과 동일하게 사용할 수 있음
-특정 데이터나 컬럼의 정보를 선별적으로 보여줄 수 있음(민감한 정보 보호)
-사용 빈도가 높은 복잡한 쿼리가 있다면 이를 VIEW로 만들어서 사용하면 편리
[표현식]
CREAT [OR REPLACE] VIEW 뷰이름 AS 서브쿼리
[OR REPLACE] : 뷰 생성 시 기존에 같은 이름의 뷰가 있다면 해당 뷰를 변경
1. VIEW 생성하기
1) 사번, 이름, 부서명, 근무 지역을 조회하고 그 결과를 V_EMPLOYEE라는 뷰를 생성해서 저장
CREATE OR REPLACE VIEW V_EMPLOYEE
AS SELECT EMP_ID, EMP_NAME, DEPT_TITLE, NATIONAL_NAME
FROM EMPLOYEE
LEFT JOIN DEPARTMENT ON(DEPT_CODE = DEPT_ID)
LEFT JOIN LOCATION ON (LOCATION_ID = LOCAL_CODE)
LEFT JOIN NATIONAL USING(NATIONAL_CODE);
--Ask your database administrator or designated security
--administrator to grant you the necessary privileges
--실행 권한이 없다는 뜻
--맨처음 계정 생성할 때 VIEW에 대한 권한은 받지 못했던 것2) 정상 생성 방법
(1) SYS 계정으로 접속 변경
(2) KH 계정에 뷰 생성 권한 부여
GRANT CREATE VIEW TO kh;
(3) kh 계정으로 접속 다시 변경해서 뷰 생성 실행
3) 사번, 이름, 부서명, 근무 지역을 조회하고 그 결과를 V_EMPLOYEE라는 뷰를 생성해서 저장
CREATE OR REPLACE VIEW V_EMPLOYEE
AS SELECT EMP_ID, EMP_NAME, DEPT_TITLE, NATIONAL_NAME
FROM EMPLOYEE
LEFT JOIN DEPARTMENT ON(DEPT_CODE = DEPT_ID)
LEFT JOIN LOCATION ON (LOCATION_ID = LOCAL_CODE)
LEFT JOIN NATIONAL USING(NATIONAL_CODE);
--> 이번에는 제대로 생성됨--뷰 조회
SELECT * FROM V_EMPLOYEE;
*베이스 테이블의 정보가 변경되면 view도 변경됨
COMMIT; --시점 만들기
4) 사번 205번인 직원의 이름을 '정중앙'으로 변경
UPDATE EMPLOYEE
SET EMP_NAME = '정중앙'
WHERE EMP_ID = 205;
--베이스테이블 확인
SELECT * FROM EMPLOYEE;
--뷰 확인
SELECT * FROM V_EMPLOYEE;
ROLLBACK;--뷰 확인
SELECT * FROM V_EMPLOYEE; --> 다시 조회하면 정중하로 바뀌어 있음
--뷰 생성
CREATE OR REPLACE VIEW V_EMP_JOB
AS SELECT EMP_ID, EMP_NAME, JOB_NAME,
DECODE(SUBSTR(EMP_NO, 8, 1), '1', '남', '2', '여'),
EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM HIRE_DATE)
FROM EMPLOYEE
JOIN JOB USING(JOB_CODE);
--must name this expression with a column alias
--컬럼의 별칭이 필요하다는 오류 생성됨--별칭 지정 후 다시 생성 => 성공적
CREATE OR REPLACE VIEW V_EMP_JOB(사번, 이름, 직급, 성별, 근무년수)
AS SELECT EMP_ID, EMP_NAME, JOB_NAME,
DECODE(SUBSTR(EMP_NO, 8, 1), '1', '남', '2', '여'),
EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM HIRE_DATE)
FROM EMPLOYEE
JOIN JOB USING(JOB_CODE);SELECT * FROM V_EMP_JOB;
--생성된 뷰 삭제
DROP VIEW V_EMP_JOB;--다시 조회해보자
SELECT * FROM V_EMP_JOB;
--"table or view does not exist"
--실행시 오류 발생--생성된 VIEW를 이용해서 CML(INSERT, UPDATE, DELETE) 사용 가능
--VIEW에서 요청한 DML구문은 베이스 테이블도 변경함
CREATE OR REPLACE VIEW V_JOB
AS SELECT JOB_CODE, JOB_NAME FROM JOB;SELECT * FROM V_JOB;
--뷰에 INSERT 사용;
INSERT INTO V_JOB VALUES('J8', '인턴');SELECT * FROM V_JOB; --8행에 인턴이 추가되어 있다.
뷰에 UPDATE 사용
UPDATE V_JOB
SET JOB_NAME = '알바'
WHERE JOB_CODE = 'J8';SELECT * FROM V_JOB;
--뷰에 DELETE 사용
DELETE FROM V_JOB
WHERE JOB_CODE = 'J8';SELECT * FROM V_JOB; --> 삭제됨
2. DML 명령어로 VIEW 조작이 불가능한 경우
1) 뷰 정의에 포함되지 않은 컬럼을 조작하는 경우
CREATE OR REPLACE VIEW V_JOB2
AS SELECT JOB_CODE
FROM JOB;SELECT * FROM V_JOB2;
2) 뷰에 정의되지 않은 컬럼(JOB_NAME)을 조작하려 해서 오류 발생
INSERT INTO V_JOB2 VALUES('J8', '인턴');
--"too many values"INSERT INTO V_JOB2 VALUES('J8');
--JOB_NAME은 NULL로 처리되어 입력SELECT * FROM JOB;
--JOB_NAME이 NULL값으로 변경됨
--해당 행 삭제해두기
DELETE FROM JOB
WHERE JOB_CODE = 'J8';SELECT * FROM JOB;
--8행 완전히 삭제됨
2) 뷰에 포함되지 않은 컬럼 중에, 베이스가 되는 테이블의 컬럼이 NOT NULL 제약조건이 지정된 경우
CREATE OR REPLACE VIEW V_JOB3
AS SELECT JOB_CODE, JOB_NAME
FROM JOB;SELECT * FROM V_JOB3;
2-1) 베이스 테이블인 JOB에 JOB_CODE는 NOT NULL인 컬럼이므로 JOB_NAME만 입력 불가
INSERT INTO V_JOB3 VALUES('인턴');
--cannot insert NULL into ("KH"."JOB"."JOB_CODE")
--오류 발생
2-2) 뷰에 정의되지 않은 컬럼 조작한 오류
INSERT INTO V_JOB3 VALUES('J8', '인턴');
--"too many values" 오류 발생2-3) 가지고 있는 컬럼으로 UPDATE/DELETE는 문제 없이 가능
INSERT INTO JOB VALUES('J8', '인턴');UPDATE V_JOB3
SET JOB_NAME = '알바'
WHERE JOB_NAME = '인턴';SELECT * FROM V_JOB3; -- > 알바 업뎃 됨
--여기 JOB_CODE 언제 넣었지
DELETE FROM V_JOB3
WHERE JOB_NAME = '알바';SELECT * FROM V_JOB3; -- > 알바 삭제됨
3) 산술 표현식으로 정의된 경우
CREATE OR REPLACE VIEW EMP_SAL
AS SELECT EMP_ID, EMP_NAME, SALARY,
(SALARY + (SALARY * NVL(BONUS, 0))) * 12 연봉
FROM EMPLOYEE;SELECT * FROM EMP_SAL;
3-1) 뷰에 산술 계산식이 포함된 경우 INSERT/UPDATE 에러
INSERT INTO EMP_SAL
VALUES (800, '정진훈', 3000000, 36000000);
--virtual column not allowed hereUPDATE EMP_SAL
SET 연봉 = 8000000000
WHERE EMP_ID = 200;
--virtual column not allowed here
--오류 발생--다만 DELETE는 가능
COMMIT; --커밋을 해줘야 복원 시점을 만들 수 있다DELETE FROM EMP_SAL
WHERE 연봉 = 124800000;SELECT * FROM EMP_SAL; --> 선동일 삭제 됨, 선동일의 연봉 금액
SELECT * FROM EMPLOYEE; --> 여기서도 삭제되었음
ROLLBACK;SELECT * FROM EMP_SAL; --> 선동일 씨 살아남
4) 그룹함수 또는 GROUP BY 절을 포함한 경우
CREATE OR REPLACE VIEW V_GROUPDEPT
AS SELECT DEPT_CODE, SUM(SALARY) 합계, AVG(SALARY) 평균
FROM EMPLOYEE
GROUP BY DEPT_CODE;SELECT * FROM V_GROUPDEPT;
4-1) 그룹함수 또는 GROUP BY를 사용한 경우 INSERT/UPDATE/DELETE시 에러 발생
INSERT INTO V_GROUPDEPT
VALUES ('D10', 600000000, 400000);
--virtual column not allowed here 오류 발생DELETE FROM V_GROUPDEPT
WHERE DEPT_CODE = 'D1';
--data manipulation operation not legal on this view 오류 발생
5) DISTINCT를 포함한 경우
CREATE OR REPLACE VIEW V_DT_EMP
AS SELECT DISTINCT JOB_CODE
FROM EMPLOYEE;SELECT * FROM V_DT_EMP; --행 4개만 보임
5-1) DISTINCT를 사용한 경우 INSERT/UPDATE/DELETE시 에러 발생
INSERT INTO V_DT_EMP VALUES ('J9');
--data manipulation operation not legal on this view 오류 발생
--데이터 조작 작업이 이 보기에 적합하지 않습니다.UPDATE V_DT_EMP
SET JOB_CODE = 'J9'
WHERE JOB_CODE = 'J7';
--data manipulation operation not legal on this view 오류 발생DELETE FROM V_DT_EMP
WHERE JOB_CODE = 'J1';
--data manipulation operation not legal on this view 오류 발생
6) JOIN을 이용해 여러 테이블을 연결한 경우
CREATE OR REPLACE VIEW V_JOINEMP
AS SELECT EMP_ID, EMP_NAME, DEPT_TITLE
FROM EMPLOYEE
JOIN DEPARTMENT ON(DEPT_CODE = DEPT_ID);
6-1) 뷰 정의시 JOIN을 사용한 경우 2개 테이블에 걸쳐서 INSERT/UPDATE 불가
INSERT INTO V_JOINEMP
VALUES (888, '조세오', '인사관리부');
--Columns belonging to more than one underlying table were either
-- inserted into or updated. 오류 발생
--둘 이상의 기본 테이블에 속한 열이 삽입되거나 업데이트되었습니다.UPDATE V_JOINEMP
SET DEPT_TITLE = '인사관리부'
WHERE EMP_ID = 219;
--An attempt was made to insert or update columns of a join view which
-- map to a non-key-preserved table. 오류 발생
--키가 보존되지 않은 테이블에 매핑되는 결합 뷰의 열을 삽입하거나 업데이트하려고 했습니다.COMMIT;--EMP_ID를 조건으로 DELETE 수행 가능
DELETE FROM V_JOINEMP
WHERE EMP_ID = 219;SELECT * FROM EMPLOYEE;
ROLLBACK;SELECT * FROM EMPLOYEE; --> 원상복구
3. VIEW 구조
-뷰 정의 시 사용한 쿼리 문장이 TEXT 컬럼에 저장되어 있으며 뷰가 실행될 때는 TEXT에 기록된 SELECT에 문장이 다시 실행 되면서 결과를 보여주는 구조
사용자 정의 뷰 확인
SELECT *
FROM USER_VIEWS;
4. VIEW 옵션
*VIEW 생성 표현식
CREATE [OR REPLACE] [FORCE | NOFORCE] VIEW 뷰이름 [(별칭)]
AS SUBQUERY
[WITH CHECK OPTION]
[WITH READ ONLY];
1) OR REPLACE 옵션
: 기존에 동일한 뷰 이름이 존재하는 경우 덮어쓰고, 존재하지 않는 경우 새로 생성함
CREATE OR REPLACE VIEW V_EMP2
AS SELECT EMP_NO, EMP_NAME
FROM EMPLOYEE;SELECT * FROM V_EMP2;
1-1) OR REPLACE 옵션 사용시 덮어쓰기 됨
CREATE OR REPLACE VIEW V_EMP2
AS SELECT EMP_NO, EMP_NAME, SALARY
FROM EMPLOYEE;SELECT * FROM V_EMP2;
1-2) OR REPLACE 옵션 제거 시 덮어쓰기 되지 않음
CREATE VIEW V_EMP2
AS SELECT EMP_NO, EMP_NAME
FROM EMPLOYEE;
--name is already used by an existing object 중복
2) FORCE / NOFORCE 옵션
(1) FORCE : 서브 쿼리에 사용된 테이블이 존재하지 않아도 뷰 생성
CREATE OR REPLACE FORCE VIEW V_EMP
AS SELECT TCODE, TNAME, TCONTENT
FROM TT;
--경고: 컴파일 오류와 함께 뷰가 생성되었습니다. 오류 발생SELECT * FROM V_EMP;
--KH.V_EMP" has errors 에러 발생
(2) NOFORCE : 서브쿼리에 사용된 테이블이 존재해야만 뷰 생성(기본값)
CREATE OR REPLACE /*NOFORCE*/ VIEW V_EMP2 --NOFORCE를 써도 안써도 같음
AS SELECT TCODE, TNAME, TCONTENT
FROM TT;
--"table or view does not exist" 오류 발생
3) WITH CHECK OPTION
: 해당 제약조건 안에서만 삽입, 삭제, 수정 가능
CREATE OR REPLACE VIEW V_EMP3
AS SELECT *
FROM EMPLOYEE
WHERE DEPT_CODE = 'D1'
WITH CHECK OPTION;SELECT * FROM V_EMP3;
INSERT INTO V_EMP3
--DEPT_CODE가 뷰를 생성할 떄 조건으로 사용되었으므로
--DEPT_CODE 값이 D2일 때 INSERT 불가
--DEPT_CODE D1으로 수정 시 INSERT 가능
VALUES (666, '오현정', '661111-2012345', 'oh_hj@kh.or.kr',
'01022442211', 'D2', 'J7', 'S1', 8000000, 0.1, 201, SYSDATE,
NULL, 'N');
--view WITH CHECK OPTION where-clause violation 오류 발생
--D1인 조건 하에서만 컨트롤이 가능하다COMMIT;INSERT INTO V_EMP3
VALUES (666, '오현정', '661111-2012345', 'oh_hj@kh.or.kr',
'01022442211', 'D1', 'J7', 'S1', 8000000, 0.1, 201, SYSDATE,
NULL, 'N');
--> 이건 삽입SELECT * FROM EMPLOYEE;
ROLLBACK; --> 원상복구 됨
3-1) D1 -> D2 UPDATE => 불가능
UPDATE V_EMP3
SET DEPT_CODE = 'D2'
WHERE DEPT_CODE = 'D1';
--view WITH CHECK OPTION where-clause violation 오류 발생
3-2) D1을 DELETE할 수 있음
COMMIT;DELETE FROM V_EMP3
WHERE DEPT_CODE = 'D1';SELECT * FROM EMPLOYEE;ROLLBACK;
4) WITH READ ONLY
: 뷰에 대해서 조회만 가능(DML 수행 불가)
CREATE OR REPLACE VIEW V_DEPT
AS SELECT *
FROM DEPARTMENT
WITH READ ONLY;DELETE FROM V_DEPT;
--cannot perform a DML operation on a read-only view
--오류 발생
--지울 수 없다