6. Oracle Subprogram ★★★

[기타SQL]/PL_SQL 2014. 10. 16. 12:28 |

6장. Oracle Subprogram★

지금까지의 PL/SQL 블록들은 전부 익명 블록인데, 익명블록이란 말그대로 PL/SQL블록에 이름이 정해져 있지 않다는 뜻이다. 익명블록들은 DECLARE 로 선언해 사용해서 정해진 이름이 없어도 잘 실행이 되었으나, 이름이 없으므로 저장이나 재 사용을 할 수 없다. 이럴 때 서브프로그램 또는 프로그램 단위를 호출해서 사용한다.
* 서브프로그램(프로그램단위) : 자주 사용되는 PL/SQL블록에 이름을 지정하고 생성해서 저장해 두었다가 필요할 경우에 호출해서 사용할 수 있도록 하는 것.

-> 대표유형 : 프로시저(PROCEDURE), 함수(Function), 패키지(PACKAGE), 트리거(TRIGGER)

프로시저(procedure), 함수(function)은 셋트이다.

익명블록	서브프로그램
이름이 지정되지 않은 PL/SQL 블록	이름이 지정된 PL/SQL블록
매번 사용시마다 컴파일된다.	최초 실행될 때 한번만 컴파일 된다
데이터베이스에 저장되지 않는다	데이터베이스에 저장된다
다른 응용프로그램에서 호출 불가하다	다른 응용프로그램에서 호출 할 수 있다
값을 반환하지 않는다	함수일 경우 값을 반환 한다
파라미터를 사용할 수 없다	파라미터를 사용할 수 있다

1. PROCEDURE (프로시저) : 지정된 특정 처리를 실행하는 서브 프로그램의 한 유형.

- 단독(standalone)으로 실행되거나 다른 PROCEDURE나 다른 툴(Oracle Developer…) 또는 다른 환경(Pro*C…)등에서 호출되어 실행된다.

- 처음 생성 후 컴파일 할 때 오브젝트로서 데이터베이스 내에 저장되며 이후로 반복적으로 호출되어 실행될 때 별도의 컴파일 없이 p-code 로 바로 실행된다.

(일반 스크립트로 저장해서 @ 기호로 불러서 실행하는 것은 실행 할 때마다 컴파일을 해야 하므로 부하가 걸리지만, 오브젝트로 저장되는 서브 프로그램은 최초에 컴파일 되어서 저장되므로 이후 재 실행 시 컴파일이 별도로 필요하지 않다)
- PROCEDURE 생성 : CREATE PROCEDURE

- PROCEDURE 삭제 : DROP PROCEDURE
- PROCEDURE 수정 : ALTER PROCEDURE

[PROCEDURE 생성 문법 ]

CREATE [OR REPLACE] PROCEDURE    PROCEDURE_name [( parameter1 [mode1] datatype1,
   parameter2 [mode2] datatype2,
    … )]
   IS | AS
PL/SQL Block ;

- OR REPLACE : 생성하고자 하는 PROCEDURE가 기존에 동일이름으로 존재할 경우, 기존의 내용을 현재의 내용으로 수정하는 옵션.

(해당 이름의 PROCEDURE를 삭제한 후 다시 생성한다.)

- parameter : PROCEDURE를 실행할 때 호출환경과 PROCEDURE간에 값을 주고 받을 때 사용되는 매개변수->모드(IN,OUT,IN OUT)에 따라 역할 다름.
파라미터를 선언할 때는 데이터형을 적고 데이터의 크기는 기록하지 않는다.

-> 형식 파라미터(formal parameter) : procedure를 생성할 때 선언부에 선언된 파라미터.

-> 실행 파라미터(actual parameter) : procedure를 실행할 때 형식 파라미터에 실제 값이나 변수를 할당/대응할 때 사용하는 파라미터.

- mode - in :사용자로부터 값을 입력받아 procedure로 전달. (기본값)

- out : procedure에서 호출환경(sql plus, 다른 프로시저 등)으로 값을 전달.

이 모드로 설정된 매개변수는 procedure내에서 읽을 수 없으며, 값을 저장하기만 하는 지역변수로 사용된다.

호출환경에서 이 매개변수로부터 값을 전달 받기 위해 환경변수가 선언되어 있어야 한다.

- in out : 호출환경과 procedure간에 값을 주고받는 지역변수. (읽기 쓰기 모두 가능)

* 파라미터 모드 별 비교

IN모드	OUT모드	IN OUT모드
기본모드	명시적으로 지정	명시적으로 지정
서브프로그램에 전달	호출환경에 반환	서브프로그램에 전달 / 호출환경에도 반환
형식 파라미터가 상수로 동작	초기화 되지 않은 변수	초기화된 변수
실제 파라미터가 리털럴, 표현식, 상수 또는 초기화된 변수가 될 수 있다.	변수만 사용가능	변수만 사용가능
기본값 할당 가능	기본값 할당 불가	기본값 할당 불가

* 참고*

-PROCEDURE의 내용을 확인할 때 : USER_SOURCE 딕셔너리 활용.

-에러내용을 보고 싶을 때 : SHOW ERRORS

실습 1. 부서번호가 20번 인 사람들의 job 을 ‘CLERK’ 으로 변경하는 PROCEDURE

SQL> CREATE OR REPLACE PROCEDURE update_20 --사용자로부터 값을 입력받지 않으므로, 매개변수를 별도로 사용하지 않음.
2 IS

   3 BEGIN                           --3번부터 7번까지가 익명블록이다.
   4    UPDATE emp
   5    SET    job='CLERK'
   6    WHERE   deptno=20;
   7 END;
   8 /
PROCEDURE    created.

SQL> EXEC update_20;    -- PROCEDURE 실행 : EXECUTE 프로시저명 (해당PROCEDURE의 소유자이거나 EXECUTE권한 있어야함)

PL/SQL PROCEDURE    successfully completed.

실습 2. 사번을 입력 받아 급여를 인상하는 PROCEDURE (사용자로부터 값을 입력 받아야 하므로 입력 매개변수를 사용)

SCOTT>SELECT   empno , ename , sal FROM   emp WHERE   empno=7902;

EMPNO       ENAME    SAL
------------ ---------- ----------
     7902     FORD      3000         -- 변경 전 3000.

SCOTT>CREATE OR REPLACE PROCEDURE up_sal    -- 1~3사이 :사람이 입력한값을 PL/SQL엔진에 저장
   2 ( vempno emp.empno%TYPE )                     -- 입력값을 저장할 변수 vempno 선언.

