정보처리기사 실기 07단원 - SQL 응용

목차

01. 데이터베이스 기본

02. 응용 SQL 작성하기

03. 절차형 SQL 활용하기

04. 데이터 조작 프로시저 최적화

 

01. 데이터베이스 기본

[1] 트랜잭션

(1) 트랜잭션의 개념

    인가받지 않은 사용자로 부터 데이터를 보장하기 위해 DBMS가 가져야하는 특성이자, 데이터베이스 시스템에서 하나의 논리적 기능을 정상적으로 수행하기 위한 작업의 기본 단위

 

(2) 트랜잭션의 특성

특성	설명	주요기법
Atomicity (원자성)	- 분해가 불가능한 작업의 최소단위 - 연산 전체가 성공 또는 실패 - 하나라도 실패할 경우 전체가 취소되어야하는특성	- Commit / Rollback - 회복성 보장
Consistency (일관성)	트랜잭션이 실행 성공 후 항상 일관된 데이터베이스 상태를 보존해야하는 특성	- 무결성 제약 조건 - 동시성 제어
Isolation (격리성)	트랜잭션 실행 중 생성하는 연산의 중간 결과를 다른 트랜잭션이 접근 불가한 특성	- Read Uncomited - Read Commited - Repeatable Read - Serializable
Durability (영속성)	성공이 완료된 트랜잭션의 결과는 영속적으로 데이터 베이스에 저장하는 특성	회복기법

 

(3) 트랜잭션의 상태 변화

상태	설명
Active (활동 상태)	초기 상태, 트랜잭션이 실행 중일 때 가지는 상태
Partially Committed (부분 완료 상태)	마지막 명령문이 실행된 후에 가지는 상태
Committed (완료 상태)	트랜잭션이 성공적으로 완료된 후 가지는 상태
Failed (실패 상태)	정상적인 실행이 더 이상 진행될 수 없을 때 가지는 상태
Aborted (철회 상태)	트랜잭션이 취소되고 데이터베이스가 트랜잭션 시작 전 상태로 환원된 상태

 

(4) 트랜잭션 제어

- TCL(Transaction Control Language) 라고도 하며 트랜잭션 결과를 허용하거나 취소하는 목적으로 사용되는 언어를 지칭

 

TCL 명령어

명령어	핵심	설명
Commit	트랜잭션 확정	트랜잭션을 메모리에 영구적으로 저장하는 명령어
Rollback	트랜잭션 취소	트랜잭션 내역을 저장 무효화 시키는 명령어
Check Point	저장 시기 설정	Rollback 을 위한 시점을 지정하는 명령어

 

(5) 병행 제어(일관성 주요 기법)

1) Concurrency Control개념

병행제어는 다수 사용자 환경에서 여러 트랜잭션을 수행할 때 , 데이터 베이스 일관성 유지를 위해 상호작용을 제어하는 기법

 

2) 병행 제어의 목적

- 데이터베이스의 공유를 최대화

- 시스템의 활용도를 최대화

- 데이터베이스의 일관성 유지

- 사용자에 대한 응답시간을 최소화

 

3) 병행 제어 미보장 시 문제점

문제점	설명
Lost Update (갱신 손실)	먼저 설행된 트랜잭션의 결과를 나중에 실행된 트랜잭션이 텊어쓸 때 발생하는 오류
Dirty Read (현황 파악 오류)	트랜잭션의 중간 수행 결과를 다른 트랜잭션이 참조하여 발생하는 오류
Inconsistency (모순성)	두 트랜잭션이 동시에 실행되어 데이터베이스의 일관성이 결여되는 오류
Cascading Rollback (연쇄복구)	복수의 트랜잭션이 데이터 공유 시 특정 트랜잭션이 처리 취소할 경우 트랜잭션이 처리한 곳의 부분을 취소하지 못하는 오류

 

4) 병행 제어 기법의 종류

- 병행 제어 기법에는 로킹, 타임 스탬프 순서가 있다. 

 

병행 제어 기법의 종류

기법	설명
Locking	- 같은 자원을 액세스하는 다중 트랜잭션 환경에서 DB의 일관성에 무결성을 유지하기 위해 트랜잭션의 순차적 진행을 보장하는 직렬화 기법
낙관적 검증	- 트랜잭션이 어떠한 검증도 수행하지 않고 일단 트랜잭션을 수행하고, 트랜잭션 종료 시 검증을 수행하여 데이터베이스에 반영하는 기법
타임 스탬프 순서	- 트랜잭션과 트랜잭션이 읽거나 갱신한 데이터에 대해 트랜잭션이 실행을 시작하기 전에 타임 스탬프를부여하여 부여된 시간에 따라 트랜잭션 작업을 수행하는 기법
다중 버전 동시성 제어	- 트랜잭션의 타임스탬프와 접근하려는 테이터의 타임스탬프를 비교하여 직렬가능성이 보장되는 적절한 버전을 선택하여 접근하도록 하는 기법

 

