YashanDB中的ANYDATA类型是数据库内置的一种自定义类型，用于存储给定类型的实例以及该类型的描述。

存储属性

类型	字节长度
ANYDATA	行存：[1,8KB]

定义格式：

类型	格式	规则
ANYDATA	ANYDATA	自定义对象类型，无需指定size

使用限制

对于ANYDATA类型数据的使用限制如下：

• 仅适用于HEAP表。

• 不能持久化存储LOB或BFILE类型数据。

• 存储到表中的ANYDATA限制在8K页面内。

静态方法Convert*

ANYDATA提供一系列静态方法用于将任意类型转换为ANYDATA实例。

STATIC FUNCTION ConvertBDouble(dbl IN BINARY_DOUBLE) return ANYDATA
STATIC FUNCTION ConvertBfile(b IN BFILE) return ANYDATA
STATIC FUNCTION ConvertBFloat(fl IN BINARY_FLOAT) return ANYDATA
STATIC FUNCTION ConvertBlob(b IN BLOB) return ANYDATA
STATIC FUNCTION ConvertChar(c IN CHAR) return ANYDATA
STATIC FUNCTION ConvertClob(c IN CLOB) return ANYDATA
--<collection_type>表示用户定义的任意集合类型
STATIC FUNCTION ConvertCollection(col IN <collection_type>) return ANYDATA
STATIC FUNCTION ConvertDate(dat IN DATE) return ANYDATA
STATIC FUNCTION ConvertIntervalDS(inv IN INTERVAL DAY TO SECOND) return ANYDATA
STATIC FUNCTION ConvertIntervalYM(invIN INTERVAL YEAR TO MONTH) return ANYDATA
STATIC FUNCTION ConvertNchar(nc IN NCHAR) return ANYDATA
STATIC FUNCTION ConvertNClob(nc IN NCLOB) return ANYDATA
STATIC FUNCTION ConvertNumber(num IN NUMBER) return ANYDATA
STATIC FUNCTION ConvertNVarchar2(nc IN NVARCHAR2) return ANYDATA
--<object_type>表示用户定义的任意对象类型
STATIC FUNCTION ConvertObject(obj IN <object_type>) return ANYDATA
STATIC FUNCTION ConvertRaw(r IN RAW) return ANYDATA
STATIC FUNCTION ConvertRef(rf IN <object_type>) return ANYDATA
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 ConvertURowid(rid IN ROWID) return ANYDATA
STATIC FUNCTION ConvertVarchar(c IN VARCHAR) return ANYDATA
STATIC FUNCTION ConvertVarchar2(c IN VARCHAR2) return ANYDATA

成员方法

ANYDATA提供成员方法用于从ANYDATA中提取值或获取类型信息。

Access*方法

若ANYDATA内部存储的类型与请求的类型不匹配，将返回NULL。

MEMBER FUNCTION AccessBDouble(self IN ANYDATA) return BINARY_DOUBLE
MEMBER FUNCTION AccessBfile(self IN ANYDATA) return BFILE
MEMBER FUNCTION AccessBFloat(self IN ANYDATA) return BINARY_FLOAT
MEMBER FUNCTION AccessBlob(self IN ANYDATA) return BLOB
MEMBER FUNCTION AccessChar(self IN ANYDATA) return CHAR
MEMBER FUNCTION AccessClob(self IN ANYDATA) return CLOB
MEMBER FUNCTION AccessDate(self IN ANYDATA) return DATE
MEMBER FUNCTION AccessIntervalYM(self IN ANYDATA) return INTERVAL YEAR TO MONTH
MEMBER FUNCTION AccessIntervalDS(self IN ANYDATA) return INTERVAL DAY TO SECOND
MEMBER FUNCTION AccessNchar(self IN ANYDATA) return NCHAR
MEMBER FUNCTION AccessNClob(self IN ANYDATA) return NCLOB
MEMBER FUNCTION AccessNumber(self IN ANYDATA) return NUMBER
MEMBER FUNCTION AccessNVarchar2(self IN ANYDATA) return NVARCHAR2
MEMBER FUNCTION AccessRaw(self IN ANYDATA) return RAW
MEMBER FUNCTION AccessTimestamp(self IN ANYDATA) return TIMESTAMP
MEMBER FUNCTION AccessTimestampLTZ(self IN ANYDATA) return TIMESTAMP WITH LOCAL TIME ZONE
MEMBER FUNCTION AccessTimestampTZ(self IN ANYDATA) return TIMESTAMP WITH TIME ZONE
MEMBER FUNCTION AccessURowid(self IN ANYDATA) return ROWID
MEMBER FUNCTION AccessVarchar(self IN ANYDATA) return VARCHAR
MEMBER FUNCTION AccessVarchar2(self IN ANYDATA) return VARCHAR2

