在oracle中更改表中的多个列

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=;

3 个解决方案

#1

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将多次提交并且可以等待多次独占表锁定。

#2

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脚本，那么往返可能会使您的脚本变得非常缓慢。

#3

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 ''               );

#1