本文转自：

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                                                                          TYPEANYDATA                                                                          TYPE BODYANYDATASET                                                                       TYPEANYDATASET                                                                       TYPE BODYDBMS_ANYDATASET_LIB                                                              LIBRARYDBMS_ANYDATA_LIB                                                                 LIBRARYSTREAMS$_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-----
     
      
       
         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.NUMBERSYS.DATESYS.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)--------------------------------------------------------------------------------514-7月 -11test 数据有了