[KH정보교육원] 23일차_DCL, TCL, VIEW

2021.11.11(목)

🌿 DCL

• DATA CONTROL LANGUAGE, 데이터 제어
• GRANT, REVOKE
• 계정에게 객체 접근 권한, 시스템 권한 부여(GRANT)하거나 회수(REVOKE)하는 언어
• 작업 흐름 예시: Local Documents > informatique > database > 20211111 폴더의 "Data Control Language_관리자 계정, sample 계정.pdf" 파일 참조

🌱 GRANT

• 권한 부여
• 표현법: GRANT 권한1, 권한2, ... TO 계정명;
• 시스템 권한: 특정 DB에 접근하는 권한 + 객체 생성할 수 있는 권한
  • CREATE SESSION: 계정에 접속할 수 있는 권한
  • CREATE TABLE: 테이블을 생성할 수 있는 권한
  • CREATE VIEW: 뷰를 생성할 수 있는 권한
  • CREATE SEQUENCE: 시퀀스를 생성할 수 있는 권한
  • CREATE USER: 계정을 생성할 수 있는 권한
  • 객체 접근 권한: 특정 객체들에 접근해서 조작할 수 있는 권한
• 관리자 계정에서 계정 생성, 권한 관리

🌱 REVOKE

• 권한 회수 시 사용하는 명령어
• 표현법: REVOKE 권한1, 권한2, ... FROM 사용자이름;

🌿 TCL

• TRANSACTION CONTROL LANGUAGE, 트랜잭션 제어 언어
• 데이터의 변경사항(DML; INSERT, UPDATE, DELETE)들을 하나의 트랜잭션에 묶어서 처리 → 트랜잭션의 대상이 되는 SQL = INSERT, UPDATE, DELETE(DML)
• COMMIT(확정)하기 전까지는 변경사항들을 하나의 트랜잭션에 담게 됨

트랜잭션(TRANSACTION)= 데이터베이스의 논리적 연산 단위

🌱 COMMIT

• 트랜잭션 종료 처리 후 확정
• 하나의 트랜잭션에 담겨있는 변경사항들을 실제 DB/물리적 파일에 반영 → 실제 반영시킨 후 트랜잭션은 비워짐

🌱 ROLLBACK

• 트랜잭션 취소
• 하나의 트랜잭션에 담겨있는 변경사항들을 삭제한 후, 마지막 COMMIT 시점으로 돌아감

🌱 SAVEPOINT

• 임시/중간 저장
• 지금 시점 임시 저장점 정의

⚠️ DDL 구문(CREATE, ALTER, DROP)을 실행하는 순간 기존에 트랜잭션에 있던 모든 변경사항들을 무조건 실제 DB에 반영(COMMIT)시킨 후(=트랜잭션 비워진 뒤) DDL이 수행됨 → DDL 수행 전 변경사항이 있었다면 정확히 FIX(COMMIT, ROLLBACK)하고 DDL을 실행해야 함

🌿 VIEW

• SELECT(쿼리문)를 저장해둘 수 있는 객체

ORACLE의 객체: 테이블, USER, 뷰 등 → DDL 사용해서 정의

• 자주 쓰는 긴 SELECT문을 VIEW로 저장해두면 긴 SELECT문을 매번 다시 기술할 필요 없음

• 임시 테이블 → 실제 데이터가 들어가는 것은 아님, 논리적인 것

• 테이블(x) 서브쿼리가 텍스트(o) 형식으로 저장됨

• 생성 방법

• CREATE (OR REPLACE) VIEW 뷰명 AS 서브쿼리;

• 해당 계정이 가지고 있는 VIEW들에 대한 내용을 조회하고자 한다면 DATE DICTIONARIES 중 USER_VIEWS 조회 → SELECT * FROM USER_VIEWS;

