[Oracle] PL/SQL 기초7 - 컬렉션(Collection) : VARRAY, Nested Table, 연관 배열, 메소드 등

[Oracle] PL/SQL 기초7 - 컬렉션(Collection) : VARRAY, Nested Table, 연관 배열, 메소드 등

 

안녕하세요. 갓대희 입니다. 이번 포스팅은 [ [Oracle] PL/SQL 기초 - 컬렉션(Collection) ] 입니다. : ) 

혹시 이해가 안되는 내용이 많을 수 있으니, 하단의 예제부터 보는것도 추천 한다.

 

0.Oracle Collection

▶ 0. 컬렉션(Collection)이란?

 - 배열 형태의 데이터 타입
 - 같은 데이터 타입을 가진 요소들로 구성 된다.
 - 다양한 데이터 타입을 가질 수 있는 동시에, 여러 로우에 해당하는 데이터를 가질 수 있는 자료형
 - 생성자를 이용해 초기화가 가능 하다.
 - 빌트인 함수와 프로시저로 구성된 컬렉션 메소드를 제공 한다.

 

▶ 1. 가변 길이 배열 (VARRAY (Variable-Size Array))

◎ 문법 : TYPE 타입명 IS { VARRAY | VARYING ARRAY } (크기) OF 요소데이터타입 [NOT NULL];

 - 고정 길이(fixed number)를 가진 배열
 - 선언시에 배열의 전체 크기를 명시해야 한다.
 - 데이터를 참조할 때 각각의 요소들 순서대로 참조해야 한다.
   예) varray의 크기가 3이라면 varray[1], varray[2], varray[3] 순서대로 데이터가 들어가야 한다.
 - 테이블 내에 저장될 수 있음. -> 테이블의 하나의 컬럼 타입으로 사용될 수 있다.
 - 숫자형 인덱스만 사용할 수 있고 생성자를 사용하여 일반 테이블의 컬럼 타입으로 사용될 수 있다.(Nested Table과 동일)

 

▶ 2.중첩 테이블 (Nested Table)

◎ 문법 : TYPE 타입명 IS TABLE OF 요소데이터타입 [NOT NULL];

 - VARRAY와 흡사한 구조를 가지고 있으나 차이점이 있음.
 - 크기에 제한은 없다.
 - 숫자형 인덱스만 사용할 수 있고 생성자를 사용하여 일반 테이블의 컬럼 타입으로 사용될 수 있다.

   (VARRAY와 동일)
 - 선언시에 배열의 크기를 명시할 필요 없음.
 - 데이터를 참조할 때 각각의 요소 순서를 지킬 필요 없음. (VARRAY와 반대)

※ PL/SQL의 테이블 
 - PL/SQL에서의 테이블은 오라클 SQL에서의 테이블과는 다르다. 
 - PL/SQL에서의 테이블은 일종의 일차원 배열이다. 
 - 하나의 테이블에 한 개의 컬럼 데이터를 저장 한다. 
 - 테이블은 크기에 제한이 없으면 그 ROW의 수는 데이터가 들어옴에 따라 자동 증가 한다. 
 - BINARY_INTEGER 타입의 인덱스 번호로 순서가 정해진다.

 

▶ 3.연관 배열 (Associative array(index-by table))

◎ 문법 : TYPE 타입명 IS TABLE OF 요소데이터타입 [ NOT NULL] INDEX BY [PLS_INTEGER | BINARY_INTEGER | VARCHAR2(크기) ] INDEX BY 키타입;

 - 연관 배열
 - 키와 값의 쌍으로 구성된 컬렉션 (Java나 C#과 같은 언어에서 사용하는 Hash table과 동일한 개념
 - 키를 Index라고 부르기 때문에 Index-by 테이블 이라고도 한다.
 - varray가 요소의 인덱스를 통해 각 요소의 값에 접근하는 반면 Associative array는 키에 의해 값에 접근한다.
 (자바에 있는 Map 구조와 대응 하는 컬렉션)

 

※ 키 타입 중에서 PLS_INTEGER는 생소 할 것이다.
 - PLS_INTEGER은 PL/SQL에서 사용가능한 데이터 타입. 속도가 BINARY_INTEGER, NUMBER와 비교하여 빠르다.
 - BINARY_INTEGER와 NUMBER 타입이 "라이브러리를 이용" 하여 수치 연산을 하는 반면, PLS_INTEGER는 실제 기계적인 연산(Machine arithmetic)을 수행하기 때문에 빠르다.
-2147483647과 2147483647 사이의 signed 정수

 

원문)
You use the PLS_INTEGER datatype to store signed integers.
Its magnitude range is -2147483648 to 2147483647, represented in 32 bits.
PLS_INTEGER values require less storage than NUMBER values and NUMBER subtypes.
Also, PLS_INTEGER operations use hardware arithmetic, so they are faster than NUMBER operations, which use library arithmetic.
For efficiency, use PLS_INTEGER for all calculations that fall within its magnitude range.
For calculations outside the range of PLS_INTEGER, you can use the INTEGER datatype.