--vempno IN emp.empno%TYPE에서 IN생략(IN모드가 기본값이므로)

   3   IS                                                               --3~4번사이 : PL/SQL엔진이 db에가서 원하는걸 꺼내와.
   4 BEGIN
   5   UPDATE emp SET sal=5000
   6   WHERE    empno=vempno ;
   7 END;
   8 /

PROCEDURE  created.

SCOTT>EXEC up_sal(7902);         -- PROCEDURE실행(사원번호를 함께 입력)

PL/SQL prodecure    successfully completed.

SCOTT>SELECT   empno , ename, sal FROM   emp WHERE   empno=7902;

EMPNO     ENAME    SAL
--------- ---------- ----------
      7902     FORD      5000    --변경 후 5000.

실습 3. 사번을 입력 받아 그 사원의 이름과 급여를 출력하는 PROCEDURE

SCOTT>CREATE OR REPLACE PROCEDURE ename_sal
   2 ( vempno emp.empno%TYPE )    -- 사용자로부터 사번입력받음.(사람-PL/SQL엔진)
   3   IS
   4     vename emp.ename%TYPE ;   --이름,급여를 db에서 꺼내옴 (PL/SQL엔진-db)
   5     vsal         emp.sal%TYPE ;
   6
   7 BEGIN
   8     SELECT    ename, sal
   9     INTO     vename , vsal   --db에가서 이름과급여를 vename,vasal에 담음
10     FROM     emp
11    WHERE    empno=vempno;
12
13    DBMS_OUTPUT.PUT_LINE ('사원명은 '||vename||' 입니다');   --13,14번줄이 없다면출력을안함.(프로시저는 return값이 없으므로)
14    DBMS_OUTPUT.PUT_LINE ('급여는 '||vsal||'입니다');
15 END;
16 /

SCOTT> set serveroutput on;

SCOTT> EXEC ename_sal(7900); -- PROCEDURE실행(사원번호를 함께 입력해야됨.)
사원명은 JAMES입니다.
급여는 950입니다.

- function은 return값이 있어서 바로 결과를 보여주지만, 프로시저는 return값이 없으므로 dbms_output.put_line을 사용하지 않으면 결과를 보여주지 않는다.

실습 4. OUT 모드 파라미터 사용 예

교수 테이블을 사용하여 PROCEDURE가 실행 될 때 교수번호를 입력 받은 후 해당 교수의 이름과 급여를 OUT 변수로 선언하여 해당 PROCEDURE 를 호출한 곳으로 OUT 하는 예제. (out변수를 사용하는 것과 작성된 procedure를 다른 함수나 procedure에서 어떻게 호출되어 사용되는지 익힐 수 있음.)

SCOTT>CREATE OR REPLACE PROCEDURE    info_prof
   2 ( v_profno       IN        professor.profno%TYPE ,
   3    v_name    OUT     professor.name%TYPE ,   -- 다른 pl/sql에게 보낼거야.
   4    v_pay        OUT     professor.pay%TYPE )    -- 다른 pl/sql에게 보낼거야.
   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    created.

-- 위에서 작성한 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 입니다.

PL/SQL PROCEDURE successfully completed.

또 다른 방법으로 별도의 변수를 선언해서 값을 수행해서 받은 후 출력할 수 도 있다.

SCOTT>VARIABLE name VARCHAR2(10)
SCOTT>VARIABLE pay      NUMBER
SCOTT>EXEC info_prof(1001 , :name , :pay);

PL/SQL PROCEDURE    successfully completed.

SCOTT>PRINT name pay

NAME
----------
조인형

      PAY
-----------
      550

ex) 입/출고 담당하는 프로시저를 먼저돌려서 결과가나오면 out, 그결과를 재고프로시저에 보내서 재고프로시저가 돌아간다.

* 프로시저 이름 지정하는 방법.
① 위치 지정: PROCEDURE 를 실행하거나 호출할 때 실제 파라미터를 형식 파라미터와 동일한 순서로 나열. (위에서 사용한 방법)
                (위치 지정 사용 예: info_prof(1001 , v_name , v_pay))
② 이름 지정: 실제 파라미터를 임의의 순서로 나열하며 연관 연산자(=>)를 사용하여 이름 지정
                   형식 파라미터를 실제 파라미터와 연관시킵니다.
[이름 지정 사용 예]

SCOTT>DECLARE
       2    v_name professor.name%TYPE    ;
       3    v_pay      professor.pay%TYPE    ;
       4 BEGIN
      5     info_prof (v_profno =>1001 ,
      6              v_name => v_name ,
      7                v_pay =>     v_pay) ;
       8    DBMS_OUTPUT.PUT_LINE ('이름:'||v_name);
       9    DBMS_OUTPUT.PUT_LINE ('급여:'||v_pay);
      10 END ;
      11 /

이름:조인형
급여:550

    PL/SQL PROCEDURE    successfully completed.

③ 혼합: 일부 실제 파라미터는 위치 지정으로 나열하고, 나머지는 이름 지정으로 나열(11g 부터 추가된 방법)

[사용예]

SCOTT> DECLARE
         2    v_name professor.name%TYPE    ;
      3    v_pay   professor.pay%TYPE    ;
         4 BEGIN
        5     info_prof(1001 ,                  -- 첫 번째 위치 변수의 값을 직접 지정
       6              v_name => v_name ,   -- 이름을 직접 지정함
       7              v_pay => v_pay);
         8    DBMS_OUTPUT.PUT_LINE (' - 이름: '||v_name);
         9    DBMS_OUTPUT.PUT_LINE (' - 급여: '||v_pay);
         10    END ;
         11   /

- 이름: 조인형
- 급여: 550

실습 5. 생성된 PROCEDURE 의 내용 확인.
USER_SOURCE 딕셔너리를 활용.

SCOTT>DESC USER_SOURCE ;

Name                        Null?    Type
----------------- -------- ----------------------------
NAME                                VARCHAR2(30)
TYPE                                 VARCHAR2(12)
LINE                                   NUMBER
TEXT                                 VARCHAR2(4000)

SCOTT>SELECT    text                     --프로시저 처음에 생성할 때 썼던 내용이 나온다.
   2   FROM   user_source
   3 WHERE   name ='INFO_PROF' ;    --USER_SOURCE, DBA_SOURCE 이런거에서 테이블명같은거 찾는건 대문자를써준다.

TEXT
-----------------------------------------------------
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 ;

10 rows SELECT ed.

