오라클에서는 자주 사용하는 PL/SQL 블록에 이름을 지정하고 생성해서 저장해 두었다가
필요할 때 호출해서 사용할 수 있도록 하는데 이를 서브 프로그램 또는 프로그램 단위라 부른다.
대표적으로 프로시저 (Procedure), 함수 (Function), 패키지 (Package), 트리거 (Trigger) 가 있으며 많이 사용하는 서브 프로그램들이니 잘 익혀두어야 한다.
익명 블록 서브 프로그램
이름이 지정되지 않은 PL/SQL 블록 이름이 지정된 PL/SQL 블록
매번 사용시마다 컴파일 최초 실행될 때 한번만 컴파일
데이터베이스에 저장되지 않음 데이터베이스에 저장됨
다른 응용프로그램에서 호출 불가 다른 응용프로그램에서 호출 가능
값을 반환하지 않음 함수일 경우 값을 반환
파라미터를 사용할 수 없음 파라미터를 사용할 수 있음
1. 프로시저
(1) 프로시저 생성 문법
CREATE [OR REPLACE] PROCEDURE procedure_name
[(
parameter1 [mode1] datatype1,
parameter1 [mode1] datatype2,
...
)]
IS | AS
PL/SQL Block ;
OR REPLACE
생성하고자 하는 프로시저가 기존에 동일 이름으로 존재할 경우, 기존의 내용을 현재의 내용으로 수정하는 옵션이다.
이 옵션은 해당 이름의 프로시저를 삭제한 후 다시 생성한다.
procedure_name
생성하고자 하는 프로시저명으로 스키마 내에서는 유일하게 명명되어야 한다.
parameter
프로시저를 생성할 때 호출 환경과 프로시저 간에 값을 주고 받을 때 사용되는 파라미터 (매개 변수) 로 모드 (IN, OUT, INOUT) 에 따라 역할이 다르게 수행된다.
파라미터를 선언할 때는 데이터형을 적고 데이터의 크기는 기록하지 않는다.
파라미터는 두 종류인데 프로시저를 생성할 때 선언부에 선언된 파라미터를 형식 파라미터 (Formal Parameter) 라고 하며, 프로시저를 실행할 때 형식 파라미터에 실제 값이나 변수를 할당/대응하는 때 사용하는 파라미터를 실행 파라미터 (Actual Parameter) 라고 부른다.
파라미터를 선언할 때 default 키워드를 사용하여 기본값을 부여할 수 있다.
mode
모드는 매개 변수의 역할을 결정짓는 것으로 3가지 종류가 있다.
in 사용자로부터 값을 입력받아 프로시저로 전달해주는 역할 (기본값이므로 생략 가능)
out 프로시저에서 호출환경 (sql plus 등) 으로 값을 전달하는 역할을 하며 이 모드로 설정된 매개 변수는 프로시저 내에서는 읽을 수 없으며,
값을 저장하기만 하는 지역 변수처럼 사용된다.
또 호출 환경에서는 이 매개 변수로부터 값을 전달 받기 위해 환경 변수가 선언되어 있어야 한다.
inout 설정된 매개 변수는 호출 환경과 프로시저 간에 값을 주고 받는 지역변수로 사용되며 읽기 쓰기 모두 가능하다.
in out inout
기본모드 명시적으로 지정해야 함 명시적으로 지정해야 함
값이 서브프로그램에 전달됨 값이 호출 환경에 반환됨 값이 서브 프로그램에도 전달되고 호출 환경에도 반환됨
형식 파라미터가 상수로 동작 초기화되지 않은 변수 초기화된 변수
실제 파라미터가 리터럴, 표현식, 변수만 사용 가능 변수만 사용 가능
상수 또는 초기화된 변수가
될 수 있음
기본값을 할당할 수 있음 기본값 할당 불가 기본값 할당 불가
생성된 프로시저의 내용을 확인할 때는 USER_SOURCE 딕셔너리를 활용하면 된다.
프로시저 생성 시 오타나 변수 미 선언 등의 에러가 발생할 경우 SHOW ERROR 명령어로 확인하면 된다.
(2) 프로시저 생성 및 사용 실습
실습1. 부서번호가 20번인 사람들의 job 을 'CLERK' 로 변경하는 프로시저
CREATE OR REPLACE PROCEDURE update_20
IS
BEGIN
UPDATE emp
SET job='CLERK'
WHERE deptno=20 ;
END ;
/
execute update_20 ;
실습2. 사번을 입력받아 급여를 인상하는 프로시저
CREATE OR REPLACE PROCEDURE up_sal
(vempno emp.empno%TYPE) -- IN이 기본값이기 때문에 생략가능
IS
BEGIN
UPDATE emp
SET sal = 5000
WHERE empno = vempno ;
END ;
/
EXEC up_sal(7902) ;
실습3. 사번을 입력받아 그 사원의 이름과 급여를 출력하는 프로시저
CREATE OR REPLACE PROCEDURE ename_sal
(vempno emp.empno%TYPE)
IS
vename emp.ename%TYPE ;
vsal emp.sal%TYPE ;
BEGIN
SELECT ename, sal
INTO vename, vsal
FROM emp
WHERE empno = vempno ;
DBMS_OUTPUT.PUT_LINE('사원명은 '||vename||' 입니다.') ;
DBMS_OUTPUT.PUT_LINE('사원명은 '||vsal||' 입니다.') ;
END ;
/
EXEC ename_sal (7902) ;
실습4. OUT 모드 파라미터 사용 예
CREATE OR REPLACE PROCEDURE info_prof
(
v_profno IN professor.profno%TYPE,
v_name OUT professor.name%TYPE,
v_pay OUT professor.pay%TYPE
)
IS
BEGIN
SELECT name, pay
INTO v_name, v_pay
FROM professor
WHERE profno = v_profno ;
END info_prof ;
/
위에서 작성한 프로시저를 호출해서 값을 가져오는 익명 블록 PL/SQL 문을 작성
DECLARE
v_name professor.name%TYPE ;
v_pay professor.pay%TYPE ;
BEGIN
info_prof(1001, v_name, v_pay) ;
DBMS_OUTPUT.PUT_LINE(v_name||' 교수의 급여는 '||v_pay||' 입니다.') ;
END ;
/
DECLARE
v_name professor.name%TYPE ;
v_pay professor.pay%TYPE ;
BEGIN
info_prof(1001, v_name, v_pay) ;
DBMS_OUTPUT.PUT_LINE('이름: '||v_name) ;
DBMS_OUTPUT.PUT_LINE('급여: '||v_pay) ;
END ;
/
VARIABLE name VARCHAR2(10)
VARIABLE pay NUMBER
EXEC info_prof(1001, :name, :pay) ;
PRINT name pay
DECLARE
v_name professor.name%TYPE ;
v_pay professor.pay%TYPE ;
BEGIN
info_prof(
v_profno => 1001,
v_name => v_name,
v_pay => v_pay
) ;
DBMS_OUTPUT.PUT_LINE('이름: '||v_name) ;
DBMS_OUTPUT.PUT_LINE('급여: '||v_pay) ;
END ;
/
혼합
DECLARE
v_name professor.name%TYPE ;
v_pay professor.pay%TYPE ;
BEGIN
info_prof(
1001,
v_name => v_name,
v_pay => v_pay
) ;
DBMS_OUTPUT.PUT_LINE('이름: '||v_name) ;
DBMS_OUTPUT.PUT_LINE('급여: '||v_pay) ;
END ;
/
실제5. 생성된 프로시저의 내용을 확인한다.
DESC USER_SOURCE ;
SELECT text
FROM user_source
WHERE name = 'INFO_PROF' ;
연습문제1. 사용자로부터 대출금액, 이자율, 대출기간을 입력받아서 출력
상환방식은 원리금 균등상환 방식
필요정보 입력받기 대출금액: 100만원
대출금리: 5%
대출기간: 1년
총 납입기한 출력 총 납입금액 출력 총 납입원금 출력 총 납입이자 출력
회차별 세부 상환 계획 출력
연습문제2. 사용자에게 문장을 입력받은 후 그 문장에서 특정 단어가 몇 회 사용되었는지 출력하는 프로시저 작성
한글
select ceil((length(str) - length(replace(str,'&찾을글자'))) / length('aaaa')) cnt
from (select '&입력글자: ' str from dual)
영어
select floor((length(str) - length(replace(str,'&find_string'))) / length('aaaa')) cnt
from (select '&input_string: ' str from dual)
2. FUNCTION (내장 함수)
내장 함수와 프로시저의 차이점은 프로시저는 정해진 작업을 수행한 후 결과를 반환할 수도 있고 (OUT, INOUT 모드 사용시) 반환하지 않고 그냥 종료할 수도 있지만 함수는 정해진 작업을 수행한 후 결과를 돌려준다 (RETURN) 는 부분만 다르다.
작업을 수행한 후 쿼리를 수행한 화면에 결과를 보여주는 것과 같은 의미이다.
CREATE FUNCTION, DROP FUNCTION, ALTER FUNCTION, SHOW ERROR 명령 사용 가능
(1) 문법
CREATE [OR REPLACE] FUNCTION function_name
[(
parameter1 [mode1] datatype1,
parameter2 [mode2] datatype2,
...
)]
RETURN datatype
IS | AS
PL/SQL Block ;
RETURN datatype
이 부분이 프로시저와 다르다. 함수명으로 리턴할 데이터의 형을 선언하는 절이다.
PL/SQL 함수 블록 내에서는 반드시 RETURN 문을 포함해야 하며, 그 때 리턴되는 데이터는 이 부분에서 선언한 데이터형과 일치되어야 한다.
이렇게 생성된 함수는 아래와 같은 곳에서 사용 가능
QUERY의 SELECT 리스트 또는 절
WHERE 및 HAVING 절의 조건식
QUERY의 CONNECT BY, START WITH, ORDER BY 및 GROUP BY 절
INSERT 문의 VALUES 절
UPDATE 문의 SET 절
(2) 함수 생성 및 사용 예제
예제1. 부서번호를 입력받아 최고 급여액을 출력하는 함수
CREATE OR REPLACE FUNCTION s_max_sal
(s_deptno emp.deptno%TYPE)
RETURN NUMBER
IS max_sal emp.sal%TYPE ;
BEGIN
SELECT MAX(sal)
INTO max_sal
FROM emp
WHERE deptno = s_deptno ;
RETURN max_sal ;
END ;
/
SELECT s_max_sal(10)
FROM dual ;
SELECT s_max_sal(20)
FROM dual ;
예제2. 부서번호를 입력받은 후 해당 부서의 인원수를 구해주는 함수
CREATE OR REPLACE FUNCTION count_mem
(count NUMBER)
RETURN NUMBER
IS max_count NUMBER ;
BEGIN
SELECT count(*)
INTO max_count
FROM emp
WHERE deptno = count ;
RETURN max_count ;
END ;
/
SELECT DISTINCT deptno, count_mem(deptno)
FROM emp ;
예3. 부서번호를 입력 받아 부서별로 평균 급여를 구해주는 함수
CREATE OR REPLACE FUNCTION avg_sal
(s_deptno emp.deptno%TYPE)
RETURN NUMBER
IS avg_sal NUMBER ;
BEGIN
SELECT ROUND(AVG(sal),2)
INTO avg_sal
FROM emp
WHERE deptno = s_deptno ;
RETURN avg_sal ;
END ;
/
SELECT DISTINCT deptno, avg_sal(deptno)
FROM emp ;
예4. 사원번호를 입력받아 해당 사원의 부서명을 알려주는 함수
CREATE OR REPLACE FUNCTION f_dname
(v_empno IN emp.empno%TYPE)
RETURN VARCHAR2
IS v_dname dept.dname%TYPE ;
BEGIN
SELECT dname
INTO v_dname
FROM dept
WHERE deptno = (
SELECT deptno
FROM emp
WHERE empno = v_empno
) ;
RETURN v_dname ;
END ;
/
SELECT ename, deptno, F_DNAME(empno) "dname"
FROM emp ;
예5. 생성된 함수 조회하기
SELECT text
FROM user_source
WHERE type = 'FUNCTION'
AND name = 'S_MAX_SAL' ;
3. ORACLE PACKAGE (패키지)
패키지는 연관성이 높은 함수나 프로시저를 하나의 그룹으로 묶어두는 개념이다.
(1) PACKAGE 구조
패키지는 두 부분으로 나누어지는데, 하나는 패키지 선언부 (Specification), 하나는 몸체부 (Body) 이다.
1) 패키지 선언부 생성
문법
CREATE [OR REPLACE] PACKAGE package_name
IS | AS
Public type and item declarations
Subprogram specifications
END package_name ;
Package_name
생성하고자 하는 패키지명으로 스키마 내에서는 유일한 이름이어야 한다.
패키지 선언부와 패키지 몸체부의 패키지 명은 동일해야 한다.
Public type and item declarations
변수, 상수, 명시적 커서, 사용자 정의 예외, pragma 등을 선언한다.
이들은 모두 PUBLIC 이란 특징을 가진다.
Subprogram specifications
PL/SQL 서브 프로그램을 선언하는 부분이다.
선언할 때에는형식 매개변수를 포함한 헤더만을 기술해준다.
2) 패키지 몸체부 (Package Body) 생성
문법
CREATE [OR REPLACE] PACKAGE BODY
package_name
IS | AS
Private type and item declarations
Subprogram bodies
END package_name ;
Package_name
생성하고자하는 패키지명으로 패키지 선언부와 동일해야 한다.
Private type and item declarations
변수, 상수, 명시적 커서, 사용자 정의 예외, pragma 등을 선언한다.
Subprogram bodies
실제 작동할 서브 프로그램 (프로시저, 함수 등) 을 기록하는 부분이다.
참조되는 변수든 서브 프로그램이든 참조하는 서브 프로그램보다 먼저 정의 되어야 한다.
일반적으로 Public 의 서브 프로그램은 마지막 부분에 정의되어야 한다.
(2) 패키지 실행
생성된 패키지 오브젝트에 대한 실행 권한을 가진 사용자만이 패키지를 호출하여 실행할 수 있다.
(3) 패키지 삭제
패키지를 삭제할 때에는 패키지 선언부와 패키지 몸체부를 모두 삭제할 수도 있고 패키지 몸체부만 삭제할 수도 있다.
DROP PACKAGE package_name ;
DROP PACKAGE BODY package_name ;
(4) 패키지 사용 예
emp 테이블에서 총 급여합계와 평균급여를 구하는 package
Package 선언부
CREATE OR REPLACE PACKAGE emp_total
AS
PROCEDURE emp_sum ;
PROCEDURE emp_avg ;
END emp_total ;
/
Package 몸체부
CREATE OR REPLACE PACKAGE BODY emp_total AS
-- 급여합계 프로시저
PROCEDURE emp_sum
IS
CURSOR emp_total_sum IS
SELECT COUNT(*), SUM(NVL(sal, 0))
FROM emp ;
total_num NUMBER ;
total_sum NUMBER ;
BEGIN
OPEN emp_total_sum ;
FETCH emp_total_sum INTO total_num, total_sum ;
DBMS_OUTPUT.PUT_LINE ('총인원수: '||total_num||' , 급여합계: '||total_sum) ;
CLOSE emp_total_sum ;
END emp_sum ;
-- 급여평균 프로시저
PROCEDURE emp_avg
IS
CURSOR emp_total_avg IS
SELECT COUNT(*), AVG(NVL(SAL, 0))
FROM emp ;
total_num NUMBER ;
total_avg NUMBER ;
BEGIN
OPEN emp_total_avg ;
FETCH emp_total_avg INTO total_num, total_avg ;
DBMS_OUTPUT.PUT_LINE ('총인원수: '||total_num||', 급여평균: '||total_avg) ;
CLOSE emp_total_avg ;
END emp_avg ;
END emp_total ;
/
패키지 생성 완료
SET SERVEROUTPUT ON
EXEC emp_total.emp_sum ;
위 패키지 실행결과가 맞는지 SQL 을 직접 수행해서 검증
SELECT COUNT(*), SUM(NVL(sal, 0))
FROM emp ;
EXEC EMP_total.emp_avg ;
예제2. member 테이블에 등록되어 있는 회원들의 성별, 아이디, 비밀번호를 검색해주는 패키지
- 사용자 이름을 입력받은 후 성별을 구분하는 pro_gender 프로시저
- 사용자 이름과 주민번호를 입력받아 회원의 아이디를 찾아주는 pro_find_id 프로시저
- 사용자 아이디와 연관어를 입력 받아 회원의 비밀번호를 알려주는 pro_find_pw 프로시저
먼저 실습에 사용될 member 테이블의 내용을 확인하겠습니다.
(실습용 데이터 스크립트 test_data.sql 을 실행시키면 자동으로 만들어진다.
SELECT *
FROM member ;
패키지 선언부 생성
CREATE OR REPLACE PACKAGE pkg_member
IS
PROCEDURE pro_gender (v_name in member.name%TYPE) ;
PROCEDURE pro_find_id (v_name in member.name%TYPE, v_jumin2 in member.jumin%TYPE) ;
PROCEDURE pro_find_pw (v_id in member.id%TYPE, v_an in member.an_key_dap%TYPE) ;
END pkg_member ;
/
패키지 몸체부 생성
CREATE OR REPLACE PACKAGE BODY pkg_member
AS
-- 성별 프로시저 시작
PROCEDURE pro_gender (v_name in member.name%TYPE)
IS
v_name2 member.name%TYPE ;
v_gender char(4) ;
v_count number := 0 ;
exception_noname exception ;
BEGIN
SELECT COUNT(*)
INTO v_count
FROM member
WHERE name = v_name ;
IF v_count = 0 THEN
RAISE EXCEPTION_NONAME ;
ELSE
SELECT name, CASE WHEN SUBSTR(jumin, 7, 1) IN (1, 3)
THEN '남자'
ELSE '여자'
END
INTO v_name2, v_gender
FROM member
WHERE name = v_name ;
DBMS_OUTPUT.PUT_LINE (v_name2||' 님의 성별은 '||v_gender||' 입니다') ;
END IF ;
EXCEPTION
WHEN EXCEPTION_NONAME
THEN RAISE_APPLICATION_ERROR (-20001, '그런 이름은 없습니다.' ) ;
END pro_gender ;
-- 성별프로시저 종료
-- 아이디 찾기 프로시저 시작
PROCEDURE pro_find_id
(v_name in member.name%TYPE, v_jumin2 in member.jumin%TYPE)
IS
v_count number := 0 ;
v_count2 number := 0 ;
v_name2 member.name%TYPE ;
v_id2 member.id%TYPE ;
exception_noname exception ;
exception_nojumin exception ;
BEGIN
SELECT COUNT(*)
INTO v_count
FROM member
WHERE name = v_name ;
IF v_count = 0 THEN
RAISE EXCEPTION_NONAME ;
ELSE
SELECT COUNT(*)
INTO v_count2
FROM member
WHERE jumin=v_jumin2 ;
IF v_count2 = 0 THEN
RAISE EXCEPTION_NOJUMIN ;
ELSE
SELECT name, id
INTO v_name2, v_id2
FROM member
WHERE name = v_name ;
END IF ;
END IF ;
DBMS_OUTPUT.PUT_LINE (v_name2||' 님의 아이디는 '||v_id2||' 입니다') ;
EXCEPTION
WHEN EXCEPTION_NONAME
THEN RAISE_APPLICATION_ERROR (-20001, '입력하신 이름은 없습니다') ;
WHEN EXCEPTION_NOJUMIN
THEN RAISE_APPLICATION_ERROR(-20002, '입력하신 주민번호는 없습니다.') ;
END PRO_FIND_ID ;
-- 아이디 찾기 프로시저 종료
-- 비밀번호 찾기 프로시저 시작
PROCEDURE pro_find_pw
(v_id in member.id%TYPE, v_an in member.an_key_dap%TYPE)
IS
v_count number := 0 ;
v_id2 member.id%TYPE ;
v_an_dap member.an_key_dap%TYPE ;
v_pw member.passwd%TYPE ;
exception_noid exception ;
exception_noan exception ;
BEGIN
SELECT COUNT(*)
INTO v_count
FROM member
WHERE id = v_id ;
IF v_count = 0 THEN
RAISE EXCEPTION_NOID ;
ELSE
SELECT an_key_dap
INTO v_an_dap
FROM member
WHERE id = v_id ;
IF v_an_dap = v_an THEN
SELECT id, passwd
INTO v_id2, v_pw
FROM member
WHERE id = v_id ;
ELSE
RAISE EXCEPTION_NOAN ;
END IF ;
END IF ;
DBMS_OUTPUT.PUT_LINE('입력하신 '||v_id||' 의 비밀번호는 '||v_pw||' 입니다.') ;
EXCEPTION
WHEN EXCEPTION_NOID
THEN RAISE_APPLICATION_ERROR(-20003, '입력하신 아이디는 없습니다.') ;
WHEN EXCEPTION_NOAN
THEN RAISE_APPLICATION_ERROR(-20004, '입력하신 연상단어가 틀립니다.') ;
END pro_find_pw ;
END pkg_member ;
/
생성된 패키지를 테스트 합니다.
set line 200
col no for 99999
col name for a10
col jumin for a13
col passwd for a5
col id for a8
col an_key for a10
col an_key_dap for a10
select * from member ;
pro_gender 프로시저 테스트
SET SERVEROUTPUT ON ;
EXEC pkg_member.pro_gender('서유딩') ;
EXEC pkg_member.pro_gender('김초딩') ;
등록되지 않은 이름 입력 후 에러 확인
EXEC pkg_member.pro_gender('유관순') ;
pro_find_id 프로시저 테스트
EXEC pkg_member.pro_find_id('서유딩', '7510231234567') ;
주민번호 잘못 입력한 후 에러 발생 확인
EXEC pkg_member.pro_find_id('서유딩', '7510231234568') ;
이름 잘못 입력한 후 에러 발생 확인
EXEC pkg_member.pro_find_id('홍길동', '7510231234568') ;
pro_find_pw 프로시저 테스트
EXEC pkg_member.pro_find_pw('simson', '김초딩') ;
아이디 틀리게 넣고 에러 확인
EXEC pkg_member.pro_find_pw('aaa', '서유딩') ;
아이디 맞고 연상단어 잘못 넣고 에러 확인
EXEC pkg_member.pro_find_pw('simson', '서유딩') ;
(5) 생성된 패키지 조회하기
선언부 조회하기
SELECT text
FROM user_source
WHERE TYPE = 'PACKAGE' ;
몸체부 조회하기
SELECT text
FROM user_source
WHERE TYPE = 'PACKAGE BODY' ;
4. TRIGGER(트리거)
(1) 개요
서브 프로그램 단위의 하나인 트리거는 테이블, 뷰, 스키마 또는 데이터베이스에 관련된 PL/SQL 블록 (또는 프로시저) 으로 관련된 특정사건 (EVENT) 이 발생될 때마다 묵시적 (자동) 으로 해당 PL/SQL 블록이 실행된다.
트리거는 데이터베이스 내에 오브젝트로서 저장되어 관리된다.
트리거 자체는 사용자가 지정해서 실행할 수 없으며, 오직 트리거 생성시 정의한 이벤트에 의해서만 묵시적인 자동실행이 이루어진다.
트리거를 생성하려면 CREATE TRIGGER, 수정하려면 ALTER TRIGGER, 삭제하려면 DROP TRIGGER 의 권한이 필요하다.
또한 DATABASE 전체의 트리거 조작은 ADMINISTRATOR DATABASE TRIGGER 시스템 권한이 필요하다.
트리거에 대한 정보는 USER_OBJECTS, USER_TRIGGERS, USER_ERRORS 딕셔너리들을 조회하면 알 수 있다.
트리거를 이루는 트리몸체에 TCL 명령 (COMMIT, ROLLBACK, SAVEPOINT) 명령은 포함될 수 없다.
(2) 주요 트리거 유형
1) 단순 DML 트리거
BEFORE TRIGGER
테이블에서 DML 이벤트를 트리거 하기 전에 트리거 본문을 실행
AFTER TRIGGER
테이블에서 DML 이벤트를 트리거 한 후에 트리거 본문을 실행
INSTEAD OF TRIGGER
트리거 문 대신 트리거 본문을 실행하며, 다른 방법으로는 수정이 불가능한 뷰에 사용된다.
뷰란 가상 테이블이므로 뷰를 통해 원본 테이블에 데이터를 변경하는 것을 권장하지 않습니다.
불가능한 것은 아니지만 뷰를 만든 것 자체가 원본 테이블에 직접 엑세스를 막기 위함이므로 뷰를 통한 데이터 변경은 권장사항이 아닙니다.
그러나 INSTEAD OF TRIGGER 를 사용하면 서버 프로세스가 TRIGGER 상에 기술된 쿼리를 직접 수행해서 원본 테이블의 데이터를 변경하게 해 줍니다.
DML TRIGGER 는 다시 문장 TRIGGER 와 행 TRIGGER 로 나누어집니다.
문장 TRIGGER 는 영향을 받는 행이 전혀 없더라도 TRIGGER 가 한번은 실행된다.
문장 트리거는 트리거 작업이 영향을 받는 행의 데이터 또는 트리거 이벤트 자체에서 제공하는 데이터에 종속되지 않은 경우에 유용하다.
행 트리거는 테이블이 트리거 이벤트의 영향을 받을 때마다 실행되고, 트리거 이벤트의 영향을 받는 행이 없을 경우에는 실행되지 않는다.
행 트리거는 영향을 받는 행의 데이터나 트리거 이벤트 자체에서 제공하는 데이터에 트리거 작업이 종속될 경우에 유용하다.
행 트리거로 생성하려면 FOR EACH ROW 라는 구절을 사용하면 된다.
행 트리거가 실행될 때 PL/SQL 런타임 엔진은 두 개의 데이터 구조를 생성하고 채운다.
OLD: 트리거가 처리한 레코드의 원래 값을 저장한다.
NEW: 새 값을 포함한다.
위 두가지 값을 사용하여 변경 전과 변경 후의 데이터를 조작할 수 있다.
사용할 때는 반드시 위 값 앞에 :(콜론) 을 붙여서 사용해야 한다.
위 두 값은 행레벨 트리거에서만 사용 가능하다.
2) 혼합 트리거
* 주로 사용되는 시점
실행되는 문장 앞에
실행되는 문장이 영향을 주는 각 행 앞에
실행되는 문장이 영향을 주는 각 행 뒤에
실행되는 문장 뒤에
* 혼합 트리거 생성시 주의사항
1. 혼합 트리거는 DML 트리거 여야 하며 테이블이나 뷰에 정의해야 한다.
2. 혼합 트리거의 본문은 PL/SQL 에서 작성한 혼합 트리거 블록이어야 한다.
3. 혼합 트리거 본문 에는 초기화 불록이 포함될 수 없으므로 예외 섹션이 있을 수 없다.
4. 한 섹션에서 발생하는 예외는 해당 섹션에서 처리되어야 한다.
다른 섹션에서 처리하도록 권한을 이전할 수 없다.
5. :OLD 및 :NEW는 선언, BEFORE STATEMENT 또는 AFTER STATEMENT 섹션에 나타날 수 없다.
6. BEFORE EACH ROW 섹션만 :NEW 값을 변경할 수 있따.
7. FOLLOWS 절을 사용하지 않으면 혼합 트리거의 실행순서가 일정하지 않는다.
3) DML이 아닌 트리거
DDL 이벤트 트리거
DML 트리거와 거의 동일하지만 트리거를 활용하여 DDL 작업만 한다.
데이터베이스 이벤트 트리거
데이터베이스 내에서 생기는 일들을 관리하기 위해서 생성하는 트리거.
사용자 관련 이벤트가 있고 시스템 관련 이벤트가 있으며 아래와 같다.
유저이벤트 트리거
사용자가 발생시키는 작업에 트리거를 생성
CREATE, ALTER, DROP
로그온 또는 로그오프
데이터베이스 또는 시스템 이벤트 트리거
데이터베이스 전체에 영향을 주는 작업에 트리거를 생성한다.
데이터베이스 종료 또는 시작
발생한 특정 오류(또는 임의의 오류)
(3) 트리거 구조
트리거는 트리거가 실행되는 시점, 트리거를 실행시키는 사건, 트리거와 관련된 테이블/뷰/스키마/데이터베이스 그리고 트리거 몸체부로 구성된다.
트리거는 트리거를 실행시키는 사건에 다라 구분되는데, 하나는 특정 테이블/뷰에 대한 DML 문과 관련된 트리거이며 다른 하나는 데이터베이스 또는 스키마 레벨에서의 시스템 사건과 관련된 트리거이다.
(4) 트리거 생성
문법
CREATE OR REPLACE TRIGGER trigger_name
timing
event1 [OR evnet2 OR event3 ...]
ON {table_name|view_name|SCHEMA|DATABASE}
[REFERENCING OLD AS old | NEW AS new]
[FOR EACH ROW [WHEN (condition)]]
trigger_body
OR REPLACE
생성하고자 하는 패키지가 기존에 동일명으로 존재할 경우, 기존의 내용을 현재의 내용으로 수정하는 옵션
trigger_name
생성되는 TRIGGER의 이름으로 같은 도메인 내에서는 중복되어서는 안된다.
timing
트리거가 실행되는 시점을 지정하는 것으로 이벤트 발생 전과 후를 의미하는 BEFORE 와 AFTER 가 있으며, 트리거가 특정 뷰에 대한 DML 일 경우에 이 timing 부분에 INSTEAD OF 를 사용한다.
event
트리거를 실행시키는 사건으로 테이블/뷰에 관련된 DML_EVENT 와 SCHEMA/DATABASE 에 관련된 DDL_EVENT 와 DATABASE_EVENT 로 나누어 진다.
ON
트리거가 발생되는 레벨 또는 대상을 지정하는 절로 트리거의 대상인 테이블/뷰 이름을 기술하거나 트리거의 레벨인 스키마 또는 데이터베이스를 지정하는 부분이다.
REFERENCING
테이블/뷰의 행 트리거와 관련된 키워드로, 처리되는 각각의 행에 대해 변경 전의 값과 변경 후의 값을 참조할 수 있도록 참조할 수 있는 이름을 재명명할 수 있는 절로 디폴트 값은 OLD 와 NEW 이다.
FOR EACH ROW
테이블/뷰의 트리거를 행트리거로 명시하는 절로, 뷰의 INSTEAD OF 에 대해서는 기본적으로 행 트리거로서 정의되므로 생략할 수 있다.
WHEN
테이블/뷰의 행 트리거의 각 행에 대해 제약을 주는 절이다. 조건은 반드시 괄호로 감싼다.
trigger_body
트리거에 의해 실행될 부분을 정의하는 곳으로, Anonymous PL/SQL Block 구조가 올 수 있으며 또한 프로시저(PL/SQL, C, JAVA)를 호출할 수도 있다.
(5) 트리거 관리
1) 활성화/비활성화 하기
ALTER TRIGGER trigger_name DISABLE | ENABLE ;
2) 특정 테이블에 속한 트리거의 활성화/비활성화
ALTER TABLE table_name DISABLE | ENABLE ALL TRIGGERS ;
3) 트리거 수정 후 다시 컴파일 하기
ALTER TRIGEER trigger_name COMPILE ;
4) 트리거 삭제
DROP TRIGGER trigger_name ;
5) 트리거 조회하기
USER_TRIGGERS 를 조회하면 된다.
6) 트리거 관련 권한들
* 스키마에서 트리거를 생성, 변경 및 삭제할 수 있는 권한
- GRANT CREATE TRIGGER TO SCOTT ;
- GRANT ALTER ANY TRIGGER TO SCOTT ;
- GRANT DROP ANY TRIGGER TO SCOTT ;
* 데이터베이스에서 트리거를 생성할 수 있는 권한
- GRANT ADMINISTER DATABASE TRIGGER TO SCOTT ;
* EXECUTE 권한 (트리거가 실행하는 스키마에 포함되지 않은 객체를 참조하는 경우)
(6) 트리거 예제
예1. 테이블에 데이터를 입력할 수 있는 시간 지정하기 (문장 레벨 트리거를 사용한다.)
연습용 테이블 생성
CREATE TABLE t_order
(
no number,
ord_code varchar2(10),
ord_date date
) ;
테이블에 데이터를 입력할 때 18:40 ~ 18:50 에만 입력허용
CREATE OR REPLACE TRIGGER t_order
BEFORE INSERT ON t_order --INSERT 되기 전에 실행하라
BEGIN
IF (TO_CHAR(SYSDATE, 'HH24:MI') NOT BETWEEN '18:40' AND '18:50' ) THEN
RAISE_APPLICATION_ERROR (-20100, '허용시간 아닙니다') ;
END IF ;
END ;
/
입력되는 경우
SELECT SYSDATE FROM DUAL ;
SELECT TO_CHAR(SYSDATE, 'HH24:MI') FROM DUAL ;
INSERT INTO t_order
VALUES (1, 'C100', SYSDATE) ;
시간이 지나서 입력 안되는 경우
SELECT SYSDATE FROM DUAL ;
SELECT TO_CHAR(SYSDATE, 'HH24:MI') FROM DUAL ;
INSERT INTO t_order
VALUES (2, 'C200', SYSDATE) ;
SQL 오류: ORA-20100: 허용시간 아닙니다
CREATE OR REPLACE TRIGGER t_order2
BEFORE INSERT ON t_order
FOR EACH ROW
BEGIN
IF (:NEW.ord_code) NOT IN ('C100') THEN
RAISE_APPLICATION_ERROR (-20100, '제품코드가 틀립니다.') ;
END IF ;
END ;
/
INSERT INTO t_order
VALUES (2, 'C100', SYSDATE) ;
앞에서 생성한 문장레벨 트리거로 인해 입력이 안된다. 삭제한다.
DROP TRIGGER T_ORDER ;
INSERT INTO t_order
VALUES (2, 'C100', SYSDATE) ;
정상입력
INSERT INTO t_order
VALUES (2, 'C200', SYSDATE) ;
SQL 오류: ORA-20100: 제품코드가 틀립니다.
입력불가
예제3. 트리거의 작동 조건을 WHEN 절로 정의한다.
ORD_CODE 가 'C500' 인 제품에 대해서만 19:30분부터 19:35분까지만 입력을 허용하는 트리거다.
당연히 다른 제품 코드는 시간에 관계없이 정상적으로 입력된다.
CREATE OR REPLACE TRIGGER t_order3
BEFORE INSERT ON t_order
FOR EACH ROW
WHEN (NEW.ord_code = 'C500')
BEGIN
IF (TO_CHAR(SYSDATE, 'HH24:MI') NOT BETWEEN '19:30' AND '19:35') THEN
RAISE_APPLICATION_ERROR(-20300, 'C500 제품의 입력허용시간이 아닙니다') ;
END IF ;
END ;
/
SELECT SYSDATE FROM DUAL ;
INSERT INTO t_order
VALUES (1, 'C500', SYSDATE) ;
SQL 오류: ORA-20300: C500 제품의 입력허용시간이 아닙니다
입력안된다.
C100만 입력 가능하게 했던 트리거 삭제
DROP TRIGGER t_order2 ;
다른 제품코드로 입력
INSERT INTO t_order
VALUES (2, 'C700', SYSDATE) ;
잘된다.
예제4. 기존테이블(t_test1)에 데이터가 업데이트될 때 기존 내용을 백업 테이블(t_test2)로 옮겨놓는 트리거를 생성한다.
CREATE TABLE t_test1
(
no number,
name varchar2(10)
) ;
CREATE TABLE t_test2
AS SELECT * FROM t_test1 ;
INSERT INTO t_test1
VALUES (1, 'AAA') ;
INSERT INTO t_test1
VALUES (2, 'BBB') ;
COMMIT ;
CREATE OR REPLACE TRIGGER t_move
BEFORE update ON t_test1
FOR EACH ROW
BEGIN
INSERT INTO t_test2 VALUES (:OLD.no, :OLD.name) ;
END ;
/
SELECT *
FROM t_test1 ;
1 AAA
2 BBB
SELECT *
FROM t_test2 ;
선택된 행 없음
UPDATE t_test1
SET no=2
WHERE name='AAA' ;
SELECT *
FROM t_test2 ;
1 AAA
예제5. 기존 테이블(t_test3) 의 데이터가 delete 될 때 기존 내용을 백업 테이블(t_test4) 로 이동시키며
이 때 백업 테이블에 삭제한 시간, 삭제 전 데이터를 모두 기록하는 트리거를 생성
CREATE TABLE t_test3
(
no number,
name varchar2(10)
) ;
CREATE TABLE t_test4
(
no number,
name varchar2(10),
time date
) ;
INSERT INTO t_test3 VALUES (1, 'AAA') ;
INSERT INTO t_test3 VALUES (2, 'BBB') ;
COMMIT ;
CREATE OR REPLACE TRIGGER d_tregger
AFTER delete ON t_test3
FOR EACH ROW
BEGIN
INSERT INTO t_test4 (no, name, time)
VALUES(:OLD.no, :OLD.name, SYSDATE) ;
END ;
/
SELECT * FROM t_test3 ;
1 AAA
2 BBB
SELECT * FROM t_test4 ;
선택된 행 없음
DELETE FROM t_test3 WHERE no=1 ;
SELECT * FROM t_test3 ;
2 BBB
SELECT * FROM t_test4 ;
1 AAA 13/11/20
예제4. 사용자의 로그온, 로그오프 정보를 테이블에 기록하는 트리거 생성
GRANT ADMINISTER DATABASE TRIGGER TO SCOTT ;
CREATE TABLE log_on
(
userid varchar2(20),
time date
) ;
CREATE OR REPLACE TRIGGER t_logon
AFTER LOGON ON database
BEGIN
INSERT INTO LOG_ON
VALUES(USER, SYSDATE) ;
END ;
/
다른 창에서 로그인 한 후 확인
SELECT userid, TO_CHAR(TIME, 'YYYY-MM-DD:HH24:MI:SS') "TIME" FROM log_on ;
SYS 2013-11-20:17:57:47
SCOTT 2013-11-20:17:57:50
CREATE TABLE log_off
(
userid varchar2(20),
time date
) ;
CREATE OR REPLACE TRIGGER t_logoff
BEFORE LOGOFF ON DATABASE
BEGIN
INSERT INTO log_off
VALUES(USER, SYSDATE) ;
END ;
/
다른 창에서 로그오프 한 후 확인
SELECT userid, TO_CHAR(TIME, 'YYYY-MM-DD:HH24:MI:SS') "TIME"
FROM log_off ;
SCOTT 2013-11-20:17:58:31
(7) 트리거 연습문제
[출처] 다양한 예제로 쉽게 배우는 오라클 SQL과 PL/SQL 서진수 저