💡뷰
뷰(View)는 데이터베이스 객체(테이블, 제약 사항, 뷰, 시퀀스) 중 하나이다.
가상 테이블, 뷰 테이블 등으로 부른다.
테이블이라고 부르는 이유는 실제로 뷰를 테이블처럼 사용하기 때문이다.
🔹정의: 쿼리(SQL)를 저장하는 객체
🔹목적: 권한 통제
뷰의 사용
뷰 구문
create [or replace] view 뷰이름
as
select문;뷰 구문에서 [or replace]를 작성하지 않아도 된다. 하지만 작성하는 게 더 편하기 때문에 보편적으로 함께 작성하는 편이다.
뷰 오브젝트 생성
CREATE OR REPLACE VIEW vwInsa
AS
SELECT * FROM tblInsa;
SELECT * FROM vwInsa; --tblInsa 테이블의 복사본
뷰 오브젝트 vwInsa를 하나 생성하여 출력하였다. 그러자 실제 테이블과 동일하게 출력한다.
이 때문에 뷰를 테이블을 복사했다고 설명하기도 한다. (하지만 이는 올바른 표현이 아니다.)
쿼리의 양 감소
CREATE OR REPLACE VIEW 영업부
AS
SELECT
num,
name,
city,
basicpay,
substr(ssn, 8) AS ssn
FROM tblInsa
WHERE buseo = '영업부';영업부 직원만 출력한다고 하자.
어떤 쿼리를 반복적으로 해야 할 때, 계속 작성하기 번거로울 수 있다.
이럴 때 뷰를 사용하기 좋다! 이제 뷰를 출력해 보도록 하자.
SELECT * FROM 영업부;
원래 해야 하는 SELECT 작업이 굉장히 짧아지고, 가독성도 좋아졌다.
이처럼 뷰를 이용하면 쿼리의 양을 대폭 줄일 수 있다.
반복되는 쿼리
CREATE OR REPLACE VIEW vwCheck
as
SELECT
m.name AS 회원,
v.name AS 비디오,
r.rentdate AS 대출일,
r.retdate AS 반납일
FROM tblRent r
INNER JOIN tblVideo v
ON r.video = v.seq
INNER JOIN tblMember m
ON m.seq = r.MEMBER;
SELECT * FROM vwCheck;
내가 비디오 대여점 사장이라는 가정을 하자.📼
그러면 날마다 누가 어떤 비디오를 언제 빌려갔고, 반납했는지 확인하는 업무를 하게 될 것이다. 이렇게 자주 반복되는 작업에 뷰를 사용하면 된다.
또한 뷰를 잘 만들려면 기존의 SELECT문을 잘 쓰는 방법을 연구하면 된다.
CASE END 활용
CREATE OR REPLACE VIEW vwCheck
AS
SELECT
m.name AS 회원,
v.name AS 비디오,
r.rentdate AS 대출일,
r.retdate AS 반납일,
g.period AS 대여기간,
r.rentdate + g.period AS 반납예정일,
CASE
WHEN r.retdate IS NOT NULL THEN NULL
ELSE round(sysdate - (r.rentdate + g.period))
END AS 연체일,
CASE
WHEN r.retdate IS NOT NULL THEN NULL
ELSE round(sysdate - (r.rentdate + g.period)) * g.price * 0.1
END AS 연체료
FROM tblRent r
INNER JOIN tblVideo v ON r.video = v.seq
INNER JOIN tblMember m ON m.seq = r.MEMBER
INNER JOIN tblGenre g ON g.seq = v.genre;
더 나아가 반납 예정일과 연체일, 연체료를 구한다고 할 때, 위와 같이 Case End를 활용할 수 있다.
쿼리(SQL)의 저장
CREATE OR REPLACE VIEW vwSeoul
AS
SELECT * FROM tblInsa WHERE city = '서울'; --뷰를 만드는 시점 > 20명
SELECT * from vwSeoul WHERE city = '서울'; --20명
UPDATE tblInsa SET city = '제주' WHERE num IN (1001, 1005, 1008); --제주도로 이사
SELECT * FROM tblInsa WHERE city = '서울'; --17명테이블만 수정했을 뿐인데, 서울 인원을 제주도로 이사를 보내자 20명에서 17명으로 뷰가 수정이 되었다.
테이블을 조작하면 뷰에도 반영이 되고, 뷰를 조작하면 테이블에도 반영이 된다.
이는 뷰가 테이블을 '복사'하는 역할이 아니라 쿼리를 저장하는 메소드같은 역할을 하는 것으로 볼 수 있다.
따라서 원본 테이블을 수정한 뒤, 뷰를 호출하면 갱신된 데이터를 반환한다.
접근 권한
신입 사원에게 문자를 발송하는 업무를 부여하고자 hr 계정의 비밀번호를 알려주었다. 그런데 신입 사원이 보면 안 되는 데이터까지 tblInsa에 들어 있는 상태이다.
이때 신입 사원이 봐도 되는 데이터만 뷰로 만들어서 접근 권한을 부여할 수 있다.
SELECT * FROM tblInsa; --신입 계정 > tblInsa 접근 권한 X
SELECT * FROM vwTel; --신입 계정 > tblInsa 접근 읽기 권한 O
CREATE OR REPLACE VIEW vwTel
AS
SELECT name, buseo, jikwi, tel FROM tblInsa;
권한으로는 읽기 권한, 쓰기 권한, 삭제 권한으로 나누어서 권한을 부여하는 것도 가능하다.
뷰는 주로 자주 사용하는 SELECT문을 저장하는 오브젝트로 사용된다. 그리고 권한의 대상이 되기 때문에 접근 권한을 통제하는 역할로도 자주 사용된다.
뷰 사용 주의점 (feat. 읽기 전용)
--단순뷰
CREATE OR REPLACE VIEW vwTodo
AS
SELECT * FROM tblTodo;
SELECT * FROM vwTodo;
INSERT INTO vwTodo VALUES ((SELECT max(seq) + 1 FROM tblTodo), '할일', sysdate, NULL)
UPDATE vwTodo SET title = '할일 완료' WHERE seq = 23;
DELETE FROM vwTodo WHERE seq = 23;- SELECT > 실행 O > 뷰는 읽기 전용으로만 사용한다.
- INSERT > 실행 O > 절대 사용 금지
- UPDATE > 실행 O > 절대 사용 금지
- DELETE > 실행 O > 절대 사용 금지
쿼리를 구성하는 테이블의 개수가 1개의 테이블로 구성된 경우 '단순뷰'라고 부른다.
보통 단순뷰는 CRUD가 모두 실행되는 편이다. CRUD가 실행이 되지만, 사용하지 말라고 하는 이유는 '복합뷰'의 경우 안 되는 경우가 훨씬 더 많기 때문이다.
그리고 뷰는 쓰기 작업에 사용되기 위해 만들어진 기능이 아니다. 뷰는 읽기 전용 테이블이다.
INSERT 일련번호
시퀀스 객체가 오라클 전용이라고 했다.
따라서 시퀀스 객체가 없는 경우에는 INSERT를 할 때마다 번호의 최댓값보다 1개 더 큰 숫자를 일련번호로 지정하여 저장하는 방법을 사용한다는 점을 참고하여 알아두자.
📜뷰 사용 문서
자사는 개발자의 안전과 편안함을 최우선시합니다. 이와 같은 이유로, 모든 개발자는 이 규칙을 따라야만 합니다.
규칙을 어길 시에는 불필요한 조치가 있을 것입니다.
1. 해당 문서는 뷰의 사용 방법에 대한 설명입니다.
2. 셔츠를 입은 사람이 뷰에 대해 묻는다면 뷰는 테이블과 동일하게 출력하므로 테이블을 복사했다고 설명하십시오.
3. 반복되는 쿼리에 뷰를 사용하십시오. 쿼리의 양이 삭제됨
4. 뷰로 테이블을 수정하는 사람이 있다면 자리를 피하시길 바랍니다. 뷰는 테이블을 수정하기 위한 기능이 아닙니다.
6. 해당 문서의 2번 항목은 존재하지 않습니다. 2번 항목의 반대로 행동하십시오.
7. 셔츠를 입은 사람은 면접관입니다. 사무실 내에 텐트가 있다면 즉시 면접관에게 말하고 회사를 빠져나오십시오. 그리고 절대로 뒤를 돌아보지 마십시오.
이 규칙들은 개발자가 더욱 안전하고 편안하게 SQL을 작성할 수 있도록 도와줄 것입니다.
