1、表字段增删改
（1）增加、删除、修改表字段
增加格式：alter table 表名 add (字段名 字段类型);
alter table ORDER_AUDIT_STATEMENT
add (LOSS_COST_ERROR_SUM
varchar(64));
删除格式：alter table 表名 drop (字段名);
alter table ORDER_AUDIT_STATEMENT
drop (user_id
,prov_id
);
修改格式：alter table 表名 modify (字段名 字段类型 默认值 是否为空);
alter table ums_user
modify (id
varchar(32));
字段重命名：alter table 表名 rename column 列明 to 新列名;
表名重命名：alter table 表名 rename to 新表名;
（2）表、字段添加注释
字段格式：comment on column 表名.字段名 is ‘中文注释’
comment on column ORDER_AUDIT_STATEMENT
.LOSS_COST_ERROR_SUM
is '稽核收入流失字段';
表名格式：comment on table 表名 is ‘注释内容’;
comment on table OPERATOR_INFO
is '操作员信息表';
（3）建表语句
DROP TABLE "JT_AUDIT"."CFG_AUSYS_OPERATE";
CREATE TABLE "JT_AUDIT"."CFG_AUSYS_OPERATE" (
"ID" VARCHAR2
(255 BYTE
) NOT NULL ,
"RULE_CODE" VARCHAR2
(255 BYTE
) NULL ,
"MONTH_ID" VARCHAR2
(255 BYTE
) NULL ,
"AREA_ID" VARCHAR2
(255 BYTE
) NULL ,
"SUSPECTED_DATA" VARCHAR2
(255 BYTE
) NULL ,
"FILE_NAME" VARCHAR2
(255 BYTE
) NULL ,
"FILE_PATH" VARCHAR2
(255 BYTE
) NULL ,
"CR_USER" VARCHAR2
(255 BYTE
) NULL ,
"CR_DATE" VARCHAR2
(255 BYTE
) NULL ,
"CR_AREA" VARCHAR2
(255 BYTE
) NULL ,
"UP_USER" VARCHAR2
(255 BYTE
) NULL ,
"UP_DATE" VARCHAR2
(255 BYTE
) NULL ,
"UP_AREA" VARCHAR2
(255 BYTE
) NULL ,
"TYPE" VARCHAR2
(255 BYTE
) NULL
)
LOGGING
NOCOMPRESS
NOCACHE
;
COMMENT ON COLUMN "JT_AUDIT"."CFG_AUSYS_OPERATE"."RULE_CODE" IS '稽核点';
COMMENT ON COLUMN "JT_AUDIT"."CFG_AUSYS_OPERATE"."MONTH_ID" IS '账期';
COMMENT ON COLUMN "JT_AUDIT"."CFG_AUSYS_OPERATE"."AREA_ID" IS '省份';
COMMENT ON COLUMN "JT_AUDIT"."CFG_AUSYS_OPERATE"."SUSPECTED_DATA" IS '疑似数据量';
COMMENT ON COLUMN "JT_AUDIT"."CFG_AUSYS_OPERATE"."FILE_NAME" IS '附件名称';
COMMENT ON COLUMN "JT_AUDIT"."CFG_AUSYS_OPERATE"."FILE_PATH" IS '附件地址';
COMMENT ON COLUMN "JT_AUDIT"."CFG_AUSYS_OPERATE"."CR_USER" IS '创建用户';
COMMENT ON COLUMN "JT_AUDIT"."CFG_AUSYS_OPERATE"."CR_DATE" IS '创建时间';
COMMENT ON COLUMN "JT_AUDIT"."CFG_AUSYS_OPERATE"."CR_AREA" IS '创建CODE';
COMMENT ON COLUMN "JT_AUDIT"."CFG_AUSYS_OPERATE"."UP_USER" IS '修改用户';
COMMENT ON COLUMN "JT_AUDIT"."CFG_AUSYS_OPERATE"."UP_DATE" IS '修改时间';
COMMENT ON COLUMN "JT_AUDIT"."CFG_AUSYS_OPERATE"."UP_AREA" IS '修改code';
COMMENT ON COLUMN "JT_AUDIT"."CFG_AUSYS_OPERATE"."TYPE" IS '状态';
ALTER TABLE "JT_AUDIT"."CFG_AUSYS_OPERATE" ADD CHECK ("ID" IS NOT NULL);
ALTER TABLE "JT_AUDIT"."CFG_AUSYS_OPERATE" ADD PRIMARY KEY ("ID");
（3）创建序列
drop sequence UMS_USER_ROLE_ID_SEQ
;
create sequence UMS_USER_ROLE_ID_SEQ
minvalue
1
maxvalue
9999999999999999999999
start with 53509
increment
by 1
cache
20;
create table RES_RESOURCE_EXTEND
(
ID VARCHAR2
(255) not null,
MENU_CODE VARCHAR2
(255),
URL VARCHAR2
(255),
TYPE VARCHAR2
(32)
)
tablespace DWSOFT_DATA_LT_ZB
pctfree
10
initrans
1
maxtrans
255
storage
(
initial
64K
next 1M
minextents
1
maxextents unlimited
);
alter table RES_RESOURCE_EXTEND
disable all triggers
;
insert into RES_RESOURCE_EXTEND
(ID
, MENU_CODE
, URL
, TYPE)
values ('353803727866777600', '1809000000', null, '11');
alter table RES_RESOURCE_EXTEND
enable all triggers
;
select ums_role_id_seq
.NEXTVAL
from dual
;
