使用动态表名查询 Oracle 数据库 [英] Querying an Oracle Database with Dynamic Table names

问题描述

我被一些糟糕的数据库设计困住了，我不得不查询按日期命名的表.

I'm stuck with some poor database design where I have to query tables that are named by date.

当表名与相关日期硬编码时，以下查询有效.

The following query works when the table names are hard coded with relevant dates.

SELECT
  ajob.ORDER_ID
     , ajob.JOB_NAME
     , abim.SERVICE_ID
     , shist.SERVICE_NAME
  FROM
    obscuredschema.A190129001_AJOB               ajob --hardcoded YYMMDD table name
    INNER JOIN obscuredschema.A190129001_ABIMSVC abim --hardcoded YYMMDD table name
               ON (ajob.ORDER_ID = abim.ORDER_ID)
    INNER JOIN obscuredschema.SERVICE_HIST       shist
               ON (abim.SERVICE_ID = shist.SERVICE_KEY)
WHERE shist.SERVICE_NAME LIKE '%BIM'
  AND shist.BIM_AUTH_ID > 0
;

注意两个硬编码的表名(以及别名)

Noting the two hardcoded table names (along with aliases)

如何使用动态表名执行相同的查询?(有两个)

动态日期的代码:TO_CHAR(trunc(sysdate - 7), 'YYMMDD')

如果第一个表名是一个字符串，我将如何构建它:'A'||TO_CHAR(trunc(sysdate - 7), 'YYMMDD')||'001_AJOB'

If the first table name were a string, here's how I would build it: 'A'||TO_CHAR(trunc(sysdate - 7), 'YYMMDD')||'001_AJOB'

如果第二个表名是一个字符串，我将如何构建它:'A'||TO_CHAR(trunc(sysdate - 7), 'YYMMDD')||'001_ABIMSVC'

If the second table name were a string, here's how I would build it: 'A'||TO_CHAR(trunc(sysdate - 7), 'YYMMDD')||'001_ABIMSVC'

推荐答案

我认为您无法使用动态表名编写简单的 SQL 查询.

I don't think you can write a plain SQL query with dynamic table names.

您可以编写一个使用立即执行并返回游标或其他东西的PL/SQL过程；昨天有人问过这个问题.如果您只是想编写此查询来与某些数据进行交互，那么这可能是您最好的选择.

You can write a PL/SQL procedure which uses execute immediate and returns a cursor or something; somebody asked about that just yesterday. If you're just trying to write this query to interact with some data, that might be your best bet.

此外，您可以通过将 PL/SQL 过程转换为 流水线函数来修改它，然后您可以使用 TABLE() 从 SQL 查询中调用它.

In addition, you could modify that by turning your PL/SQL procedure into a pipelined function, and then you could call it from a SQL query using TABLE().

如果是我，我会考虑创建同义词(或仅从动态命名表中选择的标准视图)，并在每次创建新表时安排作业重新创建它.这可能比处理流水线函数更简单.

If it were me, I'd consider creating a synonym (or a standard view which just selects from the dynamically-named-tables), and scheduling a job to re-create it every time new tables are created. That might be simpler than dealing with pipelined functions.

这篇关于使用动态表名查询 Oracle 数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！