• 서브쿼리의 SELECT절에 함수나 산술연산식이 기술되어 있는 경우 별칭 지정 필요

  • 뷰 별칭 부여 방법1) 함수나 산술연산식이 기술된 컬럼만 (필수적으로) 별칭 부여

    CREATE OR REPLACE VIEW VW_EMP_JOB AS SELECT EMP_ID, EMP_NAME, JOB_NAME, DECODE(SUBSTR(EMP_NO, 8, 1), '1', '남자', '2', '여자') 성별, CASE WHEN ENT_DATE IS NULL THEN EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM HIRE_DATE) WHEN ENT_DATE IS NOT NULL THEN EXTRACT(YEAR FROM ENT_DATE) - EXTRACT(YEAR FROM HIRE_DATE) END 근무년수 FROM EMPLOYEE LEFT JOIN JOB USING (JOB_CODE);

  • 뷰 별칭 부여 방법2) 모든 컬럼에 대한 별칭을 다 기술

    CREATE OR REPLACE VIEW VW_EMP_JOB(사번, 사원명, 직급명, 성별, 근무년수) AS SELECT EMP_ID, EMP_NAME, JOB_NAME, DECODE(SUBSTR(EMP_NO, 8, 1), '1', '남자', '2', '여자'), CASE WHEN ENT_DATE IS NULL THEN EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM HIRE_DATE) WHEN ENT_DATE IS NOT NULL THEN EXTRACT(YEAR FROM ENT_DATE) - EXTRACT(YEAR FROM HIRE_DATE) END FROM EMPLOYEE LEFT JOIN JOB USING (JOB_CODE);

• 뷰를 이용해서 DML(INSERT, UPDATE, DELETE) 사용/데이터 조작 가능 → 뷰를 통해서 데이터 변경해도 실제 데이터가 담겨있는 베이스/실제 테이블에 적용이 됨

  • 단, 뷰를 가지고 DML이 불가능한 경우가 더 많음 → 많이 안 쓰임
    • 뷰에 정의되어 있지 않은 컬럼 조작 시
    • NOT NULL 제약조건이 지정된 경우
    • 산술연산식이나 함수를 통해서 정의되어 있는 경우
    • 그룹함수나 GROUP BY절이 포함된 경우
    • DISTINCT 구문(행들의 중복을 제거하고, 중복된 값 중 가장 위에 있는 값을 반환)이 포함된 경우
    • JOIN을 이용해서 여러 테이블을 매칭시켜놓은 경우

• VIEW 옵션(생략 가능)

  • OR REPLACE: 해당 뷰가 존재하지 않으면 새로 생성 vs 존재하면 갱신시켜주는 옵션
  • FORCE: 서브쿼리에 기술된 테이블이 존재하지 않아도 뷰 생성
    NOFORCE(기본 값): 서브쿼리에 기술된 테이블이 반드시 존재해야만 뷰 생성
  • WITH CHECK OPTION: 서브쿼리의 조건절에 기술된 내용에 만족하는 값으로만 DML 가능 → 조건에 부합하지 않은 값으로 수정하는 경우 오류 발생
  • WITH READ ONLY: 뷰에 대해 조회만 가능, DML 수행 불가

  CREATE OR REPLACE NOFORCE VIEW VW_EMP
  AS SELECT *
     FROM EMPLOYEE
     WHERE SALARY >= 3000000
  WITH CHECK OPTION;
  
  CREATE OR REPLACE VIEW VW_EMPBONUS
  AS SELECT EMP_ID, EMP_NAME, BONUS
     FROM EMPLOYEE
     WHERE BONUS IS NOT NULL
  WITH READ ONLY;

🌿 SQL 종합 연습 문제

1. 직급코드 기준 사원 이상 과장 미만의 직원들을 찾아 사번, 사원명, 직급명을 조회하시오.

   -- 내가 푼 방법  
   SELECT EMP_ID, EMP_NAME, JOB_NAME  
   FROM EMPLOYEE E, JOB J  
   WHERE E.JOB_CODE = J.JOB_CODE  
   AND E.JOB_CODE IN ('J7', 'J6');
   
   -- 강사님 방법  
   SELECT EMP_ID, EMP_NAME, JOB_NAME  
   FROM EMPLOYEE  
   LEFT JOIN JOB USING (JOB\_CODE)  
   WHERE 'J5' < JOB_CODE AND JOB_CODE <= 'J7'; -- JOB 테이블 보고 직급 HIERARCHY 확인 후 조건식 만듦

