[转]oracle的ANYDATA数据类型

时间：2023-03-08 23:55:33
[转]oracle的ANYDATA数据类型

本文转自：http://blog.csdn.net/yuzhenhuan01/article/details/6606106

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

数据有了

相关文章