Decode360's Blog

业精于勤而荒于嬉 QQ:150355677 MSN:decode360@hotmail.com
  BlogJava :: 首页 :: 新随笔 :: 联系 ::  :: 管理 ::
  302 随笔 :: 26 文章 :: 82 评论 :: 0 Trackbacks
[AskTom] => AnyData Type 的使用
    学习一下Oracle自带的AnyData Type。这个特性是在9i之后加入的，主要是为了能够在存储不同类型的数据之后，取数时可以区分出来。相比把所有数据类型都存储到varchar2中，使用anydata可以自主判断数据类型之后取出，增加自动化程度。使用也不是非常复杂，见下例：
 
 
Here is an example.  It could be potentially useful in an application that stores generic attributes -- attributes you don't KNOW what the datatypes are until you actually run the code.  In the past, we would have stuffed everything into a VARCHAR2 -- dates, numbers, everything.  Now, you can put a date in and have it stay as a date (and the system will enforce it is in fact a valid date and let you perform date operations on it -- if it were in a varchar2 -- someone could put "hello world" into your "date" field)

SQL> create table t ( x sys.anyData );
Table created.
 
SQL> insert into t values ( sys.anyData.convertNumber(5) );
 
1 row created.
 
SQL> insert into t values ( sys.anyData.convertDate(sysdate) );
 
1 row created.
 
SQL> insert into t values ( sys.anyData.convertVarchar2('hello world') );
 
1 row created.
 
=================================================================
 

Use the getTypeName method of the ANYDATA function to see whatsin there....
 

SQL> select t.x.gettypeName() typeName from t t;
 
TYPENAME
--------------------
SYS.NUMBER
SYS.DATE
SYS.VARCHAR2
 
=================================================================
 

Unfortunately, they don't have a method to display the contents ofANYDATA in a query (most useful in programs that will fetch the data,figure out what it is and do  something with it -- eg: the applicationhas some intelligence as to how to handle the data)
 
Fortunately we can write one tho:
 

SQL > create or replace function getData( p_x in sys.anyData )
return varchar2
  2  as
  3      l_num number;
  4      l_date date;
  5      l_varchar2 varchar2(4000);
  6  begin
  7      case p_x.gettypeName
  8          when 'SYS.NUMBER' then
  9              if ( p_x.getNumber( l_num ) = dbms_types.success )
 10              then
 11                  l_varchar2 := l_num;
 12              end if;
 13          when 'SYS.DATE' then
 14              if ( p_x.getDate( l_date ) = dbms_types.success )
 15              then
 16                  l_varchar2 := l_date;
 17              end if;
 18          when 'SYS.VARCHAR2' then
 19              if ( p_x.getVarchar2( l_varchar2 ) = dbms_types.success )
 20              then
 21                  null;
 22              end if;
 23          else
 24              l_varchar2 := '** unknown **';
 25      end case;
 26
 27      return l_varchar2;
 28  end;
 29  /
 
Function created.
 
SQL >
SQL> select getData( x ) getdata from t;
 
GETDATA
--------------------
5
19-MAR-02
hello world
 
SQL>
 
=============================================================
 
also can do it in a sql：
 
——————-SQL Solution 1 ——————————
 
SELECT CASE WHEN anydata.gettypename(input) = 'SYS.VARCHAR2'
     THEN anydata.AccessVarchar2(input)
     WHEN anydata.gettypename(input) = 'SYS.NUMBER'
            THEN CAST(anydata.accessnumber(input) as varchar2(4000))
     WHEN anydata.gettypename(input) = 'SYS.DATE'
            THEN CAST(anydata.AccessDate(input) as varchar2(4000))
       END AS data
FROM test;
 
DATA
---------------------
5
19-MAR-02
hello world

——————-SQL Solution 2 ———————
 
SELECT CASE WHEN anydata.gettypename(input) = 'SYS.VARCHAR2'
     THEN  anydata.AccessVarchar2(input)
       END  str,
       CASE WHEN anydata.gettypename(input) = 'SYS.NUMBER'
            THEN anydata.accessnumber(input)
       END as num ,
       CASE WHEN anydata.gettypename(input) = 'SYS.DATE'
            THEN anydata.AccessDate(input)
       END  day
