Oracle数据库可重复执行脚本整理方法

最新推荐文章于 2024-01-05 10:29:50 发布

赛尔号-

最新推荐文章于 2024-01-05 10:29:50 发布

阅读量477

点赞数

分类专栏：数据库文章标签： Oracle脚本重复执行

本文链接：https://blog.csdn.net/qq_36811160/article/details/102743028

版权

数据库专栏收录该内容

12 篇文章 0 订阅

订阅专栏

使用说明

此模板适用于已经建好的数据库和表想导出重复执行的情况
导出的时候建议不要使用Navicat工具，这样会导出很多无用的代码
此模板包括序列、表结构、触发器、注释
使用时替换标明eam_roleandmenu即可
未完待续……

--创建序列eam_roleandmenu

DECLARE
    s_count   NUMBER;
BEGIN
    SELECT COUNT (1)
    INTO s_count
    FROM user_sequences
    WHERE sequence_name = UPPER ('seq_eam_roleandmenu');

    IF s_count = 0
    THEN
        EXECUTE IMMEDIATE '
               CREATE SEQUENCE seq_eam_roleandmenu
  INCREMENT BY 1
  START WITH 1
  MINVALUE 1
  MAXVALUE 99999999999
  NOCYCLE
  NOORDER
  NOCACHE
    ';
    END IF;
END;
/

-------创建eam_roleandmenu表

DECLARE
    s_count   NUMBER;
BEGIN
    SELECT COUNT (1)
    INTO s_count
    FROM user_tables
    WHERE table_name = UPPER ('eam_roleandmenu');

    IF s_count = 0
    THEN
        EXECUTE IMMEDIATE 'CREATE TABLE eam_roleandmenu
					(id                             NUMBER ,
					rolecode                       NUMBER NOT NULL,
					menuid                         VARCHAR2(8 BYTE) NOT NULL,
					empid                          NUMBER NOT NULL,
					dpid                           NUMBER NOT NULL,
					create_date                    DATE DEFAULT SYSDATE NOT NULL,
					update_date                    DATE DEFAULT SYSDATE NOT NULL)';
    END IF;
END;
/

-------创建eam_roleandmenu主键

DECLARE
    num   NUMBER;
BEGIN
    SELECT COUNT (1)
    INTO num
    FROM user_constraints a
    WHERE a.constraint_name = UPPER ('pk_eam_roleandmenu');

    IF num = 0
    THEN
        EXECUTE IMMEDIATE 'ALTER TABLE eam_roleandmenu
ADD CONSTRAINT pk_eam_roleandmenu PRIMARY KEY (id)
USING INDEX';
    END IF;
END;
/

-------创建eam_class_style触发器

CREATE OR REPLACE TRIGGER trg_eam_roleandmenu
    BEFORE INSERT
    ON eam_roleandmenu
    REFERENCING NEW AS new OLD AS old
    FOR EACH ROW
DECLARE
    integrity_error   EXCEPTION;
    errno             INTEGER;
    errmsg            CHAR (200);
BEGIN
    IF :new.id IS NULL OR :new.id <= 0
    THEN
        SELECT seq_eam_roleandmenu.NEXTVAL INTO :new.id FROM DUAL;
    END IF;
--  Errors handling
EXCEPTION
    WHEN integrity_error
    THEN
        raise_application_error (errno, errmsg);
END;
/

-- Comments for eam_roleandmenu
COMMENT 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 '更新时间'
/