Skip to main content
HashnodeMy dev journey

Command Palette

Search for a command to run...

Managing ORACLE database - PL/SQL

Updated
•27 min read
Managing ORACLE database - PL/SQL
H
Heesu Noh
Part of seriesDatabase Design and Construction

Contents

1️⃣PL/SQL
2️⃣PL/SQL 구성요소 (PL/SQL Components)
3️⃣PL/SQL 제어문(Control Structures)

PL/SQL Summary

PL/SQL (Procedural Language/Structured Query Language)은 SQL에 절차적 프로그래밍 언어의 기능을 확장한 언어로, SQL의 데이터 처리 능력과 일반 프로그래밍 언어의 제어 구조를 결합한 것이다.

✅ 주요 특징 (Key Features)

  1. SQL과 프로그래밍 언어의 결합:
    PL/SQL은 SQL의 데이터 처리 기능과 일반 프로그래밍 언어의 제어 흐름을 결합하여 데이터베이스 작업을 더 효율적으로 처리할 수 있도록 한다.

  2. 변수 및 상수 선언:
    변수와 상수를 선언하여 프로그램 내에서 값을 저장하고 변경할 수 있다.

  3. 조건문 (Control Statements):

    • IF문: 조건에 따라 다른 동작을 수행할 수 있다.

    • CASE문: 여러 조건을 한 번에 처리할 수 있으며, WHEN을 사용하여 조건을 지정한다.

  4. 반복문 (Loops):

    • FOR문: 반복 횟수가 정해져 있을 때 사용하며, 범위 내에서 반복된다.

    • LOOP문: 반복 조건을 명시적으로 설정할 수 있으며, 조건을 만족할 때까지 반복된다.

    • WHILE문: 주어진 조건이 true일 때만 반복하며, 조건이 false로 변하면 종료된다.

✅ PL/SQL 구성 요소 (PL/SQL Components)

  1. 변수와 상수 (Variables & Constants):
    PL/SQL에서는 변수와 상수를 선언하여 데이터를 저장하고 처리할 수 있다. 상수는 값이 변경되지 않는 값을 저장하는 데 사용된다.

  2. 콜렉션 (Collections):
    여러 값을 한 번에 저장할 수 있는 배열, 리스트 형태의 자료 구조이다.

  3. 레코드 (Records):
    여러 필드로 구성된 구조체와 같은 자료형으로, 관련된 데이터를 함께 저장할 수 있다. 레코드는 중첩되어 사용할 수 있게 된다.

✅ PL/SQL 제어문 (Control Structures in PL/SQL)

  • 조건문 (Conditional Statements):

    • IF문: 조건에 따라 다른 처리를 진행하는 구문이다.

    • CASE문: 여러 조건을 처리할 수 있는 구문으로, 여러 가지 경우의 수를 쉽게 다룰 수 있게 된다.

  • 반복문 (Loops):

    • FOR문: 반복 횟수가 정해져 있을 때 사용하며, 특정 범위 내에서 순차적으로 실행된다.

    • LOOP문: 종료 조건을 명시적으로 설정하여 조건이 맞을 때까지 반복을 실행한다.

    • WHILE문: 주어진 조건을 만족할 때만 반복하며, 조건이 false가 되면 종료된다.

정리 (Summary)

PL/SQL은 SQL의 데이터 처리 능력과 프로그래밍 언어의 흐름 제어 기능을 결합하여 강력한 데이터베이스 처리 시스템을 구축할 수 있게 해준다. 조건문과 반복문을 활용하여 복잡한 논리 처리를 가능하게 하고, 변수, 상수, 콜렉션, 레코드를 통해 데이터를 효율적으로 관리할 수 있게된다.

1️⃣PL/SQL

💡요약: PL/SQL (Procedural Language/Structured Query Language)은 SQL의 한계를 보완하여, 데이터베이스 내에서 논리적 흐름과 절차적 제어를 가능하게 하는 확장 언어이다. PL/SQL은 Oracle 데이터베이스에서 주로 사용되며, 다음과 같은 특징과 기능을 제공한다.

✅ PL/SQL의 특징

  1. 절차적 언어

    • SQL은 내가 원하는 것만 요구하면 되는 "비절차적" 언어라면, PL/SQL은 "절차적" 언어로 조건문, 반복문, 예외 처리 등을 지원한다.

    • 데이터베이스를 이용하다보면 "흐름"의 제어가 필요할 때가 있다. 조건에 따라서 A명령어 사용 후 B명령어 사용한다던지 일때다. PL/SQL은 논리적인 흐름 제어와 데이터 조작을 함께 처리할 수 있다.

  2. SQL과의 통합

    • PL/SQL은 SQL과 완벽하게 통합되어 있다. 데이터를 처리하는 SQL 명령어와 절차적 흐름을 조합하여 강력한 데이터베이스 작업이 가능하다.

  3. 트랜잭션 제어

    • 트랜잭션 단위로 작업이 수행되며, 명령어의 성공 또는 실패에 따라 데이터의 일관성을 보장한다.

  4. 보안 및 성능

    • 데이터베이스 내에서 직접 실행되므로 네트워크 지연이 없고, 효율적이다.

    • 데이터베이스에 저장된 PL/SQL 블록은 재사용이 가능하며, 보안성이 높다.

PL/SQL #1: 정의 및 특징 (Definition and Features of PL/SQL)

💡요약: PL/SQL은 SQL에 프로그래밍 언어(Programming Language)의 기능을 결합하여, 데이터 조작뿐 아니라 조건문과 반복문 등을 통해 절차적 흐름을 제어할 수 있음을 강조한다.

PL/SQL은 SQL(Structured Query Language)의 기능을 확장하여 만든 프로그래밍 언어이다. 데이터 조작과 제어 흐름을 결합하여 더욱 강력한 데이터베이스 작업 가능하게 한다.

  1. SQL만으로는 부족한 경우가 있다.

    • 일반 SQL은 데이터를 저장하거나 수정하거나 조회하는 데는 강력하지만, 특정 조건에 따라 명령을 실행하거나 여러 단계를 거쳐야 하는 작업을 하기에는 한계가 있다.

  2. PL/SQL의 목적

    • 이러한 한계를 해결하기 위해 절차적 프로그래밍(Programming)을 SQL에 추가한 것이 PL/SQL이다.

    • 예를 들어 "만약 어떤 조건이 만족되면, 데이터를 이렇게 처리하라"와 같은 명령을 내릴 수 있다.

  3. PL/SQL의 특징

    • 변수와 상수 선언 가능 (Can declare variables and constants): 데이터를 저장하거나 계산할 때 필요하다.

    • 조건문 사용 가능 (Can use conditional statements): 상황에 따라 다른 명령 실행한다.

    • 반복문 사용 가능 (Can use loops): 동일한 작업을 여러 번 반복한다.

PL/SQL #2: 기본 구조 (Basic Structure of PL/SQL)

💡요약:기본 구조를 세 부분으로 나누어 설명한다. 각 부분은 선언부(Declarative Part), 실행부(Executable Part), 예외처리부(Exception Handlers)로 구성되며, 특히 실행부가 PL/SQL에서 가장 중요한 핵심 역할을 담당한다고 강조하고 있다.

  1. 선언부 (Declarative Part):

    • DECLARE로 시작 (Starts with DECLARE).

    • 이 부분은 변수(variables)와 상수(constants)를 선언한다.

    • 선택 사항이며 필요하지 않을 경우 생략할 수 있다.

  2. 실행부 (Executable Part):

    • BEGIN으로 시작 (Starts with BEGIN).

    • 실제 작업(로직)을 실행하는 부분다.

    • 데이터 처리, 반복문, 조건문 등 모든 프로그램 흐름 제어가 여기서 이루어진다.

    • 필수(required)로 포함되어야 한다. 이 부분에서 모든 핵심 작업이 수행된다.

  3. 예외처리부 (Exception Handlers):

    • EXCEPTION으로 시작 (Starts with EXCEPTION).

    • 실행 중에 발생할 수 있는 오류를 처리한다.

    • 선택 사항이다.

