Oracle—anydata数据类型及国产数据库anydata实现思考

Oracle—anydata数据类型

anydata概述

Oracle的ANYDATA数据类型是一种特殊的数据类型，它允许用户在Oracle数据库中存储和操作不同数据类型的数据。ANYDATA类型可以存储各种数据类型，包括内置类型和用户自定义类型。

使用ANYDATA类型，用户可以将不同数据类型的值存储在一个列中，而无需事先知道这些值的具体数据类型。这对于处理灵活的数据模型或存储异构数据非常有用。

anydata支持的数据类型

Oracle 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,
    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,
    STATIC FUNCTION ConvertObject(obj IN "<object_type>") RETURN ANYDATA,
    STATIC FUNCTION ConvertRaw(r IN RAW) RETURN ANYDATA,
    STATIC FUNCTION ConvertRef(rf IN REF "<object_type>") RETURN ANYDATA,
    STATIC FUNCTION ConvertTimestamp(ts IN TIMESTAMP) return ANYDATA,
    STATIC FUNCTION ConvertTimestampTZ(ts IN TIMESTAMP WITH TIMEZONE) return ANYDATA,
    STATIC FUNCTION ConvertTimestampLTZ(ts IN TIMESTAMP WITH LOCAL TIMEZONE) return ANYDATA,
    STATIC FUNCTION ConvertURowid(rid IN UROWID) return ANYDATA,
    STATIC FUNCTION ConvertVarchar(c IN VARCHAR) RETURN ANYDATA,
    STATIC FUNCTION ConvertVarchar2(c IN VARCHAR2) RETURN ANYDATA,

从anydata转换成原类型

MEMBER FUNCTION AccessBDouble(self IN ANYDATA) return BINARY_DOUBLE
   DETERMINISTIC,
MEMBER FUNCTION AccessBfile(self IN ANYDATA) return BFILE,
MEMBER FUNCTION AccessBFloat(self IN ANYDATA) return BINARY_FLOAT
   DETERMINISTIC,
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 
   TIMEZONE,
MEMBER FUNCTION AccessTimestampTZ(self IN ANYDATA) return TIMESTAMP WITH 
   TIMEZONE,
MEMBER FUNCTION AccessURowid(self IN ANYDATA) return UROWID DETERMINISTIC
MEMBER FUNCTION AccessVarchar(self IN ANYDATA) return VARCHAR,
MEMBER FUNCTION AccessVarchar2(self IN ANYDATA) return VARCHAR2,

anydata案例

SQL

1. 创建表：首先，创建一个表，其中包含一列定义为ANYDATA类型。例如：

CREATE TABLE my\_table (

  data ANYDATA

);

2. 插入数据：使用Oracle提供的ANYDATA构造函数将不同数据类型的值插入到ANYDATA列中。例如，插入一个整数和一个字符串：

INSERT INTO my\_table (data) VALUES (ANYDATA.ConvertNumber(42));
INSERT INTO my\_table (data) VALUES (ANYDATA.ConvertVARCHAR2('Hello, World!'));

3. 查询数据：要查询存储在ANYDATA列中的数据，使用ANYDATA类型的方法提取原始数据类型。例如，提取整数和字符串：

SELECT data.GetNumber() AS number\_data FROM my\_table WHERE data IS OF (NUMBER);
SELECT data.GetVARCHAR2() AS string\_data FROM my\_table WHERE data IS OF (VARCHAR2);

在上述查询中，我们使用`IS OF`运算符来过滤特定数据类型的数据。

PL/SQL

