You YoungSEok yys@kangwon.ac.kr 고급 SQL You YoungSEok yys@kangwon.ac.kr.

Presentation on theme: "You YoungSEok yys@kangwon.ac.kr 고급 SQL You YoungSEok yys@kangwon.ac.kr."— Presentation transcript:

1 You YoungSEok yys@kangwon.ac.kr
고급 SQL You YoungSEok

2 목 차 View Materialized View Trigger

3 View View의 생성 View의 수정 View의 삭제

4 View 뷰(View)는 하나 이상의 테이블을 합쳐서 만든 가상 테이블 장점 편리성 재사용성 보안성
미리 정의된 뷰를 일반 테이블처럼 사용할 수 있기 때문에 편리 사용자가 필요한 정보만 요구에 맞게 가공하여 뷰로 만들어 쓸 수 있음 재사용성 자주 사용되는 질의를 뷰로 미리 정의해 놓을 수 있음 보안성 각 사용자 별 필요한 데이터만 선별하여 보여줄 수 있음 중요한 질의의 경우 질의 내용을 암호화할 수 있음

5 View의 생성 기본 문법 tbl_book 테이블에서 ‘축구’라는 문구가 포함된 자료만 보여주는 뷰
CREATE VIEW 뷰이름 AS SELECT 구문; tbl_book 테이블에서 ‘축구’라는 문구가 포함된 자료만 보여주는 뷰 위 SELECT 문을 이용해 작성한 뷰 정의문

6 View의 수정 기본 문법 CREATE OR REPLACE VIEW 뷰이름 AS SELECT 구문; 앞에 만들었던 tbl_book 테이블에서 ‘축구’라는 문구가 포함된 자료만 보여주는 뷰를 ‘야구’라는 문구가 포함된 자료만 보여주는 뷰로 수정 위 SELECT 문을 이용해 작성한 뷰 정의문

7 View의 삭제 기본 문법 DROP VIEW 뷰이름 앞서 생성한 뷰 v_book를 삭제 DROP VIEW v_book;

8 실습 2-1 주소에 ‘대한민국’을 포함하는 고객들로 구성된 뷰를 만드시오.

9 실습 2-2 실습 2-1에서 만든 뷰를 ‘영국’ 주소를 가진 고객으로 변경하시오.

10 실습 2-3 도서명, 도서출판사, 고객명을 확인할 수 있는 뷰를 만드시오. Join 참고

11 실습 2-4 고객이름과 도서이름을 바로 확인할 수 있는 뷰를 생성한 후, ‘김연아’ 고객이 구 입한 도서의 주문번호, 도서이름, 주문 금액을 조회하시오. JOIN을 이용해 테이블들을 합쳐야 함

12 Materialized View Mview의 생성 Mview의 삭제

13 Materialized View 일반 뷰는 쿼리 요청마다 테이블에서 해당되는 데이터를 가져옴 하지만 MView에서 해당되는 데이터를 View에 저장해 둠 장점 데이터를 View에서 바로 가져오기 때문에 속도가 빠름 단점 원본 테이블이 수정 될 때마다 View의 내용과 동기화 해줘야 함 구체화 뷰를 만들기 전에 생성권한을 얻어야 함 GRANT CREATE MATERIALIZED VIEW TO system; ALTER SESSION SET query_rewrite_enabled = true; ALTER SESSION SET query_rewrite_integrity = enforced;

14 Mview의 생성 기본 문법 tbl_book 테이블에서 ‘축구’라는 문구가 포함된 자료만 보여주는 구체화 뷰
CREATE MATERIALIZED VIEW 뷰이름 BUILD IMMEDIATE : Mview를 생성하는 즉시 서브쿼리를 수행하여 데이터를 Mview에 저장함 REFRESH ON [ DEMEND | COMMIT ] DEMEND 동기화 관련 프로시저를 수행하면 동기화 함 COMMIT 원본 테이블에 데이터 변경이 생기면 동기화 [ COMPLETE | FAST | FORCE | NEVER ] COMPLETE 모든 데이터를 동기화 FAST 원본 테이블에서 변경된 데이터만 동기화 (View Log를 사용해야 함) FORCE FAST가 가능하면 FAST로 아니면 COMPLETE로 동기화 NEVER 동기화를 사용하지 않음 AS SELECT 문 tbl_book 테이블에서 ‘축구’라는 문구가 포함된 자료만 보여주는 구체화 뷰 위 SELECT 문을 이용해 작성한 구체화 뷰 정의문

15 Mview의 삭제 기본 문법 앞서 생성한 뷰 mv_book를 삭제 DROP MATERIALIZED VIEW 뷰이름
DROP MATERIALIZED VIEW mv_book;

16 실습 2-5 도서 별 주문 금액을 확인할 수 있는 구체화 뷰를 만드시오. 데이터 변경마다 동기화가 일어나게 하시오.
도서 별 주문 금액을 확인할 수 있는 구체화 뷰를 만드시오. 데이터 변경마다 동기화가 일어나게 하시오. JOIN을 이용하여 테이블을 합쳐야 함 GROUP BY 절을 이용하여 도서 이름을 그룹으로 묶어야 함 집계함수를 이용하여 합계를 구해야 함 SUM() 주문금액으로 정렬(내림차순)

17 Trigger Trigger의 생성 Trigger의 수정 Trigger의 삭제

18 Trigger 임의의 Table에 SQL이 실행 되면 암시적으로 실행되는 프로시저 뷰가 아닌 Table에 대해서만 정의가 가능
트랜잭션 제어문을 사용할 수 없음

19 Trigger의 생성 기본 문법 CREATE TRIGGER 트리거이름 BEFORE | AFTER
[INSERT OR DELETE OR UPDATE] ON 테이블이름 어떤 쿼리에 반응할지 정함 ex ) INSERT OR UPDATE , INSERT OR DELETE OR UPDATE [FOR EACH ROW] 데이터 처리 시 매 건마다 트리거 실행 (INESRT와 DELETE에는 필수) DECLARE 변수 선언부 BEGIN 트리거 코드 END;

20 Trigger의 생성 tbl_book 테이블의 특정 bookid(2)를 INSERT/UPDATE/DELETE 할 수 없게 하는 트리거

21 Trigger의 수정 기본 문법 CREATE OR REPLACE TRIGGER 트리거이름 BEFORE | AFTER
[INSERT OR DELETE OR UPDATE] ON 테이블이름 어떤 쿼리에 반응할지 정함 ex ) INSERT OR UPDATE , INSERT OR DELETE OR UPDATE [FOR EACH ROW] 데이터 처리 시 매 건마다 트리거 실행 (INESRT와 DELETE에는 필수) DECLARE 변수 선언부 BEGIN 트리거 코드 EXCEPTION WHEN 예외 처리 END

22 Trigger의 수정 앞에 만든 트리거에서 UPDATE 쿼리는 허용하게 하는 트리거

23 Trigger의 삭제 기본 문법 앞에서 만든 트리거를 삭제 DROP TRIGGER 트리거 이름
DROP TRIGGER REFUSE_BOOKID_2

24 실습 2-6 tbl_book테이블에 INSERT, UPDATE 쿼리의 처리가 완료 된 후에 쿼리들의 실행 기록을 기록하는 트리거를 작성하라. CREATE TABLE tbl_book_log (type VARCHAR(20), updatetime VARCHAR(30)); 현재 시간 받아오는 함수 : to_char(sysdate,'yyyy/mm/dd hh24:mi:ss')