(6) 데이터 베이스 고립화 수준(격리성 주요 기법)

1) lsolation Level 개념

고립화 수준은 다른 트랜잭션이 현재의 데이터에 대한 무결성을 해치치 않기 위해 잠금을 설정하는 정도이다.

 

2) 고립화 수준 종류 

Read Uncommitted, Read Committed, Repeatable Read, Serializable Read가 있다.

수준	설명
Read Uncommitted	한 트랜잭션에서 연산(갱신) 중인 데이터를 다른 트랜잭션이 읽는 것을 허용하는 수준
Read Committed	- 한 트랜잭션에서 연산을 수행할 때 연산이 완료될 때가지 연산 대상 데이터에 대한 읽기를 제한하는 수준 - 연산이 완료되어 커밋된 데이터는 다른 트랜잭션이 읽는 것을 허용
Repeatable Read	- 선행 트랜잭션이 특정 데이터를 읽을 때 트랜잭션 종료 시까지 해당 데이터에 대한 갱신, 삭제를 제한하는 수준
Serializable Read	- 선행 트랜잭션이 특정 데이터 영역을 순차적으로 읽을 때, 해당 데이터 영역 전체에 대한 접근 제한하는 수준

 

(7) 회복 기법(영속성 주요 기법)

1) Recovery 개념

- 회복 기법은 트랜잭션을 수행하는 도중 장애로 인해 손상된 데이터 베이스를 손상되기 이전의 정상적인 상태로 복구시키는 작업이다.

 

2) 회족 기법 종류

- 로그 기반 회복 기법인 지연갱신회복기법, 즉각 갱신회복기법, 체크포인트회복기법, 그림자 페이징 회복기법이 있다. 

 

회복 기법 종류

기법	설명
로그 기반 회복 기법	지연 갱신 회복 기법과 즉각 갱신 회복 기법이 있음 - 지연 갱신 회복 기법(Deferred Update): 트랜잭션이 완료되기 전까지 데이터베이스에 기록하지 않은 기법 - 즉각 갱신 회복 기법(Immediate Update) : 트랜잭션 수행 중 갱신 결과를 바로 DB에 반영하는 기법
체크 포인트 회복 기법	장애 발생 시 검사점 이후에 처리된 트랜잭션에 대해서만 장애 발생 이전의 상태로 복원시키는 회복 기법
그림자 페이징 회복 기법	데이터베이스 트랜잭션 수행 시 복제본을 생성하여 데이터베이스 장애 시 이를 이용해 복구하는 기법

---

(2) DDL

(1) 데이터 정의어 (DDL : Data Definition Language)의 개념

- 데이터 정의어는 데이터의 정의하는 언어로서 '데이터를 담는 그릇을 정의하는 언어'

- 테이블과 같은 데이터 구조를 정의하는데 사용되는 명령어들로 특정 구조를 생성, 변경, 삭제, 이름을 바꾸는 명령어들을 데이터 정의어라고 부른다.

 

(2) DDL의 대상

 

도메인, 스키마, 데이블, 뷰, 인덱스

DDL 대상	설명
Domain	- 하나의 속성이 가질 수 있는 원자값들의 집합 - 속성의 데이터 타입과 크기, 제약조건 등의 정보
Schema	- 데이터베이스의 구조, 제약조건 등의 정보를 담고 있는 기본적인 구조 - 스키마는 외부/개념/내부 3계층으로 구성되어있음 - External Schema (외부 스키마) : 사용자나 개발자의 관점에서 필요로하는 db의 논리적 구조 : 사용자 뷰를 나타냄, 서브 스키마로 불림 - Conceptual Schema (개념 스키마)  : 데이터베이스의 전체적인 논리적 구조 : 전체적인 뷰를 나타냄 : 개체 간의 관계, 재약조건, 접근 권한, 무결성, 보안에 대해 정의  - Internal Scema (내부 스키마) : 물리적 저장 장치의 관점에ㅓㅅ 보는 데이터베이스 구조 : 실제로 데이터베이스에 저장될 레코드의 형식을 정의하고 저장 데이터 항목의 표현 방법, 내부 레코드의 물리적 순서등을 표현
Table	데이터 저장공간
View	하나 이상의 물리 테이블에서 유도되는 가상의 테이블
Index	검색을 빠르게 하기 위한 데이터 구조

 

1) Table 

- 테이블의 개념

테이블은 데이터를 저장하는 항목인 필드들로 구성된 데이터의 집합체

하나의 db 내에 여러 개의 테이블을 구성될 수 잇고, Relation 또는 Entity 라고도 부른다.

 

- 테이블의 용어

