时间戳

首页 > 解决方案 > 在 FROM 和 WHERE 子句中评估用户定义的变量（Oracle DEFINE 命令）

sql - 在 FROM 和 WHERE 子句中评估用户定义的变量（Oracle DEFINE 命令）

问题描述

我想使用DEFINEOracle 中的命令来定义字符串并在查询的SELECT,FROM和WHERE子句中评估它们。

我想从 、 、 、 和 表中选择 2018 年 1credits_201801月credits_201802开始credits_201803的credits_201804所有credits_201805学分credits_201806。换句话说，我想可视化 2018 年 1 月学分开始后 0、1、2、3、4 和 5 个月的行为。

例如，我尝试了以下代码来检查Jan 2018学分的状态Feb 2018：

DEFINE crop   = '2018-01-01';
DEFINE crop_n = ADD_MONTHS(&crop, 1); -- I want this to return 2018-01-01 plus `n = 1` months
DEFINE table_ = REPLACE(SUSBTR(&crop_n, 1, 7), '-'); -- I want this to return '201802'

SELECT
    PERSON_ID,
    BANK_ID,
    ACCOUNT_ID,
    START_DATE,
    MONTHS_BETWEEN(&crop_n, &crop) AS PERIOD,
    DAYS_OVERDUE
FROM
    'MDB.CREDITS_' || &table_ -- I want this to be evaluated as 'MDB.CREDITS_201802'
WHERE
    START_DATE >= &crop -- Credits that started on or after Jan 1 2018
    AND START_DATE < ADD_MONTHS(&crop, 1) -- Credits that started before Feb 1 2018

用户定义的变量在SELECT子句中运行良好，但似乎该FROM部分将表作为文字字符串而不是表的名称来评估。该WHERE条款也不起作用。

我特别需要使用DEFINE它，因为它是我所知道的唯一不需要我使用的命令INSERT INTO（我只能选择）。

标签： sqloracle

解决方案

日期是写的date '2018-01-01'，不是'2018-01-01'。

define需要一个值，因此如果您希望它包含空格，那么您需要引用它。

要从日期构造YYYYMM格式字符串，您需要使用to_char(dateval,'YYYYMM').

您还需要评估table_表达式，以便可以将文字字符串替换为最终查询中的表名。

define crop   = "date '2018-01-01'"
define crop_n = "add_months(&crop, 1)" -- I want this to return 2018-01-01 plus `n = 1` months

column table_ new_value table_

select to_char(&crop_n,'YYYYMM') as table_ from dual;

prompt crop   = &crop
prompt crop_n = &crop_n
prompt table_ = &table_

select person_id
     , bank_id
     , account_id
     , start_date
     , months_between(&crop_n, &crop) as period
     , days_overdue
from   MDB.CREDITS_&table_ -- I want this to be evaluated as 'MDB.CREDITS_201802'
where  start_date >= &crop -- Credits that started on or after Jan 1 2018
and    dcredfaperturacta < add_months(&crop, 1) -- Credits that started before Feb 1 2018
/

该column x new_value y设置将列的最后一个值捕获x到替换变量y中。它最初用于报告中的页眉，但对于编写脚本非常方便。您可以使用隐藏输出set termout off。（set termout on恢复终端输出。）

这将生成并运行

crop   = date '2018-01-01'
crop_n = add_months(date '2018-01-01', 1)
table_ = 201802

select person_id
     , bank_id
     , account_id
     , start_date
     , months_between(add_months(date '2018-01-01', 1), date '2018-01-01') as period
     , days_overdue
from   MDB.CREDITS_201802 -- I want this to be evaluated as 'MDB.CREDITS_201802'
where  start_date >= date '2018-01-01' -- Credits that started on or after Jan 1 2018
and    dcredfaperturacta < add_months(date '2018-01-01', 1) -- Credits that started before Feb 1 2018

请注意以下注释：这是 Oracle SQL*Plus 语法。Toad、PL/SQL Developer 和 SQL Developer 等其他工具在一定程度上支持它并使用不同的方法。并非所有 SQL*Plus 语法都保证可以在所有其他工具中工作。在 PL/SQL Developer 中，它只能在命令行窗口中工作。在 SQL Developer 中，您需要使用“作为脚本运行”(F5) 而不是常规的“运行”(Ctrl-Enter)。

推荐阅读