oracle - 在 Oracle 中，如何验证对象类型层次结构中使用的对象类型？

Question

我在 Oracle 架构中有一个类型层次结构:

CREATE OR REPLACE TYPE FV AS OBJECT (
   idno           NUMBER)
NOT FINAL;
/

CREATE TYPE FV_Integer UNDER FV (
   features INTEGER_ARRAY)
   NOT FINAL;
/


CREATE TYPE FV_Number UNDER FV (
   features NUMBER_ARRAY)
   NOT FINAL;
/

我想构建一个 PLSQL 函数来验证哪种类型的层次结构是对象:对于函数 dummy(obj1 FV, obj2 FV)... 我如何检查用户是什么层次结构的对象类型正在使用？

例如我要打印对象类型名称(此功能仅供引用，并非真正的pl/sql代码):

 dummy(obj1 FV, obj2 FV){
      if obj1%type = FV_INTEGER
          THEN print 'FV_INTEGER'
      endif
      if obj2%type = FV_NUMBER
          THEN print 'FV_NUMBER'
      endif
}

Answer 1

您可以使用 sys.anydata 检查对象的类型:

create or replace function which_type
    ( p_fv fv )
    return varchar2
as
begin
    return sys.anydata.gettypename(sys.anydata.convertobject(p_fv));
end which_type;

测试:

create or replace type number_array as table of number;
create or replace type integer_array as table of integer;

create or replace type fv as object (
   idno           number)
not final;
/

create type fv_integer under fv (
   features integer_array)
   not final;
/

create type fv_number under fv (
   features number_array)
   not final;
/

create table fv_test (my_fv fv);

insert into fv_test values (fv(1));
insert into fv_test values (fv_integer(1, integer_array(1)));
insert into fv_test values (fv_number(1, number_array(1)));

select which_type(my_fv) from fv_test;

WHICH_TYPE(MY_FV)
-------------------------
WILLIAM.FV
WILLIAM.FV_INTEGER
WILLIAM.FV_NUMBER

3 rows selected.