Oracle:动态设置表中的所有 NOT NULL 列以允许 NULL [英] Oracle: Dynamically set all NOT NULL columns in a Table to allow NULL

问题描述

我有一个包含 75 多列的表格.几乎所有的列都有 NOT NULL 约束.

I have a table with 75+ columns in it. Almost all of the columns have the NOT NULL constraint.

如果执行一个巨大的 alter table modify 语句(包含其中的每一列)，我会收到一条错误消息，提示您不能将此字段设置为 NULL，因为它已经是 NULL"

If do a giant alter table modify statement (with every column in there), I get an error saying something along the lines of "You can't set this field to NULL, because it already is NULL"

我必须为多个表执行此操作，因此更希望使用动态解决方案.

I have to do this for several tables, and so would prefer to have a dynamic solution.

我可以动态查找所有非空的列，并将它们设置为空吗?

Can I dynamically find all of the columns that are NOT NULL, and set them to NULL?

我见过几个类似的问题，但找不到 Oracle SQL 的解决方案.将表中的所有列修改为 '无论如何都不为空

I've seen several similar questions like this, but can't find a solution for Oracle SQL. Modify all columns in a table to 'not null' no matter what

推荐答案

这是一个测试表，有两个非空列和一个空列:

Here is a test table, with two not null columns, and one null column:

create table zzz_mark_test_me (
     cust_id varchar2(20) not null, 
     cust_name varchar2(20) null,  
     cust_phone varchar2(20) not null
);

table ZZZ_MARK_TEST_ME created.

desc zzz_mark_test_me

Name       Null     Type         
---------- -------- ------------ 
CUST_ID    NOT NULL VARCHAR2(20) 
CUST_NAME           VARCHAR2(20) 
CUST_PHONE NOT NULL VARCHAR2(20) 

现在调用这个 SQL:

Now invoke this SQL:

select 'alter table ' || table_name || 
    ' modify (' || column_name || ' null );' 
from user_tab_columns 
where table_name='ZZZ_MARK_TEST_ME' and nullable='N' 
order by column_id;

产生这个:

alter table ZZZ_MARK_TEST_ME modify (CUST_ID null );
alter table ZZZ_MARK_TEST_ME modify (CUST_PHONE null );

将输出复制/粘贴到 SQL*Plus 等中并调用:

Copy/paste the output into SQL*Plus etc. and invoke:

alter table ZZZ_MARK_TEST_ME modify (CUST_ID null );
table ZZZ_MARK_TEST_ME altered.

alter table ZZZ_MARK_TEST_ME modify (CUST_PHONE null );
table ZZZ_MARK_TEST_ME altered.

现在，不再是 NOT NULL:

And now, no more NOT NULL:

desc zzz_mark_test_me
Name       Null Type         
---------- ---- ------------ 
CUST_ID         VARCHAR2(20) 
CUST_NAME       VARCHAR2(20) 
CUST_PHONE      VARCHAR2(20) 

这篇关于Oracle:动态设置表中的所有 NOT NULL 列以允许 NULL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！