소스플로우

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