PL_SQL(Procedural Language extension to SQL)

DB/SQL

PL_SQL(Procedural Language extension to SQL)

제주니어 2022. 9. 15. 19:41

PL_SQL(Procedural Language extension to SQL)

PL/SQL은 오라클에서 제공하는 절차적인 프로그래밍 언어이다.
SQL 문의 반복적인 실행이나 조건에 따른 분기 등 다양한 기능을 제공한다.
PL/SQL은 선언부(DECLARE SECTION), 실행부(EXECUTABLE SECTION), 예외 처리부(EXCEPTION SECTION)로 구성된다.
오라클에서 내장되어 있는 절차적 언어로 SQL 문장 내에서 변수를 정의, 조건 처리(IF), 반복 처리(LOOP, WHITE, FOR)등을 지원한다.

-- 출력 기능 활성화 (환경 변수를 ON으로 바꿔준다.)
SET SERVEROUTPUT ON;

DECLARE
  -- 선언부
  ...
BEGIN
  -- 실행부
  ...
EXCEPTION
  -- 예외 처리부
  ...
END;
/

선언부(DECLARE SECTION) : DECLARE로 시작, 변수나 상수를 선언하는 영역이다.
실행부(EXECUTABLE SECTION) : BEGIN으로 시작, 제어문, 반복문, 함수 정의 등 로직을 기술하는 영역이다.
예외 처리부(EXCEPTION SECTION) : EXCEPTION으로 시작, 예외사항 발생 시 해결하기 위한 문장을 기술하는 영역이다.

PL/SQL 선언부

변수 선언 및 초기화

변수 및 상수는 일반 타입 변수, 레퍼런스 타입 변수, ROW 타입 변수로 선언해서 사용할 수 있다.

일반 타입변수

DECLARE
    -- 선언부
    변수명 [CONSTANT] 자료형(크기) [:= 값]; -- 상수로 만들고 싶을 때 [CONSTANT] 추가
  BEGIN
    -- 실행부
    ...
  END;
  /

DECLARE 
    EID NUMBER;
    ENAME VARCHAR2(15) := '박현진'; -- 선언과 동시에 초기화
    PI CONSTANT NUMBER := 3.14; -- 상수 선언, 선언과 동시에 초기화 해야한다. 
BEGIN
    EID := 300; -- (오라클에서 =는 동등 연산자, :=는 대입 연산자)
--    PI := 3.15; -- 한번 선언하면 다시 바뀌지 않는다. 

    DBMS_OUTPUT.PUT_LINE('EID : '|| EID); -- || : 연결 연산자
    DBMS_OUTPUT.PUT_LINE('ENAME : '|| ENAME);
    DBMS_OUTPUT.PUT_LINE('PI : '|| PI);
END;
/

레퍼런스 타입 변수 : 테이블의 컬럼에 데이터 타입을 참조한다.

DECLARE
    -- 선언부
    변수명 테이블명.컬럼명%TYPE [:= 값];
  BEGIN
    -- 실행부
    ...
  END;
  /

DECLARE 
    EID EMPLOYEE.EMP_ID%TYPE; -- EMPLOYEE 테이블에 EMP_ID와 동일한 타입으로 지정하겠다 - VARCHAR2(3)
    ENAME EMPLOYEE.EMP_NAME%TYPE;
    SAL EMPLOYEE.SALARY%TYPE;
BEGIN
    -- 노옹철 사원의 사번, 직원명, 급여 정보를 EID, ENAME, SAL 변수에 대입 후 출력한다.
    SELECT EMP_ID, EMP_NAME, SALARY 
    INTO EID, ENAME, SAL -- 반드시 한 줄에 담아줘야 한다.
    FROM EMPLOYEE
--    WHERE EMP_NAME = '노옹철';
    WHERE EMP_NAME = '&직원명'; -- &멘트 - 대체 변수 : 사용자에게 입력 받아 검색해서 찾을 수 있다.
    
    DBMS_OUTPUT.PUT_LINE('EID : '|| EID);
    DBMS_OUTPUT.PUT_LINE('ENAME : '|| ENAME);
    DBMS_OUTPUT.PUT_LINE('SAL : '|| SAL);
END;
/

ROW 타입 변수 : 하나의 테이블의 여러 컴럼의 값을 한꺼번에 저장할 수 있는 변수를 선언할 수 있다.

DECLARE
    -- 선언부
    변수명 테이블명%ROWTYPE;
  BEGIN
    -- 실행부
    ...
  END;
  /

DECLARE 
    EMP EMPLOYEE%ROWTYPE;
