oracle 给一个列命名,给列重命名

在工作中用到Oracle数据库的时候，你可能经常听到有人问这样的问题：如何修改数据库内的表列名？实际上，Oracle数据库压根就没有配备给表列重命名的SQL命令，不过，倒也有个命令专门负责给整个数据表重命名。

[@more@]

问题就来了，我们在开发数据库应用程序的时候经常遇到这样的情况，一开始给表列分配的名称可能因为开发进度等缘故而必须改成新的名字。简单办法还是有的，如下所示，这个改名小技法分为6个步骤，通过这些操作尽可方便地改变数据表的结构属性，其中自然包括表列的属性(名称)了：

1取消(Drop)所有索引完整性约束。

2给目标数据表起个临时的名字(比如把Tab_A改名为Table_A)。

3用修改了表列名之后的新结构创建Tab_A表。

4把Table_A表内的数据拷贝到Tab_A表。

5重建步骤1中取消的所有索引完整性约束。

6删除Table_A临时表。

但真要运用起来却可能遭遇这样的麻烦：如果步骤之一出了问题，数据库的结构就可能受到破坏。比如说，把Table_A的大量数据拷贝到Tab_A就可能因为回滚段空间不足而失败。于是Table_A仍然留在了数据库里，而Tab_A却只获得了部分拷贝数据，弄不好后者还成了空表。

如果我们采用Oralce 8i系统就能够耍个该版本特有的小花招：由开发人员自己删除原来的表列再增加新表列，从而把以上的过程减少到了只有5步：

1.针对要重命名的表列，取消施加其上的所有约束和索引。

2.添加新的表列，给这些表列赋新的名字。

3.把数据从旧表列移动到新的表列。

4.对新的表列重新创建所有的约束和索引。

5.删除旧列。

可是，前前后后怎么折腾却都要涉及到数据，难道就没有其他不涉及数据操作的表列重命名方法吗？毕竟我们仅仅想给表列改个名和数据有什么关系啊！这种删除-创建-删除的办法也够费劲的！

在这个时候不禁想起了SQL Server 的sp_rename，眼泪淌了一地……

其实，我们可以SYS用户的身份直接访问Oracle数据字典col$，然后通过更新col$表内对应列的方式就可以修改表列名了。所以我们完全可以设计一个RenameColumn过程，通过它把SYS操作的复杂全过程隐藏起来，今后你直接调用这个存储过程就万事大吉了。

在执行有关的SQL命令之前请先以SYS用户身份登录，这是因为之后你要进到Oracle数据字典，这可是需要SYS权限的。

Step 1：找出要更名的表列上依附的所有数据库对象

首先看看该表列是否带表的某部分约束：

select t.constraint_name, t.constraint_type as type, c.column_name, c.position

from dba_constraints t, dba_cons_columns c

where t.table_name = 'Table_Name'

and t.owner = 'User_Name'

and t.constraint_name = c.constraint_name

and t.table_name = c.table_name

and t.owner = c.owner

and t.constraint_name in (select c.constraint_name

from dba_constraints t, dba_cons_columns c

where t.table_name = ' Table_Name '

and t.owner = ' User_Name '

and c.column_name = 'Column_Name'

and t.constraint_name = c.constraint_name

and t.table_name = c.table_name

and t.owner = c.owner)

order by c.constraint_name,

c.position

如果列带有约束。所以在给表列更名之前需要取消该约束。

为了确定该表列是否具有约束或者索引执行以下SQL命令：

select index_name, column_name, column_position as position

from dba_ind_columns

where table_name = 'Table_Name'

and table_owner = 'User_Name'

and index_name in (select index_name

from dba_ind_columns

where table_name = 'Table_Name'

and table_owner = 'User_Name'

and column_name = 'Column')

order by index_name,

position

Step 2：直接在Oracle数据字典中给表列更名

用以下代码找到User_Name用户对应的user_id：

select user_id

from dba_users

where username = 'User_Name';

假定输出为***。接着呢可以用找到的USER_ID从OBJ$ Oracle数据字典找出Table_Name表的OBJ#：

select obj#,name

from obj$

where name = 'Table_Name' and owner# = ***;

好，用我们找到的这个OBJ# 就可以轻松地径直找出Table_Name表Column列的COL#：

select col#, name

from col$

where obj# = *****  and name = 'Column_Name';

OK，现在你就可以更新COL# Oracle数据字典中的指定列了，它对应EMPTEST表 (OBJ# = *****) 的SAL列(COL# = **)：

update col$

set name = 'NewColumn_Name'

where obj# = ***** and col# = **;

commit;

Step 3：把以上代码封装为RenameColumn过程

每次修改列名的时候都要重复以上操作过程的话简直就是发疯了，而且期间也没有足够的余地供我们进行错误处理。所以把代码封装为一个具备错误处理和优化SQL的存储过程成为必然之选。在我们这个RenameColumn过程中，第2步的几个SQL语句可以组合为一个UPDATE语句。 RenameColumn过程的全部代码请参考程序清单。

RenameColumn过程接受4个参数：pUser、pTableName、pOldColumnName和pNewColumnName。

该存储过程的第1部分首先进行以下5种检查：

· 过程是否在被SYS用户运行

· pUserName参数是否是有效的数据库用户

· pTableName参数是否是有效的数据表

· pOldColumnName参数应该是现有的表列名

· pNewColumnName参数应该是表内现在没有的表列名

如果以上检查不能通过则触发相应的异常。否则过程就会用带多个嵌套查询的UPDATE语句更新col$ Oracle数据字典中的对应行。

之后你就可以轻松地用RenameColumn过程把原来的表列名改为新的表列名：

begin

RenameColumn('User_Name', 'Table_Name', 'Column_Name', 'NewColumn_Name');

end;

Step 4：更新Oracle字典

其实就是用Oracle的catalog.sql 脚本更新Oracle字典：

@C:oracleora81RDBMSADMINcatalog.sql;

现在你再检查表结构，相信已经有了变化。

Step 5：重建先前删除的所有数据库对象

这里指的是重建依附于被改名列的数据库对象。

......

这样在没有处理数据的情况下我们就更新了一个表列名。期间唯一可能比较耗时间的过程就是在处理大型数据表的时候重建索引。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/41224/viewspace-780369/，如需转载，请注明出处，否则将追究法律责任。