출처 : http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/datatypes.htm#i10726

 

▶ 4.컬렉션 메소드

 - 컬렉션 메소드는 Collection명.메소드명 으로 접근한다.

메소드명	메소드 타입	설명
DELETE	Procedure	컬렉션 요소 삭제
TRIM	Procedure	VARRAY나 중첩 테이블의 끝에서 요소 삭제
EXTEND	Procedure	VARRAY나 중첩 테이블의 끝에서 요소 추가
EXISTS	Function	VARRAY나 중첩 테이블에 특정 요소가 존재하면 TRUE를 반환
FIRST	Function	컬렉션의 첫 번째 인덱스를 반환
LAST	Function	컬렉션의 마지막 인덱스를 반환
COUNT	Function	컬렉션 요소의 총 수를 반환
LIMIT	Function	컬렉션이 가질 수 있는 최대 요소의 수를 반환
PRIOR	Function	특정 인덱스 직전의 인덱스를 반환 (어떤 요소의 바로 전에 있는)
NEXT	Function	특정 인덱스 직후의 인덱스를 반환 (어떤 요소의 바로 앞에 있는)

 

4.1 DELETE

 - 컬렉션.DELETE : 모든 원소 삭제
 - 컬렉션.DELETE(1) : 인덱스가 1인 요소 삭제
 - 컬렉션.DELETE(3,5) : 인덱스 3~5사이의 요소 삭제. 3, 4, 5 삭제

 

※ VARRAY는 중간요소의 삭제가 불가하다.
자바의 ArrayList 처럼 중간 요소를 삭제하면 자동으로 인덱스가 한 칸씩 당겨지는 그런 구조가 아닌, 일반적인 배열의 정적인 성격을 가지고 있기 때문이다.

 

4.2 TRIM
 - VARRAY나 중첩 테이블의 맨 끝에서 부터 매개변수로 넘긴 개수만큼 해당 요소를 삭제한다.
 - 매개변수로 2가 넘어오면 맨 끝에 있는 2개의 요소를 삭제한다.
 - 매개변수로 아무 것도 넘기지 않으면 기본값으로 맨 끝에 하나의 원소만 삭제한다.
* 단 연관 배열의 경우 지원되지 않는다.

 

4.3 EXTEND

 - VARRAY와 중첩 테이블의 맨 마지막에 NULL값을 추가한다.
 - 추가로 넘겨주는 인자값에 따라 특정 요소를 복사해서 추가할 수도 있다.
EXTEND : 해당 컬렉션에 맨 끝에 NULL인 요소 한 개 추가.
EXTEND(n) : 해당 컬렉션의 맨 끝에 값이 NULL인 요소를 n개 추가.
EXTEND(n, i) : 해당 컬렉션의 i번째에 있는 요소를 맨 끝에 n개 복사해 추가함.

 

4.4 FIRST, LAST
 - 빌트인 함수로, 매개변수 없이 각각의 컬렉션의 첫 번째와 마지막 요소의 인덱스 반환한다. 
 - 만약 컬렉션이 비어 있다면 모두 NULL값을 리턴 한다.
 - 요소개 1개 뿐이라면 모두 1을 리턴 한다.

Collection이름.FIRST : 첫 번째 요소의 인덱스 반환
Collection이름.LAST : 마지막 요소의 인덱스 반환

 

