SQL 在oracle中更改表中的多列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5482152/
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
Altering more than one column in a table in oracle
提问by sridevi
Will the two scripts below (for altering a table) make diff..??
下面的两个脚本（用于更改表格）会产生差异吗？？
script 1 :
脚本 1：
alter table ACNT_MGR_HSTRY add DM_BTNUMBER DATA_TYPE ;
alter table ACNT_MGR_HSTRY add DM_BTID DATA_TYPE ;
alter table ACNT_MGR_HSTRY add DM_USERID DATA_TYPE ;
alter table ACNT_MGR_HSTRY add DM_WSID DATA_TYPE ;
script 2 :
脚本2：
alter table ACNT_MGR_HSTRY
add
(
DM_BTNUMBER DATA_TYPE,
DM_BTID DATA_TYPE,
DM_USERID DATA_TYPE,
DM_WSID DATA_TYPE
);
will update makes a diff..???
更新会有所不同..？？？
update OPERATIONAL_UNIT
set ( BANK_ID=
ENTY_CODE_ID=
TIME_ZONE=
DM_BTNUMBER=
DM_BTID=
DM_USERID=
DM_WSID=
);
-----------
update OPERATIONAL_UNIT set BANK_ID=;
update OPERATIONAL_UNIT set ENTY_CODE_ID=;
update OPERATIONAL_UNIT set TIME_ZONE=;
update OPERATIONAL_UNIT set DM_BTNUMBER=;
update OPERATIONAL_UNIT set DM_BTID=;
update OPERATIONAL_UNIT set DM_USERID=;
update OPERATIONAL_UNIT set DM_WSID=;
回答by OMG Ponies
The two examples are equivalent.
这两个例子是等价的。
I've only ever used statements like you have in the first example; I don't know if it's possible that you won't get as good an error message if using the second example format in the event of an error.. Gary Myers confirmed my belief:
我只使用过像你在第一个例子中那样的语句；我不知道如果在出现错误的情况下使用第二种示例格式，您是否可能不会得到那么好的错误消息。. 加里迈尔斯证实了我的信念：
Mostly the same. If, for example, DM_WSID already existed then the relevant statement would fail. In script 1, you'd get three of the columns added. In script 2 you wouldn't. If you have DDL triggers or AUDIT, then they will get fired multiple times for case 1. Script 1 will commit multiple times and MAY wait for an exclusive table lock several times.
大体相同。例如，如果 DM_WSID 已经存在，那么相关语句就会失败。在脚本 1 中，您将添加三列。在脚本 2 中，你不会。如果您有 DDL 触发器或 AUDIT，那么它们将在案例 1 中被多次触发。脚本 1 将提交多次并且可能多次等待排他表锁。
回答by Jon Heller
Script 2 will generally perform much better than script 1. Grouping similar changes and performing them all at once is almost always faster. But the real question is, is the difference significant?
脚本 2 通常会比脚本 1 执行得更好。将相似的更改分组并同时执行它们几乎总是更快。但真正的问题是，差异是否显着？
Based on your comment about 50 tables with 15 columns each, I'd say the difference is at least somewhat significant, and possibly very significant depending on your configuration.
根据您对 50 个表（每个表 15 列）的评论，我认为差异至少有些显着，并且可能非常显着，具体取决于您的配置。
Just yesterday I made almost the exact same change, modifying about 30 columns for about 100 tables. Running the script locally using SQL*Plus, the time decreased from 2 minutes to 4 seconds. Most of the time was probably spent communicating between SQL*Plus and the database. If you have a SQL*Plus script that needs to be run remotely those round trips could make your script painfully slow.
就在昨天，我做了几乎完全相同的更改，为大约 100 个表修改了大约 30 列。使用 SQL*Plus 在本地运行脚本，时间从 2 分钟减少到 4 秒。大多数时间可能花在 SQL*Plus 和数据库之间的通信上。如果您有一个需要远程运行的 SQL*Plus 脚本，那么这些往返可能会使您的脚本非常缓慢。
回答by burhanuddin abbas
One more way we can Modify our Columns by bracketing each column that we need to alter it . Here The instance :-
我们可以通过将需要更改的每一列括起来来修改我们的列的另一种方法。这里的实例：-
Alter table news
modify (Newsid number primary key )
modify (newsArticleNo number check (newsArticleNo > 0))
modify (NewsArea char(15) default '' );
