Oracle视图或表函数返回在另一个表中存储为文本的查询联合的函数

Question

假设我有一个“规则”表，该表具有一个列，该列包含oracle查询中的varchar2列中：

行	查询
1	选择'hardcoded_tablename_1'tablename，request_id table_1，其中state＆gt; = 4 and status_date_date＆lt; *一些日期数学到达这里*
2	选择'table_2'tableName，t2.request_id table_2 t2 t2 t2 t2 table_1 t1 on t1.request_id = t2.parent_id whend t1.status＆gt; = 4 and t1.resolve_date_date_date_date_date_date_date_date_date_date_date_date_date_date_date ＆lt; *一些日期数学到达这里*

如果这永远不会改变，我只会通过这些查询的结合来查看。 我们的要求是，我们能够以领导力的兴趣添加或修改这些规则。

因此，我需要的是：

• 一个非常聪明的视图（我认为不可能）执行和工会所有这些存储的查询字符串

或

• 表函数，该函数返回这些存储的查询字符串的结合结果。 （我认为这是更有可能的解决方案）

它只会是这两个列：表格的硬编码名称和记录的ID。

有人可以帮我开始这个吗？

谢谢

Answer 1

您可以使用管道函数。

首先创建类型：

CREATE TYPE request_data IS OBJECT (tablename VARCHAR2(30), request_id NUMBER);

CREATE TYPE request_list IS TABLE OF request_data;

然后函数：

CREATE FUNCTION get_requests RETURN request_list PIPELINED
IS
BEGIN
  FOR r IN (SELECT "QUERY" FROM table_name ORDER BY "ROW")
  LOOP
    DECLARE
      c_cursor     SYS_REFCURSOR;
      v_tablename  VARCHAR2(30);
      v_request_id NUMBER;
    BEGIN
      OPEN c_cursor FOR r."QUERY";
      
      LOOP
        FETCH c_cursor INTO v_tablename, v_request_id;
        EXIT WHEN c_cursor%NOTFOUND;
        PIPE ROW (request_data(v_tablename, v_request_id));
      END LOOP;
      
      CLOSE c_cursor;
    EXCEPTION
      WHEN NO_DATA_NEEDED THEN
        CLOSE c_cursor;
        RETURN;
    END;
  END LOOP;
END;
/

然后，如果您有示例数据：

CREATE TABLE table_name ("ROW", "QUERY") AS
SELECT 1, q'[select 'Hardcoded_Tablename_1' tablename, request_id from table_1 where status >= 4 and resolve_date < SYSDATE]' FROM DUAL UNION ALL
SELECT 2, q'[select 'Table_2' tablename, t2.request_id from table_2 t2 join table_1 t1 on t1.request_id = t2.parent_id where t1.status >= 4 and t1.resolve_date < SYSDATE]' FROM DUAL

CREATE TABLE table_1 (request_id, status, resolve_date) AS
SELECT 42, 4, SYSDATE - 1 FROM DUAL;

CREATE TABLE table_2 (request_id, parent_id) AS
SELECT 57, 42 FROM DUAL;

那么您可以在表集合表达式中使用该函数：

SELECT *
FROM   TABLE(get_requests());

输出：

tablename	request_id
hardcoded_tablename_1	42
table_2	57

db＆lt;＆gt; >在这里

Answer 2

一个选项可能是返回避难所的函数。

SQL> select * from rules;

      CROW QUERY
---------- ----------------------------------------------------------------------------------------------------
         1 select 'EMP' tablename, empno from emp where hiredate = (select max(hiredate) from emp)
         2 select 'DEPT' tablename, d.deptno from emp e join dept d on d.deptno = e.deptno where e.hiredate = (
           select min(hiredate) from emp)

函数从规则>表中创建所有查询的联合，并将其用作反驳器的 source ：

SQL> create or replace function f_test return sys_refcursor
  2  is
  3    l_rc   sys_refcursor;
  4    l_str  clob;
  5  begin
  6    for cur_r in (select query from rules order by crow) loop
  7      l_str := l_str || cur_r.query ||' union all ';
  8    end loop;
  9    l_str := rtrim(l_str, ' union all ');
 10
 11    open l_rc for l_str;
 12    return l_rc;
 13  end;
 14  /

Function created.

测试：

SQL> select f_test from dual;

F_TEST
--------------------
CURSOR STATEMENT : 1

CURSOR STATEMENT : 1

TABL      EMPNO
---- ----------
EMP        7876
DEPT         20


SQL>