如何在Oracle Apex中检索递归信息

Question

在我的Apex应用程序中，可以设置用于创建图表的过滤器。如果用户愿意，他可以选择几个系统。这些系统被创建为复选框项目。复选框项的来源基于一个值列表，该列表包含一个SQL查询，用于从我的数据库表中检索所有系统。以下是我的数据库表的结构：

CREATE TABLE system_table (
system_id                  NUMBER(16) NOT NULL,
system_name                VARCHAR2(100 CHAR) NOT NULL,
system_table_uebergeordnet_ID NUMBER(16),
CONSTRAINT system_name_unique UNIQUE(system_name),
CONSTRAINT system_table_pk PRIMARY KEY ( system_id ),
CONSTRAINT system_table_uber_id_fk FOREIGN KEY ( system_table_uebergeordnet_ID )
REFERENCES system_table ( system_id )   );

正如您在代码中看到的，最后一个属性是递归关系。我现在想要的是：如果用户选择系统名称并提交页面，则应选择系统名称和引用所选系统的所有其他系统。因此，我创建了一个名为System_ID的隐藏项。在提交页面之前，我定义了一个动态操作，它会获取所选系统名称的id。提交页面后，我的图表被创建，我正在检查where子句中的条件。看起来是这样的：

where ((instr(':' || upper(:P26_SYSTEMS) || ':', upper(SYSTEM_TABLE.SYSTEM_NAME)) > 0) or (instr(':' || upper(:P26_SYSTEMS_ALL) || ':', upper(SYSTEM_TABLE.SYSTEM_NAME)) > 0) or (SYSTEM_TABLE.SYSTEM_TABLE_UEBERGEORDNET_ID = :P26_SYSTEM_ID))

到目前为止，该查询正在运行，但只选择所选的系统名称，而不选择引用所选系统的系统。我希望我能解释这个问题，你们也能理解。你们中有人知道我做错了什么吗？

以下代码显示了我的查询：

select COUNT(TRIGGER_TABLE.DATUM_UHRZEIT) as Anzahl_Trigger,
TEST.NUMMER as NUMMER
from BRIDGE_SYSTEM_TRIGGER, SYSTEM_TABLE, TRIGGER_TABLE, FAHRT, TEST, MITARBEITER
where BRIDGE_SYSTEM_TRIGGER.SYSTEM_TABLE_SYSTEM_ID = SYSTEM_TABLE.SYSTEM_ID
and BRIDGE_SYSTEM_TRIGGER.TRIGGER_TABLE_TRIGGER_ID = TRIGGER_TABLE.TRIGGER_ID
and TRIGGER_TABLE.FAHRT_FAHRT_ID = FAHRT.FAHRT_ID
and MITARBEITER.NUMMER = FAHRT.MITARBEITER_NUMMER
and FAHRT.TEST_ID= TEST_ID
and TRIGGER_TABLE.PRIORITAET = 1
and ((instr(':' || upper(:P26_TEST) || ':', upper(TEST.TEST_ID)) > 0) or (instr(':' || upper(:P26_TEST_ALL) || ':', upper(TEST.TEST_ID)) > 0))
and ((instr(':' || upper(:P26_SYSTEMS) || ':',':' || upper(system_table.system_name) ||':') > 0)  or (instr(':' || upper(:P26_SYSTEMS_ALL) || ':', upper(SYSTEM_TABLE.SYSTEM_NAME)) > 0) or exists (select child.system_id from system_table child where  instr(':' || upper(:P26_SYSTEMS) ||':',':'|| upper(child.system_name) ||':') > 0 and child.system_table_uebergeordnet_id = system_table.system_id))
and ((instr(':' || upper(:P26_COUNTRIES) || ':', upper(FAHRT.LAND)) > 0) or (instr(':' || upper(:P26_COUNTRIES_ALL) || ':', upper(FAHRT.LAND)) > 0))
and ((instr(':' || upper(:P26_FAHRER) || ':', upper(MITARBEITER.QNUMMER)) > 0) or (instr(':' || upper(:P26_FAHRER_ALL) || ':', upper(MITARBEITER.QNUMMER)) > 0))
GROUP BY TEST.NUMMMER
ORDER BY TEST.NUMMER;

该查询统计按Testnumbers排序的每个优先级的触发器数这里的触发器与sql触发器无关！所以请不要对这个术语感到困惑一个"Fahrt"属于一个"Test"，一个"Test"可以包含几个"Fahrten"。此外，每个"Fahrt"都包含多个触发器。最后四个条件是所提到的检索已设置的过滤器信息的条件，并且只计算那些满足过滤器条件的触发器。

Answer 1

可以使用分层查询来查询分层数据结构。在Oracle中，您可以使用类似的东西(注意：未测试(：

select ...
from system_table
where instr(':'||upper(:P26_SYSTEMS)||':'
,':'||upper(system_table.system_name)||':') > 0
or system_table.system_id in (
select parent.system_id
from   system_table parent
start with instr(':'||upper(:P26_SYSTEMS)||':'
,':'||upper(parent.system_name)||':') > 0
connect by prior parent.system_table_uebergeordnet_id = parent.system_id
)

此层次查询从选定的系统名称开始，并在层次结构中向上移动到其父母、祖父母等，直至顶部。生成的一组系统ID随后用于过滤主表。

如果您需要它来选择所选系统的任何子系统，请切换connect by子句，例如

select ...
from system_table
where instr(':'||upper(:P26_SYSTEMS)||':'
,':'||upper(system_table.system_name)||':') > 0
or system_table.system_id in (
select parent.system_id
from   system_table parent
start with instr(':'||upper(:P26_SYSTEMS)||':'
,':'||upper(parent.system_name)||':') > 0
connect by prior parent.system_id = parent.system_table_uebergeordnet_id
)

第三方面，如果不是多级层次结构，则可以简化查询，例如

select ...
from system_table
where instr(':'||upper(:P26_SYSTEMS)||':'
,':'||upper(system_table.system_name)||':') > 0
or exists (
select child.system_id
from   system_table child
where  instr(':'||upper(:P26_SYSTEMS)||':'
,':'||upper(parent.system_name)||':') > 0
and    child.system_table_uebergeordnet_id = system_table.system_id
)