오라클 DBMS 정리 10

08 Nov 2018

Reading time ~4 minutes

오라클 DBMS 정리 10 - PL/SQL 제어문, RECORD TYPE, TABLE TYPE

반복문

LOOP, FOR, WHILE
- 코드를 여러번 실행하는 일
무한 LOOP에 들어가지 않도록 주의!
- 반복문을 빠져나가는 EXIT과 같이 사용될 수 있다
무한 LOOP에서의 출력문은 동작하지 않는다
- 계속 실행되면서 메모리를 다 잡아먹다가 죽어버림
- cmd에서 메모리 점유를 끊는것 - 인터럽트(Ctrl + C)
PL/SQL에서 FOR, WHILE은 LOOP를 사용하기위한 방편일 뿐, 반복은 LOOP가 함
- LOOP, END LOOP를 꼭 붙임
자바나 C에 do ~ while처럼 한번은 돌고 조건에 맞지 않으면 빠져나감

BEGIN

    LOOP 
        반복해야될 문장들;

        IF 조건 THEN
            EXIT;
        END IF;

    END LOOP;

END;

-- 또는

BEGIN

    LOOP
        반복해야될 문장들;

        EXIT WHEN (조건);

    END LOOP;

END;

-- 1에서부터 100까지 짝수만 출력
DECLARE
    cnt NUMBER(3);
BEGIN
    cnt := 1;
                      
    LOOP
        IF MOD(cnt, 2) = 0 THEN
            DBMS_OUTPUT.PUT(cnt||' ');
        END IF;
        cnt := cnt+1;
        EXIT WHEN (cnt > 100);
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('');
END;        

FOR

시작과 끝을 보기좋게 만들어 1씩 증가하여 반복시킬 때 사용
CURSOR를 실행시킬 때 사용
FOR문 인덱스변수는 선언하지 않고 사용가능!
- 시작에서 끝가지 1씩 증가하는 값을 저장하는 변수

BEGIN
    
    FOR 변수명 IN 시작 ..끝 LOOP
        
        반복시킬 문장들;
    END LOOP;

END;

-- 구구단 만들기
SET serveroutput ON
SET verify OFF
      
ACCEPT input PROMPT '검색할 구구단 수를 입력하세요 : '

DECLARE                                               
    input NUMBER;
    i NUMBER := 1;
BEGIN            
    input := &input;
     
     FOR i IN 1 ..9 LOOP
         DBMS_OUTPUT.PUT_LINE(input||' * '||i||' = '||input*i);    
     END LOOP;

    DBMS_OUTPUT.PUT_LINE(i);
END;
/

10-01

다중 FOR
- 인덱스를 여러개 사용해야할 경우
  - 테이블은 1차원이기 때문에 다중 FOR 사용할 일은 적다

BEGIN

    FOR 변수명 IN 시작 ..끝 LOOP
        -- 단일 FOR영역
        FOR 변수명 IN 시작 ..끝 LOOP
        -- 다중 FOR영역, 인덱스 두개 사용 가능
        -- (바깥FOR 인덱스, 안FOR 인덱스)
        END LOOP;
        -- 단일 FOR영역
    END LOOP;

END;

-- 다중 FOR 구구단
SET serveroutput ON
DECLARE
BEGIN
    -- 바깥 FOR의 인덱스는 단을 위해 사용
    FOR i IN 2..9 LOOP
        DBMS_OUTPUT.PUT_LINE(i||'단 시작');
        -- 안쪽 FOR의 인덱스는 곱해지는 수를 위해 사용
        FOR j IN 2..9 LOOP
            DBMS_OUTPUT.PUT_LINE(i||' * '||j||' = '||i*j);
        END LOOP;        
        DBMS_OUTPUT.PUT_LINE(i||'단 끝');
    END LOOP;
END;
/

WHILE

최송 0번 수행에 최대 조건까지 수행할 때 사용
- LOOP는 최초 한번은 수행하고 조건비교
- LOOP와 마찬가지로 변수가 필요
  - FOR 은 변수선언 필요없음

BEGIN
    초기값;
    WHILE 조건식 LOOP

        반복할 수행 문장;

    증가식, 감소식;
    END LOOP;

END;

-- 0 ~ 100 사이의 홀수만 출력하는 WHILE
DECLARE
    num NUMBER := 0;
BEGIN
    
    WHILE num < 100 LOOP
        
        num := num + 1;

        IF MOD(num, 2) = 1 THEN
            DBMS_OUTPUT.PUT(num||' ');    
        END IF;   
         
    END LOOP;              
    
    DBMS_OUTPUT.PUT_LINE('');
END;
/

TABLE 데이터형

PL/SQL의 테이블 != SQL의 테이블
테이블은 레코드를 저장하기 위한 객체
- PL/SQL에서는 배열을 지원하지 않고 사용자가 만들어서 사용
- 인덱스를 가지며 순차처리를 할 수 있는 데이터형
  - 배열 또는 리스트(파이썬)와 비슷한 객체
사용법

TABLE 데이터형 정의 (연속적으로 나열될 데이터형의 이름을 정의, DECLARE ~ BEGIN 사이)
테이블로 변수를 선언
사용 (BEGIN ~ END, 오라클에선 인덱스는 1부터 시작)

-- 1.
DECLARE
    TYPE 테이블타입명 IS TABLE OF 
     테이블을구성할데이터형 
     INDEX BY BINARY_INTEGER;

-- 테이블을구성할데이터형 - 방(요소,element)의 데이터형
-- BY BINARY_INTEGER - 테이블에서 사용할 수 있는 인덱스의 수(21억개)

    -- 2.
    변수명 테이블타입명;