용어	설명
Tuple / Row	- 테이블 내의 행을 의미하며 레코드라고도 함 - 튜플은 릴레이션에서 같은 값을 가질 수 없음
Attribute / Column	- 테이블 내의 열을 의미 - 열의 개수를 디그리라고 함
Identifier	- 여러 개의 집합체를 담고 있는 관계형 데이터 베이스에서 각각의 구분할 수 있는 논리적인 개념
Cardinality	- 튜플의 개수
Degree	- Attribute의 개수
Domain	- 하나의 애트리뷰트가 취할 수 잇는 같은 타입의 원자값 들의 집합

 

2) View

- 뷰의 개념

뷰는 논리 테이블, 사용자에게 (사용 관점에서) 테이블과 동일하다

뷰와같은 결과를 만들기 위해 조인 기능을 활용할 수 있으나, 뷰가 만들어져 있다면 사용자는 조인 없이 하나의 테이블을 대상으로 하는 단순한 질의어를 사용할 수 있다.

 

- 뷰의 특징

특징	설명
논리적 데이터 독립성 제공	데이터베이스에 영향을 주지않고 애플리케이션이 원하는 형태로 데이터에 접근 가능
데이터 조작 연산 간소화	애플리케이션이 원하는 형태의 논리적 구조를 형성하여 데이터 조작 연산을 간소화
보안 기능(접근제어)제공	특정 필드만의 선택해 뷰를 생성할 경우 애플리케이션은 선택되지 않은 필드의 조회 및 접근 불가
뷰 변경 불가	뷰 정의는 Alter문을 이용하여 변경할 수 없음

 

- 뷰의 목적

   - 뷰를 사용하는 주된 이유눈 단순 질의어를 사용할 수 있기 때문이다.

   - From 절에 있는 하나의 뷰를 통해 뷰를 구성하는 복수의 테이블을 대체하는 단순성에 그 의의가 있다.

   - 테이블의 중요 데이터 일부만을 제공할 수 잇는 장단점이 있다.

 

- 뷰의 장점과 단점

장점	단점
- 논리적 독립성제공 : 뷰는 논리 테이블 - 사용자 데이터 관리 용이 : 복수 테이블에 존재하는 여러 종류의 데이터에 대해 단순한 질의어 사용이 가능 - 데이터 보안의 용이 : 보안 데이터에 대한 접근 제어 가능	- 뷰 자체 인덱스 불가 : 인덱스는 물리적으로 저장된 데이터를 대상으로 하기에 논리적 구성인 뷰 자체는 인덱스를 가지지 못함  - 뷰 정의 변경 불가 : 뷰의 정의를 변경하려면 뷰를 삭제하고 재생성 - 데이터 변경 제약 존재 : 뷰의 내용에 대한 삽입, 삭제, 변경 제약이 있음

 

3) 인덱스

- 인덱스의 개념

인덱스는 데이터를 빠르게 찾을 수 있는 수단으로서, 테이블에 대한 조회 속도를 높여 주는 자료 구조

인덱스는 테이블의 특정 레코드 위치를 알려주는 용도로 사용한다.

 

- 인덱스의 특징

  - 기본 키 컬럼은 자동으로 인덱스가 새성된다

  - 연월일이나 이름을 기준으로 하는 인덱스는 자동으로 생성되지 않는다.

  - 테이블의 컬럼에 인덱스가 없느 ㄴ경우, 테이블의 전체 내용을 검색한다.

  - 인덱스가 생성되어 있을 때 데이터를 빠르게 찾을 수 있다.

  - 조건절에 "="로 비교되는 컬럼을 대상으로 인덱스를 생성하면 검색 속도를 높일 수 있다.

  

- 인덱스의 종류

유형	설명
Order Index	- 데이터가 정렬된 순서로 생성되는 인덱스 - B -tree 알고리즘 활용(오름차순/내림차순 지정가능)
Hash Index	- 해시 함수에 의해 직접 데이터에 키 값으로 접근하는 인덱스 - 데이터 접근 비용이 균일, 튜플양에 무관
Bitmap Index	- 각 컬럼에 적은 개수 값이 저장된 경우 선택하는 인덱스 - 수정변경이 적을 경우 유용(생년월일, 상품정보 등)
Functional Index	- 수식이나 함수를 적용하여 만든 인덱스
Singled Index	- 하나의 컬럼으로만 구서한 인덱스 - 주 사용 컬럼이 하나일 경우 사용
Concatenated Index	- 두 개 이상의 컬러으로 구성 인덱스  - where 조건으로 사용하는 빈도가 높은 경우 사용
Clutered Index	- 기본 키 기준으로 레코드를 묶어서 저장하는 인덱스 - 저장 데이터의 물리적 순서에 따라 인덱스가 생성 - 특정 범위 검색 시 유리함

 

- 인덱스의 스캔 방식

