[Oracle] 14. PL/SQL

1. PL/SQL(Procedure Language Extension To SQL)

- 오라클 자체에 내장되어있는 절차적 언어
- SQL문장 내에서 변수의 정의, 조건처리, 반복처리, 예외처리등을 지원하여 SQL의 단점을 보완.
- 한번에 다수의 SQL문을 실행할 수 있다.

 

 

1) PL/SQL의 문법

- [선언부 (DECLARE SECTION)] : DECLARE로 시작, 변수나 상수를 선언 및 초기화한다.
- 실행부 (EXECUTABLE SECTION) : BEGIN으로 시작, SQL문 또는 제어문등의 로직을 기술하는 부분
- [예외처리부(EXECTPION SECTION)] : EXCEPTION으로 시작, 예외발생시 해결하기 위한 구문을 미리 기술해둘 수 있는 부분

 

 

▶ ① 서버 아웃풋 옵션을 ON (콘솔창에 내용을 출력해주는 옵션)

SET SERVEROUTPUT ON;

 

 

▶ ② 콘솔창 확인

 

 

---

2. DECLARE 선언부

: 변수 및 상수 선언하는 공간. 일반타입변수, 레퍼런스 변수, ROW 타입변수

 

1_1) 일반타입 변수 선언 및 초기화

 

    [표현식]

변수명 [CONSTANT] 자료형 [:=값];

 

 

 

☞ 위 /가 있어야 블록 종결로 간주되어서 다음 PL문 실행이 가능함

 

 

 

 

☞ 사용자에게 값을 직접 입력받아 이렇게 작성할 수도 있다.

 

 

 

1_2) 래퍼런스 타입 변수 선언 및 초기화

- (어떤 테이블의 어떤칼럼의 데이터타입을 참조해서 그타입으로 지정)

변수명 테이블명.컬럼명%TYPE;

 

 

▶ 사번이 200번인 사원의 사번, 사원명, 연봉을 각각 EID, ENAME, SAL 변수에 대입

 

 

 

 

▶ 예제 ①

- 레퍼런스 타입 변수로 EID, ENAME, JCODE, DTITLE을 선언하고 각각 자료형 EMPLOYEE(EMP_ID, EMP_NAME, JOB_CODE, SALARY) DEPARTMENT(DEPT_TITLE)들을 참조하도록 선언

 

- 사용자가 입력한 사번인 사원의 사번, 사원명, 직급코드, 급여, 부서명 조회 후 출력

 

 

 

☞ 사용자 입력창에 사번 "200" 입력하면 다음과 같은 결과값이 출력된다.

 

 

 

1_3) ROW 타입 변수 선언

: 테이블의 한 행에 대한 "모든" 컬럼값을 한꺼번에 담을 수 있는 변수

 

[변수명][테이블명]%ROWTYPE;

 

 

 

---

2. BEGIN 실행부

<조건문>

 

1) IF 조건식 THEN 실행내용

▶ 예제 ②

- 사번 입력받은 후 해당 사원의 사번, 이름, 급여, 보너스율(%)를 출력
단, 보너스를 받지 않는 사원은 보너스를 받지 않는 사웝입니다를 출력

 

 

 

 

2) IF[조건식] THEN [실행내용] ELSE [실행내용] END IF;

▶ 예제 ②

 

 

 

▶ 예제 ③

- 레퍼런스타입변수 (EID, ENAME, DTITLE, NCODE)
- 참조할 컬럼(EMP_ID, EMP_NAME, DEPT_TITLE, NATIONAL_CODE)
- 일반타입변수 변수 명 TEAM VARCHAR2(10)

 

- 사용자가 입력한 사번의 사원의 사번, 이름, 부서명, 근무국가코드 조회후 각 변수에 대입

- 조회한 코드의 NCODE의 값이 KO일경우 TEAM변수에 '한국팀' 대입. 그게 아닐경우 TEAM에 '해외팀' 대입

- 사번, 이름, 부서, 소속을 출력

 

 

 

 

3) IF 조건식1 ELSEIF 조건식 ELSIF 조건식2 THEN 실행내용 [ELSE 실행내용] END IF;

▶ 예제 ④

- 급여가 500만원 이상이면 고급
- 300 만원이상이면 중급
- 그외 초급
- 출력문 : 해당 사원의 급여등급은 XX 입니다.

 

 

 

4) CASE 비교대상자 WHEN 동등비교값1 THEN 결과값1 WHEN 비교값2 THEN 결과값2 ELSE 결과값 END;

 

---

5) BASIC LOOP 문(반복문)

[표현식]

LOOP         반복적으로 실행할 구문;                  * 반복문을 빠져나갈 수 있는 구문 필요     END LOOP;

 

 

▶ 반복문을 빠져나갈수 있는 구문(2가지)

    1) IF[조건식] THEN EXIT; ENDIF;
    2) EXIT WHEN [조건식] END LOOP;

 

 

▶ 1~5까지 순차적으로 1씩 증가하는 값을 출력

 

 

6) FOR LOOP문(반복문)

FOR 변수 IN [REVERSE] 초기값..최종값     LOOP         반복적으로 수행할 구문;     END LOOP;

 

 

▶ 1~5까지 순차적으로 1씩 증가하는 값을 출력

 

 

▶ 시퀀스와 반복문을 이용하여 테이블에 값 삽입하기

 

 

...

 

 

7) WHILE LOOP문

WHILE 반복문이 수행될 조건     LOOP         반복적으로 실행시킬 구문     END LOOP;

 

 

▶ 1~5까지 순차적으로 1씩 증가하는 값을 출력

 

 

▶ 실습

- 구구단 짝수단 출력
- 2 X 1 = 2
- 2 X 2 = 4
- 2단 4단 6단 8단 출력...

 

- 2_1) FOR LOOP문을 활용하여 출력

 

- 2_2) WHILE LOOP문을 활용해서 출력

...

 

 

 

---

3. 예외처리부

▶ 예외(EXCEPTION) : 실행중 발생하는 오류

 

[표현식]

EXCEPTION         WHEN 예외명1 THEN 예외처리구문1;         WHEN 예외명2 THEN 예외처리구문2;         ...         WHEN OTHERS 예외처리구문N;

 

 

▶ 시스템 예외(오라클에서 미리 정의해둔 예외)

   - NO_DATE_FOUND : SELECT한 결과가 한행도 없는 경우
   - TOO_MANY_ROWS : SELECT한 결과 여러행인 경우.
   - ZERO_DIVIDE : 0으로 나눌때

   - DUP_VAL_ON_INDEX : 이미 존재하는 값이 있을 경우

 

 

▶ 사용자가 입력한 수로 나눗셈 연산한 결과를 출력

☞ 0으로 나눗셈시 에러가 발생하는데 그에 대해 예외처리 구문을 작성하여 실행

 

 

 

▶ UNIQUE 제약조건 위배

 

☞ 이미 존재하는 사원이 있으면 "이미 존재하는 사번입니다" 출력