9. [Oracle] 데이터를 조작해보자.

-DML(데이터 조작어)

• DML 문은 다음과 같은 경우에 실행합니다.

– 테이블에 새 행 추가

– 테이블의 기존 행 수정

– 테이블에서 기존 행 제거

• 트랜잭션 은 논리적 작업 단위를 형성하는 DML 문의 모음으로 구성됩니다.

 

DML(데이터 조작어)은 SQL의 핵심 부분입니다. 데이터베이스에서 데이터를 추가, 갱신 또는 삭제하려는 경우 DML 문을 실행하십시오. 논리적 작업 단위를 형성하는 DML 문의 모음을 트랜잭션 이라고 합니다.

 

-INSERT 문 구문

• INSERT 문을 사용하여 테이블에 새 행을 추가합니다.

 

-새 행 삽입

• 각 열에 대한 값을 포함하는 새 행을 삽입합니다 .

• 테이블에 있는 열의 기본 순서로 값을 나열합니다.

• 선택적으로 INSERT 절에 열을 나열합니다.

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

 

INSERT INTO departments(department_id, department_name, manager_id, location_id)

VALUES (70, 'Public Relations', 100, 1700);

 

각 열에 대한 값을 포함하는 새 행을 삽입할 수 있기 때문에 INSERT 절에서 열 리스트가 필요하지 않습니다. 그러나 열 리스트를 사용하지 않을 경우 테이블에 있는 열의 기본 순서에 따라 값이 나열되어야 하고 각 열에 대해 값이 제공되어야 합니다.

 

명확성을 위해 INSERT 절에 열 리스트를 사용하십시오. 문자와 날짜 값은 작은 따옴표로 묶습니다. 그러나 숫자 값은 작은 따옴표로 묶지 않는 것이 좋습니다.

 

-null 값을 가진 행 삽입

 

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

INSERT INTO departments (department_id, department_name)

VALUES (30, 'Purchasing');

 

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

INSERT INTO departments

VALUES (100, 'Finance', NULL, NULL);

 

대상 열에서 null 값을 사용할 수 있는지 확인하려면 DESCRIBE 명령을 사용하여 Null 상태를 확인해야 합니다.

 

- 특수 값 삽입

SYSDATE 함수는 현재 날짜와 시간을 기록합니다.

 

