정보처리기사 실기 - 7.SQL 응용

 

※학습목적으로 정리해놓은 내용입니다. 하단에 출처 표기

7. SQL 응용

7-1. 데이터 베이스 기본

1. 트랜잭션

 

트랜잭션의 특성 : ACID

원자성(Atomicity), 일관성(Consistency), 격리성(Isolation), 영속성(Durability)

 

데이터베이스의 고립화 수준(Isolation Level)

1. ReadUncommitted

한 트랜잭션에서 연산(갱신) 중인(아직 커밋되지 않은) 데이터를 다른 트랜잭션이 읽는 것을 허용. 연산(갱신) 중인 데이터에 대한 연산은 불허한다.

 

2. Read Committed

한 트랜잭션에서 연산(갱신)을 수행할 때, 연산이 완료될 때까지 연산대상 데이터에 대한 읽기를 제한, 연산이 완료되어 커밋된 데이터는 다른 트랜잭션이 읽는 것을 허용한다.

 

3. Repeatable Read

선행 트랜잭션이 특정 데이터를 읽을 때, 트랜잭션 종료 시까지 해당 데이터에 대한 갱신/삭제를 제한한다.

 

4. Serializable Read

선행 트랜잭션이 특정 데이터 영역을 순차적으로 읽을 때, 해당 데이터 영역 전체에 대한 접근을 제한한다.

 

트랜잭션 상태 변화(활부완실철)

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

 

트랜잭션 제어 : 트랜잭션 제어 언어(TCL)

 

명령어	핵심	설명
커밋(COMMIT)	트랜잭션 확정	트랜잭션을 메모리에 영구적으로 저장하는 명령어
롤백(ROLLBACK)	트랜잭션 취소	트랜잭션 내역을 무효화시키는 명령어
체크포인트(CHECKPOINT)	저장 시기 설정	ROLLBACK을 위한 시점을 지정하는 명령어

 

병행 제어 미보장 시 문제점(갱현모연)

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

 

병행 제어 기법의 종류 (로 낙타다)

기법	설명
로킹 (Locking)	- 같은 자원을 액세스하는 다중 트랜잭션 환경에서 DB의 일관성과 무결성을 유지하기 위해 트랜잭션의 순차적 진행을 보장하는 직렬화 기법 로킹의 특징 - 데이터베이스, 파일, 레코드 등은 로킹 단위가 될 수 있음 - 로킹 단위가 작아지면 데이터베이스 공유도가 증가 - 로킹 단위가 작아지면 로킹 오버헤드가 증가 - 한꺼번에 로킹할 수 있는 객체의 크기를 로킹 단위라고 함
낙관적 검증	트랜잭션이 어떠한 검증도 수행하지 않고 일단 트랜잭션을 수행하고, 트랜잭션 종료 시 검증을 수행하여 데이터베이스에 반영하는 기법
타임 스탬프 순서 (Time Stamp Ordering)	트랜잭션과 트랜잭션이 읽거나 갱신한 데이터에 대해 트랜잭션이 실행을 시작하기 전에 타임 스탬프(Time Stamp)를 부여하여 부여된 시간에 따라 트랜잭션 작업을 수행하는 기법
다중 버전 동시성 제어 (MVCC: Multi Version Concurrency Control)	트랜잭션의 타임스탬프와 접근하려는 데이터의 타임스탬프를 비교하여 직렬가능성이 보장되는 적절한 버전을 선택하여 접근하도록 하는 기법

 

회복기법(영속성 주요 기법) (화로체크)

회복 기법(Recovery) 개념

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

 

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

 

DDL 대상(도스테뷰인)

 

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

 

인덱스(Index)

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

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

 

인덱스의 종류 (순해비함 단결클)

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

 

DDL 명령어 (크알드트)

생성	CREATE	데이터베이스 오브젝트 생성하는 명령어
수정	ALTER	데이터베이스 오브젝트 변경하는 명령어
삭제	DROP	데이터베이스 오브젝트 삭제하는 명령어
	TRUNCATE	데이터베이스 오브젝트 내용 삭제하는 명령어

