정보처리기사 - SQL 응용 #124~125

124. 프로시저(Procedure)

124.1 프로시저(Procedure)

SQL을 사용하여 작성한 일련의 작업을 저장해두고 호출을 통해 원할 때마다 저장한 작업을 수행하도록 하는 절차형 SQL

• 데이터베이스에 저장되어 수행되기 때문에 스토어드(Stored) 프로시저라고도 불림
• 시스템의 일일 마감 작업, 일괄(Batch) 작업 등에 주로 사용됨

124.2 프로시저의 구성도

• DECLARE : 프로시저의 명칭, 변수, 인수, 데이터 타입을 정의하는 선언부
• BEGIN / END : 프로시저의 시작과 종료를 의미
• CONTROL : 조건문 또는 반복문이 삽입되어 순차적으로 처리
• SQL : DML, DCL이 삽입되어 데이터 관리를 위한 조회, 추가, 수정, 삭제 작업을 수행
• EXCEPTION : BEGIN ~ END 안의 구문 실행 시 예외가 발생하면 이를 처리하는 방법을 정의
• TRANSACTION : 수행된 데이터 작업들을 DB에 적용할지 취소할지를 결정하는 처리부

124.3 프로시저 생성

프로시저를 생성하기 위해서는 CREATE PROCEDURE 명령어를 사용

• 표기 형식

CREATE [OR REPLACE] PROCEDURE 프로시저명(파라미터)
[지역변수 선언]
BEGIN
    프로시저 BODY;
END;

• OR REPLACE : 선택적인(Optional) 예약어
  • 이 예약어를 사용하면 동일한 프로시저 이름이 이미 존재하는 경우, 기존의 프로시저를 대체할 수 있음
• 프로시저명 : 생성하려는 프로시저 이름을 지정
• 파라미터 : 프로시저 파라미터로는 다음과 같은 것들이 올 수 있음
  • IN : 호출 프로그램이 프로시저에게 값을 전달할 때 지정
  • OUT : 프로시저가 호출 프로그램에게 값을 반환할 때 지정
  • INOUT : 호출 프로그램이 프로시저에게 값을 전달하고, 프로시저 실행 후 호출 프로그램에 값을 반환할 때 지정
  • 매개변수형 : 호출 프로그램으로부터 전달받은 값을 저장할 변수의 이름을 지정
  • 자료형 : 변수의 자료형을 지정
• 프로시저 BODY
  • 프로시저의 코드를 기록하는 부분
  • BEGIN에서 시작하여 END로 끝나며, BEGIN과 END 사이에는 적어도 하나의 SQL문이 있어야 함

124.3.1 예제 1

• '사원번호'를 입력받아 해당 사원의 '지급방식'을 'S'로 변경하는 프로시저를 생성하라

1  CREATE OR REPLACE PROCEDURE emp_change_s(i_사원번호 IN INT)
2  IS
3  BEGIN
4      UPDATE 급여 SET 지급방식='S' WHERE 사원번호=i_사원번호;
5      EXCEPTION
6          WHEN PROGRAM_ERROR THEN
7              ROLLBACK;
8      COMMIT;
9  END;

1. 호출 프로그램이 전달한 값을 'i_사원번호'에 저장한 후 사용하는 프로시저 'emp_change_s'를 생성
2. 변수를 선언하는 예약어로, 변수를 사용하지 않으므로 예약어만 입력
3. 프로시저 BODY의 시작을 알리는 예약어로, 4부터 8까지가 하나의 블록이 됨
4. <급여> 테이블에서 '사원번호'가 'i_사원번호'로 받은 값과 같은 튜플의 '지급방식'을 'S'로 갱신
5. 예외처리의 시작을 알리는 예약어
6. SQL이 DBMS 내부 문제로 종료되었을 때 다음 문장을 수행
   • 예외의 조건(WHEN ~ THEN)에는 DBMS가 내부 문제로 종료(PROGRAM_ERROR THEN)되었을 때뿐만이 아니라, 데이터를 찾지 못했을 때, UNIQUE 옵션을 갖는 속성에 중복 데이터를 삽입할 때, 0으로 나누려 했을 때 등 여러 조건을 삽입할 수 있음
7. ERROR가 발생할 경우 수행되는 문장으로, ROLLBACK을 수행
8. 4에서 변경한 내역을 데이터베이스에 반영하는 트랜잭션 명령어
9. 프로시저 BODY의 종료를 알리는 예약어

124.4 프로시저 실행

프로시저를 실행하기 위해서는 EXECUTE 명령어 또는 CALL 명령어를 사용하며, EXECUTE 명령어를 줄여서 EXEC로 사용하기도 함

• 표기 형식

EXECUTE 프로시저명;
EXEC 프로시저명;
CALL 프로시저명;

124.4.1 예제

• '사원번호' 32를 인수로 하여 위에서 생성된 emp_change_s 프로시저를 실행하라

EXECUTE emp_change_s(32);

124.5 프로시저 제거

프로시저를 제거하기 위해서는 DROP PROCEDURE 명령어를 사용

• 표기 형식

DROP PROCEDURE 프로시저명;

124.5.1 예제

