[2020 정보처리기사 실기]Section 08.SQl응용(프로시저, 트리거)

이전글

2020/04/28 - [2020년도 상반기/정처기] - [2020 정보처리기사 실기]Section 08. SQL 응용(SELECT)

프로시저

1. 프로시저?

• 절차형 SQL을 활용하여 특정기능을 수행하는 일종의 트랜잭션 언어로, 호출을 통해 실행되어 미리 저장해 놓은 SQL작업 수행
• Stored Procedure라고도 함
• 시스템의 일일 마감 작업, Batch작업등에 주로 사용 

DECLARE
BEGIN
    [CONTROL] : /*조건문 또는 반복문*/
    [SQL]
    [EXCEPTION]
    [TRANSACTION] : /*수행된 데이터 작업들을 db에 적용할지 취소할지?*/
END;

 

2. 프로시저 생성 ; BODY에는 적어도 하나의 SQL을 포함

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

파라미터는 

IN	입력 데이터
OUT	출력 데이터
INPUT	입출력 데이터

CREAT OR REPLACE PROCEDURE emp_change_s(i_사원번호 IN INT)
IS /*변수를 사용하는 예약어 IS, 변수를 사용하지않았으므로 예약어 IS만 작성*/
BEGIN
    UPDATE 급여 SET 지급방식='S' WHERE 사원번호=i_사원번호;
    EXCEPTION 
        WHEN PROGRAM_ERROR THEN
            ROLLBACK;;
    COMMIT;
END;

 

3. 프로시저 실행

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

#EXAMPLE

EXECUTE emp_change_s(32);

 

4. 프로시저 제거

DROP PROCEDURE 프로시저명;

#EXAMPLE

DROP PROCEDUTE emp_change_s;

 

 

트리거

1.트리거?

Insert, Update, Delete등 Evenet 발할 때마다 작업을 자동으로 수행되는 절차형 SQL

• 데이터 변경 및 무결성 유지, 로그메세지 출력등에 사용
• DCL 사용불가능, DCL포함 프로시저나 함수 호출해도 오류발생

DECLARE
EVENT
BEGIN
    [CONTROL]
    [SQL]
    [EXCPETION]
END;

 

2. 트리거 생성 ; BODY에는 적어도 하나의 SQL 포함

CREATE [OR REPLACE] TRIGGER 트리거명 [동작시기 옵션][동작 옵션] ON 테이블명
REFEPENCING [NEW|OLD] AS 테이블명
FOR EACH ROW /*각 튜플마다 적용*/
[WHEN 조건식]
BEGIN 
    트리거 BODY;
END;

• 동작시기 옵션
  • AFTER : 변경된 후
  • BEFORE : 변경 전
• 동작 옵션
  • INDEST, DELETE, UPDATE 중 하나
• NEW|OLD : 트리거가 적용될 테이블의 별칭을 지정
  • NEW : 추가/수정에 참여할 튜플들의 집합(테이블)
  • OLD : 수정/삭제 전 대상이 되는 튜플들의 집합(테이블)

CREATE TRIGGER 학년정도_tri BEFORE INSERT ON 학생
REFERENCING NEW AS new_table
FOR EACH ROW
WHEN(new_table.학년 IS NULL) /*새로운 테이블에서 학년이 NULL이면 이 TRIGGER가 적용됨*/
BEGIN
	:new_table.gkrsus :='신입생';
    /*NEW 또는 OLD로 지정된 테이블 앞엔 :콜론 삽입*/
    /*저장하라는 의미로 :=를 사용하여야 함*/
END;

 

3. 트리거 제거

DROP TRIGGER 트리거명;

#EXAMPLE

DROP TRIGGER 학년정보_tri;

 

다음글

2020/04/29 - [2020년도 상반기/정처기] - [2020 정보처리기사 실기]Section 08.SQL응용(사용자 정의 함수, 커서)