类型信息查询

MEMBER FUNCTION GetTypeName(self IN ANYDATA) return VARCHAR2
MEMBER FUNCTION GetType(self IN ANYDATA, typ OUT NOCOPY ANYTYPE) return INT

• GetTypeName返回ANYDATA内部数据的类型名称（例如'SYS.NUMBER'）。
• GetType返回类型码和ANYDATA的内部数据的类型描述ANYTYPE。类型码及其对应的数据类型请参考DBMS_TYPES高级包中的TYPECODE常量。

Piecewise构建与读取

ANYDATA支持分片构建与读取模式，允许逐个元素地构建或读取集合或对象类型数据。

Piecewise构建方法

STATIC PROCEDURE BeginCreate(dtype IN OUT NOCOPY ANYTYPE, adata OUT NOCOPY ANYDATA)
MEMBER PROCEDURE EndCreate(self IN OUT NOCOPY ANYDATA)
MEMBER PROCEDURE SetNumber(self IN OUT NOCOPY ANYDATA, num IN NUMBER, 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 VARCHAR, last_elem IN BOOLEAN DEFAULT FALSE)
MEMBER PROCEDURE SetBDouble(self IN OUT NOCOPY ANYDATA, dbl IN BINARY_DOUBLE, 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 SetBFloat(self IN OUT NOCOPY ANYDATA, fl IN BINARY_FLOAT, 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 SetChar(self IN OUT NOCOPY ANYDATA, c IN CHAR, 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 SetDate(self IN OUT NOCOPY ANYDATA, dat IN DATE, 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 SetIntervalYM(self IN OUT NOCOPY ANYDATA, inv IN INTERVAL YEAR TO MONTH, 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 SetNClob(self IN OUT NOCOPY ANYDATA, nc IN NCLOB, 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 SetRaw(self IN OUT NOCOPY ANYDATA, r IN RAW, last_elem IN BOOLEAN DEFAULT FALSE)
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 SetCollection(self IN OUT NOCOPY ANYDATA, col IN <collection_type>, last_elem IN BOOLEAN DEFAULT FALSE)
MEMBER PROCEDURE SetObject(self IN OUT NOCOPY ANYDATA, obj IN <object_type>, last_elem IN BOOLEAN DEFAULT FALSE)
MEMBER PROCEDURE SetRef(self IN OUT NOCOPY ANYDATA, rf IN <object_type>, last_elem IN BOOLEAN DEFAULT FALSE)

• BeginCreate使用指定的ANYTYPE定义开始创建ANYDATA实例。
• Set*方法设置各个元素值，last_elem参数标识是否为集合中的最后一个元素。
• EndCreate完成ANYDATA创建过程。

Piecewise读取方法

MEMBER PROCEDURE Piecewise(self IN OUT NOCOPY ANYDATA)
MEMBER FUNCTION GetNumber(self IN ANYDATA, num OUT NOCOPY NUMBER) return INT
MEMBER FUNCTION GetVarchar(self IN ANYDATA, c OUT NOCOPY VARCHAR2) return INT
MEMBER FUNCTION GetVarchar2(self IN ANYDATA, c OUT NOCOPY VARCHAR2) return INT
MEMBER FUNCTION GetBDouble(self IN ANYDATA, dbl OUT NOCOPY BINARY_DOUBLE) return INT
MEMBER FUNCTION GetBfile(self IN ANYDATA, b OUT NOCOPY BFILE) return INT
MEMBER FUNCTION GetBFloat(self IN ANYDATA, fl OUT NOCOPY BINARY_FLOAT) return INT
MEMBER FUNCTION GetBlob(self IN ANYDATA, b OUT NOCOPY BLOB) return INT
MEMBER FUNCTION GetChar(self IN ANYDATA, c OUT NOCOPY CHAR) return INT
MEMBER FUNCTION GetClob(self IN ANYDATA, c OUT NOCOPY CLOB) return INT
MEMBER FUNCTION GetDate(self IN ANYDATA, dat OUT NOCOPY DATE) return INT
MEMBER FUNCTION GetIntervalDS(self IN ANYDATA, inv OUT NOCOPY INTERVAL DAY TO SECOND) return INT
MEMBER FUNCTION GetIntervalYM(self IN ANYDATA, inv OUT NOCOPY INTERVAL YEAR TO MONTH) return INT
MEMBER FUNCTION GetNChar(self IN ANYDATA, nc OUT NOCOPY NCHAR) return INT
MEMBER FUNCTION GetNClob(self IN ANYDATA, nc OUT NOCOPY NCLOB) return INT
MEMBER FUNCTION GetNVarchar2(self IN ANYDATA, nc OUT NOCOPY NVARCHAR2) return INT
MEMBER FUNCTION GetRaw(self IN ANYDATA, r OUT NOCOPY RAW) return INT
MEMBER FUNCTION GetTimestamp(self IN ANYDATA, ts OUT NOCOPY TIMESTAMP) return INT
MEMBER FUNCTION GetTimestampTZ(self IN ANYDATA, ts OUT NOCOPY TIMESTAMP WITH TIME ZONE) return INT
MEMBER FUNCTION GetTimestampLTZ(self IN ANYDATA, ts OUT NOCOPY TIMESTAMP WITH LOCAL TIME ZONE) return INT
MEMBER FUNCTION GetCollection(self IN ANYDATA, col OUT NOCOPY <collection_type>) return INT
MEMBER FUNCTION GetObject(self IN ANYDATA, obj OUT NOCOPY <object_type>) return INT
MEMBER FUNCTION GetRef(self IN ANYDATA, rf OUT NOCOPY <object_type>) return INT

• 通过调用Piecewise方法进入Piecewise读取模式。
• Get*方法在普通模式下类似于Access*方法，在Piecewise读取模式下逐个获取集合或对象的元素值。成功返回DBMS_TYPES.SUCCESS和指定类型的值，当集合中没有更多元素时返回DBMS_TYPES.NO_DATA。如果ANYDATA内部存储的类型（或元素类型）与请求的类型不匹配，则报错类型不匹配。

使用示例

SET serverout ON

--example 1
DECLARE
  adata ANYDATA;
BEGIN
  adata := ANYDATA.ConvertNumber(123);
  DBMS_OUTPUT.PUT_LINE(adata.GetTypeName);
  DBMS_OUTPUT.PUT_LINE(adata.AccessNumber);
END;
/
--result
SYS.NUMBER
123

--example 2
CREATE OR REPLACE TYPE num_table IS TABLE OF NUMBER;
/

DECLARE
  adata ANYDATA;
  ntb NUM_TABLE;
  num NUMBER;
  ret INT;
BEGIN
  adata := ANYDATA.ConvertCollection(NUM_TABLE(1, 2, 3));
  DBMS_OUTPUT.PUT_LINE(adata.GetTypeName);
  ret := adata.GetCollection(ntb);
  DBMS_OUTPUT.PUT_LINE(ntb(1));
  adata.piecewise;
  LOOP
    ret := adata.GetNumber(num);
    DBMS_OUTPUT.PUT_LINE(num);
    EXIT WHEN ret = DBMS_TYPES.NO_DATA;
  END LOOP;
END;
/
--result
SALES.NUM_TABLE
1
1
2
3

--example 3
DECLARE
  adata ANYDATA;
  atype ANYTYPE;
  num NUMBER;
  ret  INT;
BEGIN
  atype := ANYTYPE.GetPersistent('SALES', 'NUM_TABLE');
  ANYDATA.BeginCreate(atype, adata);
  adata.SetNumber(1.5, FALSE);
  adata.SetNumber(2.5, TRUE);
  adata.EndCreate;
  adata.piecewise;
  LOOP
    ret := adata.GetNumber(num);
    DBMS_OUTPUT.PUT_LINE(num);
    EXIT WHEN ret = DBMS_TYPES.NO_DATA;
  END LOOP;
END;
/
--result
1.5
2.5

--example 4
CREATE TABLE tb_anydata(id INT, c1 ANYDATA);
INSERT INTO tb_anydata VALUES(1, ANYDATA.ConvertNumber(100));
INSERT INTO tb_anydata VALUES(2, ANYDATA.ConvertVarchar('hello'));
COMMIT;

SELECT id, CAST(c1 AS NUMBER) FROM tb_anydata WHERE id = 1;

          ID CAST(C1ASNUMBER) 
------------ ---------------- 
           1              100


SELECT id, CAST(c1 AS VARCHAR) FROM tb_anydata WHERE id = 2;

          ID CAST(C1ASVARCHAR)                                                
------------ ---------------------------------------------------------------- 
           2 hello