U0100086：GaussDB不支持anydata数据类型

描述

GaussDB 不支持anydata数据类型以及构造方法，分普通类型以及构造方法(Convert*,Access*,Get*)和复合类型以及构造方法(ConvertObject,GetObject)。

数据库类型与版本

源库类型与版本：UGO 支持的 Oracle 版本。
目标库类型与版本：UGO 支持的 GaussDB 版本。

语法示例

示例1：普通类型以及构造方法
```
CREATE TABLE t1( c1 sys.anydata);
```

示例2：复合类型以及构造方法

-- 创建自定义对象类型
CREATE TYPE person_obj AS OBJECT (
   id NUMBER,
   name VARCHAR2(100),
   birthdate DATE
);
-- 创建表
drop table anydata_table;
create table anydata_table(name anydata);
-- 使用ConvertObject
DECLARE
   v_person person_obj := person_obj(1, 'John Doe', TO_DATE('1980-01-15', 'YYYY-MM-DD'));
   v_anydata ANYDATA;
BEGIN
   v_anydata := ANYDATA.ConvertObject(v_person);
   INSERT INTO anydata_table VALUES (v_anydata);
   COMMIT;
END;
/
-- 查看GetObject
DECLARE
   v_anydata ANYDATA;
   v_person person_obj;
   v_status NUMBER;
BEGIN
   -- 从表中获取ANYDATA数据
   SELECT name INTO v_anydata FROM anydata_table;
   
   -- 转换回对象
   v_status := v_anydata.GetObject(v_person);
   
   IF v_status = DBMS_TYPES.SUCCESS THEN
  DBMS_OUTPUT.PUT_LINE('ID: ' || v_person.id);
      DBMS_OUTPUT.PUT_LINE('Name: ' || v_person.name);
      DBMS_OUTPUT.PUT_LINE('Birthdate: ' || TO_CHAR(v_person.birthdate, 'YYYY-MM-DD'));
   ELSE
      DBMS_OUTPUT.PUT_LINE('Error converting object: ' || v_status);
   END IF;
END;
/

改造建议

示例1：普通类型以及构造方法改写建议

1、anydata改写为同名的type（typename_value的类型为varchar2）+package(gettypename,Convert*,Access*,Get*)+package body(gettypename,Convert*,Access*,Get*),并且创建到public下面；

2、转换后删除sys.；

3、注意：下面不构造，原因是GaussDB不支持此类型，UGO转换配置控制，PLSQL可能存在类型不匹配的情况，按实际情况加。

---Convert构造数据

--FUNCTION ConvertURowid(rid IN UROWID) return ANYDATA; ---Gauss不支持数据类型

--FUNCTION ConvertTimestampLTZ(ts IN timestamp with local time zone) return AnyData;---Gauss不支持数据类型

--FUNCTION ConvertBfile(b IN BFILE) return AnyData;---Gauss不支持数据类型

--FUNCTION ConvertNClob(nc IN NCLOB) return AnyData;---Gauss不支持数据类型

--FUNCTION ConvertBFloat(fl IN BINARY_FLOAT) return AnyData;---Gauss不支持数据类型

--FUNCTION ConvertObject(obj IN "<object_type>") RETURN ANYDATA; ---object类型无法确定，无法直接改造

--FUNCTION ConvertRef(rf IN REF "<object_type>") RETURN ANYDATA; ---object类型无法确定，无法直接改造

--FUNCTION ConvertCollection(col IN "collection_type") RETURN ANYDATA ---object类型无法确定，无法直接改造

---Access获取数据

--FUNCTION AccessBFloat(self IN AnyData) return BINARY_FLOAT ; ---Gauss不支持数据类型

--FUNCTION AccessURowid(self IN ANYDATA) return UROWID DETERMINISTIC; ---Gauss不支持数据类型

--FUNCTION AccessTimestampLTZ(self IN AnyData) return timestamp with local time zone ; ---Gauss不支持数据类型

--FUNCTION AccessBfile(self IN AnyData) return BFILE ; ---Gauss不支持数据类型

--FUNCTION AccessNClob(self IN AnyData) return NCLOB ; ---Gauss不支持数据类型

----GET获取当前数据

