레코드(RECORD)

레코드(RECORD)

레코드는 PL/SQL에서 제공하는 데이터 타입 중 하나이다.

기본 빌트인 타입(문자형, 숫자형 등)과는 달리 복합형 구조인데,

이는 다른 타입의 여러 개의 값을 가질 수 있음을 뜻한다.

테이블과 흡사하지만 

테이블과 달리 레코드는 하나의 로우만을 가질 수 있다.

따라서 레코드는 서로 다른 유형의 데이터 타입을 가진 변수 여러 개가 한줄로 쭉 이어져있는 형태라고 생각하자.

사용자 정의형 레코드

레코드는 사용자가 직접 정의할 수 있다.

위에서 언급했듯이 테이블과 비슷한 구성이며(대신 하나의 로우만을 가짐)

테이블의 컬럼에 해당하는 것을 필드라고 칭한다.

TYPE 레코드명 IS RECORD (
    필드명1 필드1타입 [[NOT NULL] := 디폴트 값]
  , 필드명2 필드2타입 [[NOT NULL] := 디폴트 값]
  ...
  );
레코드변수명 레코드명;

커서 변수를 사용하듯이

레코드 또한 타입을 먼저 선언 후 해당 타입에 대한 변수를 선언한다.

NOT NULL 여부와 디폴트 값은 선택사항이다.

그럼 부서 테이블과 같은 구조(날짜 컬럼 제외)의 레코드를 선언해보자.

DECLARE
    -- 부서레코드 타입 선언
    TYPE depart_rect IS RECORD(
        department_id   NUMBER(6)
      , department_name VARCHAR(80)
      , parent_id       NUMBER(6)
      , manager_id      NUMBER(6)
    );
    
    --위에서 선언된 타입으로 레코드 변수 선언
    vr_dep depart_rect;
    
    BEGIN
    ...
    END;

여기서 레코드 속 각각의 필드 타입을 선언할 때

필요한 테이블에서 가져올 수 있다.

즉 변수처럼 %TYPE을 사용할 수 있다.

    TYPE depart_rect IS RECORD(
        department_id   departments.department_id%TYPE
      , department_name departments.department_name%TYPE
      , parent_id       departments.parent_id%TYPE
      , manager_id      departments.manager_id%TYPE
    );
    
    -- 위에서 선언된 타입으로 레코드 변수 선언
    vr_dep depart_rect;
    
    BEGIN
    ...
    END;

선언을 했으니 레코드 변수의 데이터에 접근해보자.

레코드의 각 필드는 '레코드변수명.필드명'으로 참조할 수 있다.

직접 값을 할당하고 데이터를 출력해보자.

DECLARE
    -- 부서 레코드 타입 선언
    TYPE depart_rect IS RECORD(
        department_id   departments.department_id%TYPE
      , department_name departments.department_name%TYPE
      , parent_id       departments.parent_id%TYPE
      , manager_id      departments.manager_id%TYPE
    );
    
    -- 위에서 선언된 타입으로 레코드 변수 선언
    vr_dep depart_rect;
    -- 두 번째 변수 선언
    vr_dep2 depart_rect;
    BEGIN
        vr_dep.department_id := 99;
        vr_dep.department_name := '테스트부서';
        vr_dep.parent_id := 100;
        vr_dep.manager_id := NULL;
        
        -- 두 번째 변수에 첫 번째 레코드 변수 대입
        vr_dep2 := vr_dep;
        
        -- 두 번째 변수를 출력
        DBMS_OUTPUT.PUT_LINE('vr_dep2.department_id : ' || vr_dep2.department_id);
        DBMS_OUTPUT.PUT_LINE('vr_dep2.department_name : ' || vr_dep2.department_name);
        DBMS_OUTPUT.PUT_LINE('vr_dep2.parent_id : ' || vr_dep2.parent_id);
        DBMS_OUTPUT.PUT_LINE('vr_dep2.manager_id : ' || vr_dep2.manager_id);
    END;

vr_dep와 vr_dep2라는 두 개의 레코드 변수를 선언하고

vr_dep 변수의 각 필드의 값을 할당 해준 뒤

vr_dep2에 vr_dep를 할당하여 vr_dep2의 각 필드를 출력하였다.

이렇게 동일한 타입의 레코드 변수를 할당하면 모든 필드 값이 할당된다.

또한 레코드 전체가 아닌 필드를 선택적으로 할당도 가능하다.

예를 들어서

vr_dep2.department_name := vr_dep.department_name;

이렇게 선택적으로 하나씩 넣을 수도 있다.

당연한 말이지만

레코드 변수끼리 할당할 때는 두 레코드 변수의 타입, 즉 전체 필드의 순서, 구조, 데이터 타입등이 모두 같아야 하며

