251029 TIL

DDL(Data Definition Language)

• 데이터베이스 생성 또는 객체들을 생성 유지 관리하는 명령어
• 권한 필요
• CREATE, ALTER, DROP, TRUNCATE, RENAME, COMMENT

DCL(Cont.)

시스템 권한 회수

REVOKE create session FROM insa;

객체 권한 부여

• SYS 또는 객체 소유자가 부여

INSA 에서 HR 소유 테이블 조회

• hr.employees 테이블에 대한 SELECT 권한 없음

SYS 에서 객체 권한 부여

GRANT SELECT ON hr.employees TO insa;
SELECT * FROM dba_tab_privs WHERE grantee = 'INSA';

INSA 에서 확인

SELECT * FROM user_tab_privs;

SELECT * FROM hr.employees;

HR 에서 확인

SELECT * FROM user_tab_privs;

HR 에서 객체 권한 부여

GRANT select ON hr.departments TO insa;

객체 권한 회수

• SYS 또는 객체 소유자가 회수

REVOKE SELECT ON hr.departments FROM insa;

DML(Data Manipulation Language)

• 데이터베이스에 데이터를 입력, 수정, 삭제하는 명령어
• INSERT, UDPATE, DELETE, MERGE
• 명령어 수행 시 트랜잭션이 시작됨
• COMMIT 또는 ROLLBACK 을 수행하여 트랜잭션을 종료시켜줘야 함
• COMMIT 또는 ROLLBACK 을 수행하기 전에는 현재 작업 중인 세션과 다른 세션에서 보이는 데이터가 다름
  • UNDO 세그먼트를 통해 변경 사항을 관리

INSERT

• 테이블에 새로운 행을 입력하는 SQL문

INSERT INTO 소유자.테이블(컬럼, 컬럼, ...)
VALUES (데이터, 데이터, ...)

default 값 입력

INSERT INTO insa.emp(id, name)
VALUES(3, 'james');

INSERT INTO insa.emp(id, name, day)
VALUES(4, 'henry', default);

INSERT INTO insa.emp(id, name, day)
VALUES(5, default, default);

INSERT INTO insa.emp(id, name, day)
VALUES(6, 'khai', null);

• 해당 컬럼 및 데이터를 생략하고 입력
• 또는 데이터 부분에 default 키워드로 입력
• default 값이 선언되지 않은 컬럼에 default 키워드로 데이터를 입력하면 null 로 입력됨
• default 값이 선언되어 있더라도 null을 입력하면 null로 입력됨

UPDATE

• 특정한 필드(field) 값을 수정하는 SQL 문

UPDATE 소유자.테이블
SET 컬럼 = 새로운값, 컬럼 = 새로운값, ...
WHERE 조건;

• WHERE절이 없을 경우 테이블 전체 행 수정

UPDATE insa.emp
SET day = to_date('2025-10-01', 'yyyy-mm-dd');

default 값으로 수정

UPDATE insa.emp
SET day = default
WHERE id = 2;

• default 로 설정된 sysdate로 변경됨

null 값으로 수정

UPDATE insa.emp
SET day = null
WHERE id = 2;

DELETE

• 행을 삭제하는 SQL 문

DELETE FROM 소유자.테이블
WHERE 조건;

• WHERE절이 없을 경우 테이블 전체 행 삭제

DML 서브쿼리

테스트 환경 세팅

• 테이블 복제, CTAS

-- 테이블 구조, 데이터, 제약조건 중 NOT NULL 만 복제
CREATE TABLE emp
AS
SELECT * FROM hr.employees;

-- 컬럼 지정해서 복제
CREATE TABLE emp
AS
SELECT employee_id id, last_name || ' ' || first_name name, salary sal, department_id dept_id FROM hr.employees;

-- 테이블 구조만 복제, 데이터는 복제 X
CREATE TABLE emp
AS
SELECT * FROM hr.employees
WHERE 1 = 2;

• 사용자 소유 테이블 조회

SELECT * FROM user_tables;

• 사용자 소유 오브젝트 조회

SELECT * FROM user_objects;

• 오브젝트 구조 확인

DESC hr.emp

• 사용자 소유 테이블 컬럼 정보 조회

SELECT * FROM user_tab_columns WHERE table_name = 'EMP';

INSERT 서브쿼리

INSERT INTO hr.emp
SELECT * FROM hr.employees;