CREATE TABLE 사원 (     사번  VARCHAR(10) PRIMARY KEY , --테이블의 기본키를 정의     업무  VARCHAR(20) FOREIGN KEY REFFERENCES 부서(부서코드) , --외래키를 정의, 참조 대상을 테이블(컬럼명)로 명시     이름  VARCHAR(10) UNIQUE , -- 테이블 내에서 얻은 유일한 값을 갖도록 하는 제약 조건     생년월일  CHAR(8) NOT NULL , -- 해당 컬럼은 NULL 값을 포함하지 않도록 하는 제약 조건     성별 CHAR(1) CHECK ( 성별 = 'M' OR 성별 = 'F' ), -- CHECK : 개발자가 정의하는 제약조건 / 참이어야 하는 조건을 지정     입사일 DATE DEFAULT SYSDATE -- SYSDATE 는 현재시간 /날짜   );

# CREATE TABLE 의 컬럼에 사용되는 제약조건은 모두 사용 가능. ALTER TABLE 테이블명 ADD 컬럼명 데이터 타입 [제약조건]; ALTER TABLE 사원 ADD 전화번호 VARCHAR(11) UNIQUE; ALTER TABLE 테이블명 MODIFY 컬럼명 데이터 타입 [제약조건]; ALTER TABLE 사원 MODIFY 이름 VARCHAR(30) NOT NULL; # 컬럼 삭제 ALTER TABLE 테이블명 DROP 컬럼명; ALTER TABLE 사원 DROP 생년월일;  # 테이블 삭제 DROP TABLE 테이블명 [ CASCADE | RESTRICT ]; DROP TABLE 사원 # CASCADE : 참조하는 테이블까지 연쇄적으로 제거 # RESTRICT : 다른 테이블이 삭제할 테이블을 참조 중이면 제거하지 않는 옵션 # 테이블 내의 데이터들을 삭제 TRUNCATE TABLE 테이블 명 TRUNCATE TABLE 사원

CREATE VIEW 뷰이름 AS 조회쿼리 CREATE VIEW 사원뷰 AS SELECT 사번, 이름 FROM 사원 WHERE 성별 = 'M'; #  VIEW 테이블의 SELECT 문에는 UNION 이나 ORDER BY 를 사용할 수 없다.

* UNION : 집합 연산자로 중복 행이 제거된 쿼리 결과 집합이다.

CREATE OR REPLACE VIEW 뷰이름 AS 조회쿼리 DROP VIEW 뷰이름

CREATE [UNIQUE] INDEX 인덱스명 ON 테이블명(컬럼명1, 컬럼명2, ...) CREATE INDEX 사번인덱스 ON 사원(사번) ALTER [UNIQUE] INDEX 인덱스명 ON 테이블명(컬럼명1, 컬럼명2, ...); ALTER INDEX 사번인덱스 ON 사원(사번) DROP INDEX 인덱스명; DROP INDEX 사번인덱스; # UNIQUE 생략 가능

 

DML

SELECT / FROM / WHERE / GREOUP BY / HAVING / ODER BY

 

DCL

GRANT 권한 ON 테이블 TO 사용자; GRANT UPDATE ON 학생 TO 장길산; REVOKE 권한 ON 테이블 FROM 사용자 REVOKE UPDATE ON 학생 FROM 사용자

 

---

7-2 응용 SQL 작성하기

1. 집계성 SQL 작성

 

데이터 분석 함수 종류

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

 

집계 함수 종류

집계 함수	내용
COUNT	복수 행의 줄 수를 반환하는 함수
SUM	복수 행의 해당 컬럼 간의 합계를 계산하는 함수
AVG	복수 행의 해당 컬럼 간의 평균을 계산하는 함수
MAX	복수 행의 해당 컬럼 중 최댓값을 계산하는 함수
MIN	복수 행의 해당 컬럼 중 최솟값을 계산하는 함수
STDDEV	복수 행의 해당 컬럼 간의 표준편차를 계산하는 함수
VARIAN	복수 행의 해당 컬럼 간의 분산을 계산하는 함수

 

그룹 함수의 유형

ROLLUP 함수

- ROLLUP에 의해 지정된 컬럼은 소계(소그룹의 합계) 등 중간 집계 값을 산출하기 위한 그룹함수이다.

- 지정 컬럼의 수보다 하나 더 큰 레벨만큼의 중간 집계 값이 생성된다.

