SQL : 7,8,9 계층형 쿼리, 데이터 조작(DML), 트랜잭션

7-08. 계층형 쿼리

수직적 관계를 맺고 있는 행들의 계층형 정보를 조회할 수 있음

웹사이트에서 사용하는 답변형 게시판이나 BOM등이 계층형 정보의 전형적인 예

SELECT			select_list 
FROM					table
WHERE				conditions
START	WITH	top_level_condition --어디서 부터 시작할 것인가
CONNECT	BY	[NOCYCLE]	[PRIOR]	connect_condition
ORDER SIBLINGS BY	order_condition;

nocycle : 관계를 맺고 있는 데이터들의 순환관계가 만들어질 경우 한번만 반복하도록 함

order siblings by : 같은 레벨의 데이터 내에서 분류하고 싶을 때

level : 의사(Pseudo) 열, 계층형 쿼리를 사용할때만 사용가능 (자동으로 분류됨), 일반적인 열처럼 사용가능

prior : 먼저 봐야하는 열 앞에 붙임

• 예시

--이름순으로 정렬
select employee_id,lpad(' ',3*(level-1))|| first_name ||' '||last_name,level
from employees
	start with manager_id is null
	connect by prior employee_id=manager_id
order siblings by first_name;

    -- order by 절에 siblings를 빼면 전체를 이름순서로 정렬함
    select employee_id,
            lpad(' ',3*(level-1))|| first_name ||' '||last_name,
            level
    from employees
    start with manager_id is null
    connect by prior employee_id=manager_id
    order siblings by first_name;

--connect by prior 내 순서를 바꿀경우 관계를 역순으로 추적가능
select employee_id,
            lpad(' ',3*(level-1))|| first_name ||' '||last_name,level
    from employees
    start with employee_id =113
    connect by prior manager_id=employee_id;

8-01. 데이터 조작(DML)

1. CTAS

: NOT NULL을 제외한 다른 제약조건은 복사되지 않음

2. INSERT

multiple insert

all → when then에 맞는 모든걸 실행

first → 조건에 맞는 첫번째것만 실행시킬것인지

conditional →(조건이 있음)행단위로 쪼개서 넣음

unconditional →(조건이 없음)열단위로 쪼개서넣음

3. UPDATE

UPDATE 문장으로 기존의 행을 갱신함.

필요하다면 하나 이상의 행을 갱신할 수 있습니다.

4. DELETE

ELETE 문장을 사용하여 테이블로부터 기존의 행을 제거할 수 있습니다.

참조 무결성 제약조건에 주의해야 합니다. 다른 테이블에서 참조되고 있는 레코드가 존재할 경우를 주의해야 합니다.

delete, truncate, drop 차이점은?
delete : 데이터를 삭제합니다. rollback 명령으로 삭제 취소가 가능합니다.
truncate : 테이블의 구조는 유지하며 데이터만 삭제합니다. rollback 명령으로 데이터
의 삭제를 취소할 수 없습니다.
drop : 테이블을 삭제합니다. 데이터의 구조 또한 사라집니다. rollback 명령으로 테이
블의 삭제를 취소할 수 없습니다.

5. MERGE

데이터베이스에 INSERT 또는 UPDATE 할 때 데이터가 존재하는지를 체크를 하고 존재하면 UPDATE하고, 존재하지 않으면 INSERT를 수행할 수 있게 합니다.

MERGE INTO table [alias] --테이블 이름 선언, 이 테이블에 병합된 데이터가 저장됨
   USING	(target |	view |	subquery)	[alias] --병합할 테이블, 뷰 지정
 	ON		(join_condition) --조인조건
 WHEN MATCHED THEN --: ON 절의 조건에 맞는 것이 있으면 update를 수행
 	UPDATE	SET	column1=value1[,	...	]
	WHEN	NOT	MATCHED	THEN --: ON 절의 조건에 맞는 것이 없으면 insert를 수행
	INSERT	(column_lists)	VALUES	(value_lists);

6. Multiple INSERT를 이용한 테이블 분리

MERGE와는 반대로 한개의 테이블을 여러 테이블에 나눠 저장할 수 있음

하나의 INSERT 문에서 여러 개의 테이블에 동시에 하나의 행을 입력할 수 있음.

• UNCONDITIONAL INSERT ALL : 조건과 상관없이 기술된 여러 개의 테이블에 데이터를 입력
  
• CONDITIONAL INSERT ALL : 특정 조건들을 기술하여 그 조건에 맞는 행들을 원하는 테이블에 나누어 삽입
  
• CONDITIONAL INSERT FIRST : 첫 번째 WHEN 절에서 조건을 만족할 경우 다음의 WHEN 절은 수행하지 X
  
