📌 [정보처리기사] SQL 활용 - 프로시저(Procedure)/트리거(Trigger)

 

 

데이터베이스 구축 - SQL 활용

---

 

목차

 

💡 주요 키워드 ? 프로시저, 트리거, 웹 응용 시스템, JDBC, ODBC, 동적 SQL, 사용자 정의 함수, ORM, 쿼리 성능 최적화, 절차형 SQL 테스트

 

 

프로시저(Procedure)의 개요

📌 프로시저란 절차형 SQL을 활용하여 특정 기능을 수행하는 일종의 트랜잭션 언어로, 호출을 통해 실행되어 미리 저장해 놓은 SQL 작업을 수행한다.

• 프로시저를 만들어 데이터베이스에 저장하면 여러 프로그램에서 호출하여 사용할 수 있다.
• 프로시저는 데이터베이스에 저장되어 수행되기 때문에 스토어드(Stored) 프로시저라고도 불린다.
• 프로시저는 시스템의 일일 마감 작업, 일괄(Batch) 작업 등에 주로 사용된다.

 

💡 절차형 SQL : C프로그래밍 언어와 같이 연속적인 실행이나 분기, 반복 등의 제어가 가능한 SQL을 의미한다.
💡 트랜잭션 언어 : 데이터베이스를 조작하고 트랜잭션을 처리하는 언어로 SQL과 TCL이 트랜잭션 언어에 속한다.

 

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

 

 

 

프로시저(Procedure) 생성

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

 

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

CREATE [OR REPLACE] PROCEDURE 프로시저명(파라미터)

[지역변수 선언]

BEGIN

    프로시저 BODY;

END;

 

🔔 예제) '사원번호'를 입력 받아 해당 사원의 '지급방식'을 "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;

 

💡 위 예제는 Oracle로 작성된 프로시저이다. DBMS마다 작성 방법이 조금씩 다르지만 구성 요소는 동일하다.

 

💡 해설

1. 파라미터로 'i_사원번호'를 전달받는 프로시저 'emp_change_s'를 생성한다.
2. 변수를 선언하는 예약어로, 변수를 사용하지 않으므로 예약어만 입력한다.
3. 프로시저 BODY의 시장을 알리는 예약어로, 4 부터 8까지가 하나의 블록이 된다.
4. <급여> 테이블에서 '사원번호'가 'i_사원번호'로 받은 값과 같은 튜플의 '지급방식'을 'S'로 갱신한다.
5. 예외처리의 시작을 알리는 예약어이다.
6. SQL이 DBMS 내부 문제로 종료되었을 때 다음 문장을 수행한다.
7. ERROR가 발생할 경우 수행되는 문장으로 ROLLBACK을 수행한다.
8. 4에서 변경한 내역을 데이터베이스에 반영하는 트랜잭션 명령어이다.
9. 프로시저 BODY의 종료를 알리는 예약어이다.

💡 예외의 조건(WHEN~THEN) : DBMS가 내부 문제로 종료(PROGRAM_ERROR)되었을 때 뿐만이 아니라, 데이터를 찾지 못했을 때, UNIQUE 옵션을 갖는 속성에 중복 데이터를 삽입할 때, 0으로 나누려 했을 때 등 여러 조건을 삽입할 수 있다.

 

 

 

프로시저(Procedure) 실행

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

 

• 표기 형식

EXECUTE 프로시저명;

EXEC 프로시저명;

CALL 프로시저명;

 

🔔 예제) '사원번호' 32를 인수로 하여 위에서 생성된 emp_change_s 프로시저를 실행하시오.

EXECUTE emp_change_s(32);

 

 

 

프로시저(Procedure) 제거

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

 

• 표기 형식

DROP PROCEDURE 프로시저명;

 

🔔 예제) 앞에서 생성된 프로시저 emp_change_s를 제거하시오.

DROP PROCEDURE emp_change_s;

 

 

---

 

 

트리거(Trigger)의 개요

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

 

• 트리거는 데이터베이스에 저장되며, 데이터 변경 및 무결성 유지, 로그 메시지 출력 등의 목적으로 사용된다.
• 트리거의 구문에는 DCL(데이터 제어어)을 사용할 수 없으며, DCL이 포함된 프로시저나 함수를 호출하는 경우에도 오류가 발생한다.
• 트리거에 오류가 있는 경우 트리거가 처리하는 데이터에도 영향을 미치므로 트리거를 생성할 때 세심한 주의가 필요하다.

 

💡 이벤트(Event) : 시스템에 어떤 일이 발생한 것을 말하며, 트리거에서 이벤트는 데이터의 삽입, 갱신, 삭제와 같이 데이터 조작 작업이 발생했음을 의미한다.
💡 로그 메시지 출력 : 사용자가 컴퓨터에 요청한 명령이나 컴퓨터가 데이터를 처리하는 과정 및 결과 등을 기록으로 남긴 것을 로그(Log)라고 하며, 이것을 메시지로 출력할 때 트리거를 이용할 수 있다.

 

 

트리거(Trigger)의 구성

📌 트리거는 선언, 이벤트, 시작, 종료로 구성되며, 시작과 종료 구문 사이에는 제어(CONTROL), SQL, 예외(EXCEPTION)가 포함된다.

 

📌 트리거 구성도

 

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

 

 

트리거(Trigger)의 생성

📌 트리거를 생성하기 위해서는 CREATE TRIGGER 명령어를 사요한다.

 

• 표기 형식

CREATE [OR REPLACE] TRIGGER 트리거명 동작시기 동작 ON 테이블명

[REFERENCING NEW | OLD AS 테이블명]

[FOR EACH ROW [WHEN 조건식]]

BEGIN

    트리거 BODY;

END;

 

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

🔔 예제) <학생> 테이블에 새로운 튜플이 삽입될 때, 삽입되는 튜플에 학년 정보가 누락됐으면 '학년' 필드에 "신입생"을 치환하는 트리거를 '학년정보_tri'라는 이름으로 정의하시오.

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

 

💡 해설

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

 

 

트리거(Trigger)의 제거

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

 

• 표기 형식

DROP TRIGGER 트리거명;

 

🔔 예제) '학년정보_tri'라는 트리거를 제거하는 SQL문을 작성하시오.

DROP TRIGGER 학년정보_tri;