2. 회사에서 OFFICE 프로그램을 불법으로 사용하다 벌금이 부과되었다. 그 결과 회사 측은 전 직원들에게도 책임이 있다며 급여의 0.1%씩 강제로 기부받겠다고 한다. 그렇다면 EMPLOYEE 테이블을 활용하여 남사원 여사원 각각 총 얼마의 금액을 기부하게 되는지 조회하시오.

    -- 내가 푼 방법
    SELECT "사원", CONCAT(TO_CHAR(SUM(SALARY) * 0.001, '999,999,999'), '원') "총 기부금"
    FROM (SELECT DECODE(SUBSTR(EMP_NO, 8, 1), 1, '남자사원', 2, '여자사원') 사원, SALARY FROM EMPLOYEE)
    GROUP BY 사원;
   
    -- 강사님 방법
    SELECT DECODE(SUBSTR(EMP_NO, 8, 1), 1, '남자사원', 2, '여자사원') "사원",
           TO_CHAR(SUM(SALARY) * 0.001, '999,999') || '원' "총 기부금"
    FROM EMPLOYEE
    GROUP BY SUBSTR(EMP_NO, 8, 1);

3. EMPLOYEE 테이블에서 매니저 별로 관리하는 사원들을 조회하여 한 매니저가 관리하는 사원들의 총 인원 수를 구하여, 매니저명, 관리사원 수로 조회하시오.

   -- 나는 시간 내에 해결 못함  
   -- 강사님 설명  
   SELECT "매니저 이름", COUNT("관리사원 이름")||'명' "관리사원 수"  
   FROM (SELECT E2.EMP_NAME "매니저 이름", E1.EMP_NAME "관리사원 이름"  
   FROM EMPLOYEE E1  
   JOIN EMPLOYEE E2 ON (E1.MANAGER_ID = E2.EMP_ID))  
   GROUP BY "매니저 이름";

📗 소감

• SQL 기초 수업도 마무리 되어간다. 뒷부분으로 올 수록 반복과 연습이 덜 되어 덜 익숙한 경우가 많은데, 복습 잘 해두자!
• 강사님께서 추후 프로젝트에서 view를 사용해보라고 하셨는데, DML이 불가능한 경우들이 많으므로, 어떤 상황에서 유용하게 쓰일 수 있을지 생각해보자
• SQL문 복잡한 경우(특히 서브쿼리, 그룹함수) 아직 작성 못하는 것들이 있다. 더 연습하자!

📗 homework:

• 일반 연산자의 종류
  1. 산술 연산자: +, -, /, * -> 조회하고자 하는 컬럼들을 나열하는 SELECT절에 산술연산을 기술해서 결과 조회 가능하다.
  2. 비교 연산자: 표현식 사이의 관계를 비교하기 위해 사용하고, 비교 결과는 TRUE, FALSE, NULL 중 하나가 된다. =(일치하는가), >(큰가), <(작은가), >=(크거나 같은가), <=(작거나 같은가), !=, ^=, <>(일치하지 않는가) 및 (NOT) BETWEEN AND(특정 범위에 포함되는가), (NOT) LIKE(문자 패턴 비교), IS (NOT) NULL(NULL값인가), (NOT) IN(비교하려는 목록에 일치하는 값이 있는가) 등이 있다.
  3. 논리 연산자: 여러 개 조건의 결과를 엮어 하나의 논리 결과로 만들어주는 연산자로, AND(그리고, ~이면서), OR(또는, ~이거나), NOT(부정)이 있다.
  4. 연결 연산자: 여러 컬럼의 값들을 하나의 컬럼인 것처럼 연결하거나 컬럼과 리터럴을 연결하는 연산자로, ||이다.
• WHERE절은 조회(SELECT절)하고자 하는 테이블에 특정 조건을 제시하고 그 조건을 만족하는 데이터만을 조회하고자 할 때 기술하는 구문이다. "SELECT 조회하고자 하는 컬럼, 컬럼, .. FROM 테이블명 WHERE 조건;"과 같이 표현한다.

-> comment: 나만의 방식으로 생각/이해하려 해 보고, 내가 이해한대로, 나의 생각대로 써보기