• . UNPIVOTING INSERT : 여러 개의 into 절을 사용할 수 있지만, into 절 뒤에 오는 테이블은 모두 같아야 함. 비 관계형 데이터베이스를 관계형 데이터베이스 구조로 만들 때 사용합니다.
  

9-01. 트랜잭션(Transaction)

1. 트랜잭션

트랜잭션은 분리되어서는 안 될 작업의 단위입니다. 트랜잭션의 시작은 실행 가능한 첫 번째 SQL 문장이 실행될 때 시작합니다.

트랜잭션은 COMMIT 또는 ROLLBACK 문에 의해 명시적으로 종료하거나, DDL이나 DCL 문장 실행으로 자동 커밋되어 종료될 수 있습니다.

사용자의 데이터베이스 종료 또는 시스템 충돌(Crash)에 의한 데이터베이스 비정상적 종료로 트랜잭션이 종료되어 변경사항이 취소될 수 있습니다.

문장	설명
COMMIT	데이터를 영구적으로 저장하고 현재 트랜잭션을 종료함
SAVE savepoint_name	현재 트랜잭션에 savepoint를 지정합니다. savepoint 이름은 식별자 규칙을 따라야 하며 처음 32자만 사용됩니다.
ROLLBACK [[TO [SAVEPOINT]] savepoint_name]	rollback은 모든 미결정 데이터 변경을 버림으로써 현재의 트랜잭션을 종료합니다. rollback to savepoint_name은 savepoint와 모든 연이은 변경사항을 취소합니다.

1.1. 암시적 트랜잭션 종료

자동적인 커밋은 다음의 환경에서 발생합니다.

• DDL 문장이 완료 시
• DCL 문장이 완료 시
• 명시적인 COMMIT이나 ROLLBACK 없이 SQL Plus를 정상적으로 종료할 때(exit)

SET	AUTOCOMMIT	ON
SHOW	AUTOCOMMIT
--autocommit	IMMEDIATE

SET	AUTOCOMMIT	OFF
SHOW	AUTOCOMMIT
--autocommit	OFF

ON이면 각각의 개별적인 DML 문장이 실행되자마자 커밋됩니다. 그러므로 변경을 롤백할
수 없습니다.

1.2. 트랜잭션과 데이터 상태

1) commit 또는 rollback 이전의 데이터 상태

데이터의 이전상태 복구 가능

현재 사용자는 테이블을 질의하여 데이터 조작 작업의 결과를 검사할 수 x, 다른 사용자는 현재 사용자에 의해 만들어진 데이터 조작 작업의 결과를 볼 수 없다.

변경된 행은 잠금상태(다른 사용자들이 변경된 행 내 데이터 변경 불가능)

2) commit 이후의 데이터 상태

변경 데이터가 DB에 영구 반영됨

데이터의 이전 상태가 완전히 상실됨, 모든 사용자가 트랜잭션의 결과를 볼 수 있다. 변경된 행들이 잠금해제 되고 행들은 이제 새로운 변경을 수행하려는 다른 사용자에 의해 이용가능

모든 SAVEPOINT가 제거됨

delete from emps where department_id=10;

savepoint delete_10;

delete from emps where department_id=20;

savepoint delete_20;

delete from emps where department_id=30;
rollback to savepoint delete_20;
rollback to savepoint delete_10;

2. LOCK

2.1. 읽기일관성(ReadCosistence)

읽기 일관성은 항상 데이터의 검색이 일관되게 보증합니다.

읽기 일관성의 목적은 각각의 사용자가 DML 작업을 시작하기 전에, 마지막 커밋 시에 저장된 데이터를 알 수 있도록 보증하는 것

변경 중인 사용자에 의해 COMMIT 또는 ROLLBACK 문이 실행된 후 변경할 수 있습니다.

데이터베이스 사용자가 데이터베이스에 접근하는 두 가지 유형이 있습니다.

• 읽기(reader) : SELECT 문장
• 쓰기(writer) : INSERT, UPDATE, DELETE 문장

2.2. Lock

DML문을 실행하면 해당 트랜잭션에 의해 발생한 데이터가 다른 사용자에 의해 변경이 발생하지 않도록 Lock(잠금 현상)을 발생시킵니다.

이러한 현상은 COMMIT, ROLLBACK문이 실행되면 해제가 됩니다. Lock은 사용자 객체(테이블이나 행)나 사용자에게 보이지 않는 시스템 객체(공유 데이터 구조 및 데이터 사전 행) 등의 똑같은 자원을 액세스하는 트랜잭션들 사이의 해로운 상호작용을 막아줌.