如何在数据字典中修改Oracle的表列名-mysql教程

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

SQL>SET LONG 9999

SQL>SELECT TEXT FROM ALL_VIEWS
WHERE VIEW_NAME = ‘USER_TAB_COLUMNS’;

SQL> SELECT * FROM ALL_OBJECTS WHERE OWNER =‘SCOTT’
AND OBJECT_NAME=‘TEST’;

SQL> SELECT OBJ#,COL#,NAME FROM SYS.COL$ WHERE OBJ# =13888;
OBJ# COL# NAME
---------- ---------- -------
13888 1 EMPNO
13888 2 ENAME

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