是否有一个PL/SQL函数可以计算一个超类型的n个子类型的数量? [英] Is there a PL/SQL function to count the number of n subtypes of a super type?
问题描述
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个子类型的数量?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!