Oracle 对现有的已经含有数据表添加自动递增的ID（ID字段已经存在，只是ID没有数据）
如果原来的表中不存在ID，可以直接用命令行添加一个主键ID字段
ALTER TABLE "tablename" ADD ID NUMBER；--添加ID字段
ALTER TABLE "tablename" ADD PRIMARY KEY ("ID");--给ID添加主键
已经含有ID字段时就可以按下面SQL来执行
1 ---(1)创建一个和原来表结构相同的表 2 3 CREATE TABLE tjc01_4_TEMP AS SELECT * FROM tjc01_4 WHERE 1=0; 4 CREATE TABLE TJC01_5_TEMP AS SELECT * FROM TJC01_5 WHERE 1=0; 5 CREATE TABLE TJC01_7_TEMP AS SELECT * FROM TJC01_7 WHERE 1=0; 6 CREATE TABLE TJC01_9_TEMP_TEMP AS SELECT * FROM TJC01_9 WHERE 1=0; 7 CREATE TABLE TZY12_TEMP AS SELECT * FROM TZY12 WHERE 1=0; 8 CREATE TABLE TJC16_1_TEMP AS SELECT * FROM TJC16_1 WHERE 1=0; 9 10 11 --(2)新增序列号 12 13 -- Create sequence 14 create sequence SEQ_TJC01_4 15 minvalue 1 16 maxvalue 999999999999999999999999999 17 start with 1 18 increment by 1 19 cache 20; 20 21 22 -- Create sequence 23 create sequence SEQ_TJC01_5 24 minvalue 1 25 maxvalue 999999999999999999999999999 26 start with 1 27 increment by 1 28 cache 20; 29 30 -- Create sequence 31 create sequence SEQ_TJC01_7 32 minvalue 1 33 maxvalue 999999999999999999999999999 34 start with 1 35 increment by 1 36 cache 20; 37 38 -- Create sequence 39 create sequence SEQ_TJC01_9 40 minvalue 1 41 maxvalue 999999999999999999999999999 42 start with 1 43 increment by 1 44 cache 20; 45 46 -- Create sequence 47 create sequence SEQ_TJC16_1 48 minvalue 1 49 maxvalue 999999999999999999999999999 50 start with 1 51 increment by 1 52 cache 20; 53 54 55 -- Create sequence 56 create sequence SEQ_TZY12 57 minvalue 1 58 maxvalue 999999999999999999999999999 59 start with 1 60 increment by 1 61 cache 20; 62 63 64 65 66 --(3)给新建的表添加自增id的触发器 67 --tjc01_4 68 CREATE OR REPLACE TRIGGER trr_tjc01_4 before insert on tjc01_4_TEMP for each row 69 DECLARE next_id NUMBER; 70 begin 71 select seq_tjc01_4.nextval into next_id from dual; 72 :new.id := next_id; 73 end; 74 --TJC01_5 75 CREATE OR REPLACE TRIGGER trr_TJC01_5 before insert on TJC01_5_TEMP for each row 76 DECLARE next_id NUMBER; 77 begin 78 select seq_TJC01_5.nextval into next_id from dual; 79 :new.id := next_id; 80 end; 81 82 --TJC01_7 83 84 CREATE OR REPLACE TRIGGER trr_TJC01_7 before insert on TJC01_7_TEMP for each row 85 DECLARE next_id NUMBER; 86 begin 87 select seq_TJC01_7.nextval into next_id from dual; 88 :new.id := next_id; 89 end; 90 91 -- TJC01_9 92 CREATE OR REPLACE TRIGGER trr_TJC01_9 before insert on TJC01_9_TEMP_TEMP for each row 93 DECLARE next_id NUMBER; 94 begin 95 select seq_TJC01_9.nextval into next_id from dual; 96 :new.id := next_id; 97 end; 98 99 -- TZY12 100 CREATE OR REPLACE TRIGGER trr_TZY12 before insert on TZY12_TEMP for each row 101 DECLARE next_id NUMBER; 102 begin 103 select seq_TZY12.nextval into next_id from dual; 104 :new.id := next_id; 105 end; 106 107 --TJC16_1 108 CREATE OR REPLACE TRIGGER trr_TJC16_1 before insert on TJC16_1_TEMP for each row 109 DECLARE next_id NUMBER; 110 begin 111 select seq_TJC16_1.nextval into next_id from dual; 112 :new.id := next_id; 113 end; 114 115 --（4）将原来表数据插入到新建的表中 116 insert into tjc01_4_TEMP SELECT * FROM tjc01_4 WHERE 1=1; 117 select *from tjc01_4_TEMP; 118 insert into TJC01_5_TEMP SELECT * FROM TJC01_5 WHERE 1=1; 119 select *from TJC01_5_TEMP; 120 insert into TJC01_7_TEMP SELECT * FROM TJC01_7 WHERE 1=1; 121 select *from TJC01_7_TEMP; 122 insert into TJC01_9_TEMP_TEMP SELECT * FROM TJC01_9 WHERE 1=1; 123 select *from TJC01_9_TEMP_TEMP; 124 insert into TZY12_TEMP SELECT * FROM TZY12 WHERE 1=1; 125 select *from TZY12_TEMP; 126 insert into TJC16_1_TEMP SELECT * FROM TJC16_1 WHERE 1=1; 127 select *from TJC16_1_TEMP; 128 129 130 131 132 --(5)将原来的表名更改新的文字（也可以说是备份原来的表） 133 134 135 ALTER TABLE tjc01_4 RENAME TO tjc01_4_test; 136 ALTER TABLE TJC01_5 RENAME TO TJC01_5_test; 137 ALTER TABLE TJC01_7 RENAME TO TJC01_7_test; 138 ALTER TABLE TJC01_9 RENAME TO TJC01_9_test; 139 ALTER TABLE TZY12 RENAME TO TZY12_test; 140 ALTER TABLE TJC16_1 RENAME TO TJC16_1_test; 141 142 --（6）将新表的表名更改为原来的表名 143 144 ALTER TABLE tjc01_4_TEMP RENAME TO tjc01_4; 145 ALTER TABLE TJC01_5_TEMP RENAME TO TJC01_5; 146 ALTER TABLE TJC01_7_TEMP RENAME TO TJC01_7; 147 ALTER TABLE TJC01_9_TEMP_TEMP RENAME TO TJC01_9; 148 ALTER TABLE TZY12_TEMP RENAME TO TZY12; 149 ALTER TABLE TJC16_1_TEMP RENAME TO TJC16_1; 150 151 152 153 154 155 156 157 158 159 160 161