- ROLLUP의 지정 컬럼은 계층별로 구성되기 떄문에 순서가 바뀌면 수행 결과가 바뀜을 유의한다.

SELECT 컬럼1, 컬럼2, ..., 집계 함수  FROM 테이블명 [WHERE ...] GROUP BY [컬럼 ...] ROLLUP 컬럼 [HAVING ...] [ORDER BY ...]

- 소계 집계 대상이 되는 컬럼을 ROLLUP 뒤에 기재하고, 소계 집계 대상이 아닌 경우 GROUP BY 뒤에 기재한다.

- SELECT 뒤에 포함되는 컬럼이 GROUP BY 또는 ROLLUP 뒤에 기재되어야 한다는 점만 숙지하고 쿼리를 작성한다.

 

- 부서별 연봉 테이블

부서명(DEPT)	직위(JOB)	연봉(SALARY)
마케팅부	부장	4,000
마케팅부	차장	2,000
마케팅부	과장	1,500
기획부	부장	3,800
기획부	차장	1,800

SELECT DEPT, JOB, SUM(SALARY)  FROM  DEPT_SALARY GROUP BY ROLLUP(DEPT, JOB);

- 결과값

부서명(DEPT)	직위(JOB)	연봉(SALARY)
마케팅부	부장	4,000
마케팅부	차장	2,000
마케팅부	과장	1,500
마케팅부		7,500
기획부	부장	3,800
기획부	차장	1,800
기획부		5,600
		13,100

 

CUBE 함수

- CUBE는 결합 가능한 모든 값에 대해 다차원 집계를 생성하는 그룹 함수이다.

SELECT 컬럼명1, ..., 집계 함수  FROM 테이블명 [WHERE ...]  GROUP BY [컬럼명1, ...] CUBE(컬럼명a, d...) [HAVING ...] [ORDER BY ...]

- ROLLUP은 소계, 중간 집계를 나타내 주지만, CUBE는 결합 가능한 모든 값에 대해 다차원 집계 생성 - CUBE는 세분화된 소계가 구해짐

- 부서별 연봉 테이블

부서명(DEPT)	직위(JOB)	연봉(SALARY)
마케팅부	부장	4,000
마케팅부	차장	2,000
마케팅부	과장	1,500
기획부	부장	3,800
기획부	차장	1,800

SELECT DEPT, JOB, SUM(SALARY)  FROM DEPT_SALARY GROUP BY CUBE(DEPT, JOB);

- 결과값

부서명(DEPT)	직위(JOB)	연봉(SALARY)
		13,100
	부장	7,800
	차장	3,800
	과장	1,500
마케팅부		7,500
마케팅부	부장	4,000
마케팅부	차장	2,000
마케팅부	과장	1,500
기획부		5,600
기획부	부장	3,800
기획부	차장	1,800

 

GROUPING SETS 함수

- 집계 대상 컬럼들에 대한 개별 집계를 구할 수 있으며, ROLLUP이나 CUBE와는 달리 컬럼 간 순서와 무관한 결과를 얻을 수 있는 그룹 함수이다.

구문	설명
SELECT 컬럼명1, ..., 집계 함수  FROM 테이블명 [WHERE ...] GROUP BY [컬럼명1, ...]            GROUPING SETS(컬럼명1, ...) [HAVING ...] [ORDER BY ...]	개별 집계를 구할 수 있으며, ROLLUP 계층 구조와 달리 평등한 관계라 순서에 상관없이 동일한 결과

 

- 부서별 연봉 테이블

부서명(DEPT)	직위(JOB)	연봉(SALARY)
마케팅부	부장	4,000
마케팅부	차장	2,000
마케팅부	과장	1,500
기획부	부장	3,800
기획부	차장	1,800

SELECT DEPT, JOB, SUM(SALARY)  FROM DEPT_SALARY GROUP BY GROUPING SETS(DEPT, JOB, ( ));

- 결과값

부서명(DEPT)	직위(JOB)	연봉(SALARY)
		13,100
	부장	7,800
	차장	3,800
	과장	1,500
마케팅부		7,500
기획부		5,600

 

OLAP(Online Analytical Processing) = 윈도 함수