FROM test;
 
STR               NUM           DAY
------------      ----------    -----------
                  5
hello world                       19-MAR-02
 
 
 
 
 
 
 
Oracle anydata Package
-------------------------------------------------------------------------------------------------

CREATE OR REPLACE TYPE SYS.AnyData         as OPAQUE VARYING (*)

USING library DBMS_ANYDATA_LIB

(

  /* CONSTRUCTION */

  /* There are 2 ways to construct an AnyData. The Convert*() calls

     enable construction of the AnyData in its entirity with a single call.

     They serve as explicit CAST functions from any type in the Oracle ORDBMS

     to SYS.AnyData.

  */

  STATIC FUNCTION ConvertNumber(num IN NUMBER ) return AnyData,

  STATIC FUNCTION ConvertDate(dat IN DATE ) return AnyData,

  STATIC FUNCTION ConvertChar(c IN CHAR ) return AnyData,

  STATIC FUNCTION ConvertVarchar(c IN VARCHAR ) return AnyData,

  STATIC FUNCTION ConvertVarchar2(c IN VARCHAR2 ) return AnyData,

  STATIC FUNCTION ConvertRaw(r IN RAW ) return AnyData,

  STATIC FUNCTION ConvertBlob(b IN BLOB ) return AnyData,

  STATIC FUNCTION ConvertClob(c IN CLOB ) return AnyData,

  STATIC FUNCTION ConvertBfile(b IN BFILE ) return AnyData,

  STATIC FUNCTION ConvertObject(obj IN "<ADT_1>") return AnyData,

  STATIC FUNCTION ConvertObject(obj IN "<OPAQUE_1>") return AnyData,

  STATIC FUNCTION ConvertRef(rf IN REF "<ADT_1>") return AnyData,

  STATIC FUNCTION ConvertCollection(col IN "<COLLECTION_1>") return AnyData,

  /* The 2nd way to construct an AnyData is a piece by piece approach. The

     BeginCreate() call begins the construction process and

     EndCreate() call finishes the construction process..

     In between these 2 calls, the individual attributes of an Object Type or

     the elements of a Collection can be set using Set*()calls.

     For piece by piece access of the attributes of Objects and elements of

     Collections, the PieceWise() call should be invoked prior to

     Get*() calls.

     Note: The AnyData has to be constructed or accessed sequentially starting

     from its first attribute(or collection element).

     The BeginCreate() call automatically begins the construction in a

     piece-wise mode. There is no need to call PieceWise() immediately

     after BeginCreate().

     EndCreate should be called to finish the construction

     process (before which no access calls can be made).

  */

  /* NAME

         BeginCreate

     DESCRIPTION

         Begins creation process on a new AnyData.

     PARAMETERS

         dtype - The Type of the AnyData. (should correspond to

                                           OCI_TYPECODE_OBJECT or

                                           a Collection typecode.)

         adata - AnyData being constructed.

     EXCEPTIONS

         - DBMS_TYPES.invalid_parameters

           dtype is invalid (not fully constructed etc.).

     NOTE

         There is NO NEED to call PieceWise() immediately after this

     call. Automatically the construction process begins in a piece-wise

     manner.

  */

  STATIC PROCEDURE BeginCreate(dtype IN OUT NOCOPY AnyType,

                               adata OUT NOCOPY AnyData),

  /* NAME

         PieceWise.

     DESCRIPTION

         This call sets the MODE of access of the current data value to

         be an attribute at a time (if the data value is of TYPECODE_OBJECT).

         It sets the MODE of access of the data value to be a

         collection element at a time (if the data value is of

         collection TYPE). Once this call has been made, subsequent

         Set*'s and Get*'s will sequentially obtain

         individual attributes or collection elements.

     EXCEPTIONS

         - DBMS_TYPES.invalid_parameters

         - DBMS_TYPES.incorrect_usage

           On incorrect usage.

     NOTE

         The current data value must be of an OBJECT or COLLECTION type before

         this call can be made.

         Piece-wise construction and access of nested attributes that are of

         object or collection types is not supported.

  */

  MEMBER PROCEDURE PieceWise( self IN OUT NOCOPY AnyData),

  /* NAME

         SetNumber, SetDate etc.

     DESCRIPTION

         Sets the current data value.

         This is a list of procedures that should be called depending on the

         type of the current data value.

         The type of the data value should be the type of the attribute at the

         current position during the piece-wise construction process.

         NOTE - When BeginCreate() is called, construction has already

                begun in a piece-wise fashion. Subsequent calls to

                Set*() will set the successive attribute values.

                If the AnyData is a standalone collection, the

                Set*() call will set the successive collection

                elements.

     PARAMETERS

         num - The Number that needs to be set. etc.

         last_elem - This parameter is relevant only if AnyData represents a

                     a collection.

                     Set to TRUE if it is the last element of the collection,

                     FALSE otherwise.

     EXCEPTIONS

         - DBMS_TYPES.invalid_parameters

           Invalid Parameters (if it is not appropriate to add a number

                               at this point in the creation process).

         - DBMS_TYPES.incorrect_usage

           Incorrect usage

         - DBMS_TYPES.type_mismatch

           When the expected type is different from the passed in type.

     NOTE

         Sets the current data value.

  */

  MEMBER PROCEDURE SetNumber( self IN OUT NOCOPY AnyData, num IN NUMBER ,

                    last_elem IN boolean DEFAULT FALSE ),

  MEMBER PROCEDURE SetDate( self IN OUT NOCOPY AnyData, dat IN DATE ,

                    last_elem IN boolean DEFAULT FALSE ),

  MEMBER PROCEDURE SetChar( self IN OUT NOCOPY AnyData, c IN CHAR ,

                    last_elem IN boolean DEFAULT FALSE ),

  MEMBER PROCEDURE SetVarchar( self IN OUT NOCOPY AnyData, c IN VARCHAR ,

                    last_elem IN boolean DEFAULT FALSE ),

  MEMBER PROCEDURE SetVarchar2( self IN OUT NOCOPY AnyData,

                    c IN VARCHAR2 , last_elem IN boolean DEFAULT FALSE ),

  MEMBER PROCEDURE SetRaw( self IN OUT NOCOPY AnyData, r IN RAW ,

                    last_elem IN boolean DEFAULT FALSE ),

  MEMBER PROCEDURE SetBlob( self IN OUT NOCOPY AnyData, b IN BLOB ,

                    last_elem IN boolean DEFAULT FALSE ),

  MEMBER PROCEDURE SetClob( self IN OUT NOCOPY AnyData, c IN CLOB ,

                    last_elem IN boolean DEFAULT FALSE ),

  MEMBER PROCEDURE SetBfile( self IN OUT NOCOPY AnyData, b IN BFILE ,

                    last_elem IN boolean DEFAULT FALSE ),

  MEMBER PROCEDURE SetObject( self IN OUT NOCOPY AnyData,

                    obj IN "<ADT_1>", last_elem IN boolean DEFAULT FALSE ),

  MEMBER PROCEDURE SetObject( self IN OUT NOCOPY AnyData,

                    obj IN "<OPAQUE_1>", last_elem IN boolean DEFAULT FALSE ),

  MEMBER PROCEDURE SetRef( self IN OUT NOCOPY AnyData,

                    rf IN REF "<ADT_1>", last_elem IN boolean DEFAULT FALSE ),

  MEMBER PROCEDURE SetCollection( self IN OUT NOCOPY AnyData,

                  col IN "<COLLECTION_1>", last_elem IN boolean DEFAULT FALSE ),

  /*

     NAME

          EndCreate

     DESCRIPTION

          Ends Creation of a AnyData. Other creation functions cannot be

          called after this call.

  */

  MEMBER PROCEDURE EndCreate( self IN OUT NOCOPY AnyData),

  /* ----------------------------------------------------------------------- */

  /* ACCESSORS */

  /* ----------------------------------------------------------------------- */

  /*

     NAME

          GetTypeName

     DESCRIPTION

          Get the fully qualified Type Name for the AnyData.

          If the AnyData is based on a builtin, this function will return

          NUMBER etc.

          If it is based on a user defined type, this function will return

          <schema_name>.<type_name>. e.g. SCOTT.FOO.

          If it is based on a transient anonymous type, this function will

          return NULL.

     RETURNS

          Type name of the AnyData.

  */

  MEMBER FUNCTION GetTypeName( self IN AnyData) return VARCHAR2 DETERMINISTIC ,

  /* NAME

         GetType

     DESCRIPTION

         Gets the Type of the AnyData.

     PARAMETERS

         typ (OUT) - The AnyType corresponding to the AnyData. May be NULL

                     if it does not represent a user-defined type.

     RETURNS

         The typecode corresponding to the type of the AnyData.

     EXCEPTIONS

  */

  MEMBER FUNCTION GetType( self IN AnyData, typ OUT NOCOPY AnyType)

      return PLS_INTEGER ,

  /* NAME

         Get*()

     DESCRIPTION

         Gets the current data value (which should be of appropriate type)

         The type of the current data value depends on the MODE with which

         we are accessing (Depending on whether we have invoked the

         PieceWise() call).

         If PieceWise() has NOT been called, we are accessing the

         AnyData in its entirety and the type of the data value should match

         the type of the AnyData.

         If PieceWise() has been called, we are accessing the

         AnyData piece wise. The type of the data value should match the type

         of the attribute (or collection element) at the current position.

     PARAMETERS

         num - The Number that needs to be got. etc.

     RETURNS

         DBMS_TYPES.SUCCESS or DBMS_TYPES.NO_DATA

         The return value is relevant only if PieceWise

         has been already called (for a collection). In such a case,

         DBMS_TYPES.NO_DATA signifies the end of the collection when all

         elements have been accessed.

     EXCEPTIONS

         - DBMS_TYPES.type_mismatch

           When the expected type is different from the passed in type.

         - DBMS_TYPES.invalid_parameters

           Invalid Parameters (if it is not appropriate to add a number

                               at this point in the creation process).

         - DBMS_TYPES.incorrect_usage

           Incorrect usage.

  */

  MEMBER FUNCTION GetNumber( self IN AnyData, num OUT NOCOPY NUMBER )

              return PLS_INTEGER ,

  MEMBER FUNCTION GetDate( self IN AnyData, dat OUT NOCOPY DATE )

              return PLS_INTEGER ,

  MEMBER FUNCTION GetChar( self IN AnyData, c OUT NOCOPY CHAR )

              return PLS_INTEGER ,

  MEMBER FUNCTION GetVarchar( self IN AnyData, c OUT NOCOPY VARCHAR )

              return PLS_INTEGER ,

  MEMBER FUNCTION GetVarchar2( self IN AnyData, c OUT NOCOPY VARCHAR2 )

              return PLS_INTEGER ,

  MEMBER FUNCTION GetRaw( self IN AnyData, r OUT NOCOPY RAW )

              return PLS_INTEGER ,

  MEMBER FUNCTION GetBlob( self IN AnyData, b OUT NOCOPY BLOB )

              return PLS_INTEGER ,

  MEMBER FUNCTION GetClob( self IN AnyData, c OUT NOCOPY CLOB )

              return PLS_INTEGER ,

  MEMBER FUNCTION GetBfile( self IN AnyData, b OUT NOCOPY BFILE )

              return PLS_INTEGER ,

  MEMBER FUNCTION GetObject( self IN AnyData, obj OUT NOCOPY "<ADT_1>")

              return PLS_INTEGER ,

  MEMBER FUNCTION GetObject( self IN AnyData, obj OUT NOCOPY "<OPAQUE_1>")

              return PLS_INTEGER ,

  MEMBER FUNCTION GetRef( self IN AnyData, rf OUT NOCOPY REF "<ADT_1>")

              return PLS_INTEGER ,

  MEMBER FUNCTION GetCollection( self IN AnyData,

                                col OUT NOCOPY "<COLLECTION_1>")

                                return PLS_INTEGER ,

  /***************************************************************************/

  /* NEWLY ADDED FUNCTIONS IN 9iR2 */

  /***************************************************************************/

  /* Convert calls for Datetime and Nchar types. */

  STATIC FUNCTION ConvertTimestamp(ts IN TIMESTAMP ) return AnyData,

  STATIC FUNCTION ConvertTimestampTZ(ts IN TIMESTAMP WITH TIME ZONE )

        return AnyData,

  STATIC FUNCTION ConvertTimestampLTZ(ts IN TIMESTAMP WITH LOCAL TIME ZONE )

        return AnyData,

  STATIC FUNCTION ConvertIntervalYM(inv IN INTERVAL YEAR TO MONTH )

         return AnyData,

  STATIC FUNCTION ConvertIntervalDS(inv IN INTERVAL DAY TO SECOND )

        return AnyData,

  STATIC FUNCTION ConvertNchar(nc IN NCHAR ) return AnyData,

  STATIC FUNCTION ConvertNVarchar2(nc IN NVARCHAR2 ) return AnyData,

  STATIC FUNCTION ConvertNClob(nc IN NCLOB ) return AnyData,

  /* Set calls for Datetime and Nchar types. */

  MEMBER PROCEDURE SetTimestamp( self IN OUT NOCOPY AnyData, ts IN TIMESTAMP ,

                    last_elem IN boolean DEFAULT FALSE ),

  MEMBER PROCEDURE SetTimestampTZ( self IN OUT NOCOPY AnyData,

                    ts IN TIMESTAMP WITH TIME ZONE ,

                    last_elem IN boolean DEFAULT FALSE ),

  MEMBER PROCEDURE SetTimestampLTZ( self IN OUT NOCOPY AnyData,

                    ts IN TIMESTAMP WITH LOCAL TIME ZONE ,

                    last_elem IN boolean DEFAULT FALSE ),

  MEMBER PROCEDURE SetIntervalYM( self IN OUT NOCOPY AnyData,

                    inv IN INTERVAL YEAR TO MONTH ,

                    last_elem IN boolean DEFAULT FALSE ),

  MEMBER PROCEDURE SetIntervalDS( self IN OUT NOCOPY AnyData,

                    inv IN INTERVAL DAY TO SECOND ,

                    last_elem IN boolean DEFAULT FALSE ),

  MEMBER PROCEDURE SetNchar( self IN OUT NOCOPY AnyData,

                    nc IN NCHAR , last_elem IN boolean DEFAULT FALSE ),

  MEMBER PROCEDURE SetNVarchar2( self IN OUT NOCOPY AnyData,

                    nc IN NVarchar2 , last_elem IN boolean DEFAULT FALSE ),

  MEMBER PROCEDURE SetNClob( self IN OUT NOCOPY AnyData,

                    nc IN NClob , last_elem IN boolean DEFAULT FALSE ),

  /* Get calls for Datetime and Nchar types. */

  MEMBER FUNCTION GetTimestamp( self IN AnyData, ts OUT NOCOPY TIMESTAMP )

       return PLS_INTEGER ,

  MEMBER FUNCTION GetTimestampTZ( self IN AnyData,

       ts OUT NOCOPY TIMESTAMP WITH TIME ZONE ) return PLS_INTEGER ,

  MEMBER FUNCTION GetTimestampLTZ( self IN AnyData,

       ts OUT NOCOPY TIMESTAMP WITH LOCAL TIME ZONE ) return PLS_INTEGER ,

  MEMBER FUNCTION GetIntervalYM( self IN AnyData,

       inv IN OUT NOCOPY INTERVAL YEAR TO MONTH ) return PLS_INTEGER ,

  MEMBER FUNCTION GetIntervalDS( self IN AnyData,

       inv IN OUT NOCOPY INTERVAL DAY TO SECOND ) return PLS_INTEGER ,

  MEMBER FUNCTION GetNchar( self IN AnyData, nc OUT NOCOPY NCHAR )

       return PLS_INTEGER ,

  MEMBER FUNCTION GetNVarchar2( self IN AnyData, nc OUT NOCOPY NVARCHAR2 )

       return PLS_INTEGER ,

  MEMBER FUNCTION GetNClob( self IN AnyData, nc OUT NOCOPY NCLOB )

       return PLS_INTEGER ,

  /*

     NAME

         AccessNumber, AccessDate etc.

     DESCRIPTION

         Access functions for AnyData based on Built-ins are provided for

         SQL queriability.

         These functions do not throw exceptions on type-mismatch.

         Instead, they return NULL if the type of the AnyData does not

         correspond to the type of Access so that it is SQL friendly.

         If users want only those AnyData's of the appropriate Types returned

         in a Query, they should use a WHERE clause which uses

         GetTypeName() and choose the type they are interested in

         (say "SYS.NUMBER" etc.)

  */

  MEMBER FUNCTION AccessNumber( self IN AnyData) return NUMBER DETERMINISTIC ,

  MEMBER FUNCTION AccessDate( self IN AnyData) return DATE DETERMINISTIC ,

  MEMBER FUNCTION AccessChar( self IN AnyData) return CHAR DETERMINISTIC ,

  MEMBER FUNCTION AccessVarchar( self IN AnyData) return VARCHAR DETERMINISTIC ,

  MEMBER FUNCTION AccessVarchar2( self IN AnyData) return VARCHAR2

                          DETERMINISTIC ,

  MEMBER FUNCTION AccessRaw( self IN AnyData) return RAW DETERMINISTIC ,

  MEMBER FUNCTION AccessBlob( self IN AnyData) return BLOB DETERMINISTIC ,

  MEMBER FUNCTION AccessClob( self IN AnyData) return CLOB DETERMINISTIC ,

  MEMBER FUNCTION AccessBfile( self IN AnyData) return BFILE DETERMINISTIC ,

  MEMBER FUNCTION AccessTimestamp( self IN AnyData) return TIMESTAMP

                           DETERMINISTIC ,

  MEMBER FUNCTION AccessTimestampTZ( self IN AnyData)

         return TIMESTAMP WITH TIME ZONE DETERMINISTIC ,

  MEMBER FUNCTION AccessTimestampLTZ( self IN AnyData)

         return TIMESTAMP WITH LOCAL TIME ZONE DETERMINISTIC ,

  MEMBER FUNCTION AccessIntervalYM( self IN AnyData)

         return INTERVAL YEAR TO MONTH DETERMINISTIC ,

  MEMBER FUNCTION AccessIntervalDS( self IN AnyData)

         return INTERVAL DAY TO SECOND DETERMINISTIC ,

  MEMBER FUNCTION AccessNchar( self IN AnyData) return NCHAR DETERMINISTIC ,

  MEMBER FUNCTION AccessNVarchar2( self IN AnyData) return NVARCHAR2

                                  DETERMINISTIC ,

  MEMBER FUNCTION AccessNClob( self IN AnyData) return NCLOB DETERMINISTIC ,

  /***************************************************************************/

  /* NEWLY ADDED FUNCTIONS IN 10iR1 */

  /***************************************************************************/

  /* Convert calls for BFloat, BDouble, URowid */

  STATIC FUNCTION ConvertBFloat(fl IN BINARY_FLOAT) return AnyData,

  STATIC FUNCTION ConvertBDouble(dbl IN BINARY_DOUBLE) return AnyData,

  STATIC FUNCTION ConvertURowid(rid IN UROWID ) return AnyData,

  /* Set calls for Float, Double */

  MEMBER PROCEDURE SetBFloat( self IN OUT NOCOPY AnyData, fl IN BINARY_FLOAT,

                            last_elem IN boolean DEFAULT FALSE ),

  MEMBER PROCEDURE SetBDouble( self IN OUT NOCOPY AnyData, dbl IN BINARY_DOUBLE,

                             last_elem IN boolean DEFAULT FALSE ),

  /* Get calls for Float, Double */

  MEMBER FUNCTION GetBFloat( self IN AnyData, fl OUT NOCOPY BINARY_FLOAT)

      return PLS_INTEGER ,

  MEMBER FUNCTION GetBDouble( self IN AnyData, dbl OUT NOCOPY BINARY_DOUBLE)

      return PLS_INTEGER ,

  /* Access calls for Float, Double, Rowid */

  MEMBER FUNCTION AccessBFloat( self IN AnyData) return BINARY_FLOAT

          DETERMINISTIC ,

  MEMBER FUNCTION AccessBDouble( self IN AnyData) return BINARY_DOUBLE

          DETERMINISTIC ,

  MEMBER FUNCTION AccessURowid( self IN AnyData) return UROWID DETERMINISTIC

)

 
-The End-

posted on 2009-04-08 21:59 decode360-3 阅读(561) 评论(0)  编辑  收藏 所属分类: Toy

只有注册用户登录后才能发表评论。
网站导航: