AWS 웹 개발자 8일차 기초 SQL 학습 PL_SQL

PL/SQL 개요 및 실습

---

 

PL/SQL (Procedural Language/SQL)

PL/SQL은 오라클에서 제공하는 절차적 언어로, SQL문을 확장하여 변수 선언, 조건문(IF), 반복문(FOR, WHILE) 등을 지원합니다. 이를 통해 SQL의 기능을 보완하고 복잡한 로직을 구현할 수 있습니다.

PL/SQL 구조

1. 선언부: DECLARE로 시작하며 변수와 상수 선언 및 초기화를 진행합니다.
2. 실행부: BEGIN으로 시작하며 SQL문과 제어문 로직을 처리합니다.
3. 예외처리부: EXCEPTION으로 시작하며 예외 발생 시 처리하는 구문을 포함합니다.

---

1. 선언부 (DECLARE)

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

• 변수 EID, ENAME, PI를 선언하고 값을 초기화하여 출력합니다.

DECLARE
    EID NUMBER;
    ENAME VARCHAR2(20);
    PI CONSTANT NUMBER := 3.14;
BEGIN 
    EID := 800;
    ENAME := '최지원';
    DBMS_OUTPUT.PUT_LINE('EID : '|| EID);  -- EID 출력
    DBMS_OUTPUT.PUT_LINE('ENAME : '|| ENAME);  -- ENAME 출력
    DBMS_OUTPUT.PUT_LINE('PI : '|| PI);  -- PI 출력
END;

사용자 입력을 통한 변수 설정

• 사용자로부터 EID와 ENAME을 입력받아 출력합니다.

DECLARE
    EID NUMBER;
    ENAME VARCHAR2(20);
    PI CONSTANT NUMBER := 3.14;
BEGIN 
    EID := &번호;  -- 사용자 입력
    ENAME := '&이름';  -- 사용자 입력
    DBMS_OUTPUT.PUT_LINE('EID : '|| EID);
    DBMS_OUTPUT.PUT_LINE('ENAME : '|| ENAME);
    DBMS_OUTPUT.PUT_LINE('PI : '|| PI);
END;

레퍼런스 타입 변수 선언

• EMPLOYEE 테이블의 컬럼을 사용하여 변수를 선언하고, 해당 사원의 정보를 조회합니다.

DECLARE
    EID EMPLOYEE.EMP_ID%TYPE;
    ENAME EMPLOYEE.EMP_NAME%TYPE;
    SAL EMPLOYEE.SALARY%TYPE;
BEGIN 
    SELECT EMP_ID, EMP_NAME, SALARY
    INTO EID, ENAME, SAL
    FROM EMPLOYEE
    WHERE EMP_ID = &사번;  -- 사번으로 사원 정보 조회
    DBMS_OUTPUT.PUT_LINE('EID : '|| EID);
    DBMS_OUTPUT.PUT_LINE('ENAME : '|| ENAME);
    DBMS_OUTPUT.PUT_LINE('SAL : '|| SAL);
END;

ROW 타입 변수 선언

• EMPLOYEE 테이블에서 한 행의 데이터를 조회하여 저장합니다.

DECLARE
    E EMPLOYEE%ROWTYPE;  -- EMPLOYEE 테이블의 한 행을 저장
BEGIN
    SELECT *
    INTO E
    FROM EMPLOYEE
    WHERE EMP_ID = &사번;  -- 사번으로 사원 정보 조회
    DBMS_OUTPUT.PUT_LINE('사원명 :' || E.EMP_NAME);  -- 사원명 출력
    DBMS_OUTPUT.PUT_LINE('급여 :' || E.SALARY);  -- 급여 출력
    DBMS_OUTPUT.PUT_LINE('보너스 :' || E.BONUS);  -- 보너스 출력
END;

---

2. 실행부 (BEGIN)

조건문 (IF문)

단독 IF문

• BONUS가 0일 경우, 보너스를 지급받지 않는 사원임을 출력합니다.

DECLARE
    EID EMPLOYEE.EMP_ID%TYPE;
    ENAME EMPLOYEE.EMP_NAME%TYPE;
    JCODE EMPLOYEE.JOB_CODE%TYPE;
    SAL EMPLOYEE.SALARY%TYPE;
    BONUS EMPLOYEE.BONUS%TYPE;
BEGIN
    SELECT EMP_ID, EMP_NAME, JOB_CODE, SALARY, NVL(BONUS, 0)
    INTO EID, ENAME, JCODE, SAL, BONUS
    FROM EMPLOYEE
    WHERE EMP_ID = &사번;  -- 사번으로 사원 정보 조회
    
    DBMS_OUTPUT.PUT_LINE('EID : '|| EID);  -- EID 출력
    DBMS_OUTPUT.PUT_LINE('ENAME : '|| ENAME);  -- ENAME 출력
    DBMS_OUTPUT.PUT_LINE('JCODE : '|| JCODE);  -- 직급 코드 출력
    DBMS_OUTPUT.PUT_LINE('SAL : '|| SAL);  -- 급여 출력
    
    IF BONUS = 0  -- 보너스가 0일 경우
        THEN DBMS_OUTPUT.PUT_LINE('보너스를 지급받지 않는 사원입니다.');
    END IF;
    
    DBMS_OUTPUT.PUT_LINE('보너스 : '|| BONUS);  -- 보너스 출력
END;

IF-ELSE 문

• NCODE가 'KO'이면 '국내팀', 그렇지 않으면 '해외팀'으로 분류하여 출력합니다.

