[정보처리기사 실기] 8. SQL 응용-Chapter 1. 절차형 SQL 작성하기

정리 내용은 [수제비 2020 정보처리기사 실기]책을 기반으로 작성하였습니다.

2020 수제비 정보처리기사 실기(1권+2권 합본세트)

---

1. 프로시저

개념
절차형 SQL을 활용하여 특정 기능을 수행할 수 있는 트랜잭션 언어
프로시저 호출을 통해 실행. 이를 통해 일련의 SQL 작업을 포함하는 데이터 조작어 수행

 

구성
선언부, 시작/종료부, 제어부, SQL, 예외부, 실행부

 

프로시저 구성 상세
(1) 선언부(DECLARE): 프로시저의 명칭. 변수와 인수 그리고 그에 대한 데이터 타입 정의

프로시저 선언방법

구성	설명
CREATE	DMBS 내에 객체를 생성
[ON REPLACE]	기존 프로시저 존재 시에 현재 컴파일 내용으로 덮어씀. 만약 해당 구문이 없고, 같은 이름 프로시저 존재하면 에러 발생
PROCEDURE	프로시저를 사용한다는 의미
프로시저명	해당 프로시저 지칭하는 이름
파라미터명	프로시저 운영체제간 필요한 값을 전송하기 위한 인자
[MODE]	변수 입출력을 구분하고 IN / OUT / INOUT 3가지 구성 IN: 운영체제에서 프로시저로 값 전달 / OUT: 프로시저에서 처리된 결과를 운영체제로 전달 INOUT: IN, OUT 두 가지 기능 동시에 수행
데이터타입	해당 파라미터의 데이터 타입(용도에 따라 선택). CHAR / VARCHARE / NUMBER
IS [AS]	PL/SQL 블록을 시작. IS 또는AS키워드를 작성
변수의 선언	프로시저 내에서 사용할 변수와 변수에 대한 초기값 설정

(2) 시작/종료부(BEGIN/END): 프로시저의 시작과 종료를 표현. 쌍으로 표시
BEGIN: 프로시저의 시작을 알려줌 / END: 프로시저의 끝을 알려줌

(3) 제어부(CONTROL)기본적으로 순차적으로 처리. 조건문과 반복문을 이용하여 문장 처리
조건문: IF문(IF~ELSEIF~ELSE 문) / case문(CASE & WHEN & SET)
반복문: LOOP문/ WHILE 문 / FOR LOOP문

(4) SQL: DML 주로 이용. 자주 사용되지는 않지만 DDL중 TRUNCATE 사용

유형	동작	설명
SELECT	데이터 조회	해당 테이블을 구성하는 튜플 중 전체 또는 조건을 만족하는 튜플을 검색하는 명령
INSERT	데이터 생성	해당 테이블에 새로운 튜플을 삽입할 때 사용
UPDATE	데이터 변경	해당 테이블에 있는 튜플 중 특정 튜플의 내용을 변경할 때 사용하는 명령
DELETE	데이터 삭제	해당 테이블에 있는 튜플 중 특정 튜플을 삭제할 때 사용하는 명령

(5) 예외부(SQL): BEGIN~END 절에서 실행하는 SQL문이 실행 될 때 예외 발생 시 예외 처리 방법 정의

(6) 실행부(TRANSECTION)

COMMIT: 하나의 트랙잭션이 성공적으로 끝나고, 데이터베이스가 일관성 있는 상태에 있을 때 하나의 트랜잭션이 끝났을 때 사용하는 연산
ROLLBACK: 하나의 트랜잭션이 비정상적으로 종료되어 트랜잭션 원자성이 깨질 경우 처음부터 다시 시작하거나, 부분적으로 연산을 취소하는 연산

 

프로시저 호출문 작성
응용 프로그램에서 호출하거나 내부 스케줄러에 의해 배치 작업을 수행하는 경우 사용
EXCUTE 또는 EXEX 명령어를 사용하여 프로시저 실행
프로시저는 입출력 변수가 존재하는 경우 변수를 입력하여 실행데이터 유형의 경우 자동 변환 되어 별도 오류가 발생하지 않는 경우 많지만 가급적 프로시저에서 선언한 데이터 타입과 동일하게 입출력 변수를 넣어서 사용하는 것이 좋다.

 

2. 사용자 정의 함수

개념
절차형 SQL을 활용하여 일련의 SQL 처리를 수행하고, 수행 결과를 단일 값으로 반환할 수 있는 절차형 SQLDBMS에서 제공되는 공통적 함수 이외에 사용자가 직접 정의하고 작성

 

구성
선언부, 시작/종료부, 제어부, SQL, 예외부, 반환부
사용자 정의함수는 호출에 의해 실행, 반환되는 단일값 조회 또는 삽입, 수정 작업에 이용하는 것이 일반적
기본적인 부분은 프로시저와 동일, 종료 시 단일 값을 반환한다는 것이 프로시저의 가장 큰 차이점

 

사용자 정의함수 구성 상세
(1) 선언부(DECLARE): 사용자 정의함수의 이름. 변수와 인수 그리고 그에 대한 데이터 타입 정의

사용자 정의함수 선언 방법