[PROCEDURE 연습문제 1]
신입 사원의 대한 아래 정보를 입력 받아 Emp 테이블에 등록하는 PROCEDURE 를 작성하세요.
신입사원의 정보는 아래와 같으며 나머지 컬럼의 값은 null 입니다. (PROCEDURE 이름과 변수 명은 임의대로 하세요)
* Empno : 4000
* Name : Smith
* Job : Clerk
* Manger no: 7900
* Salary : 3500

[PROCEDURE 연습문제2 ]
Emp 테이블을 사용하여 부서번호를 입력 받아서 보너스(comm) 를 아래의 조건으로 계산하여 사번, 이름, 부서번호, 급여, 보너스를 출력하는 PROCEDURE를 작성하세요. (PROCEDURE 이름과 변수 명은 적당하게 정하세요)
* 조건
1. Deptno = 10 이면 급여의 20%
2. Deptno = 20 이면 급여의 30%
3. Deptno = 30 이면 급여의 10%
4. 나머지는 0%

여기부터실습

[PROCEDURE 연습문제 3]
emp , dept 테이블을 사용하여 사원의 사원번호를 입력 받아서 아래처럼 그 사원의 사원번호와 ,이름, 부서명 ,급여, 상여금 을 출력하는 PROCEDURE   를 작성하세요.  (PROCEDURE 이름과 변수명은 마음대로 하세요)
• 사 번 :
• 이 름 :
• 부서명 :
• 급 여 :
• 상여금 :

[PROCEDURE 연습문제 4]
아래 예시와 같이 대출금액과 대출 금리 , 기간을 입력하면 상환 내역을 출력하는 PROCEDURE 를 작성하세요.

단 상환방식은 원리금 균등방식으로 가정합니다. (아래 예시는 대출금액:1,000,000 원 , 대출금리 : 0.05(5%) , 기간: 12개월로 계산된 화면입니다)

2. FUNCTION (함수)

FUNCTION 과 PROCEDURE의 차이점

PROCEDURE 는 정해진 작업을 수행한 후 결과를 반환 할 수도 있고(OUT, IN OUT 모드 사용시) 반환 하지 않고 그냥 종료할 수도 있지만

함수는 정해진 작업을 수행 한 후 결과를 돌려준다(RETURN)는 부분만 다르다.

함수 생성 : CREATE FUNCTION

함수 삭제 : DROP FUNCTION

함수 수정 : ALTER FUNCTION

에러 확인 : SHOW ERRORS

[문 법]

CREATE [OR REPLACE] FUNCTION function_name
[( parameter1 [mode1] datatype1,
parameter2 [mode2] datatype2,
… )]
RETURN datatype
IS | AS
PL/SQL Block ;

- OR REPLACE, PARAMETER, MODE : PROCEDURE와 동일.

- RETURN datatype (PROCEDURE와 다른 부분)
: 함수명으로 리턴할 데이터(값)의 형을 선언하는 부분. PL/SQL 함수 블록 내에서는 반드시 RETURN 문을 포함해야 하며, 그 때 RETURN 되는 데이터는 이 부분에서 선언한 데이터 형과 일치되어야 한다.

* 이렇게 생성됨 함수는 아래와 같은 곳에서 사용 가능하다.

- Query의 SELECT 리스트 또는 절
- WHERE 및 HAVIN G 절의 조건식
- Query의 CONNECT BY, START WITH, ORDER BY 및 GROUP BY 절
- IN SERT 문의 VALUES 절
- UPDATE 문의 SET 절

[함수 예제 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 /
Function created.

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 /

Function created.

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 /

Function created.

SQL> SELECT DISTINCT deptno, AVG_SAL(deptno)
   2   FROM    emp;

    DEPTNO   AVG_SAL(DEPTNO)
--------------- -----------------------
         30              1566.67
         10               2916.67
         20                  2175

[함수 예제 4. 사원번호를 입력 받아 해당 사원의 부서명을 알려주는 함수 ]

SCOTT>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
10                         FROM      emp
11                         WHERE   empno=v_empno);
12    RETURN v_dname ;
13 END ;
14 /

Function created.

SCOTT>SELECT ename, deptno ,F_DNAME(empno) "DNAME"
   2 FROM   emp ;

ENAME    DEPTNO    DNAME
---------- ------------- --------------------
홍길동           10    ACCOUNTIN G
일지매           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;

11 rows selected.

3. ORACLE PACKAGE ( 패키지 )

* 패키지 : 특정 처리를 위해 관련된 PL/SQL 블록들(procedure, 함수, pl/sql데이터유형, 복합유형 등)이 논리적으로 하나의 그룹을 이루는 특수한 형태. (연관성이 높은 함수나 PROCEDURE 를 하나의 그룹으로 묶어서 관리하는 것.)
ex) 사원관리 업무 중에 입사관리, 연봉관리, 상여금관리, 근태관리, 퇴사자 관리 등의 업무가 많이 있을 경우 사원관리 라는 PACKAGE를 생성하고 그 세부에 각 작업을 수행하는 함수나 PROCEDURE 를 생성해서 관리할 때.
* package 구성.

① 선언부 : 해당 package에 사용될 함수나 procedure,변수등에 대한 정의를 선언하는 부분. (목차 같은 의미)

② 몸체부 : 선언부에서 선언된 함수나 procedure등이 실제 code로 구현되는 부분. (선언부에 선언되지 않더라도 package몸체부에 사용될 수는 있지만 별로 권장하지 않는다.)
- 만약 생성된 PACKAGE의 선언부가 변경되었다면 무조건 PACKAGE 몸체부는 다시 재 생성해야하며 PACKAGE를 참조(호출)하는 서브 프로그램들도 재 번역(recompile)해야 하지만, 반대로 PACKAGE 몸체부만 변경되었다면 PACKAGE 선언부와 다른 관련 서브프로그램에 영향을 주지 않고 몸체부만 재 생성하면 된다.

1) PACKAGE 구조 :PACKAGE 선언부(Specification)/PACKAGE 몸체부(Body) 로 구성.
(1) PACKAGE 선언부 생성
[ 문 법 ]

CREATE [OR REPLACE] PACKAGE PACKAGE_name
IS | AS   --IS 또는 AS쓴다.
   Public type and item declarations
   Subprogram specifications
END PACKAGE_name ;

- public type and item declarations : 변수,상수, 명시적 커서, 사용자 정의 예외, PRAGMA 등을 선언. (모두 public이라는 특징을 갖는다.)

(2) PACKAGE 몸체부(Body) 생성
[문 법]

CREATE [OR REPLACE] PACKAGE BODY PACKAGE_name
IS | AS
Private type and item declarations
Subprogram bodies

END PACKAGE_name ;

- private type and item declarations : 변수, 상수, 명시적커서, 사용자정의 예외, pragma등을 선언.