--FUNCTION GetBfile( self IN ANYDATA, b OUT BFILE) RETURN INTEGER; ---Gauss不支持数据类型

--FUNCTION GetBFloat( self IN ANYDATA, fl OUT BINARY_FLOAT) RETURN INTEGER;---Gauss不支持数据类型

--FUNCTION GetCollection(self IN ANYDATA,col OUT "<collection_type>")RETURN INTEGER;---Gauss不支持数据类型

--FUNCTION GetObject(self IN ANYDATA,obj OUT "<object_type>")RETURN INTEGER; object类型无法确定，无法直接改造

--FUNCTION GetRef(self IN ANYDATA,rf OUT REF "<object_type>")RETURN INTEGER;object类型无法确定，无法直接改造

--FUNCTION GetTimestampLTZ(self IN ANYDATA,ts OUT TIMESTAMP WITH LOCAL TIME ZONE) RETURN INTEGER; ---Gauss不支持数据类型

--FUNCTION GetNClob(self IN ANYDATA, nc OUT NCLOB) RETURN INTEGER; ---Gauss不支持数据类型

GaussDB改写后：

--首先构造在public下面anydata类型以及构造方法
--type：anydata包含普通类型
drop type public.anydata cascade;
CREATE  TYPE public.anydata AS  (
 typename VARCHAR2,  
 typename_value      VARCHAR2
);
--package ：anydata方法：仅处理兼容的普通数据类型，不兼容数据类型或者object不处理
CREATE OR REPLACE PACKAGE public.anydata AS
 -GetTypeName类型检查
 FUNCTION gettypename(self IN ANYDATA) RETURN       VARCHAR2;
 --Convert构造数据
 FUNCTION ConvertNumber(num IN NUMBER) return AnyData;
 FUNCTION ConvertDate(dat IN DATE) return AnyData;
 FUNCTION ConvertChar(c IN CHAR) return AnyData;
 FUNCTION ConvertVarchar(c IN VARCHAR) return AnyData;
 FUNCTION ConvertVarchar2(c IN VARCHAR2) return AnyData;
 FUNCTION ConvertBlob(b IN BLOB) return AnyData;
 FUNCTION ConvertClob(c IN CLOB) return AnyData;
 FUNCTION ConvertTimestamp(ts IN TIMESTAMP) return AnyData;
 FUNCTION ConvertTimestampTZ(ts IN timestamp with time zone) return AnyData;
 FUNCTION ConvertIntervalYM(inv IN INTERVAL YEAR TO MONTH) return AnyData;
 FUNCTION ConvertIntervalDS(inv IN INTERVAL DAY TO SECOND) return AnyData;
 FUNCTION ConvertNchar(nc IN NCHAR) return AnyData;
 FUNCTION ConvertNVarchar2(nc IN NVARCHAR2) return AnyData;
 FUNCTION ConvertRaw(r IN RAW) RETURN ANYDATA;
 FUNCTION ConvertBDouble(dbl IN BINARY_DOUBLE) return AnyData;
