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 |