구분	설명
Index Range Scan	인덱스 루트 블록에서 리프 블록까지 수직적으로 탐색한 후에 리프 블록을 필요한 범위만 스캔하는 방식
Index Full Scan	수직적 탐색 없이 인덱스 리프 블록을 처음부터 끝가지 수평적으로 탐색하는 방식
Index Unique Scan	수직적 탐색만으로 데이터를 찾는 스캔 방식
Index Skip Scan	선두 컬럼이 조건 절에 빠졌어도 인덱스를 활용하는 스캔방식

 

- DDL 명령어 : CREATE, ALTER, DROP, TRUNCATE

구분	DDL 명령어	설명
생성	CREATE	DB 오브젝트 생성하는 명령어
수정	ALTER	DB 오브젝트 변경하는 명령어
삭제	DROP	DB 오브젝트 삭제하는 명령어
삭제	TRUNCATE	DB 오브젝트 내용 삭제하는 명령어

 

- TABLE 관련 DDL

1) CREATE TABLE : 테이블을 생성하는 명령

2) ALTER TABLE : 테이블을 수정하는 명령

3) DROP TABLE : 테이블을 삭제하는 명령

4) TRUNCATE TABLE : 테이블 내의 데이터들을 삭제하는 명령 

 

- VIEW 관련 DDL

1) CREATE VIEW : 뷰를 생성하는 명령이다.

2) CREATE OR REPLACE VIEW

3) DROP VIEW

 

- INDEX 관련 DDL

1) CREATE INDEX

- CREATE INDEX 는 인덱스를 생성하는 명령이다.

- UNIQUE는 생략 가능하고 인덱스 걸린 컬럼에 중복 값을 허용하지 않는다.

- 복수 컬럼을 인덱스로 걸 수 있다.

create [unique] index 인덱스명 on 테이블명(컬럼명1, 컬럼명2, ..);

 

2) ALTER INDEX

- ALTER INDEX는 인덱스를 수정하는 명령어

- 일부 DBMS는 ALTER INDEX를 제공하지 않는다.

- 기존 인덱스를 삭제하고 신규 인덱스를 생성하는 방식으로 사용을 권고 한다.

 

ALTER INDEX 문법 

ALTER [UNIQUE] INDEX 인덱스명 ON 테이블명(컬럼명1, 컬럼명2, ...);

 

3) DROP INDEX : 인덱스를 삭제하는 명령어이다.

DORP INDEX 인덱스명;

 

(3) DML

- 데이터 조작어 : 데이터 조작어는 데이터베이스에 저장된 자료들을 입력, 수정,삭제, 조회하는 언어

- DML 명령어 : 데이터조작어의 유형에는 SELECT, INSERT, UPDATE, DELETE가 있다.

 

DML의 유형

유형	동작	설명
SELECT	조회	테이블 내 칼럼에 저장된 데이터를 조회
INSERT	삽입	테이블 내칼럼에 데이터를 추가
UPDATE	갱신	테이블 내 칼럼에 저장된 데이터를 수정
DELETE	삭제	테이블 내 칼럼에 저장된 데이터를 삭제

 

- SELECT 명령어

1) SELECT 명령어 개념 : 데이터의 내용을 조회할 때 사용하는 명령어

2) 조인(JOIN) : 두 개 이상의 테이블을 연결하여 데이터를 검색하는 방법

3) 서브쿼리(Sub-Query) : SQL문 안에 포함된 또 다른 SQL문이다.

4) 집합 연산자(Set Operator) ; 

 

(4) INSERT(데이터 삽입) 명령어 : 데이터의 내용을 삽입할 때 사용하는 명령어이다.

(5) UPDATE(데이터 변경) 명령어 : 데이터의 내용을 변경할 때 사용하는 명령어이다.

(6) DELETE(데이터 삭제) 명령어 : 데이터의 내용을 삭제할 때 사용하는 명령어이다. 

 

(4) DCL

데이터 제어어(DCL : Data Control Language)의 개념

- 데이터 제어어는 데이터베이스관리자 데이터 보안,무결성 유지, 병행 제어, 회복을 위해 관리자가 사용하는 제어용 언어

 

- 데이터 제어어의 유형에는 GRANT, REVOKE 가 있다.

1) GRANT(권한 부여) 명령어

2) REVOKE(권한 회수) 명령어

---

02. 응용 SQL 작성하기

[1] 집계성 SQL 작성

(1) 데이터 분석 함수의 개념

- 총합, 평균 드으이 데이터 분석을 위해서는 복수행기준의 데이터를 모아서 처리하느 ㄴ거을 목적으로 하는 다중 행 함수이다.

(2) 데이터 분석 함수의 종류

- SQL 표준에서는 데이터 튜플 간의 상호 연관 및 게산 분석을 위한 세 가지 함수가 있다.

 

데이터 분석 함수 종류

함수	설명
집계 함수	여러 행 또는 테이블 전체 행으로부터 하나의 결괏값을 반환하는 함수
그룹 함수	소그룹 간의 소계 및 중계 등의 중간 합계 분석 데이터를 산출하는 함수
윈도 함수	데이터 베이스를 사용한 온라인 분석 처리 용도로 사용하기 위해서 표준 SQL에 추가된 기능