INSERT INTO employees (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.124.4567', SYSDATE, 'AC_ACCOUNT', 6900, NULL, 205, 110);

 

예제는 EMPLOYEES 테이블에 사원 Popp의 정보를 기록합니다. 이 예제는 HIRE_DATE 열에 현재 날짜와 시간을 제공합니다. 이 예제에서는 데이터베이스 서버의 현재 날짜와 시간을 반환하는 SYSDATE 함수를 사용합니다. CURRENT_DATE 함수를 사용하여 해당 세션 시간대의 현재 날짜를 구할 수도 있습니다. 또한 테이블에 행을 삽입할 때 USER 함수를 사용할 수 있습니다. USER 함수는 현재 username을 기록합니다.

 

-특정 날짜 및 시간 값 삽입

INSERT INTO employees

VALUES (114, 'Den', 'Raphealy', 'DRAPHEAL', '515.127.4561', TO_DATE('FEB 3, 1999', 'MON DD, YYYY'), 'SA_REP', 11000, 0.2, 100, 60);

 

일반적으로 DD-MON-RR 형식을 사용하여 날짜 값을 삽입합니다. RR 형식을 사용하는 경우 시스템은 정확한 세기를 자동으로 제공합니다. 날짜 값을 DD-MON-YYYY 형식으로 제공할 수도 있습니다. 이 형식은 정확한 세기를 지정하는 내부 RR 형식 논리에 의존하지 않으며 세기를 명확히 지정하므로 이 형식을 사용하는 것이 좋습니다.기본 형식이 아닌 다른 형식으로 날짜를 입력해야 하는 경우(예를 들어, 다른 세기나 특정 시간) TO_DATE 함수를 사용해야 합니다.

 

예제는 EMPLOYEES 테이블에 Raphealy 사원의 정보를 기록합니다. 이 예제에서는 HIRE_DATE 열을 February 3, 1999로 설정합니다.

 

-다른 테이블에서 행 복사

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

INSERT INTO sales_reps(id, name, salary, commission_pct)

SELECT employee_id, last_name, salary, commission_pct

FROM employees

WHERE job_id LIKE '%REP%';

 

INSERT 문을 사용하여 기존 테이블에서 파생된 값으로 테이블에 행을 추가할 수 있습니다. 예제에서 INSERT INTO 문이 작동하도록 하려면 먼저 CREATE TABLE 문을 사용하여 sales_reps 테이블을 만들어 두어야 합니다. CREATE TABLE에 대해서는 "DDL 문을 사용하여 테이블 생성 및 관리" 단원에서 설명합니다. VALUES 절 자리에 subquery를 사용합니다.

 

-UPDATE 문 구문

• UPDATE 문을 사용하여 테이블의 기존 값을 수정합니다.

UPDATE table

SET column = value [, column = value, ...]

[WHERE condition];

• 필요한 경우 한 번에 두 개 이상의 행을 갱신합니다.

 

table 테이블의 이름입니다.

column 테이블에 채울 열의 이름입니다.

value 열의 해당 값 또는 subquery입니다.

condition 갱신할 행을 식별하며 열 이름, 표현식, 상수, subquery 및 비교 연산자로 구성됩니다.

 

-테이블의 행 갱신

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

UPDATE employees

SET department_id = 50 WHERE employee_id = 113;

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

UPDATE copy_emp SET department_id = 110;

• 열 값을NULL로 갱신하려면 SET column_name= NULL을 지정합니다.

 

WHERE 절이 지정된 경우 UPDATE 문은 특정 행의 값을 수정합니다. 예제는 사원 113(Popp)을 부서50으로 이동하는 것을 보여줍니다.

 

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

 

사원 113의 직무와 급여를 사원205와 일치하도록 갱신합니다.

UPDATE employees

SET job_id = (SELECT job_id FROM employees WHERE employee_id = 205), salary = (SELECT salary FROM employees WHERE employee_id = 205)

WHERE employee_id = 113;

 

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

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

UPDATE copy_emp

SET department_id = (SELECT department_id FROM employees WHERE employee_id = 100)

WHERE job_id = (SELECT job_id FROM employees WHERE employee_id = 200);

 

UPDATE 문에서 subquery를 사용하여 테이블의 값을 갱신할 수 있습니다. 예제는 EMPLOYEES 테이블의 값을 기반으로 COPY_EMP 테이블을 갱신합니다. 이 예제는 사원 200의 직무 ID를 가진 모든 사원의 부서 번호를 사원 100의 현재 부서 번호로 변경합니다.

 

-DELETE 문

 

DELETE 문을 사용하여 테이블에서 기존 행을 제거할 수 있습니다.

DELETE [FROM] table

[WHERE condition];

 

table - 테이블의 이름입니다.

condition - 삭제할 행을 식별하며 열 이름, 표현식, 상수, subquery 및 비교 연산자로 구성됩니다.

 

-테이블에서 행 삭제

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

DELETE FROM departments

WHERE department_name = 'Finance';

 

• WHERE 절을 생략하면 테이블의 모든 행이 삭제됩니다.

DELETE FROM copy_emp;

 

DELETE 문에 WHERE 절을 지정하여 특정 행을 삭제할 수 있습니다. 첫번째 예제는 DEPARTMENTS 테이블에서 Accounting 부서를 삭제합니다. SELECT 문을 사용하여 삭제된 행을 표시하는 방식으로 삭제 작업을 확인할 수 있습니다.

 

그러나 WHERE 절을 생략하면 테이블의 모든 행이 삭제됩니다. 두번째 예제는 WHERE 절이 지정되지 않았기 때문에 COPY_EMP 테이블에서 모든 행을 삭제합니다.

 

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

DELETE 문에서 subquery를 사용하여 다른 테이블의 값을 기반으로 테이블에서 행을 제거합니다.

DELETE FROM employees

WHERE department_id = (SELECT department_id FROM departments WHERE department_name LIKE '%Public%');

 

subquery를 사용하여 다른 테이블의 값을 기반으로 테이블에서 행을 삭제할 수 있습니다. 예제는 부서 이름에 문자열 Public이 포함된 부서에서 근무하는 모든 사원을 삭제합니다.

subquery는 문자열 Public을 포함한 부서 이름을 기반으로 DEPARTMENTS 테이블을 검색하여 부서 번호를 찾습니다. 그러면 subquery가 main query에 부서 번호를 전달하고 main query는 이 부서 번호를 기반으로 EMPLOYEES 테이블에서 데이터 행을 삭제합니다.

 

-TRUNCATE 문

• 테이블은 빈 상태로, 테이블 구조는 그대로 남겨둔 채 테이블에서 모든 행을 제거합니다.

• DML 문이 아니라 DDL(데이터 정의어) 문이므로 쉽게 언두할 수 없습니다.

 

TRUNCATE TABLE table_name;

 

테이블을 비우는 좀더 효율적인 방법은 TRUNCATE 문을 사용하는 것입니다. TRUNCATE 문을 사용하면 테이블이나 클러스터에서 모든 행을 신속하게 제거할 수 있습니다. TRUNCATE 문으로 행을 제거하는 방법이 DELETE 문으로 행을 제거하는 방법보다 빠른 이유는 다음과 같습니다.

 

• TRUNCATE 문은 DDL(데이터 정의어) 문이며 롤백 정보를 생성하지 않습니다. 롤백 정보는 이 단원 뒷부분에서 다 룹니다.

• 테이블 자르기는 테이블의 삭제 트리거를 유발하지 않습니다.

 

테이블이 참조 무결성 제약 조건의 상위 요소인 경우 해당 테이블을 truncate할 수 없습니다. TRUNCATE 문을 실행하기 전에 제약 조건을 비활성화해야 합니다. 제약 조건 비활성화에 대해서는 "DDL 문을 사용하여 테이블 생성 및 관리" 단원에서 설명합니다.

 

-데이터베이스 트랜잭션

 

데이터베이스 트랜잭션은 다음 중 하나로 구성됩니다.

• 데이터를 일관되게 변경하는 여러DML 문

• 하나의 DDL 문

• 하나의 DCL(데이터 제어어) 문

 

Oracle 서버는 트랜잭션에 준하여 데이터 일관성이 유지되도록 합니다. 트랜잭션은 데이터를 변경할 때 많은 유연성과 제어 기능을 제공하며 유저 프로세스 failure 또는 시스템 failure 시 데이터 일관성을 보장합니다.

트랜잭션은 데이터를 일관되게 변경하는 여러 DML 문으로 구성됩니다. 예를 들어, 두 계좌 간에 자금을 이체할 때 한 계좌의 차변과 다른 계좌의 대변에서 변경되는 금액이 동일해야 합니다. 이 두 작업은 동시에 성공하거나 실패해야 하며 차변 없이 대변만 커밋될 수는 없습니다.

 

트랜잭션 유형

유형	설명
DML(데이터 조작어)	Oracle 서버가 단일 엔티티나 논리적 작업 단위로 취급하는 임의 개수의 DML 문으로 구성됩니다.
DDL(데이터 정의어)	하나의 DDL 문으로만 구성됩니다.
DCL(데이터 제어어)	하나의 DCL 문으로만 구성됩니다.

 

-데이터베이스 트랜잭션: 시작과 종료

 

• 첫번째 DML SQL 문이 실행될 때 시작됩니다.

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

– COMMIT 또는 ROLLBACK 문 실행

– DDL 또는 DCL 문 실행 (자동 커밋)

– 유저가 SQL Developer 또는 SQL*Plus를 종료

– 시스템 중단

 

한 트랙잭션이 끝나면 다음 실행 가능한 SQL 문이 다음 트랜잭션을 자동으로 시작합니다. DDL 문 또는DCL 문은 자동으로 커밋되기 때문에 트랜잭션을 암시적으로 종료합니다.

 

-COMMIT 및 ROLLBACK 문의 이점

COMMIT 및 ROLLBACK 문을 사용할 경우 다음과 같은 이점이 있습니다.

• 데이터 일관성 보장

• 변경 사항을 영구 적용하기 전에 데이터 변경 사항 검토

• 논리적으로 관련된 작업 그룹화

 

-명시적 트랜잭션 제어문

COMMIT, SAVEPOINT 및 ROLLBACK 문을 사용하여 트랜잭션 논리를 제어할 수 있습니다.

 

명령문	설명
COMMIT	COMMIT은 보류 중인 모든 데이터 변경 사항을 영구적으로 적용하여 현재 트랜잭션을 종료합니다.
SAVEPOINT name	SAVEPOINT name은 현재 트랜잭션 내에 저장점을 표시합니다.
ROLLBACK	ROLLBACK은 보류 중인 모든 데이터 변경 사항을 폐기하여 현재 트랜잭션을 종료합니다.
ROLLBACK TO SAVEPOINT name	ROLLBACK TO SAVEPOINT는 현재 트랜잭션을 지정된 저장점으로 롤백합니다. 이에 따라 롤백 중인 저장점 이후에 생성된 변경 사항 및/또는 저장점은 폐기됩니다. TO SAVEPOINT 절을 생략하면 ROLLBACK 문은 전체 트랜잭션을 롤백합니다. 저장점은 논리적이기 때문에 생성한 저장점을 리스트로 표시할 수 없습니다.

 

-변경 사항을 표시자로 롤백

• SAVEPOINT 문을 사용하여 현재 트랜잭션에서 표시자를 생성합니다.

• ROLLBACK TO SAVEPOINT 문을 사용하여 해당 표시자로 롤백합니다.

 

UPDATE... SAVEPOINT update_done;

INSERT... ROLLBACK TO update_done;

 

트랜잭션을 작은 섹션으로 나누는 SAVEPOINT 문을 사용하여 현재 트랜잭션에서 표시자를 생성할 수 있습니다. 그런 다음 ROLLBACK TO SAVEPOINT 문을 사용하여 해당 표시자에 보류 중인 변경 사항을 폐기할 수 있습니다. 이전의 저장점과 동일한 이름으로 두번째 저장점을 만들면 이전의 저장점이 삭제됩니다.

 

-암시적 트랜잭션 처리

• 자동 커밋은 다음 상황에서 발생합니다 .

– DDL 문이 실행되는 경우

– DCL 문이 실행되는 경우

– COMMIT 또는 ROLLBACK 문을 명시적으로 실행하지 않은 채 SQL Developer 또는 SQL*Plus가 정상적으로 종료된 경우

• SQL Developer 또는 SQL*Plus가 비정상적으로 종료되거나 시스템 failure가 발생된 경우 자동 롤백이 발생합니다.

 

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

• 이전의 데이터 상태를 복구할 수 있습니다.

• 현재 유저는 SELECT 문을 사용하여 DML 작업의 결과를 확인할 수 있습니다.

• 다른 유저는 현재 유저가 실행한 DML 문의 결과를 볼수 없습니다 .

• 영향을 받는 행이 잠기므로 다른 유저가 영향을 받는 행의 데이터를 변경할 수 없습니다.

 

-COMMIT 후의 데이터 상태

• 데이터 변경 사항이 데이터베이스에 저장됩니다.

• 이전의 데이터 상태를 겹쳐씁니다.

• 모든 유저가 결과를 확인할 수 있습니다.

• 영향을 받는 행의 Lock이 해제되어 이러한 행을 다른 유저가 조작할 수 있습니다.

• 모든 저장점이 지워집니다.

 

-데이터 커밋

 

• 데이터를 변경합니다.

DELETE FROM employees

WHERE employee_id = 99999;

 

INSERT INTO departments

VALUES (290, 'Corporate Tax', NULL, 1700);

 

• 변경 사항을 커밋합니다.

COMMIT;

 

예제에서는 EMPLOYEES 테이블에서 행이 삭제되고 DEPARTMENTS 테이블에 새 행이 삽입됩니다. 이러한 변경 사항은COMMIT 문을 실행하여 저장됩니다.

 

-ROLLBACK 후의 데이터 상태

 

ROLLBACK 문을 사용하여 보류 중인 모든 변경 사항을 폐기합니다.

• 데이터 변경 사항이 실행 취소됩니다.

• 이전의 데이터 상태가 복원됩니다.

• 영향 받는 행의 잠금이 해제됩니다.

 

DELETE FROM copy_emp;

ROLLBACK ;

 

-읽기 일관성

• 읽기 일관성은 데이터에 대한 일관성 있는 뷰를 보장합니다.

• 한 유저가 변경한 사항이 다른 유저가 변경한 사항과 충돌하지 않습니다.

• 읽기 일관성은 동일한 데이터에 대해 다음 사항을 보장합니다.

– 읽는 사람은 쓰는 사람의 작업이 완료되기를 기다릴 필요가 없습니다.

– 쓰는 사람은 읽는 사람의 작업이 완료되기를 기다릴 필요가 없습니다.

– 쓰는 사람은 다른 쓰는 사람의 작업이 완료되기를 기다려야 합니다.

 

-SELECT 문의 FOR UPDATE 절

• EMPLOYEES 테이블에서 job_id가 SA_REP인 행을 잠급니다.

SELECT employee_id, salary, commission_pct, job_id

FROM employees

WHERE job_id = 'SA_REP'

FOR UPDATE

ORDER BY employee_id;

 

• ROLLBACK 또는 COMMIT를 실행하는 경우에만Lock이 해제됩니다.

• 다른 유저가 잠근 행을SELECT 문에서 잠그려고 하면 데이터베이스는 해당 행을 사용할 수 있을 때까지 기다린 다음 SELECT 문의 결과를 반환합니다.