Oracle 创建表/视图/物化视图/字段描述/主键/索引脚本

创建Table

-- DROP TABLE TEST_TABLE;
CREATE TABLE TEST_TABLE 
(
      ROWUID VARCHAR2(255 BYTE) 
    , USERID VARCHAR2(225 BYTE) 
    , USERNAME VARCHAR2(255 BYTE) 
    , MAIL VARCHAR2(255 BYTE) 
    , ROLE_ID VARCHAR2(255 BYTE) 
    , ROLE_NAME VARCHAR2(225 BYTE) 
    , VERSION_DATE TIMESTAMP(6) WITH TIME ZONE NOT NULL 
    , VERSION_NO int
);

COMMENT ON COLUMN TEST_TABLE.ROWUID IS '主键';
COMMENT ON COLUMN TEST_TABLE.USERID IS '用户编号';
COMMENT ON COLUMN TEST_TABLE.USERNAME IS '名字';
COMMENT ON COLUMN TEST_TABLE.MAIL IS '邮箱';
COMMENT ON COLUMN TEST_TABLE.ROLE_ID IS '角色主键';
COMMENT ON COLUMN TEST_TABLE.ROLE_NAME IS '角色名';
COMMENT ON COLUMN TEST_TABLE.VERSION_DATE IS '版本事件';
COMMENT ON COLUMN TEST_TABLE.VERSION_NO IS '版本';
COMMENT ON TABLE TEST_TABLE IS 'TEST_TABLE';

主键

ALTER TABLE TEST_TABLE ADD CONSTRAINT PK_TEST_TABLE PRIMARY KEY (ROWUID);

索引

CREATE INDEX INDEX_TEST_TABLE_USERID ON TEST_TABLE (USERID);

视图

CREATE OR REPLACE VIEW V_TEST_TABLE AS 
SELECT * FROM TEST_TABLE
;
COMMENT ON COLUMN V_TEST_TABLE.ROWUID IS '主键';
COMMENT ON COLUMN V_TEST_TABLE.USERID IS '用户编号';
COMMENT ON COLUMN V_TEST_TABLE.USERNAME IS '名字';
COMMENT ON COLUMN V_TEST_TABLE.MAIL IS '邮箱';
COMMENT ON COLUMN V_TEST_TABLE.ROLE_ID IS '角色主键';
COMMENT ON COLUMN V_TEST_TABLE.ROLE_NAME IS '角色名';
COMMENT ON COLUMN V_TEST_TABLE.VERSION_DATE IS '版本事件';
COMMENT ON COLUMN V_TEST_TABLE.VERSION_NO IS '版本';
COMMENT ON TABLE V_TEST_TABLE IS 'TEST_TABLE VIEW';

物化视图

CREATE MATERIALIZED VIEW MV_TEST_TABLE
build deferred
REFRESH COMPLETE ON DEMAND 
NEXT SYSDATE + 1 
AS SELECT * FROM V_TEST_TABLE

;  

授权

grant select on TEST_TABLE to XXX WITH GRANT OPTION;
grant select on V_TEST_TABLE to XXX WITH GRANT OPTION;
grant select on MV_TEST_TABLE to XXX WITH GRANT OPTION;

 修改表名

alter table TABLE_NAME_OLD rename to TABLE_NAME_NEW;

修改视图名

rename TABLE_NAME_OLD  to TABLE_NAME_NEW;

 

字段要求：

1.数字 & 字母 & 下划线 AND 只能英文开头
2.字段长度不能超过30
3.不能使用以下关键字：
DATE | LONG | DECIMAL | NUMBER | RAW | VARCHAR2 | CHAR | NULL | COLUMN |
CREATE| DELETE | UPDATE | SELECT | INDEX |
VIEW | TABLE | ORDER | BY | ASC | DESC | COMMENT |
IS | AS | ON | BY | NOT | FROM | IN | THEN | OR | AND

LOCK |