全部产品

ANYDATA

更新时间:2020-10-15 18:09:23

ANYDATA TYPE包含给定类型的实例,以及对该类型的描述。 ANYDATA可以永久存储在数据库中。

CONVERT *调用可通过一次调用来完整地构建ANYDATA。 从PolarDB-O中的任何类型到ANYDATA,它们都用作显式的CAST函数,示例如下:

STATIC FUNCTION ConvertInteger( num IN  INTEGER ) RETURN AnyData,
  STATIC FUNCTION ConvertNumber( num IN  NUMBER ) 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 ConvertNVarchar( c IN  NVARCHAR ) RETURN AnyData,
  STATIC FUNCTION ConvertNVarchar2( c IN  NVARCHAR2 ) RETURN AnyData,
  STATIC FUNCTION ConvertDate( ts IN  TIMESTAMP ) RETURN AnyData,
  STATIC FUNCTION ConvertTimestamp(ts IN TIMESTAMP ) RETURN AnyData,
  STATIC FUNCTION ConvertTimestampTZ(ts IN TIMESTAMP WITH 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 ConvertClob(c IN CLOB) RETURN ANYDATA,
  /*Don't support input AnyData type*/
  STATIC FUNCTION ConvertObject(obj IN AnyElement) RETURN AnyData,

访问功能可基于SQL使用。 这些函数不会在类型不匹配时引发异常。 相反,如果ANYDATA的类型与访问的类型不对应,则它们返回NULL。 如果只希望使用查询中返回的适当类型的ANYDATA函数,则应使用WHERE子句,该子句使用GETTYPENAME并选择您感兴趣的类型(例如“ SYS.NUMBER”)。 这些函数中的每个函数都在SYS.ANYDATA包装器中返回指定数据类型的值。

/* Get the fully qualified Type Name for the AnyData */
  MEMBER FUNCTION GetTypeName( self IN AnyData) RETURN VARCHAR2 DETERMINISTIC,

  /* Access functions for AnyData */
  MEMBER FUNCTION AccessInteger( self IN AnyData ) RETURN INTEGER , 
  MEMBER FUNCTION AccessNumber( self IN AnyData ) RETURN NUMBER ,
  MEMBER FUNCTION AccessChar( self IN AnyData ) RETURN CHAR ,
  MEMBER FUNCTION AccessVarchar( self IN AnyData ) RETURN VARCHAR ,
  MEMBER FUNCTION AccessVarchar2( self IN AnyData ) RETURN VARCHAR2 ,
  MEMBER FUNCTION AccessNVarchar( self IN AnyData ) RETURN NVARCHAR ,
  MEMBER FUNCTION AccessNVarchar2( self IN AnyData ) RETURN NVARCHAR2 ,
  MEMBER FUNCTION AccessDate( self IN AnyData ) RETURN DATE ,
  MEMBER FUNCTION AccessTimestamp( self IN AnyData ) RETURN TIMESTAMP ,
  MEMBER FUNCTION AccessTimestampTZ( self IN AnyData ) RETURN TIMESTAMP WITH TIME ZONE ,
  MEMBER FUNCTION AccessIntervalYM( self IN AnyData ) RETURN INTERVAL YEAR TO MONTH ,
  MEMBER FUNCTION AccessIntervalDS( self IN AnyData ) RETURN INTERVAL DAY TO SECOND ,   
  MEMBER FUNCTION AccessClob(self IN ANYDATA) return CLOB,
  
  /* Gets the current data value */
  MEMBER FUNCTION GetInteger( self IN AnyData, num OUT NOCOPY INTEGER) RETURN PLS_INTEGER ,
  MEMBER FUNCTION GetNumber( self IN AnyData, num OUT NOCOPY NUMBER) 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 GetNVarchar( self IN AnyData, c OUT NOCOPY NVARCHAR) RETURN PLS_INTEGER,
  MEMBER FUNCTION GetNVarchar2( self IN AnyData, c OUT NOCOPY NVARCHAR2) RETURN PLS_INTEGER,
  MEMBER FUNCTION GetDate( self IN AnyData, ts OUT NOCOPY DATE) RETURN PLS_INTEGER,
  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 GetIntervalYM( self IN AnyData, inv OUT NOCOPY INTERVAL YEAR TO MONTH) RETURN PLS_INTEGER,
  MEMBER FUNCTION GetIntervalDS( self IN AnyData, inv OUT NOCOPY INTERVAL DAY TO SECOND) RETURN PLS_INTEGER,
  MEMBER FUNCTION GetClob(self IN ANYDATA, c OUT NOCOPY CLOB) RETURN PLS_INTEGER,
  MEMBER FUNCTION GetObject( self IN AnyData, obj OUT NOCOPY AnyElement) RETURN PLS_INTEGER

示例

CREATE TABLE t1( c1 SYS.AnyData);
INSERT INTO t1 VALUES(AnyData.ConvertInteger(12));
INSERT INTO t1 VALUES(AnyData.ConvertNumber(10000));
INSERT INTO t1 VALUES(AnyData.ConvertChar('m'));

SELECT 
SYS.AnyData.GetTypeName(c1) typename, 
(CASE SYS.AnyData.GetTypeName(c1)
WHEN 'SYS.INTEGER' THEN SYS.AnyData.AccessNumber(c1)::varchar2
WHEN 'SYS.NUMBER' THEN SYS.AnyData.AccessNumber(c1)::varchar2
WHEN 'SYS.CHAR' THEN SYS.AnyData.AccessChar(c1)::varchar2
WHEN NULL THEN 'NULL' 
ELSE (
  CASE WHEN SYS.AnyData.GetTypeName(c1) IS NULL THEN 'NULL'
  ELSE CONCAT('**** unkonwn ****'::varchar2,SYS.AnyData.GetTypeName(c1))
  END
  )
END) ct, getAnyData(c1), c1
FROM t1 t1;
           typename           |                 ct                  |             getanydata              |                                  c1                                  
------------------------------+-------------------------------------+-------------------------------------+----------------------------------------------------------------------
 SYS.INTEGER                  | 12                                  | 12                                  | (SYS.INTEGER,12)
 SYS.NUMBER                   | 10000                               | 10000                               | (SYS.NUMBER,10000)
 SYS.CHAR                     | m                                   | m                                   | (SYS.CHAR,m)                      | 
(3 rows)