人大金仓数据库KingbaseES V9 anydata数据类型介绍

沉舟侧畔千帆过_
111 阅读4分钟

关键字：人大金仓、KingbaseES、anydata

一、anydata概述

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

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

二、anydata支持的数据类型

Oracle anydata支持以下类型

这里是转换成anydata

STATIC FUNCTION ConvertNumber(num IN NUMBER) return AnyData,

STATIC FUNCTION ConvertDate(dat IN sys.DATE) 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 ConvertBlob(b IN BLOB) return AnyData,

STATIC FUNCTION ConvertClob(c IN CLOB) return AnyData,

STATIC FUNCTION ConvertBfile(b IN BFILE) 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 ConvertIntervalYM(inv IN INTERVAL YEAR TO MONTH) return AnyData,

STATIC FUNCTION ConvertIntervalDS(inv IN INTERVAL DAY TO SECOND) return AnyData,

STATIC FUNCTION ConvertNchar(nc IN NCHAR) return AnyData,

STATIC FUNCTION ConvertNVarchar2(nc IN NVARCHAR2) return AnyData,

STATIC FUNCTION ConvertNClob(nc IN NCLOB) return AnyData,

STATIC FUNCTION ConvertBFloat(fl IN BINARY_FLOAT) return AnyData,

STATIC FUNCTION ConvertBDouble(dbl IN BINARY_DOUBLE) return AnyData,

从anydata转换成原类型

MEMBER FUNCTION AccessNumber(self IN AnyData) return NUMBER ,

MEMBER FUNCTION AccessDate(self IN AnyData) return sys.DATE ,

MEMBER FUNCTION AccessChar(self IN AnyData) return CHARACTER,

MEMBER FUNCTION AccessVarchar(self IN AnyData) return VARCHAR ,

MEMBER FUNCTION AccessVarchar2(self IN AnyData) return VARCHAR2 ,

MEMBER FUNCTION AccessBlob(self IN AnyData) return BLOB ,

MEMBER FUNCTION AccessClob(self IN AnyData) return CLOB ,

MEMBER FUNCTION AccessBfile(self IN AnyData) return BFILE ,

MEMBER FUNCTION AccessTimestamp(self IN AnyData) return timestamp ,

MEMBER FUNCTION AccessTimestampTZ(self IN AnyData) return timestamp with time zone ,

MEMBER FUNCTION AccessTimestampLTZ(self IN AnyData) return timestamp with local 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 AccessNchar(self IN AnyData) return CHARACTER ,

MEMBER FUNCTION AccessNVarchar2(self IN AnyData) return NVARCHAR2 ,

MEMBER fuNCTION AccessNClob(self IN AnyData) return NCLOB ,

MEMBER FUNCTION AccessBFloat(self IN AnyData) return BINARY_FLOAT ,

MEMBER FUNCTION AccessBDouble(self IN AnyData) return BINARY_DOUBLE ,

三、anydata使用案例

首先要添加anydata插件

--因为DBMS_TYPES包中需要用到raw类型，所以需要先创建kdb_raw插件。

create extension kdb_raw;

create extension dbms_types ;

create extension kdb_anydata ;

3.1 SQL

创建表

create table t1(id int, a1 anydata);

insert into t1 values(1, anydata.ConvertNumber(123.456::number));

insert into t1 values(2, anydata.ConvertDate('2022-09-27'::date));

insert into t1 values(3, anydata.ConvertChar('Kingbase'::char(8)));

insert into t1 values(4, anydata.ConvertVarchar('Kingbase'::varchar));

insert into t1 values(5, anydata.ConvertVarchar2('Kingbase'::varchar2));

insert into t1 values(6, anydata.ConvertBDouble(123.456::BINARY_DOUBLE));

insert into t1 values(7, anydata.ConvertBlob('Kingbase'::blob));

insert into t1 values(8, anydata.ConvertClob('Kingbase'::clob));

insert into t1 values(9, anydata.ConvertBfile(bfilename('','myfile.sql')));

insert into t1 values(10, anydata.ConvertTimestamp('2022-09-27 08:30:00'::timestamp));

insert into t1 values(11, anydata.ConvertTimestampTZ('2022-09-27 08:30:00+02'::timestamp with time zone));

insert into t1 values(12, anydata.ConvertTimestampLTZ('2022-09-27 08:30:00+02'::timestamp with local time zone));

insert into t1 values(13, anydata.ConvertIntervalYM(to_yminterval('10-9')));

insert into t1 values(14, anydata.ConvertIntervalDS(to_dsinterval('-1:02:03.3')));

insert into t1 values(15, anydata.ConvertNchar('Kingbase'::nchar(8)));

insert into t1 values(16, anydata.ConvertNVarchar2('Kingbase'::nvarchar2));

insert into t1 values(17, anydata.ConvertNClob('Kingbase'::nclob));

insert into t1 values(18, anydata.ConvertBFloat(123.456::BINARY_FLOAT));

select id, a1.AccessNumber() from t1;

select id, a1.AccessDate() from t1;

select id, a1.AccessChar() from t1;

select id, a1.AccessVarchar() from t1;

select id, a1.accessvarchar2() from t1;

select id, a1.AccessBlob() from t1;

select id, a1.AccessClob() from t1;

select id, a1.AccessBfile() from t1;

select id, a1.AccessTimestamp() from t1;

select id, a1.AccessTimestampTZ() from t1;

select id, a1.AccessTimestampLTZ() from t1;

select id, a1.AccessIntervalYM() from t1;

select id, a1.AccessIntervalDS() from t1;

