ORA 02436 sysdate oracle

最新推荐文章于 2024-06-19 09:02:04 发布

原创最新推荐文章于 2024-06-19 09:02:04 发布 · 1k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#insert #sql #oracle #application #table #date

本文探讨了在Oracle数据库中实现日期字段的有效性检查方法。通过触发器和默认值两种方式，确保日期不会设置在未来，提供了具体SQL示例及注意事项。

这是网上能够搜索到的比较可信的结果：
Unfortunately you can't use SYSDATE in a check constraint as it is a dynamic Oracle variable. A better way to enforce this constraint would be to use a trigger. For example:

CREATE OR REPLACE TRIGGER trg_emp_dob BEFORE INSERT OR UPDATE ON emp
BEGIN
   IF :new.dob > SYSDATE THEN
      RAISE_APPLICATION_ERROR (num => -20000, msg => 'DOB Cannot be in the future');
   END IF;
END;

不过嘿嘿，我可不信邪，万事问大哥啊，于是在asktom上找到了这个解决方案：
Just create an additional column using as default SYSDATE. Then you can use this column to compare.
And if you want to hide this column create a view around it...

SQL> create table t
2 (x   date,
3   y   date default sysdate,
4   constraint x_y check
5   (
6      x >= y
7   )
8 );

Table created.

SQL>
SQL> create view v as select x from t;

View created.

SQL>
SQL> insert into v values (sysdate);

1 row created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> insert into v values (sysdate-1);
insert into v values (sysdate-1)
*
ERROR at line 1:
ORA-02290: check constraint (ADBM.X_Y) violated

总之oracle不能非常漂亮的解决这个问题，这里两个方案你看着选吧。