--FUNCTION ConvertURowid(rid IN UROWID) return ANYDATA; ---Gauss不支持数据类型
--FUNCTION ConvertTimestampLTZ(ts IN timestamp with local time zone) return AnyData;---Gauss不支持数据类型
--FUNCTION ConvertBfile(b IN BFILE) return AnyData;---Gauss不支持数据类型
--FUNCTION ConvertNClob(nc IN NCLOB) return AnyData;---Gauss不支持数据类型
--FUNCTION ConvertBFloat(fl IN BINARY_FLOAT) return AnyData;---Gauss不支持数据类型
--FUNCTION ConvertObject(obj IN "<object_type>") RETURN ANYDATA; ---object类型无法确定，无法直接改造
--FUNCTION ConvertRef(rf IN REF "<object_type>") RETURN ANYDATA; ---object类型无法确定，无法直接改造
--FUNCTION ConvertCollection(col IN "collection_type") RETURN ANYDATA ---object类型无法确定，无法直接改造
-Access获取数据
 FUNCTION AccessNumber(self IN AnyData) return NUMBER ;
 FUNCTION AccessDate(self IN AnyData) return DATE ;
 FUNCTION AccessChar(self IN AnyData) return CHAR;
 FUNCTION AccessVarchar(self IN AnyData) return VARCHAR ;
 FUNCTION AccessVarchar2(self IN AnyData) return VARCHAR2 ;
 FUNCTION AccessBlob(self IN AnyData) return BLOB ;
 FUNCTION AccessClob(self IN AnyData) return CLOB ;
 FUNCTION AccessTimestamp(self IN AnyData) return TIMESTAMP ;
 FUNCTION AccessTimestampTZ(self IN AnyData) return timestamp with time zone ;
 FUNCTION AccessIntervalYM(self IN AnyData) return INTERVAL YEAR TO MONTH ;
 FUNCTION AccessIntervalDS(self IN AnyData) return INTERVAL DAY TO SECOND ;
 FUNCTION AccessNchar(self IN AnyData) return Nchar ;
 FUNCTION AccessNVarchar2(self IN AnyData) return NVARCHAR2 ;
 FUNCTION AccessRaw(self IN ANYDATA) return RAW;
 FUNCTION AccessBDouble(self IN AnyData) return BINARY_DOUBLE ;
 --FUNCTION AccessBFloat(self IN AnyData) return BINARY_FLOAT ;   ---Gauss不支持数据类型
 --FUNCTION AccessURowid(self IN ANYDATA) return UROWID DETERMINISTIC; ---Gauss不支持数据类型
 --FUNCTION AccessTimestampLTZ(self IN AnyData) return timestamp with local time zone ; ---Gauss不支持数据类型
 --FUNCTION AccessBfile(self IN AnyData) return BFILE ;  ---Gauss不支持数据类型
 --FUNCTION AccessNClob(self IN AnyData) return NCLOB ; ---Gauss不支持数据类型
--GET获取当前数据
FUNCTION GetBDouble(self IN ANYDATA,dbl OUT  BINARY_DOUBLE) RETURN INTEGER;
FUNCTION GetBlob(self IN ANYDATA,b OUT  BLOB) RETURN INTEGER;
FUNCTION GetChar(self IN ANYDATA,c   OUT  CHAR) RETURN INTEGER;
FUNCTION GetClob(self IN ANYDATA,c  OUT  CLOB)RETURN INTEGER;
FUNCTION GetDate(self IN ANYDATA,dat OUT  DATE)RETURN INTEGER;
FUNCTION GetIntervalDS(self IN ANYDATA,inv OUT  INTERVAL DAY TO SECOND) RETURN   INTEGER;
FUNCTION GetIntervalYM(self IN ANYDATA,inv OUT  INTERVAL YEAR TO MONTH) RETURN   INTEGER;
FUNCTION GetNchar(self IN ANYDATA, nc  OUT  NCHAR) RETURN   INTEGER;
FUNCTION GetNumber(self IN ANYDATA,num OUT  NUMBER)RETURN INTEGER;
FUNCTION GetNVarchar2(self IN ANYDATA, nc  OUT  NVARCHAR2) RETURN   INTEGER;
FUNCTION GetRaw(self IN ANYDATA,r  OUT  RAW)RETURN INTEGER;
FUNCTION GetTimestamp(self IN ANYDATA, ts  OUT  TIMESTAMP)RETURN INTEGER;
FUNCTION GetTimestampTZ(self IN ANYDATA, ts  OUT  TIMESTAMP WITH TIME ZONE) RETURN INTEGER;
FUNCTION GetVarchar(self IN ANYDATA,c  OUT  VARCHAR)RETURN INTEGER;
FUNCTION GetVarchar2(self IN ANYDATA,c  OUT  VARCHAR2)RETURN INTEGER;
--FUNCTION GetBfile( self IN ANYDATA, b  OUT  BFILE) RETURN  INTEGER;  ---Gauss不支持数据类型
--FUNCTION GetBFloat( self IN ANYDATA, fl OUT  BINARY_FLOAT) RETURN INTEGER;---Gauss不支持数据类型
--FUNCTION GetCollection(self IN ANYDATA,col OUT  "<collection_type>")RETURN INTEGER;---Gauss不支持数据类型
--FUNCTION GetObject(self IN ANYDATA,obj OUT  "<object_type>")RETURN INTEGER; object类型无法确定，无法直接改造
--FUNCTION GetRef(self IN ANYDATA,rf  OUT  REF "<object_type>")RETURN INTEGER;object类型无法确定，无法直接改造
--FUNCTION GetTimestampLTZ(self IN ANYDATA,ts  OUT  TIMESTAMP WITH LOCAL TIME ZONE) RETURN INTEGER; ---Gauss不支持数据类型
--FUNCTION GetNClob(self IN ANYDATA, nc  OUT  NCLOB) RETURN   INTEGER; ---Gauss不支持数据类型
END anydata;
/
--package body：anydata方法：仅处理兼容的普通数据类型，不兼容数据类型或者object不处理
CREATE OR REPLACE PACKAGE BODY public.anydata AS
 --GetTypeName类型检查
