是否有一个PL/SQL函数可以计算一个超类型的n个子类型的数量?

问题描述：

PL/SQL中是否有一个函数可以计算表的n个子类型(超类型)的数量?

Is there a function in PL/SQL that can count the number of n subtypes of a table (supertype)?

我尝试了以下操作:

select
    count(distinct value(t1))
from table t1;

select
    count(distinct treat(value(t1))
from table t1;

基本上，如果一个表有6个子类型，我想要一个可以输出6的查询.

Basically, if a table has 6 subtypes, I would like a query that could output 6.

答

Oracle安装程序:

CREATE TYPE parent_type AS OBJECT( id NUMBER ) NOT FINAL;
CREATE TYPE child1_type UNDER parent_type ( c1 NUMBER );
CREATE TYPE child2_type UNDER parent_type ( c2 NUMBER ) NOT FINAL;
CREATE TYPE child3_type UNDER child2_type ( c3 NUMBER );

CREATE TABLE test_data ( value parent_type );

INSERT INTO test_data ( value )
  SELECT parent_type( 1 ) FROM DUAL UNION ALL
  SELECT child1_type( 2, 1 ) FROM DUAL UNION ALL
  SELECT child1_type( 3, 1 ) FROM DUAL UNION ALL
  SELECT child1_type( 4, 1 ) FROM DUAL UNION ALL
  SELECT child2_type( 5, 2 ) FROM DUAL UNION ALL
  SELECT child2_type( 6, 2 ) FROM DUAL UNION ALL
  SELECT child3_type( 7, 3, 1 ) FROM DUAL UNION ALL
  SELECT child3_type( 8, 3, 1 ) FROM DUAL UNION ALL
  SELECT child3_type( 9, 3, 1 ) FROM DUAL;

查询1 :

如果您知道类型层次结构，则可以使用以下事实手动构建查询:TREAT(对象AS类型)将在您传递的对象为NULL时返回 NULL .可以使用 CASE 语句，并从层次结构树的叶子开始，并处理从最深的继承深度到父类型的类型:

If you know the type hierarchy then you can build a query manually using the fact that TREAT( object AS type ) will return NULL if the object you are passing is not of that type and can use a CASE statement and start from the leaves of the hierarchy tree and process the types from deepest inheritance depth through to the parent type:

SELECT COUNT( DISTINCT
         CASE
         WHEN TREAT( value AS child3_type ) IS NOT NULL
           THEN 'child3_type' -- deepest subtype
         WHEN TREAT( value AS child2_type ) IS NOT NULL
           THEN 'child2_type' -- supertype of child3_type, subtype of parent_type
         WHEN TREAT( value AS child1_type ) IS NOT NULL
           THEN 'child1_type' -- subtype of parent_type
         ELSE 'parent_type'
         END
       ) AS num_types
FROM   test_data

查询2 :

如果您有权访问 SYS.ANYDATA 类型，那么您可以获取对象的类型:

SELECT COUNT(
         DISTINCT
         SYS.ANYDATA.getTypeName(
           SYS.ANYDATA.convertObject( value )
         )
       ) AS num_types
FROM   test_data

输出:

两者都给出相同的输出:

Both give the same output:


| NUM_TYPES |
| --------: |
|         4 |

db<>小提琴此处

是否有一个PL/SQL函数可以计算一个超类型的n个子类型的数量?

相关推荐