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
"")
return
AnyData,
STATIC
FUNCTION
ConvertObject(obj
IN
"")
return
AnyData,
STATIC
FUNCTION
ConvertRef(rf
IN
REF
"")
return
AnyData,
STATIC
FUNCTION
ConvertCollection(col
IN
"")
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
"", last_elem
IN
boolean
DEFAULT
FALSE
),
MEMBER
PROCEDURE
SetObject(
self
IN
OUT
NOCOPY
AnyData,
obj
IN
"", last_elem
IN
boolean
DEFAULT
FALSE
),
MEMBER
PROCEDURE
SetRef(
self
IN
OUT
NOCOPY
AnyData,
rf
IN
REF
"", last_elem
IN
boolean
DEFAULT
FALSE
),
MEMBER
PROCEDURE
SetCollection(
self
IN
OUT
NOCOPY
AnyData,
col
IN
"", 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
.. 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
"")
return
PLS_INTEGER
,
MEMBER
FUNCTION
GetObject(
self
IN
AnyData, obj
OUT
NOCOPY
"")
return
PLS_INTEGER
,
MEMBER
FUNCTION
GetRef(
self
IN
AnyData, rf
OUT
NOCOPY
REF
"")
return
PLS_INTEGER
,
MEMBER
FUNCTION
GetCollection(
self
IN
AnyData,
col
OUT
NOCOPY
"")
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
)
扫一扫
902
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