PL/SQL #3: 기본 구조와 예제 이해 (Basic Structure of PL/SQL with Example)

💡요약: PL/SQL 블록의 기본 구조(선언부, 실행부, 예외처리부)를 이해하고, 각 부분에서 수행되는 역할을 강조한다. PL/SQL은 SQL뿐만 아니라 프로그래밍 언어의 제어 흐름(반복문, 조건문)을 추가로 사용할 수 있어 더 복잡한 작업을 처리할 수 있음을 설명하고 있다.

PL/SQL 블록은 하나의 완전한 프로그램 단위를 형성하며, 아래와 같은 구조를 가진다.

  1. DECLARE (선언부):

    • 변수(variables)나 상수(constants)를 선언하는 부분이다.

    • 예제에서는 v_lname VARCHAR(25);로 문자열 변수를 선언했다.

    • SQL문이 들어가지는 않으며, 주로 데이터 저장을 위해 변수만 선언한다.

  2. BEGIN (실행부, Executable Part):

    • 코드 실행의 핵심 부분이다.

    • SQL문과 PL/SQL 로직이 결합되어 데이터 처리와 흐름 제어를 수행한다.

    • 예제에서는:

      • SELECT last_name INTO v_lname FROM employees WHERE employee_id = 101;로 SQL을 실행하여 결과를 변수에 저장한다.

      • DBMS_OUTPUT.PUT_LINE로 결과를 출력한다.

  3. EXCEPTION (예외처리부, Exception Handlers):

    • 오류가 발생했을 때 실행되는 코드이다.

    • 예제에서는 WHEN OTHERS THEN을 사용하여 모든 종류의 오류를 포착하고 DBMS_OUTPUT.PUT_LINE('ERRORS');로 메시지를 출력한다.

    • 선택 사항이며 필요하지 않다면 생략 가능하다.

PL/SQL #4: 블록의 실행과 출력 설정 (Executing a PL/SQL Block and Output Settings)

위에서 설명한 코드를 출력한 결과이다. PL/SQL 블록 실행 시 결과를 화면에 출력하려면 SET serveroutput ON; 명령어를 먼저 실행해야 한다. 이 명령은 DBMS_OUTPUT.PUT_LINE로 출력된 결과를 SQL Developer에서 볼 수 있도록 해준다.

이 예제에서는 employee_id = 101에 해당하는 직원의 last_name이 조회되어 Kochhar라는 이름이 출력되었다.

PL/SQL #5: 예외처리 (Exception Handling in PL/SQL)

💡요약: PL/SQL의 예외처리(EXCEPTION)는 프로그램 실행 중 발생하는 오류를 처리하기 위해 사용된다. 이것은 TRY...CATCH 문을 사용하는 Java와 비슷하다. 미리 정의된 예외와 사용자 정의 예외를 처리할 수 있으며, 마지막에 WHEN OTHERS를 사용하면 예상하지 못한 오류도 처리할 수 있게된다.

✅ 기본 구조

EXCEPTION
    WHEN 예외1 THEN 예외처리1 -- 특정 오류 1에 대한 처리
    WHEN 예외2 THEN 예외처리2 -- 특정 오류 2에 대한 처리
    …
    WHEN OTHERS THEN 나머지 예외처리 -- 다른 모든 예외에 대한 처리

  • WHEN 예외1 THEN: 특정 예외에 대한 처리 코드를 작성한다.

  • WHEN OTHERS THEN: 정의되지 않은 모든 예외를 처리한다. (옵션)

✅ 예제 코드

BEGIN
    -- 실행부: 오류가 발생할 가능성이 있는 코드
    SELECT salary INTO v_salary
    FROM employees
    WHERE employee_id = 999; -- 없는 ID를 조회해 의도적으로 오류 발생
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('No data found for the given employee ID.'); -- 데이터가 없을 경우 처리
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('An unexpected error occurred.'); -- 기타 오류 처리
END;

설명:

  1. WHEN NO_DATA_FOUND THEN:

    • employee_id = 999인 데이터가 없을 경우 이 블록이 실행된다.

    • 오류 메시지: "No data found for the given employee ID."

  2. WHEN OTHERS THEN:

    • 다른 모든 예기치 못한 오류를 처리한다.

    • 예를 들어, 데이터베이스 연결 문제나 문법 오류 등이 포함된다.

✅ 실행 흐름

  1. BEGIN에서 지정된 SQL을 실행한다.

  2. 실행 중 오류가 발생하면 EXCEPTION 블록으로 이동한다.

  3. 오류 유형에 따라 WHEN ... THEN 조건문이 실행된다.

  4. WHEN OTHERS는 정의되지 않은 모든 오류를 처리한다.

PL/SQL #6: 미리 정의된 예외와 처리 방법 (Predefined Exceptions in PL/SQL)

💡요약: PL/SQL은 실행 중 발생할 수 있는 미리 정의된 예외(predefined exceptions)를 제공한다. 특정 오류를 자동으로 인식하고 처리하는 도구이다. 너무 자세히 알 필요는 없고, "이런 오류가 있을 수 있구나" 정도만 이해하면 된다.
이 예외들은 특정 상황에서 프로그램 실행을 중단하지 않고 오류를 처리하도록 도와준다.

✅ 처리 방법 (예제 코드)

DECLARE
    v_number NUMBER;
BEGIN
    -- 오류를 의도적으로 발생시키는 코드
    v_number := 10 / 0; -- 0으로 나눔
EXCEPTION
    WHEN ZERO_DIVIDE THEN
        DBMS_OUTPUT.PUT_LINE('Cannot divide by zero!'); -- 0으로 나눌 경우 처리
    WHEN INVALID_NUMBER THEN
        DBMS_OUTPUT.PUT_LINE('Invalid number encountered.'); -- 잘못된 숫자 처리
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('An unexpected error occurred.'); -- 기타 오류 처리
END;

✅ 실행 흐름

  1. 오류가 발생하면 PL/SQL은 실행을 중단하지 않고 EXCEPTION 블록으로 이동한다.

  2. 발생한 오류가 WHEN ZERO_DIVIDE 또는 WHEN INVALID_NUMBER와 일치하면 해당 처리를 실행한다.

  3. WHEN OTHERS는 위에서 처리되지 않은 나머지 모든 오류를 처리한다.

✅ 중요한 포인트

  • 미리 정의된 예외는 특정 오류를 자동으로 인식하고 처리하는 도구이다.

  • 자주 사용되는 예외:

    • NO_DATA_FOUND: SELECT 결과 없음.

    • ZERO_DIVIDE: 0으로 나눔.

    • INVALID_NUMBER: 숫자 변환 오류.

  • EXCEPTION 블록을 사용하면 이러한 오류를 안전하게 처리할 수 있다.

  • 옵션이 없다면 실행이 중단되므로 예외 처리를 적절히 설정하는 것이 중요하다.

PL/SQL #7: 엔진의 역할과 구조 (Role and Structure of the PL/SQL Engine)

💡요약: PL/SQL 엔진(PL/SQL Engine)은 PL/SQL 코드를 컴파일하고 실행하며, SQL 문장을 처리하고 그 결과를 종합하는 것이다. 오라클 데이터베이스 내부에서 PL/SQL 블록을 실행하는 중요한 구성 요소이다.

