PL/SQL (Procedural Language/SQL)

PL/SQL은 오라클에서 제공하는 프로그래밍 언어로, SQL의 확장을 통해 데이터베이스에서 복잡한 로직을 구현할 수 있습니다. PL/SQL은 일반적인 프로그래밍 언어와는 달리 데이터베이스와 밀접하게 연동되어 있어, 대량의 데이터를 처리하거나 여러 쿼리를 일괄 처리할 수 있는 강력한 기능을 제공합니다.

1. PL/SQL의 기본 구조

PL/SQL 블록은 DECLARE, BEGIN, EXCEPTION, END의 네 부분으로 나뉘며, 아래와 같은 형식을 가집니다.

DECLARE
    -- 변수 선언 영역
BEGIN
    -- 실행 영역
EXCEPTION
    -- 예외 처리 영역
END;

1.1 익명 블록 예제

SET SERVEROUTPUT ON; -- 오라클에서 출력을 활성화합니다.

DECLARE
    v_emp_name employees.first_name%TYPE; -- 변수 선언
BEGIN
    SELECT first_name INTO v_emp_name FROM employees WHERE employee_id = 100;
    DBMS_OUTPUT.PUT_LINE(v_emp_name); -- 결과 출력
END;

2. PL/SQL의 연산자

• 산술 연산자: +, -, *, /, MOD()
• 비교 연산자: =, <, >, <=, >=, <>
• 대입 연산자: :=

3. DML문 사용하기

PL/SQL 블록 내에서 SELECT INTO 문을 사용하여 데이터를 변수에 할당할 수 있습니다.

DECLARE
    v_emp_name employees.first_name%TYPE;
BEGIN
    SELECT first_name INTO v_emp_name FROM employees WHERE employee_id = 100;
    DBMS_OUTPUT.PUT_LINE(v_emp_name);
END;

4. 조건문

4.1 IF 문

DECLARE
    num1 NUMBER := 5;
    num2 NUMBER := TRUNC(DBMS_RANDOM.VALUE(1, 11));
BEGIN
    IF num1 >= num2 THEN
        DBMS_OUTPUT.PUT_LINE(num1 || '가 더 큽니다.');
    ELSE
        DBMS_OUTPUT.PUT_LINE(num2 || '가 더 큽니다.');
    END IF;
END;

4.2 CASE 문

DECLARE
    num1 NUMBER := 5;
    num2 NUMBER := TRUNC(DBMS_RANDOM.VALUE(1, 11));
BEGIN
    CASE 
        WHEN num1 >= num2 THEN
            DBMS_OUTPUT.PUT_LINE(num1 || '가 더 큽니다.');
        ELSE 
            DBMS_OUTPUT.PUT_LINE(num2 || '가 더 큽니다.');
    END CASE;
END;

5. 반복문

5.1 WHILE 문

DECLARE
    v_count NUMBER := 1;
BEGIN
    WHILE v_count <= 9 LOOP
        DBMS_OUTPUT.PUT_LINE('3 * ' || v_count || ' = ' || 3 * v_count);
        v_count := v_count + 1;
    END LOOP;
END;

5.2 FOR 문

DECLARE
BEGIN
    FOR i IN 1..9 LOOP
        DBMS_OUTPUT.PUT_LINE('3 * ' || i || ' = ' || 3 * i);
    END LOOP;
END;

6. 커서

커서는 SQL 질의 결과로 반환되는 여러 행을 처리할 수 있는 방법입니다. 커서를 사용하여 여러 행을 순회할 수 있습니다.

DECLARE
    CURSOR emp_cursor IS SELECT first_name, salary FROM employees WHERE job_id = 'IT_PROG';
    v_name employees.first_name%TYPE;
    v_salary employees.salary%TYPE;
BEGIN
    OPEN emp_cursor;
    LOOP
        FETCH emp_cursor INTO v_name, v_salary;
        EXIT WHEN emp_cursor%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE('이름: ' || v_name || ', 급여: ' || v_salary);
    END LOOP;
    CLOSE emp_cursor;
END;

7. 프로시저

7.1 스토어드 프로시저

프로시저는 하나의 쿼리 집합으로, 매개변수를 받아 실행할 수 있습니다.

CREATE OR REPLACE PROCEDURE greet_user IS
BEGIN
    DBMS_OUTPUT.PUT_LINE('Hello, World!');
END;

7.2 프로시저 호출

EXEC greet_user;

7.3 프로시저의 매개변수

CREATE OR REPLACE PROCEDURE add_numbers(p_num1 IN NUMBER, p_num2 IN NUMBER) IS
    v_sum NUMBER;
BEGIN
    v_sum := p_num1 + p_num2;
    DBMS_OUTPUT.PUT_LINE('합계: ' || v_sum);
END;

EXEC add_numbers(10, 20);

8. 트리거

트리거는 특정 테이블에 대해 INSERT, UPDATE, DELETE 작업이 발생할 때 자동으로 실행되는 PL/SQL 블록입니다.

8.1 트리거 생성

CREATE OR REPLACE TRIGGER trg_before_insert
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
    :NEW.created_at := SYSDATE; -- 새로운 행의 생성 일시 설정
END;

8.2 트리거 예제

CREATE OR REPLACE TRIGGER trg_after_delete
AFTER DELETE ON employees
FOR EACH ROW
BEGIN
    DBMS_OUTPUT.PUT_LINE('사원이 삭제되었습니다: ' || :OLD.first_name);
END;

9. 연습문제

1. 3단 구구단을 출력하는 익명 블록을 작성하세요.
2. 직원 테이블에서 ID 201의 이름과 이메일을 출력하는 익명 블록을 작성하세요.
3. 가장 큰 직원 ID에 +1한 ID로 새로운 직원을 추가하는 익명 블록을 작성하세요.

-- 1. 구구단 출력 예제
DECLARE
    i NUMBER;
BEGIN
    FOR i IN 1..9 LOOP
        DBMS_OUTPUT.PUT_LINE('3 * ' || i || ' = ' || 3 * i);
    END LOOP;
END;