소스플로우

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

-- 1. 예외처리가 필요한 이유

-- 1) 오라클 에러메세지를 사용자의 편의에 따라 출력할 수 있다. 

-- 2) 프로그램을 수행되게 할 때, 몇가지 오류가 있는 data 때문에 

-- 전체 프로그래밍이 실패하는 현상을 방지하기 위해서 필요하다.

-- 2. 예외의 종류 3가지 

-- 1) 미리 정의 해놓은 예외처리

-- 2) 미리 정의하지 않은 예외처리

-- 3) 사용자 정의 예외처리

-- 3. 예외 트랩에 대한 함수

-- 1) SQLCODE 오류코드에 대한 숫자값 반환

-- 2) SQLERRM 오류번호와 연관된 메시지를 반환 ★

예제 1. 미리 정의해놓은 예외처리를 이용해서 사원번호를 입력하였을 때,

-- 해당사원은 없습니다. 라는 메세지를 출력하게 하라. (월급출력)

SET SERVEROUTPUT ON;

ACCEPT P_EMP_NO PROMPT '사원번호를 입력해주세요';

DECLARE 

  V_EMP_NO NUMBER(8):= &P_EMP_NO;

  V_SAL NUMBER(12) := 0;

BEGIN 

  SELECT SAL 

  INTO V_SAL

  FROM TB_LS_EMP867

  WHERE EMP_NO = V_EMP_NO;

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

  EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('프로시저 처리도중 예외가 발생하였습니다.');

  SYS.DBMS_OUTPUT.PUT_LINE ('Error Code ::: ' || TO_CHAR(SQLCODE));

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

END;

/

--

-- 1. SET VERIFY OFF : PL/SQL 실행시 메시지를 출력하지 않는 설정 

-- 2. 변수이름 지정방법

-- 1) 문자로 시작

-- 2) 문자 + 숫자 가능 

-- 3) 특수문자 $,_,# 포함

-- 4) 변수의 길이는 30자 이하 

-- 5) 오라클 예약어는 변수명으로 사용할 수 없음

-- 3. 타입설정 방법

-- 1) 현재까지 PL/SQL 작성의 문제점 : 컬럼길이를 몰라서 임의로 정해버리는 문제점

-- 2) 해결 : %TYPE 속성

-- 위와 같이 데이터 타입을 선언하지 않으면, 추후에 데이터 타입을 변경할때 마다 

수반하는 작업이 많아진다. 

-- 4. NUMBER 

-- 변수 선언 시, NUMBER(7,2)의 의미는 전체 개수 7개, 소수점 개수 2개

-- ex) 12345.12

-- 5. 불린변수 선언방법

-- flaf BOOLEAN := FALSE;

-- 6. 데이터 타입의 종류 두가지 

-- 1) composite datatype

-- 2) scalar datatype

-- 7-1. 변수의 데이터 유형 <scalar datatype> : 단일 값을 보유하며 내부 구성요소가 없음.

-- 1) 문자: varchar2

-- 2) 숫자: number

-- 3) 날짜: date

-- 4) 부울: boolean --> true, false, null

-- 7-2. 변수의 데이터 유형 <cmposite datatype> : 다중 값을 보유하며 내부 구성요소가 있음.

-- 1) 레코드(Record): PL/SQL 레코드

-- 2) 컬렉션(Collection): 테이블 같은 것

-- 8. LOB (대형객체) 데이터 유형 변수

-- LOB 범주의 데이터 유형 (BLOB, CLOB 등)을 사용하여 데이터베이스 블록 크기에따라 

-- 최대 127TB까지 텍스트, 그래픽, 이미지, 비디오 클립등의 구조화되지 않은 

데이터 블록을 저장할 수 있다.

-- 1) CLOB : 대형 문자 데이터 블록을 저장하는데 사용

-- 2) BLOC : 대형 바이너리 객체를 데이터베이스에 저장하는데 사용

-- 3) BFILE : 대형 바이너리 파일을 데이터베이스 외부에 저장하는데 사용

-- 4) NCLOB : 대형 블록의 단일 바이트 또는 고정 너비 멀티바이트 

NCHAR 유니코드 데이터를 데이터베이스에 저장하는데 사용

-- 9. declare 외부에 선언한 변수 : 외부변수 / declare 내부에 선언한 변수 :  내부변수 

-- 10. 오라클 함수 2가지

-- 1. 단일행 함수: 문자, 숫자, 날짜, 변환, 일반함수

-- 2. 복수행 함수 (그룹함수): max, min, avg, sum, count

-- ★ 프로시저문에서 사용할 수 있는 함수는 단일행 함수이며,

-- ★ 프로시저문에서 사용할 수 없는 함수는 decode 및 그룹함수이다. 

-- 11. 시퀀스 생성해보기.

-- ex) 시작문자가 1이고 최대숫자가 1000인 시퀀스를 생성하시오.

 CREATE SEQUENCE SEQ_TEST

 START WITH 1 

 INCREMENT BY 1

 MAXVALUE 1000;

-- 결과확인 :  

  SELECT SEQ_TEST.NEXTVAL FROM DUAL;

-- 12. TB_LS_EMP867 테이블과 동일한 구조의 테이블 생성하고 

데이터를 이관하시오. (테이블명: TB_EMP_MIG_TEST1)

CREATE TABLE TB_EMP_MIG_TEST1 

AS 

SELECT * FROM TB_LS_EMP867;

DELETE FROM TB_EMP_MIG_TEST1;

SELECT * FROM TB_EMP_MIG_TEST1;

-- 13. 사원번호를 물어보게 하고 사원번호를 입력하면, 해당 사원의 모든 컬럼의 

정보가 TB_EMP_MIG_TEST1에 이관되도록 프로시저를 작성하시오.

SET SERVEROUTPUT ON;

SET TIMING ON; -- 해당 쿼리 및 프로시저 속도 체크

SET VERIFY OFF; 

CREATE OR REPLACE PROCEDURE PR_TEST_INST_1

(P_EMP_NO NUMBER)

IS

      -- 변수를 선언한다.

    V_EMP_NO NUMBER(10) := &P_EMP_NO;

    start_time NUMBER := 0;

    end_time NUMBER := 0;

  BEGIN

    INSERT INTO TB_EMP_MIG_TEST1 

      SELECT * FROM TB_LS_EMP867

      WHERE EMP_NO = V_EMP_NO;  

    -- ☆ SQL%rowcount 사용

    DBMS_OUTPUT.PUT_LINE( SQL%rowcount || '건이 갱신되었습니다.');  

  END;

  /

EXEC PR_TEST_INST_1(82);

1. 익명블럭 ( anonymous PL/SQL block )

- 이름이 없는 pl/sql

- DB에 이름을 가지고 저장되지 않는 형식

- 아래의 pl/sql 종류는 이름을 가지고 db에 저장되는 형식

2. Procedure 

3. Function

4. Trigger

5. Package