oracle开发建表实例sql

-- CREATE TABLE(创建表格)
CREATE TABLE TEST.TB_TABLE1
(
  IARTICLEID   NUMBER(20) NOT NULL,
  CTITLE       VARCHAR2(30),
  ITAG         INTEGER,
  CAUTHOR      VARCHAR2(30),
  ITYPE        INTEGER,
  CVIEW        INTEGER DEFAULT 0 NOT NULL,
  CSHARE       INTEGER DEFAULT 0 NOT NULL,
  CPICURL      VARCHAR2(256),
  IACTIVE      INTEGER DEFAULT 0 NOT NULL,
  IPADDINGTYPE NUMBER(1),
  CSUMMARY     VARCHAR2(256),
  IORDER       INTEGER,
  CADDTIME     DATE,
  CUPDATETIME  DATE,
  ISDELETE     INTEGER DEFAULT 0 NOT NULL,
  ISHIDDEN     INTEGER DEFAULT 0 NOT NULL,
  CCONTENT     VARCHAR2(4000),
  CURL         VARCHAR2(255),
  ISHOMEPAGE   INTEGER DEFAULT 0 NOT NULL
);

-- ADD COMMENTS TO THE TABLE(添加字段注释)
COMMENT ON TABLE TEST.TB_TABLE1
  IS 'XX资讯表';
-- ADD COMMENTS TO THE COLUMNS
COMMENT ON COLUMN TEST.TB_TABLE1.IARTICLEID
  IS '文章ID';
COMMENT ON COLUMN TEST.TB_TABLE1.CTITLE
  IS '标题';
COMMENT ON COLUMN TEST.TB_TABLE1.ITAG
  IS '标签,1:精选,2:贷款资质,3:下款口子';
COMMENT ON COLUMN TEST.TB_TABLE1.CAUTHOR
  IS '创建者';
COMMENT ON COLUMN TEST.TB_TABLE1.ITYPE
  IS '文章类型,1:贷款技巧,2:贷款资讯';
COMMENT ON COLUMN TEST.TB_TABLE1.CVIEW
  IS '浏览数';
COMMENT ON COLUMN TEST.TB_TABLE1.CSHARE
  IS '分享数';
COMMENT ON COLUMN TEST.TB_TABLE1.CPICURL
  IS '图片地址';
COMMENT ON COLUMN TEST.TB_TABLE1.IACTIVE
  IS '上下架 0 上架 1下架';
COMMENT ON COLUMN TEST.TB_TABLE1.IPADDINGTYPE
  IS '边距  1:有边距 0：无边距';
COMMENT ON COLUMN TEST.TB_TABLE1.CSUMMARY
  IS '摘要';
COMMENT ON COLUMN TEST.TB_TABLE1.IORDER
  IS '排序';
COMMENT ON COLUMN TEST.TB_TABLE1.CADDTIME
  IS '添加时间';
COMMENT ON COLUMN TEST.TB_TABLE1.CUPDATETIME
  IS '修改时间';
COMMENT ON COLUMN TEST.TB_TABLE1.ISDELETE
  IS '是否删除标记，0:未删除，1:已删除';
COMMENT ON COLUMN TEST.TB_TABLE1.ISHIDDEN
  IS '是否隐藏标记，0:未隐藏，1:已隐藏';
COMMENT ON COLUMN TEST.TB_TABLE1.CCONTENT
  IS '内容';
COMMENT ON COLUMN TEST.TB_TABLE1.CURL
  IS '生成的H5地址';
COMMENT ON COLUMN TEST.TB_TABLE1.ISHOMEPAGE
  IS '是否首页展示，0:展示，1:不展示';

--ADD PRIMARY KEY
ALTER TABLE TEST.TB_TABLE1 ADD 
  CONSTRAINT PK_TB_TABLE1
  PRIMARY KEY(IARTICLEID);

--CREATE INDEX
CREATE INDEX TEST.IK_TABLE1_ITAG ON TEST.TB_TABLE1(ITAG);
CREATE INDEX TEST.IK_TABLE1_CVIEW_CTIME_IHP ON TEST.TB_TABLE1(CVIEW, CUPDATETIME, ISHOMEPAGE);
CREATE UNIQUE INDEX TEST.UK_TABLE1_CURL ON TEST.TB_TABLE1(CURL);

--CREATE SEQUENCE(序列)
CREATE SEQUENCE TEST.SEQ_TABLE1 INCREMENT BY 1 START WITH 1 MINVALUE 1 MAXVALUE 9999999999999999999999999999 CACHE 20;


--ADD COLUMN(添加字段)
ALTER TABLE TEST.TB_TABLE1
ADD(
   CLP_SERVICE_TEL   VARCHAR2(100),
   CLP_QUERY_URL     VARCHAR2(500)
);

--ADD COMMENT(添加字段注释)
COMMENT ON COLUMN TEST.TB_TABLE1.CLP_SERVICE_TEL
  IS '客服电话';
COMMENT ON COLUMN TEST.TB_TABLE1.CLP_QUERY_URL
  IS '进度查询URL';
  
  
--UPDATE
update TEST.TB_TABLE1 t set t.cidfront_small_path = replace(t.cidfront_small,'http://img.youyuwo.com','/opt/yyloan') where t.cidfront_small is not null and t.cidfront_small like '%img.youyuwo.com%';
commit;

--INSERT
insert into TEST.TB_TABLE1(cmenuid, cmenuname, cmenudesc, cmenuhref, chreftarget, cmenuimage, cparentid, iorderid, idispflag)
values ('M15010', '新特惠快讯', '改版', null, '/manage/operation/special_info.html', null, 'M15000', 10, 1);

INSERT INTO TEST.TB_TABLE1(CMENUID, CMENUNAME, CMENUDESC, CMENUHREF, CHREFTARGET, CMENUIMAGE, CPARENTID, IORDERID, IDISPFLAG) VALUES('M12016','公告配置','','',	'/manage/credit/card_notice.html','','M12000',16,1);

commit;

--DELETE
delete from TEST.TB_TABLE1 t where t.cmenuhref is null and t.chreftarget not like '%card_notice%';
commit;

--修改表中字段属性
alter table credit.tb_lend_count_ctmanager modify (CREALNAME null,CBANKNO null,CREGISTERBANKNAME null,COMMISSION NUMBER(22));