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

1. 프로시저

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

프로시저 호출을 통해 실행되며, 이를 통해 일련의 SQ?l 작업을 포함하는 DML를 수행한다.  

 

• 프로시저 구성
  • 선언부(DECLARE)
  • 시작/종료부(BEGIN/END)  
  • 제어부(CONTRAL)
  • SQL
  • 예외부(EXCEPTION)
  • 실행부(TRANSACTION)
• 프로시저 구성 상세

선언부

CREATE [OR REPLACE] PROCEDURE 프로시저_명  
파라미터_명 [MODE] 데이터 타입
...
IS
변수 선언

 

구성	설명
CREATE	객체 생성
[OR REPLACE]	기존 프로시저 존재 시 덮어씀 (OR REPLACE 없이 동일 프로시저 존재시 에러발생)
PROCEDURE	프로시저 사용
프로시저 명
파라미터 명	프로시저와 운영체제 간 필요한 값을 전송하기 위한 인자
[MODE]	변수 입출력 구분, IN/OUT/INOUT 으로 구성
데이터 타입	CHAR(고정 길이 문자열), VARCHAR(가변 길이 문자열), NUMBER
IS [AS]	PL/SQL 블록 시작 IS 또는 AS 키워드 작성
변수의 선언	프로시저 내 사용할 변수 설정

 

프로시저 SQL

유형	동작
SELECT	데이터 조회
INSERT	데이터 생성
UPDATE	데이터 변경
DELETE	데이터 삭제

 

예외부

EXCEPTION
	WHEN 조건 THEN
    	SET 명령어;

 

실행부

 

COMMIT : 하나의 트랜잭션 성공적으로 끝나고, DB가 일관성 있는 상태일 때 하나의 트랜잭션이 끝났을 때 사용

ROLLBACK : 하나의 트랜잭션이 비정상 종료되어 트랜잭션 원자성이 깨질 경우 다시 시작 또는 부분적 연산 취소  

 

• 프로시저 호출문 작성
  • SQL> EXECUTE(EXEC) 프로시저_명 (파라미터_1, 파라미터_2, ...);

 

2. 사용자 정의 함수  

• 사용자 정의 함수 구성
  • 선언부(DECLARE)
  • 시작/종료부(BEGIN/END)  
  • 제어부(CONTRAL)
  • SQL
  • 예외부(EXCEPTION)
  • 반환부(RETURN)
• 프로시저와 Transaction, Return만 차이나고 문법 동일

3. 트리거(Trigger) 

• 트리거의 목적
  • 특정 테이블에 대한 데이터 변경을 시작점으로 설정, 그와 관련된 작업을 자동적으로 수행하기 위함
  • DBMS의 CRUD 작업의 자동화
  • 데이터 무결성 유지 및 로그 메시지 출력 등의 처리를 위해 트리거 사용
• 트리거의 종류
  • 행 트리거 : 데이터 변화 시 실행
  • 문장 트리거 : 트리거에 의해 단 한 번 실행
• 트리거의 구성
  • 선언부(DECLARE)
  • 이벤트부(EVENT)
  • 시작/종료부(BEGIN/END)  
  • 제어부(CONTRAL)
  • SQL
  • 예외부(EXCEPTION)