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

프로시저 ⭐⭐⭐

프로시저의 개념

절차형 SQL을 활용하여 특정 기능을 수행할 수 있는 트랜잭션 언어

프로시저 호출을 통해 실행되며 이를 통해 일련의 SQL 작업을 포함하는 데이터 조작어를 수행한다.

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

프로시저 구성

선언부

CREATE [OR REPLACE] PROCEDURE 프로시저_명
...
IS
변수 선언

시작/종료부

프로시저의 시작을 알려주는 명령어 → BEGIN

프로시저의 끝을 알려주는 명령어 → END

제어부

1. 조건문

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

2. 간단한 케이스문

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

3. 검색된 케이스 문 

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

4. Loop문

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

5. While 문

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

6. FOR LOOP 문

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

제어부

제어부

예외부

EXCEPTION
	WHEN 조건 THEN
		SET 명령어;

실행부

해당 프로시저에서 수행한 DML을 DBMS에 반영할지 복구할지를 결정하는 부분

COMMIT : 하나의 트랜잭션이 성공적으로 끝나고 데이터베이스가 일관성 있는 상태에 있을 때 하나의 트랜잭션이 끝났을 때 사용하는 연산

ROLLBACK : 하나의 트랜잭션이 비정상적으로 종료되어 트랜잭션 원자성이 꺠질 경우 처음부터 다시 시작하거나, 부분적으로 연산을 취소하는 연산

프로시저 호출문 작성

EXECUTE 프로시저_명 (파라미터1, 파라미터2, ...)

사용자 정의함수 ⭐⭐⭐

사용자 정의함수

절차형 SQL을 활용하여 일련의 SQL 처리를 수행하고, 수행 결과를 단일 값으로 반환할 수 있는 절차형 SQL

사용자 정의 함수 구성 (디비컨SER)

사용자 정의 함수 구성

사용자 정의 함수 - 선언부

CREATE [OR REPLACE] FUNCTION 함수명
...
IS
RETURN 데이터_타입
변수 선언

사용자 정의 함수 - 시작/종료부

프로시저에 BEGIN / END 키워드는 반드시 포함

사용자 정의 함수 - 제어부

단위 블록별 실행 흐름을 제어하는 부분으로 크게 IF문과 ELSE 문으로 나뉜다.

사용자 정의 함수 - SQL

데이터 관리를 위한 조회, 추가, 수정, 삭제를 수행하는 부분

사용자 정의 함수 - 예외부

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

사용자 정의 함수 - 반환부

RETURN 명령을 통해 사용자 정의함수 조욜 시 사용자 정의함수를 쿼리에 반환하는 단일값 정의

트리거 ⭐⭐⭐

트리거의 개념

특정 테이블에 삽입, 수정, 삭제 등의 데이터 변경 이벤트가 발생하면 DBMS에서 자동적으로 실행되도록 구현된 프로그램

트리거 종류

트리거 종류

트리거의 구성

• 프로시저나 사용자 정의함수와 기본적 문법은 같다.
• 반환 값이 없고 DML을 주된 목적으로 한다는 점에서는 프로시저와 유사
• EVENT 명령어를 통해 트리거 실행을 위한 이벤트를 인지함
• 외부 변수 IN / OUT이 없다는 점은 프로시저나 사용자 정의함수와 다르다.

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

트리거 구성

트리거 - 선언부

CREATE [OR REPLACE] TRIGGER 트리거명

트리거 - 이벤트부

순서 유형 ON 테이블명
[FOR EACH ROW]

EX )
AFTER UPDATE OR DELETE
ON EMPLOYEE
FOR EACH ROW

시작 / 종료부

BEGIN / END 반드시 포함 되어야 한다.

제어부

단위 블록별 실행흐름을 제어하는 부분으로 IF문과 CASE 문으로 나뉜다.

SQL

데이터 관리를 위한 조회, 추가, 수정, 삭제를 수행하는 부분

SELECT, INSERT, DELETE, UPDATE 문장을 주로 사용

예외부

반드시 포함될 필요는 없다 / 실행 중 발생 가능한 예외 상황을 수행하는 부분