• 위에서 생성된 프로시저 emp_change_s를 제거하라

DROP PROCEDURE emp_change_s;

---

125. 트리거(Trigger)

125.1 트리거(Trigger)

데이터베이스 시스템에서 데이터의 삽입(Insert), 갱신(Update), 삭제(Delete) 등의 이벤트(Event)가 발생할 때 관련 작업이 자동으로 수행되게 하는 절차형 SQL

• 트리거는 데이터베이스에 저장되며, 데이터 변경 및 무결성 유지, 로그 메시지 출력 등의 목적으로 사용됨
• 트리거의 구문에는 DCL(데이터 제어어)을 사용할 수 없으며, DCL이 포함된 프로시저나 함수를 호출하는 경우에 오류가 발생
• 이벤트(Event) : 시스템에 어떤 일이 발생한 것을 말하며, 트리거에서의 이벤트는 데이터 삽입, 갱신, 삭제와 같이 데이터 조작 작업이 발생했음을 의미
• 무결성(Integrity) : 데이터베이스에 들어 있는 데이터의 정확성을 보장하기 위해 정확하지 않은 데이터가 데이터베이스 내에 저장되는 것을 방지하기 위한 제약조건
• 로그 메시지 출력 : 로그를 메시지로 출력할 때 트리거를 이용

125.2 트리거의 구성도

• DECLARE : 트리거의 명칭, 변수 및 상수, 데이터 타입을 정의하는 선언부
• EVENT : 트리거가 실행되는 조건을 명시
• BEGIN / END : 트리거의 시작과 종료를 의미
• CONTROL : 조건문 또는 반복문이 삽입되어 순차적으로 처리
• SQL : DML문이 삽입되어 데이터 관리를 위한 조회, 추가, 수정, 삭제 작업을 수행
• EXCEPTION : BEGIN ~ END 안의 구문 실행 시 예외가 발생하면 이를 처리하는 방법을 정의

125.3 트리거의 생성

트리거를 생성하기 위해서는 CREATE TRIGGER 명령어를 사용

• 표기 형식

CREATE [OR REPLACE] TRIGGER 트리거명 [동작시기 옵션][동작 옵션] ON 테이블명
REFERENCING [NEW | OLD] AS 테이블명
FOR EACH ROW
[WHEN 조건식]
BEGIN
    트리거 BODY;
END;

• OR REPLACE : 선택적인(Optional) 예약어
  • 이 예약어를 사용하면 동일한 트리거 이름이 이미 존재하는 경우, 기존의 트리거를 대체할 수 있음
• 동작시기 옵션 : 트리거가 실행될 때를 지정
  • AFTER : 테이블이 변경된 후에 트리거가 실행
  • BEFORE : 테이블이 변경되기 전에 트리거가 실행
• 동작 옵션 : 트리거가 실행되게 할 작업의 종류를 지정
  • INSERT : 테이블에 새로운 튜플을 삽입할 때 트리거가 실행
  • DELETE : 테이블의 튜플을 삭제할 때 트리거가 실행
  • UPDATE : 테이블의 튜플을 수정할 때 트리거가 실행
• NEW | OLD : 트리거가 적용될 테이블의 별칭을 지정
  • NEW : 추가되거나 수정에 참여할 튜플들의 집합(테이블)을 의미
  • OLD : 수정되거나 삭제 전 대상이 되는 튜플들의 집합(테이블)을 의미
• FOR EACH ROW : 각 튜플마다 트리거를 적용한다는 의미
• WHEN 조건식 : 선택적인(Optional) 예약어
  • 트리거를 적용할 튜플의 조건을 지정
• 트리거 BODY
  • 트리거의 본문 코드를 입력하는 부분
  • BEGIN으로 시작해서 END로 끝나는데, 적어도 하나 이상의 SQL문이 있어야 함
    • 그렇지 않으면 오류가 발생

125.3.1 예제

• <학생> 테이블에 새로운 튜플이 삽입될 때, 삽입되는 튜플에 학년 정보가 누락되었으면 '학년' 속성에 '신입생'을 저장하는 트리거를 '학년정보_tri'라는 이름으로 정의하라

1  CREATE TRIGGER 학년정보_tri BEFORE INSERT ON 학생
2  REFERENCING NEW AS new_table
3  FOR EACH ROW
4  WHEN (new_table.학년 IS NULL)
   BEGIN
5      :new_table.학년:='신입생';
   END;

1. <학생> 테이블에 튜플을 삽입하기 전에 동작하는 트리거 '학년정보_tri'를 생성
2. 새로 추가될 튜플들의 집합 NEW의 별칭을 로 명명
3. 모든 튜플을 대상으로 함
4. 에서 '학년' 속성이 NULL인 튜플에 '학년정보_tri'가 적용
5. 의 '학년' 속성에 '신입생'을 저장
   • 2에서 NEW 또는 OLD로 지정된 테이블 이름 앞에는 콜론(:)이 들어감
   • A:=B : A에 B를 저장하라는 의미로, '='가 아닌 ':='를 사용

125.4 트리거의 제거

트리거를 제거하기 위해서는 DROP TRIGGER 명령어를 사용

• 표기 형식

DROP TRIGGER 트리거명;