oracle的ANYDATA数据类型

 

ANYDATA数据类型是个有点奇特的类型，你可以把不同数据类型的数据通过转换插入该类型的字段中，这个特性可以适应一些特殊的需求，不过插入和查询这个字段时稍有点繁琐。

由下语句看出ANYDATA是一个TYPE：

SQL> SELECT object_name, object_type  FROM dba_objects WHERE object_name like '%ANYDATA%' AND owner = 'SYS';
 
OBJECT_NAME                                                                      OBJECT_TYPE
-------------------------------------------------------------------------------- -------------------
ANYDATA                                                                          TYPE
ANYDATA                                                                          TYPE BODY
ANYDATASET                                                                       TYPE
ANYDATASET                                                                       TYPE BODY
DBMS_ANYDATASET_LIB                                                              LIBRARY
DBMS_ANYDATA_LIB                                                                 LIBRARY
STREAMS$_ANYDATA_ARRAY                                                           TYPE
 
7 rows selected
 

我们来看下这个TYPE可以使用的一些东西：

SQL> DESC ANYDATA;
Element             Type     
------------------- ---------
CONVERTNUMBER       FUNCTION 
CONVERTDATE         FUNCTION 
CONVERTCHAR         FUNCTION 
CONVERTVARCHAR      FUNCTION 
CONVERTVARCHAR2     FUNCTION 
CONVERTRAW          FUNCTION 
CONVERTBLOB         FUNCTION 
CONVERTCLOB         FUNCTION 
CONVERTBFILE        FUNCTION 
CONVERTOBJECT       FUNCTION 
CONVERTREF          FUNCTION 
CONVERTCOLLECTION   FUNCTION 
BEGINCREATE         PROCEDURE
PIECEWISE           PROCEDURE
SETNUMBER           PROCEDURE
SETDATE             PROCEDURE
SETCHAR             PROCEDURE
SETVARCHAR          PROCEDURE
SETVARCHAR2         PROCEDURE
SETRAW              PROCEDURE
SETBLOB             PROCEDURE
SETCLOB             PROCEDURE
SETBFILE            PROCEDURE
SETOBJECT           PROCEDURE
SETREF              PROCEDURE
SETCOLLECTION       PROCEDURE
ENDCREATE           PROCEDURE
GETTYPENAME         FUNCTION 
GETTYPE             FUNCTION 
GETNUMBER           FUNCTION 
GETDATE             FUNCTION 
GETCHAR             FUNCTION 
GETVARCHAR          FUNCTION 
GETVARCHAR2         FUNCTION 
GETRAW              FUNCTION 
GETBLOB             FUNCTION 
GETCLOB             FUNCTION 
GETBFILE            FUNCTION 
GETOBJECT           FUNCTION 
GETREF              FUNCTION 
GETCOLLECTION       FUNCTION 
CONVERTTIMESTAMP    FUNCTION 
CONVERTTIMESTAMPTZ  FUNCTION 
CONVERTTIMESTAMPLTZ FUNCTION 
CONVERTINTERVALYM   FUNCTION 
CONVERTINTERVALDS   FUNCTION 
CONVERTNCHAR        FUNCTION 
CONVERTNVARCHAR2    FUNCTION 
CONVERTNCLOB        FUNCTION 
SETTIMESTAMP        PROCEDURE
SETTIMESTAMPTZ      PROCEDURE
SETTIMESTAMPLTZ     PROCEDURE
SETINTERVALYM       PROCEDURE
SETINTERVALDS       PROCEDURE
SETNCHAR            PROCEDURE
SETNVARCHAR2        PROCEDURE
SETNCLOB            PROCEDURE
GETTIMESTAMP        FUNCTION 
GETTIMESTAMPTZ      FUNCTION 
GETTIMESTAMPLTZ     FUNCTION 
GETINTERVALYM       FUNCTION 
GETINTERVALDS       FUNCTION 
GETNCHAR            FUNCTION 
GETNVARCHAR2        FUNCTION 
GETNCLOB            FUNCTION 
ACCESSNUMBER        FUNCTION 
ACCESSDATE          FUNCTION 
ACCESSCHAR          FUNCTION 
ACCESSVARCHAR       FUNCTION 
ACCESSVARCHAR2      FUNCTION 
ACCESSRAW           FUNCTION 
ACCESSBLOB          FUNCTION 
ACCESSCLOB          FUNCTION 
ACCESSBFILE         FUNCTION 
ACCESSTIMESTAMP     FUNCTION 
ACCESSTIMESTAMPTZ   FUNCTION 
ACCESSTIMESTAMPLTZ  FUNCTION 
ACCESSINTERVALYM    FUNCTION 
ACCESSINTERVALDS    FUNCTION 
ACCESSNCHAR         FUNCTION 
ACCESSNVARCHAR2     FUNCTION 
ACCESSNCLOB         FUNCTION 
CONVERTBFLOAT       FUNCTION 
CONVERTBDOUBLE      FUNCTION 
CONVERTUROWID       FUNCTION 
SETBFLOAT           PROCEDURE
SETBDOUBLE          PROCEDURE
GETBFLOAT           FUNCTION 
GETBDOUBLE          FUNCTION 
ACCESSBFLOAT        FUNCTION 
ACCESSBDOUBLE       FUNCTION 
ACCESSUROWID        FUNCTION 