function gettypename(self IN ANYDATA) RETURN VARCHAR2 as
v_data VARCHAR2;
    begin
        v_data:=self.typename;
        return v_data;
    end ;
--Convert函数构造数据
FUNCTION ConvertNumber(num IN NUMBER) return AnyData
as
 v_data anydata;
begin
  v_data.typename := 'NUMBER';
  v_data.typename_value :=num;
  return v_data;
end;
 FUNCTION ConvertDate(dat IN DATE) return AnyData
 as
 v_data anydata;
begin
  v_data.typename := 'DATE';
  v_data.typename_value :=dat;
  return v_data;
end;
 FUNCTION ConvertChar(c IN CHAR) return AnyData
 as
 v_data anydata;
begin
  v_data.typename := 'CHAR';
  v_data.typename_value :=c;
  return v_data;
end;
 FUNCTION ConvertVarchar(c IN VARCHAR) return AnyData
  as
 v_data anydata;
begin
  v_data.typename := 'VARCHAR';
  v_data.typename_value :=c;
  return v_data;
end;
 FUNCTION ConvertVarchar2(c IN VARCHAR2) return AnyData
   as
 v_data anydata;
begin
  v_data.typename := 'VARCHAR2';
  v_data.typename_value :=c;
  return v_data;
end;
 FUNCTION ConvertBlob(b IN BLOB) return AnyData
    as
 v_data anydata;
begin
  v_data.typename := 'BLOB';
  v_data.typename_value :=b;
  return v_data;
end;
 FUNCTION ConvertClob(c IN CLOB) return AnyData
     as
 v_data anydata;
begin
  v_data.typename := 'CLOB';
  v_data.typename_value :=c;
  return v_data;
end;
 FUNCTION ConvertTimestamp(ts IN TIMESTAMP) return AnyData
   as
 v_data anydata;
begin
  v_data.typename := 'TIMESTAMP';
  v_data.typename_value :=ts;
  return v_data;
end;
 FUNCTION ConvertTimestampTZ(ts IN timestamp with time zone) return AnyData
    as
 v_data anydata;
begin
  v_data.typename := 'timestamp with time zone';
  v_data.typename_value :=ts;
  return v_data;
end;
 FUNCTION ConvertIntervalYM(inv IN INTERVAL YEAR TO MONTH) return AnyData
     as
 v_data anydata;
begin
  v_data.typename := 'INTERVAL YEAR TO MONTH';
  v_data.typename_value :=inv;
  return v_data;
end;
 FUNCTION ConvertIntervalDS(inv IN INTERVAL DAY TO SECOND) return AnyData
      as
 v_data anydata;
begin
  v_data.typename := 'INTERVAL DAY TO SECOND';
  v_data.typename_value :=inv;
  return v_data;
end;
 FUNCTION ConvertNchar(nc IN NCHAR) return AnyData
       as
 v_data anydata;
begin
  v_data.typename := 'NCHAR';
  v_data.typename_value :=nc;
  return v_data;
end;
 FUNCTION ConvertNVarchar2(nc IN NVARCHAR2) return AnyData
      as
 v_data anydata;
begin
  v_data.typename := 'NVARCHAR2';
  v_data.typename_value :=nc;
  return v_data;
end;
 FUNCTION ConvertRaw(r IN RAW) RETURN ANYDATA
       as
 v_data anydata;
