DataBase+SQL

PROCEDURE TRIGGER

campanula 2023. 6. 29. 17:49

트리거: 서브 프로그램 단위의 하나인 TRIGGER는 테이블, 뷰, 스키마 또는 데이터베이스에 관련된 PL/SQL 불록 ( 또는 프로시저)으로 관련된 특정 사건(EVENT)이 발생될 때마다 묵시적(자동)으로 해당 PL/SQL 블록이 실행

데이터 베이스 내에 오브젝트로서 저장되어 관리되고, 사용자가 지정해서 실행을 할 수 없을며, 오직 TRIGGER 생성시 정의한 특정 사건(EVENT)에 의해서만 묵시적인 자동 실행(FIRE)이 이루어진다.

 

TRIGERR 몸체(실행부)에 TCL 명령(COMMIT, ROLLBACK,SAVEPOINT 명령)이 포함될 수 없다.

 

트리거란 특정 테이블의 데이터에 변경이 가해졌을 때 자동으로 수행되는 저장 프로시저

저장 프로시저는 필요할 때마다 사용자가 직접 EXECUTE 명령어로 호출해야 하지만 트리거는 테이블의 데이터가 INSERT, UPDATE, DELETE문에 의해 변경 될 때 자동으로 수행. (트리거를 사용자가 직접 실행X)

 

CREATE TRIGGER TRIGGER_NAME
TIMING[BEFORE|AFTER] --1
EVENT[INSERT|UPDATE|DELETE] --2
ON TABLE_NAME
[FOR EACH ROW]
[WHEN CONDITIONS]
BEGIN
	STATEMENT
END

 

① 트리거의 타이밍(트리거의 동작 시점을 설정)

[BEFORE] 타이밍은 어떤 테이블에 INSERT, UPDATE, DELETE 문이 실행될 때 해당 문장이 실행되기 전에 트리거가 가지고 있는 BEGIN ~ END 사이의 문장을 실행한다. [AFTER] 타이밍은 INSERT, UPDATE, DELETE 문이 실행되고 난 후에 트리거가 가지고 있는 BEGIN ~ END 사이의 문장 실행

 

②트리거의 이벤트(트리거 동작을 위한 이벤트 종류를 정의하는 단계)

트리거의 이벤트는 사용자가 어떤 DML(INSERT, UPDATE, DELETE)문을 실행했을 때 트리거를 발생시킬 것인지 결정, ON 테이블명은 트리거가 주목하는 대상 테이블을 정의

 

③ 트리거의 유형 (FOR EACH ROW에 의해 문장 레벨 트리거와 행 레벨 트리거로 나눔)

FOR EACH ROW 생략 - 문장 레벨 트리거 (영향을 받는 행이 전혀 없더라고 TRIGGER가 한번은 실행. TRIGGER 작업이 영향을 받는 행의 데이터 또는 TRIGGER 이벤트 자체에서 제공하는 데이터에 종속되지 않은 경우 유용)

FOR EACH ROW 기술 - 행 레벨 트리거 (추가되는 행의 수만큼 트리거가 동작, 행 내에서 이벤트 발생되는걸 기준으로 트리거의 감시, 보완 범위를 정함, 영향을 받는 행의 데이터나 TRIGGER 이벤트 자체에서 제공하는 데이터에 종속되는 경우 유용)

 

④ :NEW & :OLD

행 레벨 트리거에서 컬럼의 실제 데이터 값을 제어하는데 사용되는 연산자

INSERT 문: 입력된 컬럼의 값 :NEW

DELETE문: 삭제되는 컬럼의 값 :OLD,

UPDATE문: 변경 전 컬럼 데이터 값 :OLD, 수정할 새로운 데이터값 :NEW

 

⑤ 트리거의 몸체 (BEGIN ~ END)

해당 타이밍에 해당 이벤트가 발생하게 되면 실행될 기본 로직이 포함되는 부분으로 BEGIN ~ END에 기술

 

-트리거의 사용목적

데이터베이스 테이블을 생성하는 과정에서 참조 무결성과 데이터 무결성 등의 복잡한 제약 조건을 생성하는 경우
데이터베이스 테이블의 데이터에 생기는 작업을 감시, 보완
데이터베이스 테이블에 생기는 변화에 따라 필요한 다른 프로그램을 실행하는 경우
불필요한 트랜잭션을 금지하기 위해
컬럼의 값을 자동으로 생성되도록 하는 경우

 

FUNCTION: 값을 반환하는 명명된PL/SQL BLOCK으로 오라클 내장 함수와 같이 SQL 표현식의 일부로 사용하여 복잡한 SQL문을 간단한 형태로 사용 가능. 값을 반환하는 RETURN이 반드시 포함되며 반드시 하나의 값을 반환

내장 함수와 프로시저는 문법이나 특징이 거의 비슷하지만 차이점은 프로시저는 정해진 작업은 수행한 후 결과를 반환 할 수도 있고(OUT, IN OUT 모드 사용 시) 반환하지 않고 그냥 종료할 수도 있지만 함수는 정해진 작업을 수행한 후 결과를 돌려준다(RETURN)는 부분

RETURN의 데이터 타입: CHAR, DATE, NUMBER

 

CREATE [OR REPLACE] FUNCTION FUNCTION명
(매개변수 1 데이터타입, 매개변수2 데이터타입...)
RETURN 데이터타입 -- 함수가 반환할 데이터 타입 지정. 크기는 지정할 수 없다.
IS 
	지역변수 (선언)
BEGIN
	실행부
    RETURN 반환값;	--매개변수를 받아 특정 연산을 수행한 후 반환할 값 명시
    [EXCEPTION 예외 처리부]
END [함수이름];
/

 

PL/SQL 예외

컴파일 에러: PL/SQL 블록이 PARSE 되는 동안에 오타등으로 인하여 발생되는 에러

런타임 에러: PL/SQL 블록이 실행되는 동안에 발생되는 에러 (오라클에서는 예외(EXCEPTION)라고 함)

 

오라클의 예외 2가지

오라클 예외, 사용자에 의해 정의 되는 사용자 정의 예외(USER-DEFINED EXCEPTION)

 

-미리 정의되어 있는 오라클 예외들

NO_DATA_FOUND : PL/SQL SELECT문이 한건도 리턴하지 못했을 경우 발생

PROGRAM_ERROR : PL/SQL 이 내부적인 문제를 가지고 있는 경우

TOO_MANY_ROWS : PL.SQL SELECT 문이 두 건 이상의 행을 리턴했을 때

VALUE_ERROR : 산술, 변환, 절삭 또는 크기 제약에 에러가 생겼을 때 발생되는 예외

 

EXCEPTION
WHEN EXCEPTION1 [OR EXCEPTION2...] THEN
	STATEMENT1, 
    	STATEMENT2;
...
[WHEN EXEPTION3 [OR EXCEPTION4...] THEN
	STATEMENT3;
    	STATEMENT4;
 ...]