데이터베이스

[10일차] SEQUENCE(시퀀스), PL/SQL

Dodledd 2024. 2. 28. 19:29

시퀀스란?

자동으로 번호를 발생시켜주는 역할을 하는 객체
적수값을 순차적으로 일정값씩 증가시키면서 생성해줌

[표현식] CREATE SEQUENCE 시퀀스명
[SRATT WITH 시작숫자] --> 처음 발생시킬 시작값 지정[기본값 1]
[INCREMENT BY 숫자] --> 몇 씩 증가시킬건지[기본값1]
[MAXVALUE 숫자] --> 최대값 지정[기본값매우큼] [MINVALUE 숫자] --> 최소값 지정[기본값1]
[CYCLE | NOCYCLE] --> 값의 순환 여부를 지정해준다 [기본값 NOCYCLE]
[NOCACHE | CACHE 바이트크기] --> 캐시메모리를 할상해준다 (기본값 CACHE 20)

*캐리메모리 : 미리 발생될 값들을 생성해서 저장해두는 공간 매번 호출 될 때 마다 새로 번호를 생성하는게 아니라 캐리메모리 공간에 미리 생성된 값들을 가져다 쓸 수 있다. (속도가 빨라진다)

테이블명 : TB_
뷰명 : VW_
시퀀스 : SEQ_
트리거 : TRG_

 

 

 

1. 시퀀스 생성

CREATE SEQUENCE SEQ_TEST;

CREATE SEQUENCE SEQ_EMPNO
START WITH 300
INCREMENT BY 5
MAXVALUE 310
NOCYCLE
NOCACHE;

 

 

2. 시퀀스 사용

시퀀스명.CURRVAL : 현재 시퀀스 값(마지막으로 성공한 NEXTVAL의 값)
시퀀스명.NEXTVAL : 시퀀스값에 일정값을 증가시켜 발생한 값, 현재 시퀀스 값에서 INCREMENT BY 한 값만큼
증가된 값

 

 

바로 CURRVAL을 사용하려하면 에러가 난다. NEXTVAL을 한 번이라도 실행해야 CURRVAL을 사용할 수 있다.

SELECT SEQ_EMPNO.CURRVAL FROM DUAL; --에러

왜? CURRVAL는 마지막으로 성공한 NEXTVAL의 값을 저장해서 보여주는 임시값이니깐

 

SELECT SEQ_EMPNO.NEXTVAL FROM DUAL;--300
SELECT SEQ_EMPNO.CURRVAL FROM DUAL;--300
SELECT SEQ_EMPNO.NEXTVAL FROM DUAL;--305
SELECT SEQ_EMPNO.NEXTVAL FROM DUAL;--310
SELECT SEQ_EMPNO.NEXTVAL FROM DUAL;--315 에러 MAXVALUE 넘어서

마지막에 왜 에러가날까?

우리는 시퀀스를 생성할 때 MAX값으로 310을 줬기 때문에 그 값을 넘어서면 에러가 난다.

 

 

중요

 

우리가 DB에서 사용할 때 하는 방법 중 하나이다.

제일 처음 값, 즉 1씩 증가하는 시퀀스의 기본기능을 이용하여 우리는 DB에 값을 넣을 때 기본키 대신 저렇게

SEQ_EID.NEXTVAL을 적어주면 1,2,3,4,5.... 이렇게 쭉 들어가게 되는 것  이다.

CREATE SEQUENCE SEQ_EID
START WITH 400
NOCACHE;

INSERT INTO EMPLOYEE (EMP_ID, EMP_NAME, EMP_NO, JOB_CODE, HIRE_DATE)
    VALUES (SEQ_EID.NEXTVAL, '김말똥', '111111-2222222', 'J6', SYSDATE);
    
INSERT INTO EMPLOYEE (EMP_ID, EMP_NAME, EMP_NO, JOB_CODE, HIRE_DATE)
    VALUES (SEQ_EID.NEXTVAL, '김새똥', '111111-2222222', 'J6', SYSDATE);
    
SELECT * FROM EMPLOYEE;

 

3. 시퀀스 변경

3. 시퀀스의 구조변경 ALTER SEQUENCE 시퀀스명
[INCREMENT BY 숫자] --> 몇 씩 증가시킬건지[기본값1]
[MAXVALUE 숫자] --> 최대값 지정[기본값매우큼]
[MINVALUE 숫자] --> 최소값 지정[기본값1]
[CYCLE | NOCYCLE] --> 값의 순환 여부를 지정해준다 [기본값 NOCYCLE]
[NOCACHE | CACHE 바이트크기] --> 캐시메모리를 할상해준다 (기본값 CACHE 20)

