oracle 插入空值时插入默认值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5388778/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Insert default value when null is inserted
提问by SWilk
I have an Oracle database, and a table with several not null columns, all with default values.
我有一个 Oracle 数据库和一个包含几个非空列的表，所有列都具有默认值。
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.
我想对要插入的任何数据使用一个 insert 语句，并且不必费心检查插入的值是否为空值。
Is there any way to fall back to default column value when null is inserted?
插入空值时，有没有办法回退到默认列值？
I have this code:
我有这个代码：
<?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)
";
}
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.
因此，我必须完全省略该列，否则我将出现“尝试将空值插入到非空值列”的错误。当然我有多个可为空的列，所以代码 create insert 语句非常不可读，丑陋，我只是不喜欢那样。
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) );
That of course is a hypothetical query. Do you know any way I would achieve that goal with Oracle DBMS?
这当然是一个假设的查询。您知道我可以通过 Oracle DBMS 实现该目标的任何方式吗？
EDIT:
编辑：
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.
谢谢大家的答案。看来没有“标准”的方式来实现我想要的，所以我接受了 IMO 的最佳答案：我应该停止聪明并坚持通过自动构建的语句忽略空值。
Not exactly what I would like to see, but no better choice.
不完全是我想看到的，但没有更好的选择。
回答by Lisa Aaron
For those who reading it now:
对于那些现在阅读它的人：
In Oracle 12c there is new feature: DEFAULT ON NULL. For example:
在 Oracle 12c 中有一个新特性：DEFAULT ON NULL。例如：
CREATE TABLE tab1 (
col1 NUMBER DEFAULT 5,
col2 NUMBER DEFAULT ON NULL 7,
description VARCHAR2(30)
);
So when you try to INSERT null in col2, this will automatically be 7.
因此，当您尝试在 col2 中插入 null 时，这将自动为 7。
回答by Vincent Malgrat
As explained in this AskTom thread, the DEFAULTkeyword will only work as a stand-alone expression in a column insert and won't work when mixed with functions or expressions such as NVL.
正如在此AskTom 线程中所解释的，该DEFAULT关键字将仅用作列插入中的独立表达式，并且在与 NVL 等函数或表达式混合时将不起作用。
In other words this is a valid query:
换句话说，这是一个有效的查询：
INSERT INTO schema.my_table
( pk_column, other_column, not_null_column_with_default_value)
VALUES
(:pk_column,:other_column, DEFAULT)
You could use a dynamic query with all rows and either a bind variable or the constant DEFAULT if the variable is null. This could be as simple as replacing the string :not_null_column_with_default_valuewith the string DEFAULTin your $insert.
如果变量为空，您可以对所有行和绑定变量或常量 DEFAULT 使用动态查询。这可能是那样简单替换串:not_null_column_with_default_value与串DEFAULT在你的$insert。
You could also query the view ALL_TAB_COLUMNSand use nvl(:your_variable, :column_default). The default value is the column DATA_DEFAULT.
您还可以查询视图ALL_TAB_COLUMNS并使用nvl(:your_variable, :column_default). 默认值为 column DATA_DEFAULT。
回答by Martin Schapendonk
I think the cleanest way is to not mention them in your INSERT-statement. You could start writing triggers to fill default values but that's heavy armor for what you're aiming at.
我认为最干净的方法是不在您的 INSERT 语句中提及它们。您可以开始编写触发器来填充默认值，但这对您的目标来说是沉重的负担。
Isn't it possible to restructure your application code a bit? In PHP, you could construct a clean INSERT-statement without messy if's, e.g. like this:
难道不能稍微重构一下你的应用程序代码吗？在 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);
?>
回答by FerranB
The better option for performance is the first one.
性能更好的选择是第一个。
Anyway, as I understand, you don't want to repeat the insert column names and values due the difficult to make modifications. Another option you can use is to run an insert with returningclause followed by an update:
无论如何，据我了解，由于难以进行修改，您不想重复插入列名称和值。您可以使用的另一个选项是运行带有返回子句的插入，然后是更新：
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
It seems to work with PHP.
它似乎适用于 PHP。
After this you can check for nullon insered_valuebind variable. If it's null you can run the following update:
在此之后，你可以检查null在insered_value绑定变量。如果它为空，您可以运行以下更新：
UPDATE my_table
SET not_null_column_with_default_value = DEFAULT
WHERE pk_column = :pk_column:
回答by tbone
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.
我想对要插入的任何数据使用一个 insert 语句，并且不必费心检查插入的值是否为空值。
Define your table with a default value for that column. For example:
使用该列的默认值定义您的表。例如：
create table myTable
(
created_date date default sysdate,
...
)
tablespace...
or alter an existing table:
或更改现有表：
alter table myTable modify(created_date default sysdate);
Now you (or anyone using myTable) don't have to worry about default values, as it should be. Just know that for this example, the column is still nullable, so someone could explicitly insert a null. If this isn't desired, make the column not null as well.
现在您（或任何使用 myTable 的人）不必担心默认值，因为它应该是。只知道对于这个例子，列仍然可以为空，所以有人可以显式地插入一个空值。如果不需要，也使该列不为空。
EDIT: Assuming the above is already done, you can use the DEFAULT keyword in your insert statement. I would avoid triggers for this.
编辑：假设以上已经完成，您可以在插入语句中使用 DEFAULT 关键字。我会避免触发。
