Oracle 10gr2：输入的执行日期是早上9点到下午5点？

Z时代
2024-01-10
分类：问答

我想强制执行该日期时间在上午9点到下午5点之间。我如何使用ORACLE CHECK约束强制执行此操作？Oracle 10gr2：输入的执行日期是早上9点到下午5点？

回答：

SQL> ed 
Wrote file afiedt.buf 
    1 create table date_check (
    2 dt date check(to_number(to_char(dt, 'HH24')) between 9 and 16) 
    3*) 
SQL>/
Table created. 
SQL> ed 
Wrote file afiedt.buf 
    1* insert into date_check values(to_date('01/01/2008 08:30', 'DD/MM/YYYY HH2 
4:MI')) 
SQL>/
insert into date_check values(to_date('01/01/2008 08:30', 'DD/MM/YYYY HH24:MI' 
)) 
* 
ERROR at line 1: 
ORA-02290: check constraint (SCOTT.SYS_C006170) violated 
SQL> ed 
Wrote file afiedt.buf 
    1* insert into date_check values(to_date('01/01/2008 10:30', 'DD/MM/YYYY HH2 
4:MI')) 
SQL>/
1 row created. 
SQL> ed 
Wrote file afiedt.buf 
    1* insert into date_check values(to_date('01/01/2008 17:30', 'DD/MM/YYYY HH2 
4:MI')) 
SQL>/
insert into date_check values(to_date('01/01/2008 17:30', 'DD/MM/YYYY HH24:MI' 
)) 
* 
ERROR at line 1: 
ORA-02290: check constraint (SCOTT.SYS_C006170) violated