[정처기 필기] 「3」 | SQL 활용 - (4.1) 프로시저, 트리거

[정처기 필기] 「3」 | SQL 활용 - (4.1) 프로시저, 트리거

「1」 소프트웨어 설계

「2」 소프트웨어 개발

> 「3」 데이터베이스 구축

- 논리 데이터베이스 설계, 물리 데이터베이스 설계, SQL 응용, > SQL 활용, 데이터 전환

「4」  프로그래밍 언어 활용

「5」  정보시스템 구축 관리

 

> 1 프로시저

> 2 트리거

3 사용자 정의 함수

4 DBMS 접속 기술

1. 프로시저

프로시저(Procedure)의 개요

 

절차형 SQL을 활용하여 특정 기능을 수행하는 트랜잭션 언어, 호출을 통해 미리 적은 SQL 작업 수행

 

- 프로시저를 만들어 데이터베이스에 저장하면, 여러 프로그램에서 호출하여 사용 가능

- 데이터베이스에 저장하여 수행되므로 == 스토어드(Stored) 프로시저

- 시스템의 일일 마감 작업, 일괄(Batch) 작업

 

구성도

	프로시저 DECLARE(필수) BEGIN(필수)   - CONTROL   - SQL   - EXCEPTION   - TRANSACTION END(필수)
데이터 →			→ 결과

 

- DECLARE : 프로시저 명칭, 변수, 인수, 데이터 타입 정의하는 선언부

- BEGIN / END : 프로시저 시작, 종료

- CONTROL : 조건문 또는 반복문 삽입되어 순차적으로 처리

- SQL : DML, DCL이 삽입되어 데이터 관리를 위한 조회, 추가, 수정, 삭제 작업 수행

- EXCEPTION : BEGIN ~ END 안의 구문 실행 시 예외 발생하면 처리 방법 정의

- TRANSACTION : 수행된 데이터 작업들을 DB에 적용할지 취소할지 결정하는 처리부

 

프로시저 생성

 

CREATE PROCEDURE 명령어를 사용하여 프로시저 생성

 

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

 

- OR REPLACE : 동일한 프로시저 이름이 존재하는 경우, 기존의 프로시저를 대체

- 파라미터

  : IN - 호출 프로그램이 프로시저에게 값 전달

  : OUT - 프로시저가 호출 프로그램에게 값 반환

  : INOUT - 호출 프로그램이 프로시저에게 값 전달, 프로시저 실행 후 호출 프로그램에 값 반환

  : 매개변수명 - 호출 프로그램으로부터 전달받은 값을 저장할 변수의 이름 지정

  : 자료형 - 변수의 자료형 지정

- 프로시저 BODY :  코드 입력, BEGIN에서 END로 끝나며, 사이에 하나 이상 SQL문

 

ex)

CREATE OR REPLACE PROCEDURE emp_chage_s(i_사원번호 IN INT)

IS

BEGIN

    UPDATE 급여 SET 지급방식 = 'S' WHERE 사원번호 = i_사원번호;

    EXCEPTION

        WHEN PROGRAM_ERROR THEN

            ROLLBACK;

    COMMIT;

END;

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

 

프로시저 실행

 

EXECUTE 명령어(줄여서 EXEC도 가능) 또는 CALL 명령어를 사용하여 프로시저를 실행

 

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

 

ex)

EXECUTE emp_change_s(32);

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

 

프로시저 제거

 

DROP PROCEDURE 명령어를 사용하여 프로시저 제거

 

DROP PROCEDURE 프로시저명;

 

ex)

DROP PROCEDURE emp_change_s;

// 위에서 생성된 프로시저 emp_change_s를 제거

2. 트리거

트리거(Trigger)의 개요

 

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

 

- 데이터베이스에 저장, 데이터 변경 / 무결성 유지, 로그 메시지 출력 등의 목적

- DCL 사용 불가, DCL이 포함된 프로시저나 함수 호출하는 경우도 오류 발생

- 트리거에 오류 있으면, 트리거가 처리하는 데이터에도 영향 미치므로 트리거를 생성할 때 주의 필요

 

트리거의 구성

 

선언, 이벤트, 시작, 종료로 구성, 시작과 종료 구문 사이 제어(CONTROL), SQL, 예외(EXCEPTION) 포함

 

구성도

 

	트리거 DECLARE(필수) EVENT(필수) BEGIN(필수)   - CONTROL   - SQL   - EXCEPTION END(필수)
이벤트 →			→ 데이터 변경

 

- DECLARE : 트리거 명칭, 변수 / 상수, 데이터 타입 정의하는 선언부

- EVENT : 트리거가 실행되는 조건 명시

- BEGIN / END : 트리거 시작, 종료

- CONTROL : 조건문 또는 반복문 삽입되어 순차적으로 처리

- SQL : DML이 삽입되어 데이터 관리를 위한 조회, 추가, 수정, 삭제 작업 수행

- EXCEPTION : BEGIN ~ END 안의 구문 실행 시 예외 발생하면 처리 방법 정의

 

트리거의 생성

 

CREATE TRIGGER 명령어를 사용하여 트리거를 생성

 

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

 

- OR REPLACE : 동일한 트리거 이름이 이미 존재하는 경우, 기존의 트리거 대체

- 동작시기 : 실행될 때를 지정

  : AFTER - 테이블 변경된 후에 트리거 실행

  : BEFORE - 테이블 변경 전에 트리거 실행

- 동작 : 작업의 종류 지정

  : INSERT - 테이블에 새로운 튜플 삽입 시 트리거 실행

  : DELETE - 테이블의 튜플 삭제 시 트리거 실행

  : UPDATE - 테이블의 튜플 수정 시 트리거 실행

- NEW | OLD : 트리거가 적용될 테이블의 별칭 지정

  : NEW - 추가, 수정에 참여할 튜플의 집합 의미

  : OLD - 수정, 삭제 전 대상이 되는 튜플의 집합 의미

- FOR EACH ROW : 각 튜플마다 트리거를 적용

- WHEN 조건식 : 트리거를 적용할 튜플의 조건 지정

- 트리거 BODY : 본문 코드 입력, BEGIN에서 END로 끝나며, 사이에 하나 이상의 SQL문, 아니면 오류발생

 

ex)

CREATE TRIGGER 학년정보_tri BEFORE INSERT ON 학생

REFERENCING NEW AS new_table

FOR EACH ROW

WHEN (new_table.학년 IN NULL)

    BEGIN

        :new_table.학년 := '신입생';

// NEW 또는 OLD로 지정된 테이블 이름 앞에는 콜론(:), A := B, A를 B로 치환

    END;

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

 

트리거의 제거

 

DROP TRIGGER 명령어 사용하여 트리거를 제거

 

DROP TRIGGER 트리거명;

 

ex)

DROP TRIGGER 학년정보_tri;

// '학년정보_tri'라는 트리거를 제거

 

 

 

 

 

 

출처 | <시나공> 정보처리기사 필기 2024 기본서 (길벗알앤디)