테스트 환경 세팅

• 테이블 삭제

DROP TABLE hr.emp PURGE;

• emp 테이블 생성 후 employees 테이블 데이터 복제해서 입력

CREATE TABLE hr.emp
(id number,
name varchar2(30),
dept_id number,
dept_name varchar2(30))
TABLESPACE users;

INSERT INTO hr.emp(id, name)
SELECT employee_id, last_name
FROM hr.employees;

COMMIT;

UPDATE 서브쿼리

UPDATE hr.emp
SET name = (SELECT last_name FROM hr.employees WHERE employee_id = 100)
WHERE id = 100;

상호 연관 UPDATE 서브쿼리

UPDATE hr.emp e
SET dept_id = (SELECT department_id 
                FROM hr.employees 
                WHERE employee_id = e.id);

UPDATE hr.emp e
SET dept_name = (SELECT department_name
                FROM hr.departments 
                WHERE department_id = e.dept_id);    

DELETE 서브쿼리

DELETE FROM hr.emp
WHERE id IN (SELECT employee_id
                FROM hr.employees
                WHERE hire_date >= to_date('2003-01-01', 'yyyy-mm-dd')
                AND hire_date < to_date('2004-01-01', 'yyyy-mm-dd'));

SELECT /*+ gather_plan_statistics */ *
FROM hr.emp
WHERE id IN (SELECT employee_id
                FROM hr.employees
                WHERE hire_date >= to_date('2003-01-01', 'yyyy-mm-dd')
                AND hire_date < to_date('2004-01-01', 'yyyy-mm-dd'));

