[9일차] ETC(DCL, TCL) , VIEW(뷰)

오늘도 할게 많다 빠르게 정리해보자!

DCL

데이터 제어문

계정에게 시스템 권한 또는 객체접근 권한을 부여하거나 회수하는 구문
>시스템권한 : DB에 접근하는 권한, 객체를 생성할 수 있는 권한
>객체접근권한: 특정 객체들을 조작할 수 있는 권한

CREATE USER 계정명 IDENTIFIED BY 비밀번호;
GRANT 권한(RESOURCE, CONNECT) TO 계정;

우리는 이미 계정을 만들 때 사용했던 적이 있다.

SELECT *
FROM ROLE_SYS_PRIVS;

간단한 코드를 보면 알 수 있다.

여긴 별로 배운게 없어서 빠르게 넘어가도록 하겠다 !!

TCL

트랜잭션

-데이터베이스의 논리적 연산단위
-데이터의 변경사항(DML)등을 하나의 트랜잭션에 묶어서 처리
DML문 한개를 수행할 때 트랜잭션이 존재하지 않는다면 트랜잭션을 만들어서 묶음
트랜잭션이 존재한다면 해당 트랜잭션에 묶어서 처리

COMMIT하기 전까지의 변경사항들을 하나의 트랜잭션에 담는다.
- 트랜잭션에 대상이 되는SQL : INSERT, UPDATE,DELETE

COMMIT(트랜잭션 종료 처리 후 확정)
ROLLBACK(트랜잭션 취소)
SAVEPOINT(임시저장)

-COMMIT : 한 트랜잭션에 담겨있는 변경사항들을 실제 DB에 반영시키겠다는 의미
-ROLLBACK : 한 트랜잭션에 담겨있는 변경사항들을 삭제(취소) 한 후 마지막 COMMIT시점으로 돌아감
-SAVEPOINT 포인트명 : 현재 시점에 해당 포인트명으로 임시저장을 해두는 것

내 생각대로의 트랜잭션 : 데이터베이스는 안정성과 신뢰성이 매우 중요하기 때문에 실수를 방지하기 위해 이런 안전장치를 만들어 놓은 느낌이다. 명령들이 모여 미리보기식으로 한 번 실행하고

COMMIT으로 너 진짜 그거 할거야? 진짜 할꺼야? 이런 느낌으로 생각된다.

하지만 단점도 있다.

한 트랜잭션이 끝나지않고 오류가 발생하게 된다면 다른 데이터베이스를 사용하는 곳에서 값을 가져올 수 없게된다.

ROLLBACK

방금 한 행동을 되돌리기!

CREATE TABLE EMP_01
AS (SELECT EMP_ID, EMP_NAME, DEPT_TITLE
    FROM EMPLOYEE
    JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID));

--사번이 200, 201번인 사람 삭제
DELETE FROM EMP_01
WHERE EMP_ID = 200;

DELETE FROM EMP_01
WHERE EMP_ID = 201;

이렇게 200번과 201번을 삭제하고 값을 불러보면

없어진 것을 볼 수 있다.

여기서 다시 ROLLBACK을 하고 값을 보면

ROLLBACK;

SELECT * FROM EMP_01;

사라졌었던 200번과 201번이 돌아오게 된다!

이게 바로 트랜잭션, COMMIT을 하기전까지 데이터베이스에 반영되지 않는다.

그럼 COMMIT을 하고 확인해보자

--사번이 200, 201번인 사람 삭제
DELETE FROM EMP_01
WHERE EMP_ID = 200;
DELETE FROM EMP_01
WHERE EMP_ID = 201;

COMMIT;
ROLLBACK;
SELECT * FROM EMP_01;

다시 돌아오지 못하는 것을 볼 수 있다...

SAVEPOINT

게임을 좋아하면 한 번쯤 들어봤을 용어이다. 말 그대로 저장하는 포인트를 만들어서 내가 저장한 포인트로 시간을 돌리는 것이다.

--217, 216, 214사원 삭제
DELETE FROM EMP_01
WHERE EMP_ID IN (217, 216, 214);

SELECT * FROM EMP_01;

SAVEPOINT SP;

사원 217,216,214 를 삭제하고 SAVEPOINT를 지정했다.

INSERT INTO EMP_01
VALUES(801, '김말똥', '기술지원부');

DELETE FROM EMP_01
WHERE EMP_ID = 210;

SELECT * FROM EMP_01;

ROLLBACK TO SP;

