oracle建表语句,包括创建索引,赋权,主键自增

朱雀 2022-02-15 13:19 141阅读 0赞

\-- 建表     
create table tbl\_person  
(  
  id                     NUMBER not null,  
  name      VARCHAR2(100),  
  sex      INTEGER,  
createtime      VARCHAR2(100)  
  updatetime               VARCHAR2(100)  
)

\--表空间参数  
tablespace HAHA  
  pctfree 10  
  initrans 1  
  maxtrans 255  
  storage  
  (  
    initial 64K  
    minextents 1  
    maxextents unlimited  
  );  
\-- 添加注释   
comment on table ADMIN\_DEV.tbl\_person  
  is '人员表';  
\-- Add comments to the columns   
comment on column ADMIN\_DEV.tbl\_person.id        
  is '主键id';

comment on column ADMIN\_DEV.tbl\_person.name        
  is '姓名';  
comment on column ADMIN\_DEV.tbl\_person.sex        
  is '性别';  
comment on column ADMIN\_DEV.tbl\_person.createtime        
  is '创建时间';  
  comment on column ADMIN\_DEV.tbl\_person.updatetime                
  is '更新时间';  
\-- Create/Recreate primary, unique and foreign key constraints

\--创建约束  
alter table tbl\_person  
  add constraint tbl\_person primary key (ID)  
  using index   
  tablespace OAPP\_INDEX  
  pctfree 10  
  initrans 2  
  maxtrans 255;

\--创建自增id

CREATE SEQUENCE tbl\_person\_ID MINVALUE 1 MAXVALUE 999999999999999999999999999 START WITH 1 INCREMENT BY 1 CACHE 20;

\--创建索引  
  Create Index tbl\_person on tbl\_person(name);

\--创建别名  
CREATE SYNONYM ADMIN\_DEV\_R.tbl\_person FOR ADMIN\_DEV.tbl\_person;

CREATE SYNONYM ADMIN\_DEV\_W.tbl\_person FOR ADMIN\_DEV.tbl\_person;

CREATE SYNONYM ADMIN\_DEV\_W.tbl\_person\_ID FOR ADMIN\_DEV.tbl\_person\_ID;

\-- 赋权  
grant select on ADMIN\_DEV.FNA\_REVENUE\_BUDGET to ADMIN\_DEV\_R;  
 GRANT UPDATE ON "ADMIN\_DEV"."tbl\_person" TO "ADMIN\_DEV\_W";  
  GRANT SELECT ON "ADMIN\_DEV"."tbl\_person" TO "ADMIN\_DEV\_W";  
  GRANT INSERT ON "ADMIN\_DEV"."tbl\_person" TO "ADMIN\_DEV\_W";  
  GRANT DELETE ON "ADMIN\_DEV"."tbl\_person" TO "ADMIN\_DEV\_W";  
  GRANT ALTER ON "ADMIN\_DEV"."tbl\_person" TO "ADMIN\_DEV\_W";