/*
<VIEW>
SELECT문을 저장할 수 있는 객체(논리적인 가상 테이블)
데이터를 저장하고 있지 않으며 테이블에 대한 SQL만 저장되어 있어 VIEW에 접근할 때 SQL을 수행하면서 결과값을 가져온다.
[표현법]
CREATE [OR REPLACE] VIEW 뷰명
AS 서브쿼리;
-- [OR REPLACE] : 기본 객체가 존재하는 경우 덮어쓰기
*/
-- '한국'에서 근무하는 사원들의 사번, 이름, 부서명, 급여, 근무국가명을 조회
SELECT emp_id, emp_name, dept_title, salary, national_name
FROM emp
LEFT JOIN dept ON(dept_code = dept_id)
JOIN location ON(location_id = local_code)
JOIN national USING(national_code)
WHERE national_name = '한국';
------------------------------------------------------------------------
-- 뷰 생성
------------------------------------------------------------------------
CREATE OR REPLACE VIEW v_emp
AS SELECT emp_id, emp_name, dept_title, salary, national_name
FROM emp
LEFT JOIN dept ON(dept_code = dept_id)
LEFT JOIN location ON(location_id = local_code)
LEFT JOIN national USING(national_code);
SELECT *
FROM v_emp
WHERE national_name='러시아';
-- 총무부에서 근무하는 사원의 사원명 급여 조회
SELECT emp_name, salary, dept_title
FROM v_emp
WHERE dept_title='총무부';
SELECT * FROM USER_VIEWS;
-- 사원의 사번, 사원명, 성별, 근무년수를 조회할 수 있는 뷰를 생성
CREATE VIEW v_emp_01
AS
SELECT emp_id, emp_name, DECODE(SUBSTR(emp_no,8,1),'1','남','2','여','3','남','4','여', '확인') AS 성별,
EXTRACT(YEAR FROM SYSDATE)-EXTRACT(YEAR FROM hire_date) AS 근무년수
FROM emp;
SELECT * FROM v_emp_01 WHERE emp_name='송종기';
-- VIEW 삭제
DROP VIEW v_emp_01;
-- 별칭을 주는 방법2
CREATE VIEW v_emp_02("사번", "사원명", "성별", "근무년수")
AS
SELECT emp_id, emp_name, DECODE(SUBSTR(emp_no,8,1),'1','남','2','여','3','남','4','여', '확인') AS 성별,
EXTRACT(YEAR FROM SYSDATE)-EXTRACT(YEAR FROM hire_date) AS 년수
FROM emp;
SELECT * FROM v_emp_02;
/*
<VIEW를 이용해서 DML(INSERT, UPDATE, DELETE) 사용>
- 뷰를 통해 데이터를 변경하게되면 실제 데이터가 담겨있는 기본 테이블에도 적용됨
*/
-- 테이블을 복사해서 뷰 생성
CREATE VIEW v_job
AS SELECT * FROM job;
SELECT * FROM v_job;
-- VIEW를 통해 테이블에 INSERT
INSERT INTO v_job VALUES('J8', '알바');
-- VIEW를 통해 테이블에 UPDATE
UPDATE v_job SET job_name='인턴' WHERE job_code = 'J8';
-- VIEW를 통해 테이블에 DELETE
DELETE v_job WHERE job_code='J8';
/*
<DML 구문으로 VIEW조작이 불가능한 경우>
*/
-- 1) 뷰 정의에 포함되지 않는 컬럼을 조작하는 경우
CREATE OR REPLACE VIEW v_job
AS SELECT job_code FROM job;
INSERT INTO v_job VALUES('J8', '인턴'); -- 값의 수가 너무 많습니다
INSERT INTO v_job VALUES('J8'); -- 삽입 가능
UPDATE v_job SET job_name='알바' WHERE job_code='J8'; -- 부적합한 식별자 job_name이 없음
-- 2) 뷰에 포함되지 않은 컬럼 중 기본테이블에 NOT NULL 제약조건이 지정된 경우
CREATE OR REPLACE VIEW v_job
AS SELECT job_name FROM job;
INSERT INTO v_job VALUES('알바'); -- 삽입 불가
-- 3) 산술 표현식으로 정의된 경우
CREATE VIEW v_emp_sal
AS SELECT emp_id, emp_name, salary, salary*12 AS 연봉
FROM emp;
SELECT * FROM v_emp_sal;
-- 산술표현식으로 만들면 삽입 불가 가상 열은 사용할 수 없습니다
INSERT INTO v_emp_sal VALUES('230', '강수정', '3000000', '30000000');
-- 산술표현식으로 만들면 업데이트 불가 가상 열은 사용할 수 없습니다
-- 산술연산과 무관한 컬럼은 변경 가능
UPDATE v_emp_sal SET 연봉 = 3000000 WHERE emp_name = '선동일';
-- 4) 그룹함수나 GROUP BY절을 포함한 경우
-- 부서별 급여의 합계와 평균을 구하세요
-- 부서번호, 합계, 평균
CREATE OR REPLACE VIEW v_emp_sal
AS
SELECT dept_code, SUM(salary) 합계, FLOOR(AVG(salary)) 평균
FROM emp
JOIN dept ON(dept_code=dept_id)
GROUP BY dept_code;
-- 5) DISTINCT를 포함한 경우
-- 6) JOIN을 이용해 여러 테이블을 연결한 경우
-- VIEW를 생성 할 수 있지만 DML문을 이용하여 데이터를 조작 할 수 없다
/*
<VIEW 옵션>
CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW 뷰이름
AS 서브쿼리
[WITH CHECK OPTION]
[WITH READ ONLY];
- OR REPLACE : 기존에 동일한 뷰가 있으면 덮어쓰고, 없으면 신규로 생성
- FORCE : 서브쿼리에 기술된 테이블이 존재하지 않아도 뷰가 생성됨
- NOFORCE : 서브쿼리에 기술된 테이블이 존재 해야지만 뷰가 생성됨(기본값)
- WITH CHECK OPTION : 서브쿼리에 기술된 조건에 부합하지 않는 값으로 수정하는 경우 오류를 발생 시킴
- WITH READ ONLY : 뷰에 대한 조회만 가능(DML 사용 불가)
*/
-- 1)OR REPLACE : 있으면 덮어쓰기 없으면 생성
CREATE OR REPLACE VIEW v_emp_01
AS SELECT emp_name, salary, hire_date FROM emp;
SELECT * FROM v_emp_01;
-- 2) FORCE / NOFORCE
CREATE FORCE VIEW v_emp_02
AS SELECT tcode, tname tcontect FROM tt;
SELECT * FROM v_emp_02; -- 테이블이 없어서 조회 안됨 테이블을 생성하고 난 이후부터 조회가능
CREATE TABLE TT(TCODE NUMBER, TNAME VARCHAR2(10), TCONTENT VARCHAR2(20));
-- 3) WITH CHECK OPTION
CREATE VIEW v_emp_03
AS
SELECT *
FROM emp
WHERE salary>=3000000
WITH CHECK OPTION;
-- ERR 조건이 300이상이므로 200일땐 오류
UPDATE v_emp_03 SET salary = 2000000 WHERE emp_id=200;
-- 4) WITH READ ONLY(읽기 전용)
CREATE VIEW v_dept
AS SELECT * FROM dept
WITH READ ONLY;
SELECT * FROM v_dept;
-- ERR 읽기 전용 뷰에서는 DML 작업을 수행할 수 없습니다.
UPDATE v_dept SET dept_id = 'D10' WHERE dept_title = '총무부';
/*
<TCL(Transaction Control Language)>
- 트랜젝션을 제어하는 언어
* 트랜젝션
- 하나의 논리적인 작업 단위 EX)ATM기에서 현금을 출금/타행송금 등 1.카드삽입 2.메뉴선택 3.금액확인 및 인증 4.계좌에서 금액차감 5.현금인출 6.종료
- 각각의 작업들을 묶어서 하나의 작업단위로 만들어 놓은것
- 하나의 트랜젝션으로 이루어진 작업들은 반드시 한번에 완료가 되어야 함
- 그렇지않으면 모두 취소가 되어야 한다.
- 데이터의 변경사항(DML)을 묶어서 하나의 트랜젝션에 담아 처리한다.
- COMMIT : 트랜젝션 종류 처리 후 저장
- ROLLBACK : 트랜젝션 취소
- SAVEPOINT : 임시저장
*/
DROP TABLE emp_01;
CREATE TABLE emp_01 AS
SELECT emp_id, emp_name, dept_title
FROM emp, dept
WHERE dept_code = dept_id(+);
-- 200번 사원 삭제
DELETE FROM emp_01 WHERE emp_id IN(200);
-- SAVEPOINT 지정
SAVEPOINT SE;
-- 201 삭제
DELETE FROM emp_01 WHERE emp_id = 201;
SELECT * FROM emp_01 ORDER BY emp_id;
--
ROLLBACK TO SE;
ROLLBACK;
-- DDL 구문을 실행하는 순간 기존 메모리 버퍼에 임시 저장된 변경사항들이 DB에 커밋됨 -> 롤백 불가능
CREATE TABLE TEST (
tid NUMBER
);
댓글