大多从字面上都可以看出这些方法的功能

 

下面创建一个包含该类型的字段的测试表
SQL> CREATE TABLE yutest (v_cl1 sys.anyData);
 
Table created
 

desc一下：
SQL> desc yutest;
Name  Type        Nullable Default Comments
----- ----------- -------- ------- --------
V_CL1 SYS.ANYDATA Y                        
 

插入几条测试数据，注意这里使用的相应的方法函数插入：
SQL> INSERT INTO yutest VALUES (sys.anyData.convertNumber(5));

 
1 row inserted
 
SQL> INSERT INTO yutest VALUES (sys.anyData.convertDate(SYSDATE));

 
1 row inserted
 
SQL> INSERT INTO yutest VALUES (sys.anyData.convertVarchar2('test'));

 
1 row inserted
 
SQL> commit;
 
Commit complete

 

直接查询表中数据：
SQL> SELECT *    FROM yutest a;
 
V_CL1
-----
<Obje
<Obje
<Obje
 查不出来具体的数据，看来必须转换下才能查看了。
 

这个是查看各个数据行的数据类型的：

这个是错的
SQL> SELECT v_cl1.gettypeName()    FROM yutest;
 
SELECT v_cl1.gettypeName()    FROM yutest
 
ORA-00904: "V_CL1"."GETTYPENAME": 标识符无效
 

这个也是错的
SQL> SELECT yutest.v_cl1.gettypeName()    FROM yutest;
 
SELECT yutest.v_cl1.gettypeName()    FROM yutest
 
ORA-00904: "YUTEST"."V_CL1"."GETTYPENAME": 标识符无效
 
这个才是正确的查询方法

SQL> SELECT a.v_cl1.gettypeName()    FROM yutest a;
 
A.V_CL1.GETTYPENAME()
--------------------------------------------------------------------------------
SYS.NUMBER
SYS.DATE
SYS.VARCHAR2

 

 示例中涉及了3个数据类型，我这里也简单是写个转换函数，也只转换了3个类型

CREATE OR REPLACE FUNCTION getanydata(v_data IN sys.anyData)
  RETURN VARCHAR2 IS
  v_num     NUMBER;
  v_date    DATE;
  v_re_data VARCHAR2(4000);
BEGIN
  CASE v_data.gettypeName
    WHEN 'SYS.NUMBER' THEN
      IF (v_data.getNumber(v_num) = dbms_types.success) THEN
        v_re_data := v_num;
      END IF;
    WHEN 'SYS.DATE' THEN
      IF (v_data.getDate(v_date) = dbms_types.success) THEN
        v_re_data := v_date;
      END IF;
    WHEN 'SYS.VARCHAR2' THEN
      IF (v_data.getVarchar2(v_re_data) = dbms_types.success) THEN
        NULL;
      END IF;
    ELSE
      v_re_data := 'not number or date or varchar2';
  END CASE;

  RETURN v_re_data;
END getanydata;

 

使用新建的函数来查询

SQL> SELECT getanydata(v_cl1) FROM yutest a;
 
GETANYDATA(V_CL1)
--------------------------------------------------------------------------------
5
14-7月 -11
test

 

数据有了