9. SQL의 활용 (3)

1. 트랜잭션의 개념과 특징 

(1) 트랜잭션의 개념과 특징

• 트랜잭션(Transaction)의 개념 
  • 데이터베이스의 무결성을 보장하며 요청된 작업을 완료하기 위하여, 한번에 수행되어야 할 일련의 Read와 Write연산을 정의한 논리적 작업 단위
  • 분할할 수 없는 최소단위, All or Nothing 
• 트랜잭션의 특징
  • 원자성(Atomicity)
    • All or Nothing, 완전히 완료되지 않았을 경우 수행되기 전과 같아야 함
  • 일관성(Consistency)
    • 실행이 성공적으로 완료되면 데이터베이스는 모순 없이 일관성이 보존된 상태
  • 고립성(Isolation)
    • 트랜잭션의 부분적 실행결과를 다른 트랜잭션이 접근할 수 없음 
  • 지속성(Durability)
    • 완료된 트랜잭션의 결과는 영구적으로 데이터베이스에 저장 
• 트랜잭션 상태 다이어그램 구성 

상태	설명
실행(Active)	트랜잭션 실행 시작 혹은 실행 중인 상태
부분 완료(Partially Committed)	트랜잭션의 마지막 명령을 실행한 후의 상태
실패(Failed)	트랜잭션을 더 이상 정상적으로 실행할 수 없음을 발견한 상태
철회(Aborted)	트랜잭션이 실패하여 취소되어 시작 전 상태로 환원된 상태(ROLLBACK) * 참고:: SAVEPOINT: 롤백 기준점을 설정할 수 있음
완료(Committed)	트랜잭션이 성공적으로 완료된 상태 (COMMIT)

2. TCL (Transaction Control Language) 구문과 활용

(1) TCL 구문의 종류와 특징

기능	Oracle	MariaDB
트랜잭션 시작	DML 시 자동으로 시작 또는 SET TRANSACTION	DML 시 자동으로 시작 또는 START TRANSACTION (Autocommit=1 이어도 트랜잭션 유지)
커밋(COMMIT)	명시적 커밋, DDL 시 암묵적 커밋 지원	명시적 커밋, DDL 시 암묵적 커밋 지원
롤백(ROLLBACK)	전체 롤백 또는 SAVEPOINT로 부분 롤백 가능	전체 롤백 또는 SAVEPOINT로 부분 롤백 가능
SAVEPOINT	지원	지원
AUTOCOMMIT	기본적으로 비활성화 SET AUTOCOMMIT ON/OFF (오라클 전용, 도구에서 설정)	기본적으로 활성화  SET autocommit=1/0

3. 저장 프로시저 (Stored Procedure)의 특징과 활용

(1) 저장 프로시저의 개념과 특징

• 저장 프로시저(Stored Procedure)의 개념
  • 저장 프로시저(Stored Procedure) 
    • 데이터베이스에 저장된 SQL 명령어와 로직을 그룹화한 서브루틴, 이름을 이용하여 호출하여 사용
  • 장점
    • 코드 재사용
    • 성능 최적화
    • 보안성 향상
    • 일관성 유지
• 오라클 저장 프로시저의 특징
  • 구조 
    • 선언부(Declarative)
      • 변수와 상수를 선언 
    • 실행부(Executable)
      • SQL 명령어와 로직 실행
    • 예외 처리부(Exception Handling) 
      • 오류 발생 시 처리 로직 포함
  • 특징
    • PL/SQL 기반
    • 입력 매개변수(IN), 출력 매개변수(OUT), 입력/출력 매개변수(IN OUT) 지원
      • 별도의 반환 값 없음
    • 트랜잭션 관리 기능 내장 (COMMIT, ROLLBACK 등)
    • 데이터 사전(Data Dictionary)에 저장
      • 여러 사용자와 애플리케이션에서 재사용 가능
• MariaDB 저장 프로시저의 특징
  • 구조
    • 구분자(DELIMITER) 변경
    • CREATE PROCEDURE를 사용하여 정의
    • BEGIN ~ END 블록 사용
    • 매개변수 유형 : IN(입력), OUT(출력), INOUT(입출력) 지원
  • 특징
    • SQL 표준을 준수하며 간단한 문법으로 작성 가능
    • 트랜잭션 관리 가능
      • START TRANSACTION, COMMIT, ROLLBACK 등
    • 커서(Cursor)를 사용하여 여러 행 데이터를 처리할 수 있음