💡 중요한 포인트

    1. PL/SQL 엔진은 PL/SQL 코드를 처리하고 SQL 문을 실행하는 역할을 한다.

      1. 실행 단계:

        • 컴파일 → PL/SQL 엔진 실행 → SQL 문 처리 → 결과 종합

      2. Database Application은 외부 프로그램이며, 결과 종합 단계에서 PL/SQL 엔진을 통해 데이터베이스와 연결된다.

      3. PL/SQL 엔진은 SQL문과 PL/SQL 로직을 효율적으로 처리하는 독립적인 구조를 가지고 있다.

PL/SQL 엔진을 별도로 두는 이유는 효율적인 실행과 데이터베이스와의 통합을 위해서입니다. 이 정도만 이해하면 되겠다.

  1. 컴파일 단계 (Compilation):

    • PL/SQL 블록은 먼저 System Global Area(SGA)에서 컴파일된다.

    • 컴파일후, 이 컴파일된 코드는 PL/SQL 엔진으로 전달된다.

  2. 실행 단계 (Execution):

    • PL/SQL 엔진은 컴파일된 코드를 실행한다.

    • 이 과정에서 SQL문(statement)은 데이터베이스에 전달되어 실행된다.

    • SQL Statement Executor는 SQL 문장을 처리한 결과를 PL/SQL 엔진으로 반환한다.

  3. 종합 및 실행 (Processing Results):

    • PL/SQL 엔진은 SQL 실행 결과를 종합해 데이터베이스 응용 프로그램(database application)에 전달한다.

    • 여기서 Database Application은 PL/SQL 엔진 외부에서 실행되는 프로그램을 의미한다.

2️⃣PL/SQL 구성요소 (PL/SQL Components)

💡요약: PL/SQL도 다른 프로그래밍 언어처럼 다양한 구성 요소를 제공한다. 대표적인 요소로는 변수(Variables)와 상수(Constants)가 있다. 이 두 가지는 데이터를 저장하고 처리하는 데 핵심적인 역할을 하며, 정보 시스템이나 대부분의 프로그래밍에서 없어서는 안 되는 중요한 개념이다.

✅ 변수(Variables)란? 변수(Variable)는 데이터를 저장하는 메모리 공간이다. 프로그램이 데이터를 읽고 처리한 결과를 저장하거나, 최종 결과를 출력하기 위해 사용된다.

변수의 역할

  • 데이터 저장: 예를 들어, 숫자, 문자열, 이미지 등을 저장합니다.

  • 중간 결과 저장: 계산 도중 중간 값을 저장하여 후속 처리를 가능하게 합니다.

  • 최종 출력 준비: 결과 값을 변수에 저장한 후, 이를 출력하거나 활용합니다.

변수 선언 예제

emp_num1 NUMBER(9); 
-- 직원 번호를 저장하기 위한 숫자형 변수 선언

✅ 상수(Constants)란? 상수(Constant)는 한 번 정의하면 값이 변하지 않는 데이터이다.
이는 프로그램에 제약을 추가하여 코드의 일관성을 유지하고, 의도치 않은 오류를 방지한다.

상수를 사용하는 이유

  • 코드 안정성: 여러 개발자가 작업 중, 중요한 데이터가 실수로 수정되는 것을 방지한다.

  • 가독성 향상: 코드에서 특정 숫자나 값의 의미를 명확히 나타낼 수 있다.
    예: nYear CONSTANT INTEGER := 30;는 "30"이 연도를 의미함을 명확히 한다.

상수 선언 예제

Year CONSTANT INTEGER := 30;  
-- 30이라는 값은 "변경 불가"하며 연도와 관련된 고정된 값임을 나타낸다.

💡 중요한 개념 요약

  1. 변수는 데이터를 저장하고 가공하는 데 사용되는 기본적인 메모리 공간이다.

    • 예: emp_num1 NUMBER(9); (9자리 숫자형 데이터를 저장)

  2. 상수는 값을 변경할 수 없으며, 프로그램의 안정성과 가독성을 높이는 데 도움을 준다.

    • 예: nYear CONSTANT INTEGER := 30; (30은 변하지 않는 값)

  3. 상수의 목적은 제약을 통해 오류를 방지하고, 협업 중 코드의 일관성을 유지하는 데 있다.

PL/SQL 구성요소 #1: 변수와 데이터 타입 (PL/SQL Variables and Data Types)

💡요약: PL/SQL에서 변수(Variables)는 데이터 값을 저장하는 메모리 공간이다. 하지만, 변수는 타입(Type)과 항상 함께 정의된다. 타입이란 저장할 데이터의 종류를 나타낸다. 예를 들어, 정수는 정수 타입, 실수는 실수 타입, 문자열은 문자열 타입이어야 한다. 이렇게 타입을 명시하는 이유는 데이터가 올바른 형태로 저장되도록 하기 위해서이다. 또한, 데이터베이스 컬럼의 데이터 타입과 변수 타입은 일관성을 유지해야 한다.

✅ %TYPE - 테이블 컬럼의 데이터 타입 사용

PL/SQL에서 변수를 정의할 때, 기존 테이블의 컬럼에서 데이터 타입을 그대로 가져올 수 있다. 이때 사용하는 것이 %TYPE이다.

Salaries EMPLOYEES.SALARY%TYPE;
-- EMPLOYEES 테이블의 SALARY 컬럼의 데이터 타입을 사용하여 변수를 정의

  • EMPLOYEES.SALARY%TYPE은 EMPLOYEES 테이블의 SALARY 컬럼의 데이터 타입을 그대로 사용하는 방법이다.

  • 이점: 테이블 컬럼의 데이터 타입을 그대로 사용하므로, 데이터베이스 구조 변경 시 변수의 타입도 자동으로 일관되게 유지된다.

✅%ROWTYPE - 테이블의 한 레코드를 변수로 선언

%TYPE과 유사하나 %ROWTYPE은 하나 이상의 값을 묶어서 사용할 때 유용한 방법이다. 이 방식은 테이블의 전체 레코드를 한꺼번에 변수로 선언할 수 있게 해준다.

emp_record EMPLOYEES%ROWTYPE;
-- EMPLOYEES 테이블의 한 행(Row)을 저장할 수 있는 변수 선언

  • EMPLOYEES%ROWTYPE은 EMPLOYEES 테이블의 한 레코드 전체를 저장할 수 있는 변수를 정의하는 방법이다.

  • 이 변수에는 테이블의 모든 컬럼의 데이터가 포함된다.

💡 중요한 개념 요약

  1. %TYPE은 테이블의 컬럼 데이터 타입을 그대로 사용하는 방법이다.

    • 예: EMPLOYEES.SALARY%TYPE (테이블의 SALARY 컬럼 타입을 그대로 사용)

  2. %ROWTYPE은 테이블의 전체 행(레코드)을 변수로 선언할 때 사용한다.

    • 예: EMPLOYEES%ROWTYPE (테이블의 한 행을 저장하는 변수)

  3. 타입 일관성은 데이터베이스의 컬럼과 변수가 같은 타입을 유지하도록 보장한다.

PL/SQL 구성요소 #2: 콜렉션 (Collection)

💡요약: PL/SQL에서 변수는 기본적으로 하나의 값만을 저장할 수 있습니다. 하지만, 배열 형태의 데이터값을 PL/SQL에서도 지원하는데 이를 콜렉션(Collection)이라고 한다. PL/SQL에서는 세 가지 종류의 콜렉션을 지원한다. VARRAY, Nested Table, Associative Array. 각각의 특성을 이해하는 것이 중요하다.

