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

Question

我想强制执行该日期时间在上午9点到下午5点之间。我如何使用ORACLE CHECK约束强制执行此操作？

Answer 1

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