BEGIN
    SELECT *
    INTO EMP
    FROM EMPLOYEE
    WHERE EMP_NAME = '&직원명'; -- 데이터 개수가 같아야 한다.
    
    DBMS_OUTPUT.PUT_LINE('사번 : ' || EMP.EMP_ID);
    DBMS_OUTPUT.PUT_LINE('이름 : ' || EMP.EMP_NAME);
    DBMS_OUTPUT.PUT_LINE('주민번호 : ' || EMP.EMP_NO);
    DBMS_OUTPUT.PUT_LINE('이메일 : ' || EMP.EMAIL);
    DBMS_OUTPUT.PUT_LINE('전화번호 : ' || EMP.PHONE);
    DBMS_OUTPUT.PUT_LINE('부서 코드 : ' || EMP.DEPT_CODE);
    DBMS_OUTPUT.PUT_LINE('직급 코드 : ' || EMP.JOB_CODE);
    DBMS_OUTPUT.PUT_LINE('급여 : ' || TO_CHAR(EMP.SALARY, 'FML99,999,999'));
    DBMS_OUTPUT.PUT_LINE('입사일 : ' || TO_CHAR(EMP.HIRE_DATE, 'YYYY"년" MM"월" DD"일"'));
END;
/

PL/SQL 실행부

1) 선택문

단일 IF

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

-- 사번을 입력받은 후 해당 사원의 사번, 이름, 급여, 보너스를 출력
-- 단, 보너스를 받지 않은 사원은 보너스 출력 전에 '보너스를 지급받지 않은 사원입니다.'라는 문구를 출력한다. 
DECLARE 
    EID EMPLOYEE.EMP_ID%TYPE;
    ENAME EMPLOYEE.EMP_NAME%TYPE;
    SALARY EMPLOYEE.SALARY%TYPE;
    BONUS EMPLOYEE.BONUS%TYPE;
BEGIN
    SELECT EMP_ID, EMP_NAME, SALARY, BONUS
    INTO EID, ENAME, SALARY, BONUS
    FROM EMPLOYEE
    WHERE EMP_ID = '&사번';
    
    DBMS_OUTPUT.PUT_LINE('사번 : ' || EID);
    DBMS_OUTPUT.PUT_LINE('이름 : ' || ENAME);
    DBMS_OUTPUT.PUT_LINE('급여 : ' || SALARY);
    
    IF (BONUS IS NULL) THEN
        DBMS_OUTPUT.PUT_LINE('보너스를 지급받지 않는 사원입니다.');
    END IF;
    DBMS_OUTPUT.PUT_LINE('보너스 : ' || BONUS);
END;
/

IF ~ ELSE

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

-- 위의 PL/SQL 구문은 IF ~ ELSE 문으로 변경하여 작성 
DECLARE 
    EID EMPLOYEE.EMP_ID%TYPE;
    ENAME EMPLOYEE.EMP_NAME%TYPE;
    SALARY EMPLOYEE.SALARY%TYPE;
    BONUS EMPLOYEE.BONUS%TYPE;
BEGIN
    SELECT EMP_ID, EMP_NAME, SALARY, NVL(BONUS, 0)
    INTO EID, ENAME, SALARY, BONUS
    FROM EMPLOYEE
    WHERE EMP_ID = '&사번';
    
    DBMS_OUTPUT.PUT_LINE('사번 : ' || EID);
    DBMS_OUTPUT.PUT_LINE('이름 : ' || ENAME);
    DBMS_OUTPUT.PUT_LINE('급여 : ' || SALARY);
    
--    IF (BONUS IS NULL) THEN
    IF (BONUS = 0) THEN
        DBMS_OUTPUT.PUT_LINE('보너스를 지급받지 않는 사원입니다.');
    ELSE 
        DBMS_OUTPUT.PUT_LINE('보너스율 : ' || BONUS * 100 || '%');
    END IF;
    
END;

-- 사번을 입력 받아서 해당 사원의 사번, 이름, 부서명, 국가 코드를 조회한 후 출력
-- 단, 국가 코드가 'KO'이면 국내팀 그 외는 해외팀으로 출력한다. 

DECLARE
    EID EMPLOYEE.EMP_ID%TYPE;
    ENAME EMPLOYEE.EMP_NAME%TYPE;
    DTITLE DEPARTMENT.DEPT_TITLE%TYPE;
    NCODE LOCATION.NATIONAL_CODE%TYPE;