✅ VARRAY (변수 크기 배열)

    • 고정된 크기: VARRAY는 선언할 때 배열의 크기를 정해 놓아야 한다. 크기는 고정되지만, 중간에 배열의 크기를 변경할 수 있다.

      • 숫자형 인덱스: VARRAY는 숫자형 인덱스를 사용하여 배열의 요소를 접근한다.

      • 순서와 밀집된 데이터: VARRAY는 순서가 중요한 데이터를 처리하는 데 유리하며, 밀집된 데이터 집합을 처리할 때 사용된다. 예를 들어, 학생들의 점수, 주문 목록 등 순서대로 처리할 필요가 있는 데이터에 적합하다.

      • 일부 원소 삭제 불가: VARRAY에서 원소는 삭제할 수 없으며, 전체 배열을 삭제해야 한다. 즉, 중간에 원소를 제거할 수는 없고, 배열을 전체적으로 초기화해야 한다.

      • 배열의 크기 변경 가능: 배열의 크기를 선언할 때 크기를 지정하지만, 이후 배열 크기를 동적으로 변경할 수 있다.

      • 테이블 내 저장 가능: VARRAY는 데이터베이스 테이블의 컬럼 타입으로 사용할 수 있다. 예를 들어, 한 테이블의 컬럼에 여러 값을 배열로 저장할 수 있다.

DECLARE
-- VARRAY 타입 정의
    TYPE num_array IS VARRAY(5) OF NUMBER;  -- 5개의 숫자를 저장할 수 있는 배열 타입 선언
    nums num_array := num_array(1, 2, 3, 4, 5);  -- 배열 초기화
BEGIN
    -- 배열 값 출력
    DBMS_OUTPUT.PUT_LINE(nums(1));  -- 첫 번째 요소 출력
END;

✅ 제한 사항

  • 배열의 크기 고정: VARRAY를 선언할 때 크기를 고정해야 하므로, 동적으로 크기를 변경하는 데는 제한이 있다.

  • 부분 삭제 불가: 배열 내 원소는 삭제할 수 없으며, 전체 배열 삭제만 가능하다.

  • 단일 타입의 데이터만 사용 가능: VARRAY는 배열 내 동일한 데이터 타입을 갖는 원소들만 포함할 수 있다.

✅VARRAY (변수 크기 배열) 사용 예시: 복잡해보이지만 전혀 어렵지 않다. 예제코드가 이미 잘 정리되어있기 때문에 필요시마다 찾아서 하면 된다. 배열 선언과 배열 초기화, 그리고 배열의 값 출력을 다루고 있습니다. 이 코드를 이해하고 필요할 때 참조하면 복잡한 PL/SQL 배열 사용에 대한 이해가 쉬워질 것이다.

DECLARE
    TYPE Foursome IS VARRAY(4) OF VARCHAR2(15);  -- VARRAY type 선언
    -- 'Foursome'은 크기 4의 문자열 배열을 정의
    team Foursome := Foursome('John', 'Mary', 'Alberto', 'Juanita');  -- 배열 초기화
BEGIN
    DBMS_OUTPUT.PUT_LINE('---');
    FOR i IN 1..4 LOOP  -- 배열의 각 요소에 접근
        DBMS_OUTPUT.PUT_LINE(i || '.' || team(i));  -- 배열의 원소 출력
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('---');
END;

  1. VARRAY 타입 정의

     TYPE Foursome IS VARRAY(4) OF VARCHAR2(15);
    • Foursome이라는 이름의 VARRAY 타입을 정의한다. 이 배열은 4개의 요소를 가질 수 있으며, 각 요소는 최대 15자의 문자열이다.

  2. VARRAY 변수 초기화

     team Foursome := Foursome('John', 'Mary', 'Alberto', 'Juanita');
    • team이라는 VARRAY 변수를 선언하고, 'John', 'Mary', 'Alberto', 'Juanita'라는 문자열 값을 배열로 초기화한다.

  3. 배열 값 출력

     FOR i IN 1..4 LOOP
     DBMS_OUTPUT.PUT_LINE(i || '.' || team(i));
 END LOOP;

    • FOR 루프를 사용하여 배열의 각 원소를 출력한다. 배열의 인덱스는 1부터 4까지이며, 각 인덱스에 해당하는 team(i)의 값을 출력한다.

    • DBMS_OUTPUT.PUT_LINE은 출력문을 담당한다. i || '.' || team(i)는 배열의 인덱스와 해당 인덱스의 값을 문자열로 연결하여 출력하는 부분이다.

  4. 구분선 출력

      DBMS_OUTPUT.PUT_LINE('---');
 END;

    이 부분은 배열의 출력 전에 구분선을 출력하여, 출력되는 값들을 더 명확하게 구분하기 위한 구문이다. 구분선으로 ---를 출력하여 배열 항목들을 보기 좋게 구분할 수 있다.

✅ Nested Table (중첩 테이블)

  • 동적 크기: NESTED TABLE은 처음 선언할 때 크기를 명시할 필요가 없으며, 사용에 따라 크기가 동적으로 변경된다. 즉, 배열의 크기를 제한하지 않고 유연하게 데이터를 추가할 수 있다.

  • 숫자형 인덱스: 이 배열은 숫자형 인덱스를 사용하며, 데이터가 추가될 때 순차적으로 인덱스가 증가한다. 처음에 a,b,c가 있고 d,f가 추가가 된다면 순서대로 1,2,3,4,5로 구성된다.

  • 삭제가 가능: NESTED TABLE은 중간에 원소를 삭제할 수 있는 특성이 있다. 삭제된 위치는 NULL로 처리되고, 테이블이 흩어진 상태(sparse)로 듬성 듬성한 배열이 될수있다.

  • 밀집(dense)과 흩어진(sparse) 데이터: 처음에는 데이터를 밀집된 형태로 처리하지만, 일부 원소가 삭제되면 배열이 흩어지게 된다. 이로 인해 NESTED TABLE은 매우 유연하게 데이터를 처리할 수 있게 된다.

  • 테이블의 컬럼으로 사용 가능: NESTED TABLE은 테이블 내 컬럼 타입으로도 사용할 수 있지만, 성능상의 이유로 자주 사용되지 않는 것이 권장된다.

  • 문법: TYPE 타입명 IS TABLE OF 요소데이터 타입 [NOT NULL]

DECLARE
    TYPE num_table IS TABLE OF NUMBER;  -- 숫자들을 저장하는 중첩 테이블 타입 선언
    nums num_table := num_table(1, 2, 3, 4, 5);  -- 테이블 초기화
BEGIN
    DBMS_OUTPUT.PUT_LINE(nums(1));  -- 첫 번째 요소 출력
END;
  • 중첩 테이블은 배열처럼 여러 값을 저장하지만, 동적 크기를 갖는다.

✅ Nested Table (중첩 테이블) 사용 예시

DECLARE
    TYPE Roster IS TABLE OF VARCHAR2(15); -- NESTED TABLE 타입 정의
    -- NESTED TABLE 변수 초기화
    names Roster := Roster('D Caruso', 'J Hamil', 'D Piro', 'R Singh');
BEGIN
    DBMS_OUTPUT.PUT_LINE('---');
    -- FIRST와 LAST 메소드를 사용하여 배열의 처음부터 끝까지 순회
    FOR i IN names.FIRST .. names.LAST LOOP
        DBMS_OUTPUT.PUT_LINE(names(i)); -- 각 이름 출력
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('---');
END;

  1. NESTED TABLE 타입 선언

     TYPE Roster IS TABLE OF VARCHAR2(15);

    Roster라는 이름의 NESTED TABLE 타입을 정의하였다. 이 배열은 VARCHAR2(15) 타입의 요소들을 담을 수 있으며, 각 요소는 최대 15자까지 가능하다는 뜻이다.

  2. NESTED TABLE 변수 초기화

     names Roster := Roster('D Caruso', 'J Hamil', 'D Piro', 'R Singh');

    names라는 변수를 선언하고 Roster 타입을 이용해 초기값을 설정하였다. 여기서는 네 명의 이름을 담고 있는 배열을 생성한다.

  3. FIRST와 LAST 메소드 사용:

     FOR i IN names.FIRST .. names.LAST LOOP
     DBMS_OUTPUT.PUT_LINE(names(i));
 END LOOP;

    • FIRST: 배열에서 첫 번째 요소의 인덱스를 반환한다. 예를 들어, names.FIRST는 1을 반환한다.

    • LAST: 배열에서 마지막 요소의 인덱스를 반환합니다. 예를 들어, names.LAST는 4를 반환한다.

    • names(i): 배열의 인덱스 i에 해당하는 값을 가져온다. 루프 내에서 인덱스를 1부터 4까지 순차적으로 증가시키며 값을 출력하게 된다.

