使用VARCHAR2列中的语句进行查询 [英] Query using a statement within a VARCHAR2 column

问题描述

有没有一种select语句可以在表中包含的 WHERE 子句中包含一条语句?例如，下表:

Is there a way for a select statement to include in the WHERE clause a statement that is contained within the table? For example, the following table:

CREATE TABLE test_tab(
    date_column DATE,
    frequency NUMBER,
    test_statement VARCHAR2(255)
)
/

如果

MOD(SYSDATE-DATE，频率)= 0

包含在 test_statement 列中，有没有一种方法可以选择正确的行? test_statement 将有所不同，并且在整个表格中都不相同.我可以在PL/SQL中做到这一点，但希望在不使用PL/SQL的情况下做到这一点.

were contained within the column test_statement, is there a way to select rows where this is true? The test_statement will vary and not be the same throughout the table. I am able to do this in PL/SQL but looking to do this without the use of PL/SQL.

推荐答案

这种SQL中的动态SQL可以使用 DBMS_XMLGEN.getXML 创建.尽管查询看起来有些奇怪，所以您可能需要考虑采用其他设计.

This kind of dynamic SQL in SQL can created with DBMS_XMLGEN.getXML. Although the query looks a bit odd so you might want to consider a different design.

首先，我使用您的DDL创建了一个示例表和行.我不确定您要使用条件做什么，因此我将条件简化为两行.第一行与第一个条件匹配，而第二行都不与第二个条件匹配.

First, I created a sample table and row using your DDL. I'm not sure exactly what you're trying to do with the conditions, so I simplified them into two rows with simpler conditions. The first row matches the first condition, and neither row matches the second condition.

--Create sample table and row that matches the condition.
CREATE TABLE test_tab(
    date_column DATE,
    frequency NUMBER,
    test_statement VARCHAR2(255)
)
/

insert into test_tab values(sysdate, 1, 'frequency = 1');
insert into test_tab values(sysdate, 2, '1=2');
commit;

这是大查询，它仅返回第一行，该行仅与第一个条件匹配.

Here's the large query, and it only returns the first row, which only matches the first condition.

--Find rows where ROWID is in a list of ROWIDs that match the condition.
select *
from test_tab
where rowid in
(
    --Convert XMLType to relational data.
    select the_rowid
    from
    (
        --Convert CLOB to XMLType.
        select xmltype(xml_results) xml_results
        from
        (
            --Create a single XML file with the ROWIDs that match the condition.
            select dbms_xmlgen.getxml('
                select rowid
                from test_tab where '||test_statement) xml_results
            from test_tab
        )
        where xml_results is not null
    )
    cross join
    xmltable
    (
        '/ROWSET/ROW'
        passing xml_results
        columns
            the_rowid varchar2(128) path 'ROWID'
    )
);

这篇关于使用VARCHAR2列中的语句进行查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！