Oracle：基于筛选条件的默认列值

Question

开发人员要求在一个表格中添加一列，默认值为'N'，但是如果条目的id = 3，则此列的默认值应为'Y'，有没有办法在Oracle中实现这一点？

- Drew

---

通常我会从应用程序方面处理这些事情。只需 column = 'N'; if(id == 3) then column = 'Y'; -----store row in DB------。如果数据库中存在行，则只需使用 case when 语句更新现有行的列。 - Deep

在插入期间，也许可以添加一个触发器吗？ - ah_hau

你只需要使用带有DECODE函数的VIRTUAL列。 - Lalit Kumar B

@Deepak Pawar：通常我不会在应用程序中处理这个问题，因为这样会使数据库完整性受到威胁，因为您可能无法控制所有应用程序。此外，从维护的角度来看，许多应用程序可能需要更新。 - user2672165

记录的ID确实是3吗？那么我们正在谈论一条记录。插入ID为3且值为“Y”的记录，并将“N”设置为该列的默认值。 - Thorsten Kettner

Answer 1

我同意评论者提到的这不是一个好的数据库设计。尽管如此，在现实情况下妥协数据库设计并不罕见。

我不确定虚拟列是否符合要求。OP寻求一种默认值的方法；虚拟列与默认约束不同（例如，使用默认约束我们可以将除默认值以外的值插入到列中）。最好的方法可能是使用触发器来设置“默认”值：

CREATE OR REPLACE TRIGGER mytrigger
  BEFORE INSERT ON mytable FOR EACH ROW
  WHEN (new.mycolumn IS NULL)
BEGIN
  SELECT DECODE(id, 3, 'Y', 'N') INTO :new.mycolumn FROM dual;
END;
/

无论您使用Oracle 10g还是11g（您已经标记了这两个版本），触发器都可以正常工作。

希望这可以帮助您。

- David Faber

---

如果您使用 :new.mycolumn := CASE :new.id WHEN 3 THEN 'Y' ELSE 'N' END; 而不是 SELECT，您可以节省 PL/SQL 引擎跳转到 SQL 引擎并返回以执行 IF 测试的成本。此外，我认为在没有 :new. 的情况下，您对 id 的引用将不在范围内。我也欢迎您对在此处使用 :old 而不是 :new 进行评论。 - Unoembre

感谢您的评论，我认为您提到的一切都是正确的。除了使用CASE之外，我们还可以使用DECODE(:new.id, 3, 'Y', 'N')。 - David Faber

请记住，DECODE 仅在 SQL 中有效，而不在 PL/SQL 中有效。CASE 在两者中都有效。 - Unoembre

Answer 2

11g方法

从Oracle 11g及以上版本开始，您可以使用VIRTUAL列一步完成此操作。

测试案例

SQL> CREATE TABLE tab_default (
  2    ID          NUMBER,
  3    flag varchar2(1) GENERATED ALWAYS AS (decode(id, 3, 'Y', 'N')) VIRTUAL
  4  );

Table created.

SQL>
SQL> INSERT INTO tab_default (ID) VALUES (1);

1 row created.

SQL> INSERT INTO tab_default (ID) VALUES (3);

1 row created.

SQL> INSERT INTO tab_default (ID) VALUES (10);

1 row created.

SQL> SELECT * FROM tab_default;

        ID F
---------- -
         1 N
         3 Y
        10 N

SQL>

所以，在VIRTUAL列声明中使用DECODE函数可以为您处理要求。

10g方法

您可以使用以下方法来满足要求：

1. DEFAULT值
2. 每当id = 3时，使用AFTER INSERT TRIGGER

创建表时将DEFAULT值设置为'N'。仅在插入新行且id列的值为3时触发触发器，使触发器将该值更新为'Y'。否则，对于所有其他情况，缺省值将为'N'。

- Lalit Kumar B

---

为什么有人想要使用AFTER触发器而不是BEFORE触发器？ - David Faber

因为触发器至少确保事务前进了。如果你依赖于触发器前，基于此进行更改，可能就不一定有把握了。在这种情况下，两种方式都差不多，不过我更喜欢使用触发器后。顺便说一句，我并不是触发器的忠实粉丝，因为它们是一种影响结果的操作，即副作用。但是，在这种情况下，如果某人没有使用11g，则这是唯一的方法。这也是我编辑答案并将11g方法放在10g触发器方法之前的主要原因。 - Lalit Kumar B

Answer 3

在向您的表中添加新列后，您可以使用以下查询向该列插入值：

update table_name set column_name = ( case when id = 3 then 'Y'  else 'N' end );    

在插入新记录时，您可以使用以下方法：
1）在创建插入查询时决定列，您可以在创建查询时添加逻辑。
2）在数据库中创建触发器，该触发器应在插入任何新行到表后更新您的列值。

- Yagnesh Agola

Answer 4

这是一个非常糟糕的数据库设计。它不符合关系型数据库的规范。 我建议保留该表并在该表上创建一个新视图，其中包含一个额外的列，可以使用DECODE或CASE WHEN进行计算。

- Charmi

---

并不是每个应用程序都能在一夜之间得到修复。这就是为什么我们需要一些权宜之计，以便在找到和实施永久解决方案之前进行临时处理。 - Lalit Kumar B

Answer 5

创建一个新表格，并添加一个值列：

create table table1 as
select u.*,
case when id=3 then 'Y' ELSE 'N'
  END value  
from table2 u

- Yana Lisogurski