[Oracle] CH10 : 레코드와 컬렉션

레코드

레코드: 복수의 값을 저장할 수 있는 변수

레코드 종류
%ROWTYPE 레코드	%ROWTYPE 속성으로 선언된 변수
사용자 정의 레코드	사용자 정의의 레코드 타입으로 선언된 변수

---

사용자 정의 레코드 정의

사용자가 타입을 정의할 때, 필드 개수와 필드명과 데이터 타입 정의

ㄴ 정의한 타입과 변수명 매핑

- 사용자 정의 레코드는 NUMBER 타입이나 %ROWTYPE 속성의 변수와는 달리 타입 정의와 변수 정의의 두 단계 필요

 

- 레코드 타입 정의

사용자 고유의 레코드 타입 정의

* 필드의 데이터 타입에 %TYPE, %ROWTYPE 속성 사용 가능

* 필드에 NOT NULL 제약 조건 지정 간으

* 필드에 디폴트 값을 지정하여 초기화 가능

TYPE <레코드 타입명> IS RECORD
  (<필드명> <데이터 타입>
   [NOT NULL { := | DEFAULT} <디폴트 값>]
  [,<필드명> <데이터 타입>
   [NOT NULL { := | DEFAULT} <디폴트 값>] ...]);

- 레코드 정의

앞서 정의한 레코드 타입과 레코드명 매핑

<레코드명> <레코드 타입명>;

 

사용자 정의 레코드 사용 방법

사용자 정의 레코드는 %ROWTYPE 속성의 변수와 동일하게 사용 가능

레코드명과 필드명을 지정하여 각 필드를 참조하고, 대입 연산자(:=)와 SELECT INTO문, FETCH INTO문을 사용하여 값을 대입

DECLARE
    TYPE rec IS RECORD
      (a NUMBER(2),  -- a, b라는 두 개의 필드가 있는 레코드 타입 rec 정의
       b VARCHAR2(6));
    record_type rec;  -- 레코드 타입 rec의 사용자 정의 레코드 record_type 정의
BEGIN
    record_type.a := 1;  -- 실행부에서 레코드의 각 필드에 값을 대입
    record_type.b := 'Part1';
    DBMS_OUTPUT.PUT_LINE(record_type.a||' '||record_type.b);
END;
/
Part1

PL/SQL 처리가 정상적으로 완료되었습니다.

동일한 레코드 타입으로 선언된 레코드가 있다면 필드명을 지정하지 않고 레코드에 값 대입 가능

DECLARE
    TYPE rec IS RECORD
      (a NUBMER(2),
       b VARCHAR2(6));
    record_type  rec; -- 두 개의 레코드 타입 rec의 레코드 정의
    record_type2 rec;
BEGIN
    record_type.a := 1;
    record_type.b := 'Part1';
    record_type2 := record_type; -- 같은 레코드 타입 rec이기 때문에 필드명을 지정하지 않고 값 대입 가능
    DBMS_OUTPUT.PUT_LINE(record_type2.a||' '||record_type2.b);
END;
/
Part1

PL/SQL 처리가 정상적으로 완료되었습니다.

 

 

파라미터와 함수의 반환 값으로 레코드 사용

레코드 타입을 프로시저의 파라미터와 함수의 반환 값으로 사용하는 것이 가능

ㄴ> 레코드 타입을 지정하면 복수의 값을 통합하여 다른 프로그램에 전달 가능

