【Oracle Database】配列型の値を登録したい

概要

RDBを使うのであれば一つのカラムに複数値登録したい場合、関連テーブルなどを使うのが定石ですが、配列で値を管理できれば良いというケースもあると思います。例えばPostgreSQLであればこちらの公式ドキュメントにある通り、配列型の列を設定して、値を登録できます。
では、Oracle Databaseを使う場合は配列型の値をどのようにして登録できるのか、調べてみたのでメモ書きします。

対応

Manipulating the VARRAY with The VARRAY Self-joinの記事にある通り、VARRAYで定義できるコレクション型を使えばいけそうです。公式ドキュメントはコレクション・データ型のサポートを参照ください。

サンプルSQL

まずは配列を設定するためのTYPEを定義します。以下のようなCATEGORIES_TYPEという名前で定義します。

CREATE OR REPLACE TYPE CATEGORIES_TYPE IS VARRAY(30) of VARCHAR2(100)

配列型CATEGORIES_TYPEをカラムにもつテーブルを作成します。

CREATE TABLE POST 
(
 id VARCHAR2(100),
 title VARCHAR2(1000) NOT NULL,
 contents VARCHAR2(2000),
 categories CATEGORIES_TYPE,
 constraint pk_post_id primary key(id)
)

insertはTYPEを指定して、以下のように行います。

 INSERT POST
(ID, TITLE, CONTENTS, CATEGORIES)
VALUES('id_1', 'title1', 'contents1', CATEGORIES_TYPE('categoryId1','categoryId2'))

配列の値を条件にしてレコードを抽出したい場合は、以下のようなselect文になります。

SELECT *
FROM POST p
WHERE EXISTS (
  SELECT 'x'
  FROM TABLE(p.CATEGORIES) cs
  WHERE cs.COLUMN_VALUE IN ('categoryId1') 
)

その他

コレクション型にはindexを貼れないようなので、行数が多くなりそうでwhere句の条件で使用する場合は、普通に関連テーブルの設計にした方が良いように思います。