소스플로우

1. 패키지를 사용해야 하는 이유

 1) 정보 은닉화

 2) 유지보수의 용이성 

 3) 성능 향상

 4) 오버로딩 가능 

2. 패키지 구성요소

 1) 명세 (spec) : body 에서 구현 할 프로시저, 함수, 전역변수의 이름을 선언

 2) 몸체 (body): 실제 구현코드 ( 프로시저 , 함수 ) 

3. 패키지의 오버로딩 명세 (spec) 예제 

CREATE OR REPLACE PACKAGE OVER_PACK

IS

PROCEDURE ADD_DEPT

( P_DEPT_NO IN DEPT.DEPT_NO%TYPE ,

 P_DEPT_NM IN DEPT.DEPT_NM%TYPE DEFAULT 'UNKNOWN',

 P_LOC IN DEPT.LOC%TYPE DEFAULT 0);

PROCEDURE ADD_DEPT

( P_DEPT_NM IN DEPT.DEPT_NM%TYPE DEFAULT 'UNKNOWN',

 P_LOC IN DEPT.LOC%TYPE DEFAULT 0);

END OVER_PACK;

/

CREATE TABLE LOGIN_INFO 

  ( USER_ID VARCHAR2(20),

    LOGIN_DATE DATE,

    ACTION VARCHAR2(50) );

1. 오라클에 접속할 때마다 LOGIN_INFO 테이블에 접속정보가 남겨지게 하시오.

CREATE OR REPLACE TRIGGER LOGIN_INFO

  AFTER LOGON ON DATABASE

BEGIN 

  INSERT INTO LOGIN_INFO

  VALUES ('CHPARK',TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS'),'로그인 액션 취함');

END;

/

2. DB를 SHUTDOWN 하기 전에 LOGIN_INFO 테이블에 접속정보가 남겨지게 하시오.

CREATE OR REPLACE TRIGGER LOGIN_INFO

 BEFORE SHUTDOWN ON DATABASE

BEGIN 

  INSERT INTO LOGIN_INFO

  VALUES ('CHPARK',TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS'),'로그인 액션 취함');

END;

/

1. Trigger (트리거) 의 종류

1) DML 트리거

2) DDL 트리거

3) DB  작업에 대한 트리거 ( log on, log off, startup, shutdown ) 

트리거란 ?

1) INSERT, UPDATE, DELETE 문이 TABLE에 대해 묵시적으로 수행되는 PROCEDURE이다.

2) 트리거는 TABLE과는 별도로 DATABASE에 저장된다.

3) 트리거는 VIEW에 대해서가 아니라 TABLE에 관해서만 정의될 수 있다. 

4) 행트리거 : 컬럼의 각각의 행의 데이터 행 변화가 생길 때마다 실행되며, 그 데이터 행의 실제값을 제어할 수 있다. 

5) 문장 트리거 : 트리거 사건에 의해 단 한번 실행되며, 컬럼의 각 데이터 행을 제어할 수 없다. 

==========================================================

트리거 문법 : 

CREATE OR REPLACE TRIGGER 트리거명

BEFORE | AFTER

트리거이벤트[ INSERT | UPDATE | DELETE ] (OF 컬럼명) ON 테이블명

FOR EACH ROW

WHEN ( CONDITION )

[ PL/SQL BLOCK ]

==========================================================

- DML 트리거의 사용 예    

==========================================================

예제 1. 

CREATE OR REPLACE TRIGGER TRIG_TEST_EMP

  BEFORE INSERT OR UPDATE OF SAL ON TB_LS_EMP867

BEGIN 

-- IF문을 사용해서 트리거를 공통으로 사용할 수있다. 혼합트리거 

