소스플로우

1. RAISE_APPLICATION_ERROR를 사용하여 오류코드 -20000부터 -20999의 범위 내에서 사용자 정의 예외를 만들 수 있다. 

- STEP 1: DECLARE  OR IS ... 예외의 이름을 선언 (선언절)

- STEP 2: RAISE문을 사용하여 직접적으로 예외를 발생시킨다. (실행절)

- STEP 3: 예외가 발생할 경우 해당 예외를 참조한다. (예외절)

ex) step 1: 예외의 이름을 선언 

sync_exec_day Exception;

V_SYNC_DAY VARCHAR2(4) := TO_CHAR(SYSDATE,'YD');

ex) step 2: RAISE 문을 사용하여 직접적으로 예외를 발생시킨다. 

IF V_SYNC_DAY IN ('목','일') THEN

RAISE sync_exec_day;

END IF;

ex) step 3: 예외가 발생할 경우 해당 예외를 참조한다.

EXCEPTION WHEN sync_exec_day THEN RAISE_APPLICATION_ERROR (-20001, '목,일요일에는 데이터를 갱신할 수 없습니다.' );

/* 예제 프로시저 */

SET SERVEROUTPUT ON;

DECLARE     

  V_SYNC_DAY   VARCHAR2(4) := TO_CHAR(SYSDATE,'DY');

  sync_exec_day EXCEPTION;  

BEGIN

  IF V_SYNC_DAY  IN ('토','일') THEN

    RAISE sync_exec_day ; 

  END IF;

  EXCEPTION

    WHEN sync_exec_day  THEN 

      RAISE_APPLICATION_ERROR(-20001,'토,일요일엔 데이터를 갱신할 수 없습니다.');

END;

/

1. ggusr 계정에 PR_EMP_TEST (프로시저)를 실행할 수 있는 권한을 부여하고  ggusr 세션에서 수행해보아라. 

1) 권한 부여하기 GRANT EXECUTE ON PR_EMP_TEST TO ggusr

2) SQL > connect ggusr/123123;

         SQL > EXEC PR_EMP_TEST;

2. AUTHID CURRENT USER

1) 더 엄격한 권한 관리가 가능하다. 

2) 프로시저를 실행할 수 있는 권한을 부여하더라도 TB_LS_EMP867 테이블을 조회할 수 있는 권한이 같이 부여되는 것은 아니다. 

즉,,

SQL > EXEC PR_EMP_TEST; ( 수행 O )

SQL > SELECT * FROM TB_LS-EMP867; ( 수행 X )

※ autonomous : 자율적인

※ PRAGMA : Compiler의 Preprocessor에게 지시/ 전달한다. 

In Oracle PL/SQL, PRAGMA refers to a compiler directive or "hint" it is used to provide an instruction to the compiler. The directive restricts member subprograms to query or modify database tables and packaged variables. Pragma directives are processed at compile time where they pass necessary information to the compiler; they are not processed at runtime.

The 5 types of Pragma directives available in Oracle are listed below:

1. PRAGMA AUTONOMOUS_TRANSACTION: This pragma can perform an autonomous transaction within a PL/SQL block between a BEGIN and END statement without affecting the entire transaction.


2. PRAGMA SERIALLY_REUSABLE: This directive tels Oracle that the package state is needed only for the duration of one call to the server. After the call is made the package may be unloaded to reclaim memory.


3. PRAGMA RESTRICT_REFRENCES: Defines the purity level of a packaged program. After Oracle8i this is no longer required.


4. PRAGMA EXCEPTION_INIT: This directive binds a user defined exception to a particular error number.


5. PRAGMA INLINE: (Introduced in Oracle 11g) This directive specifies that a subprogram call either is or is not to be inlined. Inlining replaces a subprogram call with a copy of the called subprogram.

3.  PRAGMA AUTONOMOUS_TRANSACTION 을 가지고 독립 트랜잭션 제어

1) 다른 주 트랜잭션에 의해 시작되는 독립적인 트랜잭션이다. 

     .... 좀 더 공부하기..

4. 함수 생성 시, parallel enable 힌트를 사용하는 이유가 무엇인가?

 - 병렬 처리를 하게되면 데이터를 빨리 검색할 수 있다. 

5. 함수 생성 시, deterministic 절을 사용하는 이유는 ? 

 - 컬럼의 갯수만큼 실행된다.

 - deterministic을 써서 함수를 생성하면, 같은 값이 들어오면 함수를 실행하지 않고 데이터만 출력해준다. 

참조: www.grubee.net/lecture/1075

3-3 패키지 실행