- subprogram bodies : 실제 작동할 서브프로그램(procedure, 함수 등)을 기록. 단 서브프로그램의 순서를 주의해야 한다. 기본적으로 참조되는 변수든 서브프로그램이든 참조하는 서브프로그램보다는 먼저 정의돼야 한다. 일반적으로 public의 서브프로그램은 마지막 부분에 정의한다.

2) PACKAGE 실행하기
: 생성된 PACKAGE 오브젝트에 대한 실행권한을 가진 사용자만이 PACKAGE를 호출하여 실행할 수 있다.

3) PACKAGE 삭제
: PACKAGE 선언부와 PACKAGE 몸체부를 모두 삭제할 수 도 있고, PACKAGE 몸체부만 삭제할 수도 있다.

DROP PACKAGE PACKAGE_name ;
DROP PACKAGE BODY PACKAGE_name ;

4) PACKAGE 사용 예

예1) Emp table 에서 총 급여합계와 평균 급여를 구하는 emp_sal_mgr PACKAGE.

-- 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   /

PACKAGE created.

-- PACKAGE 몸체부
SQL> CREATE OR REPLACE PACKAGE BODY emp_sal_mgr AS
   2   PROCEDURE    emp_sal_sum
   3   IS
   4    CURSOR emp_total_sum    IS
   5       SELECT   COUNT(*), SUM(NVL(sal,0))
   6       FROM    emp;
   7    total_num NUMBER    ;
   8    total_sum NUMBER ;
   9   BEGIN
10    OPEN emp_total_sum ;
11     FETCH emp_total_sum    INTO    total_num , total_sum ;
12    DBMS_OUTPUT.PUT_LINE ('총인원수: '||total_num||' , 급여합계: '||total_sum);
13    CLOSE emp_total_sum;
14   END emp_sal_sum ; --emp_sum PROCEDURE 끝

15   PROCEDURE    emp_sal_avg -- emp_avg PROCEDURE 시작
16   IS
17    CURSOR emp_total_avg    IS
18      SELECT    COUNT(*), AVG(NVL(sal,0))
19      FROM    emp;
20    total_num NUMBER    ;
21    total_avg NUMBER    ;    -- 다음 페이지에서 계속됩니다

22 BEGIN
23    OPEN emp_total_avg ;
24     FETCH emp_total_avg    INTO    total_num , total_avg ;
25        DBMS_OUTPUT.PUT_LINE ('총인원수: '||total_num||' , 급여평균: '||total_avg);
26    CLOSE emp_total_avg ;
27     END emp_sal_avg ; -- PROCEDURE   끝
28    END emp_sal_mgr ; -- PACKAGE 끝
29 /

PACKAGE body created.

-- 생성된 패키지를 실행.

SQL> SET SERVEROUTPUT ON
SQL> EXEC emp_sal_mgr.emp_sal_sum; --PACKAGE 이름.PROCEDURE 이름 으로 실행.

총인원수: 14 , 급여합계: 29025

PL/SQL PROCEDURE successfully completed.

-- 위 PACKAGE 실행결과가 맞는지 SQL 을 직접 수행해서 검증.

SQL> SELECT COUNT(*) , SUM(NVL(sal,0)) FROM emp;

COUNT(*) SUM(NVL(SAL,0))
-------------- ---------------------
14 29025

SQL> EXEC emp_sal_mgr.emp_sal_avg ;

총인원수: 14 , 급여평균: 2073.214285714285714285714285714285714286

PL/SQL procedure successfully completed.

이하 예제는 생략함.

예2) member 테이블에 등록되어 있는 회원들의 성별과 아이디 검색과 비밀번호를 검색해 주는 PACKAGE. (실제 사용하는 쿼리라 양많고 복잡해)
사용자 정보가 들어있는 member 테이블을 사용하여 회원 관리를 하는 PL/SQL PACKAGE 를 생성.

* 아래와 같은 procedure로 구성.
- 사용자 이름을 입력 받은 후 남자인지 여자인지를 구분하는 pro_gender 프로시저    ,
- 사용자 이름과 주민번호를 입력 받아 회원의 아이디를 찾아주는 pro_find_id 프로시저    ,
- 사용자 아이디와 연상단어를 입력 받아 회원의 비밀번호를 알려주는 pro_find_pw 프로시저

[준비]

SCOTT>SELECT    * FROM   member ;   -- 실습용 스크립트인 test_data.sql 을 실행시키시면 테이블이 자동으로 만들어짐.
   NO NAME        JUMIN              PASSWD     ID           AN_KEY     AN_KEY_DAP
-------- ------------ ------------------- ------------ --------------- --------------- ---------------
1001   홍길동      7510231234567    a1234         simson      아내이름?       유관순
1002   유관순      7509222345678   b1234        bobby       남편이름?       홍길동
1003   강감찬      1410234567890    c1234       daddy       아빠이름?       홍길동
1004   일지매       1609223456789   d1234        mommy     엄마이름?       유관순

Step 1. PACKAGE 선언부 생성.

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   /

package created.

Step 2. PACKAGE BODY부 생성.

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   --0명일경우.
14       RAISE exception_noname ;
15      ELSE
16        SELECT    name , CASE   WHEN     SUBSTR( jumin ,7,1) IN    (1,3)
17                              THEN    '남자'
18                           ELSE    '여자'
19                        END
20       INTO v_name2 , v_gender
21       FROM   member
22      WHERE   name = v_name ;
23      DBMS_OUTPUT.PUT_LINE(v_name2||' 님의 성별은 '||v_gender||' 입니다');
24      END IF ;
25    EXCEPTION
26     WHEN    exception_noname
27      THEN   RAISE_APPLICATION_ERROR(-20001,'그런 이름은 없습니다!') ;
28    END pro_gender; -- 성별 조회 PROCEDURE   종료됩니다.

29 PROCEDURE   pro_find_id   -- 이름,주민번호 입력 받아 아이디를 검색하는 프로시저.
30    (v_name      IN    member.name%TYPE    ,

31     v_jumin2    IN    member.jumin%TYPE    )
32   IS
33     v_count    NUMBER := 0 ;
34     v_count2   NUMBER := 0 ;
35     v_name2   member.name%TYPE    ;
36     v_id2       member.id%TYPE    ;

