• 创建 Table、Synonym

创建 Table、Synonym

-- Create table
create table PUBDATA.DELIVERY_ADDRESS_BOOK
(
  address      VARCHAR2(500) not null,
  address_code INTEGER not null,
  created_user VARCHAR2(100) not null,
  created_date DATE not null,
  updated_user VARCHAR2(100) not null,
  updated_date DATE not null
);

-- Add comments to the table 
comment on table PUBDATA.DELIVERY_ADDRESS_BOOK
  is '保单收件地址 机构和部门 的地址薄';
-- Add comments to the columns 
comment on column PUBDATA.DELIVERY_ADDRESS_BOOK.address
  is '地址';
comment on column PUBDATA.DELIVERY_ADDRESS_BOOK.address_code
  is '地址编码';
comment on column PUBDATA.DELIVERY_ADDRESS_BOOK.created_user
  is '创建人';
comment on column PUBDATA.DELIVERY_ADDRESS_BOOK.created_date
  is '创建时间';
comment on column PUBDATA.DELIVERY_ADDRESS_BOOK.updated_user
  is '更新人';
comment on column PUBDATA.DELIVERY_ADDRESS_BOOK.updated_date
  is '更新时间';
  
-- Create/Recreate indexes 
create index PUBDATA.IX_DELIVERY_ADDRESS_BOOK_1 on PUBDATA.DELIVERY_ADDRESS_BOOK (address);
create index PUBDATA.IX_DELIVERY_ADDRESS_BOOK_2 on PUBDATA.DELIVERY_ADDRESS_BOOK (address_code);

-- Create/Recreate primary, unique and foreign key constraints 
alter table PUBDATA.DELIVERY_ADDRESS_BOOK
  add constraint PK_DELIVERY_ADDRESS_BOOK primary key (address, address_code);


-- Grant/Revoke object privileges 
grant select, insert, update, delete on PUBDATA.DELIVERY_ADDRESS_BOOK to NBUOPR;
grant select, insert, update, delete on PUBDATA.DELIVERY_ADDRESS_BOOK to NBUCDE;

-- Create synonym
CREATE PUBLIC SYNONYM DELIVERY_ADDRESS_BOOK FOR PUBDATA.DELIVERY_ADDRESS_BOOK;