CREATE OR REPLACE PROCEDURE get_record(rec dept%ROWTYPE) -- 파라미터의 데이터 타입에 %ROWTYPE 속성 변수 지정
IS
BEGIN
    DBMS_OUTPUT.PUT_LINE(rec.deptno||'
                       '||rec.dname||'
                       '||rec.loc);
END;
/

프로시저가 생성되었습니다.


-- 레코드를 전달받은 프로그램
DECLARE
  dept_row dept%ROWTYPE;
BEGIN
  SELECT * INTO dept_row FROM dept WHERE deptno = 20;
  get_record(dept_row); -- 프로시저 get_record 실행(SELECT INTO문에서 추출한 변수를 실제 파라미터에 지정)
END;
/
RESEARCH DALLAS -- 파라미터에서 한 번에 세 개의 값을 받은 것을 확인 가능

PL/SQL 처리가 정상적으로 완료되었습니다.

 

 

레코드를 사용한 데이터 삽입 및 변경

레코드를 사용하면 테이블에 값을 삽입, 업데이트하는 작업을 한꺼번에 수행 가능

 

- 레코드 삽입

ㄴ INSERT 문의 VALUES 절에 레코드 지정 가능

ㄴ 필드의 개수를 테이블 정의 또는 INTO절 컬럼 목록과 동일하게 해야 함

ㄴ 필드의 데이터 타입은 적재 대상 컬럼의 데이터 타입과 호환성을 가져야 함

INSERT INTO <테이블명> VALUES <레코드명>;

DECLARE
  rec dept%ROWTYPE;
BEGIN
  rec.deptno := 50;   -- 레코드에는 삽입할 세 개의 값을 미리 대입
  rec.dname  := 'SE';
  rec.loc    := 'DETROIT';
  
  INSERT INTO dept VALUES rec; -- 레코드를 사용하여 결정된 값 삽입
END;
/

PL/SQL 처리가 정상적으로 완료되었습니다.

SELECT * FROM dept WHERE deptno = 50;

DEPTNO   DNAME    LOC
------   ------   -------
50       SE       DETROIT

 

 

레코드를 사용한 행 데이터 변경

통상적으로 UPDATE문에서는 특정 컬럼 단위로 업데이트하지만, 레코드를 사용하면 값을 1행(row)단위로 한꺼번에 업데이트 가능. -> ROW 키워드 사용

UPDATE <테이블명> SET ROW = <레코드명>;

ROW

- ROW 키워드와 서브 쿼리를 함께 사용할 수 없음

- ROW 키워드를 사용하는 경우, 허용되는 SET절은 한 개뿐임

 

레코드를 사용하여 행을 업데이트하는 경우, 레코드를 테이블의 행 구성에 따라 지정할 필요가 있기 때문에 SET절에는 %ROWTYPE 속성을 사용하는 것이 가장 효율적.

DECLARE
  rec dept%ROWTYPE;
BEGIN
  rec.deptno := 60; -- 업데이트할 세 개의 값을 레코드에 할당하여
  rec.dname  := 'PERSONNEL'; -- DEPT 컬럼의 모든 컬럼을 한꺼번에 업데이트
  rec.loc    := 'CHICAGO';
  
  UPDATE dept SET ROW = rec WHERE deptno = 50;
END;
/

PL/SQL 처리가 정상적으로 완료되었습니다.


SELECT * FROM dept WHERE deptno = 60;

DEPTNO  DNAME       LOC
------  ---------  ------------
60	    PERSONNEL   CHICAGO

---

컬렉션

컬렉션 : 동일한 데이터 타입의 복수의 값을 받는 변수

컬렉션을 레코드와 달리 요소 및 인덱스로 구성되며, 번호를 사용하여 각 요소에 액세스

변수를 배열로 처리하는 것이 가능

 

세 종류의 컬렉션

- 연관 배열(Associative Array)

- 중첩 테이블(NESTED TABLE)

- VAARAY

 

PL/SQL에서 컬렉션을 조작할때는 일반적으로 연관 배열을 가장 많이 사용

---

연관 배열(Associative Array)

연관 배열을 정의하려면 먼저 TABLE 타입을 정의할 필요가 있음

ㄴ 타입 정의 시에 인덱스와 요소의 데이터 타입을 정의. 레코드와 같이 타입 정의와 변수 정의의 두 단계 필요

 

* 연관 배열 정의

- 타입 정의 

: 연관 배열을 사용하려면 먼저 TABLE 타입 정의

ㄴ TABLE OF 뒤에 TABLE 타입 요소의 데이터 타입 지정

ㄴ INDEX BY 절 뒤에 지정한 데이터 타입이 인덱스의 데이터 타입

TYPE <테이블 타입> IS TABLE OF <데이터 타입> [NOT NULL]
  INDEX BY {
    BINARY_INTEGER | PLS_INTEGER | <문자 데이터 타입> (<상한 사이즈>)
};

 

- 연관 배열 정의 : 앞서 정의한 테이블 타입과 연관 배열명 매핑

<연관 배열명> <테이블 타입명>;

 

 

*연관 배열 사용 방법

연관 배열의 요소를 참조하려면 인덱스 번호 사용

요소에 값을 대입하는 경우는 대입 연산자(:=), SELECT INTO문, FETCH INTO문 사용

-- 연관 배열 참조
<연관 배열명>(<인덱스 번호>);

DECLARE
  TYPE tab IS TABLE OF VARCHAR2(10) INDEX BY PLS_INTEGER; -- 문자 데이터를 저장할 TABLE 타입 tab 정의
  col_table tab; -- 연관 배열 col_table 정의
BEGIN
  col_table(1) := 'Part1'; -- 실행부에서 인덱스 번호 각각에 값 대입
  col_table(2) := 'Part2';
  col_table(3) := 'Part3';
  
  DBMS_OUTPUT.PUT_LINE(col_table(1)); -- 각 요소에 저장된 값은 인덱스 번호를 사용하여 참조
  DBMS_OUTPUT.PUT_LINE(col_table(1));
  DBMS_OUTPUT.PUT_LINE(col_table(1));
END;
/
Part1
Part2
Part3

PL/SQL 처리가 정상적으로 완료되었습니다.

연관 배열 사용시 주의점

- 이미 사용되고 있는 인덱스 번호에 값을 대입하는 경우, 요소는 덮어쓰게 됨

- 인덱스 번호는 연속해서 지정할 필요 없음

- 미사용 인덱스 번호를 지정하여 요소를 참조하는 경우 오류 발생

---

컬렉션 메소드

컬렉션 메소드(Collection method)를 사용하면 컬렉션에 대한 정보 확인 및 요소 삭제, 컬렉션 조작 등 가능

메소드명	기능
COUNT	컬렉션에 현재 포함되어 있는 요소의 수를 리턴
DELETE	컬렉션의 모든 요소를 삭제
DELETE(n)	컬렉션의 n번째 요소를 삭제
DELETE(n, m)	컬렉션의 n번째 부터 m번째까지의 요소 삭제
FIRST	컬렉션의 첫 번째 인덱스 번호 리턴
LAST	컬렉션의 마지막 인덱스 번호 리턴
PRIOR(n)	컬렉션 n 앞의 인덱스 번호 리턴
NEXT(n)	컬렉션 n 뒤의 인덱스 번호 리턴
EXISTS(n)	컬렉션 n번째 요소가 존재하는 경우에 TRUE 리턴

 

컬렉션 메소드 조회

-- 컬렉션 메소드 조회 시 컬렉션 명 뒤에 메소드 이름 지정
<컬렉션명>.<메소드명>

DECLARE
  TYPE tab IS TABLE OF VARCHAR2(10) INDEX BY PLS_INTEGER;
  col_table tab;
BEGIN
  col_table(1) := 'Part1';
  col_table(100) := 'Part2';
  DBMS_OUTPUT.PUT_LINE(col_table.COUNT); -- 요소 개수
  DBMS_OUTPUT.PUT_LINE(col_table.FIRST); -- 첫번째 인덱스 번호
  DBMS_OUTPUT.PUT_LINE(col_table.LAST); -- 마지막 인덱스 번호
  DBMS_OUTPUT.PUT_LINE(col_table.PRIOR(100)); -- 100보다 한 개 이전의 인덱스 번호
  DBMS_OUTPUT.PUT_LINE(col_table.NEXT(1)); -- 한 개 이후의 인덱스 번호
END;
/
2
1
100
1
100

PL/SQL 처리가 정상적으로 완료되었습니다.

---

레코드의 연관 배열

요소의 데이터 타입에 레코드를 지정한 연관 배열 작성 가능

ㄴ TABLE OF 의 데이터 타입에 레코드 타입을 지정함

TYPE <테이블 타입> IS TABLE OF <레코드명>
  INDEX BY {
    BINARY_INTEGER | PLS_INTEGER | <문자 데이터 타입>(<상한 사이즈>)
};

레코드는 사용자 정의 레코드와 %ROWTYPE 속성 변수 사용 가능

각 필드에 액세스 하려면 인덱스 번호뿐만 아니라 필드명을 지정할 필요가 있음

<연관 배열명>(<인덱스 번호>).<필드명>

레코드 연관 배열을 사용하면 테이블 내의 복수 행 데이터를 하나의 변수로 통합하여 대입 가능

DECLARE
  TYPE tab IS TABLE OF dept%ROWTYPE INDEX BY PLS_INTEGER; -- 레코드의 요소에 %ROWTYPE 속성 변수를 사용한 TABLE 타입 tab 정의
  table_rec tab; -- 연관 배열 table_rec 선언
  CURSOR dept_cl IS SELECT * FROM dept;
  n NUMBER := 0;
BEGIN
  FOR r IN dept_cl LOOP
    n := n + 1;
    table_rec(n) := r; -- 실행부의 커서 FOR 루프 내에서 변수 n을 인덱스 번호로 하여 DEPT 테이블의 한 행의 데이터 대입
    DBMS_OUTPUT.PUT_LINE(table_rec(n).deptno||' '|| -- LOOP문이 종료된 지점에서 연관 배열 table_rec에는 DEPT 테이블의 모든 데이터가 저장되어 있음
                         table_rec(n).dname||' '|| -- 각 필드에는 인덱스 번호와 필드 이름을 지정하여 액세스
                         table_rec(n).loc);
  END LOOP;
END;
/

10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

PL/SQL 처리가 정상적으로 완료되었습니다.

 

임시 테이블(TEMPORARY TABLE)과 연관 배열

Oracle에는 임시 테이블(Temporary table) 오브젝트 존재.

일시적으로 데이터를 저장하는 데 사용되며, 세션 또는 트랜잭션이 종료되면 데이터 삭제

- 데이터베이스 오브젝트로 작성되기 때문에 사용 시 타입 정의 불필요

- 값을 저장하려면 INSERT 문 사용

- 값을 조회하려면 SELECT 문 사용

- 세션이나 트랜잭션 사이에서만 값 참조 가능

CREATE GLOBAL TEMPORARY TABLE <테이블명>
  (<컬럼명> <데이터 타입> [, <컬럼명> <데이터 타입>]...)
ON COMMIT {DELETE|PRESERVE} ROWS];