BEGIN
    SELECT E.EMP_ID, 
           E.EMP_NAME,
           D.DEPT_TITLE,
           L.NATIONAL_CODE
    INTO EID, ENAME, DTITLE, NCODE
    FROM EMPLOYEE E
    INNER JOIN DEPARTMENT D ON (E.DEPT_CODE = D.DEPT_ID)
    INNER JOIN LOCATION L ON (D.LOCATION_ID = L.LOCAL_CODE)
    WHERE EMP_ID = '203';
    
    DBMS_OUTPUT.PUT_LINE('사번 : ' || EID);
    DBMS_OUTPUT.PUT_LINE('이름 : ' || ENAME);
    DBMS_OUTPUT.PUT_LINE('부서명 : ' || DTITLE);
    
    IF (NCODE = 'KO') THEN
        DBMS_OUTPUT.PUT_LINE('소속 : 국내팀');
    ELSE
        DBMS_OUTPUT.PUT_LINE('소속 : 해외팀');
    END IF;
END;
/

IF ~ ELSIF ~ ELSE

개수는 제한이 없다.

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

-- 사용자에게 점수를 입력 받아서 SCORE 변수에 저장한 후 학점은 입력된 점수에 따라 GRADE 변수에 저장한다. 
-- 90점 이상은 'A'학점
-- 80점 이상은 'B'학점
-- 70점 이상은 'C'학점
-- 60점 이상은 'D'학점
-- 60점 미만은 'F'학점
-- 출력은 '당신의 점수는 95점이고, 학점은 A학점입니다.'와 같이 출력한다.

DECLARE
    SCORE NUMBER;
    GRADE CHAR(1);
BEGIN
    SCORE := '&점수';
    
    IF (SCORE <= 100 AND SCORE >= 0) THEN
        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;    
        
        DBMS_OUTPUT.PUT_LINE('당신의 점수는 ' || SCORE || '점이고, 학점은 ' || GRADE || '학점입니다.');   
    ELSE    
        DBMS_OUTPUT.PUT_LINE('점수를 잘못 입력하였습니다.');
    END IF;

----------------------------------------------------------------------------    
--    IF (SCORE > 100 OR SCORE < 0) THEN
--        DBMS_OUTPUT.PUT_LINE('점수를 잘못 입력하였습니다.');
--       
--        RETURN;
--    END IF;
--    
--    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;          
----------------------------------------------------------------------------
--    IF (SCORE > 100 OR SCORE < 0) THEN
--        DBMS_OUTPUT.PUT_LINE('점수를 잘못 입력하였습니다.');
--        
--        RETURN;
--    ELSIF(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;    
    
--    DBMS_OUTPUT.PUT_LINE('당신의 점수는 ' || SCORE || '점이고, 학점은 ' || GRADE || '학점입니다.');

END;
/

-- 사용자에게 입력받은 사번과 일치하는 사원의 급여 조회 후 출력한다. 
-- 500만원 이상이면 '고급'
-- 300만원 이상이면 '중급'
-- 300만원 미만이면 '초급'
-- 출력은 '해당 사원의 급여 등급은 고급입니다.'와 같이 출력한다. 
DECLARE
    SALARY EMPLOYEE.SALARY%TYPE;
    GRADE VARCHAR2(10);
BEGIN
    SELECT SALARY
    INTO SALARY
    FROM EMPLOYEE
    WHERE EMP_ID = 200;
    
    IF (SALARY >= 5000000) THEN
        GRADE := '고급';
    ELSIF (SALARY >= 3000000) THEN
        GRADE := '중급';
    ELSE 
        GRADE := '초급';
    END IF;
    
    DBMS_OUTPUT.PUT_LINE('해당 사원의 급여 등급은 ' || GRADE || '입니다.');
END;
/

-- 실행부에 SELECT 구문 여러개 넣기
DECLARE
    SAL EMPLOYEE.SALARY%TYPE;
    GRADE SAL_GRADE.SAL_LEVEL%TYPE;
BEGIN
    SELECT SALARY
    INTO SAL
    FROM EMPLOYEE
    WHERE EMP_ID = '&사번';
    
    SELECT SAL_LEVEL
    INTO GRADE
    FROM SAL_GRADE
    WHERE SAL BETWEEN MIN_SAL AND MAX_SAL;
    
    DBMS_OUTPUT.PUT_LINE('해당 사원의 급여 등급은 ' || GRADE || '입니다.');
END;
/

CASE

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

-- 사용자로부터 사번을 입력받은 후에 사원의 모든 컬럼에 데이터를 EMP에 대입하고 DEPT_CODE에 따라 알맞은 부서를 출력한다. 
DECLARE
    EMP EMPLOYEE%ROWTYPE;
    DTITLE VARCHAR2(30);
BEGIN
    SELECT *
    INTO EMP
    FROM EMPLOYEE
    WHERE EMP_ID = '&사번';
    
    DTITLE := 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 '총무부'
                   ELSE '부서없음'
               END; 
    
    DBMS_OUTPUT.PUT_LINE('사번 : ' || EMP.EMP_ID);
    DBMS_OUTPUT.PUT_LINE('이름 : ' || EMP.EMP_NAME);
    DBMS_OUTPUT.PUT_LINE('부서 코드 : ' || EMP.DEPT_CODE);
    DBMS_OUTPUT.PUT_LINE('부서명 : ' || DTITLE);
END;
/

2) 반복문

