记一次生产事故 ORACLE初始化脚本可重复执行

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

喜羊羊love红太狼

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

阅读量218

点赞数

分类专栏：数据库文章标签： oracle 数据库 sql

本文链接：https://blog.csdn.net/qq_38423256/article/details/127577201

版权

数据库专栏收录该内容

29 篇文章 0 订阅

订阅专栏

背景：最近有个地方项目升级，将发出去的初始库脚本执行了一次，生产数据全删了。

初始库脚本安全性

第一：能够可重复执行

第二：生产环境执行了初始化脚本不会影响生产库数据

oracle初始库脚本

declare  num number;
 begin
 select count(1) into num from user_tables where table_name=upper('ACT_BIZ_PARATRANSFER') ;
 if num = 0  then
 execute immediate 
 'CREATE TABLE GISQBPM.ACT_BIZ_PARATRANSFER (
  ID NVARCHAR2(64) NOT NULL,
  TASKID NVARCHAR2(64),
  PROCESS_INST_ID NVARCHAR2(64),
  TASK_NAME NVARCHAR2(64),
  TASK_LINK NVARCHAR2(32),
  ASSIGNEE NVARCHAR2(32),
  TRANSFER_ASSIGNEE NVARCHAR2(32),
  TRANSFER_DATE TIMESTAMP(6),
  REV_ NUMBER
)';
 end if;
  execute immediate 'COMMENT ON COLUMN GISQBPM.ACT_BIZ_PARATRANSFER.ID IS ''主键id''';
  execute immediate 'COMMENT ON COLUMN GISQBPM.ACT_BIZ_PARATRANSFER.TASKID IS ''任务id''';
  execute immediate 'COMMENT ON COLUMN GISQBPM.ACT_BIZ_PARATRANSFER.PROCESS_INST_ID IS ''流程实例id''';
  execute immediate 'COMMENT ON COLUMN GISQBPM.ACT_BIZ_PARATRANSFER.TASK_NAME IS ''任务名称''';
  execute immediate 'COMMENT ON COLUMN GISQBPM.ACT_BIZ_PARATRANSFER.TASK_LINK IS ''转交任务环节''';
  execute immediate 'COMMENT ON COLUMN GISQBPM.ACT_BIZ_PARATRANSFER.ASSIGNEE IS ''原始持有任务用户''';
  execute immediate 'COMMENT ON COLUMN GISQBPM.ACT_BIZ_PARATRANSFER.TRANSFER_ASSIGNEE IS ''转交人''';
  execute immediate 'COMMENT ON COLUMN GISQBPM.ACT_BIZ_PARATRANSFER.TRANSFER_DATE IS ''转交日期(yyy)''';
  execute immediate 'COMMENT ON TABLE GISQBPM.ACT_BIZ_PARATRANSFER IS ''平行移交记录表''';
 end;
 /
-- ----------------------------
-- Table structure for ACT_BIZ_PROCDEF
-- ----------------------------
declare  num number;
 begin
 select count(1) into num from user_tables where table_name=upper('ACT_BIZ_PROCDEF') ;
 if num = 0  then
 execute immediate 
'CREATE TABLE GISQBPM.ACT_BIZ_PROCDEF (
  ID_ NVARCHAR2(128) NOT NULL,
  REV_ NUMBER,
  PROC_DEF_ID_ NVARCHAR2(128) NOT NULL,
  FORM_KEY_ VARCHAR2(2048 BYTE),
  PROC_CODE_ NVARCHAR2(128),
  SERIAL_RULE_ NVARCHAR2(255),
  PROC_DEF_KEY_ NVARCHAR2(255),
  USER_NAME_ NVARCHAR2(128)
)';
 end if;
	execute immediate 'COMMENT ON COLUMN GISQBPM.ACT_BIZ_PROCDEF.ID_ IS ''主键''';
	execute immediate 'COMMENT ON COLUMN GISQBPM.ACT_BIZ_PROCDEF.REV_ IS ''版本号''';
	execute immediate 'COMMENT ON COLUMN GISQBPM.ACT_BIZ_PROCDEF.PROC_DEF_ID_ IS ''流程定义id''';
	execute immediate 'COMMENT ON COLUMN GISQBPM.ACT_BIZ_PROCDEF.FORM_KEY_ IS ''表单键值''';
	execute immediate 'COMMENT ON COLUMN GISQBPM.ACT_BIZ_PROCDEF.PROC_CODE_ IS ''流程代码''';
	execute immediate 'COMMENT ON COLUMN GISQBPM.ACT_BIZ_PROCDEF.SERIAL_RULE_ IS ''编号规则''';
	execute immediate 'COMMENT ON COLUMN GISQBPM.ACT_BIZ_PROCDEF.PROC_DEF_KEY_ IS ''流程定义键值''';
	execute immediate 'COMMENT ON COLUMN GISQBPM.ACT_BIZ_PROCDEF.USER_NAME_ IS ''用户名''';
	execute immediate 'COMMENT ON TABLE GISQBPM.ACT_BIZ_PROCDEF IS ''业务流程定义表''';
 end;
/

注意：oracle注意 / 分割符