구성	설명
CREATE	DMBS 내에 객체 트리거, 함수, 프로시저 생성
[ON REPLACE]	기존 사용자 정의 함수 존재 시에 현재 컴파일 내용으로 덮어씀. 만약 해당 구문이 없고, 같은 이름 사용자 정의 함수 존재하면 에러 발생
FUNCTION	FUNCTION은 사용자 정의함수를 사용한다는 의미
함수명	해당 사용자 정의함수를 지칭하는 이름
RETURN 데이터타입	함수가 반환할 때 데이터 타입을 지정
파라미터명	사용자 정의함수와 운영체제 간 필요한 값을 전송하기 위한 인자
[MODE]	변수 입출력을 구분하고 IN / OUT / INOUT 3가지 구성
데이터타입	해당 파라미터의 데이터 타입(용도에 따라 선택). CHAR / VARCHARE / NUMBER
IS [AS]	PL/SQL 블록을 시작. IS 또는AS키워드를 작성
변수의 선언	사용자 정의함수 내에서 사용할 변수와 변수에 대한 초기값 설정

(2) 시작/종료부(BEGIN/END): 사용자 정의함수의 실행 시작과 종료를 표현. 쌍으로 표시. 반드시 포함되어야 함

(3) 제어부(CONTROL): 기본적으로 순차적으로 처리. 비죠 조건에 따라 블록 또는 문장을 실행. 조건에 따라 반복 실행

(4) SQL: 데이터 관리를 위한 조회, 추가, 수정, 삭제를 수행하는 부분INSERT, DELETE, UPDATE를 통한 데이터 조작은 할 수 없고, SELECT를 통해 조회만 가능

(5) 예외부(EXECTION): 실행 중 발생 가능한 예외사항을 수행하는 부분

(6) 반환부(RETURN): RETURN 명령을 통해 사용자 정의함수 종료 시 사용자 정의함수를 호출한 쿼리에 반환하는 단일값을 정의

 

사용자 정의함수 호출문 작성
작성된 사용자 정의함수는 외부에서의 호출을 통해 실행

 

3. 사용자 정의 함수

개념
특정 테이블에 삽입, 수정, 삭제 등의 데이터 변경 이벤트가 발생하면 DBMS에서 자동적으로 실행되도록 구현된 프로그램이벤트는 전체 트랜잭션 대상과 각행에 의해 발생되는 경우 모두를 포함할 수 있으며, 테이블 뷰와 DB 작업을 대상으로 정의

 

목적
특정 테이블에 대한 데이터 변경을 시점으로 설정하고, 그와 관련된 작업을 자동적으로 수행
이벤트와 관련된 테이블의 데이터 삽입, 추가, 삭제 작업을 DBMS가 자동적으로 실행시키는데 활요
데이터 무결성 유지 및 로그메시지 출력 등의 별도 처리를 위해 트리거를 사용

 

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

 

구성
선언부, 이벤트부, 시작/종료부, 제어부, SQL, 예외부
프로시저나 사용자 정의함수와 기본적 문법 같음
반환값이 없다는 점, DML을 주된 목적으로 한다는 점에서 프로시저와 유사
EVENT 명령어를 통해 트리거 실행을 위한 이벤트를 인지한다는 점, 외부 변수 IN/OUT 없다는 점은 프로시저나 사용자 정의함수와 다름

 

트리거 구문 상세
(1) 선언부(DECLRAE): 트리거 변수, 이름 타입을 선언하는 부분

트리거 선언 방법

구성	설명
CREATE	DMBS 내에 객체 트리거, 함수, 프로시저 생성
[ON REPLACE]	기존 트리거 존재 시에 현재 컴파일 내용으로 덮어씀. 만약 해당 구문이 없고, 같은 이름 트리거 존재하면 에러 발생
TRIGGER	TRIGGER은 트리거를 사용한다는 의미
트리거명	해당 트리거를 지칭하는 이름

(2) 이벤트부(EVENT): 트리거 타이밍, 트리거 이벤트, 트리거를 수행할 테이블 지정. 트리거 이벤트 순서, 트리거 이벤트 유형에 맞게 트리거 수행을 위한 조건 입력
매번 변경되는 데이터 행 수만큼 실행을 위한 명령어(FOR EACH NOW)를 정의하고 함

FOR EACH NOW 정의 방법

트리거 이벤트 순서
BEFORE: 이벤트 부 테이블에 대한 INSERT, UPDATE, DELETE 수행 전 트리거 실행하도록 지정
AFTER: 이벤트 부 테이블에 대한 INSERT, UPDATE, DELETE 수행 후 트리거 실행하도록 지정
트리거 이벤트 유형
DML에 해당되는 INSERT, UPDATE, DELETE중 트리거 수행할 명령어 유형 선택. 여러 개 중복 선택 가능
INSERT(새로운 행 삽입) / UPDATE(기존 행의 변경) / DELETE(기존 행 삭제)

(3) 시작/종료부(BEGIN/END): 트리거의 실행 시작과 종료를 알려주는 부분. 반드시 포함되어야 한다.

(4) 제어부(CONTROL): 단위 블록별 실행 흐름을 제어하는 부분으로 IF문과 CASE문으로 나뉨

(5) SQL: 데이터 관리를 위한 조회, 추가 수정, 삭제를 수행하는 부분. SELECT, INSERT, DELETE, UPDATE 문장 주로 사용 행 트리거 안에서 OLD 및 NEW 수식자 접두어를 붙여 데이터 변경 전 후 열의 값 참조

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

(6) 예외부(EXECTION): 반드시 포함될 필요 없음. 실행 중 발생 가능한 예외 상황 수행

 

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