FIRST와 LAST 메소드는 배열의 시작과 끝을 나타내는 메소드로, 이를 사용하여 배열의 처음부터 끝까지 순차적으로 접근한다.

  1. 결과 출력: FOR 루프를 통해 names 배열에 있는 각 요소를 출력한다. 이 배열의 크기와 요소는 FIRST와 LAST 메소드를 통해 동적으로 결정되며, 이 범위 내에서 배열의 모든 값을 출력한다.

👀VARAY와 NESTED TABLE의 차이점 간단 소개

  • VARRAY: 고정 크기, 밀집(dense)된 데이터, 순차적 처리

  • NESTED TABLE: 동적 크기, 희소(sparse) 데이터, 유연한 데이터 처리, 빈번한 삽입 및 삭제가 이루어지면 성능이 저하될 수 있다.

✅ Associative Array (연관 배열 또는 맵)

  • 키와 값의 쌍: Associative Array는 키-값 쌍으로 데이터를 저장하는 맵(Map)과 유사한 데이터 구조이다. 예를 들어, (A, 30), (B, 50), (C, 20)와 같은 형태이다. 같은 데이터 타입을 가진 요소들로 구성된다.

    • A, B, C는 키(인덱스)이며, 30, 50, 20은 값(Value)이 된다. 각각의 요소가 고유한 키에 의해 식별되며, 키를 통해 값을 빠르게 검색할 수 있다.

  • Index와 데이터 타입:

    • Index는 PLS_INTEGER, BINARY_INTEGER, VARCHAR2 등의 데이터 타입을 사용할 수 있다.

    • PLS_INTEGER는 PL/SQL에서 제공하는 특수한 정수 타입으로, 계산 속도가 빠르기 때문에 자주 사용된다.

    • 키를 Index라고 부르기 때문에 Index-by 테이블 이라고도 한다.

  • 동적 크기: Associative Array는 크기가 동적이어서, 필요한 만큼 요소를 추가하거나 제거할 수 있다.

  • 배열 접근: 값을 참조할 때 인덱스를 사용하여 빠르게 접근할 수 있게 된다.

문법:

TYPE 타입명 IS TABLE OF 요소 데이터타입 [NOT NULL]
INDEX BY [PLS_INTEGER | BINARY_INTEGER | VARCHAR2(크기)];
  • 예를 들어, 인덱스가 VARCHAR2인 경우는 문자열을 키로 사용하여 배열을 구성할 수 있다.
DECLARE
    TYPE AssociativeArray IS TABLE OF NUMBER INDEX BY VARCHAR2(20);  -- 키는 VARCHAR2, 값은 NUMBER
    scores AssociativeArray;
BEGIN
    scores('A') := 30;  -- 키 'A'에 30을 저장
    scores('B') := 50;  -- 키 'B'에 50을 저장
    scores('C') := 20;  -- 키 'C'에 20을 저장

    DBMS_OUTPUT.PUT_LINE('A: ' || scores('A'));  -- A의 값을 출력 -> 30
    DBMS_OUTPUT.PUT_LINE('B: ' || scores('B'));  -- B의 값을 출력 -> 50
    DBMS_OUTPUT.PUT_LINE('C: ' || scores('C'));  -- C의 값을 출력 -> 20
END;

  • 인덱스 타입: PLS_INTEGER, BINARY_INTEGER, VARCHAR2 등을 키로 사용하며, 문자열을 인덱스로 사용하는 것도 가능하다.

  • 유사한 구조: 자바의 Map과 유사하며, 인덱스를 통해 값을 빠르게 찾을 수 있다.

  • 데이터베이스의 키-값 구조를 구현할 때 유용하며, 특정 키를 기준으로 빠르게 데이터를 처리해야 할 경우 적합하다.

  • 예를 들어, 학생 이름을 점수와 연결하는 구조나 상품명을 가격과 연결하는 구조 등에서 사용할 수 있다.

✅ Associative Array (연관 배열 또는 맵) 사용예제

도시 이름을 인구 수와 연결하는 구조를 표현한 코드이다.

  • Associative Array 타입 정의

      TYPE population IS TABLE OF NUMBER INDEX BY VARCHAR2(64);

    • population이라는 타입을 정의하고, NUMBER 타입 값을 저장하는 배열을 생성한다.

    • 인덱스는 VARCHAR2(64) 타입으로 설정하여, 각 원소를 문자열(도시 이름)으로 식별한다.

  • 변수 선언:

      city_population population;
    • city_population이라는 변수를 선언하여 population 타입의 associative array를 만든다.

  • 값 추가:

      city_population('Smallville') := 2000;
  city_population('Midland') := 750000;
  city_population('Megalopolis') := 1000000;
    • city_population 배열에 도시 이름을 키로, 인구 수를 값으로 추가한다. 각 도시의 인구를 인덱스를 사용하여 저장한다.

✅ 콜렉션 메소드

콜렉션을 사용하면 배열의 크기를 동적으로 관리하거나, 여러 값을 효율적으로 저장하고 관리할 수 있게 된다. PL/SQL은 이러한 콜렉션을 위한 메소드도 제공하여 데이터를 쉽게 다룰 수 있게 해준다. 예를 들어, 배열에서 첫 번째 요소를 가져오거나, 크기를 세거나, 다음 요소로 이동하는 작업을 메소드로 처리할 수 있다.

주요 메소드 예시

  • FIRST: 첫 번째 요소를 반환

  • LAST: 마지막 요소를 반환

  • NEXT: 다음 요소로 이동

  • COUNT: 배열의 요소 수를 반환

PL/SQL 구성요소 #1: 레코드 (Record)

💡요약: 레코드 (Record)는 PL/SQL에서 여러 다른 타입의 데이터를 하나의 구조체로 묶을 수 있는 데이터 타입이다. 이를 통해 테이블의 한 행(row)을 그대로 PL/SQL에서 다룰 수 있으며, 테이블의 각 열(column)을 개별적으로 처리할 수 있게 된다. 구조체 (Structure)와 유사한 개념으로, 데이터베이스 테이블의 각 컬럼을 변수로 묶어 관리할 수 있다.

✅ 레코드의 주요 특징

  1. 복합 데이터 구조:
  • 레코드는 여러 필드(field)로 구성된다. 각 필드는 다른 데이터 타입을 가질 수 있다. 예를 들어, 이름은 문자열로, 나이는 정수로, 날짜는 날짜 형식으로 지정할 수 있다.
  1. 테이블 또는 커서 행을 참조:

  • 테이블에서 한 행을 읽어와서 저장할 때, ROWTYPE을 사용하여 해당 테이블의 행 구조와 동일한 레코드를 정의할 수 있다.

  • 커서에서 한 행을 가져올 때도 ROWTYPE을 사용하여 커서의 구조와 일치하는 레코드를 정의할 수 있다.

✅레코드 정의 방법

  • 사용자 정의 레코드: 이 방법은 특정 구조를 가진 레코드를 정의할 때 사용한다.

      TYPE 레코드이름 IS RECORD (
      필드1 데이터타입1,
      필드2 데이터타입2,
      ...
  );

  • 테이블의 행을 레코드로 정의:

      레코드이름 테이블명%ROWTYPE;

    테이블의 한 행과 동일한 구조를 가지는 레코드를 정의할 때 사용한다. 이 경우, 테이블의 컬럼명이 자동으로 레코드의 필드명이 된다.

  • 커서의 행을 레코드로 정의: 커서에서 반환된 한 행의 구조를 레코드로 정의할 때 사용한다.

      레코드이름 커서명%ROWTYPE;