의사결정 지원 시스템으로, 사용자가 동일한 데이터를 여러 기준을 이용하는 다양한 방식으로 바라보면서 다차원 데이터 분석을 할 수 있도록 도와주는 기술이다.

 

윈도 함수의 분류(순행비)

분류	설명
순위 함수	- 레코드의 순위를 계산하는 함수 - RANK, DENSE_RANK, ROW_NUMBER 함수가 존재
행 순서 함수	- 레코드에서 가장 먼저 나오거나 가장 뒤에 나오는 값, 이전/이후의 값들을 출력하는 함수 - FIRST_VALUE, LAST_VALUE, LAG, LEAD 함수가 존재
그룹 내 비율 함수	- 백분율을 보여주거나 행의 순서별 백분율 등 비율과 관련된 통계를 보여주는 함수 - RATIO_TO_REPORT, PERCENT_RANK 함수가 존재

---

7-3 절차형 SQL 활용하기

1. 절차형 SQL

종류

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

사용자 정의 함수 : 일련의 SQL 처리를 수행하고, 수행 겨로가를 단일 값으로 반환할 수 있는 절차형 SQL

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

 

출력부

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

 

제어부

- IF문

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

- 간단한 케이스 문

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

- 검색된 케이스 문

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

- 반복문

LOOP 문 LOOP   문장;   EXIT WHEN 탈출조건; END LOOP; ------------------------------ WHILE 문 WHILE 반복 조건 LOOP    문장; EXIT WHEN 탈출조건; END LOOP; ------------------------------ FOR LOOP 문 FOR 인덱스 IN 시작값 .. 종료값 LOOP    문장; END LOOP;

- 예외부(EXCEPTION)

EXCEPTION   WHEN 조건 THEN       SET 명령어;

 

2. 프로시저

프로시저 구성(디비컨 SET)

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

 

프로시저 문법

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

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

 

프로시저 호출문

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

 

--① 선언부 CREATE PROCEDURE SALES_CLOSING (V_CLOSING_DATE IN CHAR(8)) -- 마감일자 IS   V_SALES_TOT_AMT NUMBER :=0; --총매출액	- SALES_CLOSING 이라는 프로시저 정의 - 외부에서 입력받을 CHAR 형 변수 - 내부에서 사용할 변수 선언
--② 시작/종료부 BEGIN DBMS_OUTPUT.PUT_LINE(V_CLOSING_DATE);	- 프로시저 시작
--③ 제어부 IF V_CLOSING_DATE < "20000101" THEN SET    V_CLOSING_DATE := "20200101"; END IF;	- V_CLOSING_DATE라는 값이 2000년 1월 1일 보다 작은 값일 경우, 202001이라는 값으로 대입
--④ SQL  SELECT SUM(SALES_AMT)     INFO V_SALES_TOT_AMT    FROM SALES_LIST_T --판매내역 테이블  WHERE SALES_DATE = V_CLOSING_DATE;	- SELECT 문에서 나온 결과는 INTO 문에 선언된 변수에 값이 전달(SELECT에서 조회된 결과가 없을 경우 NO_DATA_FOUND라는 결과가 발생)
--⑤ 예외부 EXCEPTION     WHEN NO_DATA_FOUND THEN        SET V_SALES_TOT_AMT :=0;	- NO_DATA_FOUND(쿼리 결과가 없을 경우)일 경우 V_SALES_TOT_AMT 변수에 0 대입
INSERT INTO SALES_CLOSED_T --마감내역 TABLE (SALES_DATE, SALES_TOT_AMT) VALUES(V_CLOSING_DATE, V_SALES_TOT_AMT); --⑥ 실행부 DBMS_OUTPUT_LINE(V_SALES_TOT_AMT); COMMIT; END;	- INSERT INTO ~ VALUE 구문을 이용하여 변수 값들을 테이블에 삽입 - 종료시 트랜잭션을 완료 처리

> EXECUTE SALES_CLOSING('20170425');		V_CLOSING_DATE라는 변수에 20170425라는 값 전달
출력	20170425 100000000	입력한 값 출력 V_SALES_TOT_AMT 출력

 

사용자 정의 함수

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

 

사용자 정의함수 문법

CREATE [ OR REPLACE ] FUNCTION 함수명 (파라미터_명 IN 데이터_타입, ...) RETURN 데이터_타입 IS    변수 선언 BEGIN    명령어;    RETURN 변수; END;

 