37      exception_noname    EXCEPTION    ;
38      exception_nojumin    EXCEPTION    ;
39 BEGIN
40      SELECT    count(*) INTO v_count
41      FROM   member
42     WHERE   name = v_name ;
43       IF v_count = 0 THEN
44         RAISE exception_noname; -- 이름이 없을 경우 에러를 발생시킵니다.
45       ELSE
46         SELECT    count(*) INTO v_count2
47         FROM   member
48        WHERE    jumin =v_jumin2 ;
49         IF v_count2 = 0 THEN
50           RAISE exception_nojumin    ; -- 주민번호가 없을 경우 에러를 발생시킵니다.
51         ELSE
52           SELECT    name , id   INTO v_name2 , v_id2
53           FROM   member
54          WHERE    name=v_name ;
55         END IF;
56        END IF;
57     DBMS_OUTPUT.PUT_LINE(v_name2||'님의 아이디는 '||v_id2||' 입니다');
58   EXCEPTION
59     WHEN    exception_noname
60        THEN   RAISE_APPLICATION_ERROR(-20001,'입력하신 이름은 없습니다!');
61     WHEN    exception_nojumin
62        THEN RAISE_APPLICATION_ERROR(-20002,'입력하신 주민번호는 없습니다!');
63   END pro_find_id ; -- 아이디를 찾는 PROCEDURE 를 종료합니다.

64 PROCEDURE   pro_find_pw -- 아이디와 연관단어를 입력하여 비번을 찾아내는 PROCEDURE
65 (v_id    IN   member.id%TYPE    ,
66   v_an IN   member.an_key_dap%TYPE    )
67   IS
68    v_count NUMBER    := 0 ;
69    v_id2 member.id%TYPE    ;
70    v_an_dap member.an_key_dap%TYPE    ;
71    v_pw member.passwd%TYPE    ;
72     exception_noid   EXCEPTION    ;
73     exception_noan   EXCEPTION    ;
74 BEGIN

75     SELECT    count(*) INTO v_count
76     FROM   member
77    WHERE    id = v_id ;
78    IF   v_count = 0 THEN
79       RAISE exception_noid ; -- 아이디가 없으면 에러를 발생시킵니다.
80    ELSE
81       SELECT    an_key_dap INTO v_an_dap
82       FROM   member
83      WHERE    id = v_id ;
84        IF v_an_dap = v_an   THEN
85          SELECT    id , passwd    INTO    v_id2 , v_pw
86          FROM   member
87         WHERE    id = v_id ;
88        ELSE   RAISE exception_noan;
89       END IF ;
90     END IF ;
91    DBMS_OUTPUT.PUT_LINE('입력하신 '||v_id||' 의 비밀번호는 '||v_pw||' 입니다');
92   EXCEPTION
93    WHEN    exception_noid
94      THEN RAISE_APPLICATION_ERROR (-20003,'입력하신 아이디는 없습니다!');
95    WHEN    exception_noan
96      THEN RAISE_APPLICATION_ERROR (-20004,'입력하신 연상단어가 틀립니다!');
97    END pro_find_pw ; -- PROCEDURE   를 종료합니다
98    END pkg_member ; --   PACKAGE를 종료합니다.
99   /

package body created.

Step 3. 생성된 PACKAGE를 테스트 합니다.

-- 현재 테이블에 들어 있는 데이터를 조회합니다.
SCOTT>set line 200
SCOTT>col no for 99999
SCOTT>col name for a10
SCOTT>col jumin for a13
SCOTT>col passwd for a5
SCOTT>col id for a8
SCOTT>col an_key for a10
SCOTT>col an_key_dap for a10

SCOTT>SELECT    * FROM   member ;

    NO   NAME       JUMIN            PASSW     ID       AN_KEY     AN_KEY_DAP
--------- ------------- -------------------- --------- ---------- ------------    -----------------
   1001    홍길동      7510231234567     a1234    simson   아내이름?      유관순
   1002    유관순      7509222345678    b1234   bobby   남편이름?     홍길동
   1003    강감찬      1410234567890     c1234    daddy    아빠이름?     홍길동
   1004    일지매        1609223456789    d1234   mommy 엄마이름?     유관순

- pro_gender PROCEDURE 를 테스트 합니다.

SCOTT>EXEC pkg_member.pro_gender('홍길동') ;
홍길동 님의 성별은 남자 입니다

PL/SQL procedure successfully completed.

SCOTT>EXEC pkg_member.pro_gender('유관순');
유관순 님의 성별은 여자 입니다

PL/SQL procedure successfully completed.

- 등록 되지 않은 이름을 입력 한 후 에러를 확인합니다.

SCOTT>EXEC pkg_member.pro_gender('유관순') ;
BEGIN   pkg_member.pro_gender('유관순') ; END;
*
ERROR at line 1:
ORA-20001: 그런 이름은 없습니다!   --의도했던 에러 메시지가 발생합니다.
ORA-06512: at "SCOTT.PKG_MEMBER", line    27
ORA-06512: at line    1

- pro_find_id PROCEDURE 를 테스트합니다.

이 PROCEDURE 는 이름과 주민번호를 입력하면 아이디를 검색해 주는 기능을 합니다.

SCOTT>EXEC pkg_member.pro_find_id('홍길동','7510231234567') ;
홍길동님의 아이디는 simson 입니다

PL/SQL PROCEDURE successfully completed.

- 주민번호 잘못 입력한 후 에러 발생 확인

SCOTT>EXEC pkg_member.pro_find_id('홍길동','1234567123456');
BEGIN   pkg_member.pro_find_id('홍길동','1234567123456'); END;

*
ERROR at line    1:
ORA-20002: 입력하신 주민번호는 없습니다! -- 의도했던 에러 메시지가 발생합니다.
ORA-06512: at "SCOTT.PKG_MEMBER", line 62
ORA-06512: at line    1

- 이름 잘못 입력한 후 에러발생 확인

SCOTT>exec pkg_member.pro_find_id('홍길동','7510231234567');
BEGIN   pkg_member.pro_find_id('홍길동','7510231234567'); END;

*
ERROR at line    1:
ORA-20001: 입력하신 이름은 없습니다! -- 의도했던 에러 메시지가 발생합니다.
ORA-06512: at "SCOTT.PKG_MEMBER", line    60
ORA-06512: at line    1

- pro_find_pw PROCEDURE 를 테스트 합니다.

이 PROCEDURE 는 사용자 아이디와 연상단어를 입력 받은 후 비번을 알려주는 PROCEDURE 입
니다.

SCOTT>EXEC pkg_member.pro_find_pw('simson','유관순');
입력하신 simson 의 비밀번호는 a1234 입니다

PL/SQL PROCEDURE successfully completed.

- 아이디를 틀리게 넣고 에러를 확인