begin
  v_data.typename := 'RAW';
  v_data.typename_value :=r;
  return v_data;
end;
 FUNCTION ConvertBDouble(dbl IN BINARY_DOUBLE) return AnyData
       as
 v_data anydata;
begin
  v_data.typename := 'BINARY_DOUBLE';
  v_data.typename_value :=dbl;
  return v_data;
end;
--Access获取数据
 FUNCTION AccessNumber(self IN AnyData) return NUMBER 
 as
 v_data number;
 begin
 if self.typename <> 'NUMBER' then
  return null;
  else 
  v_data:=self.typename_value;
  end if;
  return v_data;
 end;
 FUNCTION AccessDate(self IN AnyData) return DATE 
 as
 v_data DATE;
 begin
 if self.typename <> 'DATE' then
  return null;
  else 
  v_data:=self.typename_value;
    end if;
  return v_data;
 end;
 FUNCTION AccessChar(self IN AnyData) return CHAR
 as
 v_data CHAR;
 begin
 if self.typename <> 'CHAR' then
   return null;
  else 
  v_data:=self.typename_value;
    end if;
  return v_data;
 end;
 FUNCTION AccessVarchar(self IN AnyData) return VARCHAR 
  as
 v_data VARCHAR;
 begin
   if self.typename <> 'VARCHAR' then
   return null;
  else 
  v_data:=self.typename_value;
  end if;
  return v_data;
 end;
 FUNCTION AccessVarchar2(self IN AnyData) return VARCHAR2 
   as
 v_data VARCHAR2;
 begin
 if self.typename <> 'VARCHAR2' then
  return null;
  else 
  v_data:=self.typename_value;
    end if;
  return v_data;
 end;
 FUNCTION AccessBlob(self IN AnyData) return BLOB 
    as
 v_data BLOB;
 begin
 if self.typename <> 'BLOB' then
  return null;
  else 
  v_data:=self.typename_value;
    end if;
  return v_data;
 end;
 FUNCTION AccessClob(self IN AnyData) return CLOB 
    as
 v_data CLOB;
 begin
 if self.typename <> 'CLOB' then
  return null;
  else 
  v_data:=self.typename_value;
    end if;
  return v_data;
 end;
 FUNCTION AccessTimestamp(self IN AnyData) return TIMESTAMP 
    as
 v_data TIMESTAMP;
 begin
 if self.typename <> 'TIMESTAMP' then
  return null;
  else 
  v_data:=self.typename_value;
    end if;
  return v_data;
 end;
FUNCTION AccessTimestampTZ(self IN AnyData) return timestamp with time zone 
    as
 v_data timestamp with time zone ;
 begin
 if self.typename <> 'TIMESTAMP WITH TIME ZONE' then
  return null;
  else 
  v_data:=self.typename_value;
    end if;
  return v_data;
 end;
FUNCTION AccessIntervalYM(self IN AnyData) return INTERVAL YEAR TO MONTH 
  as
 v_data INTERVAL YEAR TO MONTH ;
 begin
 if self.typename <> 'INTERVAL YEAR TO MONTH' then
  return null;
  else 
  v_data:=self.typename_value;
    end if;
  return v_data;
 end;
FUNCTION AccessIntervalDS(self IN AnyData) return INTERVAL DAY TO SECOND 
  as
 v_data INTERVAL DAY TO SECOND ;
 begin
 if self.typename <> 'INTERVAL DAY TO SECOND' then
  return null;
  else 
  v_data:=self.typename_value;
    end if;
  return v_data;
 end;
FUNCTION AccessNchar(self IN AnyData) return NCHAR 
  as
 v_data NCHAR;
 begin
 if self.typename <> 'NCHAR' then
  return null;
  else 
  v_data:=self.typename_value;
    end if;
  return v_data;
 end;