구성	설명
[ OR REPLACE]	OR REPLACE 명령은 기존 사용자 정의함수 존재 시에 현재 컬파일하는 내용으로 덮어씀(만약에 OR REPLACE 명령이 없고, 같은 이름의 사용자 정의함수가 존재하면 에러 발생)
모드 ([IN])	변수의 입출력을 구분하고, IN 1가지로 구성
RETURN 데이터 타입	사용자 정의함수가 종료될 때 반환하는 데이터 타입을 정의
BEGIN	사용자 정의함수의 시작을 알려주는 키워드
RETURN 데이터 값	사용자 정의함수가 종료될 때 반환하는 단일 값을 정의
END	사용자 정의함수의 끝을 알려주는 키워드

① 선언부 CREATE FUNCTION GET_AGE (V_BIRTH_DATE IN CHAR(8)) RETURN NUMBER IS   V_CURRENT_YEAR CHAR(4); --현재 연도   V_BIRTH_YEAR CHAR(4);     -- 생년   V_AGE NUMBER; -- 나이	- GET_AGE라는 사용자 함수 정의 - V_BIRTH_DATE를 입력변수로 선언 - CHAR형 변수 선언 - CHAR형 변수 선언 - NUMBER형 변수 선언 - 입력 받은 V_BIRTH_DATE 값 출력
② 시작/종료부 BEGIN DBMS_OUTPUT.PUT_LINE(V_BIRTH_DATE);
③ 제어부 IF V_BIRTH_DATE > "30000000" THEN    V_BIRTH_DATE = "20200101"; END IF;	- V_BIRTH_DATE 라는 값이 30000000보다 클 경우, 20200101이라는 값으로 대입
④ SQL SELECT TO_CHAR(SYSDATE, 'YYYY'),            SUBSTR(V_BIRTH_DATE, 1, 4)     INTO V_CURRENT_YEAR, -- 현재 연도            V_BIRTH_YEAR --생년     FROM DUAL; V_AGE = TO_NUMBER(V_CURRENT_YEAR) - TO_NUMBER(V_BIRTH_YEAR) +1; DBMS_OUTPUT.PUT_LINE(V_CURRENT_YEAR); DBMS_OUTPUT.PUT_LINE(V_BIRTH_YEAR); DBMS_OUTPUT.PUT_LINE(V_AGE);	- 현재 일자(SYSDATE)를 조회하고 연도 4자리만 파싱, 생년월일 8자리에서 4자리만 파싱하여 각각 변수에 입력 - 현재 연도와 생년을 숫자 형식으로 변환한 후 두 수의 차에 1을 더하고 나이 변수에 입력 - 현재 연도 출력 - 입력 받은 생년 출력 - 구해진 나이 출력
⑥ 반환부 RETURN V_AGE; --나이를 반환 END;	- 구해진 나이를 반환

SELECT GET_AGE('19900101')   FROM DUAL;		생년월일 값(1990년 1월 1일)을 가지고 나이를 조회
UPDATE EMPLOYEE_INFO_T      SET AGE = GET_AGE(BIRTH_DATE)  WHERE EMPLOYEE_ID = '2017001';		직원 아이디(EMPLOYEE_ID) 값을 활용하여 생일(BIRTH_DATE) 컬럼 내의 값을 직접 활용하여 나이를 수정
출력	19900101 2021 1990 32	입력받은 V_BIRTH_DATE 출력 현재 연도 V_CURRENT_YEAR 출력 입력받은 생년 V_BIRTH_YEAR 출력 구해진 나이 V_AGE 출력

 

트리거

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

 

트리거의 종류

종류	설명
행 트리거	데이터 변화가 생길 때마다 실행
문장 트리거	트리거에 의해 단 한 번 실행

 

트리거의 구성 (디이비컨 SE)

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

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

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