PL/SQL也提供CONVERT*、ACCESS*和GETTYPENAME函数。由于以下示例不向数据库保留任何内容，因此还包括BLOB和CLOB转换。

    SET SERVEROUTPUT ON
    
    DECLARE
      l_varchar2 VARCHAR2(50) := 'This is some data';
      l_number   NUMBER       := 1234567890;
      l_date     DATE         := TO_DATE('01-JAN-2012','DD-MON-YYYY');
      l_clob     CLOB         := 'This is some CLOB data';
      l_blob     BLOB         := UTL_RAW.cast_to_raw('This is some BLOB data');
      l_anydata  SYS.ANYDATA;
    BEGIN
      -- Convert VARCHAR2 to ANYDATA and back.
      l_anydata  := SYS.ANYDATA.convertVarchar2(l_varchar2);
      l_varchar2 := SYS.ANYDATA.accessVarchar2(l_anydata);
      DBMS_OUTPUT.put_line('VARCHAR2: ' || l_varchar2);
    
      -- Convert NUMBER to ANYDATA and back.
      l_anydata  := SYS.ANYDATA.convertNumber(l_number);
      l_number   := SYS.ANYDATA.accessNumber(l_anydata);
      DBMS_OUTPUT.put_line('NUMBER  : ' || TO_CHAR(l_number));
    
      -- Convert DATE to ANYDATA and back.
      l_anydata  := SYS.ANYDATA.convertDate(l_date);
      l_date     := SYS.ANYDATA.accessDate(l_anydata);
      DBMS_OUTPUT.put_line('DATE    : ' || TO_CHAR(l_date, 'DD-MON-YYYY'));
    
      -- Convert ANYDATA to CLOB
      l_anydata  := SYS.ANYDATA.convertClob(l_clob);
      l_clob     := SYS.ANYDATA.accessClob(l_anydata);
      DBMS_OUTPUT.put_line('CLOB    : ' || l_clob);
    
      -- Convert ANYDATA to BLOB
      l_anydata  := SYS.ANYDATA.convertBlob(l_blob);
      l_blob     := SYS.ANYDATA.accessBlob(l_anydata);
      DBMS_OUTPUT.put_line('BLOB    : ' || UTL_RAW.cast_to_varchar2(l_blob));
    END;
    /
    VARCHAR2: This is some data
    NUMBER  : 1234567890
    DATE    : 01-JAN-2012
    CLOB    : This is some CLOB data
    BLOB    : This is some BLOB data
    
    PL/SQL procedure successfully completed.
    
    SQL>

从PL/SQL中，我们还可以使用GET_函数将数据从ANYDATA类型中提取到变量中。_

    SET SERVEROUTPUT ON
    
    DECLARE
      l_varchar2 VARCHAR2(50) := 'This is some data';
      l_anydata  SYS.ANYDATA;
    BEGIN
      -- Convert VARCHAR2 to ANYDATA and back.
      l_anydata  := SYS.ANYDATA.convertVarchar2(l_varchar2);
      
      IF l_anydata.getVarchar2(l_varchar2) = DBMS_TYPES.SUCCESS THEN
        DBMS_OUTPUT.put_line('VARCHAR2: ' || l_varchar2);
      END IF;
    END;
    /
    VARCHAR2: This is some data
    
    PL/SQL procedure successfully completed.
    
    SQL>

Complex Types

anydata同样支持复杂类型以及用户自定义类型，我们可以通过CONVERTOBJECT和GETOBJECT函数来实现

    CREATE OR REPLACE TYPE t_my_type AS OBJECT (
      g_val1 VARCHAR2(10),
      g_val2 VARCHAR2(10)
    );
    /
    
    SET SERVEROUTPUT ON
    
    DECLARE
      l_obj     t_my_type := t_my_type('1111111111', '2222222222');
      l_anydata SYS.ANYDATA;
    BEGIN
      -- Convert Object to ANYDATA and back.
      l_anydata := SYS.ANYDATA.convertObject(l_obj);
      
      IF l_anydata.getObject(l_obj) = DBMS_TYPES.SUCCESS THEN
        DBMS_OUTPUT.put_line('T_MY_TYPE : ' || l_obj.g_val1 || ' : ' || l_obj.g_val2);
      END IF;
    END;
    /
    T_MY_TYPE : 1111111111 : 2222222222
    
    PL/SQL procedure successfully completed.
    
    SQL>

国产数据库anydata类型实现

国产关系型数据库，根据出身可分为，MySQL系列，PostgreSQL系列，自研系列

这里讲下PostgreSQL系列数据库如何实现Oracle的anydata类型的思路

对于anydata类型数据，存放的数据类型不同，所需要的存储空间也不同，同时anydata数据存在瞬态和持久态两种形态，这里我们首先区分什么时候是瞬态类型，什么时候是持久态类型，

瞬态就是PL/SQL中临时定义的，在存在于运行期间，即在内存中，不会持久化到硬盘中

持久态则是要持久化到硬盘中的

在Oracle中，anydata数据是不会直接显示给用户的，需要用函数来进行转换才会显示

PostgreSQL中有一种基础类型bytea，bytea可以存储任何二进制字符串。bytea类型是变长的，它的存储空间由实际的二进制数据决定。基于bytea类型来实现anydata，优点是，bytea是pg基本类型，在anydata类型实现上可以直接利用现有的资源来进行开发实现，缺点就是用户可以直接进行访问和操作bytea实现的anydata，这样无法用它来对anydata数据的完整性进行校验。

对于anydata的实现，专门的实现一种数据类型，优点是，可以很好的校验数据完整性，缺点就是相关接口需要重新实现，开发周期较长。

目前已有相关基于pg开发的国产数据库厂商实现了Oracle的anydata数据类型。

未完待续…

参考文献

ANYDATA TYPE (oracle.com)