database oracle 中的修改列 - 如何在设置为可为空之前检查列是否可为空？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1028956/
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
MODIFY COLUMN in oracle - How to check if a column is nullable before setting to nullable?
提问by Jay S
I'm trying to fill in for a colleague in doing some Oracle work, and ran into a snag. In attempting to write a script to modify a column to nullable, I ran into the lovely ORA-01451 error:
我试图为一位同事做一些 Oracle 工作，但遇到了一个障碍。在尝试编写脚本以将列修改为可为空时，我遇到了可爱的 ORA-01451 错误：
ORA-01451: column to be modified to NULL cannot be modified to NULL
This is happening because the column is already NULL. We have several databases that need to be udpated, so in my faulty assumption I figured setting it to NULL should work across the board to make sure everybody was up to date, regardless of whether they had manually set this column to nullable or not. However, this apparently causes an error for some folks who already have the column as nullable.
发生这种情况是因为该列已经为 NULL。我们有几个需要更新的数据库，所以在我错误的假设中，我认为将它设置为 NULL 应该全面工作以确保每个人都是最新的，无论他们是否手动将此列设置为可空。但是，这显然会导致某些已经将该列设为可为空的人出错。
How does one check if a column is already nullable so as to avoid the error? Something that would accomplish this idea:
如何检查一列是否已经可以为空以避免错误？可以实现这个想法的东西：
IF( MyTable.MyColumn IS NOT NULLABLE)
ALTER TABLE MyTable MODIFY(MyColumn NULL);
回答by Tony Andrews
You could do this in PL/SQL:
您可以在 PL/SQL 中执行此操作：
declare
l_nullable user_tab_columns.nullable%type;
begin
select nullable into l_nullable
from user_tab_columns
where table_name = 'MYTABLE'
and column_name = 'MYCOLUMN';
if l_nullable = 'N' then
execute immediate 'alter table mytable modify (mycolumn null)';
end if;
end;
回答by Rob van Laarhoven
just do the alter table and catch the exception.
只需执行更改表并捕获异常即可。
DECLARE
allready_null EXCEPTION;
PRAGMA EXCEPTION_INIT(allready_null, -1451);
BEGIN
execute immediate 'ALTER TABLE TAB MODIFY(COL NULL)';
EXCEPTION
WHEN allready_null THEN
null; -- handle the error
END;
/
if you don't want to use PL/SQL
如果你不想使用 PL/SQL
set feedback off
set echo off
set feedback off
set pages 0
set head off
spool to_null.sql
select 'alter table TAB modify (COL NULL);'
from user_tab_columns
where table_name = 'TAB'
and column_name = 'COL'
and nullable = 'N';
spool off
set feedback on
set echo on
set termout on
@@to_null.sql
host rm -f to_null.sql
or just do the alter table and ignore the error.
或者只是做改变表并忽略错误。