4.5 COUNT, LIMIT
 - 컬렉션의 요소 개수를 반환하는 메소드
 - COUNT는 현재 들어있는 요소의 수
 - LIMIT 메소드는 해당 컬렉션이 가질 수 있는 요소 최대 수를 반환 한다.
※ VARRAY는 중간에 요소를 삭제할 수 없으므로 COUNT와 LIMIT가 항상 같다.

※ 연관배열(Associative array)과 중첩 테이블(Nested Table)은 현재 요소는 알 수 있지만 추가할 수 있는 원소의 크기는 제한되어 있지 않으므로, LIMIT는 항상 NULL을 리턴한다. 
※ 연관배열, 중첩 테이블 : COUNT = 현재 들어있는 원소 개수, LIMIT = NULL

Collection이름.COUNT
Collection이름.LIMIT

 

4.6 PRIOR, NEXT
 - PRIOR은 현재 컬렉션 요소 사이에서 앞의 요소의 인덱스를 리턴한다.
 - PRIOR의 경우, 만약 맨 첫 번째의 (FIRST) 의 요소의 인덱스를 받을경우, NULL을 리턴한다.
 - 왜냐하면 첫 번째 앞의 요소는 존재하지 않기 때문이다.
 - NEXT의 경우 전달 받은 인덱스의 바로 뒤에 있는 원소를 리턴한다.
   만약 맨 뒤의 원소 (LAST)의 요소의 인덱슬르 받은 경우 NULL을 리턴한다.

Collection이름.PRIOR(num);
Collection이름.NEXT(num);

 

ex)PL/SQL 테이블 문법 및 선언예제

DECLARE
-- 타입 선언
TYPE 가변테스트      IS VARRAY(3) OF INTEGER;                        -- INTEGER형 3개
TYPE 중첩테스트      IS TABLE OF VARCHAR2(10);                       -- VARCHAR2(10)형, 크기 설정 없음
TYPE 연관테스트_NUMBER  	IS TABLE OF NUMBER       INDEX BY PLS_INTEGER;  -- 키는 PLS_INTEGER 형, 값은 NUMBER형
TYPE 연관테스트_문자열1 IS TABLE OF VARCHAR2(32) INDEX BY PLS_INTEGER;  -- 키는 PLS_INTEGER 형, 값은 VARCHAR2(32)형
TYPE 연관테스트_문자열2 IS TABLE OF VARCHAR2(32) INDEX BY VARCHAR2(64); -- 키는 VARCHAR2(64)형, 값은 VARCHAR2(32)형

-- 변수 선언
varray1   가변테스트;
nested1   중첩테스트;
associ1   연관테스트_NUMBER;
associ2   연관테스트_문자열1;
associ3   연관테스트_문자열2;

BEGIN
	-- 변수에 값 할당
	varray1      := 가변테스트(1,10,100);               -- VARRAY(3) 이므로 3개까지 입력
	nested1      := 중첩테스트('A', 'B', 'C', 'D');  	-- nested table은 배열 크기 제한이 없다
	associ1(99)   := 20190528;
	associ2(1)   := '연관테스트 문자열';
	associ3('GOD') := '갓';
	associ3('DAMN') := '때엠~';
	
	-- print
	dbms_output.put_line(varray1(1)  );    -- 1 (첫번째, VARRY는 1부터 시작)
	dbms_output.put_line(nested1(2)  );    -- B (두번째)
	dbms_output.put_line(associ1(99)  );   -- 20190528
	dbms_output.put_line(associ2(1)  );    -- 연관테스트 문자열
	dbms_output.put_line(associ3('GOD'));  -- 갓
	dbms_output.put_line(associ3('DAMN')); -- 때엠~
END;

▶ 5.컬렉션을 데이터베이스 객체로 만들어 사용 하는 방법

-- type 생성
CREATE TYPE 가변Type AS VARRAY(26) OF VARCHAR2(2);  --> TYPE 가변Type Compiled

DECLARE
	-- 객체 선언
	TEST_VARRAY 가변Type;
BEGIN
	-- 값 할당
	TEST_VARRAY := 가변Type('A', 'B', 'C', 'D');

	-- print
    dbms_output.put_line(TEST_VARRAY(2));  -- B
END;