SCOTT>EXEC pkg_member.pro_find_pw('aaaa','유관순') ;
BEGIN   pkg_member.pro_find_pw('aaaa','유관순') ; END;
*
ERROR at line    1:
ORA-20003: 입력하신 아이디는 없습니다!   -- 의도했던 에러 메시지가 발생합니다.

ORA-06512: at "SCOTT.PKG_MEMBER", line 94

ORA-06512: at line 1

- 아이디는 맞고 연상 단어를 잘못 넣은 후 에러 확인

SCOTT>EXEC pkg_member.pro_find_pw('simson','홍길동') ;
BEGIN   pkg_member.pro_find_pw('simson','홍길동') ; END;
*
ERROR at line    1:
ORA-20004: 입력하신 연상단어가 틀립니다!   -- 의도했던 에러 메시지가 발생합니다.
ORA-06512: at "SCOTT.PKG_MEMBER", line    96
ORA-06512: at line    1

5) 생성된   PACKAGE 조회하기

- 선언부 조회하기

SCOTT>SELECT text
   2   FROM   user_source
   3 WHERE    type=' PACKAGE';

TEXT
--------------------------------------------------------------------------------
PACKAGE emp_total as
PROCEDURE    emp_sum;
PROCEDURE    emp_avg;
end emp_total;

- BODY 부 조회하기

SCOTT>SELECT text
   2    FROM   user_source
   3 WHERE    type like ' PACKAGE BODY';

TEXT
--------------------------------------------------------------------------------
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;

28 rows selected.

4. TRIGGER (트리거)

1)개요

2)주요 TRIGGER유형

(1) 단순 DML TRIGEER

- before trigger : 테이블에서 DML이벤트를 trigger하기전에 trigger 본문을 실행.

- after trigger : 테이블에서 DML이벤트를 trigger한 후에 trigger 본문을 실행.

- instead of trigger : (잘안씀)

ex) 입고가 되면 재고테이블에 반영.

DML TRIGGER는 다시 문장 TRIGGER 와 행 TRIGGER 로 나누어집니다.

문장 TRIGGER는 대상이 모든 행이며 영향을 받는 행이 전혀 없더라도 TRIGGER가 무조건 한번은 실행된다.

예를 들어서 특정 테이블에 데이터를 입력할 수 있는 시간을 지정하는 경우에 어떤 데이터가 들어오든지 TRIGGER 를 적용하게 됩니다. 또는 특정 테이블에 데이터를 입력할 수 있는 계정을 등록하는 등과 같이 테이블에 들어 있는 행이 대상이 아니라 테이블 자체를 대상으로 TRIGGER를 작동하게 만들 경우에 문장 TRIGGER를 사용하면 됩니다.

행 TRIGGER는 테이블에 있는 특정 행이 TRIGGER 이벤트의 영향을 받을 경우 해당 TRIGGER가 실행되고, TRIGGER 이벤트의 영향을 받는 행이 없을 경우에는 TRIGGER는 실행되지 않습니다.

즉 특정행만 이 TRIGGER 의 영향을 받는 것입니다. 예를 들어 제품번호가
100 번인 제품만 입력이 안되게 막고 싶을 경우에 다른 행은 TRIGGER 와 상관없지만 100번
제품은 TRIGGER 가 적용되어 입력이 안 되는 것입니다. 이처럼 특정 행에게만 영향을 주는
TRIGGER 를 행 TRIGGER 라고 하며 행 TRIGGER 로 생성하려면 FOR EACH ROW 라는 구절을
사용하면 됩니다.

바로 예제로 점프

5) TRIGGER 예제
( 아래 실습을 하기 전에 아래와 같이 scott 사용자에게 CREATE TRIGGER 권한을 할당하세요)

SYS> GRANT create tigger TO scott ;

예 1) 테이블에 데이터를 입력할 수 있는 시간 지정하기 (테이블 전체가 대상이므로 문장 레벨 TRIGGER 사용.)
테스트를 하기 위해 아래와 같이 연습용 테이블 t_order 테이블을 생성합니다.

SCOTT>CREATE TABLE    t_order_tab
   2 (   no         NUMBER ,
   3    ord_code   VARCHAR2(10),
   4    ord_date   DATE ) ;

Table created.

위에서 생성한 테이블에 데이터를 입력할 때 입력시간이 18:40 분에서 18:50 분일 경우만 입력을 허용하고 그 외 시간일 경우는 에러를 발생시키는 TRIGGER 생성하겠습니다.

SCOTT>CREATE OR REPLACE TRIGGER t_order
   2 BEFORE    INSERT ON t_order_tab -- t_oorder_tab에 insert되기전에!!
   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 /

trigger created.

SCOTT>SELECT SYSDATE FROM DUAL;

SYSDATE
------------------------
2014-04-07:18:43:51 – 입력 허용 시간 입니다.

SCOTT>INSERT INTO t_order_tab
2 VALUES(1,'C100',SYSDATE);

1 row created.   – 정상적으로 입력됩니다.

SCOTT>SELECT SYSDATE FROM   DUAL;

SYSDATE
------------------------
2014-04-07:18:53:06   -- 입력허용시간이 지났습니다

SCOTT>INSERT INTO t_order_tab
   2 VALUES(2,'C200',SYSDATE);
INSERT INTO t_order_tab
             *
ERROR at line    1:
ORA-20100: 허용시간 아닙니다   -- 의도하던 에러가 발생합니다
ORA-06512: at "SCOTT.T_ORDER", line    3
ORA-04088: error during execution of trigger 'SCOTT.T_ORDER'

예 2)
테이블에 입력될 데이터 값을 지정하고 그 값 이외에는 에러를 발생시키는 TRIGGER 를 생성합
니다.

위 문장 레벨 TRIGGER 실습에서 생성한 테이블로 계속 실습하겠습니다.
이번에는 제품코드가 ‘C100’ 번인 제품이 입력될 경우 입력을 허용하고 나머지 제품은 모두 에러
를 발생시키는 TRIGGER 를 작성하겠습니다. 이 경우는 테이블에 있는 행이 TRIGGER 의 검사 대
상이므로 행 레벨 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
   6      RAISE_APPLICATION_ERROR(-20200,'제품코드가 틀립니다!') ;
   7    END IF;
   8 END;
   9 /

TRIGGER created.

-- 신규 내용을 입력하는데 앞에서 생성한 문장레벨 TRIGGER로 인해 입력이 안됩니다.

SCOTT>INSERT INTO t_order_tab
   2 VALUES(2,'C100',SYSDATE) ;
INSERT INTO T_ORDER_TAB
             *
ERROR at line    1:
ORA-20100: 허용시간 아닙니다
ORA-06512: at "SCOTT.T_ORDER", line    3
ORA-04088: error during execution of TRIGGER 'SCOTT.T_ORDER'

