[32일 차] : Oracle - SEQUENCE, PLSQL

서하루·2022년 11월 25일

(2) Oracle 공부기록

목록 보기

10/11

[학습목표]

SEQUENCE (시퀀스)
PL (SQL문 내 변수, 조건문, 반복문 등)

💡 SEQUENCE

- 자동으로 숫자를 발생시켜주는 역할을 하는 객체
- 정수값을 순차적으로 일정값씩 증가시키며 생성

📒 1. 시퀀스 객체 생성

[표현법]
CREATE SEQUENCE 시퀀스명
[START WITH 시작 숫자] => 처음 발생시킬 시작값 지정(생략시 1부터 시작)
[INCREMENT BY 숫자] => 몇 씩 증가시킬건지 (생략시 1씩 증가)
[MAXVALUE 숫자] => 최대값 지정(생략시 기본값 짱 큼)
[MINVALUE 숫자] => 최소값 지정 (생략시 1)
[CYCLE | NOCYCLE] => 값 순환여부지정 (생략시 NOCYCLE 기본값)
[NOCACHE | CACHE 바이트크기] => 캐시 메모리하당 (기본값 CACHE 20)

--1. 시퀀스 객체 생성
CREATE SEQUENCE SEQ_EMPNO
START WITH 300
INSREMENT BY 5
MAXVALUE 310
NOCYCLE
NOCACHE;

/*
출력값
MINVALUE = 1
MAXVALUE = 310
INCREMENT = 5
CACHE = 0

*/

📒 1-2. 시퀀스 사용 (숫자 사용)

시퀀스명.CURRVAL : 현재 시퀀스의 값(마지막에 성공적으로 수행된 NEXTVAL)
시퀀스명.NEXTVAL : 일정값을 증가시켜 새롭게 발생된 값

📒 1-3. 시퀀스 변경

[표현법]
ALTER SEQUENCE 시퀀스명
[INCREMENT BY 증가값][MAXVALUE 최대값]
[MINVALUE 최소값][CYCLE|NOCYCLE]
[NOCACHE|CACHE 바이트크기];

🔴 START WITH는 변경 불가능함

📒 1-4. 시퀀스 삭제

[표현법]
DROP SEQUENCE 시퀀스명;

🖊️ 2. PL

오라클에 내장되어 있는 절차적언어로 SQL문 내에서 변수활용, 조건처리(IF), 반복처리(LOOP, FOR, WHILE)등을 지원하여 SQL문의 단점 보완하고 다수의 SQL문을 한번에 실행가능하다.

💡PL / SQL 구조

선언부 : DECLARE 로 시작하며 변수 또는 상수를 선언 및 초기화하는 부분
실행부 : BEGIN으로 시작하며 SQL문 또는 제어문등의 로직 기술 부분
예외처리부 : EXCEPTION으로 시작하며 예외발생시 해결구문 기술

🖊️ 2-1. DECLARE 선언부 (일반타입변수)

변수 및 상수 선언( 선언과 동시에 초기화 가능)
➡️ 변수명 [CONSTANT] 자료형 := 값;

DECLARE
	EID NUMBER;
    ENAME VARCHAR2(20);
    ACADEMY CONSTANT VARCHAR2(20) := '아카데미';

BEGIN
	EID := &번호;
    ENAME := '&이름';

DBMS_OUTPUT.PUT_LINE ('EID : ' || EID);
DBMS_OUTPUT.PUT_LINE ('ENAME : ' || ENAME);
DBMS_OUTPUT.PUT_LINE ('ACADEMY : ' || ACADEMY);

END;
/

&번호 또는 &이름 : 사용자가 입력할 수 있게 하는 구문
SQL문의 출력문(SYSTEM.OUT.PRINT) : DBMS_OUTPUT.PUT_LINE();

🖊️ 2-2. DECLARE 선언부 (레퍼런스타입변수)

특정 컬럼의 데이터타입을 참조해서 그 타입으로 지정하는 것
➡️ 변수명 테이블명.컬럼명%TYPE

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;
/

🖊️ 2-3. DECLARE 선언부 (ROW타입변수)

테이블의 한 행에 대한 모든 컬럼을 한번에 담을 수 있는 변수
➡️ 변수명 테이블명%ROWTYPE

DECLARE 
    E EMPLOYEE%ROWTYPE;
BEGIN
    SELECT *
      INTO E
      FROM EMPLOYEE
     WHERE EMP_ID = &사번;
    
   -- DBMS_OUTPUT.PUT_LINE(E);
   DBMS_OUTPUT.PUT_LINE('사원명: ' || E.EMP_NAME);
   DBMS_OUTPUT.PUT_LINE('급여 : ' || E.SALARY);
   DBMS_OUTPUT.PUT_LINE('보너스 : ' || NVL(E.BONUS,0));