*START WITH는 변경이 불가

 

 

방금 만든 시퀀스의 증가값을 10으로, MAX값을 400으로 바꿔보자

ALTER SEQUENCE SEQ_EMPNO
INCREMENT BY 10
MAXVALUE 400;

 

 

4.시퀀스 삭제

DROP SEQUENCE SEQ_EMPNO;

그냥 테이블 삭제하듯 시퀀스 적어서 삭제하면 된다.

 

<PL / SQL> (PROCEDURE LANGUAGE EXTENSION TO SQL)

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

 

*PL/SQL 구조
-[선언부] : DECLARE로 시작, 변수나 상수를 선언 및 초기화하는 부분
-[실행부] : BEGIN으로 시작, SQL문 또는 제어문(조건문, 반복문)등의 로직을 기술하는 부분
-[예외처리부] : EXCDETION으로 시작, 예외발생시 해결하기 위한 구문

 

시작하기에 앞서

SET SERVEROUTPUT ON;

를 사용해줘야 작성가능하다!

 

그럼 모든 언어를 처음 배울 때 항상 작성하는 그 문구를 작성해보자

BEGIN
    --SYSTEM.OUT.PRINTLN("HELLO ORACLE") 자바버젼
    DBMS_OUTPUT.PUT_LINE('HELLO ORACLE');
END;
/

이렇게 끝날 때 / 를 해줘야 Developer가 인식한다

 

1. DECLARE 선언부

변수 및 상수 선언 하는 공간(선언과 동시에 초기화도 가능)
일반타입 변수, 레퍼런스타입 변수, ROW타입 변수

 

1_1) 일반타입 변수 선언 및 초기화
[표현식]
변수명 [CONSTANT] 자료형 [:=값] <-초기화가능

 

값을 넣는 방식이 독특하다.

:= 임을 잊지말자

 

DECLARE //선언부
    EID NUMBER;
    ENAME VARCHAR2(20);
    PI CONSTANT NUMBER :=3.14;
BEGIN//실행부
    EID := 800;
    ENAME := '울랄라';
    
    DBMS_OUTPUT.PUT_LINE('EID: ' || EID);
    DBMS_OUTPUT.PUT_LINE('ENAME : ' || ENAME);
    DBMS_OUTPUT.PUT_LINE('PI : ' || PI);

END;
/

 

Developer에서 입력받게 만들 수도 있다.

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

이렇게 & 를 붙여서 작성하면 Java에서 Scanner을 사용하여 입력받듯 Developer에서도 입력 받을 수 있다.

이렇게 나온다

하지만 사용하지 않는기능이다..

 

 

1_2) 

레퍼런스 타입 변수 선언 및 초기화(어떤 테이블의 어떤 컬럼의 데이터 타입을 참조해서 그 타입으로 지정)

 

DECLARE
    EID EMPLOYEE.EMP_ID%TYPE;
    ENAME EMPLOYEE.EMP_NAME%TYPE;
    SAL EMPLOYEE.SALARY%TYPE;
BEGIN
--    EID :=800;
--    ENAME :='울랄라';
--    SAL := 10000000;

    --사번이 200번인 사원의 사번, 사원명, 급여 조회해서 각 변수에 대입
    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;
/

선언부에 보면 %TYPE으로 우리가 테이블 만들 때 적용해놓은 TYPE을 그대로 복사해올 수 있다.

 

1_3) ROW 타입 변수 선언

테이블의 한 행에 대한 모든 컬럼값을 한꺼번에 담을 수 있는 변수
[표현식] 변수명 테이블명 %ROWRYPE;
DECLARE
    E EMPLOYEE%ROWTYPE;
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('보너스 : ' || NVL(E.BONUS, 0));
END;
/

 

 

2. BEGIN 실행부

<조건문>  1) IF 조건식 THEN 실행내용 END IF; (IF문만 단독으로 사용할 때)

 

DECLARE
    EID EMPLOYEE.EMP_ID%TYPE;
    ENAME EMPLOYEE.EMP_NAME%TYPE;
    SAL EMPLOYEE.SALARY%TYPE;
    BONUS EMPLOYEE.BONUS%TYPE;
BEGIN
    SELECT EMP_ID, EMP_NAME, SALARY, NVL(BONUS,0)
    INTO EID, ENAME, SAL, BONUS
    FROM EMPLOYEE
    WHERE EMP_ID = &사번;
    
    DBMS_OUTPUT.PUT_LINE('사번 : ' || EID);
    DBMS_OUTPUT.PUT_LINE('사원명 : ' || ENAME);
    DBMS_OUTPUT.PUT_LINE('급여 : ' || SAL);
    
    IF BONUS = 0
        THEN DBMS_OUTPUT.PUT_LINE('보너스를 지급받지 않은 사원입니다.');
    END IF;
    DBMS_OUTPUT.PUT_LINE('보너스 : ' || BONUS);
