Oracle SQL将非空列更新为空字符串

Question

我在编写 SQL Update 语句时遇到了问题，需要将非空字段更新为空字符串。

UPDATE channel_mgmt.channels
    SET registered_address_id=p_address_id
        ,vendor_id=p_spc_code
     WHERE id=v_channel_id;

在这种情况下，p_spc_code可以设置为''，但是当我运行以下代码时，出现了SQL错误：

Error report:
ORA-01407: cannot update ("CHANNEL_MGMT"."CHANNELS"."VENDOR_ID") to NULL
ORA-06512: at line 8973
 01407. 00000 -  "cannot update (%s) to NULL"
*Cause:    
*Action:

有什么办法可以解决这个问题吗？我在某些情况下需要使用空字符串，但我不确定为什么Oracle会抱怨空值。

desc channel_mgmt.channels
Name                  Null     Type               
--------------------- -------- ------------------ 
ID                    NOT NULL NUMBER(16)         
CHANNEL_STATUS_KEY    NOT NULL VARCHAR2(64 CHAR)  
REGISTERED_ADDRESS_ID NOT NULL NUMBER(16)         
VENDOR_ID             NOT NULL VARCHAR2(64 CHAR)

- John McDonnell

Answer 1

如果在一个列上定义了NOT NULL约束，您不能通过将null或零长度字符串('')指定为列的新值来更新表。当赋给varchar2列或变量时，Oracle会将零长度字符串('')视为NULL，因此从Oracle的角度来看，这两个语句是相同的：set col_name = null和set col_name = ''。如果您想允许一个列接受NULL，则需要从该列中删除非空约束，如果不会造成伤害：

alter table <<table_name>> modify (<<column_name>> [<<data type>>] null)

- Nick Krasnov

---

很遗憾，我无法更新表格。是否可以在插入语句中加入一个检查，如果为空，则将列更新为“ ”？这是一个小技巧。 - John McDonnell

不行，因为Oracle无法区分NULL和空字符串。禁止NULL的约束也会禁止空字符串。你能做到的最接近的方法是将其设置为包含单个空格的字符串。 - Walter Mitty

Answer 2

Oracle无法区分空字符串和空值。它使用相同的标记（一个字节的零）来表示它们两者。

- Walter Mitty

---

好的，但是Oracle如何允许在这种情况下使用插入语句？如果我执行插入操作，我可以将列中的值设置为''，我想在更新语句中复制这个操作。 - John McDonnell

Answer 3

您尝试过使用一个空格的字符串' '吗？如果是varchar类型，则大多数情况下比较（即您未指定比较白色字符）应该按预期工作。我的意思是，对于varchar字段，' ' = ' '（单个和双重空格）应该评估为true。（我没有Oracle实例，所以无法确定...）

- veljkoz

---

可以，不确定为什么我没早点想到，只是试图过度复杂化问题 :( - John McDonnell

Answer 4

你的id不为null并且你想将其设置为null，你可以将所有表行插入到另一个表中作为备份，然后删除你的表并再次创建一个具有可为null的id行，并从备份中重新插入你的行。只需注意如果您使用id作为主键，则它将再次为非null。

另一种方法是在更新中使用''，它会返回空格但不是null。

CREATE TABLE BACKUP(
ID                    NOT NULL NUMBER(16),         
CHANNEL_STATUS_KEY    NOT NULL VARCHAR2(64 CHAR),  
REGISTERED_ADDRESS_ID NOT NULL NUMBER(16),         
VENDOR_ID             NOT NULL VARCHAR2(64 CHAR));

INSERT INTO BACKUP (SELECT * FROM YourTable);

ALTER TABLE YourTable(
ID                             NUMBER(16),         
CHANNEL_STATUS_KEY    NOT NULL VARCHAR2(64 CHAR),  
REGISTERED_ADDRESS_ID NOT NULL NUMBER(16),         
VENDOR_ID             NOT NULL VARCHAR2(64 CHAR));

INSERT INTO YourTable (SELECT * FROM BACKUP);

现在使用您的查询

- Hamidreza

Answer 5

如果您有一个 NOT NULL 列，并且要将其更新为 NULL，也许您需要考虑一下应用程序中的逻辑。 否则，您可以定义一个 nvl(column, some_pattern)，例如 '???'（当然取决于列的长度），用于未知空数据，当您知道不允许插入/更新 NULL 时。

然后您就有了某些内容来检查是否有任何行为不正确或在视图中过滤那三个问号并将其再次显示为 NULL。

- Dom84