AI 助理
备案控制台
文档
输入文档关键字查找
首页 云原生数据库 PolarDB 云原生数据库PolarDB PostgreSQL版（兼容Oracle） 开发指南（Oracle语法兼容1.0） 内置函数 ANYDATA

ANYDATA

更新时间：
产品详情
相关技术圈
我的收藏

ANYDATA TYPE包含给定类型的实例，以及对该类型的描述。 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)

上一篇：子查询（EXISTS、IN、ANY、SOME、ALL）下一篇：GROUP_ID
文档推荐