END;
/

 

 

당연히 IF ESLE도 지원한다.

DECLARE
    EID EMPLOYEE.EMP_ID%TYPE;
    ENAME EMPLOYEE.EMP_NAME%TYPE;
    SAL EMPLOYEE.SALARY%TYPE;
    BONUS EMPLOYEE.BONUS%TYPE;
BEGIN
    SELECT EMP_ID, EMP_NAME, SALARY, NVL(BONUS,0)
    INTO EID, ENAME, SAL, BONUS
    FROM EMPLOYEE
    WHERE EMP_ID = &사번;
    
    DBMS_OUTPUT.PUT_LINE('사번 : ' || EID);
    DBMS_OUTPUT.PUT_LINE('사원명 : ' || ENAME);
    DBMS_OUTPUT.PUT_LINE('급여 : ' || SAL);
    
    IF BONUS = 0
        THEN DBMS_OUTPUT.PUT_LINE('보너스를 지급받지 않은 사원입니다.');
    ELSE
        DBMS_OUTPUT.PUT_LINE('보너스 : ' || BONUS * 100  || '%');
    END IF;
    
END;
/

 

 

<반복문>

1) BASIC LOOP문 [표현식]
LOOP 반복적으로 실행할 구문;
*반복문을 빠져나갈 수 있는 구문 END LOOP;
*반복문을 빠져나갈 수 있는 구문
1) IF 조건식 THEN EXIT; END IF;
2) EXIT WHEN 조건식;
DECLARE
    I NUMBER := 1;
BEGIN
    LOOP
        DBMS_OUTPUT.PUT_LINE(I);
        I := I + 1 ;
        
        --IF I = 6 THEN EXIT; END IF;
        EXIT WHEN I =6;
    END LOOP;

END;
/

 

 

2) FOR LOOP문
[표현식]
FOR 변수 IN [REVERSE] 초기값..최종값
LOOP
      반복적으로 실행할 문장
END LOOP;

 

BEGIN
    FOR I IN 1..5 -- 1부터 5까지 반복
    LOOP
    DBMS_OUTPUT.PUT_LINE(I);
    END LOOP;
END;
/

BEGIN --반대로 진행하기
    FOR I IN REVERSE 1..5 -- 5부터 1까지 반복
    LOOP
    DBMS_OUTPUT.PUT_LINE(I);
    END LOOP;
END;
/

 

시퀀스를 같이 사용하여 값을 계속 넣어줄 수도 있다.

CREATE SEQUENCE SEQ_TNO
START WITH 1
INCREMENT BY 2
MAXVALUE 1000
NOCYCLE
NOCACHE;

BEGIN
    FOR I IN 1..100
    LOOP
        INSERT INTO TEST VALUES(SEQ_TNO.NEXTVAL, SYSDATE);
    END LOOP;
END;
/

이렇게 되면 1부터 100까지 시퀀스를 사용하여 기본키 값을 자동으로 넣어주게 된다.

 

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

 

 

3. 예외처리부

예외(EXCEPTION) : 실행 중 발생하는 오류

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

*시스템 예외(오라클에서 미리 정의해둔 예외)
-NO_DATA_FOUND : SELECT한 결과가 한 행도 없을 때
-TOO_MANY_ROWS : SELECT한 결과가 너무 많은 행일 경우
-ZERO_DIVIED : 0으로 나눌 때
-DUP_VAR_ON_INDEX : UNIQUE 제약조건에 위해되었을 경우
...

 

DECLARE
    RESULT NUMBER;
BEGIN
    RESULT  :=10 / &숫자;
    DBMS_OUTPUT.PUT_LINE('결과 : ' || RESULT);
EXCEPTION
    WHEN ZERO_DIVIDE THEN DBMS_OUTPUT.PUT_LINE('나누기 연산시 0으로 나눌 수 없다.');
END;
/

이런 느낌으로 사용한다

 

 

 

 

 

 

 

 

 

데이터 베이스의 끝이 보인다..

하지만 오늘 배운 PL/SQL은 뭔가 DB의 역할이 아닌데 그래도 언어라서 조건문, 반복문을 넣어준게 아닌가? 라는 생각이 들었다.

아니나 다를까 알아보니 DB에서 이런 것을 사용할 일은 진짜 거ㅓㅓㅓㅓㅓ의 없다고 한다...

그래도 배워놓으면 언젠가 쓸 곳이 있지 않을까 생각해본다!!