필드만 할당할 때는 필드의 데이터 타입이 같아야한다.

 

또한 레코드 변수를 사용해서 테이블에 INSERT, UPDATE를 할 수도 있다.

테스트를 해보자.

먼저, 테스트를 위해 부서테이블의 복사본을 만든다.

CREATE TABLE chll_dep AS
    SELECT department_id, department_name, parent_id, manager_id
      FROM DEPARTMENTS
     WHERE 1=2;

빈 테이블(부서테이블의 껍데기만)을 만들기 위해 WHERE 1=2 조건을 주었으며

조건을 뺀후 TRUNCATE를 해도 상관없다.

이제 레코드 변수를 선언 후에 값을 할당하고 데이터를 넣어보자.

DECLARE
    -- 부서 레코드 타입 선언
    TYPE dept_rect IS RECORD(
        department_id   departments.department_id%TYPE
      , department_name departments.department_name%TYPE
      , parent_id       departments.parent_id%TYPE
      , manager_id      departments.manager_id%TYPE
    );
    
    -- 위에서 선언된 타입으로 레코드 변수 선언
    vr_dep dept_rect;
    
    BEGIN
            vr_dep.department_id := 999;
            vr_dep.department_name := '테스트부서';
            vr_dep.parent_id := 100;
            vr_dep.manager_id := NULL;
            
            -- 레코드 필드를 명시해서 INSERT
            INSERT INTO chll_dep VALUES(vr_dep.department_id, vr_dep.department_name, vr_dep.parent_id, vr_dep.manager_id);
            
            -- 레코드 필드 순서와 개수, 타입이 모두 같다면 레코드 변수명 만으로도 INSERT 가능
            INSERT INTO chll_dep VALUES vr_dep;
            
            commit;
    END;

INSERT 결과를 보면

동일하게 두 건의 데이터를 확인할 수 있으며

UPDATE 문에도 동일하게 레코드를 사용할 수 있다.

테이블형 레코드

특정 테이블의 모든 컬럼을 받아 사용하는 레코드를 선언할 때

일일이 필드를 하나씩 선언해줄 필요가 없다.

'%ROWTYPE'으로 선언해주면 해당 테이블의 모든 컬럼을 타입 그대로 가져오게 된다.

이번에는 '%ROWTYPE'을 사용해보자.

먼저, 부서테이블의 복사본을 만든다.

CREATE TABLE chll_dep2 AS
    SELECT *
      FROM departments
     WHERE 1=2;

이제 테이블형 레코드 변수를 선언해서 사용해보자.

부서 테이블에서 데이터를 가져와 레코드 변수에 넣은 후

이 변수로 데이터를 chll_dep2테이블에 넣을 것이다.

DECLARE
    -- 테이블형 레코드 변수 선언
    vr_dep departments%ROWTYPE;
    
    BEGIN
        -- 부서 테이블의 모든 정보를 레코드 변수에 넣는다.
        SELECT *
          INTO vr_dep
          FROM departments
         WHERE department_id = 20;
         
        -- 레코드 변수를 이용해 ch11_dep2 테이블에 데이터를 넣는다.
        INSERT INTO chll_dep2 VALUES vr_dep;
        
        COMMIT;
    END;

결과를 보면 

원하는대로 잘 들어간 것을 확인할 수 있다.

커서형 레코드

커서형 레코드란 커서를 레코드 변수로 받는 것을 말한다.

참고로 커서가 기억이 안난다면 아래의 두 글을 보면 된다.

https://fox-dev-diary.tistory.com/entry/%EC%BB%A4%EC%84%9CCURSOR

묵시적 커서(CURSOR)

https://fox-dev-diary.tistory.com/entry/%EB%AA%85%EC%8B%9C%EC%A0%81-%EC%BB%A4%EC%84%9CCURSOR

명시적 커서(CURSOR)

 

커서형 레코드 변수 역시 테이블형 레코드처럼 %ROWTYPE을 사용한다. EX) 커서명%ROWTYPE

이제 명시적 커서를 선언 후에 레코드 면수에 담고 다시 chll_dep 테이블에 데이터를 넣어보자.

DECLARE
    -- 커서 선언
    CURSOR c1
    IS
    SELECT department_id, department_name, parent_id, manager_id
      FROM departments;
      
    -- 커서형 레코드 변수 선언
    vr_dep c1%ROWTYPE;
    
    BEGIN
        -- 데이터 삭제
        DELETE chll_dep;

        -- 커서 오픈
        OPEN c1;
        
        -- 루프를 돌며 vr_dep 레코드 변수에 값을 담고 다시 chll_dep에 INSERT
        LOOP
            FETCH c1 INTO vr_dep;
            EXIT WHEN c1%NOTFOUND;
            -- 레코드 변수를 이용해 chll_dep 테이블에 데이터 삽입
            INSERT INTO chll_dep VALUES vr_dep;
        END LOOP;
        
        CLOSE c1;
        
        COMMIT;
    END;