파라미터	개요
DELETE ROWS	트랜잭션 내에서 값을 보유( Default )
PRESERVE ROWS	세션 내에서 값을 보유

CREATE GLOBAL TEMPORARY TABLE temp1(col1 NUMBER(3), col2 VARCHAR2(10)) ON COMMIT DELETE ROWS;
-- 임시 테이블 temp1 작성
테이블이 생성되었습니다.

INSERT INTO temp1 VALUES(10, 'Part1'); -- 임시테이블에 데이터를 대입하는 경우에는 INSERT문 실행하고

1개의 행이 만들어졌습니다.

INSERT INTO temp1 VALUES(20, 'Part2');
1 개의 행이 만들어졌습니다.

SELECT * FROM temp1; -- 조회하는 경우에는 SELECT문 실행

COL1  COL2
----  -----
10    Part1
20    Part2

COMMIT -- 트랜잭션을 COMMIT문으로 종료
-- 임시 테이블 temp1에는 DELETE ROWS가 지정되어 있기 때문에 트랜잭션의 종료와 동시에 삽입 값이 삭제
커밋이 완료되었습니다.

SELECT * FROM temp1;

선택된 레코드가 없습니다.

 

 

파라미터와 함수의 반환 값으로 연관 배열 사용

프로시저의 파라미터와 함수의 반환 값에 TABLE 타입을 사용하면 다른 프로그램에 배열을 그대로 전달 가능

