[오라클로 배우는 데이터베이스 입문] 17. 레코드와 컬렉션

16장 pl/sql에 이은 내용이다.

변수의 종류에서 스칼라형, 참조형, 복합형, lob형중에 레코드와 컬렉션은 복합형에 해당한다.

레코드

레코드는 자료형이 각기 다른 데이터를 하나의 변수에 저장하는 데 사용.
(객체지향 프로그래밍언어의 구조체, 클래스개념과 비슷함)

레코드 정의해서 사용하는법

DECLARE
    TYPE REC_DEPT IS RECORD(
        deptno NUMBER(2) NOT NULL :=99,
        dname DEPT.DNAME%TYPE,
        loc DEPT.LOC%TYPE
    );
    dept_rec REC_DEPT; --dept_rec가 레코드REC_DEPT의 변수명이다.
    
BEGIN
    dept_rec.deptno := 99;
    dept_rec.dname := 'DATABASE';
    dept_rec.loc := 'SEOUL';
    DBMS_OUTPUT.PUT_LINE('DEPTNO : ' || dept_rec.deptno);
    DBMS_OUTPUT.PUT_LINE('DNAME : ' || dept_rec.dname);
    DBMS_OUTPUT.PUT_LINE('LOC : ' || dept_rec.loc);
END;
/

레코드를 사용한 INSERT

PL/SQL 문에서 테이블에 데이터를 삽입 수정하는 INSERT, UPDATE문에도 레코드를 사용할수있다.

레코드를 사용할테이블 DEPT_RECORD를 먼저만들고

CREATE TABLE DEPT_RECORD
AS SELECT * FROM DEPT;

기존 INSERT문에는 VALUES에 하나하나씩 입력해야했는데,
레코드를 선언해놓으면 VALUES절에 레코드 이름만 명시해도됨.

DECLARE
    TYPE REC_DEPT IS RECORD(
        deptno NUMBER(2) NOT NULL := 99,
        dname DEPT.DNAME%TYPE,
        loc DEPT.LOC%TYPE
    );
    dept_rec REC_DEPT;
BEGIN
    dept_rec.deptno :=99;
    dept_rec.dname := 'DATABASE';
    dept_rec.loc := 'SEOUL';
    
INSERT INTO DEPT_RECORD
VALUES dept_rec;
END;
/

데이터가 추가된것을 볼수있다.

레코드를 사용한 UPDATE

UPDATE문에도 마찬가지로 사용이 가능하다.

DECLARE
    TYPE REC_DEPT IS RECORD(
        deptno NUMBER(2) NOT NULL := 99,
        dname DEPT.DNAME%TYPE,
        loc DEPT.LOC%TYPE
    );
    dept_rec REC_DEPT;
BEGIN
    dept_rec.deptno :=50;
    dept_rec.dname := 'DB';
    dept_rec.loc := 'SEOUL';
    
    UPDATE DEPT_RECORD
    SET ROW = dept_rec
    WHERE DEPTNO = 99;
END;
/

데이터가 업데이트됐다.

레코드를 포함하는 레코드

레코드에 포함된 변수의 자료형을 지정할 때 다른 레코드를 지정할 수도 있다.

DECLARE
    TYPE REC_DEPT IS RECORD(
        deptno DEPT.DEPTNO%TYPE,
        dname DEPT.DNAME%TYPE,
        loc DEPT.LOC%TYPE
    );
    TYPE REC_EMP IS RECORD(
        empno EMP.EMPNO%TYPE,
        ename EMP.ENAME%TYPE,
        dinfo REC_DEPT
    );
    emp_rec REC_EMP;
BEGIN
    SELECT E.EMPNO, E.ENAME, D.DEPTNO, D.DNAME, D.LOC
    INTO emp_rec.empno,emp_rec.ename,emp_rec.dinfo.deptno,emp_rec.dinfo.dname,emp_rec.dinfo.loc
    FROM EMP E, DEPT D
    WHERE E.DEPTNO = D.DEPTNO
    AND E.EMPNO = 7788;
    
    DBMS_OUTPUT.PUT_LINE('EMPNO : '|| emp_rec.empno);
    DBMS_OUTPUT.PUT_LINE('ENAME : '|| emp_rec.ename);
    DBMS_OUTPUT.PUT_LINE('DEPTNO : '|| emp_rec.dinfo.deptno);
    DBMS_OUTPUT.PUT_LINE('DNAME : '|| emp_rec.dinfo.dname);
    DBMS_OUTPUT.PUT_LINE('LOC : '|| emp_rec.dinfo.loc);
