PL/SQL 익명 블록과 서브 프로그램의 차이
PL/SQL 블록에 이름을 지정하고 생성해서 저장해 두었다가 필요할 경우에 호출ㅇ해서 사용할 수 있도록 하는데 이를 서브프로그램 또는 프로그램 단위라 부른다.
대표 적인 유형으로 프로시저(PROCEDURE), 함수(FUNCTION), 패키지(PACKAGE), 트리거(TRIGGER) 가 있다
1. PROCEDURE (프로시저)
특정 작업을 처리하는 이름있는 PL/SQL BLOCK
프로시저 생성 - create procedure
프로시저 삭제 - drop procedure
프로시저 수정 - alter procedure
PROCEDURE 생성 문법 :
CREATE [OR REPLACE] PROCEDURE PROCEDURE_name
[( parameter1 [mode1] datatype1,
parameter2 [mode2] datatype2,
...)]
IS |AS
- OR REPLACE : 생성하고자 하는 PROCEDURE 가 기존에 동일 이름으로 존재할 경우, 기존의 내용을 현재 내용으로 수정하는 옵션, 이 옵션은 해당 이름의 PROCEDURE를 삭제한 후 다시 생성
- PROCEDURE_name : 생성하고자 하는 PROCEDURE 명으로 스키마 내에서는 유일하게 명명 되어야 한다
- parameter : PROCEDURE 를 실행할 때 호출 환경과 PROCEDURE 간 값을 주고 받을 때 사용되는 파라미터(매개변수)로 모드(IN,OUT,IN OUT) 에 따라 역할이 다르게 수행
PROCEDURE 생성시 선언부에 선언된 파라미터를 형식 파라미터
PROCEDURE 실행시 형식 파라미터에 실제 값이나 변수를 할당/대응 하는 파라미터를 실행 파라미터
- mode : 매개변수의 역할을 결정짓는 것
- IN 모드 : 사용자로부터 값을 입력받아 PROCEDURE 로 전달해 주는 역할을 하는 모드 (기본값으로 생략가능)
- OUT 모드 : PROCEDURE 에서 호출환경(SQL PLUS, 다른 프로시저 등)으로 값을 전달하는 역할
이 모드로 설정된 매개변수는 프로시저 내에서는 읽을 수 없으며, 값을 저장하기만 하는 지역변수처럼 사용
호출 환경에서는 이 매개변수로부터 값을 전달받기 위해 환경변수가 선언되어 있어야 함
- IN OUT 모드 : 설정된 매개변수는 호출환경과 프로시저 간에 값을 주고 받는 지역변수로 사용되며 읽기,쓰기가능
생성된 PROCEDURE 의 내용을 확인할 때는 USER_SOURCE 딕셔너리를 활용
실습 1. 부서번호가 20번 인 사람들의 job 을 'CLERK' 으로 변경하는 PROCEDURE
SQL> CREATE OR REPLACE PROCEDURE update_20
2 IS
3 BEGIN
4 UPDATE emp
5 SET job = 'CLERK'
6 WHERE deptno=20;
7 END ;
8 /
PROCEDURE created
SQL>EXEC update_20; --프로시저를 실행하려면 execute(줄여서 exec) 명령어로 프로시저 이름을 적고 실행
실습 2. 사번을 입력 받아 급여를 인상하는 PROCEDURE
SCOTT>SELECT empno, ename, sal FROM emp WHERE empno=7902 ;
EMPNO ENAME SAL
------ ------ ----
7902 FORD 3000 <- 변경전 SAL 값이 3000
SCOTT>CREATE OR REPLACE PROCEDURE up_sal
2 (vempno IN emp.empno%TYPE) -- 입력값을 저장할 변수 vempno 를 선언, IN 모드 기본값이라 생략가능
3 IS
4 BEGIN
5 UPDATE emp SET sal=5000
6 WHERE empno=vempno ;
7 END ;
8 /
PROCEDURE created
SCOTT>exec up_sal(7902) ; --PROCEDURE 를 실행할 때 수정할 사원번호를 함께 입력
EMPNO ENAME SAL
------ ------ ----
7902 FROD 5000 <- 변경후 급여가 500으로 바뀜
※ DB로 가서 데이터를 PL/SQL로 가져올 때 사용하는 변수는 3번째 줄과 4번째줄 사이 IS 와 BEGIN 사이에 입력하고,
2번 줄의 변수는 사용자로부터 데이터를 입력 받아서 PL/SQL 로 가져오는 역할을 하는 변수
실습 3. 사번을 입력 받아 그 사원의 이름과 급여를 출력하는 PROCEDURE
SCOTT>CREATE OR REPLACE PROCEDURE ename_sal
2 (vempno emp.empno%TYPE)
3 IS
4 vename emp.ename%TYPE ;
5 vsal emp.sal%TYPE ;
6 BEGIN
7 SELECT ename, sal
8 INTO vename, vsal
9 FROM emp
10 WHERE empno=vempno ;
11 DBMS_OUTPUT.PUT_LINE('사원명은 '||vename||' 입니다') ;
12 DBMS_OUTPUT.PUT_LINE('급여는 '||vsal||' 입니다') ;
13 END ;
14 /
SCOTT>exec ename_sal(7902) ;
-> 사원명은 FOR 입니다
급여는 5000 입니다
실습 4. OUT 모드 파라미터 사용 예
SCOTT>CREATE OR REPLACE PROCEDURE info_prf
2 (v_profno IN professor.profno%TYPE,
3 v_name OUT professor.name%TYPE, --이름값을 저장할 변수
4 v_pay OUT professor.pay%TYPE ) --급여를 저장할 변수
5 IS
6 BEGIN
7 SELECT name, pay INTO v_name, v_pay
8 FROM professor
9 WHERE profno = v_profno ;
10 END info_prof ;
11 /
위에서 작성한 PROCEDURE 를 호출해서 값을 가져오는 익명 블록 PL/SQL 문을 작성
SCOTT>DECLARE
2 v_name professor.name%TYPE ;
3 v_pay professor.pay%TYPE ;
4 BEGIN
5 info_prof(1001,v_name,v_pay) ;
6 DBMS_OUTPUT.PUT_LINE(v_name||' 교수의 급여는 '||v_pay||'입니다') ;
7 END ;
8 /
-> 조인형 교수의 급여는 550 입니다
위 예에서 5번 줄을 보면 프로시저를 호출 할 때 값을 주는 것을 알 수 있습니다.
또는 아래와 같이 DBMS_OUTPUT.PUT_LINE 문장으로 사용도 가능
SCOTT>DECLARE
2 v_name professor.name%TYPE ;
3 v_pay professor.pay%TYPE ;
4 BEGIN
5 info_prof(1001,v_name,v_pay) ;
6 DBMS_OUTPUT.PUT_LINE('이 름:'||v_name) ;
7 DBMS_OUTPUT.PUT_LINE('급 여:'||v_pay) ;
8 END ;
9 /
-> 이 름 : 조인형
급 여 : 550
또 다른 방법으로는 별도의 변수를 선언해서 값을 수행해서 받은 후 출력할 수 도 있습니다.
SCOTT>VARIABLE name VARCHAR2(10)
SCOTT>VARIABLE pay NUMBER
SCOTT>EXEC info_prof(1001, :name, :pay) ;
SCOTT>PRINT name pay
NAME
-----------
조인형
PAY
-----------
550
2. FUNCTION (함수)
함수 생성 : CREATE FUNCTION
함수 삭제 : DROP FUNCTION
함수 수정 : ALTER FUNCTION
에러 발생시 : SHOW ERRORS 확인
함수 예제 1. 부서번호를 입력 받아 최고 급여액을 출력하는 함수
SQL>CREATE OR REPLACE FUNCTION f_max_sal
2 (v_deptno emp.deptno%TYPE)
3 RETURN NUMBER
4 IS
5 max_sal emp.sal%TYPE ;
6 BEGIN
7 SELECT max(sal) INTO max_sal
8 FROM emp
9 WHERE deptno = v_deptno ;
10 RETURN max_sal ; -- 이부분의 데이터 형이 위 3번 줄의 형과 같아야 함
11 END ;
12 /
SQL>SELECT f_max_sal(10) FROM dual ;
F_MAX_SAL(10)
-------------
5000
SQL>SELECT f_max_sal(20) FROM dual ;
F_MAX_SAL(20)
-------------
3000
함수 예제 2. 부서번호를 입력 받은 후 해당 부서의 인원수를 구해주는 함수
SQL>CREATE OR REPLACE FUNCTION count_mem
2 (v_deptno NUMBER)
3 RETURN NUMBER
4 IS
5 total_count NUMBER
6 BEGIN
7 SELECT count(*) INTO total_count
8 FROM emp
9 WHERE deptno = v_deptno ;
10 RETURN total_count ; -- 이 부분의 데이터 형이 위 3번 줄 데이터 형과 같아야 함
11 END;
12 /
SQL> SELECT DISTINCT deptno, COUNT_MEM(deptno)
2 FROM emp ;
DEPTNO COUNT_MEM(DEPTNO)
------- -------------------
30 6
20 5
10 3
함수 예제 3. 부서번호를 입력 받아 부서별로 평균 급여를 구해주는 함수
SQL>CREATE OR REPLACE FUNCTION avg_sal
2 (v_deptno emp.deptno%TYPE)
3 RETURN NUMBER
4 IS
5 avg_sal NUMBER ;
6 BEGIN
7 SELECT ROUND(AVG(sal),2) INTO avg_sal
8 FROM emp
9 WHERE deptno = v_deptno ;
10 RETURN avg_sal ;
11 END ;
12 /
SQL> SELECT DISTINCT deptno, AVG_SAL(deptno)
2 FROM emp ;
DEPTNO AVG_SAL(DEPTNO)
------- ----------------
30 1566.67
10 2916.67
20 2175
함수 예제 4. 사원번호를 입력 받아 해당 사원의 부서명을 알려주는 함수
SQL>CREATE OR REPLACE FUNCTION f_dname
2 (v_empno IN emp.empno%TYPE)
3 RETURN varchar2
4 IS
5 v_dname dept.dname%TYPE ;
6 BEGIN
7 SELECT dname INTO v_dname
8 FROM dept
9 WHERE deptno = ( SELECT deptno
FROM emp
WHERE empno = v_empno) ;
10 RETURN v_dname ;
11 END ;
12 /
SCOTT> SELECT ename, deptno, F_DNAME(empno) "DNAME"
2 FROM emp ;
ENAME DEPTNO DNAME
------ ------- ------------
홍길동 10 ACCOUNTING
일지매 30 SALES
SMITH 20 RESEARCH
ALLEN 30 SALES
.......
함수 예제 5. 생성된 함수 조회하기
SCOTT>SELECT text
2 FROM user_source
3 WHERE type='FUNCTION'
4 AND name = 'F_MAX_SAL' ;
TEXT
-------------------------------------------
function f_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 ;
3. ORACLE PACKAGE (패키지)
연관성이 높은 함수나 PROCEDURE 를 하나의 그룹으로 묶어서 관리하고 사용하는 개념
PACKAGE 는 PACKAGE 선언부(Spec)와 PACKAGE 몸체부(body) 로 구성
- PACKAGE 선언부 : 해당 패키지에 사용될 함수나 프로시저, 변수 등에 대한 정의를 선언
- PACKAGE 몸체부 : 선언부에서 선언된 함수나 ㅡㅍ로시저 등이 실제 CODE로 구현되는 부분
만약 생성된 PACKAGE의 선언부가 변경되었다면 무조건 PACKAGE 몸체부는 재 생성해야 하며, 패키지를 참조(호출)하는 서브 프로그램들도 재 번역(recompile)해야한다. 반대로 패키지 몸체부만 변경되는 경우라면 몸체부만 재생성하면 된다.
1. 패키지 실행 하기
생성된 패키지 오브젝트에 대한 실행 권한을 가진 사용자만이 패키지를 호출하여 실행 할 수 있다
2. 패키지 삭제
패키지 선언부와 몸체부 모두 삭제 : DROP PACKAGE PACKAGE_name ;
패키지 몸체부만 삭제 : DROP PACKAGE BODY PACKAGE_name ;
사용 예 1. Emp table 에서 총 급여 합계와 평균 급여를 구하는 emp_sal_mgr PACKAGE 입니다.
패키지 선언부
SQL>CREATE OR REPLACE PACKAGE emp_sal_mgr
2 AS
3 PROCEDURE emp_sal_sum ;
4 PROCEDURE emp_sal_avg ;
5 END emp_sal_mgr ;
6 /
패키지 몸체부
SQL>CREATE OR REPLACE PACKAGE BODY emp_sal_mgr
2 AS
3 PROCEDURE emp_sal_sum
4 IS
5 CURSOR emp_total_sum IS
6 SELECT COUNT(*), SUM(NVL(sal,0))
7 FROM emp ;
8 total_num NUMBER ;
9 total_sum NUMBER ;
10 BEGIN
11 OPEN emp_total_sum ;
12 FETCH emp_total_sum INTO total_num, total_sum ;
13 DBMS_OUTPUT.PUT_LINE('총인원수:'||total_num||', 급여합계: '||total_sum) ;
14 CLOSE emp_total_sum ;
15 END emp_sal_sum ; --emp_sum 합계 구하는 PROCEDURE 끝
16 PROCEDURE emp_sal_avg --emp_avg 평균 구하는 PROCEDURE 시작
17 IS
18 CURSOR emp_total_avg IS
19 SELECT COUNT(*),AVG(NVL(sal,0))
20 FROM emp ;
21 total_num NUMBER ;
22 total_avg NUMBER ;
23 BEGIN
24 OPEN emp_total_avg ;
25 FETCH emp_total_avg INTO total_num, total_avg ;
26 DBMS_OUTPUT.PUT_LINE('총인원수:'||total_avg||', 급여합계: '||total_avg) ;
27 CLOSE emp_total_avg ;
28 END emp_sal_avg ; --평균 구하는 PROCEDURE 끝
29 END emp_sal_mgr ; --PACKAGE 끝
30 /
생성된 패키지 실행
SQL>SET SERVEROUTPUT ON
SQL>EXEC emp_sal_mgr.emp_sal_sum ; --패키지이름.프로시저 이름으로 실행
-> 총인원수 : 14, 급여합계 : 29025
SQL>EXEC emp_sal_mgr.emp_sal_avg ;
-> 총 인원수 : 14, 급여평균 : 2073.214287................
위 패키지 실행결과가 맞는지 sql을 직접 수행해서 검증
SQL>SELECT COUNT(*), SUM(NVL(sal,0)) FROM emp;
COUNT(*) SUM(NVL(SAL,0))
-------- ---------------
14 29025
사용 예2. member 테이블에 등록되어 있는 회원들의 성별과 아이디 검색과 비밀번호를 검색해주는 패키지
사용자 정보가 들어있는 member 테이블을 사용하여 회원관리를 하는 PL/SQL 패키지를 생성
이 패키지는 아래와 같은 프로시저로 구성
- 사용자 이름을 입력 받은 후 남자인지 여자인지를 구분하는 pro_gender 프로시저
- 사용자 이름과 주민번호를 입력 받아 회원의 아이디를 찾아주는 pro_find_id 프로시저
- 사용자 아이디와 연상단어를 입력 받아 회원의 비밀번호를 알려주는 pro_fin_pw 프로시저
1. 패키지 선언부를 생성
SCOTT>CREATE OR REPLACE PACKAGE pkg_member
2 IS
3 PROCEDURE pro_gender
4 (v_name IN member.name%TYPE) ;
5 PROCEDURE pro_find_id
6 (v_name IN member.name%TYPE ,
7 v_jumin2 IN member.jumin%TYPE) ;
8 PROCEDURE pro_find_pw
9 (v_id IN member.id%TYPE,
10 v_an IN member.an_key_dap%TYPE ) ;
11 END pkg_member ;
12 /
2. 패키지 몸체부를 생성
SCOTT>CREATE OR REPLACE PACKAGE BODY pkg_member
2 AS
3 PROCEDURE pro_gender(v_name IN member.name%TYPE) --성별 조회하는 프로시저,
4 IS
5 v_name2 member.name%TYPE ;
6 v_gender CHAR(4) ;
7 v_count NUMBER := 0 ;
8 exception_noname EXCEPTION ;
9 BEGIN
10 SELECT count(*) INTO v_count
11 FROM member
12 WHERE name = v_name ;
13 IF v_count = 0 THEN
14 RAISE exception_noname ;
15 ELSE
16 SELECT name, CASE WHEN SUBSTR(jumin,7,1) IN (1,3) THEN '남자' ELSE '여자' END
17 INTO v_name2, v_gender
18 FROM member
19 WHERE name = v_name ;
20 DBMS_OUTPUT.PUT_LINE(v_name2||' 님의 성별은 '||v_gender||' 입니다') ;
21 END IF ;
22 EXCEPTION
23 WHEN exception_noname
24 THEN RAISE_APPLICATION_ERROR(-20001,'그런 이름은 없습니다!') ;
25 END pro_gender ; --성별 조회 PROCEDURE 종료
26 PROCEDURE pro_find_id --이름,주민번호 입력받아 아이디를 검색하는 프로시저
27 (v_name IN member.name%TYPE ,
28 v_jumion2 IN member.jumin%TYPE )
29 IS
30 v_count NUMBER := 0 ;
31 v_count2 NUMBER := 0 ;
32 v_name2 member.name%TYPE ;
33 v_id2 member.id%TYPE ;
34 exception_noname EXCEPTION ;
35 excetpion_nojumin EXCEPTION ;
36 BEGIN
37 SELECT count(*) INTO v_count
38 FROM member
39 WHERE name = v_name ;
40 IF v_count = 0 THEN
41 RAISE exception_noname ; --이름이 없을 경우 에러를 발생시킴
42 ELSE
43 SELECT count(*) INTO v_count2
44 FROM member
45 WHERE jumin = v_jumin2 ;
46 IF v_count2 = 0 THEN
47 RAISE exception_nojumin ; -- 주민번호가 없을 경우 에러를 발생시킴
48 ELSE
49 SELECT name, id INTO v_name2, v_id2
50 FROM member
51 WHERE name = v_name ;
52 END IF ;
53 END IF ;
54 DBMS_OUTPUT.PUT_LINE(v_name2||' 님의 아이디는 '||v_id2||'입니다') ;
55 EXCEPTION
56 WHEN exception_noname
57 THEN RAISE_APPLICATION_ERROR(-20001,'입력하신 이름은 없습니다!') ;
58 WHEN exception_nojumin
59 THEN RAISE_APPLICATION_ERROR(-20002,'입력하신 주민번호는 없습니다!') ;
60 END pro_find_id ; --아이디를 찾는 프로시저를 종료
61 PROCEDURE pro_find_pw --아이디와 연관단어를 입력하여 비번을 찾아내는 프로시저
62 (v_id IN member.id%TYPE ,
63 v_an IN member.an_key_dap%TYPE )
64 IS
65 v_count NUMBER := 0 ;
66 v_id2 member.id%TYPE ;
67 v_an_dap member.an_key_dap%TYPE ;
68 v_pw member.passwd%TYPE ;
69 exception_noid EXCEPTION ;
70 exception_noan EXCEPTION ;
71 BEGIN
72 SELECT count(*) INTO v_count
73 FROM member
74 WHERE id = v_id ;
75 IF v_count = 0 THEN
76 RAISE exception_noid ; -- 아이디가 없으면 에러를 발생시킴
77 ELSE
78 SELECT an_key_dap INTO v_an_dap
79 FROM member
80 WHERE id = v_id ;
81 IF v_an_dap = v_an THEN
82 SELECT id, passwd INTO v_id2, v_pw
83 FROM member
84 WHERE id = v_id ;
85 ELSE RAISE exception_noan ;
86 END IF ;
87 END IF ;
88 DBMS_OUTPUT.PUT_LINE('입력하신 '||v_id||' 의 비밀번호는 '||v_pw||' 입니다!') ;
89 EXCEPTION
90 WHEN exception_noid
91 THEN RAISE_APPLICATION_ERROR (-20003,'입력하신 아이디는 없습니다!') ;
92 WHEN exception_noan
93 THEN RAISE_APPLICATION_ERROR (-20003,'입력하신 연상단어가 틀립니다!') ;
94 END pro_find_pw ; -- 프로시저를 종료
95 END pkg_member ; -- 패키지 종료
96 /
- pro_gender PROCEDURE 테스트
SCOTT>EXEC pkg_member.pro_gender('홍길동') ;
-> 홍길동 님의 성별은 남자 입니다
SCOTT>EXEC pkg_member.pro_gender('유관순') ;
-> 유관순 님의 성별은 여자 입니다
- pro_find_id PROCEDURE 테스트
이름과 주민번호를 입력하면 아이디를 검색해주는 기능
SCOTT>EXEC pkg_member.pro_find_id('홍길동',7510231234567') ;
-> 홍길동님의 아이디는 simson 입니다
- pro_find_pw PROCEDURE 테스트
SCOTT>EXEC pkg_member.pro_find_pw('simson','홍길동') ;
-> 입력하신 simson 의 비밀번호는 a1234 입니다
4. TRIGGER (트리거)
테이블, 뷰, 스키마 또는 데이터베이스에 관련된 PL/SQL블록(또는 프로시저)으로 관련된 특정 사건(EVENT)이 발생될 때마다 묵시적(자동) 으로 해당 PL/SQL TRIGGER 블록이 실행됨
간단한 예로 테이블에 상품이 입고되면(EVENT 발생) 재고 테이블에 자동으로 재고가 증가하게 만드는것
1) 주요 트리거 유형
(1) 단순 DML TRIGGER
- BEFORE TRIGGER
테이블에서 DML 이벤트를 TRIGGER 하기 전에 TRIGGER 본문을 실행
즉 어떤 작업을 하기전에 TRIGGER를 먼저 수행 한 후 작업을 하는 경우
- AFTER TRIGGER
테이블에서 DML 이벤트를 TRIGGER 한 후에 TRIGGER 본문을 실행
- INSTEAD OF TRIGGER
TRIGGER 문 대신 TRIGGER 본문을 실행, 다른 방법으로는 수정이 불가능한 뷰에 사용
DML TRIGGER
- 문장 TRIGGER : 대상이 모든 행으로 영향을 받는 행이 없더라도 TRIGGER가 한 번은 실행 됨
예를들어 특정 테이블에 데이터를 입력할 수 있는 시간을 지정하는 경우 어떤 데이터가 들어오던지 간에 TRIGGER 를 적용
- 행 TRIGGER : 특정 행이 TRIGGER 이벤트의 영향을 받을 경우 해당 TRIGGER가 실행되고 영향을 받는 행이 없을 경우 TRIGGER 가 실행되지 않는다.
얘를들어 제품번호가 100번인 제품만 입력안되게 막고 싶을 경우 다른 행은 TRIGGER 와 상관없지만 100번 제품은 TRIGGER 가 적용되어 입력이 안됨
행 TRIGGER가 실행될 때 PL/SQL 런타임 엔진은 두개의 데이터 구조를 생성하고 채움
- OLD : TRIGGER 가 처리한 레코드의 원래 값(기존값) 을 저장
- NEW : 새로 입력되거나 변경되는 값을 의미
(2) 혼합 TRIGGER (11g 부터 추가)
여러가지 TRIGGER 를 하나로 만든 것으로 PL/SQL 의 패키지와 비슷한 개념
혼합 TRIGGER 가 주로 사용되는 시점
- 실행되는 문장 앞에
- 실행되는 문장이 영향을 주는 각 행 앞에
- 실행되는 문장이 영향을 주는 각 행 뒤에
- 실해오디는 문장 뒤에
혼합 TRIGGER 생성시 주의사항
- 혼합 TRIGGER 는 DML TRIGGER여야 하며 테이블이나 뷰에 정의해야 한다
- 혼합 TRIGGER의 본문은 PL/SQL 에서 작성한 혼합 TRIGGER 블록이어야 한다
- 혼합 TRIGGER 본문에는 초기화 블록이 포함될 수 없으므로 예외 섹션이 있을수 없다
- 한 섹션에서 발생하는 예외는 해당 섹션에서 처리되어야 한다
- :OLD 및 :NEW 는 선언, BEFORE STATEMENT 또는 AFTER STATEMENT 섹션에 나타날수 없다
- BEFORE EACH ROW 섹션만 :NEW 값을 변경할 수 있다
- FOLLOWS 절을 사용하지 않으면 혼합 TRIGGER 의 실행 순서가 일정하지 않는다
2) TRIGGERR 구조
트리거는 트리거가 실행되는 시점(Timing), 트리거를 실행시키는 사건(Event), 트리거와 관련된 테이블/뷰/스키마/데이터베이스 그리고 트리거 몸체부(body)로 구성
3) TRIGGER 관리
(1) 활성화/비활성화 하기
SQL>ALTER TRIGGER TRIGGER_name DISABLE | ENABLE ;
(2) 특정 테이블에 속한 TRIGGER의 활성화/비활성화
SQL>ALTER TABLE table_name DISABLE | ENABLE ALL TRIGGERS ;
(3) TRIGGER 수정 후 다시 컴파일
SQL>ALTER TRIGGER tirgger_name COMPILE ;
(4) TRIGGER 삭제
SQL>DROP TRIGGER trigger_name ;
(5) TRIGGER 관련 권한
※ TRIGGER 를 생성, 변경 및 삭제할 수 있는 권한
- GRANT CREATE TRIGGER TO SCOTT ;
- GRANT ALTER ANY TRIGGER TO SCOTT ;
- GRANT DROP ANY TRIGGER TO SCOTT ;
※ 데이터베이스에서 TRIGGER를 생성할 수 있는 권한
- GRANT ADMIN IS TER DATABASE TRIGGER TO SCOTT ;
※ EXCUTE 권한 (TRIGGER 가 실행하는 스키마에 포함되지 않은 객체를 참조하는 경우)
4) TRIGGER 예제
(실습을 하기전 scott 사용자에게 create trigger 권한을 할당)
SYS>GRANT create trigger TO scott ;
예 1) 테이블에 데이터를 입력할 수 있는 시간 지정하기
(테이블 전체가 대상이므로 문장 레벨 TRIGGER 사용)
테스트를 위해 연습용 테이블 t_order 테이블 생성
SCOTT>CREATE TABLE t_order_tab
2 ( no NUMBER ,
3 ord_code VARCHAR2(10),
4 ord_date DATE ) ;
데이터를 입력할 때 입력시간이 18:40 분에서 18:50 분일 경우만 입력을 허용하고 그 외는 에러발생 트리거 생성
SCOTT>CREATE OR REPLACE TRIGGER t_order
2 BEFORE INSERT ON t_order_tab
3 BEGIN
4 IF(TO_CHAR(SYSDATE,'HH24:MI') NOT BETWEEN '18:40' AND '18:50') THEN
5 RAISE_APPLICATION_ERROR(-20100,'허용시간 아닙니다') ;
6 END IF ;
7 END ;
8 /
18:40 ~ 18:50 에 입력 할 경우
SCOTT>INSERT INTO t_order_tab
2 VALUES(1,'C100',SYSDATE) ;
-> 1 row created -- 정상적으로 입력됨
18:40 ~ 18:50 이외의 시간에 입력 할 경우
SCOTT>INSERT INTO t_order_tab
2 VALUES(2,'C200',SYSDATE) ;
-> ERROR at line 1:
ORA-20100: 허용시간 아닙니다 -- 의도하던 에러 발생
예 2) 테이블에 입력될 데이터 값을 지정하고 그 값 이외에는 에러를 발생시키는 TRIGGER 를 생성
SCOTT>CREATE OR REPLACE TRIGGER t_order2
2 BEFORE INSERT ON t_order_tab
3 FOR EACH ROW --행 레벨 TRIGGER
4 BEGIN
5 IF(:NEW.ord_code) NOT IN ('C100') THEN --:NEW 새로들어오는 데이터
6 RAISE_APLICATION_ERROR(-20200,'제품코드가 틀립니다!') ;
7 END IF ;
8 END ;
9 /
신규 내용을 입력하는데 앞에서 생성한 문장레벨 TRIGGER로 인해 입력이 안됨
따라서 앞의 실습에서 만든 TRIGGER 삭제
SCOTT>DROP TRIGGER t_order ;
올바른 제품코드 입력
SCOTT>INSERT INTO t_order_tab
2 VALUES(2,'C100',SYSDATE) ;
-> 1 row created - 정상입력
틀린 제품코드(C200) 입력
SCOTT>INSERT INTO t_order_tab
2 VALUES(3,'C200',SYSDATE) ;
-> ERROR at line 1:
ORA-20200 : 제품코드가 틀립니다! - 의도하던 오류 발생
예 3) TRIGGER 의 작동 조건을 WHEN 절로 더 구체적으로 지정
ORD_CODE 가 'C500' 인 제품에 대해서만 19:30 부터 19:35 까지 입력을 허용하는 TRIGGER
다른 제품코드느 시간 관계없이 정상적으로 입력 가능
SCOTT>CREATE OR REPLACE TRIGGER t_order3
2 BEFORE INSERT ON t_order_tab
3 FOR EACH ROW
4 WHEN (NEW.ord_code ='C500') <-- WHEN 절에는 NEW 앞에 콜론 기호(:) 삭제
5 BEGIN
6 IF(TO_CHAR(SYSDATE,'HH24:MI')NOT BETWEEN '19:30 AND '19:35')THEN
7 RAISE_APPLICATION_ERROR(-20300,'C500 제품의 입력허용시간이 아닙니다!') ;
8 END IF
9 END ;
10 /
예 4) 특정 테이블에 입력할 수 있는 계정을 지정하기
(테이블 자체가 대상이므로 문장 TRIGGER 를 사용)
실습을 위해 새로운 테이블을 생성
SCOTT>CREATE TABLE t_test1(no NUMBER);
SCOTT>CREATE TRIGGER t_usercheck
2 BEFORE INSERT OR UPDATE OR DELETE
3 ON t_test1
4 BEGIN
5 IF USER NOT IN ('SCOTT','HR') THEN
6 RAISE_APPLICATION_ERROR(-20001,'허락된 계정이 아닙니다!!') ;
7 END IF ;
8 END ;
9 /
테스트를 위해 새로운 사용자 계정 USER1을 생성하고 필요한 권한을 할당
SCOTT> conn / as sysdba
SYS>CREATE USER user1 IDENTIFIED BY user1
2 DEFAULT TABLESPACE users
3 TEMPORARY TABLESPACE temp ;
SYS>GRANT connect, resource TO user1 ;
SYS>GRANT INSERT ON scott.t_test1 TO user1 ;
SCOTT>CONN user1/user1
USER1>INSERT INTO scott.t_test1 VALUES(1) ;
-> ERROR at line 1:
ORA-20001 : 허락된 계정이 아닙니다!!
예 5. 기존 테이블(t_test1) 에 데이터가 업데이트 될 때 기존 내용을 백업테이블(t_test2)으로 옮겨놓는 TRIGGER 를 생성. 삭제되는 특정 행이 TRIGGER의 대상이므로 행 레벨 TRIGGER를 사용
SCOTT>CREATE TABLE t_test1
2 (no NUMBER, name VARCHAR2(10)) ;
SCOTT>CREATE TABLE t_test2
2 AS SELECT * FROM t_test1 ;
SCOTT>INSERT INTO t_test1 VALUES (1,'AAA') ;
SCOTT>INSERT INTO t_test1 VALUES (2,'BBB') ;
SCOTT>COMMIT ;
SCOTT>CREATE OR REPLACE TRIGGER t_move
2 BEFORE UPDATE ON t_test1
3 FOR EACH ROW
4 BEGIN
5 INSERT INTO t_test2 VALUES (:OLD,no , :OLD.name) ;
6 END ;
7 /
위 예에서 5번 라인에 :OLD.no , :OLD,name 은 update 되면서 이전 데이터를 의미
SCOTT>SELECT * FROM t_test1 ;
NO NAME
--- -----
1 AAA
2 BBB
SCOTT>SELECT * FROM t_test2 ;
no rows selected
SCOTT>UPDATE t_test1
2 SET no=2
3 WHERE name='AAA' ;
1 row updated.
SCOTT>SELECT * FROM t_test2 ;
NO NAME
--- ------
1 AAA
'오라클 > PL/SQL' 카테고리의 다른 글
| PL/SQL 예외처리 (0) | 2014.10.16 |
|---|---|
| PL/SQL CURSOR (0) | 2014.10.14 |
| PL/SQL 제어문 (0) | 2014.10.13 |
| PL/SQL 변수 (0) | 2014.10.13 |
| PL/SQL 시작하기 (0) | 2014.10.13 |