插入null时插入默认值

Question

我有一个 Oracle 数据库和一个包含多个非空列的表，所有列都有默认值。

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

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

我有这样的代码：

<?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 插入非空列”。 当然，我有多个可为空的列，因此代码 create insert 语句非常不可读、丑陋，我就是不喜欢这样。

我想要一个声明，类似于：

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 实现这一目标吗？

编辑：

谢谢大家的回答。似乎没有“标准”方法来实现我想要的，所以我接受了 IMO 的最佳答案：我应该停止聪明并坚持通过自动构建的语句省略空值。

不完全是我想看到的，但也没有更好的选择。

Answer 1

对于那些现在正在阅读的人：

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

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

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

Answer 2

如AskTom 中所述thread 时，DEFAULT 关键字只能作为列插入中的独立表达式使用，与 NVL 等函数或表达式混合使用时将不起作用。

换句话说，这是一个有效的查询：

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

您可以对所有行和绑定变量或常量 DEFAULT（如果变量为空）使用动态查询。这可能很简单，只需将 $insert 中的字符串 :not_null_column_with_default_value 替换为字符串 DEFAULT 即可。

您还可以查询视图 ALL_TAB_COLUMNS 并使用 nvl(:your_variable, :column_default)。默认值为 DATA_DEFAULT 列。

Answer 3

我认为最干净的方法是不在 INSERT 语句中提及它们。您可以开始编写触发器来填充默认值，但这对于您的目标来说是重甲。

难道不能稍微重构一下你的应用程序代码吗？在 PHP 中，您可以构造一个干净的 INSERT 语句，而无需混乱的 if 语句，例如：

<?php
$insert['column_name1'] = 'column_value1';
$insert['column_name2'] = 'column_value2';
$insert['column_name3'] = '';
$insert['column_name4'] = 'column_value4';

// remove null values
foreach ($insert as $key => $value) {
  if (is_null($value) || $value=="") {
    unset($insert[$key]);
  }
}

// construct insert statement
$statement = "insert into table (". implode(array_keys($insert), ',') .") values (:". implode(array_keys($insert), ',:') .")";

// call oci_parse
$stid = oci_parse($conn, $statement);

// bind parameters
foreach ($insert as $key => $value) {
  oci_bind_by_name($stid, ":".$key, $value);
}

// execute!
oci_execute($stid);
?>

Answer 4

性能更好的选择是第一个。

无论如何，据我了解，您不想重复插入列名称和值，因为很难进行修改。您可以使用的另一个选项是使用 运行插入返回子句后跟更新：

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)
RETURNING not_null_column_with_default_value 
INTO :insered_value

它似乎工作使用 PHP。

之后，您可以检查 insered_value 绑定变量上的 null。如果它为空，您可以运行以下更新：

UPDATE my_table
   SET not_null_column_with_default_value  = DEFAULT
 WHERE  pk_column = :pk_column: 

Answer 5

我想使用一个插入件
我想要的任何数据的声明
插入，并且不必费心检查是否
插入的值是否为空。

使用该列的默认值定义表。例如：

create table myTable
(
  created_date date default sysdate,
  ...
)
tablespace...

或更改现有表：

alter table myTable modify(created_date default sysdate);

现在您（或任何使用 myTable 的人）不必担心默认值，因为它应该如此。只需知道，对于此示例，该列仍然可为空，因此有人可以显式插入空值。如果不需要这样做，也请将该列设置为不为空。

编辑：假设上述内容已经完成，您可以在插入语句中使用 DEFAULT 关键字。我会避免触发这一点。