Chinaunix首页 | 论坛 | 博客
  • 博客访问: 104572999
  • 博文数量: 19283
  • 博客积分: 9968
  • 博客等级: 上将
  • 技术积分: 196062
  • 用 户 组: 普通用户
  • 注册时间: 2007-02-07 14:28
文章分类

全部博文(19283)

文章存档

2011年(1)

2009年(125)

2008年(19094)

2007年(63)

分类: Oracle

2008-04-01 20:23:56

来源:赛迪网    作者:29331

在Oracle数据库中没有提供直接修改表中列名称的功能,但在实际使用时常需要修改表的列名和列顺序,在不得已的情况下,有些Oracle的使用者用重新创建一个新的具有正确列名和顺序的数据库表,再将旧表的数据转储进来,最后删除旧表并将新表重命名为旧表的方法来完成此功能。此方法的最大问题是要求有双倍的存储空间、较大的回滚段和较长的时间,如果表中数据量较大,这项工作开销会很大。实际上我们可以从数据字典中直接修改表列的名称和顺序。下面是实现的具体步骤:

◆以internal用户名登录Oracle数据库,并创建一测试表。

以下是引用片段: 
SQL>CREATE TABLE SCOTT.TEST AS SELECT EMPNO,
ENAME FROM SCOTT.EMP;
SQL>DESC SCOTT.TEST 
Name Type Nullable Default Comments 
------- ------------ ------- 
EMPNO NUMBER(4) Y 
ENAME VARCHAR2(10) Y

下面需要要把SCOTT.TEST表中EMPNO和ENAME两列调换顺序,并把ENAME列更名为EMP_NAME,EMPNO改为EMP_NO。

◆查询表中列的实际存储位置或表。

  SQL>SET LONG 9999

  由于TEXT列是LONG类型,只有“SET”之后才能完全显示。

  SQL>SELECT TEXT FROM ALL_VIEWS

  WHERE VIEW_NAME = ‘USER_TAB_COLUMNS’;

数据字典视图USER_TAB_COLUMNS中存储有表列的定义信息,从该语句的查询结果可以看出,列定义信息是存储在表SYS.COL$中的,即如果修改表中列的定义,应该在SYS.COL$表中修改。

◆从数据字典视图ALL_OBJECTS中查找对象SCOTT.TEST对象ID。

SQL> SELECT * FROM ALL_OBJECTS WHERE OWNER =‘SCOTT’

AND OBJECT_NAME=‘TEST’;

◆根据SCOTT.TEST对象的ID,从SYS.COL$检索出表中列的定义信息。

  SQL> SELECT OBJ#,COL#,NAME FROM SYS.COL$ WHERE OBJ# =13888;

  OBJ# COL# NAME

  ---------- ---------- -------

  13888 1 EMPNO

  13888 2 ENAME

◆使用Update语句来进行修改。

UPDATE SYS.COL$ SET COL# = 2,NAME=‘EMP_NO’

WHERE OBJ# = 13888 AND NAME=‘EMPNO’;

UPDATE SYS.COL$ SET COL# = 1,NAME=‘EMP_NAME’

WHERE OBJ# = 13888 AND NAME =‘ENAME’;

  COMMIT;

◆重启数据库服务。

由于数据字典是在数据库启动时加载到SQL中的,所以修改了它之后,如果使用“SELECT * FROM SCOTT.TEST; ”,会发现好像并没有修改。因此,修改完成之后,还需要重启数据库服务。

  SQL>SHUTDOWN

  SQL>STARTUP

  这时,再查看,就会发现修改已经成功。

  SQL> SELECT * FROM SCOTT.TEST;

  EMP_NAME EMP_NO

  ---------- ------

  SMITH 7369

  ALLEN 7499

  WARD 7521

  ……

这种方法直接从数据库中进行表列定义的修改,存在一定风险,但它对于数据量特别大的表是非常有用的。充分利用数据字典功能,往往能够完成日常很难完成的工作。下面笔者写了一段简单的存储过程,可实现表中列的重命名。大家可以直接调用这个过程来完成列的重命名:

  SQL>exec altercolname 
  (‘模式名称’,‘表名称’,‘原列名称’,‘新列名称’); 
  create or replace procedure sys.altercolname 
  (schmaname in varchar2, 
  tabname in varchar2, 
  oldcolname in varchar2, 
  newcolname in varchar2) is 
  n_schmaname varchar2(30); --模式名称 
  n_tablename varchar2(30); --表名称 
  n_oldcolname varchar2(30); --原来列名称 
  n_newcolname varchar2(30); --新的列名称 
  n_objnum number; 
  begin 
  n_schmaname := upper(schmaname); 
  n_tablename := upper(tabname); 
  n_oldcolname := upper(oldcolname); 
  n_newcolname := upper(newcolname); 
  SELECT OBJECT_ID INTO n_objnum 
  FROM ALL_OBJECTS 
  WHERE OWNER = n_schmaname 
  AND OBJECT_NAME=n_tablename; 
  UPDATE SYS.COL$ 
  SET NAME=n_newcolname 
  WHERE OBJ# = n_objnum AND 
  NAME=n_oldcolname; 
  COMMIT; 
  end altercolname;
阅读(316) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~