(3) 집계 함수

(4) 그룹 함수

(5) 윈도 함수

 

---

03 절차형 SQL 활용하기

[1] 절차형 SQL 

(1) 절차형 SQL(Procedural Language) 개념

- 절차형 SQl은 일반적인 개발 언어처럼 SQL언어에서도 절차 지향적인 프로그램이 가능하도록 하는 트랜잭션 언어이다.

 

(2) 절차형 SQL 종류

- 프로시저, 사용자 정의함수, 트리거가 있다.

 

절차형 SQL종류

종류	설명
Procedure (프로시저)	일련의 쿼리들을 마치 하나의 함수처럼 실행하기 위한 쿼리의 집합
User-Defined Function) 사용자 정의함수	일련의 SQl처리를 수행하고 수행결과를 단일 값으로 반환할 수 있는 절차형 SQL
트리거 (Tirgger)	데이터베이스 시스템에서 삽입, 갱신, 삭제 등의 이벤트가 발생할 때마다 관련 작업이 자동으로 수행되는 절차형 SQL

(3) 출력부

1) DBMS_OUTPUT패키지 개념

- DBMS_OUTPUT은 메세지를 버퍼에 저장하고 버퍼로부터 메시지를 읽어오기 위한 인터페이스 패키지이다.

- DBMS_OUTPUT은 절차형 SQL이 정상적으로 구현되었는지 테스트하기 위한 목적으로 많이 사용한다.

 

2) DBMS_OUTPUT 패키지 종류

DBMS_OUTPUT 패키지 종류

명령어	설명
DBMS_OUTPUT.PUT(문자열);	개행 없이 문자열을 출력하는 프로시저
DBMS_OUTPUT.PUT_LINE(문자열);	문자열을 출력 후 개행하는 프로시저

 

(4) 제어부(CONTROL)

1) 조건부

(1) IF문 - 조건이 참인지 것인지에 따라 경로를 선택하는 조건문이다.

IF문

IF 조건 THEN 문장;

ELSIF 조건 THEN 문장;
...
ELSE 문장;

END IF;

(2) 간단한 케이스 문(Simple Case Expression)

- 간단한 케이스 문은 명확한 값을 가지는 조건에 다라 여러 개의 선택 경로 중 하나를 취하고자 할 때

  사용하는 조건문이다.

- 범위 같은 더 복잡한 매칭을 수행하려면, 검색된 CASE문을 사용해야한다.

간단한 케이스 문

CASE 변수
  WHEN 값1 THEN
   SET 명령어;
 
  WHEN 값2 THEN
   SET 명령어;
   
  ...
  
  ELSE
   SET 명령어;
 END CASE;

 

(3) 검색된 케이스 문(Searched Case Expression)

- 검색된 케이스 문은 명확한 값 및 범위를 가지는 조건에 따라 여러 개의 선택 경로 중 하나를 취하고자 할 때 사용하는 조건문이다.

검색된 케이스 문 

CASE 변수
 WHEN 조건1 THEN
  SET 명령어;
 WHEN 조건2 THEN
  SET 명령어;
...
 ELSE
  SET 명령어;
END CASE;

 

2) 반복문

(1) LOOP 문

- LOOP 문을 특정 조건이 만족될 때까지 반복해서 문장을 실행하는 반복문이다

- EXIT WHEN에 반복문 탈출 조건을 작성한다

 

LOOP문

LOOP
 문장;
 EXIT WHEN 탈출조건;
END LOOP;

(2) WHILE 문

- WHLE 문은 시작과 종료 조건을 지정하여 참인 동안에는 해당 문장을 반복해서 실행하는 명령문이다.

- WHILE 문은 조건이 참일 경우 반복하고 조건이 거짓이거나 EXIT 조건이 만족하는 경우 반복문을 빠져나온다.

 

WHILE 문

WHILE 반복조건 LOOP
 문장;
EXIT WHEN 탈출조건;

END LOOP;

 

(3) FOR LOOP문

 - FOR LOOP 문을 시작 값과 끝값을 지정하여 해당 값이 그 구간 내에 있을 때 반복하는 반목문이다.

 

FOR LOOP문

FOR 인덱스 IN 시작값 ... 종료값
LOOP 문장;
END LOOP;

 

(5) 예외부(EXCEPTION)

- 예외부는 실행 중 발생 가능한 예외상황을 수행하는 부분이다.

 

예외부

EXCEPTION
  WHEN 조건 THEN
    SET명령어;

---

[2] 프로시저

(1) Procedure 개념

- 프로시저는 일련의 쿼리들을 마치 하나의 함수처럼 실행하기 위한 쿼리의 집합이다.

 

(2) 프로시저 구성