그럼 이 코드를 실행했을 때 결과는

마지막 ROLLBACK SP 가 실행되서 #2번 코드는 그냥 없던 일이 되버린다.

*여기서 주의사항

DDL문(CREATE, ALTER, DROP)을 수행하는 순간 기존 트랜잭션에 있던 변경사항들은
무조건 COMMIT이 된다(실제 DB반영이 된다.)
즉, DDL문 수행전 변경사항들이 있다면 정확하게 픽스하고 해라!

앞으로 우리가 삭제할 일은 거의 없을 것이다. 이미 만들어져 있는게 많고 값을 가져오고 보내고를 많이 할 것 같다.

그래도 삭제하면 안되니 하면 안되는 것을 배우는 것도 중요한 것 같다.

VIEW (뷰)

SELECT문(쿼리문)을 저장해둘 수 있는 객체
(자주 사용하는 SELECT문을 저장해두면 긴 SELECT문을 매번 다시 기술할 필요가 없다.)
임시테이블 같은 존재(실제 데이터가 담겨있는 건 아니다 => 논리적인 테이블)

VIEW 생성방법
[표현식]
CREATE VIEW 뷰명 AS 서브쿼리

****암묵적인 약속****
테이블의 네이밍은 TB_ 로 시작하고
뷰의 네이밍은 VW_ 로 시작한다고 한다 !

뷰의 장점

우린 항상 SELECT할 때 길고 긴 문장을 사용해야 했다. 다른 값을 가져올 때도 WHERE 조건문만 살짝 바꿔서 복붙하고 가져오는 번거로움이 있는데 그것이 해결된다!
코드가 간략해지므로 가독성이 매우 높아진다.

뷰의 단점

아직까진 초보단계라 찾지못했다.

그럼 장점 1번을 확인해보자

만약 우리가 한국, 일본, ~~러시아~~ 에서 근무하는 사람을 찾고싶다면

예전방식으로

--한국에서 근무하는 사원들의 사번, 이름, 부서명, 급여, 근무국가명 조회
SELECT EMP_ID, EMP_NAME, DEPT_TITLE, SALARY, NATIONAL_NAME
FROM EMPLOYEE
JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID)
JOIN LOCATION ON (LOCATION_ID = LOCAL_CODE)
JOIN NATIONAL USING(NATIONAL_CODE)
WHERE NATIONAL_NAME = '한국';

--러시아에서 근무하는 사원들의 사번, 이름, 부서명, 급여, 근무국가명 조회
SELECT EMP_ID, EMP_NAME, DEPT_TITLE, SALARY, NATIONAL_NAME
FROM EMPLOYEE
JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID)
JOIN LOCATION ON (LOCATION_ID = LOCAL_CODE)
JOIN NATIONAL USING(NATIONAL_CODE)
WHERE NATIONAL_NAME = '러시아';

--일본에서 근무하는 사원들의 사번, 이름, 부서명, 급여, 근무국가명 조회
SELECT EMP_ID, EMP_NAME, DEPT_TITLE, SALARY, NATIONAL_NAME
FROM EMPLOYEE
JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID)
JOIN LOCATION ON (LOCATION_ID = LOCAL_CODE)
JOIN NATIONAL USING(NATIONAL_CODE)
WHERE NATIONAL_NAME = '일본';

이렇게 매우 거지같은 과정을 반복해야 했지만 이젠 해방이다.

뷰의 표현법에 맞춰 생성해준다. (WHERE절은 빼는게 포인트)

CREATE VIEW VW_EMPLOYEE
AS SELECT EMP_ID, EMP_NAME, DEPT_TITLE, SALARY, NATIONAL_NAME
    FROM EMPLOYEE
    JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID)
    JOIN LOCATION ON (LOCATION_ID = LOCAL_CODE)
    JOIN NATIONAL USING(NATIONAL_CODE);

이렇게 만들어 놓으면 JAVA형식에선 메소드 하나 만들어놓고 재사용하는 것과 같다.

자 그럼 위에 거지같은 코드를 다시 작성해보자

--한국에서 근무하는 사원들의 사번, 이름, 부서명, 급여, 근무국가명 조회
SELECT *
FROM VW_EMPLOYEE
WHERE NATIONAL_NAME = '한국';

--러시아에서 근무하는 사원들의 사번, 이름, 부서명, 급여, 근무국가명 조회
SELECT *
FROM VW_EMPLOYEE
WHERE NATIONAL_NAME = '러시아';