구성요소	설명
선언부 (DECLARE)	트리거의 명칭을 정의하는 부분
이벤트부 (EVENT)	트리거가 실행되는 타이밍, 이벤트를 명시하는 부분
시작/종료부 (BEGIN/END)	- 트리거의 시작과 종료를 표현하는 데 필수적이며, BEGIN/END가 쌍을 이루어 추가되므로 블록으로 구성 - 다수 실행을 제어하는 기본적 단우가 되며 논리적 프로세스를 구성
제어부 (CONTROL)	- 기본적으로는 순차적으로 처리 - 비교 조건에 따라 블록 또는 문장을 실행 - 조건에 따라 반복 실행
SQL	- DML을 주로 사용하고, 자주 사용되지 않지만 DDL(TRUNCATE 등)을 사용
예외부 (EXCEPTION)	- BEGIN~END 절에서 실행되는 SQL 문이 실행될 때 예외 발생 시 예외처리 방법을 정의하는 처리부

 

트리거 문법

CREATE [ OR REPLACE ] TRIGGER 트리거명 [ BEFORE | AFTER ] 유형 ON 테이블명 [ FOR EACH ROW ] BEGIN END;

구성	설명
[ OR REPLACE ]	OR REPLACE 명령은 기존 트리거 존재 시에 현재 컴파일하는 내용으로 덮어씀 (만약에 OR REPLACE 명령이 없고, 같은 이름의 트리거가 존재하면 에러 발생)
순서 ([BEFORE | AFTER])	DML과 트리거가 실행되는 순서의 전후 관계를 BEFORE, AFTER 키워드를 이용하여 결정 BEFORE : 테이블명에 대한 INSERT / UPDATE / DELETE 를 수행하기 전에 트리거가 실행하도록 지정하는 명령 AFTER : 테이블명에 대한 INSERT / UPDATE / DELETE가 성공적으로 실행되었을 때만 트리거가 실행하도록 지정하는 명령
유형	DML에 해당되는 INSERT, UPDATE, DELETE 중 트리거를 수행할 명령어 유형을 선택 - INSERT(새로운 행 삽입), UPDTAE(기존 행의 변경), DELETE(기존 행의 삭제)는 여러 개 중복으로 선택이 가능 예: INSERT OR DELETE
FOR EACH ROW	매번 변경되는 데이터 행의 수만큼 실행을 위한 명령어
BEGIN	트리거의 시작을 알려주는 키워드
END	트리거의 끝을 알려주는 키워드

-- ① 선언부 CREATE TRIGGER PUT_EMPLAYEE_HIST	- PUT_EMPLAYEE_HIST라는 트리거 생성
-- ② 이벤트부 AFTER UPDATE OR DELETE ON EMPLOYEE FOR EACH ROW	- 직원 정보(EMPLOYEE) 테이블 수정 및 삭제 후 해당 트리거 실행
--③ 시작/종료부 BEGIN
--④ 제어부 IF UPDATING THEN	- EMPLOYEE에 UPDATE 연산이 발생할 경우
--⑤ SQL INSERT INTO EMPLAYEE_HIST(EMPLAYEE_ID, EMPLAYEE_NAME, EMPLAYEE_STATUS) VALUES( :OLD.EMPLOYEE_ID, :NEW.EMPLOYEE_NAME, "부서이동");	- EMPLOYEE 테이블에 갱신 전 EMPLOYEE_ID, 갱신후 EMPLOYEE_NAME 값을 EMPLOYEE_HIST 테이블에 삽입
ELSE IF DELETING THEN   INSERT INTO EMPLOYEE_HIST(EMPLOYEE_ID, EMPLOYEE_NAME, EMPLOYEE_DEPT) VALUES(:OLD.EMPLOYEE_ID, :OLD.EMPLOYEE_NAME, "퇴사")'; END IF; END;	- EMPLOYEE에 DELETE 연산이 발생할 경우 - EMPLOYEE 테이블에 삭제 전 EMPLOYEE_ID, EMPLOYEE_NAME 값을 EMPLOYEE_HIST 테이블에 삽입

---

7-4 데이터 조작 프로시저 최적화

 

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

*APM(Application Performance Monitoring) : 안정적인 시스템 운영을 위해 부하량, 접속자 파악 및 장애진단 등을 목적으로 하는 성능 모니터링 도구를 의미한다.

 

옵티마이저 유형

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

 

힌트 사용

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

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

- 힌트 사용 예시

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

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

- 주요 옵티마이저 힌트

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

 

---

출처 : 수제비 2021 정보처리기사 실기 Vol.1 - 도서출판 건기원