END;
/

※ 방금알았는데 DBMS_OUTPUT.PUT_LINE 가 출력이 안될때는 SET SERVEROUTPUT ON;을 써줘야 한다. 기본값이 OFF로 되어있어서 출력이안된듯..

컬렉션

컬렉션은 특정 자료형의 데이터를 여러개 저장하는 복합 자료형이다.

PL/SQL에서 사용할수있는 컬렉션은 3종류가있다.

• 연관배열
• 중첩테이블
• VARRAY
  이 책에서는 사용빈도가 가장 높은 연관 배열에대해서 다루었다.

연관배열

json처럼 key-value로 구성되는 컬렉션.
키는 중복되지 않는 값이다.

DECLARE
    TYPE ITAB_EX IS TABLE OF VARCHAR2(20)
INDEX BY PLS_INTEGER;
    
    text_arr ITAB_EX;
    
BEGIN
    text_arr(1) := '1st data';
    text_arr(2) := '2nd data';
    text_arr(3) := '3rd data';
    text_arr(4) := '4th data';
    
    DBMS_OUTPUT.PUT_LINE('text_arr(1) : '|| text_arr(1));
    DBMS_OUTPUT.PUT_LINE('text_arr(2) : '|| text_arr(2));
    DBMS_OUTPUT.PUT_LINE('text_arr(3) : '|| text_arr(3));
    DBMS_OUTPUT.PUT_LINE('text_arr(4) : '|| text_arr(4));
END;
/

연관배열 자료형에도 레코드를 사용할 수 있다.
특정테이블의 전체 열과 같은 구성을 가진 연관 배열을 만든다면 %ROWTYPE으로 참조형을 사용하는것이 간편할것이다.

컬렉션 메소드

오라클에셔 컬렉션을 사용하기 편리하게하기위해 제공하는 메소드

• EXISTS(n) : 컬렉션에서 n인덱스의 데이터 존재여부를 T/F로 반환
• COUNT : 컬렉션에 포함되어 있는 요소 개수를 반환
• LIMIT : 현재 컬렉션의 최대 크기 반환
• FIRST : 컬렉션의 첫번째 인덱스 번호 반환
• LAST : 컬렉션의 마지막 인덱스 번호 반환
• PRIOR(n) : 컬렉션에서 n인덱스 바로 앞 인덱스 값 반환 없으면 NULL반환
• NEXT(n) : 컬렉션에서 n인덱스 바로 다음 인덱스 값 반환 없으면 NULL반환
• DELETE : 컬렉션에 저장된 요소 지우는데 사용
• EXTEND : 컬렉션 크기 증가
• TRIM : 컬렉션 크기 감소

컬렉션 메소드 사용하기

DECLARE
    TYPE ITAB_EX IS TABLE OF VARCHAR2(20)
INDEX BY PLS_INTEGER;

    text_arr ITAB_EX;
    
BEGIN
    text_arr(1) := '1st data';
    text_arr(2) := '2nd data';
    text_arr(3) := '3rd data';
    text_arr(50) := '50th data';
    
    DBMS_OUTPUT.PUT_LINE('text_arr.COUNT : '|| text_arr.COUNT);
    DBMS_OUTPUT.PUT_LINE('text_arr.FIRST : '|| text_arr.FIRST);
    DBMS_OUTPUT.PUT_LINE('text_arr.LAST : '|| text_arr.LAST);
    DBMS_OUTPUT.PUT_LINE('text_arr.PRIOR(50) : '|| text_arr.PRIOR(50));
    DBMS_OUTPUT.PUT_LINE('text_arr.NEXT(50) : '|| text_arr.NEXT(50));
    
END;
/