DB2 CREATE TYPE : 사용자 정의 데이터 유형 정의

1. CREATE TYPE 문

 

CREATE TYPE 문은 distinct 유형, row 유형, array 유형, cursor 유형 등의 사용자 데이터 유형을 정의한다.

이렇게 정의된 데이터 유형은 SQL PL 변수의 데이터 유형으로 사용될 수 있다.

CREATE [OR REPLACE] TYPE [스키마명.]유형명
          AS { distinct 유형 | row 유형 | array 유형 | cursor 유형 }

스키마명은 선택적으로 지정 가능하며, 스키마명을 명시적으로 지정하지 않은 경우에는 PATH 에 등록된  기본 스키마명이 적용된다.

유형명에는 정의하고자 하는 데이터 유형의 이름을 대소문자 구분없이 지정할 수 있고,

AS 다음에 세부 데이터 유형을 정의할 수 있다.

 

📌 OR REPLACE 옵션

CREATE TYPE 문에 OR REPLACE 옵션을 지정하게 되면 기존의 사용자 정의 데이터 유형이 재생성된다.

만일 지정한 데이터 유형이 존재하지 않으면 새로운 데이터 유형이 생성된다.

distinct 유형 이외의 유형에서 해당 옵션을 사용할 수 있다.

CREATE OR REPLACE TYPE RE_PLACE AS ROW (RENO INT, RENAME VARCHAR(10), REPHONE VARCHAR(20)) @

위와 같은 예문이 존재한다고 가정하였을 때,

RE_PLACE 라는 이름의 기존 데이터 유형이 존재하는지 먼저 확인하게 된다.

RE_PLACE
RENO | RENAME         | REPHONE
INT      | VARCHAR(10) | INT

RE_PLACE 데이터 유형은 기존에 위와 같은 형태로 존재했다면 기존의 유형의 3번째 유형을 VARCHAR(20) 으로 변경하여 재생성하게 된다.

 

1️⃣ distinct 유형 정의

distinct 유형은 built-in 유형을 기반으로 생성되는 단일값 유형이다.

CREATE TYPE 유형명 AS { built-in 유형 | anchor-scalar 유형 }
[ WITH WEAK TYPE RULES [ CHECK (제약 조건) ] ]

CREATE TYPE SALARY AS INT
WITH WEAK TYPE RULES CHECK(VALUE <= 1000) @

위의 예문에서는 SALARY 라는 이름의 데이터 유형의 이름을 정의하였고,

이 데이터 유형은 INT 유형을 기반으로 하는 distinct 유형이다.

또한 제약 조건으로써 1000 이하의 값만 허용함을 명시하였다.

 

2️⃣ row 유형 정의

CREATE [OR REPLACE] TYPE 유형명 AS ROW anchor-row 유형

CREATE [OR REPLACE] TYPE 유형명 AS ROW (필드1 유형1, 필드2 유형1, ....)

유형1 { built-in 유형 | anchor 유형 | distinct 유형 | row 유형 | array 유형 }

row 유형을 정의할 때에는 anchor 를 지정하거나 row 유형을 구성하는 필드를 콤마 구분자를 이용하여 나열하는 방법을 활용할 수 있다.

필드에 대해 나열하는 방법의 경우, 필드에 대해 필드명, 데이터 유형, 데이터 유형 길이와 크기 등을 지정한다.

CREATE TYPE SCHOOL.STUDENT AS ROW ANCHOR ROW SCHOOL.TEACHER @

SCHOOL.TEACHER 유형
TNO | TNAME            | TCLASS
INT   | VARCHAR(10) | INT

위의 예시에서는 SCHOOL 스키마의 STUDENT 라는 유형명으로 row 유형을 정의하는데,

SCHOOL.TEACHER 유형를 anchor 유형으로 지정하였기 때문에 STUDENT 는 TEACHER 를 참조하여

3개의 필드로 구성된다.

CREATE TYPE SCHOOL.STUDENT AS ROW (SNO INT, SNAME VARCHAR(10), SCLASS INT) @

첫번째 예시를 필드를 직접 나열하는 방식으로 변경하면 위와 같이 변경할 수 있다.

 

3️⃣ array 유형 정의

CREATE [OR REPLACE] TYPE 유형명 AS 유형1 ARRAY [크기]

CREATE [OR REPLACE] TYPE 유형명 AS 유형1 ARRAY [유형2]