- 패키지의 실행은 패키지명 (ex : EMP_PKG) 다음에 점(.)을 찍고 프로시저나 함수 명을 적어주면 된다. 

SET SERVEROUTPUT ON;

-- 패키지 실행 

SQL > EXEC EMP_PKG.ALL_EMP_INFO;

SQL > EXEC EMP_PKG.ALL_EMP_INFO_2;

SQL > EXEC EMP_PKG.ALL_EMP_SAL;

SQL > EXEC EMP_PKG.SPC_DEPT_EMP_INFO (P_DEPT_NO IN NUMBER);

SQL > EXEC EMP_PKG.SPC_DEPT_EMP_SAL (P_DEPT_NO IN NUMBER);

create or replace PROCEDURE PR_POPULAR_MOVE_INTO_RCMD

(P_USER_NO NUMBER)

IS

    CURSOR RCMD_CURSOR IS 

        SELECT Y.RO, SUBSTR(Y.CRS_SESS_ID,1,7) CRS_ID, Y.CRS_SESS_ID, Y.WR_DTM

        FROM(

            SELECT ROWNUM RO, X.*

            FROM (

                  SELECT *

                  FROM TB_ST_CRS_STTS_DAY 

                  WHERE RCMD_KIND_CD = '2' 

                  AND WR_PER_NO = P_USER_NO

                  ORDER BY RCMD_DT, SORT_OR

                )X

           )Y 

      WHERE Y.RO <= 12;

    V_SORT_OR NUMBER(5) := 0;

    V_CRS_ID VARCHAR2(10);

    V_CRS_SESS_ID VARCHAR2(20);

    V_RCMD_DTM VARCHAR2(14);

    V_STMT VARCHAR2(200);

    V_CNT NUMBER(4);

-- BASIC LOOP 사용    

BEGIN

    /* 오늘 반영한 데이터가 있는지 조회 Start 

       동적 SQL 적용 (그룹함수 사용가능하다) */

    V_STMT :=

    'SELECT COUNT(CRS_ID) FROM TB_LS_CRS_RCMD 

WHERE USER_NO = 15771499 

AND SUBSTR(RCMD_DTM,1,8)  = TO_CHAR(SYSDATE,''YYYYMMDD'')';

    EXECUTE IMMEDIATE V_STMT

    INTO V_CNT;

    DBMS_OUTPUT.PUT_LINE('V_CNT:::'||V_CNT);

    /* End */

    IF V_CNT = 0 THEN

      OPEN RCMD_CURSOR;

        LOOP

          FETCH RCMD_CURSOR INTO V_SORT_OR, V_CRS_ID, V_CRS_SESS_ID, V_RCMD_DTM;

          -- EXIT의 위치는 반드시 여기로 (원하는 로직:INSERT 위)! 안그러면 Error발생한다.

          -- 무결성제약조건위배 :WHY? 마지막행이 중복실행되므로

          EXIT WHEN RCMD_CURSOR%NOTFOUND; 

--          DBMS_OUTPUT.PUT_LINE('=============================');

--          DBMS_OUTPUT.PUT_LINE('V_SORT_OR::'|| V_SORT_OR);

--          DBMS_OUTPUT.PUT_LINE('V_CRS_ID::'|| V_CRS_ID);

--          DBMS_OUTPUT.PUT_LINE('V_CRS_SESS_ID::'|| V_CRS_SESS_ID);

--          DBMS_OUTPUT.PUT_LINE('V_RCMD_DTM::'|| V_RCMD_DTM);

--          DBMS_OUTPUT.PUT_LINE('=============================');

--          DBMS_OUTPUT.PUT_LINE('INSERT INTO TB_LS_CRS_RCMD VALUES (15771499,' || V_CRS_ID || ', 04 , 15771499,' || V_CRS_SESS_ID || ', ,' || 

V_RCMD_DTM || ', 15771499 ,' || TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS') || ',2,' || V_SORT_OR || ')');

          INSERT INTO TB_LS_CRS_RCMD VALUES (15771499, V_CRS_ID, '04' , 15771499 , V_CRS_SESS_ID, '', V_RCMD_DTM, 15771499, 

TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS'), '2', V_SORT_OR);

        END LOOP;

      CLOSE RCMD_CURSOR;

    ELSE DBMS_OUTPUT.PUT_LINE('데이터가 이미 삽입되어있습니다.'); ROLLBACK;

    END IF;

    COMMIT;  -- 예외처리 전에 반드시 커밋을해야한다. 

    EXCEPTION

      WHEN OTHERS THEN

        DBMS_OUTPUT.PUT_LINE('Error Number: ' ||SQLERRM);

        ROLLBACK;

END;