学习Oracle，根据日期设置布尔值 [英] Learning Oracle, Setting boolean based on date

问题描述

我刚刚在学校里就Oracle开设了一个模块，我想寻求一些帮助，因为我在数据库系统功能方面相对较新.

I've just started a module in school on Oracle, I would like to ask for some help as I'm relatively new in regards to the capabilities of database systems in general.

对于我其中一项任务的一部分，我试图创建一个存储客户详细信息的表，其中包含"LastBillPaidDate"列.作为DATE数据类型和"hasUnpaidBill"和BOOLEAN数据类型.

For a part of one of my assignments, I'm trying to create a table where it stores customers details, there are columns of "LastBillPaidDate" as a DATE datatype and "hasUnpaidBill" and a BOOLEAN datatype.

在设置约束时，是否有办法使我在X天前LastBillPaidDate为false时将hasUnpaidBill从false设置为true?

When it comes to setting constraints, is there a way for me to make it such that when LastBillPaidDate is X days ago, I set hasUnpaidBill from false to true?

再次感谢，并期待与大家学习！

Thanks again and looking forward to learning from you all!

推荐答案

表中首先应该有 has_unpaid_bill 列吗?它取决于 SYSDATE .需求就像虚拟列一样，但是-您不能在其中使用 SYSDATE ，因为它不是确定性函数.因此，改为使用视图怎么样?将所需的数据保留在表中，但查询视图:

Should you have the has_unpaid_bill column in the table at the first place? It depends on SYSDATE. The requirement smells like a virtual column, but - you can't use SYSDATE there as it is not a deterministic function. Therefore, how about a view instead? Keep data you need in the table, but query the view:

表和一些示例记录:

SQL> create table test
  2    (id                  number,
  3     last_bill_paid_date date
  4    );

Table created.

SQL> insert into test values (1, date '2020-11-09');

1 row created.

SQL> insert into test values (1, date '2020-11-02');

1 row created.

查看:

SQL> create or replace view v_test as
  2    select id,
  3           last_bill_paid_date,
  4           case when trunc(sysdate) - last_bill_paid_date > 5 then 'Y'
  5                else 'N'
  6           end has_unpai_dbill
  7    from test;

View created.

SQL>
SQL> select * from v_test;

        ID LAST_BILL_ H
---------- ---------- -
         1 09.11.2020 N
         1 02.11.2020 Y

SQL>

这篇关于学习Oracle，根据日期设置布尔值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！