BASIC LOOP

LOOP
  반복적으로 실행시킬 구문

  [반복문을 빠져나갈 조건문 작성]
  1) IF 조건식 THEN EXIT; END IF; 
  2) EIXT WHEN 조건식;
END LOOP;

-- 1 ~ 5까지 순차적으로 1씩 증가하는 값을 출력
DECLARE
    NUM NUMBER := 1;
BEGIN
    LOOP
        DBMS_OUTPUT.PUT_LINE(NUM);
        
        NUM := NUM + 1;
        
--        IF NUM > 5 THEN
--            EXIT;
--        END IF;
        EXIT WHEN NUM > 10;
    END LOOP;
END;
/

WHILE LOOP

WHILE 조건식
LOOP
  반복적으로 실행할 구문;
END LOOP;

-- 1 ~ 5까지 순차적으로 1씩 증가하는 값을 출력
DECLARE
    NUM NUMBER := 1;
BEGIN
    WHILE NUM <= 5
    LOOP
        DBMS_OUTPUT.PUT_LINE(NUM);
        
        NUM := NUM + 1;
    END LOOP;
END;
/

-- 구구단 출력 (2 ~ 9단)
DECLARE 
    DAN NUMBER := 2;
    SU NUMBER;
    RESULT NUMBER;
BEGIN
    WHILE DAN <= 9
    LOOP
        SU := 1;
        
        WHILE SU <= 9
        LOOP
            RESULT := DAN * SU;
            
            DBMS_OUTPUT.PUT_LINE(DAN || ' X ' || SU || ' = ' || RESULT);
            
            SU := SU + 1;
        END LOOP;
        DBMS_OUTPUT.PUT_LINE(' ');
        DAN := DAN + 1;
    END LOOP;
END;
/

FOR LOOP

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

-- 1 ~ 5까지 순차적으로 1씩 증가하는 값을 출력
BEGIN
    FOR NUM IN 1..5
    LOOP
        DBMS_OUTPUT.PUT_LINE(NUM);
    END LOOP;
END;
/

-- 역순으로 출력
BEGIN
    FOR NUM IN REVERSE 1..5
    LOOP
        DBMS_OUTPUT.PUT_LINE(NUM);
    END LOOP;
END;
/

-- 구구단(2 ~ 9단) 출력 단, 짝수단만 출력한다.
BEGIN
    FOR DAN IN 2..9
    LOOP
        IF (MOD(DAN, 2) = 0) THEN 
            FOR SU IN 1..9
            LOOP
                DBMS_OUTPUT.PUT_LINE(DAN || ' X ' || SU || ' = ' || DAN * SU);
            END LOOP;
        END IF;
        DBMS_OUTPUT.PUT_LINE('');
    END LOOP;
END;
/

-- 반복문을 이용한 데이터 삽입
-- 실습 테이블 만들기
DROP TABLE TEST;

CREATE TABLE TEST (
     NUM NUMBER,
     CREATE_DATE DATE
);

TRUNCATE TABLE TEST;
SELECT * FROM TEST;
ROLLBACK;

-- TEST 테이블에 10개의 행을 INSERT 하는 PL/SQL 작성
BEGIN
    FOR NUM IN 1..10
    LOOP
        INSERT INTO TEST VALUES(NUM, SYSDATE);
        
        IF (MOD(NUM, 2) = 0) THEN
            COMMIT;
        ELSE
            ROLLBACK;
        END IF;
        
    END LOOP;
END;
/

SELECT * FROM TEST;

PL/SQL 예외 처리부

PL/SQL 문에서 발생한 예외를 예외 처리부에서 처리가 가능하다.

DECLARE
    ...
BEGIN
    ...
EXCEPTION
  WHEN 예외명 1 THEN 예외 처리 구문 1;
  WHEN 예외명 2 THEN 예외 처리 구문 2;
  ...
  WHEN OTHERS THEN 예외 처리 구문;