유형1 { built-in 유형 | anchor 유형 | row 유형 | array 유형 }
유형2 { INT | VARCHAR(N) | anchor 유형 }

array 유형을 정의하는 방법은 이름을 지정하고, AS 다음에 array 유형을 구성하는 원소의 데이터 유형을 지정하면 된다.

이때 만일 ordinary array 유형으로 정의하려면, [ ] 안에 양의 정수를 지정하거나 [ ] 만 지정한다.

또는 associative array 유형으로 정의하려면, [ ] 안에 INT, VARCHAR(N) 또는 VARCHAR 유형의  anchor 유형을 지정하면 된다.

CREATE TYPE ARR_NAME AS VARCHAR(10) ARRAY [3] @

위의 예시는 ARR_NAME 으로 ARRAY 를 정의하였고, ordinary array 유형으로 정의하기 위하여 [ ] 에 양의 정수를 지정하였다. 

3으로 지정하였기 때문에 ARR_NAME 은 VARCHAR(10) 유형의 원소를 3개 가질 수 있게 된다.

CREATE TYPE ARR_NAME AS VARCHAR(10) ARRAY [VARCHAR(20)] @

이번 예시는 [ ] 안에 양의 정수가 아닌 VARCHAR(20) 을 넣었기 때문에 associative array 유형으로 정의된다.

associative array 유형은 관련 인덱스의 값이 정수 또는 문자열이 가능하여 여기서는 인덱스의 데이터 유형을 VARCHAR(20)으로 정의한 것이다. 

즉, ARR_NAME 배열은 각 요소의 데이터 유형을 VARCHAR(10) 으로 갖고, 각 요소의 인덱스는 VARCHAR(20) 으로 갖는 배열이 된다는 것이다. 

 

4️⃣ cursor 유형 정의

CREATE [OR REPLACE] TYPE 유형명 AS CURSOR

CREATE [OR REPLACE] TYPE 유형명 AS 유형1 CURSOR

유형1 { row 유형 | anchor-row 유형 }

임의의 SELECT 문을 통하여 도출하고자 하는 결과 집합의 레코드가 결정된 상태가 아닌 weakly typed cursor 를 정의하려면, 첫번째 방식대로 cursor 유형을 지정하면 된다.

반대로 결과 집합의 레코드이 구조가 결정되어 있는 경우의 strongly typed cursor 를 정의하기 위해서는 row 유형 또는 row 유형에 대한 anchor 유형을 지정해주면 된다.

CREATE TYPE CUR_WEAK AS CURSOR @

위의 예문은 CUR_WEAK 이라는 이름으로 CURSOR 유형을 정의하였고, 이때의 CURSOR 유형은 weakly typed cursor 유형으로 built-in 유형의 cursor 유형으로 지정된다.

CUR_WEAK 유형으로 선언하는 CURSOR 변수는 임의의 결과 집합을 반환하는 SELECT 문 또는 VALUES 문에서 사용될 수 있다.

CREATE TYPE CUR_STR AS ANCHOR ROW OF STUDENT CURSOR @

위의 예문은 CUR_STR 이라는 이름으로 CURSOR 유형을 정의하였다.

CUR_STR 은 STUDENT  를 참조하여 이 결과 집합 구조와 동일한 구조를 가지게 되는데 이를 strongly typed cursor 유형이라고 한다. 

CUR_STR 유형으로 선언하는 CURSOR 변수는 반드시 INT, VARCHAR(10), INT 유형의 데이터 유형으로 결과 집합을 내야 한다.

 

 

2. 데이터 유형 제거하기

 

앞서 만들어 놓은 데이터 유형을 제거하려면 DROP TYPE 문을 활용할 수 있다.

DROP TYPE [스키마명.]유형명 [ RESTRICT ]

삭제하고자 하는 데이터 유형의 이름을 대소문자 구분없이 지정하고, 이때 선택적으로 RESTRICT  키워드를 지정할 수 있는데, 이 키워드를 사용하면 삭제하고자 하는 데이터 유형을 참조하고 있는 오브젝트가 있는 경우 해당 데이터 유형을 삭제할 수 없도록 해준다.

DROP TYPE STUDENT @

이를 실행하게 되면 STUDENT 유형이 제거가 되고 이 유형을 참조하는 오브젝트는 INVALID 상태가 된다.