44_[db]저장 서브 프로그램

14 minute read

저장 서브 프로그램

앞서 사용했던 PL/SQL 블록은 익명 블록으로, 오라클에 저장되지 않는 블록이었다!

이런 익명 블록은 반복적인 PL/SQL의 블록단위에 대한 작업 수행의 어려움이 있다!

그래서 필요한 것이 “저장 서브 프로그램”, 여러 번 PL/SQL로 작성된 프로그램을 사용하기 위해서 오라클에 특정 이름으로 저장해두고 사용하는 PL/SQL 프로그램!

🌟 메모리, 성능, 재사용성 등의 면에서 장점!!

익명 블록 vs 저장 서브 프로그램

🌺 저장 서브 프로그램의 종류 🌺

프로시져(저장 프로시져 Stored Procedure)
- 특정 처리 작업을 수행하는 데 사용
- 용도에 따라 파라미터 사용할 수도, 사용하지 않을 수도 있음
- SQL문에서는 사용 불가

-프로시져 생성하기

[USAGE]

CREATE [OR REPLACE] PROCEDURE 프로시져_이름
IS | AS
	선언부
BEGIN
	실행부
EXCEPTION
	예외처리부
END 프로시져이름;

프로시져는 뷰를 생성할 때 처럼 REPLACE도 가능하게 하여, 존재하는 경우에는 덮어쓸 수 있도록 한다!
프로시져를 생성할 때, 선언부는 DECLARE 대신 IS나 AS를 사용하는데 , 선언부가 존재하지 않더라도 해당 키워드는 꼭 명시해야 한다!
예외처리부와 END 뒤에 붙는 프로시져 이름은 생략할 수 있다!

CREATE OR REPLACE PROCEDURE PC_EX
IS
    V_EMPNO NUMBER:=7788;
    V_ENAME VARCHAR2(10);
BEGIN
    V_ENAME:='SCOTT';
    DBMS_OUTPUT.PUT_LINE('NAME: '||V_ENAME||', EMPNO: '||V_EMPNO);
END;
/

프로시져를 위와 같은 형태로 만들게 되면 ,저장할 때에, 처음에는

PL/SQL 프로시저가 성공적으로 완료되었습니다.

Procedure PC_EX이(가) 컴파일되었습니다.

와 같이 출력되고

그 이후에는 “컴파일되었습니다”만 출력된다. 그 이유는 REPLACE 속성을 주었기 때문이다!

만약 OR REPLACE를 제거하면

오류 보고 - ORA-00955: name is already used by an existing object

00000 - “name is already used by an existing object”

와 같은 오류가 보고된다!

-프로시져 실행하기

[USAGE]

EXECUTE 프로시져_이름;

-🌻 특정 블록 및 익명 블록에서 프로시져 실행하기

BEGIN
	프로시져 이름;
END;
/

-프로시져 내용 확인하기 : USER_SOURCE 데이터 사전 뷰에서 조회

NAME : 서브프로그램(생성 객체) 이름
TYPE: 서브 프로그램 종류(프로시져, 함수 등)
LINE : 서브 프로그램에 작성한 줄 번호
TEXT : 서브 프로그램에 작성한 소스 코드

-프로시져 삭제하기

DROP PROCEDURE 프로시져_이름;

-파라미터를 사용하는 프로시져

