使用说明
此模板适用于已经建好的数据库和表想导出重复执行的情况导出的时候建议不要使用Navicat工具,这样会导出很多无用的代码此模板包括序列、表结构、触发器、注释使用时替换标明eam_roleandmenu即可未完待续……--创建序列eam_roleandmenuDECLAREs_count NUMBER;BEGINSELECT COUNT (1)INTO s_countFROM user_sequencesWHERE sequence_name = UPPER ('seq_eam_roleandmenu');IF s_count = 0THENEXECUTE IMMEDIATE 'CREATE SEQUENCE seq_eam_roleandmenuINCREMENT BY 1START WITH 1MINVALUE 1MAXVALUE 99999999999NOCYCLENOORDERNOCACHE';END IF;END;/-------创建eam_roleandmenu表DECLAREs_count NUMBER;BEGINSELECT COUNT (1)INTO s_countFROM user_tablesWHERE table_name = UPPER ('eam_roleandmenu');IF s_count = 0THENEXECUTE IMMEDIATE 'CREATE TABLE eam_roleandmenu(id NUMBER ,rolecode NUMBER NOT NULL,menuidVARCHAR2(8 BYTE) NOT NULL,empidNUMBER NOT NULL,dpid NUMBER NOT NULL,create_dateDATE DEFAULT SYSDATE NOT NULL,update_dateDATE DEFAULT SYSDATE NOT NULL)';END IF;END;/-------创建eam_roleandmenu主键DECLAREnum NUMBER;BEGINSELECT COUNT (1)INTO numFROM user_constraints aWHERE a.constraint_name = UPPER ('pk_eam_roleandmenu');IF num = 0THENEXECUTE IMMEDIATE 'ALTER TABLE eam_roleandmenuADD CONSTRAINT pk_eam_roleandmenu PRIMARY KEY (id)USING INDEX';END IF;END;/-------创建eam_class_style触发器CREATE OR REPLACE TRIGGER trg_eam_roleandmenuBEFORE INSERTON eam_roleandmenuREFERENCING NEW AS new OLD AS oldFOR EACH ROWDECLAREintegrity_error EXCEPTION;errno INTEGER;errmsg CHAR (200);BEGINIF :new.id IS NULL OR :new.id <= 0THENSELECT seq_eam_roleandmenu.NEXTVAL INTO :new.id FROM DUAL;END IF;-- Errors handlingEXCEPTIONWHEN integrity_errorTHENraise_application_error (errno, errmsg);END;/-- Comments for eam_roleandmenuCOMMENT ON TABLE eam_roleandmenu IS '角色菜单表'/COMMENT ON COLUMN eam_roleandmenu.create_date IS '创建时间'/COMMENT ON COLUMN eam_roleandmenu.dpid IS '客户代码'/COMMENT ON COLUMN eam_roleandmenu.empid IS '职员ID'/COMMENT ON COLUMN eam_roleandmenu.id IS '编号,自动增长'/COMMENT ON COLUMN eam_roleandmenu.menuid IS '菜单ID'/COMMENT ON COLUMN eam_roleandmenu.rolecode IS '角色code'/COMMENT ON COLUMN eam_roleandmenu.update_date IS '更新时间'/
如果觉得《Oracle数据库可重复执行脚本整理方法》对你有帮助,请点赞、收藏,并留下你的观点哦!