select id, a1.AccessNchar() from t1;

select id, a1.AccessNVarchar2() from t1;

select id, a1.AccessNClob() from t1;

select id, a1.AccessBFloat() from t1;

select id, a1.AccessBDouble() from t1;

3.2 PL/SQL

PL/SQL也可以使用 anydata数据类型

CREATE OR REPLACE FUNCTION fw_get_sys_anydata (

p_sys_anydata IN ANYDATA

) RETURNS VARCHAR2

AS

DECLARE

v_return_value VARCHAR2(4000);

v_get_status VARCHAR2(1);

v_number_field NUMBER;

v_date_field sys.DATE;

v_char_field CHAR(4000);

v_varchar2_field VARCHAR2(4000);

-- v_raw_field raw;

v_blob_field blob;

v_clob_field CLOB;

v_bfile_field bfile;

v_timestamp_field TIMESTAMP;

v_timestamptz_field timestamp with time zone;

v_intervalym_field interval year to MONTH;

v_intervalds_field interval day to SECOND;

v_nclob_field nclob;

v_bfloat_field BINARY_FLOAT;

v_bdouble_field BINARY_DOUBLE;

--v_obj obj1;

--v_col col1;

--v_nest nest1;

BEGIN

IF p_sys_anydata IS NOT NULL THEN

CASE anydata.gettypename(p_sys_anydata)

WHEN 'pg_catalog.numeric' THEN

v_get_status := p_sys_anydata.getnumber(v_number_field);

v_return_value := TO_CHAR(v_number_field);

when 'sys.date' then

v_get_status := p_sys_anydata.getdate(v_date_field);

v_return_value := TO_CHAR(v_date_field, 'MM/dd/yyyy HH24:mi:ss');

when 'pg_catalog.bpchar' then

v_get_status := p_sys_anydata.getchar(v_char_field);

v_return_value := rtrim(v_char_field);

when 'pg_catalog.varchar' then

v_get_status := p_sys_anydata.getvarchar2(v_varchar2_field);

v_return_value := v_varchar2_field;

--when 'pg_catalog.varchar' then

--when 'sys.raw' then

-- v_get_status := p_sys_anydata.getraw(v_raw_field);

-- v_return_value := v_raw_field;

when 'pg_catalog.blob' then

v_get_status := p_sys_anydata.getblob(v_blob_field);

v_return_value := v_blob_field;

when 'pg_catalog.clob' then

v_get_status := p_sys_anydata.getclob(v_clob_field);

v_return_value := v_clob_field;

when 'pg_catalog.bfile' then

v_get_status := p_sys_anydata.getbfile(v_bfile_field);

v_return_value := v_bfile_field;

when 'pg_catalog.timestamp' then

v_get_status := p_sys_anydata.gettimestamp(v_timestamp_field);

v_return_value := TO_CHAR(v_timestamp_field, 'MM/dd/yyyy HH24:mi:ss');

when 'pg_catalog.timestamptz' then

v_get_status := p_sys_anydata.GetTimestampTZ(v_timestamptz_field);

v_return_value := TO_CHAR(v_timestamptz_field, 'MM/dd/yyyy HH24:mi:ss');

--when 'pg_catalog.timestamp' then

when 'sys.yminterval' then

v_get_status := p_sys_anydata.GetIntervalYM(v_intervalym_field);

v_return_value := v_intervalym_field;

when 'sys.dsinterval' then

v_get_status := p_sys_anydata.GetIntervalDS(v_intervalds_field);

v_return_value := v_intervalds_field;

--when 'pg_catalog.bpchar' then

--when 'pg_catalog.varchar' then

when 'pg_catalog.nclob' then

v_get_status := p_sys_anydata.GetNClob(v_nclob_field);

v_return_value := v_nclob_field;

when 'pg_catalog.float4' then

v_get_status := p_sys_anydata.GetBFloat(v_bfloat_field);

v_return_value := v_bfloat_field;

when 'pg_catalog.float8' then

v_get_status := p_sys_anydata.GetBDouble(v_bdouble_field);

v_return_value := v_bdouble_field;

/* when 'public.obj1' then

v_get_status := p_sys_anydata.GetObject(v_obj);

v_return_value := v_obj;

when 'publie.col1' then

v_get_status := p_sys_anydata.GetCollection(v_col);

v_return_value := v_col;

when 'public.nest1' then

v_get_status := p_sys_anydata.GetCollection(v_nest);

v_return_value := v_nest; */

ELSE

v_return_value := '** unknown **';

END CASE;

ELSE

v_return_value := 'null';

END IF;

dbms_output.put_line('type: ' || anydata.gettypename(p_sys_anydata) || ', value: ' || v_return_value);

RETURN v_return_value;

END fw_get_sys_anydata;

3.3 Complex Types

anydata同样支持复杂类型以及用户自定义类型

--persistent object type

create or replace type objtyp4 as object(a1 number, a2 varchar2(50));

/

declare

atype anytype;

adata anydata;

n1 number;

v1 varchar2(100);

ret int;

begin

atype := getanytypefrompersistent('public', 'objtyp4');

anydata.begincreate(atype, adata);

adata.setnumber(123.456);

adata.setvarchar2('Kingbase');

adata.endcreate();

adata.piecewise();

ret := adata.getvarchar2(v1);

ret := adata.getnumber(n1);

dbms_output.put_line('ret: ' || ret || ' n1:' || n1 || ' v1: ' || v1);

end;

/

drop type objtyp4;

/

avatar
文章
阅读
粉丝