FUNCTION AccessNVarchar2(self IN AnyData) return NVARCHAR2 
  as
 v_data NVARCHAR2;
 begin
 if self.typename <> 'NVARCHAR2' then
  return null;
  else 
  v_data:=self.typename_value;
    end if;
  return v_data;
 end;
 FUNCTION AccessRaw(self IN ANYDATA) return RAW
 as
 v_data RAW;
 begin
 if self.typename <> 'RAW' then
  return null;
  else 
  v_data:=self.typename_value;
    end if;
  return v_data;
 end;
 FUNCTION AccessBDouble(self IN AnyData) return BINARY_DOUBLE 
 as
 v_data BINARY_DOUBLE;
 begin
 if self.typename <> 'BINARY_DOUBLE' then
  return null;
  else 
  v_data:=self.typename_value;
   end if;
  return v_data;
 end;
 --GET获取当前数据
FUNCTION GetBDouble(self IN ANYDATA,dbl OUT  BINARY_DOUBLE) RETURN INTEGER
 as
 begin
 if self.typename <> 'BINARY_DOUBLE' then
  return null;
  else 
  dbl:=self.typename_value;
   end if;
  return 0;
 end;
FUNCTION GetBlob(self IN ANYDATA,b OUT  BLOB) RETURN INTEGER
as
 begin
 if self.typename <> 'BLOB' then
  return null;
  else 
  b:=self.typename_value;
   end if;
  return 0;
 end;
FUNCTION GetChar(self IN ANYDATA,c   OUT  CHAR) RETURN INTEGER
as
 begin
 if self.typename <> 'CHAR' then
  return null;
  else 
  c:=self.typename_value;
   end if;
  return 0;
 end;
FUNCTION GetClob(self IN ANYDATA,c  OUT  CLOB)RETURN INTEGER
as
 begin
 if self.typename <> 'CLOB' then
  return null;
  else 
  c:=self.typename_value;
   end if;
  return 0;
 end;
FUNCTION GetDate(self IN ANYDATA,dat OUT  DATE)RETURN INTEGER
as
 begin
 if self.typename <> 'DATE' then
  return null;
  else 
  dat:=self.typename_value;
   end if;
  return 0;
 end;
FUNCTION GetIntervalDS(self IN ANYDATA,inv OUT  INTERVAL DAY TO SECOND) RETURN   INTEGER
as
 begin
 if self.typename <> 'INTERVAL DAY TO SECOND' then
  return null;
  else 
  inv:=self.typename_value;
   end if;
  return 0;
 end;
FUNCTION GetIntervalYM(self IN ANYDATA,inv OUT  INTERVAL YEAR TO MONTH) RETURN   INTEGER
as
 begin
 if self.typename <> 'INTERVAL YEAR TO MONTH' then
  return null;
  else 
  inv:=self.typename_value;
   end if;
  return 0;
 end;
FUNCTION GetNchar(self IN ANYDATA, nc  OUT  NCHAR) RETURN   INTEGER
as
 begin
 if self.typename <> 'NCHAR' then
  return null;
  else 
  nc:=self.typename_value;
   end if;
  return 0;
 end;
FUNCTION GetNumber(self IN ANYDATA,num OUT  NUMBER)RETURN INTEGER
as
 begin
 if self.typename <> 'NUMBER' then
  return null;
  else 
  num:=self.typename_value;
   end if;
  return 0;
 end;
FUNCTION GetNVarchar2(self IN ANYDATA, nc  OUT  NVARCHAR2) RETURN   INTEGER
as
 begin
 if self.typename <> 'NVARCHAR2' then
  return null;
  else 
  nc:=self.typename_value;
   end if;
  return 0;
 end;
FUNCTION GetRaw(self IN ANYDATA,r  OUT  RAW)RETURN INTEGER
as
 begin
 if self.typename <> 'RAW' then
  return null;
  else 
  r:=self.typename_value;
   end if;
  return 0;
 end;
FUNCTION GetTimestamp(self IN ANYDATA, ts  OUT  TIMESTAMP)RETURN INTEGER
as
 begin
 if self.typename <> 'TIMESTAMP' then
  return null;
  else 
  ts:=self.typename_value;
   end if;
  return 0;
 end;
FUNCTION GetTimestampTZ(self IN ANYDATA, ts  OUT  TIMESTAMP WITH TIME ZONE) RETURN INTEGER
as
 begin
 if self.typename <> 'TIMESTAMP WITH TIME ZONE' then
  return null;
  else 
  ts:=self.typename_value;
   end if;
  return 0;
 end;