--일본에서 근무하는 사원들의 사번, 이름, 부서명, 급여, 근무국가명 조회
SELECT *
FROM VW_EMPLOYEE
WHERE NATIONAL_NAME = '일본';

절반 이하로 줄었다. 매우 편리하다.

그렇다면 동작 원리는 무엇일까?

SELECT * FROM (SELECT EMP_ID, EMP_NAME, DEPT_TITLE, SALARY, NATIONAL_NAME
    FROM EMPLOYEE
    JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID)
    JOIN LOCATION ON (LOCATION_ID = LOCAL_CODE)
    JOIN NATIONAL USING(NATIONAL_CODE));

느낌을 살리기 위해 코드로 가져왔다.

코드에 보면 나와있듯 FROM에 서브쿼리로 가져온 것이다. (예전에 배웠음)

**주의사항**

CREATE VIEW VW_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);

위 코드를 실행할 시 에러가 나게된다.

이유는 무엇일까?

A) 서브쿼리의 SELECT절에 함수식이나 산술연산식이 기술되어있을 경우 반드시 별칭을 지정해야한다.

그렇다면 수정된 코드를 봐보자

CREATE VIEW VW_EMP_JOB
AS SELECT EMP_ID, EMP_NAME, JOB_NAME,
          DECODE(SUBSTR(EMP_NO, 8, 1),'1','남','2','여') AS "성별",
          EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM HIRE_DATE) AS "근무년수"
   FROM EMPLOYEE
   JOIN JOB USING(JOB_CODE);

이러면 오류가 나지 않는다

그러면 왜 별칭부여를 해줘야할까?

현실세계로 예를 들어보면,

우리는 왜 법을 지켜야할까? -> 법을 지키지 않으면 나쁜사람이다.

우리는 왜 코드의 규칙을 지켜야할까? -> 규칙을 지키지 않으면 나쁜사람이다.

그냥 코드의 큐칙일 뿐이다. 받아들이고 외워라.

뷰를 통해서 DML(INSERT, UPDATE, DELETE)

뷰는 REFERENCES의 개념이다.

즉 뷰를 통해서 조작하게 되면 실제 데이터가 담겨있는 테이블에 반영이 된다는 뜻이다.

코드를 통해서 확인해보자

CREATE VIEW VW_JOB
AS SELECT JOB_CODE, JOB_NAME
    FROM JOB;

이렇게 뷰를 생성해주고 두 테이블의 값을 가져오면

사진은 하나지만 둘이 실행결과가 같다는 것을 알 수 있다.

그럼 여기서 INSERT 해보자

--뷰를 통해서 INSERT
INSERT INTO VW_JOB VALUES('J8', '인턴');

SELECT * FROM JOB;

진짜 참조의 개념이다. 값이 추가가 됐다.

물론 UPDATE도 가능하다.

--뷰를 통해서 UPDATE
UPDATE VW_JOB
SET JOB_NAME = '알바'
WHERE JOB_CODE = 'J8';

VIEW 옵션

[상세표현식] CREATE [OR REPLACE] [FORCE | NOFORCE] VIEW 뷰명
AS 서브쿼리
[WITH CHECK OPTION]
[WITH READ ONLY];

1) OR REPLACE : 기존에 동일한 뷰가 있을 경우 갱신하고, 존재하지 않을 경우 새로 생성해라
2) FORCE | NOFORCE
> FORCE : 서브쿼리에 기술된 테이블이 존재하지 않아도 뷰가 생성되도록해라
> NOFORCE : 서브쿼리에 기술된 테이블이 존재하는 테이블이여야만 뷰가 생성되게 하는 옵션(기본값)
3) WITH CHECK OPTION : DML시 서브쿼리에 기술된 조건에 부합한 값으로만 DML이 가능하도록
4) WITH READ ONLY : 뷰에 대해서 조회만 가능하도록

OR REPLACE

CREATE OR REPLACE VIEW VW_EMPLOYEE
AS SELECT EMP_ID, EMP_NAME, DEPT_TITLE, SALARY, NATIONAL_NAME, BONUS
    FROM EMPLOYEE
    JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID)
    JOIN LOCATION ON (LOCATION_ID = LOCAL_CODE)
    JOIN NATIONAL USING(NATIONAL_CODE);

이건 간단하니 한 번만 보겠다.

위 처럼 VIEW 를 생성할 때 ON REPLACE를 적어주면 된다.