-- 사용자정의 예외처리

  IF (TO_CHAR(SYSDATE,'DY') IN ('토','일')) 

  THEN RAISE_APPLICATION_ERROR(- 20003,'토요일, 일요일에는 데이터 갱신을 할 수 없습니다.');

  END IF;

  IF (TO_CHAR(SYSDATE,'HH24MI') NOT BETWEEN '09:00' AND '11:30' ) 

  THEN RAISE_APPLICATION_ERROR(- 20005,' 오전업무시간 외에는 수정할 수 없습니다.');

  END IF;

END;

/ 

SQL >  SELECT * FROM TB_LS_EMP867;

SQL >  UPDATE TB_LS_EMP867 

  SET SAL = '3000000'

  WHERE EMP_NO = 20;  

SQL >  SELECT * FROM TB_LS_EMP867;

예제 2. 사원테이블의 부서를 갱신하면, 변경되기 전 월급과 변경 후의 월급이 TB_LS_EMP867_HIST

테이블에 입력되게 하시오.

2-1. 테이블 생성을 먼저 해주자. 

CREATE TABLE TB_LS_EMP867_HIST

( EMP_NO NUMBER(8), 

  OLD_DEPT_NO NUMBER(4), 

  NEW_DEPT_NO NUMBER(4), 

  WR_DTM VARCHAR2(14));

2-2. 트리거 생성을 해주자. 

CREATE OR REPLACE TRIGGER TRIG_EDIT_DEPT

AFTER UPDATE OF DEPT_NO ON TB_LS_EMP867

FOR EACH ROW

• :old - refers to Old Value
• :new - refers to New value

BEGIN  

INSERT INTO TB_LS_EMP867_HIST 

VALUES ( :OLD.EMP_NO, :OLD.DEPT_NO, :NEW.DEPT_NO, TO_CHAR(SYSDATE ,'YYYYMMDDHH24MISS') );

-- 트리거에서는 이곳에 COMMIT을 하면 에러가 발생한다. 

END;

/

SQL > UPDATE TB_LS_EMP_SET ....으로 테스트

============================================================

예제 3. TB_LS_EMP867_DELETE 테이블을 생성하고 TB_LS_EMP867 의 데이터를 삭제하면,

삭제한 레코드 중 EMP_NO, DEPT_NO, SAL 데이터가  TB_LS_EMP867_DELETE 테이블에 입력하되게 

TRIGGER를 생성하시오.

2-1. 테이블 생성을 먼저 해주자. 

CREATE TABLE TB_LS_EMP867_DELETE

( EMP_NO NUMBER(8), 

  DEPT_NO NUMBER(4), 

  SAL NUMBER(12), 

  WR_DTM VARCHAR2(14));

AFTER DELETE ON TB_LS_EMP867

FOR EACH ROW

• :old - refers to Old Value
• :new - refers to New value

BEGIN 

INSERT INTO TB_LS_EMP867_DELETE

VALUES (:OLD.EMP_NO, :OLD.DEPT_NO, :OLD.SAL, TO_CHAR(SYSDATE ,'YYYYMMDDHH24MISS') );

END;

/

SQL > DELETE FROM TB_LS_EMP_SET ....으로 테스트

CREATE OR REPLACE TRIGGER NO_DDL

BEFORE DROP OR ALTER ON chpark.SCHEMA

BEGIN 

RAISE_APPLICATION_ERROR(-20010,'테이블을 삭제하거나 변경할 수 없습니다.');

END;

/

SQL > DROP TABLE TB_LS_EMP867;  으로 테스트 

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;

-- 1. RANK 함수 알아보기 

-- 2. REF CURSOR (DECLARE 절에서 선언 X, BEGIN 절에서 선언하는 CURSOR ) 

-- 동적 SQL 에서 커서를 사용하기 위한 CURSOR ( 중요★☆ )

-- 1) 커서를 사용하려면 아래와 같은 절차가 진행된다. ★ 

-- CURSOR 선언 >> CURSOR OPEN >> CURSOR에 있는 내용 FETCH >> CURSOR CLOSE

