ネスト表を使ってみた

テーブルのカラムにテーブルが作れるらしいので試してみました。
下記の親子関係のテーブルをネスト表に書き換えてみます。

CREATE TABLE CUSTOMER
(
	ID		NUMBER NOT NULL,
	NAME	VARCHAR2(100),
	CONSTRAINT PK_CUSTOMER PRIMARY KEY (ID) USING INDEX
);
/
CREATE TABLE ADDRESS
(
	ID				NUMBER NOT NULL,
	CUSTOMER_ID		NUMBER,
	ZIP_CODE		VARCHAR2(10),
	CONSTRAINT PK_ADDRESS PRIMARY KEY (ID) USING INDEX,
	CONSTRAINT FK_ADDRESS FOREIGN KEY (CUSTOMER_ID) REFERENCES CUSTOMER (ID)
);
/

INSERT INTO CUSTOMER(ID,NAME) VALUES(1,'取引先Ａ');
/
INSERT ALL
INTO ADDRESS(ID,CUSTOMER_ID,ZIP_CODE) VALUES (1,1,'1234567')
INTO ADDRESS(ID,CUSTOMER_ID,ZIP_CODE) VALUES (2,1,'3216543')
SELECT * FROM DUAL;
/

SELECTする

SELECT
	 C.ID AS CUSTOMER_ID
	,C.NAME
	,A.ID AS ADDRESS_ID
	,A.ZIP_CODE
FROM
	CUSTOMER C
INNER JOIN
	ADDRESS A
ON
	A.CUSTOMER_ID = C.ID;

結果

CUSTOMER_ID	NAME	ADDRESS_ID	ZIP_CODE
1	取引先Ａ	1	1234567
1	取引先Ａ	2	3216543

これを、ネスト表で表現してみる。

まずADDRESSテーブルと同様のTYPEと、その作成したTYPEでTABLEのTYPEを作成

CREATE TYPE NEST_ADDRESS AS OBJECT (ZIP_CODE VARCHAR(10));
/
CREATE OR REPLACE TYPE NEST_ADDRESS_LIST AS TABLE OF NEST_ADDRESS;
/

次にCUSTOMERテーブル作成
ADDRESS_LIST カラムのTYPE をさっきつくったNEST_ADDRESS_LISTにします

CREATE TABLE NEST_CUSTOMER
(
	ID NUMBER,
	NAME VARCHAR2(100),
	ADDRESS_LIST NEST_ADDRESS_LIST
)
NESTED TABLE ADDRESS_LIST STORE AS ADDRESS_NTAB;
/

これで完成です。

次にデータをINSERTしてみます。
NEST_ADDRESS_LIST TYPE に、 NEST_ADDRESS TYPE のデータを複数いれます。

INSERT INTO NEST_CUSTOMER(ID,NAME,ADDRESS_LIST)
VALUES(1,'取引先Ａ',
	NEST_ADDRESS_LIST(
		 NEST_ADDRESS('1234567')
		,NEST_ADDRESS('3216543')
	)
);

で、SELECTします。
TABLE(ネストカラム)で取得します。

SELECT
	 N.ID
	,N.NAME
	,A.ZIP_CODE
FROM
	 NEST_CUSTOMER N
	,TABLE(N.ADDRESS_LIST) A

結果、ちゃんとADDRESS_LIST に2レコード入ってることがわかります。

ID	NAME	ZIP_CODE
1	取引先Ａ	1234567
1	取引先Ａ	3216543

ADDRESS_LIST の データをいじりたいときはどうすれば良いのか調べて見たところ
下記のように一度レコードをサブクエリで指定してあげてから操作するようです。

INSERT

INSERT INTO TABLE(SELECT ADDRESS_LIST FROM NEST_CUSTOMER WHERE ID = 1)
VALUES(NEST_ADDRESS('987654321'));

ID	NAME	ZIP_CODE
1	取引先Ａ	1234567
1	取引先Ａ	3216543
1	取引先Ａ	987654321

UPDATE

UPDATE TABLE (SELECT ADDRESS_LIST FROM NEST_CUSTOMER WHERE ID = 1) NC
SET VALUE(NC) = NEST_ADDRESS('111111')
WHERE NC.ZIP_CODE = '987654321';

ID	NAME	ZIP_CODE
1	取引先Ａ	1234567
1	取引先Ａ	3216543
1	取引先Ａ	111111

DELETE

DELETE FROM TABLE(SELECT ADDRESS_LIST FROM NEST_CUSTOMER WHERE ID = 1) NC
WHERE
NC.ZIP_CODE = '111111';

ID	NAME	ZIP_CODE
1	取引先Ａ	1234567
1	取引先Ａ	3216543

ひとまず今日はここまで。。。
難しかった上にあまり使い道が思いつかないですね。