BEGIN

    -- 3.
    변수명(인덱스) := 값;

    -- 테이블타입변수의 레코드를 반환하는 변수명.COUNT 사용가능;
END;

DECLARE
    -- 1, 2 테이블 데이터형 정의, 변수로 선언
    TYPE num_tab IS TABLE OF NUMBER 
        INDEX BY BINARY_INTEGER;

    nt num_tab;
    
BEGIN                                                       
    -- 3. 사용
    nt(1) := 10;
    nt(2) := 20;
    nt(3) := 30;    

    -- 4. 테이블 변수의 길이(방의 갯수)만큼 반복해 모든 값 출력
    FOR i IN 1..nt.COUNT LOOP
        DBMS_OUTPUT.PUT_LINE(nt(i));
    END LOOP;

END;
/

RECORD

여러 종류의 데이터형을 묶어서 하나의 데이터형으로 사용하는 것
- ROWTYPE의 경우 들어갈 변수들을 결정하지 못하지만 RECORD타입은 원하는 변수들을 결정할 수 있다
레코드변수는 테이블변수와 같이 사용
사용법
1. RECORD 데이터형 선언
2. 레코드 변수 선언
3. 값 설정
4. 값 사용

DECLARE
    -- 1.
    TYPE 레코드타입명 IS RECORD (
        변수명 데이터형(크기),
        ...
    );
    -- 2.
    레코드변수명 레코드타입명;
BEGIN
    -- 3.
    레코드변수명.변수명 := 값;
    -- 4.
    ... := 레코드변수명.변수명;

DECLARE
    TYPE rec IS RECORD (
        name VARCHAR2(10),
        addr VARCHAR2(10),
        age NUMBER
    );             
     
    data rec;
BEGIN
    
    data.name := '오영근';
    data.addr := '서울';
    data.age := 29;
    
    DBMS_OUTPUT.PUT_LINE(data.name||' '||data.addr||' '||data.age);
END;
/

-- RECORD : 사용하고 싶은 데이터형을 묶어 하나의 데이터형으로 사용하는 것
DECLARE
    
    -- 1. 레코드 선언 : 레코드에 포함될 변수를 설정하는 것
    TYPE score_record IS RECORD (
        num NUMBER(4),
        name VARCHAR2(30),
        oracle_score NUMBER(3),
        java_score NUMBER(3)    
    );                       
    
    -- 2. 레코드로 변수 생성
    student score_record;

BEGIN                     

  -- 3. 값 할당
    student.num := 0001;
    student.name := 'young';
    student.oracle_score := 88;
    student.java_score := 60;
    
    -- 4. 값 사용
    DBMS_OUTPUT.PUT_LINE('번호 : '||student.num||', 이름 : '
      ||student.name||', 오라클점수 : '
      ||student.oracle_score||', 자바점수 : '
      ||student.java_score||', 총점 : '
      ||(student.oracle_score+student.java_score));
      
END;
/       

테이블타입과 레코드타입 같이 사용

레코드형을 만들고, 테이블형에 담아 FOR나 WHILE로 자동화처리가 가능해짐
- 일괄처리

RECORD타입 선언
선언된 레코드타입 이용 TABLE타입 선언
TABLE타입으로 테이블변수 선언
TABLE변수에 값 할당
반복문으로 이용

DECLARE
    TYPE 레코드타입명 IS RECORD (
        변수명 데이터형(크기),
        ...
    );

    TYPE 테이블타입명 IS TABLE OF 
     레코드변수명 
     INDEX BY BINARY_INTEGER;    
    
    테이블변수명 테이블타입명;
BEGIN
    변수명(인덱스).레코드하위변수명 := 값;
    FOR 인덱스변수명 IN 시작인덱스 ..테이블변수명.COUNT LOOP
        -- 테이블변수명(인덱스변수명).레코드하위변수명 으로 값을 사용
    END LOOP;
END;
/

SET serveroutput ON

DECLARE

    -- 1. RECORD타입 선언
    TYPE emp_rec IS RECORD (
        empno emp.empno%TYPE,
        ename emp.ename%TYPE,
        hiredate emp.hiredate%TYPE,
        sal emp.sal%TYPE
    );         
    
    -- 2.선언된 레코드타입 이용 TABLE타입 선언
    TYPE emp_tab IS TABLE OF
     emp_rec
     INDEX BY BINARY_INTEGER;                 
    
    -- 3.TABLE타입으로 변수 선언
    employee emp_tab;

BEGIN              
         
    -- 4.TABLE변수에 값 할당
    employee(1).empno := 0001;       
    employee(1).ename := 'young';
    employee(1).hiredate := '2018-01-01';
    employee(1).sal := 5000;  
    
    employee(2).empno := 0002;
    employee(2).ename := 'song';
    employee(2).hiredate := '2018-02-01';
    employee(2).sal := 6000;

    -- 5.반복문으로 이용
    FOR i IN 1..employee.COUNT LOOP
        DBMS_OUTPUT.PUT_LINE(employee(i).empno||' '
            ||employee(i).ename||' '||employee(i).hiredate||' '
            ||employee(i).sal);
    END LOOP;
                   
    DBMS_OUTPUT.PUT_LINE('입사한 개월차 : '
        ||MONTHS_BETWEEN(employee(2).hiredate, employee(1).hiredate));
    DBMS_OUTPUT.PUT_LINE('연봉차 : '||ABS(employee(1).sal - employee(2).sal));

END;
/     

숙제풀이1

숙제풀이2

숙제풀이3

오라클DBMS