插入null时插入默认值 [英] Insert default value when null is inserted

问题描述

我有一个Oracle数据库，以及一个包含几个非空列的表，所有列均具有默认值.

I have an Oracle database, and a table with several not null columns, all with default values.

我想对要插入的任何数据使用一个insert语句，并且不必费心检查插入的值是否为null.

I would like to use one insert statement for any data I want to insert, and don't bother to check if the values inserted are nulls or not.

插入null时，有什么方法可以退回到默认列值?

Is there any way to fall back to default column value when null is inserted?

我有此代码:

<?php
if (!empty($values['not_null_column_with_default_value'])) {
    $insert = "
        INSERT INTO schema.my_table
            ( pk_column, other_column, not_null_column_with_default_value)
        VALUES
            (:pk_column,:other_column,:not_null_column_with_default_value)
     ";
} else {
    $insert = "
        INSERT INTO schema.my_table
            ( pk_column, other_column)
        VALUES
            (:pk_column,:other_column)
     ";        
}

因此，我必须完全省略该列，否则将出现错误尝试将null插入为非null的列". 当然，我有多个可为空的列，因此代码create insert语句非常不可读，丑陋，我只是不喜欢这种方式.

So, I have to omit the column entirely, or I will have the error "trying insert null to not null column". Of course I have multiple nullable columns, so the code create insert statement is very unreadable, ugly, and I just don't like it that way.

我想发表一条声明，类似于:

I would like to have one statement, something similar to:

INSERT INTO schema.my_table
    ( pk_column, other_column, not_null_column_with_default_value)
VALUES
    (:pk_column,:other_column, NVL(:not_null_column_with_default_value, DEFAULT) );

那当然是一个假设的查询.您知道我可以通过Oracle DBMS实现该目标的任何方式吗?

That of course is a hypothetical query. Do you know any way I would achieve that goal with Oracle DBMS?

编辑:

谢谢大家的回答.它似乎没有实现我想要的目标的标准"方法，因此我接受了IMO的最佳答案:我应该不再变得聪明，而是坚持通过自动生成的语句忽略空值.

Thank you all for your answers. It seams that there is no "standard" way to achieve what I wanted to, so I accepted the IMO best answer: That I should stop being to smart and stick to just omitting the null values via automatically built statements.

并非完全符合我的意愿，但没有更好的选择.

Not exactly what I would like to see, but no better choice.

推荐答案

对于那些现在阅读它的人:

For those who reading it now:

Oracle 12c中有一个新功能:默认为空.例如:

In Oracle 12c there is new feature: DEFAULT ON NULL. For example:

CREATE TABLE tab1 (
  col1        NUMBER DEFAULT 5,
  col2        NUMBER DEFAULT ON NULL 7,
  description VARCHAR2(30)
);

因此，当您尝试在col2中插入null时，它将自动为7.

So when you try to INSERT null in col2, this will automatically be 7.

这篇关于插入null时插入默认值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！