END;
/

📚 3. BEGIN 실행부 (조건문)

➡️ IF 조건식 THEN 실행내용 END IF; (==단일 IF문)

-- 특정 사원의 사번, 이름, 급여, 보너스율(%) 출력
-- 단, 보너스를 받지 않는 사원은 보너스율 출력 전, '보너스를 지급받지 않는 사원입니다.' 출력

DECREATE
    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 = 0 
    	THEN DBMS_OUTPUT.PUT_LINE('보너스를 받지 않는 사원입니다.');
    END IF;
    
	 DBMS_OUTPUT.PUT_LINE('보너스율 : ' || BONUS * 100 || '%');

END;
/

➡️ IF 조건식 THEN 실행내용 ELSE END IF; (==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 = 0 
    	 THEN DBMS_OUTPUT.PUT_LINE('보너스를 지급받지 않는 사원입니다.');
    ELSE
        DBMS_OUTPUT.PUT_LINE('보너스율 : ' || BONUS * 100 || '%');
    END IF;
    
END;
/
-- 보너스율이 나오거나, 지급받지 않는 사원이거나 둘 중에 하나만 해당하는 문구 출력됨

➡️ IF 조건식1 THEN 실행내용1 ELSIF 조건식2 THEN 실행내용2... END IF; (==IF -ELSE IF)

DECLARE
    SCORE NUMBER;
    GRADE VARCHAR2(10);
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;
    
    -- 출력형식 : 당신의 점수는 XX점이고, 학점은 X학점입니다
    DBMS_OUTPUT.PUT_LINE('당신의 점수는 ' || SCORE || '점이고, 학점은 ' || GRADE || '학점입니다.');
END;
/

➡️ CASE 비교대상자 WHEN 비교값1 THEN 결과값1 WHEN 비교값2 THEN 결과값2 .. END IF;

DECLARE
    EMP EMPLOYEE%ROWTYPE;
    DNAME VARCHAR2(30);
BEGIN
    SELECT *
      INTO EMP
      FROM EMPLOYEE
     WHERE EMP_ID = &사번;
     
    DNAME := CASE EMP.DEPT_CODE 
                WHEN 'D1' THEN '인사팀'
                WHEN 'D2' THEN '회계팀'
                WHEN 'D3' THEN '마케팅팀'
                ELSE '기타팀'
             END;
             
     -- XXX님은 XXX입니다. 출력
     DBMS_OUTPUT.PUT_LINE(EMP.EMP_NAME || '님은 ' || DNAME || '입니다.');
END;
/

📚 4. BEGIN 실행부 (반복문)

➡️ BASIC LOOP문

LOOP
반복적으로 수행할 구문;
(반복문을 빠져나갈 수 있는 구문)
END LOOP;

반복문을 빠져나갈 수 있는 구문 2가지 !
1). IF 조건식 THEN EXIT; END IF;
2). EXIT WHEN 조건식;

-- 1부터 5까지 1씩 증가되는 값 출력하기

DECLARE
	I NUMBER := 1;
BEGIN
	
    LOOP
    	DBMS_OUTPUT.PUT_LINE(I);
        I := I + 1;
        
        EXIT WHEN I = 6;
    END LOOP;
END;
/

-- I가 6이 되기 전까지 1씩 증가시키기 ! FOR문이랑 같은 조건식
-- 출력시 1,2,3,4,5

➡️ FOR LOOP문

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

BEGIN
    FOR I IN REVERSE 1..5
    LOOP
        DBMS_OUTPUT.PUT_LINE(I);
    END LOOP;
END;

-- 출력값 5,4,3,2,1
/

➡️ WHILE LOOP문

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

DECLARE
	I NUMBER := 1;

BEGIN
	WHILE I <6
    LOOP
    	DBMS_OUTPUT.PUT_LINE(I);
        I := I + 1;
        END LOOP;
        
END;

📚 5.예외처리부 (EXCEPTION)

[표현법]
EXCEPTION
WHEN 예외명1 THEN 예외처리구문1;
WHEN 예외명2 THEN 예외처리구문2;
...
WHEN OTHERS THEN 예외처리구문;

* 시스템예외 ( 오라클에서 미리 정의해둔 예외)
- NO _DATE_FOUND : SELECT한 결과가 한 행도 없을 경우
- TOO_MANY_ROWS  : SELECT한 결과가 여러행 일때
- ZERO_DIVIDE : 0으로 나누기 했을경우
- DUP_VAL_ON_INDEX : UNIQUE 제약조건에 위배됐을 경우