SCOTT>DROP TRIGGER t_order ;    -- 앞의 실습에서 만든 TRIGGER 를 삭제합니다.

TRIGGER dropped.

-- 올바른 제품코드 데이터를 입력합니다.
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) ;
INSERT INTO T_ORDER_TAB
             *
ERROR at line    1:
ORA-20200: 제품코드가 틀립니다!   -- 의도 하던 오류가 발생합니다.
ORA-06512: at "SCOTT.T_ORDER2", line    3
ORA-04088: error during execution of TRIGGER 'SCOTT.T_ORDER2'

예 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   절에는 콜론 기호는 ( : ) 삭제할 것
   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 /

trigger created.

SCOTT>SELECT SYSDATE FROM   DUAL;

SYSDATE
-----------------------
2014-04-07:19:31:42   -- 입력 허용되는 시간입니다.

SCOTT>INSERT INTO t_order_tab
   2 VALUES (1,'C500',SYSDATE);

1 row created. – 정상적으로 입력됩니다.

SCOTT>SELECT SYSDATE FROM DUAL;

SYSDATE
-----------------------
2014-04-07:19:36:04   -- 입력 허용 불가 시간입니다

SCOTT>INSERT INTO t_order_tab
   2 VALUES (2,'C500',SYSDATE) ;
INSERT INTO t_order_tab
             *
ERROR at line    1:
ORA-20300: C500 제품의 입력허용시간이 아닙니다!   -- 에러가 발생합니다
ORA-06512: at "SCOTT.T_ORDER3", line    3
ORA-04088: error during execution of TRIGGER 'SCOTT.T_ORDER3'

- 다른 제품 코드로 입력하니 정상적으로 입력됩니다.

SCOTT>INSERT INTO t_order_tab
2 VALUES (2,'C600',SYSDATE) ;

1 row created.

예 4 ) 특정 테이블에 입력할 수 있는 계정을 지정하기
(테이블 자체가 대상이므로 문장 TRIGGER 를 사용합니다.)
실습을 위해 새로운 테이블을 생성합니다.

SCOTT>CREATE TABLE    t_test1 (no NUMBER );
Table created.

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   /

trigger created.

테스트를 위해 새로운 사용자 계정 user1 을 생성하고 필요한 권한을 할당합니다..

SCOTT>conn / as sysdba

Connected.

SYS>CREATE USER user1 IDENTIFIED BY user1
   2 DEFAULT TABLESPACE users
   3    TEMPORARY TABLESPACE temp;
User created.

SYS>GRANT connect , resource TO user1;
Grant succeeded.

SYS>GRANT INSERT ON scott.t_test1 TO user1;
Grant succeeded.

SCOTT>CONN user1/user1
Connected.

USER1>INSERT INTO scott.t_test1 VALUES (1);
INSERT INTO scott.t_test1 VALUES (1)
                   *
ERROR at line    1:
ORA-20001: 허락된 계정이 아닙니다!!!
ORA-06512: at "SCOTT.T_USERCHECK", line   3
ORA-04088: error during execution of TRIGGER 'SCOTT.T_USERCHECK'

예 5) 기존 테이블(t_test1) 에 데이터가 업데이트 될 때 기존 내용을 백업테이블(t_test2)으로
옮겨놓는 TRIGGER 를 생성합니다. 삭제되는 특정 행이 TRIGGER의 대상이므로 Row 레벨
TRIGGER 를 사용합니다.

SCOTT> CREATE TABLE t_test1
2 (no NUMBER , name VARCHAR2(10));

Table created.

SCOTT> CREATE TABLE    t_test2
2 AS SELECT * FROM    t_test1;

Table created.

SCOTT> INSERT INTO    t_test1   VALUES (1,'AAA');
1 row created.

SCOTT> INSERT INTO    t_test1   VALUES (2,'BBB');
1 row created.

SCOTT> COMMIT;
Commit complete.

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   /

TRIGGER created.

위 예에서 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

예 6 )
기존 테이블(t_test3) 의 데이터가 delete 될 때 기존 내용을 백업 테이블(t_test4)로 이동시키며
이때 백업 테이블에 삭제한 시간, 삭제 전 데이터, 삭제한 사용자 이름을 모두 기록하는
TRIGGER를 생성합니다.

SCOTT> CREATE TABLE    t_test3
   2 ( no        NUMBER ,
3   name    VARCHAR2(10));

Table created.
SCOTT> CREATE TABLE    t_test4
   2 (   no        NUMBER    ,
   3    name      VARCHAR2(10),
   4      time       DATE ,
   5    username VARCHAR2(10)) ;

Table created.

SCOTT> INSERT INTO t_test3 VALUES (1,'AAA');
1 row created.

SCOTT> INSERT INTO t_test3 VALUES (2,'BBB');
1 row created.

SCOTT> COMMIT ;
Commit complete.

SCOTT> SELECT * FROM    t_test3;

    NO    NAME
---------- -------------
       1    AAA
       2     BBB

SCOTT> SELECT * FROM    t_test4;

no rows selected

SCOTT> CREATE OR REPLACE TRIGGER d_trigger
   2 AFTER delete ON t_test3
   3   FOR EACH ROW
   4 BEGIN
   5       INSERT INTO t_test4
   6     VALUES(:OLD.no, :OLD.name , SYSDATE , USER );
   7   END ;
   8    /

trigger created.

SCOTT> SELECT * FROM t_test3;

     NO   NAME
----------    ---------
       1    AAA
       2     BBB

SCOTT> SELECT * FROM t_test4;

no rows selected

SCOTT> DELETE FROM t_test3 WHERE no=1;

1 row deleted.

SCOTT> SELECT * FROM t_test3;

     NO   NAME
----------    -----------
       2     BBB

SCOTT>SELECT * FROM    t_test4;

     NO   NAME    TIME         USERNAME
---------- ---------- ---------------- ----------------
        1   AAA      30-NOV-13    SCOTT

예 7 ) 사용자의 로그온, 로그오프 정보를 테이블에 기록하는 TRIGGER 생성
이번 실습은 아래와 같이 관리자 계정인 SYSTEM 계정으로 로그인 해서 작업하세요.

SCOTT> CONN   system/oracle ;

- 로그온 정보를 기록할 테이블을 생성합니다.

SYSTEM>CREATE TABLE    log_on
   2 (userid VARCHAR2(20),
   3   time    DATE );
Table created.

- TRIGGER를 작성합니다.