구성요소	설명
DECLARE (선언부)	- 프로시저의 명칭, 변수와 인수, 그에 대한데이터 타입을 정의하는 부분
BEGIN/END (시작/종료부)	- 프로시저의 시작, 종료를 표현 - 다수 실행을 제어하는 기본적 단위가 되며 논리적 프로세스를 구성
SQL	- DML을 주로 사용 - 자주 사용되지 않지만 DDL중 TRUNCATE 사용
EXCEPTION (예외부)	BEGIN~END 절에서 실행되는 SQL문이 실행될 때 예외 발생 시 예외 처리 방법을 정리하는 처리부
TRANSACTION (실행부)	- 프로시저에서 수행된 DML 수행 내역의 DBMS의 적용 또는 취소 여부를 결정하는 처리부

 

(3) 프로시저 문법

 

프로시저 문법

CREATE [OR REPLACE] 프로시저_명
(파라미터_명 [IN | OUT | INOUT] 데이터_타입, ...)
IS
 변수 선언
BEGIN
 명령어;
[COMMIT | ROLLBACK]

END;

구성	설명
[OR REPLACE]	기존 프로시저 존재 시에 현재 컴파일하는 내용으로 덮어씀
모드 ([IN | OUT | INOUT])	변수의 입출력을 구분하고 IN/OUT/ 3가지로 구성 IN : 운영체제에서 프로시저로 값을 전달하는 모드 OUT : 프로시저에서 처리된 결과를 운영체제로 전달하는 모드 INOUT : IN 과 OUT의 두가지 기능을동시에 수행하는 모드
BEGIN	프로시저의 시작을 알려주는 키워드
COMMIT	하나의 트랜잭션이 성공적으로 끝내고 데이터베이스가 일관성 있는상태에 있을 때  하나의 트랜잭션이 끝났을 때 사용하는 연산
ROLLBACK	하나의 트랜잭션이 비정상적으로 종료되어 트랜잭션 원자성이 깨질 경우 처음부터 다시 시작하거나 부분적으로 연산을 취소하는 연산
END	프로시저의 끝을 알려주는 키워드

 

(4) 프로시저 호출문 작성

- SQL TOOL 을 활용하여 직접 실행시키는 경우에는 EXECUTE 또는 EXEC 명령어를 이용하여 프로시저를 실행한다.

- 프로시저에 입출력 변수가 존재하는 경우 변수를 입력하여 실행해야한다.

- 데이터 유형의 경우 자동 변환이 되어 별도 오류가 발생하지 않은 경우가 많지만 가급적 프로시저에서선언한 데이터 타입과 동일하게 입출력 벼수를넣어서 실행하는 것이 좋다. 

 

프로시저 호출문 작성

SQL > EXECUTE 프로시저_명 (파라미터_1, 파라미터_2, ...);

 

[3] 사용자 정의함수

(1) 사용자 정의 함수(User-Defined Function) 개념

- 사용자 정의함수는 일련의 SQL처리를 수행하고, 수행 결과를 단일 값으로 반환할 수 있는 절차형 SQL이다.

 

(2) 사용자 정의함수 구성

- 기본적인 사항은 프로시저와 동일하고 반환에서의 부분만 프로시저와 다르다.

- 사용자 정의함수의 호출을 통해 실행되며, 바환되는 단일 값을 조회 또는 삽입, 수정 작업에 이용하는 것이 일반적이다.

- 기본적인 개념 및 사용법, 문법 등은 프로시저와 동일하며, 종료 시 단일 값을 반환한다는 것이 프로시저와의 가장 큰 차이점이다. 

 

사용자 정의함수 구성

구성요소	설명
선언부(DECLARE)	- 사용자 정의함수의 명칭, 변수와 인수 그리고 그에 대한 데이타입을 정하는 부분
시작/종료부(BEGIN/END)	- 사용자 정의함수의 시작과 종료를 표현하는데 필수적이며, BEGIN/END가 쌍을 이루어 추가되므로 블록으로 구성 - 다수 실행을 제어하는 기본적 단위가되며논리적 프로세스를 구성
제어부(CONTROL)	- 기본적으로는 순차적으로 처리 - 비교 조건에 따라 블록 또는 문장을 실행 - 조건에 따라 반복 실행
SQL	- 조회 용도로 SELECT 문을 사용 - 데이터를 조작하는 INSERT, DELETE, UPDATE는 사용할 수 없음
예외부(EXCEPTION)	- BEGIN~END 절에서 실행되는 SQL 문이 실행될 때 예외 발생 시 예외 처리하는 방법을정의하는 처리부
반환부(RETURN)	- 호출문에 대한 함숫값을 반환

 

(3) 사용자 정의함수 문법

 

사용자 정의함수 문법

 

[4] 트리거

(1) Trigger 개념

- 트리거는 데이터베이스 시스템에서 삽입, 갱신, 삭제 등의 이벤트가 발생할 때마다 관련 작업이 자동으로 수행되는 절차형SQL이다.

