[학습목표]
- DCL < 데이터 제어 언어 >
- TCL < 트랜잭션 제어 언어 (COMMIT, ROLLBACK) >
- VIEW
💡DCL (DATE CONTROL LANGUAGE)
=> 데이터 제어 언어
계정에게 시스템 권한 또는 객체접근권한을
부여(GRANT)하거나,회수(REVOKE)하는 구문
-- 계정생성구문
CREATE USER 계정명 IDENTIFIED BY 계정명;
-- 권한부여구문
GRANT CREATE TABLE/VIEW TO 계정명;
-- 최소한의 권한
-- CONNECT : 접속권한
-- RESOURCE : 객체생성권한1. 📒 TCL (트랜잭션언어)
- COMMIT : 변경사항을 실제 DB에 반영시키겠다.
- ROLLBACK : 변경사항을 삭제한 후, 마지막 COMMIT시점으로 돌아가겠다.
- SAVEPOINT : 현재 시점에 해당하는 포인트명으로 임시저장점을 정의하겠다.
💡
트랜잭션이란 ?
- 데이터베이스의
논리적연산단위이다.- 데이터의
변경사항(DML)을 하나로 묶어서 처리할 때 필요한 개념DML문을 한번 수행할 때 트랜잭션이존재하면 해당트랜잭션에 같이 묶어서 처리- 트랜잭션이
존재하지 않으면새로운 트랜잭션 만들어서 묶음
--1)
SELECT * FROM EMP_01
--사번이 900번인 사원 지우기
DELETE FROM EMP_01
WHERE EMP_ID = 900;
ROLLBACK;
-- ROLLBACK하는 순간 트랜잭션은 사라짐
-- 즉, 900번 사원 지운 변경사항은 사라짐
-- 실제 DB까지 도달 못함
--2)
-- 800번 사원 추가하기
INSERT INTO EMP_01
VALUES(800,'홍길동','총무부');
COMMIT;
-- 트랜잭션에 있던 사항들이 실제 DB에 반영됨
-- ROLLBACK해도 수정 전으로 돌아가지않음 !
--3)
--217,216번 사원 삭제하기
DELETE FROM EMP_01
WHERE EMP_ID IN (216,217);
SAVEPOINT SP -- 임시저장점
--700번 사원추가하기
INSERT INTO EMP_01
VALUES(700, '김똥개', '인사팀');
-- 200번 사원 삭제하기
DELETE FROM EMP_01
WHERE EMP_ID = 200;
ROLLBACK TO SP;
-- SAVEPOINT SP를 저장한 이후 데이터는 삭제됨
COMMIT; -- 216,217 사원만 삭제됨💡 DDL문(CREATE, ALTER, DROP)을 수행하는 순간 무조건 COMMIT 실행됨(실제 DB에 반영) --> ROLLBACK 안됨
💡 즉, DDL문 수행 전 변경사항들이 있었다면 정확히 픽스(COMMIT, ROLLBACK)하고 진행하기 !
2. 📒 VIEW
- SELECT문(쿼리문)을 저장해둘 수 있는 객체
- 임시테이블 같은 존재 (=실제 데이터가 담겨있지는 않음 => 논리적 테이블이라고도 함)
💡 VIEW 생성
CREATE [OR REPLACE] VIEW 뷰명 AS 서브쿼리
> [OR REPLACE] : 뷰 생성시 중복된 이름의 뷰가 없다면 새롭게 뷰 생성 / 중복된 이름의 뷰가 있다면 해당 뷰를 변경하는 옵션(갱신)
--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 = '러시아';
--2단계) VIEW 생성!
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를 생성해놓고
-- 한국에서 근무하는 사원 조회
SELECT *
FROM VW_EMPLOYEE
WHERE NATIONAL_NAME = '한국';
-- 간단하게 쿼리문으로 사원 조회 가능 !
2-1. 📒 VIEW 컬럼에 별칭 부여
🔴 서브쿼리의 SELECT절에 산술연산식, 함수식을 기술했을 경우, 반드시 별칭을 지정해야함 !
--사번, 이름, 직급명, 성별(남/녀), 근무년수 조회
CREATE OR REPLACE 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);
--근무년수가 20년 이상인 사원 조회
SELECT 이름, 직급명
FROM VW_EMP_JOB
WHERE 근무년수 >= 20;
-- 여자사원만 조회
SELECT *
FROM VW_EMP_JOB
WHERE 성별 ='여';
-- 뷰 삭제하고자 한다면?
DROP VIEW VW_EMP_JOB;
📝 EXTRACT(YEAR | MONTH | DAY FROM DATE) 📝
📌 특정 날짜로부터 년도/월/일 만 추출해서 반환해주는 함수