DECLARE
    EID EMPLOYEE.EMP_ID%TYPE;
    ENAME EMPLOYEE.EMP_NAME%TYPE;
    DTITLE DEPARTMENT.DEPT_TITLE%TYPE;
    NCODE LOCATION.NATIONAL_CODE%TYPE;
    TEAM VARCHAR(10);
BEGIN
    SELECT EMP_ID, EMP_NAME, DEPT_TITLE, NATIONAL_CODE
    INTO EID, ENAME, DTITLE, NCODE
    FROM EMPLOYEE
    JOIN LOCATION ON (LOCATION_ID = LOCAL_CODE)
    JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID)
    WHERE EMP_ID = &사번;

    IF NCODE = 'KO'  -- 국적이 'KO'일 경우
    THEN TEAM := '국내팀';
    ELSE  -- 그 외
    TEAM := '해외팀';
    END IF;

    DBMS_OUTPUT.PUT_LINE('사번 :' || EID);  -- 사번 출력
    DBMS_OUTPUT.PUT_LINE('이름 :' || ENAME);  -- 이름 출력
    DBMS_OUTPUT.PUT_LINE('부서 :' || DTITLE);  -- 부서 출력
    DBMS_OUTPUT.PUT_LINE('소속 :' || TEAM);  -- 팀 소속 출력
END;

IF-ELSIF-ELSE 문

• SCORE에 따라 등급을 부여하여 출력합니다.

DECLARE
    SCORE NUMBER;
    GRADE VARCHAR2(1);
BEGIN
    SCORE := &점수;  -- 점수 입력받기
    
    IF SCORE >= 90 THEN GRADE := 'A';  -- 90 이상 'A'
    ELSIF SCORE >= 80 THEN GRADE := 'B';  -- 80 이상 'B'
    ELSIF SCORE >= 70 THEN GRADE := 'C';  -- 70 이상 'C'
    ELSIF SCORE >= 60 THEN GRADE := 'D';  -- 60 이상 'D'
    ELSE GRADE := 'F';  -- 그 외 'F'
    END IF;
    
    DBMS_OUTPUT.PUT_LINE('당신의 점수는 :' || SCORE || '점이며, 학점은 ' || GRADE || '학점입니다.');
END;

---

3. 반복문 (LOOP)

BASIC LOOP문

• I 값을 1부터 10까지 출력합니다.

DECLARE
    I NUMBER := 0;
BEGIN
    LOOP
        I := I + 1;  -- I 증가
        DBMS_OUTPUT.PUT_LINE(I);  -- I 출력
        EXIT WHEN I = 10;  -- I가 10이면 종료
    END LOOP;
END;

FOR LOOP문 (정해진 횟수 반복)

• 1부터 10까지 역순으로 출력합니다.

BEGIN 
    FOR I IN REVERSE 1..10  -- 10부터 1까지 반복
    LOOP
        DBMS_OUTPUT.PUT_LINE(I);  -- I 출력
    END LOOP;
END;

WHILE LOOP문

• I 값을 0부터 9까지 출력합니다.

DECLARE 
    I NUMBER := 0;
BEGIN
    WHILE I < 10  -- I가 10 미만일 때 반복
    LOOP
        DBMS_OUTPUT.PUT_LINE(I);  -- I 출력
        I := I + 1;  -- I 증가
    END LOOP;
END;

---

4. 예외처리부 (EXCEPTION)

예외 처리 구문

• 0으로 나누는 연산에서 발생하는 ZERO_DIVIDE 예외를 처리합니다.

DECLARE
    RESULT NUMBER;
BEGIN
    RESULT := 10/&숫자;  -- 나누기 연산
    DBMS_OUTPUT.PUT_LINE('결과 :' || RESULT);
EXCEPTION
    WHEN ZERO_DIVIDE THEN DBMS_OUTPUT.PUT_LINE('나누기 연산시 0으로 나눌 수 없습니다.');
    WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('예외가 발생하였습니다.');
END;

예외 처리 (DUP_VAL_ON_INDEX)

• 동일한 사번을 삽입할 때 발생하는 DUP_VAL_ON_INDEX 예외를 처리합니다.

BEGIN
    UPDATE EMPLOYEE
    SET EMP_ID = '&변경할사번'
    WHERE EMP_NAME = '노옹철';
EXCEPTION
    WHEN DUP_VAL_ON_INDEX THEN DBMS_OUTPUT.PUT_LINE('이미 존재하는 사원입니다.');
END;

---

5. 테이블 및 시퀀스 작업 예시

테이블 생성

• TEST 테이블을 생성합니다.

CREATE TABLE TEST(
    TNO NUMBER PRIMARY KEY,  -- TNO는 기본 키
    TDATE DATE );  -- TDATE는 날짜 타입

시퀀스 생성

• SEQ_TNO라는 시퀀스를 생성합니다.

CREATE SEQUENCE SEQ_TNO;

반복문을 사용하여 데이터 삽입

• SEQ_TNO 시퀀스를 사용해 1부터 100까지 데이터를 삽입합니다.

BEGIN 
    FOR I IN 1..100  -- 1부터 100까지 반복
    LOOP
        INSERT INTO TEST VALUES(SEQ_TNO.NEXTVAL, SYSDATE);  -- 시퀀스 값과 현재 날짜 삽입
    END LOOP;
END;

---

결론

PL/SQL은 SQL문을 확장하여 변수 선언, 조건문, 반복문을 사용한 다양한 논리 구현을 가능하게 합니다. 예외 처리 구문을 통해 오류를 적절히 처리하며, 반복문을 활용하여 효율적인 데이터 처리가 가능합니다. 이를 통해 복잡한 비즈니스 로직을 구현하는 데 매우 유용합니다.

---