소스플로우

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

SET SERVEROUTPUT ON;

CREATE OR REPLACE PROCEDURE PR_EMP_TEST9(P_DEPT_NO NUMBER)

    -- CURSOR 명 : EMP_CURSOR

    -- EMP_CURSOR 에 조건에 맞는 TB_LS_EMP867 테이블에서 가져온 데이터들을 담아준다.

IS

    CURSOR EMP_CURSOR IS

    SELECT EMP_NO, EMP_ID, EMP_NM, DEPT_NO, SAL

    FROM TB_LS_EMP867

    WHERE DEPT_NO = P_DEPT_NO;

BEGIN

    -- CURSOR OPEN 사용 X, FETCH 사용 X, EMP_RECORD 임의부여

    FOR EMP_RECORD IN EMP_CURSOR LOOP

       DBMS_OUTPUT.PUT_LINE(EMP_RECORD.EMP_NM || '님의 레코드는 다음과 같습니다. 

: ' || EMP_RECORD.EMP_NO || ' | ' || EMP_RECORD.EMP_ID || ' | ' || EMP_RECORD.DEPT_NO || ' | ' || EMP_RECORD.SAL );

      IF EMP_RECORD.SAL > 3000000 

      THEN INSERT INTO EMP745(EMP_NO, EMP_ID, EMP_NM, DEPT_NO, SAL, INCOME_LVL) 

-- TABLE의 컬럼에 대해 반드시 명시해주자. 

          VALUES (EMP_RECORD.EMP_NO, EMP_RECORD.EMP_NM, EMP_RECORD.EMP_ID, 

EMP_RECORD.DEPT_NO, EMP_RECORD.SAL, '고소득');

      ELSE 

      INSERT INTO EMP745(EMP_NO, EMP_ID, EMP_NM, DEPT_NO, SAL, INCOME_LVL)

-- TABLE의 컬럼에 대해 반드시 명시해주자. 

          VALUES (EMP_RECORD.EMP_NO, EMP_RECORD.EMP_NM, EMP_RECORD.EMP_ID,

EMP_RECORD.DEPT_NO, EMP_RECORD.SAL, '저소득');

      END IF;

    END LOOP;

END;

/

EXEC PR_EMP_TEST9(82);

SELECT * FROM EMP745;

DELETE FROM EMP745;

SET SERVEROUTPUT ON;

CREATE OR REPLACE PROCEDURE PR_EMP_TEST7(P_DEPT_NO NUMBER)

    -- CURSOR 명 : EMP_CURSOR

    -- EMP_CURSOR 에 조건에 맞는 TB_LS_EMP867 테이블에서 가져온 데이터들을 담아준다.

IS

    CURSOR EMP_CURSOR IS

    SELECT EMP_NO, EMP_ID, EMP_NM, DEPT_NO, SAL

    FROM TB_LS_EMP867

    WHERE DEPT_NO = &P_DEPT_NO;

    -- 변수를 선언한다.

    V_EMP_NO NUMBER(8) := 0;

    V_EMP_ID VARCHAR2(30);

    V_EMP_NM VARCHAR2(20);

    V_DEPT_NO NUMBER(4) := 0;

    V_SAL NUMBER(10) := 0;

BEGIN

  OPEN EMP_CURSOR;

    FETCH EMP_CURSOR INTO V_EMP_NO, V_EMP_ID, V_EMP_NM, V_DEPT_NO, V_SAL;

    WHILE EMP_CURSOR%FOUND LOOP

      DBMS_OUTPUT.PUT_LINE(V_EMP_NM || '님의 레코드는 다음과 같습니다. : ' || V_DEPT_NO || ' | ' || V_EMP_NM || ' | ' 

|| V_EMP_ID || ' | ' || V_DEPT_NO || ' | ' || V_SAL );

      --다음 커서를 가져와야하므로 빼먹어서는 안된다. 만약 빼먹으면 무한루프에 빠지게된다.

        IF V_SAL > 3000000 

        THEN INSERT INTO EMP745(EMP_NO, EMP_NM, EMP_ID, DEPT_NO, SAL, INCOME_LVL)

            VALUES(V_EMP_NO, V_EMP_NM, V_EMP_NM, V_DEPT_NO, V_SAL, '고소득');

        ELSE 

        INSERT INTO EMP745(EMP_NO, EMP_NM, EMP_ID, DEPT_NO, SAL, INCOME_LVL)

            VALUES(V_EMP_NO, V_EMP_NM, V_EMP_NM, V_DEPT_NO, V_SAL, '저소득');

        END IF;

      FETCH EMP_CURSOR INTO V_EMP_NO, V_EMP_ID, V_EMP_NM, V_DEPT_NO, V_SAL;

    END LOOP;

  CLOSE EMP_CURSOR;

END;

/

EXEC PR_EMP_TEST7(82);

SELECT * FROM EMP745;

DELETE FROM EMP745;

1. BASIC LOOP을 활용하여 EMP745 테이블의 레코드를 업데이트하여라.

SET SERVEROUTPUT ON;

CREATE OR REPLACE PROCEDURE PR_EMP_TEST5

  (P_DEPT_NO NUMBER)

IS

  -- CURSOR 명 : EMP_CURSOR

  -- EMP_CURSOR 에 조건에 맞는 TB_LS_EMP867 테이블에서 가져온 데이터들을 담아준다.

  CURSOR EMP_CURSOR IS

  SELECT EMP_NO, EMP_NM, EMP_ID, DEPT_NO, SAL

  FROM TB_LS_EMP867

  WHERE DEPT_NO = P_DEPT_NO;

  -- 변수를 선언한다.

    V_EMP_NO NUMBER(8) := 0;

    V_EMP_ID VARCHAR2(30);

    V_EMP_NM VARCHAR2(20);

    V_DEPT_NO NUMBER(4) := 0;

    V_SAL NUMBER(10) := 0;

BEGIN

  OPEN EMP_CURSOR;

    LOOP

      FETCH EMP_CURSOR INTO V_EMP_NO, V_EMP_ID, V_EMP_NM, V_DEPT_NO, V_SAL;

      EXIT WHEN EMP_CURSOR%NOTFOUND;

        IF V_SAL > 3000000 THEN 

        -- TABLE의 컬럼에 대해 반드시 명시해주자. 

  INSERT INTO EMP745(EMP_NO, EMP_ID, EMP_NM, DEPT_NO, SAL, INCOME_LVL) 

        VALUES (V_EMP_NO, V_EMP_ID, V_EMP_NM, V_DEPT_NO, V_SAL, '고소득');

        ELSE INSERT INTO EMP745(EMP_NO, EMP_ID, EMP_NM, DEPT_NO, SAL, INCOME_LVL)

        VALUES (V_EMP_NO, V_EMP_ID, V_EMP_NM, V_DEPT_NO, V_SAL, '저소득');

        END IF;

    END LOOP;

  CLOSE EMP_CURSOR;  

END;

/

SELECT * FROM EMP745;

EXEC PR_EMP_TEST5(82);

-- 조합 데이터 타입의 2가지 종류

 -- 1. 레코드 (RECORD)

 -- 2. 컬렉션 (COLLECTION)

-- CURSOR

-- 수행하는 SQL문의 결과를 처리하기 위한 메모리영역

 -- 1. 암시적 커서: SQL%rowcount와 같은 커서를 말한다.

 -- 2. 명시적 커서: 메모리를 올려놓고 빈곳에다가 올리고 싶은 데이터를 올린다. 

-- 2. CURSOR를 사용하지 않고 FOR LOOP을 사용하여 TB_LS_EMP867 테이블에서 레코드를 뽑아봐라

SET SERVEROUTPUT ON;

CREATE OR REPLACE PROCEDURE PR_EMP_TEST2

(P_DEPT_NO NUMBER)

IS

    -- CURSOR 명 : EMP_CURSOR

    -- EMP_CURSOR 에 조건에 맞는 TB_LS_EMP867 테이블에서 가져온 데이터들을 담아준다. 

    CURSOR EMP_CURSOR IS

    SELECT EMP_NO, EMP_NM, EMP_ID, DEPT_NO, SAL

    FROM TB_LS_EMP867

    WHERE DEPT_NO = P_DEPT_NO;

BEGIN

      -- CURSOR를 특별하게 사용하지 않고 FOR ..  LOOP을 돌려보자.

      FOR EMP_RECORD IN EMP_CURSOR LOOP

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

        DBMS_OUTPUT.PUT_LINE(EMP_RECORD.EMP_NM);

        DBMS_OUTPUT.PUT_LINE(EMP_RECORD.EMP_NO);

        DBMS_OUTPUT.PUT_LINE(EMP_RECORD.EMP_ID);

        DBMS_OUTPUT.PUT_LINE(EMP_RECORD.DEPT_NO);

        DBMS_OUTPUT.PUT_LINE(EMP_RECORD.SAL);

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

      END LOOP;

END;

/

EXEC PR_EMP_TEST2(82);

---- 조합 데이터 타입의 2가지 종류

 -- 1. 레코드 (RECORD)

 -- 2. 컬렉션 (COLLECTION)

-- CURSOR

-- 수행하는 SQL문의 결과를 처리하기 위한 메모리영역

 -- 1. 암시적 커서: SQL%rowcount와 같은 커서를 말한다.

 -- 2. 명시적 커서: 메모리를 올려놓고 빈곳에다가 올리고 싶은 데이터를 올린다. 

1. CURSOR를 사용하지 않고 FOR LOOP을 사용하여 TB_LS_EMP867 테이블에서 레코드를 뽑아봐라

SET SERVEROUTPUT ON;

ACCEPT P_DEPT_NO PROMPT '원하는 부서를 입력하고 FOR..LOOP과 CURSOR를 이용하여 데이터를 추출해보아라.';

DECLARE 

    -- CURSOR 명 : EMP_CURSOR

    -- EMP_CURSOR 에 조건에 맞는 TB_LS_EMP867 테이블에서 가져온 데이터들을 담아준다.

    CURSOR EMP_CURSOR IS 

    SELECT EMP_NO, EMP_NM, EMP_ID, DEPT_NO, SAL

    FROM TB_LS_EMP867

    WHERE DEPT_NO = &P_DEPT_NO;

BEGIN

      -- CURSOR를 특별하게 사용하지 않고 FOR ..  LOOP을 돌려보자.

-- FOR 와 IN 사이의 EMP_RECORD 의경우 임의로 작명해도된다. : EMP_CURSOR 의 값들을 담아놓은 객체라보면된다.

      FOR EMP_RECORD IN EMP_CURSOR LOOP

        DBMS_OUTPUT.PUT_LINE(EMP_RECORD.EMP_NM || '님의 레코드는 다음과 같습니다. 

        : ' || EMP_RECORD.EMP_NO || ' | ' || EMP_RECORD.EMP_ID || ' | ' || EMP_RECORD.DEPT_NO || ' | ' || EMP_RECORD.SAL );

      END LOOP;

END;

/

-- EMP_CURSOR%FOUND : 커서에서 데이터가 발견되면 TRUE

-- EMP_CURSOR%NOTFOUND : 커서에서 데이터가 발견되지 않으면 TRUE  

SET SERVEROUTPUT ON;

CREATE OR REPLACE PROCEDURE PR_EMP_TEST2

(P_DEPT_NO NUMBER)

IS 

  -- CURSOR 명 : EMP_CURSOR

  -- EMP_CURSOR 에 조건에 맞는 TB_LS_EMP867 테이블에서 가져온 데이터들을 담아준다.

  CURSOR EMP_CURSOR IS

  SELECT EMP_NO, EMP_NM, EMP_ID, DEPT_NO, SAL

  FROM TB_LS_EMP867

  WHERE DEPT_NO = P_DEPT_NO;

  -- 변수를 선언한다.

  V_EMP_NO NUMBER(8) := 0;

  V_EMP_ID VARCHAR2(30);

  V_EMP_NM VARCHAR2(20);

  V_DEPT_NO NUMBER(4) := 0;

  V_SAL NUMBER(10) := 0;

BEGIN

  OPEN EMP_CURSOR;

    FETCH EMP_CURSOR INTO V_EMP_NO, V_EMP_NM, V_EMP_ID, V_DEPT_NO, V_SAL;

    WHILE EMP_CURSOR%FOUND LOOP

      DBMS_OUTPUT.PUT_LINE(V_EMP_NM || '님의 레코드는 다음과 같습니다.

 : ' || V_DEPT_NO || ' | ' || V_EMP_NM || ' | ' || V_EMP_ID || ' | ' || V_DEPT_NO || ' | ' || V_SAL );

      FETCH EMP_CURSOR INTO V_EMP_NO, V_EMP_NM, V_EMP_ID, V_DEPT_NO, V_SAL;

    END LOOP;

  CLOSE EMP_CURSOR;

END;

/

EXEC PR_EMP_TEST2(82);

-- EMP_CURSOR%FOUND : 커서에서 데이터가 발견되면 TRUE

-- EMP_CURSOR%NOTFOUND : 커서에서 데이터가 발견되지 않으면 TRUE  

SET SERVEROUTPUT ON;

ACCEPT P_DEPT_NO PROMPT '데이터가 필요한 부서를 입력하시오';

DECLARE 

    -- CURSOR 명 : EMP_CURSOR

    -- EMP_CURSOR 에 조건에 맞는 TB_LS_EMP867 테이블에서 가져온 데이터들을 담아준다.

    CURSOR EMP_CURSOR IS

    SELECT EMP_NO, EMP_ID, EMP_NM, DEPT_NO, SAL

    FROM TB_LS_EMP867

    WHERE DEPT_NO = &P_DEPT_NO;

    -- 변수를 선언한다.

    V_EMP_NO NUMBER(8) := 0;

    V_EMP_ID VARCHAR2(30);

    V_EMP_NM VARCHAR2(20);

    V_DEPT_NO NUMBER(4) := 0;

    V_SAL NUMBER(10) := 0;

BEGIN

  OPEN EMP_CURSOR;

    FETCH EMP_CURSOR INTO V_EMP_NO, V_EMP_ID, V_EMP_NM, V_DEPT_NO, V_SAL;

    WHILE EMP_CURSOR%FOUND LOOP

      DBMS_OUTPUT.PUT_LINE(V_EMP_NM || '님의 레코드는 다음과 같습니다. : ' || V_DEPT_NO || ' | ' 

|| V_EMP_NM || ' | ' || V_EMP_ID || ' | ' || V_DEPT_NO || ' | ' || V_SAL );

      FETCH EMP_CURSOR INTO V_EMP_NO, V_EMP_ID, V_EMP_NM, V_DEPT_NO, V_SAL; 

-- next()와 비슷하게 생각해라.

    END LOOP;

  CLOSE EMP_CURSOR;

END;

/