PL/SQL 구성요소 #2: 레코드 예제 1 (Example of Record)

💡요약: 이 예제에서는 PL/SQL 레코드 (PL/SQL Record) 의 사용법을 보여준다. 레코드는 여러 다른 데이터 타입을 하나로 묶어서 처리할 수 있게 해주는 구조체 (Structure) 이다. 주어진 예제에서는 DeptRecType이라는 이름의 레코드를 정의하고, 이를 dept_rec라는 변수에 할당하였다.

DECLARE
  TYPE DeptRecType IS RECORD (  -- 레코드 타입을 정의
    dept_id NUMBER(4) NOT NULL := 10,  -- 부서 ID (기본값: 10)
    dept_name VARCHAR2(30) NOT NULL := 'Administration',  -- 부서 이름 (기본값: 'Administration')
    mgr_id NUMBER(6) := 200,  -- 부서 관리자 ID (기본값: 200)
    loc_id NUMBER(4)  -- 위치 ID (기본값 없음)
  );

  dept_rec DeptRecType;  -- DeptRecType 타입의 변수 선언

BEGIN
DBMS_OUTPUT.PUT_LINE('dept_rec:');
DBMS_OUTPUT.PUT_LINE('---------');
DBMS_OUTPUT.PUT_LINE('dept_id: ' || dept_rec.dept_id);
DBMS_OUTPUT.PUT_LINE('dept_name: ' || dept_rec.dept_name);
DBMS_OUTPUT.PUT_LINE('mgr_id: ' || dept_rec.mgr_id);
DBMS_OUTPUT.PUT_LINE('loc_id: ' || dept_rec.loc_id);
END;

  1. 레코드 타입 정의 (TYPE DeptRecType IS RECORD):

    • 이 부분에서 DeptRecType이라는 레코드 타입을 정의하고 있다. 레코드 타입은 하나의 변수로 여러 데이터를 묶을 수 있도록 해준다.

  2. 필드와 기본값 (dept_id, dept_name, mgr_id, loc_id):

    • 각 레코드는 여러 필드(field)로 구성됩니다. 필드는 서로 다른 데이터 타입을 가질 수 있다. 예를 들어, dept_id는 숫자 타입이고, dept_name은 문자열 타입이다. 각 필드에는 기본값 (default value)이 설정되어 있다.

      • dept_id: 기본값 10 (부서 ID)

      • dept_name: 기본값 'Administration' (부서 이름)

      • mgr_id: 기본값 200 (부서 관리자 ID)

      • loc_id: 기본값 없음 (위치 ID) **기본값이 지정되지 않았기 때문에, 해당 값은 NULL이 된다. 출력 결과는 빈 문자열처럼 보이게 된다. 즉, 공백이 출력된다.

  3. 변수 선언 (dept_rec DeptRecType):

    • dept_rec라는 변수는 DeptRecType 타입의 레코드 변수를 선언하는 부분이다. 이 변수는 나중에 데이터를 저장하는데 사용된다.

  4. DBMS_OUTPUT.PUT_LINE:

    • DBMS_OUTPUT.PUT_LINE은 PL/SQL에서 결과를 화면에 출력하는 명령이다. 이 명령을 사용해 변수의 값을 출력할 수 있.

  5. 점 연산자 (Dot operator):

    • 레코드의 필드에 접근할 때 dept_rec.dept_id와 같이 점 연산자 (dot operator)를 사용한다. dept_rec는 레코드 변수이고, 그 뒤에 점(.)을 찍고 필드명을 입력하여 해당 필드의 값을 참조할 수 있다.

      • dept_rec.dept_id: 부서 ID

      • dept_rec.dept_name: 부서 이름

      • dept_rec.mgr_id: 부서 관리자 ID

      • dept_rec.loc_id: 위치 ID

PL/SQL 구성요소 #3: 레코드 예제 2 (Example of Record)

💡요약: 이 예제에서는 레코드 (Record)와 그 안에 또 다른 레코드가 포함된 구조를 설명하고 있다. 레코드는 데이터베이스 테이블의 한 행(row)과 비슷한 개념으로, 여러 개의 필드를 가지는 데이터 타입이다. 중첩된 레코드 (Nested Record) 는 하나의 레코드 안에 다른 레코드가 포함되는 구조를 의미한다.

TYPE name_rec IS RECORD (
    first employees.first_name%TYPE,  -- 직원의 이름
    last employees.last_name%TYPE    -- 직원의 성
);
TYPE contact IS RECORD (
    name name_rec, -- 중첩된 레코드
    phone employees.phone_number%TYPE  -- 전화번호
);
friend contact;

BEGIN
friend.name.first := 'John';
friend.name.last := 'Smith';
friend.phone := '1-650-555-1234';
DBMS_OUTPUT.PUT_LINE ( friend.name.first || ', ' ||
friend.name.last || ', ' || friend.phone );
END;

TYPE name_rec IS RECORD (
    first employees.first_name%TYPE,  -- 직원의 이름
    last employees.last_name%TYPE    -- 직원의 성
);
  • name_rec 레코드 타입: first와 last라는 두 필드를 가지며, 각각 employees 테이블의 first_name과 last_name 컬럼의 데이터 타입을 따른다.
TYPE contact IS RECORD (
    name name_rec, -- 중첩된 레코드
    phone employees.phone_number%TYPE  -- 전화번호
);

  • contact 레코드 타입:

    • name이라는 필드를 가진다. 이 필드는 name_rec 레코드 타입을 사용하므로, **first**와 **last**를 가지게 된다.

    • 또 하나의 필드인 phone은 직원의 전화번호를 나타내는 employees.phone_number 컬럼의 데이터 타입을 사용한다.

friend contact;  -- friend 변수 선언
  • friend 변수: friend는 contact 타입의 레코드 변수로, 이 변수는 name 필드 안에 **first**와 **last**를 포함하고, phone 필드에는 전화번호를 저장할 수 있다.
BEGIN
friend.name.first := 'John';
friend.name.last := 'Smith';
friend.phone := '1-650-555-1234';

  • 레코드 값 할당:

    • friend.name.first := 'John'; friend 레코드의 name 중 first 필드에 'John'이라는 값을 할당한다.

    • friend.name.last := 'Smith'; friend 레코드의 name 중 last 필드에 'Smith'라는 값을 할당한다.

    • friend.phone := '1-650-555-1234'; friend 레코드의 phone 필드에 전화번호 '1-650-555-1234'를 할당한다.

이렇게 세 가지 필드에 값을 할당하여 친구의 이름과 전화번호를 설정한다.

DBMS_OUTPUT.PUT_LINE ( friend.name.first || ', ' ||
friend.name.last || ', ' || friend.phone );
END;

  • 값 출력: 이 부분은 **DBMS_OUTPUT.PUT_LINE**을 사용하여 레코드의 값을 출력한다.

    • || 기호는 문자열을 이어 붙이는 (concatenation) 연산자입니다.

    • friend.name.first, friend.name.last, friend.phone 값들이 이어져서 하나의 긴 문자열을 만든다.

    • 예를 들어, 위 코드에서는 friend.name.first가 'John', friend.name.last가 'Smith', friend.phone이 '1-650-555-1234'이므로 출력되는 결과는 John, Smith, 1-650-555-1234가 된다.

3️⃣PL/SQL 제어문(Control Structures)