CRETE OR REPLACE PACKAGE test_para
IS
  TYPE tab IS TABLE OF dept%ROWTYPE INDEX BY BINARY_INTEGER;
  table_rec tab; -- 레코드를 요소로 가지는 TABLE 타입 tab의 연관 배열 table_rec 정의
  PROCEDURE get_table(para OUT NOCOPY tab); -- 패키지 tset_para 내에 연관 배열을 OUT 모드의 파라미터로 전달할 프로시저 get_table
END;                           -- 파라미터의 모드 지정 후에는 NOCOPY 옵션 지정
/

패키지가 생성되었습니다.


CREATE OR REPLACE PACKAGE BODY test_para
IS
  PROCEDURE get_table(para OUT NOCOPY tab)
  IS
    n NUMBER := 0;
    CURSOR dept_cl IS SELECT * FROM dept;
  BEGIN
    FOR r IN dept_cl LOOP
      n := n + 1;
      table_rec(n) := r; -- 패키지 본체에는 연관 배열 table_rec에 DEPT 테이블의 데이터 대입
    END LOOP;
    para := table_rec; -- OUT 모드의 파라미터 para에 전달(연관 배열 전달 가능)
  END get_table;
END;
/

패키지 본문이 생성되었습니다.


BEGIN
  test_para.get_table(test_para.table_rec); -- 패키지 내의 연관 배열 table_rec을 OUT모드 파라미터 위치에 지정한 프로시저 get_table 실행
  FOR r IN 1.. test_para.table_rec.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE(test_para.table_rec(r).deptno||' '||
                         test_para.table_rec(r).dname||' '||
                         test_para.table_rec(r).loc);
  END LOOP;
END;
/

10 ACCOUNTING NEW YORK -- 프로시저에서 테이블 데이터가 한꺼번에 전달된 것을 확인 가능
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

PL/SQL 처리가 정상적으로 완료되었습니다.

 

 

NOCOPY 옵션

OUT, IN OUT 모드 파라미터에 NOCOPY 옵션 지정 가능.

반환 값이 존재하는 주소만 호출한 쪽에 참조되도록 하는 것이 가능

ㄴ 컬렉션과 같이 사이즈가 큰 반환 값을 전달하는 경우에, 컬렉션을 복사할 필요가 없어져서 메모리 공간 절약 가능

 

 

 

 

 

 

참조 : 책 '전문가를 위한 오라클 PL/SQL 입문'