Oracle - SQL (DML)

DML (Data Manipualte Language)

DML문을 사용한 테이블 관리 

 

INERT 구문 

INSERT INTO table (column, ...) VALUES (value, ...)

 

새 행 삽입 

문자와 날짜 값은 작은따옴표로 묶습니다.   

INSERT INTO departments_new(department_id, department_name, manager_id, location_id)
VALUES(70, 'Public Relations', 100, 1700)

 

데이터 타입을 확인하는 방법

DESCRIBE departments_new;

SELECT * FROM USER_TAB_COLUMNS
WHERE TABLE_NAME = 'DEPARTMENTS_NEW';

 

NULL 값을 가진 행 삽입

암시적 방법 : 열 리스트에서 열을 생략합니다.

INSERT INTO departments(department_id, department_name)
VALUES(30, 'Purchasing');

명시적 방법 : VALUES 절에서 NULL 키워드를 지정합니다. 

INSERT INTO departments
VALUES (100, 'Finance', NULL, NULL)

 

특수 값 삽입 

CURRENT_DATE 함수는 세션 시간대의현재 날짜를 반환합니다.

INSERT INTO employees_new(employee_id, first_name, last_name, email, phone_number, hire_date, job_id
, salary, commission_pct, manager_id, department_id)
VALUES (113, 'Louis', 'Popp', 'LPOPP', '515.153.4567', CURRENT_DATE, 'AC_ACCOUNT',6900,NULL, 205,110);

 

특정 날짜 및 시간 값 삽입 

INSERT INTO employees_new
VALUES(114, 'DEN', 'Raphealy', 'DRAPHEAL', '515.127.4561',
TO_DATE('2016-07-13','YYYY-MM-DD'), 'SA_REP', 11000, 0.2, 100, 60);

TO_DATE 함수는 Oracle 데이터베이스에서 사용되는 날짜 문자열을 실제 날짜 데이터로 변환하는 함수입니다. 주로 문자열 형식의 날짜 데이터를 Oracle 날짜 데이터 타입인 **DATE**로 변환할 때 사용됩니다.

• date_string: 날짜 정보를 포함하는 문자열입니다. 이 문자열은 특정 형식에 따라야 합니다.
• format: **date_string**의 형식을 지정하는 문자열입니다. 형식 문자열은 날짜 및 시간 요소의 구분, 형식 및 순서를 정의합니다.

 

다른 테이블에서 행 복사

- INSERT 문을 subquery로 작성합니다.

## SALES_REPS 테이블 생성 
CREATE TABLE sales_reps 
AS
SELECT * FROM employees
WHERE 1 = 0 ;

# employees 테이블에서 행 복사
INSERT INTO sales_reps(id, name, salary, commission_pct)
SELECT id, name, salary, commission_pct 
FROM employees
WHERE job_id LIKE '%REP%'

- VALUES 절을 사용하지 않습니다.

- INSERT 절의 열 개수를 subquery의 열 개수와 일치시킵니다.

- subquery에서 반환되는 모든 행을 SALES_REPS 테이블에 삽입합니다.

 

테이블의 데이터 변경

UPDATE 문 구문

UPDATE table
SET comulmn = value
WHERE condition ;

 

테이블의 행 갱신

- WHERE 절을 지정하면 특정 행에서 값이 수정됩니다.

UPDATE employees_new
SET department_id = 50
WHERE emplpoyee_id = 113;

- WHERE 절을 생략하면 테이블의 모든 행에서 값이 수정됩니다.

UPDATE copy_emp
SET department = 110;

- SET column_name = NULL 을 지정하여 열 값을 NULL 로 갱신합니다.

UPDATE employees
SET job_id = 'IT_PROG', commission_pct = NULL
WHERE employee_id = 114;

 

Subquery를 사용하여 두 개의 열 갱신

사원 103의 직무와 급여를 사원 205와 일치하도록 갱신

UPDATE employees_new
SET (job_id, salary) = (SELECT job_id, salary
			FROM employees
                        WHERE employee_id = 205)
WHERE employee_id = 103;

 

다른 테이블을 기반으로 행 갱신 

UPDATE 문에서 Subquery를 사용하여 다른 테입르의 값을 기반으로 테이블의 행 값을 갱신합니다. 

## employees_new 테이블의 department_id column 수정 
UPDATE employees_new
## employees 테이블의 employee_id 값이 100인 행의 department_id 값으로 수정
SET department_id = (SELECT department_id
		    FROM employees
                    WHERE employee_id = 100)
## employee_id가 200 인 행 수정
WHERE job _id = (SELECT job_id 
		FROM employees
                WHERE employee_id = 200);

 

 

DELETE 문

DLEETE FROM table
WHERE condition;

 

테이블에서 행 삭제

- WHERE 절을 지정하면 특정 행이 삭제됩니다.

DELETE FROM departments
WHERE department_name = 'Finance';

- WHERE 절을 생략하면 모든 행이 삭제됩니다.

DELETE FROM copy_emp;

 

다른 테이블을 기반으로 행 삭제

DELETE FROM employees_new
WHERE department_id IN (SELECT department_id
                        FROM departments
                        WHERE department_name LIKE '%Public%'

 

백업 및 복구 작업

• 데이터베이스의 내용을 수정할 때엔 반드시 백업과정을 거쳐야 합니다.

## 백업하기
CREATE TABLE employees_new_2023
AS
SELECT * FROM employees_new
WHERE department_id = 50;

## UPDATE 문으로 수정 후 커밋
UPDATE employees_new
SET department_id = 100
WHERE employee_id = 113;

COMMIT;

## 백업해둔 값으로 복구하기
UPDATE employees_new
SET department_id = (SELECT department_id
                     FROM employees_new
                     WHERE employee_id = 113)
WHERE employee_id = 113;

 

데이터베이스 트랜잭션

트랜잭션 시작과 종료

첫번쨰 DML 문이 실행될 때 시작됩니다.

다음 상황 중 하나가 발생하면 종료됩니다.

•   COMMIT 또는 ROLLBACK 문 실행
•   DDL 또는 DCL 문 실행(auto commit)
•   유저가 SQL Developer 또는 SQL*Plus 종료
•   시스템 중단 

COMMIT 및 ROLLBACK 문의 이점 

• 데이터 일관성 보장
• 변경 사항을 영구 적용하기 전에 데이터 변경 사항 검토
• 논리적으로 관련된 작업 그룹화

COMMIT 또는 ROLLBACK 이전의 데이터 상태

• 이전의 데이터 상태를 복구 할 수 있습니다.
• 현재 세션은 SELECT 문을 사용하여 DML 작업의 결과를 확인할 수 있습니다.
• 다른 세션은 현재 세션이 실행한 DML 문의 결과를 볼 수 없습니다.
• 영향을 받는 행이 잠기므로 다른 세션이 영향을 받는 행의 데이터를 변경할 수 있습니다.