데이터를 조회해보면 원하는대로 정상적으로 잘 조회가 된다.

자, 지금까지는 레코드 변수를 사용해서 INSERT를 해왔다.

이제 UPDATE문도 한번 사용해보자.

사용하는 방법은 간단한데

SET 절에서 갱신할 컬럼을 명시하고 해당 컬럼 값으로는 '레코드변수.필드명'을 사용하면 된다.

그런데 만약 테이블의 모든 컬럼을 갱신하려면

SET 절에 모든 컬럼과 레코드 변수의 필드를 적어주어야 할까?

그것은 매우 번거롭기 때문에 방법이 있다.

INSERT 문처럼 컬럼 수와 순서, 타입이 같다면

간단하게 레코드 변수만 기술해서 UPDATE를 할 수 있다.

ROW 키워드를 사용하는 것인데 밑에 예제를 보자.

DECLARE
    -- 레코드 변수 선언
    vr_dep  chll_dep%ROWTYPE;
    
    BEGIN
        vr_dep.department_id := 20;
        vr_dep.department_name := '테스트';
        vr_dep.parent_id := 10;
        vr_dep.manager_id := 200;
        
        -- ROW를 사용하면 해당 로우 전체가 갱신됨
        UPDATE chll_dep
          SET ROW = vr_dep
        WHERE department_id = vr_dep.department_id;
        
        COMMIT;
    END;

결과를 확인해보면

원래 마케팅 부서였지만 값이 바뀐 것을 확인할 수 있다.

ROW 키워드를 사용하면 레코드로 전체 행을 갱신할 수 있는데

주의할 점은 해당 테이블의 컬럼 개수, 순서, 타입과 레코드의 필드가 모두 동일해야 한다.

사실 로우 전체를 갱신하는 일은 드물기 때문에 

보통 컬럼명과 레코드 필드명을 모두 기술하긴 한다.

중첩 레코드

레코드를 선언할 때

필드 타입을 이미 선언된 다른 레코드 타입으로 선언할 수 있다.

이를 중첩 레코드라고 한다.

즉 레코드의 필드의 데이터 타입이 레코드인 것이다.

예제를 보자.

DECLARE
    -- 부서번호, 부서명을 필드로 가지는 dep_rec 레코드 타입 선언
    TYPE dep_rec IS RECORD (
        dep_id   departments.department_id%TYPE
      , dep_name departments.department_name%TYPE
    );
    
    -- 사번, 사원명, 그리고 dep_rec 타입의 레코드 선언
    TYPE emp_rec IS RECORD (
        emp_id   employees.employee_id%TYPE
      , emp_name employees.emp_name%TYPE
      , dep      dep_rec   -- 필드의 타입이 레코드
    );
    
    --emp_rec 타입의 레코드 변수 선언
    vr_emp_rec emp_rec;
    
    BEGIN
        -- 100번 사원의 사번, 사원명, 부서번호, 부서명을 가져옴
        SELECT a.employee_id, a.emp_name, a.department_id, b.department_name
          INTO vr_emp_rec.emp_id, vr_emp_rec.emp_name, vr_emp_rec.dep.dep_id, vr_emp_rec.dep.dep_name
          FROM employees a, departments b
         WHERE a.employee_id = 100
           AND a.department_id = b.department_id;
        
        -- 레코드 변수 값 출력
        DBMS_OUTPUT.PUT_LINE('emp_id : ' || vr_emp_rec.emp_id);
        DBMS_OUTPUT.PUT_LINE('emp_iname : ' || vr_emp_rec.emp_name);
        DBMS_OUTPUT.PUT_LINE('dep_id : ' || vr_emp_rec.dep.dep_id);
        DBMS_OUTPUT.PUT_LINE('dep_name : ' || vr_emp_rec.dep.dep_name);
    END;

emp_rec라는 레코드의 필드 중 dep라는 필드의 타입은 dep_rec라는 레코드 타입이다.

이처럼 레코드 타입의 필드를 가진 레코드가 중첩 레코드이다.

데이터는 .으로 하나씩 타고 들어가면서 참조가 가능하다.

한 가지 주의할 점은 잘 생각해보면 알겠지만

중첩 레코드는 SELECT INTO, INSERT 문의 VALUES, UPDATE 문의 SET ROW 절에서

일반 레코드처럼 레코드 변수명만 기술할 수는 없고

각 레코드 필드명을 일일이 기술해줘야 한다.