SQL - PL/SQL

PL/SQL

• Procedural Language extension to SQL
• 오라클 자체에 내장되어 있는 절차적 언어
• SQL의 단점을 보완하여 SQL 문장 내에서 변수의 정의, 조건처리(IF), 반복처리(LOOP, FOR, WHILE) 등을 지원

---

PL/SQL 구조

구조	설명
DECLARE SECTION (선언부)	DECLARE로 시작. 변수나 상수를 선언하는 부분. 생략 가능
EXECUTABLE SECTION (실행부)	BEGIN으로 시작.제어문, 반복문, 함수 정의 등 로직 기술
EXCEPTION SECTION (예외처리부)	EXCEPTION으로 시작. 예외사항 발생 시 해결하기 위한 문장 기술

---

출력 기능 활성/비활성

• PUT_LINE이라는 프로시저를 이용하여 출력
• DBMS_OTUPUT 패키지에 포함 되어 있음

-- ON/OFF
SET SERVEROUTPUT ON;

---

선언부 DECLARE SECTION

• DECLARE로 시작
• 변수, 상수 선언
• 선언과 동시에 초기화 가능
• 변수, 상수 선언
  • 상수는 1) 일반 타입, 2) 레퍼런스 타입, 3) ROW 타입의 변수로 선언하여 사용

---

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

변수명 [CONSTANT] 자료형(크기) [:= 값];
-- CONSTANT : 상수
-- := 값 : 선언과 동시에 초기화

DECLARE
    EID NUMBER;
    ENAME VARCHAR2(30);
    PI CONSTANT NUMBER := 3.14;

---

---

1. 레퍼런스 타입 변수 선언 및 초기화

변수명 테이블명.컬럼명%TYPE;
-- %TYPE : 타입을 참조
-- 해당 테이블, 컬럼의 데이터 타입을 참조하여 변수 선언

DECLARE
    EID EMPLOYEE.EMP_ID%TYPE;
    ENAME EMPLOYEE.EMP_NAME%TYPE;
    SAL EMPLOYEE.SALARY%TYPE;

---

1. ROW 타입 변수 선언 및 초기화
   • 한 테이블의 여러 컬럼값을 한 번에 저장 가능
   • 모든 컬럼 조회 시 사용

변수명 테이블병%ROWTYPE;

DECLARE
    EMP EMPLOYEE%ROWTYPE;

---

실행부 EXECUTABLE SECTION

• BEGIN으로 시작
• 제어문, 반복문 함수 정의 등 로직 기술

---

1. 선택문 IF

IF

IF 조건식 THEN 
    실행 문장 
END IF;

BEGIN
    -- ...

    IF (BONUS = 0)
        THEN DBMS_OUTPUT.PUT_LINE('보너스가 없습니다.');
    END IF;

---

IF ~ ELSE

IF 조건식 THEN 
    실행 문장 
ELSE
    실행 문장
END IF;

BEGIN
    -- ...

    IF (BONUS IS NULL) THEN 
        DBMS_OUTPUT.PUT_LINE('보너스가 없습니다.');
    ELSE
        DBMS_OUTPUT.PUT_LINE('보너스 : ' || BONUS);    
    END IF;

---

IF ~ ELSEIF ~ ELSE

IF 조건식 THEN 
    실행 문장 
ELSIF 조건식 THEN
    실행 문장
    ...
[ELSE
    실행 문장]
END IF;

BEGIN
    SCORE := '&정수';
    
    IF ( SCORE >= 90 ) THEN
        GRADE := 'A';
    ELSIF ( SCORE >= 80 ) THEN
        GRADE := 'B';
    ELSIF ( SCORE >= 70 ) THEN
        GRADE := 'C';
    ELSIF ( SCORE >= 60 ) THEN
        GRADE := 'D';
    ELSE
        GRADE := 'F';
    END IF;

---

CASE

CASE 비교대상
    WHEN 비교값1 THEN 결과값1
    WHEN 비교값2 THEN 결과값2
    WHEN 비교값3 THEN 결과값3
    ...
    [ELSE 결과값N]
END;

BEGIN
    -- ...

    DNAME := CASE EMP.DEPT_CODE
        WHEN 'D1' THEN '인사관리부'
        WHEN 'D2' THEN '회계관리부'
        WHEN 'D3' THEN '마케팅부'
        WHEN 'D4' THEN '국내영업부'
        WHEN 'D5' THEN '해외영업1부'
        WHEN 'D6' THEN '해외영업2부'
        WHEN 'D7' THEN '해외영업3부'
        WHEN 'D8' THEN '기술지원부'
        WHEN 'D9' THEN '총무부'
    END;

---

1. 반복문 LOOP

BASIC LOOP

LOOP
    반복 실행 코드
    
    [반복문 종료 조건문]
    1) IF 조건식 THEN EXIT;
        END IF;
        
    2) EXIT WHEN 조건식;
    
END LOOP;

-- 1-5까지 1씩 증가
DECLARE
    NUM NUMBER := 1; -- 선언과 동시에 초기화
    
BEGIN
    LOOP
        DBMS_OUTPUT.PUT_LINE(NUM);
        
        NUM := NUM + 1;
        
        -- 1) 반복문 종료
        IF NUM = '6' THEN EXIT;
        END IF;
    END LOOP;

END;
/

---

WHILE LOOP

WHILE 조건식
LOOP
    반복 실행 코드
END LOOP;

-- 5-10까지 1씩 증가
DECLARE
    NUM NUMBER := 5;
BEGIN
    WHILE (NUM <= 10) -- 5보다 작을 때까지
    LOOP
        DBMS_OUTPUT.PUT_LINE(NUM);
        
        NUM := NUM + 1;
    END LOOP;
END;
/

---

FOR LOOP

FOR 변수 IN 초기값..최종값
LOOP
    반복 실행 코드
    
END LOOP;

-- 1-5까지 1씩 증가
BEGIN
    FOR NUM IN 1..5
    LOOP
        DBMS_OUTPUT.PUT_LINE(NUM);
        
    END LOOP;
END;
/

---

예외처리부 EXCEPTION SECTION

• EXCEPTION으로 시작
• 예외 발생 시 해결을 위한 문장 기술

EXCEPTION
    WHEN 예외1 THEN 예외 처리 코드1;
    WHEN 예외2 THEN 예외 처리 코드2;
    ...
    WHEN OTHERS THNEN 예외 처리 코드N

EXCEPTION
    WHEN ZERO_DIVIDE THEN DBMS_OUTPUT.PUT_LINE('0으로 나눌 수 없습니다.');

• 오라클 내에 정의 되어 있는 예외(시스템 예외)
  • NO_DATA_FOUND : SELECT문의 수행 결과가 없을 경우
  • TOO_MANY_ROWS : 하나의 행을 조회해야 하는데 SELECT문에서 여라 개의 행을 반환할 경우
  • ZERO_DIVIDE : 0으로 나눌 경우
  • DUP_VAL_ON_INDEX : UNIQUE 제약 조건을 가진 컬럼에 중복된 데이터가 INSERT 될 경우

---