如何为增加的列指定位置 (zz)

修改表的例子：

Examples

Add a column to a table
   ALTER TABLE STAFF_OPTIONS
      ADD SO_INSURANCE_PROVIDER Varchar2(35);

Add  a default value to a column
   ALTER TABLE STAFF_OPTIONS
      MODIFY SO_INSURANCE_PROVIDER Varchar2(35) DEFAULT 'ABC Ins';

Add two columns to a table and remove a constraint
   ALTER TABLE STAFF_OPTIONS
      ADD (SO_STAFF_ID INT, SO_PENSION_ID INT)
          STORAGE INITIAL 10 K
          NEXT 10 K
          MAXEXTENTS 121
          PCTINCREASE 0
          FREELISTS 2
      DROP CONSTRAINT cons_SO;

//z 2012-08-09 09:33:52 IS2120@.T1428351245[T63,L414,R16,V609] 

 How does one add a column to the middle of a table? 

 Submitted by admin on Sat, 2005-12-03 00:53 


 Oracle only allows columns to be added to the end of an existing table. Example: 


 SQL> CREATE TABLE tab1 ( col1 NUMBER ); 


 Table created. 


 //z 2012-08-09 09:33:52 IS2120@T1428351245[T63,L414,R16,V609] 

 SQL> ALTER TABLE tab1 ADD (col2 DATE); 


 Table altered. 


 SQL> DESC tab1 

  Name                                      Null?    Type 

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

  COL1                                               NUMBER 

  COL2                                               DATE 


 Nevertheless, some databases also allow columns to be added to an existing table after a particular column (i.e. in the middle of the table). For example, in MySQL the following syntax is valid: 


 ALTER TABLE tablename ADD columnname AFTER columnname; 


 Oracle does not support this syntax. However, it doesn't mean that it cannot be done. 


 Workarounds: 


 1. Create a new table and copy the data across. 


 SQL> RENAME tab1 TO tab1_old; 


 Table renamed. 


 SQL> CREATE TABLE tab1 AS SELECT 0 AS col1, col1 AS col2 FROM tab1_old; 


 Table created. 


 2. Use the DBMS_REDEFINITION package to change the structure on-line while users are workining. 

 ‹ Can one retrieve only the Nth row from a table? up How does one drop/ rename a columns in a table? › 

 » 


     Login to post comments 


 How does one add a column to the middle of a table? 

 Submitted by samar bijaya panda (not verified) on Tue, 2006-02-21 06:21. 


 Here is another workaround: 


 create table emptest as select empno, 1 as id, 'x' as emp_name, ename from emp; 


 In the code above, replace 1 as id, 'x' as emp_name with your new columns. 

 » 


     Login to post comments 


 How does one add a column to the middle of a table? 

 Submitted by Kalyani P. Banerjee (not verified) on Tue, 2006-07-25 00:14. 


 To add a column in middle of the table of varchar data type: 


 SQL>create table test (ename varchar2(20),salary number); 

 Table created 


 SQL>desc test; 

 Name Null? Type 

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

 ENAME VARCHAR2(20) SALARY NUMBER 


 [i]SQL>rename test to test1; 

 Table renamed 

 [ii]SQL>create table test2 (id varchar2(20)); 

 Table created 

 [iii]SQL>create table test as(select test1.ename,test2.id,test1.salary from test1,test2); 

 Table created 

 ........................................................................................ 

 SQL>desc test; 

 Name Null? Type 

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

 ENAME VARCHAR2(20) 

 ID VARCHAR2(20) 

 SALARY NUMBER 

 » 

 //z 2012-08-09 09:33:52 IS2120@1428351245[T63,L414,R16,V609] 

 How does one add a column to the middle of a table? 

 Submitted by SandhyaRR on Mon, 2010-09-27 03:09. 


 There is a table T with col1 and col2 and you want to add a new column col3 after col1. 


 1. Rename the column col2 to col3 as: 

 ALTER TABLE tablename RENAME COLUMN col2 TO col3; 


 2. Add a new column to the table 

 alter table t1 add (col2 datatype); 


 3.Now finally interchange the data contained in the two column: 

 UPDATE EMPLOYEE 

 SET col2 = col3 

 ,col3 = col2 


 Note: Data types of the interchanged columns should match. 

 //z 2012-08-09 09:33:52 IS2120@.T1428351245[T63,L414,R16,V609]