FUNCTION GetVarchar(self IN ANYDATA,c  OUT  VARCHAR)RETURN INTEGER
as
 begin
 if self.typename <> 'VARCHAR' then
  return null;
  else 
  c:=self.typename_value;
   end if;
  return 0;
 end;
FUNCTION GetVarchar2(self IN ANYDATA,c  OUT  VARCHAR2)RETURN INTEGER
as
 begin
 if self.typename <> 'VARCHAR2' then
  return null;
  else 
  c:=self.typename_value;
   end if;
  return 0;
 end;
END anydata;
/
--table
CREATE TABLE t1( c1 anydata);

示例2：复合类型以及构造方法改写建议

1、anydata改写为同名的type(typename_value的类型为object)+package(gettypename,ConvertObject,GetObject)+package body(gettypename,ConvertObject,GetObject),并且创建到public下面；

2、转换后删除sys.；

3、v_anydata.GetObject(v_person)改写为anydata.GetObject(v_anydata,v_person)；

4、DBMS_TYPES.SUCCESS改为0。

GaussDB改写后：

-- 创建自定义对象类型
CREATE TYPE person_obj AS  (
   id NUMBER,
   name VARCHAR2(100),
   birthdate DATE
);
--创建一个anydata type
drop type public.anydata;
CREATE  TYPE public.anydata AS  (
 typename varchar2,  
 typename_value      person_obj
);
--创建一个package ：anydata方法，构造object类型
CREATE OR REPLACE PACKAGE public.anydata AS
 ---gettypename
 FUNCTION gettypename(self IN ANYDATA) RETURN       VARCHAR2;
 ---ConvertObject
 FUNCTION ConvertObject(obj  IN person_obj) return AnyData;
 ---GetObject
 FUNCTION GetObject(self IN AnyData, obj OUT person_obj ) return INTEGER ;
 END anydata;
/
--创建一个 package body：anydata方法，构造object类型
CREATE OR REPLACE PACKAGE BODY public.anydata AS
 --gettypename
function gettypename(self IN ANYDATA) RETURN VARCHAR2 as
v_data VARCHAR2;
    begin
        v_data:=self.typename;
        return v_data;
    end ;
  --ConvertObject
FUNCTION ConvertObject(obj  IN person_obj) return AnyData
as
 v_data anydata;
begin
  v_data.typename := 'person_obj';
  v_data.typename_value :=obj;
  return v_data;
end;
--GetObject
 FUNCTION GetObject(self IN AnyData, obj OUT person_obj ) return INTEGER
 as
 begin
 if self.typename <> 'person_obj' then
  return null;
  else 
  obj:=self.typename_value;
   end if;
  return 0;
 end;
END anydata;
/
--创建表
drop table anydata_table;
create table anydata_table(name anydata);
-- 使用ConvertObject
DECLARE
   v_person person_obj := person_obj(1, 'John Doe', TO_DATE('1980-01-15', 'YYYY-MM-DD'));
   v_anydata ANYDATA;
BEGIN
   v_anydata := ANYDATA.ConvertObject(v_person);
   INSERT INTO anydata_table VALUES (v_anydata);
   COMMIT;
END;
/
--查看GetObject
DECLARE
   v_anydata ANYDATA;
   v_person person_obj;
   v_status NUMBER;
BEGIN
   -- 从表中获取ANYDATA数据
   SELECT name INTO v_anydata FROM anydata_table;
   -- 转换回对象
   v_status := anydata.GetObject(v_anydata,v_person);
   IF v_status = 0 THEN
  DBE_OUTPUT.PRINT_LINE('ID: ' || v_person.id);
      DBE_OUTPUT.PRINT_LINE('Name: ' || v_person.name);
      DBE_OUTPUT.PRINT_LINE('Birthdate: ' || TO_CHAR(v_person.birthdate, 'YYYY-MM-DD'));
   ELSE
      DBE_OUTPUT.PRINT_LINE('Error converting object: ' || v_status);
   END IF;
END;
/

父主题： Oracle To GaussDB 转换错误码

上一篇：U0100085：GaussDB不支持TABLE函数中使用子查询

下一篇：U0100087：GaussDB中DBE_UTILITY.GET_TIME返回值精度与Oracle存在差异