- 이벤트는 전체 트랜잭션 대상과 각행에 의해 발생하는 경우 모두를 포함할 수 있으며 테이블과 뷰, DB 작업을 대상으로 정의할 수 있다.

 

(2) 트리거의 목적

- 특정 테이블에 대한 데이터 변경을 시작점으로 설정하고, 그와 관련된 작업을 자동적으로 수행하기 위해 트리거를 사용한다.

- 일반적으로 이벤트와 관련된 테이블의 데이터 삽입, 추가, 삭제 작업을 DBMS가 자동적으로 실행시키는 데 활용한다.

- 데이터 무결성 유지 및 로그 메시지 출력 등의 별도 처리를 위해 트리거를 사용한다.

 

(3) 트리거의 종류

-트리거의 종류는 행 트리거 , 문장 트리거가 있다

  행 트리거 : 데이터 변화가 생길 때 마다 실행

  문장 트리거 : 트리거에 의해 단 한 번 실행

 

(4) 트리거의 구성

- 프로시저나 사용자 정의함수와 기본적 문법은 같다.

- 반환 값이 없다는 점, DML을 주된 목적으로 한다는 점에서는 프로시저와 유사하다

- EVENT 명령어를 통해 트리거 실행을 위한 이벤트를 인지한다는 점, 외부 변수 IN/OUT이 없다는 점은 프로시저나 사용자 정의함수와 다르다.

 

(5) 트리거 문법

 

트리거 문법

구성	설명
[OR REPLACE]	기존 트리거 존재 시에 현재 컴파일하는 내용으로 덮어씀
순서 ([BEFORE | AFTER])	DML과 트리거가 실행되는 순서의 전후 관계를 BEFORE, AFTER키워드를 이용하여 결정
유형	- DML에 해당되는 INSERT, UPDATE, DELETE 중 트리거를 수행할 명령어 유행을 선택 - INSERT, UPDATE, DELETE는 여러 개 중복으로 선택이 가능
FOR EACH ROW	매번 변경되는 데이터 행의 수만큼 실행을 위한 명령어
BEGIN	트리거의 시작을 알려주는 키워드
END	트리거의 끝을 알려주는 키워드

- 행 트리거 안에서 OLD 및 NEW 수식자 접두어를 붙여 데이터 변경 전후 열의 값을 참조한다.

 

트리거 SQL중 접두어

데이터 작업	OLD	NEW
INSERT	NULL	삽입된 값
UPDATE	갱신 전의 값	갱신 후의 값
DELETE	삭제 전의 값	NULL

 

(6) 트리거 작성 시 주의 사항

주의사항	설명
TCL 사용 불가	트리거 내에는 COMMIT, ROLLBACK 등의 트랜잭션 제어어 사용 시 컴파일 에러 발생
오류에 주의	- 트리거 실행 중 오류가 발생하게 되면 트리거 실행의 원인을 제공한 데이터 작업에도 영향 - 특정 테이블에 데이터를 추가한 후 발생하는 트리거에서 오류가 발생할 경우에는 트리거 이후의 작업이 진행되지 않거나 데이터가 추가되지않음

---

04 데이터 조작 프로시저 최적화

[1] 데이터 조작 프로시저 성능개선

(1) 쿼리 성능 개선(튜닝)의 개념

- 쿼리 성능 개선은 데이터베이스에서 프로시저에 있는 SQL 시랳ㅇ 계획을 분석, 수정을 통해 최소의 시간으로 원한느 결과를 얻도록 프로시저를 수정하는 작업이다.

 

- SQL 성능 개선을 통해 데이터 조작 프로시저의 성능 개선이 가능하다

 

(2) 쿼리 성능 개선 절차

 

SQL 성능 개선 절차

순서	절차	설명
1	문제있는 SQL 식별	문제 있는 SQL을 식별하기 위해 애플리케이션의 성능을 관리 및 모니터링 도구인 APM등을 활용
2	옵티마이저 통계 확인	옵티마이저는 개발자가 작성한SQL을 가장  빠르고 효율적으로 수행할 최적의 처리 경로를 생성해주는 데이터베이스 핵심모듈
3	SQL 문 재구성	- 범위가 아닌 특정 값 지정으로 범위를 줄여 처리 속도를 빠르게 함 - 옵티마이저가 비정상적인 실행계획을 수립할 경우, 힌트로서 옵티마이저의 접근 경로 및 조인 순서를 제어
4	인덱스 재구성	- 성능에 중요한 액세스 경로를 고려하여 인덱스 생성 - 실행계획을 검토하여 기존 인덱스의 열 순서를 변경/추가
5	실행계획 유지관리	- 데이터베이스 버전 업그레이드, 데이터 전환 등 시스템 환경의 변경 사항 발생 시에도 실행 계획이 유지되고 있는 지 관리

 

(3) 옵티마이저 통계 확인