💡요약: PL/SQL에서 제어문은 프로그램 흐름을 제어하는 데 사용된다. 제어문은 조건문과 반복문으로 나뉜다. IF문은 조건에 맞춰 여러 가지 처리를 할 수 있는 제어문이고, CASE문은 여러 조건값을 비교하여 해당하는 처리문을 실행하는 제어문이다. 두 문법 모두 PL/SQL에서 자주 사용된다.

  • IF문은 주어진 조건이 참일 때 실행할 문을 결정하고, 추가적인 조건을 ELSIF로 확인할 수 있다. 마지막에는 ELSE로 모든 조건이 거짓일 때 실행될 문을 설정할 수 있다.

  • CASE문은 여러 조건을 한 번에 확인하고, 조건값에 맞는 처리문을 실행하는 방식이다. WHEN으로 조건을 비교하고, ELSE는 모든 조건이 일치하지 않을 때 실행된다.

✅IF문 (IF Statement)

IF문은 주어진 조건이 참일 때 특정 코드를 실행하는 제어문이다. 주로 조건에 맞는 처리를 할 때 사용된다.

IF 조건 THEN
    처리문1;
ELSIF 조건2 THEN
    처리문2;
...
ELSE
    처리문N;
END IF;

  • IF 뒤에 조건을 적고, 그 조건이 참일 때 처리할 내용을 THEN 아래에 작성한다.

  • ELSIF는 첫 번째 조건이 거짓일 때 추가적으로 또 다른 조건을 검사할 수 있게 해준다.

  • ELSE는 모든 조건이 거짓일 때 실행되는 코드를 작성한다.

예시: 이 예제는 판매 금액(sales)과 목표 금액(quota)을 비교하여 보너스(bonus)를 계산하는 로직을 보여준다. 사용된 제어문은 IF로, 주어진 조건에 따라 보너스 금액을 다르게 계산한다.

DECLARE --변수 선언 (Variable Declaration)
    sales NUMBER := 10100;  -- 판매 금액 (Sales amount)
    quota NUMBER := 10500;  -- 목표 금액 (Quota)
    bonus NUMBER := 0;      -- 보너스 금액 (Bonus)

BEGIN --  IF문 사용 (Using IF statement)
    IF sales > (quota + 200) THEN  -- 판매 금액이 목표 금액보다 200 이상 클 때
        bonus := (sales - quota) / 4;  -- 보너스는 목표 금액을 초과한 판매 금액의 1/4
    ELSE
        IF sales > quota THEN  -- 판매 금액이 목표 금액을 초과할 때
            bonus := 50;  -- 보너스는 50
        ELSE  -- 판매 금액이 목표 금액에 미치지 못할 때
            bonus := 0;  -- 보너스는 0
        END IF;
    END IF;
    DBMS_OUTPUT.PUT_LINE('bonus = ' || bonus);  -- 계산된 보너스 출력
END;

  • 변수 선언 (Variable Declaration): sales: 실제 판매 금액 , quota: 목표 금액 , bonus: 보너스 금액으로, 기본값은 0으로 설정된다.

  • IF문 사용 (Using IF statement)

    • 첫 번째 IF문은 판매 금액이 목표 금액을 200 이상 초과했을 때 보너스를 1/4로 계산한다. (sales - quota) / 4로 계산된 보너스를 bonus에 할당한다.

    • 만약 첫 번째 조건이 거짓이라면, 두 번째 IF문이 실행된다. 이 경우 판매 금액이 목표 금액을 초과하면 보너스를 50으로 설정한다.

    • 만약 두 번째 조건도 거짓이라면, ELSE 부분에서 보너스를 0으로 설정한다.

  • DBMS_OUTPUT.PUT_LINE을 사용하여 최종 보너스를 출력한다.

✅CASE문 (CASE Statement)

CASE문은 여러 조건을 동시에 처리하고, 조건값에 맞는 처리문을 실행하는 제어문이다. 각 조건을 WHEN과 함께 설정하여 사용한다.

plsqlCopy codeCASE 조건
    WHEN 조건값1 THEN 처리문1;
    WHEN 조건값2 THEN 처리문2;
    ...
    ELSE 처리문N;
END CASE;

  • WHEN은 주어진 조건값을 검사하고, 일치하는 조건값에 해당하는 코드를 실행합니다.

  • ELSE는 모든 조건에 맞지 않을 때 실행할 코드를 작성합니다.

예시: 이 예제는 GRADE 값에 따라 적절한 평가 메시지를 출력하는 CASE문을 보여준다. CASE문은 조건에 맞는 값을 선택하여 처리할 수 있는 구조이다.

DECLARE -- 변수 선언 (Variable Declaration)
grade CHAR(1);

BEGIN
grade := 'B';

CASE grade -- CASE문 사용 (Using CASE statement)
WHEN 'A' THEN DBMS_OUTPUT.PUT_LINE('Excellent');
WHEN 'B' THEN DBMS_OUTPUT.PUT_LINE('Very Good');
WHEN 'C' THEN DBMS_OUTPUT.PUT_LINE('Good');
WHEN 'D' THEN DBMS_OUTPUT.PUT_LINE('Fair');
WHEN 'F' THEN DBMS_OUTPUT.PUT_LINE('Poor');

ELSE DBMS_OUTPUT.PUT_LINE('No such grade');

END CASE;
END;

  • 변수 선언 (Variable Declaration): 먼저, grade라는 학생의 성적을 나타내 변수를 선언하고 초기값으로 'B'를 설정한다.

  • CASE문 사용 (Using CASE statement): CASE문은 grade 값에 따라 각각 다른 출력을 한다. 성적이 'A'일 때는 Excellent, 'B'일 때는 Very Good 등의 메시지를 출력한다.

  • 만약 grade 값이 'A', 'B', 'C', 'D', 'F' 중 어느 것도 아니면 ELSE를 사용하여 'No such grade' 메시지를 출력하게 된다.

  • CASE문은 grade 값을 체크하여 각 조건에 맞는 문장을 실행한다.

  • WHEN 뒤에 조건값을 적고, 조건이 맞으면 그 뒤의 THEN 절을 실행하게 된다.

  • 조건에 맞는 것이 없으면 ELSE 절을 실행한다.

PL/SQL 구성요소 #4: 반복문 (Loops)

💡요약: PL/SQL에서 반복문을 사용하면 특정 조건을 만족할 때까지 동일한 작업을 반복할 수 있다. 가장 많이 사용되는 반복문은 FOR문이며, 그 외에 LOOP문과 WHILE문도 자주 사용된다.

💡LOOP

✅ LOOP문 (LOOP Statement) : LOOP문은 무한 반복문이다. 조건 없이 반복이 시작되고, 반복을 멈추기 위해서는 EXIT 조건을 명시해야 한다.

✅ LOOP문 예제 (LOOP Example)

DECLARE
    x NUMBER := 0;  -- 변수 x를 0으로 초기화
BEGIN
    LOOP
        DBMS_OUTPUT.PUT_LINE('Inside loop: x = ' || TO_CHAR(x));  -- x 값을 출력
        x := x + 1;  -- x 값 1 증가
        IF x > 3 THEN  -- x가 3보다 크면 반복문을 종료
            EXIT;  -- EXIT 조건이 맞으면 반복문을 종료
        END IF;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('After loop: x = ' || TO_CHAR(x));  -- 반복문 종료 후 x 값을 출력
END;

  • LOOP: 반복문을 시작한다. 이 안의 모든 코드가 반복 실행된다.

  • DBMS_OUTPUT.PUT_LINE: x 값을 출력하는 명령어이다.

  • x := x + 1: x 값을 1 증가시키는 연산이다.

  • IF x > 3 THEN EXIT;: x가 3보다 커지면 EXIT가 실행되어 반복문을 종료한다.

  • END LOOP;: LOOP문이 끝나는 부분이다.

  • DBMS_OUTPUT.PUT_LINE: 반복문 종료 후 x 값을 출력한다.

