/* Navicat Premium Data Transfer Source Server : 172.18.32.35 hgtest Source Server Type : Oracle Source Server Version : 110200 Source Host : 172.18.32.35:1521 Source Schema : HGTEST Target Server Type : Oracle Target Server Version : 110200 File Encoding : 65001 Date: 01/06/2020 11:16:17 */ -- ---------------------------- -- Table structure for TP_MST_WORKSTATION -- ---------------------------- DROP TABLE "HGTEST"."TP_MST_WORKSTATION"; CREATE TABLE "HGTEST"."TP_MST_WORKSTATION" ( "WORKSTATIONID" NUMBER NOT NULL, "WORKSTATIONTYPEID" NUMBER NOT NULL, "WORKSTATIONNAME" NVARCHAR2(32) NOT NULL, "PROCEDUREID" NUMBER, "GROUTINGLINEID" NUMBER, "USERCODE" NVARCHAR2(32), "PCCODE" NVARCHAR2(64), "PLCIP" NVARCHAR2(32), "PLCPORT" NUMBER, "PLCOBJECT" NVARCHAR2(128), "DISPLAYNO" NUMBER, "REMARKS" NVARCHAR2(128), "ACCOUNTID" NUMBER NOT NULL, "VALUEFLAG" CHAR(1 BYTE) DEFAULT 1 NOT NULL, "CREATETIME" DATE DEFAULT sysdate NOT NULL, "CREATEUSERID" NUMBER NOT NULL, "UPDATETIME" DATE DEFAULT sysdate NOT NULL, "UPDATEUSERID" NUMBER NOT NULL, "OPTIMESTAMP" TIMESTAMP(6) DEFAULT systimestamp NOT NULL, "UJOBSID" NUMBER NOT NULL ) TABLESPACE "USERS" LOGGING NOCOMPRESS PCTFREE 10 INITRANS 1 STORAGE ( INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 BUFFER_POOL DEFAULT ) PARALLEL 1 NOCACHE DISABLE ROW MOVEMENT ; COMMENT ON COLUMN "HGTEST"."TP_MST_WORKSTATION"."WORKSTATIONID" IS '工位ID'; COMMENT ON COLUMN "HGTEST"."TP_MST_WORKSTATION"."WORKSTATIONTYPEID" IS '工位类型ID'; COMMENT ON COLUMN "HGTEST"."TP_MST_WORKSTATION"."WORKSTATIONNAME" IS '工位名称'; COMMENT ON COLUMN "HGTEST"."TP_MST_WORKSTATION"."PROCEDUREID" IS '工序ID'; COMMENT ON COLUMN "HGTEST"."TP_MST_WORKSTATION"."GROUTINGLINEID" IS '成型线ID'; COMMENT ON COLUMN "HGTEST"."TP_MST_WORKSTATION"."USERCODE" IS '当前工号'; COMMENT ON COLUMN "HGTEST"."TP_MST_WORKSTATION"."PCCODE" IS '工位PC识别码'; COMMENT ON COLUMN "HGTEST"."TP_MST_WORKSTATION"."PLCIP" IS 'PLCIP地址'; COMMENT ON COLUMN "HGTEST"."TP_MST_WORKSTATION"."PLCPORT" IS 'PLC端口'; COMMENT ON COLUMN "HGTEST"."TP_MST_WORKSTATION"."PLCOBJECT" IS 'PLC对象'; COMMENT ON COLUMN "HGTEST"."TP_MST_WORKSTATION"."DISPLAYNO" IS '显示顺序'; COMMENT ON COLUMN "HGTEST"."TP_MST_WORKSTATION"."REMARKS" IS '备注'; COMMENT ON COLUMN "HGTEST"."TP_MST_WORKSTATION"."ACCOUNTID" IS '帐套ID (所属帐套ID)'; COMMENT ON COLUMN "HGTEST"."TP_MST_WORKSTATION"."VALUEFLAG" IS '有效标识 (1：正常 0：停用)'; COMMENT ON COLUMN "HGTEST"."TP_MST_WORKSTATION"."CREATETIME" IS '创建时间 (更新时，不更新该数据)'; COMMENT ON COLUMN "HGTEST"."TP_MST_WORKSTATION"."CREATEUSERID" IS '创建数据操作员ID (更新时，不更新该数据)'; COMMENT ON COLUMN "HGTEST"."TP_MST_WORKSTATION"."UPDATETIME" IS '更新时间 (新建时，取新建时间；更新时，取最新时间更新)'; COMMENT ON COLUMN "HGTEST"."TP_MST_WORKSTATION"."UPDATEUSERID" IS '更新数据操作员ID (新建时，取创建者ID；更新时，取更新者ID)'; COMMENT ON COLUMN "HGTEST"."TP_MST_WORKSTATION"."OPTIMESTAMP" IS '操作时间戳 (防止多用户同时对单一记录操作的时间戳)'; COMMENT ON COLUMN "HGTEST"."TP_MST_WORKSTATION"."UJOBSID" IS '工种ID (工号中包含的工种（Jobs）)'; COMMENT ON TABLE "HGTEST"."TP_MST_WORKSTATION" IS '工位配置表'; -- ---------------------------- -- Primary Key structure for table TP_MST_WORKSTATION -- ---------------------------- ALTER TABLE "HGTEST"."TP_MST_WORKSTATION" ADD CONSTRAINT "PK_MST_WORKSTATION" PRIMARY KEY ("WORKSTATIONID"); -- ---------------------------- -- Checks structure for table TP_MST_WORKSTATION -- ---------------------------- ALTER TABLE "HGTEST"."TP_MST_WORKSTATION" ADD CONSTRAINT "SYS_C0016275" CHECK ("WORKSTATIONID" IS NOT NULL) NOT DEFERRABLE INITIALLY IMMEDIATE NORELY VALIDATE; ALTER TABLE "HGTEST"."TP_MST_WORKSTATION" ADD CONSTRAINT "SYS_C0016276" CHECK ("WORKSTATIONTYPEID" IS NOT NULL) NOT DEFERRABLE INITIALLY IMMEDIATE NORELY VALIDATE; ALTER TABLE "HGTEST"."TP_MST_WORKSTATION" ADD CONSTRAINT "SYS_C0016277" CHECK ("WORKSTATIONNAME" IS NOT NULL) NOT DEFERRABLE INITIALLY IMMEDIATE NORELY VALIDATE; ALTER TABLE "HGTEST"."TP_MST_WORKSTATION" ADD CONSTRAINT "SYS_C0016278" CHECK ("ACCOUNTID" IS NOT NULL) NOT DEFERRABLE INITIALLY IMMEDIATE NORELY VALIDATE; ALTER TABLE "HGTEST"."TP_MST_WORKSTATION" ADD CONSTRAINT "SYS_C0016279" CHECK ("VALUEFLAG" IS NOT NULL) NOT DEFERRABLE INITIALLY IMMEDIATE NORELY VALIDATE; ALTER TABLE "HGTEST"."TP_MST_WORKSTATION" ADD CONSTRAINT "SYS_C0016280" CHECK ("CREATETIME" IS NOT NULL) NOT DEFERRABLE INITIALLY IMMEDIATE NORELY VALIDATE; ALTER TABLE "HGTEST"."TP_MST_WORKSTATION" ADD CONSTRAINT "SYS_C0016281" CHECK ("CREATEUSERID" IS NOT NULL) NOT DEFERRABLE INITIALLY IMMEDIATE NORELY VALIDATE; ALTER TABLE "HGTEST"."TP_MST_WORKSTATION" ADD CONSTRAINT "SYS_C0016282" CHECK ("UPDATETIME" IS NOT NULL) NOT DEFERRABLE INITIALLY IMMEDIATE NORELY VALIDATE; ALTER TABLE "HGTEST"."TP_MST_WORKSTATION" ADD CONSTRAINT "SYS_C0016283" CHECK ("UPDATEUSERID" IS NOT NULL) NOT DEFERRABLE INITIALLY IMMEDIATE NORELY VALIDATE; ALTER TABLE "HGTEST"."TP_MST_WORKSTATION" ADD CONSTRAINT "SYS_C0016284" CHECK ("OPTIMESTAMP" IS NOT NULL) NOT DEFERRABLE INITIALLY IMMEDIATE NORELY VALIDATE; ALTER TABLE "HGTEST"."TP_MST_WORKSTATION" ADD CONSTRAINT "SYS_C0016805" CHECK ("UJOBSID" IS NOT NULL) NOT DEFERRABLE INITIALLY IMMEDIATE NORELY VALIDATE; ALTER TABLE "HGTEST"."TP_MST_WORKSTATION" ADD CONSTRAINT "SYS_C0020107" CHECK ("WORKSTATIONID" IS NOT NULL) NOT DEFERRABLE INITIALLY IMMEDIATE NORELY VALIDATE; ALTER TABLE "HGTEST"."TP_MST_WORKSTATION" ADD CONSTRAINT "SYS_C0020108" CHECK ("WORKSTATIONTYPEID" IS NOT NULL) NOT DEFERRABLE INITIALLY IMMEDIATE NORELY VALIDATE; ALTER TABLE "HGTEST"."TP_MST_WORKSTATION" ADD CONSTRAINT "SYS_C0020109" CHECK ("WORKSTATIONNAME" IS NOT NULL) NOT DEFERRABLE INITIALLY IMMEDIATE NORELY VALIDATE; ALTER TABLE "HGTEST"."TP_MST_WORKSTATION" ADD CONSTRAINT "SYS_C0020110" CHECK ("ACCOUNTID" IS NOT NULL) NOT DEFERRABLE INITIALLY IMMEDIATE NORELY VALIDATE; ALTER TABLE "HGTEST"."TP_MST_WORKSTATION" ADD CONSTRAINT "SYS_C0020111" CHECK ("VALUEFLAG" IS NOT NULL) NOT DEFERRABLE INITIALLY IMMEDIATE NORELY VALIDATE; ALTER TABLE "HGTEST"."TP_MST_WORKSTATION" ADD CONSTRAINT "SYS_C0020112" CHECK ("CREATETIME" IS NOT NULL) NOT DEFERRABLE INITIALLY IMMEDIATE NORELY VALIDATE; ALTER TABLE "HGTEST"."TP_MST_WORKSTATION" ADD CONSTRAINT "SYS_C0020113" CHECK ("CREATEUSERID" IS NOT NULL) NOT DEFERRABLE INITIALLY IMMEDIATE NORELY VALIDATE; ALTER TABLE "HGTEST"."TP_MST_WORKSTATION" ADD CONSTRAINT "SYS_C0020114" CHECK ("UPDATETIME" IS NOT NULL) NOT DEFERRABLE INITIALLY IMMEDIATE NORELY VALIDATE; ALTER TABLE "HGTEST"."TP_MST_WORKSTATION" ADD CONSTRAINT "SYS_C0020115" CHECK ("UPDATEUSERID" IS NOT NULL) NOT DEFERRABLE INITIALLY IMMEDIATE NORELY VALIDATE; ALTER TABLE "HGTEST"."TP_MST_WORKSTATION" ADD CONSTRAINT "SYS_C0020116" CHECK ("OPTIMESTAMP" IS NOT NULL) NOT DEFERRABLE INITIALLY IMMEDIATE NORELY VALIDATE; -- ---------------------------- -- Triggers structure for table TP_MST_WORKSTATION -- ---------------------------- CREATE TRIGGER "HGTEST"."TRG_MST_WORKSTATION_B_I" BEFORE INSERT ON "HGTEST"."TP_MST_WORKSTATION" REFERENCING OLD AS "OLD" NEW AS "NEW" FOR EACH ROW DECLARE BEGIN IF :NEW.WORKSTATIONID IS NULL THEN SELECT SEQ_MST_WORKSTATION_ID.NEXTVAL INTO :NEW.WORKSTATIONID FROM DUAL; END IF; END TRG_MST_WORKSTATION_B_I; / CREATE TRIGGER "HGTEST"."TRG_MST_WORKSTATION_B_U" BEFORE UPDATE ON "HGTEST"."TP_MST_WORKSTATION" REFERENCING OLD AS "OLD" NEW AS "NEW" FOR EACH ROW DECLARE BEGIN SELECT SYSDATE INTO :NEW.UPDATETIME FROM DUAL; SELECT SYSTIMESTAMP INTO :NEW.OPTIMESTAMP FROM DUAL; END TRG_MST_WORKSTATION_B_U; / -- Create sequence create sequence SEQ_MST_WORKSTATION_ID minvalue 1 maxvalue 9999999999999999999999999999 start with 1 increment by 1 cache 20;