HEGII.ss.mes.web/wwwroot/sqlupdate/工位管理/TP_MST_WORKSTATION.sql

/*
 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;