CREATE [OR REPLACE] PROCEDURE 프로시져 이름
[(파라미터_이름1 [MODES] 자료형 [:=값],...
 (파라미터_이름N [MODES] 자료형 [DEFAULT 기본값])]
IS | AS
	선언부
BEGIN
	실행부
EXCEPTION
	예외처리부
END [(프로시져 이름)];

자료형은 자릿수 지정 및 NOT NULL 제약조건 사용 불가!
MODES: 파라미터 모드
예외처리부는 생략 가능

🌹 파라미터 모드

프로시져 파라미터 모드

🌟 자릿수 지정이 불가함을 확인해보자!

CREATE OR REPLACE PROCEDURE PR_EX
(
    PARAM1 IN NUMBER(4),
    PARAM2 NUMBER DEFAULT 5
)
IS

BEGIN
    DBMS_OUTPUT.PUT_LINE('PARAM1: ' || PARAM1);
    DBMS_OUTPUT.PUT_LINE('PARAM2: '||PARAM2);
END;
/

만약 위와 같이 실행하게 된다면

LINE/COL ERROR ——— ————————————————————- 3/21 PLS-00103: Encountered the symbol “(“ when expecting one of the following: := . ) , @ % default character The symbol “:=” was substituted for “(“ to continue. 오류: 컴파일러 로그를 확인하십시오.

위와 같이 ( 기호가 := 로 대체되었다라는 권장 문구를 확인해볼 수 있다!

*NOT NULL도 동일한 권장 문구를 확인해볼 수 있다!

🌟 파라미터가 있는 프로시져를 실행하려면

EXECUTE 프로시져명(파라미터1, ...,파라미터N);

와 같이 실행해주면 되는데, 기본값이 있는 경우가 있다면 최소한 그 파라미터를 제외한 파라미터들에 대해서는 값을 입력해주어야 한다!

(A) IN 모드

CREATE OR REPLACE PROCEDURE PR_EX
(
    PARAM1 IN NUMBER,
    PARAM2 NUMBER DEFAULT 5
)
IS

BEGIN
    DBMS_OUTPUT.PUT_LINE('PARAM1: ' || PARAM1);
    DBMS_OUTPUT.PUT_LINE('PARAM2: '||PARAM2);
END;
/

만약 IN 모드로 파라미터의 모드를 설정해주고, 위와 같이 두 개의 파라미터 중 하나는 기본값을 설정해두었다면

EXECUTE PR_EX(3);

로 입력값을 PARAM1에만 넣어주면 PARAM2는 기본값 5가 , PARAM1에는 3이 입력되어

PARAM1: 3 PARAM2: 5

을 출력하게 된다!

❓ 그러면 기본값이 설정된 파라미터에 값을 넣으면 어떻게 될까 ❓

EXECUTE PR_EX(3,10);

위와 같이 IN 모드 파라미터에 3을, DEFAULT값이 설정된 PARAM2에는 10을 넣게 되면

더이상 PARAM2는 기본값이 아닌 10이라는 값이 들어가게 되어

PARAM1: 3 PARAM2: 10

을 출력하게 된다!

❓ 그런데 만약에 기본값이 주어진 파라미터 외의 파라미터들에도 값을 넣지 않는다면 ❓

만약 동일 프로시져에 대해서

EXECUTE PR_EX();

위와 같이 기본값이 설정된 파라미터는 괜찮지만, 그 외의 파라미터들에 대해서 어떤 값도 넣지 않는다면

BEGIN PR_EX(); END; 오류 보고 - ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to ‘PR_EX’ ORA-06550: line 1, column 7: PL/SQL: Statement ignored

00000 - “line %s, column %s:\n%s” *Cause: Usually a PL/SQL compilation error. *Action:

위와 같이 인자의 갯수 혹은 인자의 유형이 잘못되었다는 오류를 보고받게 되고, 프로시져는 실행에 실패하고 만다!

파라미터에 값을 지정하는 방식

파라미터에 값을 지정하는 방식은 크게 위치 지정, 이름 지정, 혼합지정이 있다

CREATE OR REPLACE PROCEDURE PR_EX
(
    PARAM1 IN NUMBER,
    PARAM2 NUMBER DEFAULT 5
)
IS

BEGIN
    DBMS_OUTPUT.PUT_LINE('PARAM1: ' || PARAM1);
    DBMS_OUTPUT.PUT_LINE('PARAM2: '||PARAM2);
END;
/

각각을 이해해보고자 위에서 사용한 프로시져를 이용하자면,

EXECUTE PR_EX(3,10);

위와 같이 생성할 때 지정한 파라미터의 순서에 맞추어 넣는 것이 바로 “위치 지정” 방식이다!

EXECUTE PR_EX(PARAM1=>3,PARAM2=>10);

위와 같이 ⇒ 기호를 이용하여 어떤 파라미터에 어떤 값이 대입되는지를 명시하는 것을 “이름 지정” 방식

EXECUTE PR_EX(3,PARAM2=>10);

위와 같이 어떤 파라미터는 순서대로 입력하는 위치 지정방식으로 표시하여 대입하고, 어떤 파라미터는 ⇒기호를 이용해서 이름지정 방식으로 대입하는 것을 “혼합 방식”이라고 한다!

🌟 파라미터 갯수가 많아지면 다소 복잡해지는데, 이러한 이유에서 이름 지정방식이 편리해질 수 있다!

(B) OUT 모드

CREATE OR REPLACE PROCEDURE PR_OUT
(
    NO_INPUT IN EMP.EMPNO%TYPE,
    ENAME_OUTPUT OUT EMP.ENAME%TYPE,
    SAL_OUTPUT OUT EMP.SAL%TYPE
)
IS

BEGIN
    SELECT ENAME, SAL INTO ENAME_OUTPUT, SAL_OUTPUT
    FROM EMP
    WHERE EMPNO = NO_INPUT;
    
END PR_OUT;
/

OUT 모드는 OUT 키워드를 이용해서 위와 같이 사용할 수 있다!

주의할 점은 실행하는 방법이다!

DECLARE
	OUT 파라미터를 위한 변수들
BEGIN
	프로시져이름(IN 파라미터에 대한 값, OUT파라미터를 위한 변수들);
END;
/

실행할 때에는 OUT 파라미터에 대한 변수를 선언해두고 값을 해당 변수에서 반환받아야 사용할 수 있다!

DECLARE
    O_NAME EMP.ENAME%TYPE;
    O_SAL    EMP.SAL%TYPE;
BEGIN
    PR_OUT(7788,O_NAME,O_SAL);
    DBMS_OUTPUT.PUT_LINE('ENAME: '||O_NAME);
    DBMS_OUTPUT.PUT_LINE('SAL    :' ||O_SAL);
END;
/

ENAME: SCOTT SAL :3000

IN OUT 모드는 IN, OUT 모드를 동시에 수행하는 모드이다!

IN OUT 모드는 실행할 때 아래와 같이

DECLARE
	IN OUT 모드를 위한 변수;
BEGIN
	IN OUT 모드를 위한 변수에 값을 대입;
END;
/

OUT 모드처럼 변수를 설정해두고, 그 변수에 값을 대입해준 후 사용할 수 있다!

IN모드와 비교했을 때, 값을 대입하는 방식이 다소 간접적이라는 부분만 제외하고는 비슷하다!

CREATE OR REPLACE PROCEDURE PR_IO
(
    IO_NO IN OUT EMP.EMPNO%TYPE,
    O_NAME OUT EMP.ENAME%TYPE,
    O_SAL OUT EMP.SAL%TYPE
)
IS

BEGIN
    SELECT ENAME, SAL INTO O_NAME, O_SAL
    FROM EMP
    WHERE EMPNO = IO_NO;
END;
/

DECLARE
    IO_NO        EMP.EMPNO%TYPE;
    O_NAME    EMP.ENAME%TYPE;
    O_SAL        EMP.SAL%TYPE;
BEGIN
    IO_NO:=7788;
    PR_IO(IO_NO,O_NAME,O_SAL);
    DBMS_OUTPUT.PUT_LINE(IO_NO||'       '||O_NAME||'        '||O_SAL);
END;
/

7788 SCOTT 3000

-프로시져 오류 정보 확인하기

(1) 오류가 발생하는 프로시져를 먼저 만들어 보자!

CREATE OR REPLACE PROCEDURE PRO_ERR
IS
    ERR_NO NUMBER;
BEGIN
    --:=를 사용하지 않았음->에러 발생
    ERR_NO = 100;
    DBMS_OUTPUT.PUT_LINE(ERR_NO);
END;
/

위의 프로시져를 생성하려 하면,

:= 문자 가 아닌 = 문자로 값을 대입하려고 했으므로 에러가 발생하지만, 컴파일된다

이러한 프로시져에 대한 오류는 아래와 같이

🌹 SHOW ERRORS 혹은 SHOW ERR

SHOW ERRORS 혹은 SHOW ERR는 일련의 명령어로써, 가장 최근에 생성되거나 변경된 서브 프로그램의 오류 정보를 출력해준다!

저장 서브 프로그램의 가장 최근 오류 정보를 확인하는 명령어- SHOW ERRORS

저장 서브 프로그램의 가장 최근 오류 정보를 확인하는 명령어 - SHOW ERR

❓ 만약, 특정 저장 서브 프로그램의 오류 정보를 확인하고 싶다면 ❓

SHOW ERR 프로그램_종류 프로그램_이름;

을 이용하면 어떤 저장 서브 프로그램의 어떤 프로시져/함수/패키지/트리거 에 대한 오류 정보만 파악할 수 있다!

저장 서브 프로그램- 특정 프로그램의 오류 확인하기 SHOW ERR 프로그램 종류 프로그램 이름;

🌹 USER_ERRORS 라는 데이터 사전 뷰를 이용한 오류 확인하기!

USER_ERRORS 라는 데이터 사전 뷰를 이용하면, 위에서 이용했던 SHOW ERR를 통한 특정 프로그램에서의 오류를 테이블 형태로 확인해볼 수 있다!

데이터 사전 뷰 USER_ERRORS를 이용한 오류 확인

NAME은 저장 서브 프로그램의 이름
TYPE은 저장 서브 프로그램의 종류
TEXT는오류 정보와 메시지
ATTRIBUTE에는 오류임을 나타내는 ERROR 가 값으로 들어간 상태!

USER_ERRORS 를 통해 반환받을 수 있는 표에서 각각의 속성 중에서는 위와 같은 속성을 통해서 어떤 오류가 있었는지 확인할 수 있다!

함수 Stored Function

특정 연산을 거친 결과 값을 반환
SQL 문에서 사용 가능

프로시져와 함수의 차이점

🌟 RETURN 문이 실행되면 함수 실행은 즉시 종료됨!

CREATE [OR REPLACE] FUNCTION 함수 이름
[(파라미터_이름1 [IN] 자료형1 :=값,
파라미터_이름2 [IN] 자료형2 DEFAULT 기본값
...
파라미터_이름N [IN] 자료형N
)]
RETURN 자료형
IS | AS
	선언부
BEGIN
	실행부
	RETURN(반환값);
EXCEPTION
	예외처리부
END [함수 이름];

🌻 먼저, 선언부를 살펴보자!

CREATE [OR REPLACE] FUNCTION 함수 이름 [(파라미터_이름1 [IN] 자료형1 :=값, 파라미터_이름2 [IN] 자료형2 DEFAULT 기본값 … 파라미터_이름N [IN] 자료형N )]

이 부분에서 눈여겨 볼 만한 것은, 파라미터를 사용할 수도, 아닐 수도 있다는 점과!

모드는 IN 모드만이 지원된다는 점,

값은 프로시져에서와 동일하게 := 혹은 DEFAULT를 이용해서 지정해줄 수 있다는 점

이 주목할만 하다!

🌻 그 다음은, 파라미터 밑에 있는 RETURN절을 살펴보자!

RETURN 자료형

RETURN 절에서 조심해줄 부분은!

뒤에 ; (세미 콜론)이 붙지 않는다는 점이다!

이 부분을 통해서는 반환 값의 자료형을 지정해줄 수 있다!

🌻 다음은, 실행부의 RETURN 문을 살펴보자!

BEGIN 실행부 RETURN(반환값);

위의 RETURN 문에서는 함수의 반환값을 지정해줄 수 있다!

이제 간단한 예시를 통해서 함수라는 저장 서브 프로그램을 접해보자!

CREATE OR REPLACE FUNCTION FUNC_AFTERTAX(
    SAL IN NUMBER
)
RETURN NUMBER
IS
    TAX NUMBER:=0.05;
BEGIN
    RETURN (ROUND(SAL-(SAL*TAX)));
END FUNC_AFTERTAX;
/

위의 경우는 FUNC_AFTERTAX라는 함수 프로그램인데, 파라미터로 NUMBER형의 IN 모드 파라미터 SAL을 이용하며, NUMBER형태로 지불한 세금에서 다시 돌려받을 금액을 반환하는 형태이다!

위와 같이 함수를 이용할 때에는 꼭!! RETURN을 통해서 최소 하나의 반환은 있어야 한다!

❓ 함수는 어떻게 실행할까요 ❓

DECLARE
	반환값을_저장할_변수 자료형;
BEGIN
	반환값을_저장할_변수 := 함수이름(IN 파라미터에 대한 값);
END;
/

DECLARE
    AFTERTAX NUMBER;
BEGIN
    AFTERTAX := FUNC_AFTERTAX(3000);
    DBMS_OUTPUT.PUT_LINE('AFTER TAX: '||AFTERTAX);
END;
/

AFTER TAX: 2850

그리고! 함수는 DUAL 테이블을 이용해서 SQL문에서도 실행할 수 있다!

SELECT 함수명(IN 파라미터 값)
FROM DUAL;

SELECT FUNC_AFTERTAX(3000)
FROM DUAL;

SQL 문에서 함수 실행하기

또한, 함수는 값을 반환한다는 점에서,

함수에 정의한 파라미터의 자료형 == 테이블에서의 자료형

이면, 내장함수처럼 특정 열 혹은 데이터 간 연산 가공된 데이터 입력도 가능하다!

함수를 이용해서 테이블에 값 입력하기

❓ 함수 삭제하기

DROP FUNCTION 함수이름;

DROP FUNCTION FUNC_AFTERTAX;

Function FUNC_AFTERTAX이(가) 삭제되었습니다.

패키지

업무나 기능 면에서 연관성이 높은 여러 개의 PL/SQL 서브 프로그램을 그룹화(=하나의 논리 그룹으로 묶는 것)하여 통합 및 관리하는 데 사용하는 객체

[프로시져, 함수 등 : 각각 개별 기능 수행을 위해서 제작 후 따로 저장했음]

🌟 장점 - 모듈성, 쉬운 응용 프로그램 설계, 정보 은닉, 기능성 향상, 성능 향상

패키지에 포함된 모든 서브 프로그램은 메모리에 한 번에 로딩되는데, 로딩 후 호출은 디스크 I/O(=물리적 디스크에 관해서 입출력 수행을 하거나 읽고 쓰는 행위)를 일으키지 않기 때문에 성능이 향상됨!
패키지는 크게 명세(specification)과 본문(body)로 나누어 제작!

🌺 패키지 명세

패키지에 포함할 변수, 상수, 예외, 커서, PL/SQL 서브프로그램을 선언하는 용도로 사용
패키지 명세에서 선언한 여러 객체는 패키지 내부, 외부 모두에서 참조 가능!

CREATE [OR REPLACE] PACKAGE 패키지_이름
IS|AS
	서브프로그램을 포함한 다양한 객체 선언
	--서브프로그램의 경우
	서브프로그램_종류 서브프로그램_이름[(파라미터_이름 자료형) RETURN 자료형];
END [패키지이름];

CREATE OR REPLACE PACKAGE PKG_EX
IS
    SPEC_NO NUMBER:=10;
    FUNCTION FUNC_AFTERTAX(SAL NUMBER) RETURN NUMBER;
    PROCEDURE PRO_EMP(IN_EMPNO IN EMP.EMPNO%TYPE);
    PROCEDURE PRO_DEPT(IN_DEPTNO IN DEPT.DEPTNO%TYPE);
END;
/

🌻 이미 생성되어 있는 패키지 명세의 코드 혹은 선언한 서브 프로그램을 확인하는 방법

(A) USER_SOURCE 데이터 사전 뷰 조회

SELECT ~
FROM USER_SOURCE
[WHERE NAME='~'];

USER_SOURCE 데이터 사전 뷰- 패키지 명세의 코드 확인

USER_SOURCE 데이터 사전 뷰 - 선언된 서브 프로그램 확인(위의 사진은 해당 부분에 대한 행을 보여주고 있지는 않음)

(B) DESC 명령어 - 디비버에서는 사용이 어려움

DESC 패키지이름;

DESC 패키지이름-패키지 명세 정보 확인

🌺 패키지 본문

패키지 명세에서 선언한 서브 프로그램의 코드를 작성
패키지 명세에 선언하지 않은 객체 및 서브프로그램 정의도 가능!
패키지 본문에만 존재하는 프로그램은 패키지 내부에서만 사용 가능!

CREATE [OR REPLACE] PACKAGE BODY 패키지_이름
IS | AS
	패키지 명세에서 선언한 서프 프로그램 및 객체 정의
	경우에 따라 패키지 명세에 존재하지 않는 객체 및 서브프로그램도 정의 가능(BUT 패키지
	내부에서만 사용 가능)
END [패키지 이름];

🌟 패키지 명세에서 선언한 서브프로그램을 정의하는 방식은 기존에 정의하는 방식에서

CREATE OR REPLACE ~

를

서브프로그램_종류 서브프로그램_이름[(파라미터_이름 자료형) RETURN 자료형]

으로 변경하여 적용하였다!

CREATE OR REPLACE PACKAGE BODY PKG_EX
IS
    --패키지 본문에서 생성한 객체
    BODY_NO NUMBER:=10;
    
    FUNCTION FUNC_AFTERTAX(SAL NUMBER) RETURN NUMBER
        IS
            TAX NUMBER:=0.05;
        BEGIN
            RETURN (ROUND(SAL-(SAL*TAX)));
        END FUNC_AFTERTAX;
    
    PROCEDURE PRO_EMP(IN_EMPNO IN EMP.EMPNO%TYPE)
        IS
            OUT_ENAME EMP.ENAME%TYPE;
            OUT_SAL      EMP.SAL%TYPE;
        BEGIN
            SELECT ENAME, SAL INTO OUT_ENAME, OUT_SAL
            FROM EMP
            WHERE EMPNO=IN_EMPNO;
    
            DBMS_OUTPUT.PUT_LINE(IN_EMPNO || '      '||OUT_ENAME||'     '||OUT_SAL);
        END PRO_EMP;
        
        PROCEDURE PRO_DEPT(IN_DEPTNO DEPT.DEPTNO%TYPE)
            IS
                OUT_DNAME DEPT.DNAME%TYPE;
                OUT_LOC     DEPT.LOC%TYPE;
            BEGIN
                SELECT DNAME, LOC INTO OUT_DNAME, OUT_LOC
                FROM DEPT
                WHERE DEPTNO=IN_DEPTNO;
                
                DBMS_OUTPUT.PUT_LINE(IN_DEPTNO||'       '||OUT_DNAME||'         '||OUT_LOC);
            END PRO_DEPT;
END;
/

🌟 서브 프로그램 오버로드

기본적으로 서브 프로그램 이름은 중복될 수 없지만,

같은 패키지에서 사용하는 파라미터의 갯수, 자료형, 순서가 다를 경우

이름이 같은 서브프로그램을 정의할 수 있다! 이를 “서브 프로그램 오버로드 subprogram overload” 라고 한다!

같은 기능을 수행하는 여러 서브 프로그램이 입력 데이터를 각각 다르게 정의하고자 할 때 사용!
자바에서의 오버로딩과 같은 개념!
서브프로그램 종류가 같아야 오버로드가 가능!

◀️ 자바에서 오버로딩이 형이 다르면 오버로딩이라고 할 수없는 경우처럼!

서브 프로그램 오버로드는 명세에 선언 후 본문에서 정의하는 방식으로 사용이 되는데, 이렇게 되면 서브프로그램 내외부에서 접근하기 좋을것 같다!

CREATE [OR REPLACE] PACKAGE 패키지 이름
IS|AS
	서브프로그램종류A 서브프로그램 이름B(파라미터 정의);
	서브프로그램종류A 서브프로그램 이름B(파라미터의 갯수, 자료형, 순서가 다르게 정의);
END [패키지 이름];

-패키지 명세

CREATE OR REPLACE PACKAGE PKG_OVL
IS
    PROCEDURE PRO_EMP(IN_EMPNO IN EMP.EMPNO%TYPE);
    PROCEDURE PRO_EMP(IN_ENAME IN EMP.ENAME%TYPE);
END;
/

-패키지 본문

CREATE OR REPLACE PACKAGE BODY PKG_OVL
IS
    PROCEDURE PRO_EMP(IN_EMPNO IN EMP.EMPNO%TYPE)
        IS
            OUT_ENAME EMP.ENAME%TYPE;
            OUT_SAL     EMP.SAL%TYPE;
        BEGIN
            SELECT ENAME, SAL INTO OUT_ENAME, OUT_SAL
            FROM EMP
            WHERE EMPNO=IN_EMPNO;
            
            DBMS_OUTPUT.PUT_LINE(OUT_ENAME||'       '||OUT_SAL);
        END PRO_EMP;
    PROCEDURE PRO_EMP(IN_ENAME IN EMP.ENAME%TYPE)
        IS
            OUT_ENAME EMP.ENAME%TYPE;
            OUT_SAL      EMP.SAL%TYPE;
        BEGIN
            SELECT ENAME, SAL INTO OUT_ENAME, OUT_SAL
            FROM EMP
            WHERE ENAME=IN_ENAME;
            
            DBMS_OUTPUT.PUT_LINE(OUT_ENAME|| '      '||OUT_SAL);
        END PRO_EMP;
END;
/

🌹 패키지 사용하기

패키지 이름.객체 이름;

으로 익명 블록 내부에서 사용 가능!

익명 블록과 패키지이름.객체 이름으로 패키지 사용하기

🌹 패키지 삭제하기

(1) 패키지 명세와 본문을 한번에 삭제 DROP PACKAGE 패키지 이름;

(2) 패키지 본문만 삭제 DROP PACKAGE BODY 패키지 이름;

⚠️ 패키지에 포함된 객체 및 서브 프로그램만을 따로 수정, 삭제하는 것은 불가능하지만,

CREATE OR REPLACE로 패키지 내부에서 객체 및 서브프로그램을 먼저 수정, 삭제하는 것은 가능하다!

*오라클에서 제공하는 패키지: https://docs.oracle.com/cd/B28359_01/appdev.111/b28419/intro.htm#i1010103

🌟 패키지가 삭제되면 내부에 있던 객체, 서브 프로그램 모두 삭제된다!

트리거 Trigger

DB 내에서 이벤트가 발생할 경우, 자동으로 실행되는 기능을 정의한 서브 프로그램
예) 오라클 DB 가동 혹은 종료 시 DBA 등 관련 업무자에게 메일을 보내는 기능
특정 작업이나 이벤트 발생으로 다른 데이터 작업을 추가 실행하므로 잘못 사용 시 성능 저하의 원인이 될 수도 있으므로 주의하기!
트리거가 동작을 지정할 수 있는 이벤트(테이블, 뷰, 스키마, DB 수준)

-DML : INSERT, UPDATE, DELETE

-DDL : CREATE, ALTER, DROP

-DB 동작 : SERVERERROR, LOGON, LOGOFF,STARTUP, SHUTDOWN

🌟 장점 🌟

(1) 데이터 관련 작업을 좀 더 간편하게 수행 가능

(2) 제약조건만으로 구현이 어렵거나 불가능한 좀 더 복잡한 데이터 규칙을 정할 수 있어서 더 수준 높은 데이터 정의가 가능

(3) 데이터 변경과 관련된 일련의 정보를 기록해 둘 수 있으므로 여러 사용자가 공유하는 데이터 보안성과 안정성, 문제가 발생했을 때 대처 능력을 높일 수 있음

🌻 트리거 종류

트리거 종류

🌷 트리거가 발생할 수 있는 이벤트 종류에 따른 트리거 분류

A. DML 트리거 : DML 명령어 실행 시 작동하는 트리거

CREATE [OR REPLACE] TRIGGER 트리거_이름
BEFORE|AFTER
INSERT|UPDATE|DELETE ON 테이블이름
REFERENCING OLD as 변경전 값을 참조하는 변수명 | New as 변경 후 값을 참조하는 변수명
FOR EACH ROW WHEN 조건식
FOLLOWS 트리거이름2, 트리거이름3,...
ENABLE|DISABLE

DECLARE
	선언부
BEGIN
	실행부
EXCEPTION
	예외처리부
END;

BEFORE, AFTER는 트리거가 작동할 타이밍을 지정하는 데, -BEFORE: DML 명령어가 실행되기 전 시점 -AFTER : DML 명령어가 실행된 후 시점 에 트리거가 작동된다
아래는, 트리거가 작동할 DML 명령어를 작성하는데, 여러 종류의 DML 명령어 지정시에는 OR로 구분한다!

INSERT

UPDATE

DELETE ON 테이블이름

REFERENCING 부분은 DML로 변경되는 행의 변경 전 값과 변경 후 값을 참조하는 데 사용(생략 가능)

REFERENCING OLD as 변경전 값을 참조하는 변수명

New as 변경 후 값을 참조하는 변수명

FOR EACH ROW 부분은 생략하게 되면 DML 명령어 실행 시 한 번만 실행하고, 생략하지 않으면 DML 문장에 영향을 받는 행 별로 실행할 지 지정하여 실행하게 한다! 그리고 WHEN과 함께 사용 시, 영향을 받는 행 중 특정 조건에 해당되는 경우에만 실행하게 할 수 있다!

FOR EACH ROW WHEN 조건식

FOLLOWS는 관련 트리거의 실행 순서를 지정(생략 가능. 11g부터 사용 가능)

FOLLOWS 트리거이름2, 트리거이름3,…

ENABLE DISABLE은 트리거의 활성화 혹은 비활성화를 지정(생략 가능. 11g부터 사용 가능)

ENABLE

DISABLE

*INSERTING : INSERT 명령어가 실행된 경우 *UPDATING : UPDATE 명령어가 실행된 경우 *DELETING : DELETE 명령어가 실행된 경우

(EXERCISE)- BEFORE DML 트리거 생성

CREATE OR REPLACE TRIGGER trg_emp_nodml_weekend
BEFORE
INSERT OR UPDATE OR DELETE ON EMP_TRG
BEGIN
    IF TO_CHAR(SYSDATE, 'DY') IN ('토','일') THEN
        IF INSERTING THEN
            RAISE_APPLICATION_ERROR(-20000,'주말 사원 정보 추가 불가');
        ELSIF UPDATING THEN
            RAISE_APPLICATION_ERROR(-20001, '주말 사원 정보 수정 불가');
        ELSIF DELETING THEN
            RAISE_APPLICATION_ERROR(-20002,'주말 사원 정보 삭제 불가');
        ELSE
            RAISE_APPLICATION_ERROR(-20003,'주말 사원 정보 변경 불가');
        END IF;
    END IF;
END;
/

위의 경우, 현재 요일이 토요일이나 일요일인 경우, INSERT 혹은 UPDATE, DELETE 작업 전 이면

RAISE_APPLICATION_ERROR(-20000~-20999 사이 코드, 에러메시지)[사용자 에러 코드 함수]를 이용해서 에러를 발생시키는 트리거를 생성하였다

EMP_TRG 테이블에 UPDATE를 해보자(실행하는 지금이 마침 딱! 일요일이라서 적합하다 😄)

UPDATE EMP_TRG
SET    SAL=3500
WHERE EMPNO=7788;

그러면 위와 같이, “ORA-20001: 주말 사원 정보 수정 불가”와 같은 오류가 발생하는 것을 확인해볼 수 있다!

(EXERCISE)- AFTER DML 트리거 생성

먼저, EMP_TRG 테이블에 DML 명령어를 실행했을 경우, 테이블에 수행된 명령어 종류와 사원번호, 사용자 이름, 수행된 날짜를 기록하기 위한 로그 테이블을 생성해보자

CREATE TABLE EMP_TRG_LOG(
    TABLENAME VARCHAR2(10), --DML이 수행된 테이블 이름
    DML_TYPE    VARCHAR2(10), --DML 명령어 종류
    EMPNO        NUMBER(4), --DML 대상이 된 사원 번호
    USER_NAME VARCHAR2(30), --DML을 수행한 USER 이름
    CHANGE_DATE DATE --DML이 수행된 날짜
);

🌟 :NEW.필드명 은 필드에 새로 추가된 값을 의미!

🌟:OLD.필드명 은 변경 전 필드값을 의미!

SYS_CONTEXT(‘NAMESPACE’, ‘Parameter’) -세션 정보를 얻어오는 함수 -namespace와 관계되는 파라미터 값을 반환 -오라클에서는 namespace로 USERENV사용 -파라미터는 여기를 참고 -SESSION_USER는 로그인에서 식별된 사용자의 사용자 이름 혹은 스키마 이름

CREATE OR REPLACE TRIGGER trg_emp_log
AFTER
INSERT OR UPDATE OR DELETE ON EMP_TRG
FOR EACH ROW
BEGIN
    IF INSERTING THEN
        INSERT INTO EMP_TRG_LOG
        VALUES('EMP_TRG', 'INSERT', :NEW.EMPNO,SYS_CONTEXT('USERENV','SESSION_USER'),SYSDATE);
        
    ELSIF UPDATING THEN
         INSERT INTO EMP_TRG_LOG
        VALUES('EMP_TRG', 'UPDATE', :OLD.EMPNO,SYS_CONTEXT('USERENV','SESSION_USER'),SYSDATE);       
    
    ELSIF DELETING THEN
         INSERT INTO EMP_TRG_LOG
        VALUES('EMP_TRG', 'DELETE', :OLD.EMPNO,SYS_CONTEXT('USERENV','SESSION_USER'),SYSDATE);
    END IF;
END;
/

이번에는 일련의 로그를 저장하는 테이블을 미리 만들어두고, 트리거가 DML 명령어 발생 후에 작동되도록 하는데, -INSERT 작업이면, 새로운 EMPNO값이 대상이므로 :NEW를 이용해서 EMPNO에 기록을 하고 그 외에는 이전 EMPNO값이 대상이므로 :OLD를 이용해서 EMPNO에 기록을 하였다

-DML을 수행한 사용자 이름은 세션 객체를 얻어오는 SYS_CONTEXT를 통해서 사용자의 이름을 얻어오도록 하였다

-그리고 FOR EACH ROW로, 영향을 받는 행마다 작동될 수 있도록 하였다

이제 INSERT하고 COMMIT을 해보자

위의 일련의 작업을 하게 되면, EMP_TRG의 최하단에는 사원번호가 9999, 이름이 TEST인 레코드가 추가되고, EMP_TRG_LOG에서는 해당 작업 요약을 확인해볼 수 있다!

이번에는 UPDATE를 수행해보자

그러면, 이번에는 사원번호가 9999인 사람들이 3분이 계셨던 것 같다!

그래서 UPDATE 작업 갯수만큼 EMP_TRG_LOG 내부 데이터도 추가되었고, 현재까지의 작업을 모두 확인해볼 수 있다!

🌟 트리거 정보 조회

USER_TRIGGERS 데이터 사전 뷰를 이용!

SELECT TRIGGER_NAME, TRIGGER_TYPE, TRIGGERING_EVENT, TABLE_NAME, STATUS
FROM USER_TRIGGERS;

트리거 정보 조회- 데이터 사전 뷰 USER_TRIGGERS

위의 경우, 각 행별 이벤트 발생 후에 수행하므로 AFTER EACH ROW가 트리거 TYPE으로 확인되었다. 그리고 활성화된 트리거이면서 DML 작업 이벤트를 수행할 때 그 대상이 EMP_TRG 테이블에 대한 것임을 확인해볼 수 있다!

🌟 트리거 변경

-특정 트리거 활성화 혹은 비활성화

ALTER TRIGGER 트리거_이름 ENABLE

DISABLE;

-특정 테이블과 관련된 모든 트리거 상태를 활성화 혹은 비활성화

ALTER TABLE 테이블_이름 ENABLE

DISABLE ALL_TRIGGERS;

🌟 트리거 삭제

DROP TRIGGER 트리거_이름;

Twitter Facebook LinkedIn

J. S. Jeong

44_[db]저장 서브 프로그램

저장 서브 프로그램

You May Also Enjoy

1_[spring]rest api 구축에 힘을 실어주는 hateoas

2_[spring jpa]m to n 연관관계 중간 테이블을 엔티티로 관리하는 경우

1_[spring jpa]m to n 연관관계 중간 테이블을 엔티티로 관리하지 않는 경우

2_[spring]websockethandler를 이용한 웹소켓 서버 구현