SYSTEM>CREATE OR REPLACE TRIGGER t_logon
   2 AFTER LOGON ON database
   3 BEGIN
   4     INSERT INTO LOG_ON
   5    VALUES(USER,SYSDATE) ;
   6   END ;
   7 /

trigger created.

- 다른 창에서 로그인 한 후 log_on 테이블의 내용을 확인합니다.

SYSTEM>SELECT userid, TO_CHAR(TIME,'YYYY-MM-DD:HH24:MI:SS') "TIME"
   2   FROM    log_on ;

USERID                      TIME
-------------------- -------------------------------
SYS                      2014-04-07:20:41:29
SYSTEM               2014-04-07:20:44:14
SCOTT                2014-04-07:20:44:31
SYS                      2014-04-07:20:47:30

4 rows selected.

- log off 정보를 저장할 테이블을 생성합니다.

SYSTEM>CREATE TABLE    log_off
   2 (userid VARCHAR2(20),
   3   time DATE ) ;

Table created.

SYSTEM> CREATE OR REPLACE TRIGGER t_logoff
   2 BEFORE LOGOFF ON DATABASE
   3 BEGIN
   4     INSERT INTO log_off
   5    VALUES(USER,SYSDATE);

   6    END ;
   7 /

trigger created.

-- 다른 창에서 logoff 한 후 테이블을 조회 해 보겠습니다.

SYSTEM>SELECT userid , TO_CHAR(TIME,'YYYY-MM-DD:HH24:MI:SS') "TIME"
   2   FROM    log_off ;

USERID                 TIME
-------------------- -----------------------------------
SYS                  2014-04-07:21:05:32
SCOTT               2014-04-07:21:06:14
SYS                  2014-04-07:21:06:32

위와 같이 LOGON / LOGOFF 시 정보를 TRIGGER 를 사용하여 남길 수 있습니다

예 8) 특정 사용자가 DDL 을 수행하지 못하게 막는 TRIGGER

오라클 8.1.6 버전부터 제공하는 SYSTEM TRIGGER 기능을 이용하여 특정 사용자가 DDL 을 사용
하는 것을 제어 할 수 있습니다. 현업에서 아주 많이 사용되고 있는 TRIGGER 예 입니다.
이 실습을 하기 위해서 아래와 같이 관리자 계정으로 로그인 하세요.

SCOTT>CONN sys/oracle as sysdba ;

CASE    1. scott 사용자가 DDL 모두를 사용하지 못하게 막는 TRIGGER

SYS>CREATE OR REPLACE TRIGGER scott_no_ddl
   2    BEFORE DDL ON scott.schema
   3 BEGIN
   4    RAISE_APPLICATION_ERROR(-20001,'DDL 은 사용할 수 없습니다!!');
   5   END;
   6 /

trigger created.

SYS>CONN scott/tiger
Connected.

SCOTT>CREATE TABLE    test_ddl_1
   2 (no NUMBER ) ;

CREATE TABLE    test_ddl_1
*
ERROR at line    1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20001: DDL 은 사용할 수 없습니다!!
ORA-06512: at line    2

예상했던 에러가 발생하는 것을 확인 할 수 있습니다.
다음 실습을 위해 위 TRIGGER 를 삭제합니다.

SCOTT>CONN sys/oracle as sysdba

Connected.

SYS>DROP TRIGGER scott_no_ddl;

TRIGGER dropped.

CASE    2. scott 계정에서 Table 과 Index 에만 DDL을 사용 못하게 막는 TRIGGER

SYS>CREATE OR REPLACE TRIGGER   scott_no_ddl_2
   2    BEFORE   DDL ON scott.schema
   3   WHEN (ora_dict_obj_type='TABLE' OR ora_dict_obj_type='INDEX')
   4 BEGIN
   5    RAISE_APPLICATION_ERROR(-20002,'Table과 Index에는 DDL 사용할 수 없습니다!');
   6   END;
   7 /

trigger created.

SYS>CONN scott/tiger
Connected.

SCOTT>CREATE TABLE ddl_test_1 (no NUMBER );
CREATE TABLE ddl_test_1 (no NUMBER )
*

ERROR at line    1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20002: Table과 Index에는 DDL 사용할 수 없습니다!
ORA-06512: at line    2

SCOTT>CREATE INDEX idx_emp_no ON emp(empno);

create Index idx_emp_no on emp(empno)
                            *
ERROR at line    1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20002: Table과 Index에는 DDL 사용할 수 없습니다!
ORA-06512: at line    2

SCOTT>CRATE VIEW v_emp
   2 AS    SELECT empno , ename FROM    emp ;

View created.

SCOTT>CONN sys/oracle as sysdba
Connected.

SYS>DROP TRIGGER scott_no_ddl_2;

TRIGGER dropped. <- 다음 실습을 위해 위에서 생성한 TRIGGER 를 삭제합니다.

CASE    3. Scott schema 의 Drop 과 Truncate 작업을 막는 TRIGGER

SCOTT>CONN sys/oracle as sysdba
Connected.

SYS>CREATE OR REPLACE TRIGGER scott_no_ddl_3
   2   BEFORE DROP OR TRUNCATE ON scott.schema
   3 BEGIN
   4    RAISE_APPLICATION_ERROR(-20003,'Drop 과 Truncate 사용 불가입니다!');
   5   END;

6 /

trigger created.

SYS>CONN scott/tiger
Connected.

SCOTT>DROP VIEW v_emp;
drop view v_emp
*
ERROR at line    1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20003: Drop 과 Truncate 사용 불가입니다!
ORA-06512: at line    2

SCOTT>TRUNCATE TABLE emp;
truncate table emp
                *
ERROR at line    1:
ORA-00604: error occurred at recursive SQL level 1
ORA-20003: Drop 과 Truncate 사용 불가입니다!
ORA-06512: at line    2

이상으로 다양한   TRIGGER 활용 예제들을 살펴보았습니다

TRIGGER 를 끝으로 PL/SQL 기초과정을 마치겠습니다.

jyc8618

저작자표시 변경금지

'[기타SQL] > PL_SQL' 카테고리의 다른 글

5. Oracle Exception (예외처리) (0)	2014.10.15
4. PL/SQL Cursor(커서) 정리해야됨. (1)	2014.10.14
3. PL/SQL 제어문. (0)	2014.10.13
2. PL/SQL 변수. (0)	2014.10.13
1. ORACLE PL/SQL 시작하기 (0)	2014.10.13

Posted by JJ*

Category

Recent Posts

Recent Trackbacks

Tags

6. Oracle Subprogram ★★★

'[기타SQL] > PL_SQL' 카테고리의 다른 글

티스토리툴바