-- 2) 아래의 예와 같이 선언하면 쿼리의 결과가 ○ 메모리 ○ 에 적재된다. ★

-- ex) CURSOR EMP_CURSOR IS 

--      SELECT EMP_NO, EMP_ID

--      FROM TB_LS_EMP867;

-- 3) 우리가 알고있는 커서 선언은 DECLARE 절 OR(IS..) 에서 수행하는데,

--    REF CURSOR는 커서선언이 BEGIN 절에서 이루어진다 ★

-- 4) 동적 SQL에서 커서를 사용하려면 REF CURSOR 를 사용해야한다.  ★

-- 예제 1) 부서번호를 입력하면 해당 부서번호인 사원들의 정보가 출력되게 하는 프로시저를 

--  생성하는데, 82번 부서번호를 입력해서 수행할때는 월급이 3000000원보다 큰 사원들의 정보만 

--  출력되게 하라.

-- BASIC LOOP과 동적SQL, REF CURSOR를 활용하여 여러개의 행이 결과로 출력될 수 있도록 해야한다.

CREATE OR REPLACE PROCEDURE PR_USING_REF_CURSOR

  (P_DEPT_NO NUMBER)

IS  

  /* 먼저 타입을 선언해야한다. 타입명 : EMP_REF_CSR */

  -- REF CURSOR를 사용하기 위한 TYPE을 선언해준다.

  TYPE EMP_REF_CSR IS REF CURSOR;  

  -- 위에서만든 REF CURSOR 타입의 EMP_REF_CSR TYPE으로 EMP_CURSOR 라는 REF CURSOR를 선언

  EMP_CURSOR EMP_REF_CRS; 

  -- EMP_RECORD 를 선언한다.

  EMP_RECORD TB_LS_EMP867%ROWTYPE;

  V_STMT VARCHAR2(200);

BEGIN 

  V_STMT := 'SELECT EMP_NO, EMP_NM, EMP_ID, DEPT_NO, SAL

, INCOME_LVL FROM TB_LS_EMP867 WHERE DEPT_NO = :BUSEO_NUM';

  OPEN EMP_CURSOR FOR V_STMT USING P_DEPT_NO; 

  FOR EMP_RECORD IN EMP_CURSOR LOOP

    IF EMP_RECORD.SAL > 3000000 

    THEN DBMS_OUT.PUT_LINE('고소득:'||EMP_RECORD.SAL);

    ELSE 

    DBMS_OUT.PUT_LINE('저소득:'||EMP_RECORD.SAL);

    END IF;

  END LOOP;

  EXCEPTION WHEN OTHERS THEN DBMS_OUT.PUT_LINE('Error Number: '||SQLERRM);

END;

/

1. 동적 SQL

-- 1) PL/SQL 을 실행할 때마다 SQL이 동적으로 변경된다는 의미

-- 2) 프로시저 생성할 때 SELECT문의 INTO절 없이 사용 가능해진다. 

-- 3) 프로시저 생성 시, DDL문이나 DCL문을 포함시킬 수 있다.

-- ( DDL : CREATE, ALTER, DROP, TRUNCATE, RENAME )

-- ( DCL : GRANT, REVOKE )

2. 동적 SQL을 사용하는 방법 2가지

-- 1) EXECUTE IMMEDIATE 절 사용하는 방법 (가장 많이 활용)

- INTO절 없는 SELECT 문장

- DDL 문을 PL/SQL에서 수행

- DML 문을 PL/SQL에서 수행

- ANONYMOUS PL/SQL을 수행

-- 2) DBMS_SQL 패키지를 이용하는 방법

예제 1-1. 부서번호를 입력해서 프로시저를 실행하면 아래와 같이 결과가 출력되게 하라. (동적 SQL 사용 x)

-- 부서번호 : 82

-- 토탈월급 : 

-- 최대월급 : 

-- 최소월급 :

-- 평균월급 : 

SET SERVEROUTPUT ON;

