ANYDATA

CONVERT *调用可通过一次调用来完整地构建ANYDATA。 从PolarDB PostgreSQL版（兼容Oracle）中的任何类型到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

示例

1. 创建测试函数getAnyData，根据输入的AnyData类型数据v_data，返回相应的数据类型的值。

   CREATE OR REPLACE FUNCTION getAnyData(v_data IN AnyData) RETURN VARCHAR2 
   IS
   	  v_int INTEGER;
   	  v_num NUMBER;
   	  v_char CHAR;
   	  v_varchar VARCHAR;
   	  v_varchar2 VARCHAR2;
   	  v_nvarchar NVARCHAR;
   	  v_nvarchar2 NVARCHAR2;  
   	  v_date DATE;
   	  v_timestamp TIMESTAMP;
   	  v_timestamptz TIMESTAMP WITH TIME ZONE;
   	  v_intervalds INTERVAL DAY TO SECOND;
   	  v_intervalym INTERVAL YEAR TO MONTH;
   	  v_clob CLOB;
   	  v_blob BLOB;
   	  v_object AnyData;
   	  v_re_data VARCHAR2;
   BEGIN
   	  IF v_data IS NOT NULL THEN 
   		 CASE v_data.getTypeName
   		 WHEN 'SYS.INTEGER' THEN
   		  IF (v_data.getInteger(v_int) = 0) THEN
   			 v_re_data := v_int;
   		  END IF;  
   		 WHEN 'SYS.NUMBER' THEN
   		  IF (v_data.getNumber(v_num) = 0) THEN
   			 v_re_data := v_num;
   		  END IF;
   		 WHEN 'SYS.CHAR' THEN
   		  IF (v_data.getChar(v_char) = 0) THEN
   			 v_re_data := v_char;
   		  END IF;	
   		 WHEN 'SYS.VARCHAR' THEN
   		  IF (v_data.getVarchar(v_varchar) = 0) THEN
   			 v_re_data := v_varchar;
   		  END IF;	  	  
   		 WHEN 'SYS.VARCHAR2' THEN
   		  IF (v_data.getVarchar2(v_varchar2) = 0) THEN
   			 v_re_data := v_varchar2;
   		  END IF;	 
   		 WHEN 'SYS.NVARCHAR' THEN
   		  IF (v_data.getNVarchar(v_nvarchar) = 0) THEN
   			 v_re_data := v_nvarchar;
   		  END IF;		  
   		 WHEN 'SYS.NVARCHAR2' THEN
   		  IF (v_data.getNVarchar2(v_nvarchar2) = 0) THEN
   			 v_re_data := v_nvarchar2;
   		  END IF;		  
   		 WHEN 'SYS.DATE' THEN
   		  IF (v_data.getDate(v_date) = 0) THEN
   			 v_re_data := v_date;
   		  END IF;
   		 WHEN 'SYS.TIMESTAMP' THEN
   		  IF (v_data.getTimestamp(v_timestamp) = 0) THEN
   			 v_re_data := v_timestamp;
   		  END IF;
   		 WHEN 'SYS.TIMESTAMP WITH TIME ZONE' THEN
   		  IF (v_data.getTimestampTZ(v_timestamptz) = 0) THEN
   			 v_re_data := v_timestamptz;
   		  END IF;	
   		 WHEN 'SYS.INTERVAL DAY TO SECOND' THEN
   		  IF (v_data.getIntervalDS(v_intervalds) = 0) THEN
   			 v_re_data := v_intervalds;
   		  END IF;	
   		 WHEN 'SYS.INTERVAL YEAR TO MONTH' THEN
   		  IF (v_data.getIntervalYM(v_intervalym) = 0) THEN
   			 v_re_data := v_intervalym;
   		  END IF;	  
   		 WHEN 'SYS.CLOB' THEN
   		  IF (v_data.getClob(v_clob) = 0) THEN
   			 v_re_data := v_clob;
   		  END IF;
   		 WHEN 'SYS.BLOB' THEN
   		  IF (v_data.getBlob(v_blob) = 0) THEN
   			 v_re_data := v_blob;
   		  END IF;
   		 WHEN 'PUBLIC.TEST_TYPE' THEN
   		  IF (v_data.getClob(v_clob) = 0) THEN
   			 v_re_data := v_clob;
   		  END IF;
   		 ELSE
   		   v_re_data := ' ** unkonwn ** ' || v_data.getTypeName;
   		 END CASE;
   	 ELSE 
   		RETURN NULL;
   	 END IF;
      RETURN v_re_data;
   END getAnyData;

2. 使用getAnyData函数，对数据的类型进行转换处理。

   ---创建准备数据
   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'));
   
   ---使用getAnyData函数
   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)