1) 옵티마이저(Optimizer)의 개념

- SQL을 가장빠르고 효율적으로 수행할 최적의 처리경로를 생성해주는 DBMS 내부의 핵심엔진이다.

- 옵티마이저가 생성한 SQL 처리경로를 실행계획이라고 부른다.

 

2) 옵티마이저의 유형

- 옵티마이저의 유형으로는 RBO, CBO가 있다.

 

옵티마이저 유형별 비교

비교	규칙기반 옵티마이저(RBO)	비용기반 옵티마이저(CBO)
개념	통계 정보가 없는 상태에서 사전 등록된 규칙에 따라 질의 실행 계획을 선택하는 옵티마이저	통계 정보로부터 모든 접근 결로를 고려한 질의실행계획을 선택하는 옵티마이저
핵심	규칙(우선 순위)기반	비용(수행 시간)기반
평가 기준	인덱스 구조, 연산자, 조건절 형태 등	레코드 개수, 블록 개수, 평균 행 길이 컬럼 값의 수, 컬럼 값 분포, 인덱스 높이, 클러스터링 팩터 등
장점	사용자가 원하는 처리경로로 유도하기가 쉬움	옵티마이저의 이해도가 낮아도 성능보장 가능 (기본설정)

 

3) SQL수행과정 내 옵티마이저역할

- 비용기반 옵티마이저 기반으로 쿼리 변환, 비용 산정, 계획 생성으로 구분된다.

 

SQL수행 시 옵티마이저 역할

서브엔진	역할
Query Transformer	- SQL을 좀 더 일반적이고 표준화된 형태로 변환
Estimator	- 쿼리 명령어 각 단계의 선택도, 카디널리티, 비용을 계산 - 궁극적으로 실행계획 전체에 대한 총비용 계산
Plan Generator	- 하나의 쿼리를 수행 시 후보군이 될 만한 실행계획들을 생성해내는 역할

 

4) 힌트 사용

- SQL 성능 개선의 핵심 부분으로 옵티마이저의 실행 계획을 원하는 대로 변경할 수 있게 한다.

- 옵티마이저가 항상 최선의 실행 계획을 수립할 수 없어 명시적인 힌트를 통해 실행계획을 변경한다.

 

힌트 사용 예시

SELECT /*+RULE*/ ENAME, SAL FROM EMP WHERE EMPNO > 9000;

- 비용 기반 옵티마이저에서 규칙기반 옵티마이저모드로 변경 수행

 

주요 옵티마이저 힌트

힌트	설명
/* +RULE */	규칙 기반 접근 방식을 사용하도록 지정
/* +CHOOSE */	오라클 옵티마이저 디폴트 값에 따름
/* +INDEX(테이블명 인덱스명) */	지정된 인덱스를 강제적으로 사용하도록 지정
/* +USER_HASH(테이블명) */	지정된 테이블의 조인이 Hash Join 형식으로 일어나도록 유도
/* +USER_MERGE(테이블명) */	지정된 테이블들의 조인이 Sort Merge형식으로일어나동록 유도
/* +USER_NL(테이블명) */	지정된 테이블들의 조인이 Nested Loop 형식으로 일어나도록 유도

 

(4) SQL 문 재구성

- SQL 문의 성능개선을 위해 재구성가이드를 참고하여 쿼리를 재구성한다

구성 가이드	설명
특정 값 지정	- 조건절의 > 또는 < 가 아닌 = 을 사용
별도의 SQL 사용	- 다양한 작업에 대해 하나의 SQL문을 사용할 경우 각 작업에 최적화 되지않은 결과 발생 - 하나의 SQL문 사용 시 UNION ALL 연산자를 사용
힌트 사용	- 옵티마이저가 비정상적인 실행 계획을 수립 시 힌트로서 액세스 경로 및 조인 순서를 제어할 수 있도록 함
HAVING 미사용	- 인덱스가 걸려잇는 컬럼은 HAVING사용 시 인덱스 미사용
인덱스만 질의 사용	가능한 인덱스만 이용해 질의를 수행하여 옵티마이저가 최적의 경로를 찾도록 유도

 

(5) 인덱스 재구성

- 인덱스를 재구성하거나 새로 생성하여 성능 개선에 참고한다.

 

인덱스 재구성 가이드 예시

구성 가이드	설명
자주 쓰는 컬럼 선정	조건절에 항상 사용되거나, 자주 사용되는 컬럼 설정
SORT명령어 생략	SORT명령어를 생략하기 위한 컬럼을 추가
분포도를 고려	분포도가 좋은 컬럼은 단독으로 인덱스를 생성
변경 적은 컬럼 선정	데이터의 변경이 적은 컬럼에 인덱스를 생성
결합 인덱스 사용	인덱스들이 자주 조합될 때는 결합 인덱스를 생성

 

[출저] 수제비 2021...! 정보처리기사 실기