CREATE OR REPLACE PROCEDURE PR_LS_EMP745

  ( P_DEPT_NO NUMBER )

IS  

  V_DEPT_NO NUMBER(4) := 0;

  V_TOT_SAL NUMBER(12) := 0;

  V_MAX_SAL NUMBER(12) := 0;

  V_MIN_SAL NUMBER(12) := 0;

  V_AVG_SAL NUMBER(12) := 0;

BEGIN

  SELECT DEPT_NO, SUM(SAL), MAX(SAL), MIN(SAL), AVG(SAL)

  INTO V_DEPT_NO, V_TOT_SAL, V_MAX_SAL, V_MIN_SAL, V_AVG_SAL

  FROM TB_LS_EMP867

  WHERE DEPT_NO = P_DEPT_NO

  GROUP BY DEPT_NO;

  DBMS_OUTPUT.PUT_LINE('부서번호: ' || V_DEPT_NO);

  DBMS_OUTPUT.PUT_LINE('토탈월급: ' || V_TOT_SAL);

  DBMS_OUTPUT.PUT_LINE('최대월급: ' || V_MAX_SAL);

  DBMS_OUTPUT.PUT_LINE('최소월급: ' || V_MIN_SAL);

  DBMS_OUTPUT.PUT_LINE('평균월급: ' || V_AVG_SAL);

  EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Error Number :' || SQLERRM);

END;

/

EXEC PR_LS_EMP745(82);

#############################################################

바인드변수: http://egloos.zum.com/messfilm/v/2166470

#############################################################

예제 1-2. 부서번호를 입력해서 프로시저를 실행하면 아래와 같이 결과가 출력되게 하라. (동적 SQL 사용 O)

☆★☆★☆

CREATE OR REPLACE PROCEDURE PR_LS_EMP810

  ( P_DEPT_NO NUMBER )

IS 

    V_DEPT_NO NUMBER(4) := 0;

    V_TOT_SAL NUMBER(12) := 0;

    V_MAX_SAL NUMBER(12) := 0;

    V_MIN_SAL NUMBER(12) := 0;

    V_AVG_SAL NUMBER(12) := 0;

    -- 반드시 선언해줘야한다. CLOB 왜 안되는지 알아보기.

    V_STMT VARCHAR2(300);

BEGIN

    -- 동적 SQL Start

    -- :NAME 은 바인드 변수이다. 

    -- 바인드변수는 임의로 설정가능하며 변수명 앞에 콜론을 꼭 넣어줘야한다.

    V_STMT := 

    'SELECT DEPT_NO, SUM(SAL), MAX(SAL), MIN(SAL), AVG(SAL)  

      FROM TB_LS_EMP867 

      WHERE DEPT_NO = :BUSEO_NUM

      GROUP BY DEPT_NO';

    -- V_STMT 결과값을 수행하는데 P_DEPT_NO를 BUSEO_NUM으로 사용한다.

    EXECUTE IMMEDIATE V_STMT 

    INTO V_DEPT_NO, V_TOT_SAL, V_MAX_SAL, V_MIN_SAL, V_AVG_SAL

    USING P_DEPT_NO; 

    -- P_DEPT_NO: 바인드변수에 들어갈 변수이다.

    DBMS_OUTPUT.PUT_LINE('부서번호: ' || P_DEPT_NO);

    DBMS_OUTPUT.PUT_LINE('토탈월급: ' || V_TOT_SAL);

    DBMS_OUTPUT.PUT_LINE('최대월급: ' || V_MAX_SAL);

    DBMS_OUTPUT.PUT_LINE('최소월급: ' || V_MIN_SAL);

    DBMS_OUTPUT.PUT_LINE('평균월급: ' || V_AVG_SAL);

    -- ( 예외처리 ) 에러번호 출력: SQLERRM

    EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Error Number :' || SQLERRM);

END;

/

EXEC PR_LS_EMP810(82);