✅중요한 부분 요약 (Summary of Key Points)

  • LOOP문은 조건 없이 무한 반복되므로, 반드시 반복을 종료할 조건을 설정해야 한다.

  • EXIT 명령어를 사용하여 반복문을 강제로 종료할 수 있다.

  • LOOP문은 간단한 반복을 위해 사용된다.

💡EXIT WHEN 문 사용 (Using EXIT WHEN) LOOP문을 더 간단하게 표현할 수 있는 방법은 EXIT WHEN 조건을 사용하는 것이다. EXIT WHEN은 반복문을 특정 조건에서 자동으로 종료하도록 도와준다. 이를 통해 IF문을 간소화할 수 있게 되고 코드가 더 깔끔하고 읽기 쉽다.

DECLARE
    x NUMBER := 0;  -- 변수 x를 0으로 초기화
BEGIN
    LOOP
        DBMS_OUTPUT.PUT_LINE('Inside loop: x = ' || TO_CHAR(x));  -- x 값을 출력
        x := x + 1;  -- x 값 1 증가
        EXIT WHEN x > 3;  -- x가 3보다 크면 반복문 종료
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('After loop: x = ' || TO_CHAR(x));  -- 반복문 종료 후 x 값을 출력
END;

  • EXIT WHEN x > 3;: 이 구문은 x가 3보다 크면 반복문을 종료한다. 이전 예제에서 IF문과 EXIT를 사용한 방식과 동일한 기능을 한다.

  • 나머지 부분은 이전 예제와 동일하며, 반복문 안에서 x 값을 1씩 증가시키고, x > 3이면 반복문을 종료하게 된다

  • EXIT WHEN을 사용하면 조건을 간단하게 표현할 수 있으며, IF문 없이 바로 종료 조건을 지정할 수 있게 된다.

  • **EXIT WHEN**은 조건을 만족하면 즉시 반복문을 종료한다.

💡FOR Loop

✅FOR Loop: FOR문은 반복문 중 하나로, 인덱스 변수를 사용하여 정해진 범위 내에서 값을 증가시키거나 감소시키면서 반복된다. FOR문은 범위 내에서 인덱스가 자동으로 증가하거나 감소하므로 코드가 간결해지고 이해하기 쉬워진다. 가장 많이 쓰이는 loop 이다.

  • 카운터: 반복문을 실행할 때 값을 가지고 있는 인덱스 변수이다.

  • 최소값..최대값: 반복문이 시작될 범위와 종료될 범위를 나타낸다.

  • FOR문은 시작값부터 끝값까지 반복을 수행하며, 끝값을 포함하지 않으며, 자동으로 증가한다.

✅FOR문 예제 (FOR Loop Example)

BEGIN
    DBMS_OUTPUT.PUT_LINE('lower_bound < upper_bound');
    FOR i IN 1..3 LOOP  -- i는 1부터 3까지 반복
        DBMS_OUTPUT.PUT_LINE(i);  -- i 값을 출력
    END LOOP;

    DBMS_OUTPUT.PUT_LINE('lower_bound = upper_bound');
    FOR i IN 2..2 LOOP  -- i는 2부터 2까지 반복
        DBMS_OUTPUT.PUT_LINE(i);  -- i 값을 출력
    END LOOP;

    DBMS_OUTPUT.PUT_LINE('lower_bound > upper_bound');
    FOR i IN 3..1 LOOP  -- 실행되지 않음, 시작값이 끝값보다 큼
        DBMS_OUTPUT.PUT_LINE(i);  -- 실행되지 않음
    END LOOP;
END;

  1. 첫 번째 FOR문 (First FOR Loop):

    • 범위가 1..3이므로 i는 1, 2, 3으로 반복된다.

    • 출력 결과는 1, 2, 3이 차례로 출력된다.

  2. 두 번째 FOR문 (Second FOR Loop):

    • 범위가 2..2로, i는 2로만 반복된다.

    • 출력은 2만 출력된다.

  3. 세 번째 FOR문 (Third FOR Loop):

    • 범위가 3..1로, 시작값이 끝값보다 크기 때문에 반복문이 실행되지 않게 된다.

    • 결과적으로 출력되지 않는다.

✅ 중요한 부분 요약 (Summary of Key Points)

  • FOR문은 범위 내에서 자동으로 값을 증가시키며 반복한다.

  • 시작값이 끝값보다 크면 반복문이 실행되지 않게 된다.

  • 범위 내에서 지정된 값들만 반복되며, 끝값은 포함되지 않는다.

💡WHILE 문

✅ WHILE 문: WHILE문은 조건이 만족될 때까지 반복을 실행하는 구조이다. 조건이 true일 때만 반복이 실행되며, 조건이 false이면 반복을 종료한다. 이 예제는 두 개의 WHILE문을 사용하여 조건을 다르게 설정하고 있다.

✅ WHILE문 예제 (WHILE Loop Example)

DECLARE
    done BOOLEAN := FALSE;
BEGIN
    WHILE done LOOP
        DBMS_OUTPUT.PUT_LINE('This line does not print.');
        done := TRUE;  -- 이 할당은 실행되지 않습니다.
    END LOOP;

    WHILE NOT done LOOP
        DBMS_OUTPUT.PUT_LINE('Hello, world!');
        done := TRUE;
    END LOOP;
END;

  1. 첫 번째 WHILE문 (First WHILE Loop):

    • done은 FALSE로 초기화되었기 때문에, 첫 번째 WHILE문은 조건이 false로 시작하여 실행되지 않게된다.

    • done := TRUE;는 실행되지 않으며 WHILE문이 실행되지 않기 때문에, 해당 출력문은 출력되지 않게된다.

  2. 두 번째 WHILE문 (Second WHILE Loop):

    • done은 첫 번째 WHILE문에서 변경되지 않아서 여전히 FALSE이다.

    • NOT done은 TRUE가 되어 두 번째 WHILE문이 실행된다.

    • DBMS_OUTPUT.PUT_LINE('Hello, world!');가 출력되며, done := TRUE;로 done이 TRUE로 변경된다.

    • 그 후 조건이 false가 되어 두 번째 WHILE문은 종료된다.

✅ 중요한 부분 요약 (Summary of Key Points)

  • WHILE문은 조건이 true일 때만 실행된다.

  • done이 FALSE이면 첫 번째 WHILE문은 실행되지 않고, 두 번째 WHILE문에서만 실행된다.

  • done := TRUE;는 두 번째 WHILE문에서 실행되어 조건이 false로 변경되며 반복이 종료된다.

LOOP, FOR, WHILE 비교 (Comparison of LOOP, FOR, WHILE)

  1. LOOP:

    • 무한 루프로 시작하지만, 내부에서 EXIT 조건을 사용해 종료할 수 있다.

    • 조건을 동적으로 설정할 수 있어 유연성이 높다.

  2. FOR:

    • 반복 횟수가 명확하게 주어질 때 사용된다.

    • 코드가 간결하고 반복 횟수가 정해져 있어 코드 작성이 용이하다.

  3. WHILE:

    • 조건이 true일 때만 반복되며, 조건을 만족할 때까지 반복한다.

    • 반복 조건이 false가 되면 반복이 종료되며, 조건 만족 여부를 기준으로 반복이 진행된다.

💡 정리 (Summary)

  • LOOP: 반복을 명시적으로 제어하고 싶을 때 사용.

  • FOR: 반복 횟수가 정해져 있을 때 사용.

  • WHILE: 조건에 맞을 때만 반복하고 싶을 때 사용.

Database Design and Construction

Part 3 of 13

As part of the database system, This subject is advanced study. This lecture explains the basic theory of DBMS, database design, application program creation, and database management knowledge, and cultivates database utilization skills.

Up next

Partition, View and Sequence in Database

파티션, 뷰, 시퀀스

More from this blog

My dev journey

125 posts

Managing ORACLE database - PL/SQL