(없으면 생성, 있으면 덮어씌우기)

FORCE | NOFORCE

NOFORCE

CREATE OR REPLACE NOFORCE VIEW VW_EMP
AS SELECT TCODE, TNAME, TCONTENT
    FROM TT;

우선 기본값으로 설정되어 있는 NOFORCE이다.

기준이되는 테이블이 없다면 생성이 되지 않는다.

FORCE

CREATE OR REPLACE FORCE VIEW VW_EMP
AS SELECT TCODE, TNAME, TCONTENT
    FROM TT;

FORCE이다. 기준이 되는 테이블이 없어도 생성이된다.

그리고 값을 보기 위해 SELECT를 해주면

SELECT * FROM VW_EMP;

오류가 나며 실행이 되지 않게된다.

이것을 풀어보자면,

나 : 일단 테이블 없어도 뷰 좀 만들어봐 내가 이따가 테이블 만들게

컴퓨터 : 그래 대신 테이블 만들기 전까지 못쓴다?

나 : 그래그래

WITH CHECK OPTION

서브쿼리에 기술된 조건에 부합하지 않는 값으로 수정시 오류 발생

CREATE OR REPLACE VIEW VW_EMP
AS SELECT *
   FROM EMPLOYEE
   WHERE SALARY >= 3000000
WITH CHECK OPTION;

이렇게 VIEW를 생성하고 SALARY에는 조건이 달려있다.

여기서 200번 사원의 급여를 200만원으로 바꿔보자

UPDATE VW_EMP
SET SALARY = 2000000
WHERE EMP_ID = 200;

실행이 되지 않았다. 이유는?

WITH CHECK OPTION를 기술함으로써

200번사원 급여를 200만원으로 변경(SALARY >= 3000000조건에 맞지않는 변경)이 되지않는다.

WITH READ ONLY

뷰에 대해 조회만 가능하도록한다.

CREATE OR REPLACE VIEW VW_EMP
AS SELECT EMP_ID, EMP_NAME, BONUS
    FROM EMPLOYEE
    WHERE BONUS IS NOT NULL
    WITH READ ONLY;

위 처럼 VIEW를 READ ONLY로 만들어주고

DELETE
FROM VW_EMP
WHERE EMP_ID = 200;

삭제를 시도하면

오류가 나게된다.

당연하다 우리가 WITH READ ONLY 를 기술하여 읽기 전용으로 만들었기 때문이다.

솔직히 참조의 개념이라 VIEW를 통해 실제 데이터의 값이 바뀌는 것은 위험하다고 생각이 된다. 그래서 VIEW를 만들때 원래 목적에 맞게 읽고 보기만 하게끔 WITH READ ONLY는 항상 써줘야 할 것 같은 느낌이 든다.

이렇게 VIEW가 끝났다. 데이터베이스의 초반에 SELECT가 너무 방대하고 많아서 나머지는 정말 훅훅 지나가는 것 같다.

언젠가 녀석들이 다 모여서 쓰나미가 되어 덮치면 숨도 못쉬고 휩쓸릴 수 있으니 복습을 열심히해서 노아의방주를 만들어놓자

'데이터베이스' 카테고리의 다른 글

[마지막] TRIGGER (트리거) (0)	2024.02.29
[10일차] SEQUENCE(시퀀스), PL/SQL (0)	2024.02.28
[8일차] DML(Data Manipulation Language), DDL(Data Definition Language) (0)	2024.02.26
[7일차] DDL(CREATE) (2)	2024.02.25
[6일차] SUBQUERY (0)	2024.02.24

도들

[9일차] ETC(DCL, TCL) , VIEW(뷰)

DCL

TCL

ROLLBACK

SAVEPOINT

VIEW (뷰)

뷰를 통해서 DML(INSERT, UPDATE, DELETE)

VIEW 옵션

OR REPLACE

FORCE | NOFORCE

WITH CHECK OPTION

WITH READ ONLY

'데이터베이스' 카테고리의 다른 글

티스토리툴바

[9일차] ETC(DCL, TCL) , VIEW(뷰)

DCL

TCL

ROLLBACK

SAVEPOINT

VIEW (뷰)

뷰를 통해서 DML(INSERT, UPDATE, DELETE)

VIEW 옵션

OR REPLACE

FORCE | NOFORCE

WITH CHECK OPTION

WITH READ ONLY

'데이터베이스' 카테고리의 다른 글

관련글

티스토리툴바