1 使用 C##PLATFORM 创建表

—- Create table

create table APS_ASSY_SCHEDULE

(

schedule_code VARCHAR2(20),

work_order_code VARCHAR2(100),

material_code VARCHAR2(50),

work_center_code VARCHAR2(50),

group_code VARCHAR2(50),

shifts_code VARCHAR2(100),

schedule_time DATE,

plan_start_time DATE,

plan_end_time DATE,

schedule_qty NUMBER(9),

flag NUMBER(1) default 1,

sn NUMBER not null,

mark_time TIMESTAMP(6) default systimestamp,

mark_date VARCHAR2(10) default to_char(sysdate,‘yyyy-mm-dd’),

uda1 VARCHAR2(50),

uda2 VARCHAR2(50),

uda3 VARCHAR2(50),

uda4 VARCHAR2(100),

uda5 VARCHAR2(100)

)

;

– Add comments to the table

comment on table APS_ASSY_SCHEDULE

is ‘组装、喷涂工单排程表’;

– Add comments to the columns

comment on column APS_ASSY_SCHEDULE.schedule_code

is ‘排程编号计算字段，APS 中没有排程编号，但可以使用工单号、排程日期、产线编号、班次唯一确定一个排程。’;

comment on column APS_ASSY_SCHEDULE.work_order_code

is ‘工单号’;

comment on column APS_ASSY_SCHEDULE.material_code

is ‘物料编号’;

comment on column APS_ASSY_SCHEDULE.work_center_code

is ‘产线编号’;

comment on column APS_ASSY_SCHEDULE.group_code

is ‘班组编号’;

comment on column APS_ASSY_SCHEDULE.shifts_code

is ‘班次’;

comment on column APS_ASSY_SCHEDULE.schedule_time

is ‘排程日期’;

comment on column APS_ASSY_SCHEDULE.plan_start_time

is ‘计划开始时间’;

comment on column APS_ASSY_SCHEDULE.plan_end_time

is ‘计划完成时间’;

comment on column APS_ASSY_SCHEDULE.schedule_qty

is ‘排程数量’;

comment on column APS_ASSY_SCHEDULE.flag

is ‘操作标识：1- 新增；2- 修改；3- 删除’;

comment on column APS_ASSY_SCHEDULE.sn

is ‘序号’;

comment on column APS_ASSY_SCHEDULE.mark_time

is ‘时间戳’;

comment on column APS_ASSY_SCHEDULE.mark_date

is ‘日期’;

comment on column APS_ASSY_SCHEDULE.uda1

is ‘预留字段 1’;

comment on column APS_ASSY_SCHEDULE.uda2

is ‘预留字段 2’;

comment on column APS_ASSY_SCHEDULE.uda3

is ‘预留字段 3’;

– Create/Recreate primary, unique and foreign key constraints

alter table APS_ASSY_SCHEDULE

add constraint PK_APS_AS primary key (SN)

;

– Create/Recreate indexes

create index I_APS_AS1 on APS_ASSY_SCHEDULE (MARK_TIME)

;

create index I_APS_AS2 on APS_ASSY_SCHEDULE (MARK_DATE)

;

2 实现自增主键

– Create sequence

create sequence SEQ_APS_ASSY_SCHEDULE

minvalue 1

maxvalue 99999999999999999999999

start with 1

increment by 1

cache 20000;

create or replace trigger tg_APS_ASSY_SCHEDULE

before insert on APS_ASSY_SCHEDULE for each row

begin

select SEQ_APS_ASSY_SCHEDULE.nextval into :new.sn from dual;

end tg_APS_ASSY_SCHEDULE;

3 创建用户并授权

create user C##APS identified by APS123;

GRANT connect,resource to C##APS;

4 表授权

– Grant/Revoke object privileges

grant select, insert on c##platform.APS_ASSY_SCHEDULE to C##APS;

create synonym C##APS.APS_ASSY_SCHEDULE for C##PLATFORM.APS_ASSY_SCHEDULE;