日期写为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)。