END

오라클에서 미리 정의되어 있는 예외

NO_DATA_FOUND

SELECT 문의 수행 결과가 한 행도 없을 경우에 발생한다.

TOO_MANY_ROWS

한 행이 리턴되어야 하는데 SELECT 문에서 여러 개의 행을 반환할 때 발생한다.

SELECT EMP_ID, MANAGER_ID
FROM EMPLOYEE
WHERE MANAGER_ID = 200;

DECLARE
    EID EMPLOYEE.EMP_ID%TYPE;
    MID EMPLOYEE.MANAGER_ID%TYPE;
    
BEGIN
    SELECT EMP_ID, MANAGER_ID
    INTO EID, MID
    FROM EMPLOYEE
    WHERE MANAGER_ID = &사수사번;
    
    DBMS_OUTPUT.PUT_LINE('사번 : ' || EID);
    DBMS_OUTPUT.PUT_LINE('사수 사번 : ' || MID);
EXCEPTION 
    WHEN TOO_MANY_ROWS THEN DBMS_OUTPUT.PUT_LINE('너무 많은 행이 조회되었습니다. ');
    WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('조회 결과가 없습니다.');
    WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('오류가 발생했습니다.');
END;
/

ZERO_DIVIDE

숫자를 0으로 나눌 때 발생한다.

-- 사용자가 입력한 수로 나눗셈 연산 결과를 출력
DECLARE 
    RESULT NUMBER;
    
BEGIN
    RESULT := 10 / &숫자;
    
    DBMS_OUTPUT.PUT_LINE('결과 : ' || RESULT);

EXCEPTION
    WHEN ZERO_DIVIDE THEN DBMS_OUTPUT.PUT_LINE('나누기 연산시 O으로 나눌 수 없습니다.');
END;
/

DUP_VAL_ON_INDEX

UNIQUE 제약조건을 가진 컬럼에 중복된 데이터가 INSERT 될 때 발생한다.

BEGIN
    UPDATE EMPLOYEE
    SET EMP_ID = 200
    WHERE EMP_NAME = '&이름';

EXCEPTION 
    WHEN DUP_VAL_ON_INDEX THEN DBMS_OUTPUT.PUT_LINE('이미 존재하는 사번입니다.');
--    WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('오류가 발생했습니다.');
END;
/

저작자표시 비영리 동일조건

'DB > SQL' 카테고리의 다른 글

OBJECT - TRIGGER (0)	2022.09.14
OBJECT - CURSOR (0)	2022.09.13
OBJECT - FUNCTION (0)	2022.09.12
OBJECT - PROCEDURE (0)	2022.09.11
OBJECT - SYNONYM (0)	2022.09.10

현재글PL_SQL(Procedural Language extension to SQL)

바켠진로그

현재 백엔드 개발자로 활동하고 있습니다.

java, columnDefinition, hiberanate, pattern, @creationtimestamp, jpa,

Today :
Yesterday :

바켠진로그

PL_SQL(Procedural Language extension to SQL)

PL_SQL(Procedural Language extension to SQL)

PL/SQL 선언부

변수 선언 및 초기화

PL/SQL 실행부

1) 선택문

단일 IF

IF ~ ELSE

IF ~ ELSIF ~ ELSE

CASE

2) 반복문

BASIC LOOP

WHILE LOOP

FOR LOOP

PL/SQL 예외 처리부

오라클에서 미리 정의되어 있는 예외

NO_DATA_FOUND

TOO_MANY_ROWS

ZERO_DIVIDE

DUP_VAL_ON_INDEX

'DB > SQL' 카테고리의 다른 글

'DB/SQL'의 다른글

티스토리툴바

« 2025/02 »
일	월	화	수	목	금	토
						1
2	3	4	5	6	7	8
9	10	11	12	13	14	15
16	17	18	19	20	21	22
23	24	25	26	27	28

PL_SQL(Procedural Language extension to SQL)

PL_SQL(Procedural Language extension to SQL)

PL/SQL 선언부

변수 선언 및 초기화

PL/SQL 실행부

1) 선택문

단일 IF

IF ~ ELSE

IF ~ ELSIF ~ ELSE

CASE

2) 반복문

BASIC LOOP

WHILE LOOP

FOR LOOP

PL/SQL 예외 처리부

오라클에서 미리 정의되어 있는 예외

NO_DATA_FOUND

TOO_MANY_ROWS

ZERO_DIVIDE

DUP_VAL_ON_INDEX

'DB > SQL' 카테고리의 다른 글

'DB/SQL'의 다른글

관련글

티스토리툴바