oracle怎么固定列顺序,指定列的顺序同时不破坏数据的步骤-Oracle

指定列的顺序同时不破坏数据的步骤

--T_LAW_RULE

ALTER TABLE T_LAW_RULE drop primary key; --删除主键

ALTER TABLE T_LAW_RULE MODIFY(ID NULL); --设为null

ALTER TABLE T_LAW_RULE ADD(ID_TMP CHAR(32)); --添加一临时列

create table T_LAW_RULE_copy as select * from T_LAW_RULE; --复制表以及数据

update T_LAW_RULE a

set a.ID_TMP=(select b.ID from T_LAW_RULE_copy b where b.ID=a.ID)

where exists (select 1 from T_LAW_RULE_copy b where a.ID=b.ID); --插入数据

drop table T_LAW_RULE_copy; --删除表以及数据

ALTER TABLE T_LAW_RULE DROP COLUMN ID; --删除原来列

alter table T_LAW_RULE rename column ID_TMP to ID; --修改临时列列名

ALTER TABLE "T_LAW_RULE" ADD CHECK ("ID" IS NOT NULL);

ALTER TABLE "T_LAW_RULE" ADD PRIMARY KEY ("ID");

--创建临时表

CREATE TABLE "CCNTBPM_KJ"."T_LAW_RULE_TMP" (

"ID" CHAR(32 BYTE) NOT NULL ,

"LAWS_NAME" VARCHAR2(200 BYTE) NULL ,

"TITLE" VARCHAR2(200 BYTE) NULL ,

"ISDELETED" CHAR(1 BYTE) DEFAULT '0' NULL ,

"CREATOR" VARCHAR2(32 BYTE) NULL ,

"UPDATER" VARCHAR2(32 BYTE) NULL ,

"CREATIME" DATE DEFAULT sysdate NULL ,

"UPDATIME" DATE NULL ,

"DISOBEYRULE" CHAR(1 BYTE) NULL ,

"BODY" CLOB NULL

)

LOGGING

NOCOMPRESS

NOCACHE

;

COMMENT ON TABLE "CCNTBPM_KJ"."T_LAW_RULE_TMP" IS 't_law_rule(法律法规表)';

COMMENT ON COLUMN "CCNTBPM_KJ"."T_LAW_RULE_TMP"."LAWS_NAME" IS '法律法规名称';

COMMENT ON COLUMN "CCNTBPM_KJ"."T_LAW_RULE_TMP"."TITLE" IS '章节标题';

COMMENT ON COLUMN "CCNTBPM_KJ"."T_LAW_RULE_TMP"."ISDELETED" IS '是否删除';

COMMENT ON COLUMN "CCNTBPM_KJ"."T_LAW_RULE_TMP"."CREATOR" IS '创建者';

COMMENT ON COLUMN "CCNTBPM_KJ"."T_LAW_RULE_TMP"."UPDATER" IS '修改者';

COMMENT ON COLUMN "CCNTBPM_KJ"."T_LAW_RULE_TMP"."CREATIME" IS '创建时间';

COMMENT ON COLUMN "CCNTBPM_KJ"."T_LAW_RULE_TMP"."UPDATIME" IS '修改时间';

COMMENT ON COLUMN "CCNTBPM_KJ"."T_LAW_RULE_TMP"."DISOBEYRULE" IS '违罚则管理 1为违则，2为罚则';

COMMENT ON COLUMN "CCNTBPM_KJ"."T_LAW_RULE_TMP"."BODY" IS '正文内容';

INSERT into "T_LAW_RULE_TMP" select t.ID,t.LAWS_NAME,t.TITLE,t.ISDELETED,t.CREATOR,t.UPDATER,t.CREATIME,t.UPDATIME,t.DISOBEYRULE,t.BODY from T_LAW_RULE t;

drop table T_LAW_RULE; --删除表以及数据

ALTER table T_LAW_RULE_TMP rename to T_LAW_RULE; --改名

ALTER TABLE "T_LAW_RULE" ADD CHECK ("ID" IS NOT NULL);

ALTER TABLE "T_LAW_RULE" ADD PRIMARY KEY ("ID");