Oracle ANYDATA型

内容

ANYDATA型という型を初めて知ったのでメモする。

ANYDATA型とは

複数の型(CHAR型やNUMBER型など)のデータを格納することができる型のようだ。

使い方

テーブルの作成

SQL> CREATE TABLE TEST01 (
  ADT ANYDATA
);

データの格納

SQL> INSERT INTO TEST01 VALUES (ANYDATA.CONVERTNUMBER(1));
SQL> INSERT INTO TEST01 VALUES (ANYDATA.CONVERTDATE(SYSDATE));
SQL> INSERT INTO TEST01 VALUES (ANYDATA.CONVERTVARCHAR2('ABCDEFG'));
SQL> INSERT INTO TEST01 VALUES (ANYDATA.CONVERTCHAR('C'));
SQL> COMMIT;

データの内容表示

通常のSELECTでは、以下のように内容が表示されない。

SQL> SELECT * FROM TEST01;
ADT()
--------------------
ANYDATA()
ANYDATA()
ANYDATA()
ANYDATA()

ANYDATA.ACCESS*で変換する必要がある。

-- NUMBER,DATE,VARCHAR2,CHARのみ対応。必要があれば追加する。
SQL> SELECT
      ANYDATA.GETTYPENAME(ADT) TYPE_NAME,
      CASE ANYDATA.GETTYPENAME(ADT)
        WHEN 'SYS.NUMBER'   THEN TO_CHAR(ANYDATA.ACCESSNUMBER(ADT))
        WHEN 'SYS.DATE'     THEN TO_CHAR(ANYDATA.ACCESSDATE(ADT))
        WHEN 'SYS.VARCHAR2' THEN ANYDATA.ACCESSVARCHAR2(ADT)
        WHEN 'SYS.CHAR'     THEN ANYDATA.ACCESSCHAR(ADT)
        ELSE NULL
      END AS DATA
      FROM TEST01;

TYPE_NAME    DATA
------------ -------------------
SYS.NUMBER   1
SYS.DATE     2015-10-28 18:14:26
SYS.VARCHAR2 ABCDEFG
SYS.CHAR     C

データの内容表示（専用ファンクション作成）

ANYDATAをVARCHAR2に変換するファンクションANYDATA2VARCHAR2を作成。
とりあえずNUMBER,DATE,VARCHAR2,CHARに対応。
他に必要があれば追加していく。

SQL> CREATE OR REPLACE FUNCTION ANYDATA2VARCHAR2 (ADATA IN ANYDATA)
        RETURN VARCHAR2
        IS
            vSTATE  NUMBER;
            vTYPE   VARCHAR2(30);
            vNUM    NUMBER;
            vDATE   DATE;
            vVCHAR2 VARCHAR2(4000);
            vCHAR   CHAR;
        BEGIN
            vTYPE := ANYDATA.GETTYPENAME(ADATA);
            CASE vTYPE
                WHEN 'SYS.NUMBER'   THEN RETURN TO_CHAR(ANYDATA.ACCESSNUMBER(ADATA));
                WHEN 'SYS.DATE'     THEN RETURN TO_CHAR(ANYDATA.ACCESSDATE(ADATA));
                WHEN 'SYS.VARCHAR2' THEN RETURN ANYDATA.ACCESSVARCHAR2(ADATA);
                WHEN 'SYS.CHAR'     THEN RETURN ANYDATA.ACCESSCHAR(ADATA);
                ELSE RETURN null;
            END CASE ;
        END;
        /

SQL> SELECT ANYDATA.GETTYPENAME(ADT) TYPE, ANYDATA2VARCHAR2(ADT) DATA FROM TEST01;
TYPE         DATA
------------ -------------------
SYS.NUMBER   1
SYS.DATE     2015-10-28 18:14:26
SYS.VARCHAR2 ABCDEFG
SYS.CHAR     C

その他の検証

索引を作成することができるか？

SQL> CREATE INDEX IND_TEST01 ON TEST01(ADT);
CREATE INDEX IND_TEST01 ON TEST01(ADT)
                                  *
行1でエラーが発生しました。:
ORA-02327: データ型ADTの式に索引は作成できません。

→できないようだ。

VIEWで使いやすくしてみる。

SQL> CREATE OR REPLACE VIEW VIEW_TEST01
        AS
        SELECT ANYDATA.GETTYPENAME(ADT) TYPE, ANYDATA2VARCHAR2(ADT) DATA FROM TEST01;
ビューが作成されました。

SQL> SELECT * FROM VIEW_TEST01;
TYPE         DATA
------------ -------------------
SYS.NUMBER   1
SYS.DATE     2015-10-28 18:14:26
SYS.VARCHAR2 ABCDEFG
SYS.CHAR     C

リファレンス

• 241 ANYDATAタイプ(Oracle® Database PL/SQLパッケージおよびタイプ・リファレンス 11g リリース2)