有没有一种方法可以将列添加到Oracle表中的指定位置? [英] Is there a way to add column at a specified position in Oracle table?

问题描述

考虑一下我在Oracle 10G中创建的这个初始表:

Consider this inital table I have created in Oracle 10G:

╔═════════════════════════════════╗
║  CUSTOMER_ID ACC_NO ACC_BALANCE ║
╠═════════════════════════════════╣
║         100    200        1000  ║
║         101    150        4000  ║
║         102    350        2000  ║
║         103    450        2500  ║
║         104    550        2200  ║
╚═════════════════════════════════╝

现在，我想在表中添加另一列customer_name.我用过:

Now I want to add another column customer_name into the table. I used:

ALTER TABLE BANK_ACCOUNT 
  ADD (CUSTOMER_NAME VARCHAR2(30)); 

，该列将作为表的最后一列插入，而我希望将该列作为第二列添加到表中.现在，我提到的SQL代码无法执行此操作.那么，如何在指定位置添加列?在SQL中甚至可能吗?

and the column is being inserted as the last column in the table whereas I want the column to be added to the table as the second column. Now the SQL code I mentioned is unable to do so. So how can I add the column at a specified position? Is it even possible in SQL?

推荐答案

由于列select允许您(从逻辑上)指定顺序，因此实际上不在列所在的位置 取回它们.

It really doesn't matter where the column is physically since select will allow you to specify the order (logically) in which they're retrieved.

而且，如果使用的select *不允许您指定顺序，则可能不应该指定顺序.在极少数情况下，您应该执行此操作(例如，数据库分析工具)，大多数情况下，最好选择所需的单个列，即使您希望全部使用.这使您可以快速捕获模式更改，以便使程序适应这些更改.

And, if you're using select * which doesn't let you specify the order, you probably shouldn't be. There are precious few situations where you should be doing that (DB analysis tools, for example), most of the time it's better to select the individual columns you want, even if you want them all. This allows you to catch schema changes quickly so you can adapt your programs to them.

在任何情况下，除非您明确列出它们，否则SQL本身都不保证返回列的顺序. select *可能会按今天的顺序将它们给您，明天将按字母顺序将它们给您.即使特定的实现允许您执行此操作(通过在正确的"位置将列创建一个新表并复制数据，或提供SQL扩展如alter table T insert column C1 before C2)，也建议您不要这样做.它不会移植到其他实现中，我更希望代码尽可能地具有移植性.

In any case, SQL itself makes no guarantees about the order in which columns are returned unless you explicitly list them. select * may give them to you in ordinal order today and alphabetic order tomorrow. Even if a particular implementation allows you to do it (by creating a new table with the column in the "right" place and copying the data across, or providing an SQL extension like alter table T insert column C1 before C2), I'd advise against it. It won't be portable to other implementations and I prefer to have my code as portable as practicable.

此外，除了在SQL中可以指定的内容外，DBMS还应该能够完全控制如何存储数据.可能是您的主键是第七和第四十二列的组合，将它们放在物理记录的前面可能会更有效.

In addition, beyond what you can specify in SQL, a DBMS should be able to totally control how it stores data. It may be that your primary key is a composite of the seventh and forty-second column and it may be more efficient to have them at the front of the physical records.

这篇关于有没有一种方法可以将列添加到Oracle表中的指定位置?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！