---------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |      1 |        |      6 |00:00:00.01 |      13 |       |       |          |
|*  1 |  HASH JOIN         |           |      1 |     16 |      6 |00:00:00.01 |      13 |  1517K|  1517K| 1028K (0)|
|*  2 |   TABLE ACCESS FULL| EMPLOYEES |      1 |     16 |      6 |00:00:00.01 |       6 |       |       |          |
|   3 |   TABLE ACCESS FULL| EMP       |      1 |    107 |    107 |00:00:00.01 |       7 |       |       |          |
---------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("ID"="EMPLOYEE_ID")
   2 - filter(("HIRE_DATE"<TO_DATE(' 2004-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND 
              "HIRE_DATE">=TO_DATE(' 2003-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))   

상호 연관 DELETE 서브쿼리

DELETE FROM hr.emp e
WHERE exists (SELECT 'x'
                FROM hr.employees
                WHERE employee_id = e.id
                AND hire_date >= to_date('2003-01-01', 'yyyy-mm-dd')
                AND hire_date < to_date('2004-01-01', 'yyyy-mm-dd'));

SELECT /*+ gather_plan_statistics */ *
FROM hr.emp e
WHERE exists(SELECT 'x'
                FROM hr.employees
                WHERE employee_id = e.id
                AND hire_date >= to_date('2003-01-01', 'yyyy-mm-dd')
                AND hire_date < to_date('2004-01-01', 'yyyy-mm-dd'));

---------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |      1 |        |      6 |00:00:00.01 |      13 |       |       |          |
|*  1 |  HASH JOIN SEMI    |           |      1 |     16 |      6 |00:00:00.01 |      13 |   972K|   972K| 1253K (0)|
|   2 |   TABLE ACCESS FULL| EMP       |      1 |    107 |    107 |00:00:00.01 |       7 |       |       |          |
|*  3 |   TABLE ACCESS FULL| EMPLOYEES |      1 |     16 |      6 |00:00:00.01 |       6 |       |       |          |
---------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("EMPLOYEE_ID"="E"."ID")
   3 - filter(("HIRE_DATE"<TO_DATE(' 2004-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND 
              "HIRE_DATE">=TO_DATE(' 2003-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))   

주의

자동 COMMIT이 발생할 때

DELETE FROM hr.emp e
WHERE exists (SELECT employee_id
                FROM hr.job_history
                WHERE employee_id = e.id); 

SELECT *
FROM hr.emp e
WHERE exists (SELECT employee_id
                FROM hr.job_history
                WHERE employee_id = e.id);         

CREATE TABLE hr.test(id number) TABLESPACE users;   

SELECT *
FROM hr.emp e
WHERE exists (SELECT employee_id
                FROM hr.job_history
                WHERE employee_id = e.id);    

ROLLBACK; -- 롤백 안됨

SELECT *
FROM hr.emp e
WHERE exists (SELECT employee_id
                FROM hr.job_history
                WHERE employee_id = e.id); 

• DDL, DCL 문장을 수행하면 내부적으로 AUTO COMMIT 수행
  • 내부적으로 딕셔너리 테이블에 DML 작업이 수행되기 때문에 COMMIT이 수행됨
• DDL, DCL문과 DML은 절대 같은 세션 안에서 수행하면 안됨
• SQL*Plus에서 exit로 종료하면 자동 commit 수행

• SQL*Plus에서 conn을 수행하면 자동 commit 수행

• SQL*Plus에서 exit나 conn을 수행하기 전에 트랜잭션 종료시키기

자동 ROLLBACK이 발생할 때

• SQL*Plus이 비정상적으로 종료되었을 때
• DML 문장을 수행하고 있는 컴퓨터가 비정상적으로 종료되었을 때
• client-server 환경에서 네트워크 장애가 발생하는 경우

다중 테이블 INSERT(9i)

• source 테이블에서 데이터를 추출해서 여러 target 테이블에 데이터를 로드(적재)하는 SQL문
• ETL(Extraction(추출), Transfromation(변환), Load(적재))

BAD PRACTICE

INSERT INTO hr.sal_history(id, day, sal)
SELECT employee_id, hire_date, salary
FROM hr.employees;

INSERT INTO hr.mgr_history(id, mgr, sal)
SELECT employee_id, manager_id, salary
FROM hr.employees;

• 동일한 source 테이블을 여러 번 추출해야 함
• source 테이블이 대용량일수록 부하
• source 테이블을 한 번만 읽은 후 여러 target 테이블에 나눠서 저장하게 하려면 프로그래밍적 구현 필요

1. 무조건 INSERT ALL

INSERT ALL
INTO hr.sal_history(id, day, sal) VALUES(employee_id, hire_date, salary)
INTO hr.mgr_history(id, mgr, sal) VALUES(employee_id, manager_id, salary)
SELECT employee_id, hire_date, manager_id, salary
FROM hr.employees;

• 9i 버전부터는 INSERT ALL 명령어 지원

INSERT ALL
INTO hr.sal_history(id, day, sal) VALUES(no, hire, sal)
INTO hr.mgr_history(id, mgr, sal) VALUES(no, mgr_id, sal)
SELECT employee_id no, hire_date hire, manager_id mgr_id, salary*1.1 sal
FROM hr.employees;

• 별칭 지정 가능

2. 조건 INSERT ALL

INSERT ALL
WHEN day < to_date('2005-01-01', 'yyyy-mm-dd') AND sal >= 5000 THEN
    INTO hr.emp_history(id, day, sal) VALUES (id, day, sal)
WHEN comm IS NOT NULL THEN
    INTO hr.emp_sal(id, comm, sal) VALUES(id, comm, sal)
SELECT employee_id id, hire_date day, salary sal, commission_pct comm
FROM hr.employees;

• WHEN 조건 THEN INTO …
• 조건에 따라 INSERT 가능

3. 조건 FIRST INSERT

INSERT FIRST
WHEN salary < 5000 THEN
    INTO hr.sal_low(id, name, sal) VALUES(employee_id, last_name, salary)
WHEN salary BETWEEN 5000 AND 10000 THEN
    INTO hr.sal_mid(id, name, sal) VALUES(employee_id, last_name, salary)
ELSE
    INTO hr.sal_high(id, name, sal) VALUES(employee_id, last_name, salary)
SELECT employee_id, last_name, salary
FROM hr.employees;

• 첫 번째로 만족한 조건만 처리

TCL(Transaction Control Language)

• 데이터의 일관성을 유지하면서 안정적으로 데이터를 복구시키기 위한 명령어
• COMMIT, ROLLBACK, SAVEPOINT

Transaction(트랜잭션)

• 논리적으로 DML을 하나로 묶어서 처리하는 작업 단위

COMMIT

• DML 작업을 영구히 저장
• 트랜잭션 시작 부분(첫 DML 문장)부터 COMMIT 을 만날 때까지 작업한 내용 전부 저장

ROLLBACK

• DML 작업을 영구히 취소
• 트랜잭션 시작 부분(첫 DML 문장)부터 ROLLBACK 을 만날 때까지 작업한 내용 전부 취소

SAVEPOINT

• DML 작업시에 ROLLBACK을 도와주는 표시자

SAVEPOINT 지정 X

-- 1
INSERT INTO hr.test(id) VALUES(100);

-- 2
UPDATE hr.test
SET id = 300
WHERE id = 3

SELECT * FROM hr.test;

-- 3
DELETE FROM hr.test WHERE id = 2;

SELECT * FROM hr.test;

ROLLBACK; -- 1, 2, 3 전부 취소

SELECT * FROM hr.test

SAVEPOINT 지정

-- 1
INSERT INTO hr.test(id) VALUES(100);
SAVEPOINT A;

-- 2
UPDATE hr.test
SET id = 300
WHERE id = 3;
SAVEPOINT B;

SELECT * FROM hr.test;

-- 3
DELETE FROM hr.test WHERE id = 2;

SELECT * FROM hr.test;

ROLLBACK TO B; -- B 이후로만 취소, 1, 2 문장은 트랜잭션 진행 중

SELECT * FROM hr.test

COMMIT; -- 1, 2 작업 내용 저장

문제

문제 환경 세팅

CREATE TABLE hr.oltp_emp
AS
SELECT employee_id, last_name, salary, department_id
FROM hr.employees;

desc hr.oltp_emp

-- 기존 테이블에 컬럼 추가
ALTER TABLE hr.oltp_emp ADD flag char(1);

SELECT * FROM hr.oltp_emp;

CREATE TABLE hr.dw_emp
AS
SELECT employee_id, last_name, salary, department_id
FROM hr.employees
WHERE department_id = 20;

SELECT * FROM hr.dw_emp;

SELECT * FROM hr.oltp_emp WHERE employee_id IN (201, 202);

UPDATE hr.oltp_emp
SET flag = 'd'
WHERE employee_id = 202;

UPDATE hr.oltp_emp
SET salary = 20000
WHERE employee_id = 201;

COMMIT;

문제 47

-- [문제47] oltp_emp에 있는 사원들 중에 dw_emp에 존재하는 사원 정보를 출력해주세요.
SELECT *
FROM hr.oltp_emp o
WHERE exists (SELECT 'x'
                FROM hr.dw_emp
                WHERE employee_id = o.employee_id);

문제 48

-- [문제48] dw_emp에 있는 사원들 중에 oltp_emp에 존재하는 사원들은 oltp_emp에 있는 급여를 기준으로 10% 인상해주세요. 테스트한 후 rollback 수행하세요.
UPDATE hr.dw_emp d
SET salary = (SELECT salary * 1.1
                FROM hr.oltp_emp
                WHERE employee_id = d.employee_id);

SELECT d.employee_id dw_id, d.salary dw_sal, o.employee_id oltp_id, o.salary oltp_sal
FROM hr.dw_emp d, hr.oltp_emp o
WHERE d.employee_id = o.employee_id;

문제 49

-- [문제49] dw_emp에 있는 사원들 중에 oltp_emp에 존재하는 사원들중에 flag값이 'd'인 사원에 대해서 삭제해 주세요.테스트한 후 rollback 수행하세요.
DELETE FROM hr.dw_emp d
WHERE exists (SELECT 'x'
                FROM hr.oltp_emp
                WHERE employee_id = d.employee_id
                AND flag = 'd');

SELECT d.employee_id dw_id, o.employee_id oltp_id, o.flag
FROM hr.dw_emp d, hr.oltp_emp o
WHERE d.employee_id = o.employee_id;                

문제 50

-- [문제50] oltp_emp테이블에 있는 데이터 중에 dw_emp테이블에 없는 데이터들을 dw_emp테이블에 입력해주세요. 테스트한 후 rollback 수행하세요.
INSERT INTO hr.dw_emp(employee_id, last_name, salary, department_id)
SELECT employee_id, last_name, salary, department_id 
FROM hr.oltp_emp o
WHERE not exists (SELECT 'x'
                    FROM hr.dw_emp
                    WHERE employee_id = o.employee_id); -- 105행 삽입

SELECT *
FROM hr.dw_emp d
WHERE not exists (SELECT 'x' FROM hr.oltp_emp); 
-- 조건에 해당하는 데이터가 없으므로 oltp_emp 데이터가 모두 dw_emp에 존재