常用oracle可重复执行的脚本模板

为保证脚本的可重复执行以及丢失,涉及到数据库环境的移植等,就会使用可重复执行脚本,此处仅提供相关一些模板

说明下:该脚本需要在命令窗口执行,而不是在SQL窗口执行

创建序的脚本

 1 /**
 2  *  作者:zkongbai
 3  *  时间:11-30 09:22:10
 4  *  描述:创建序列【SEQ_ZZ_DOUBLE_RANDOM_TASK】
 5  *  脚本可重复执行
 6  */
 7 PROMPT SEQ_ZZ_DOUBLE_RANDOM_TASK  双随机事件脚本 创建序列
 8 DECLARE
 9     FLAG_NUM NUMBER;
10 BEGIN
11     SELECT COUNT(*) INTO FLAG_NUM
12     FROM USER_SEQUENCES ASQ
13     WHERE ASQ.SEQUENCE_NAME = UPPER('SEQ_ZZ_DOUBLE_RANDOM_TASK');
14     IF FLAG_NUM = 0 THEN 
15         EXECUTE IMMEDIATE 'CREATE SEQUENCE SEQ_ZZ_DOUBLE_RANDOM_TASK MINVALUE 1 MAXVALUE 999999999 START WITH 1 INCREMENT BY 1 CACHE 20'; 
16     END IF;
17 END;
18 /
19 COMMIT;

创建表的脚本

 1 /**
 2  *  作者:zkongbai
 3  *  时间:11-21 09:22:10
 4  *  描述:创建表【双随机任务,序:SEQ_ZZ_DOUBLE_RANDOM_TASK】
 5  *  脚本可重复执行
 6  */
 7 PROMPT T_ZZ_DOUBLE_RANDOM_TASK 创建表
 8 DECLARE
 9     FLAG_NUM NUMBER;
10 BEGIN
11     SELECT COUNT(*) INTO FLAG_NUM
12     FROM USER_TABLES ATS
13     WHERE ATS.TABLE_NAME = UPPER('T_ZZ_DOUBLE_RANDOM_TASK');
14     IF FLAG_NUM = 0 THEN
15         EXECUTE IMMEDIATE 'CREATE TABLE T_ZZ_DOUBLE_RANDOM_TASK
16             (
17                 ID              NUMBER(8) not null,
18                 EVENT_ID        NUMBER(8),
19                 JUDGE_COMMENT   VARCHAR2(500),
20                 JUDGE_RESULE    CHAR(1),
21                 JUDGE_PERSON    NUMBER(8),
22                 CREATE_TIME     DATE,
23                 STATUS          CHAR(1),
24                 UPDATE_TIME     DATE,
25                 CREATE_USER_ID  NUMBER(8),
26                 CREATE_ORG_CODE VARCHAR2(32),
27                 CONSTRAINT PK_T_ZZ_DOUBLE_RANDOM_TASK PRIMARY KEY (ID)
28             )';
29         -- ADD COMMENTS TO THE TABLE 
30         EXECUTE IMMEDIATE 'COMMENT ON TABLE T_ZZ_DOUBLE_RANDOM_TASK IS ''双随机任务,序:SEQ_ZZ_DOUBLE_RANDOM_TASK'''; 
31         -- ADD COMMENTS TO THE COLUMNS 
32         EXECUTE IMMEDIATE 'COMMENT ON COLUMN T_ZZ_DOUBLE_RANDOM_TASK.EVENT_ID  IS ''事件ID''';
33         EXECUTE IMMEDIATE 'COMMENT ON COLUMN T_ZZ_DOUBLE_RANDOM_TASK.JUDGE_COMMENT  IS ''评价意见''';
34         EXECUTE IMMEDIATE 'COMMENT ON COLUMN T_ZZ_DOUBLE_RANDOM_TASK.JUDGE_RESULE  IS ''评价结果(1:合格,2:不合格)''';
35         EXECUTE IMMEDIATE 'COMMENT ON COLUMN T_ZZ_DOUBLE_RANDOM_TASK.JUDGE_PERSON  IS ''评价人''';
36         EXECUTE IMMEDIATE 'COMMENT ON COLUMN T_ZZ_DOUBLE_RANDOM_TASK.STATUS  IS ''状态.1:有效,0:无效''';
37         EXECUTE IMMEDIATE 'COMMENT ON COLUMN T_ZZ_DOUBLE_RANDOM_TASK.CREATE_USER_ID  IS ''创建人''';
38         EXECUTE IMMEDIATE 'COMMENT ON COLUMN T_ZZ_DOUBLE_RANDOM_TASK.CREATE_ORG_CODE  IS ''创建组织''';
39     END IF;
40 END;
41 /
42 COMMIT;

修改字段的脚本

 1 /**
 2  *  作者:zkongbai
 3  *  时间:2018/1/11
 4  *  描述:双随机任务添加字段 TASK_TYPE
 5  *  脚本可重复执行
 6  */
 7 PROMPT T_ZZ_DOUBLE_RANDOM_TASK 双随机任务添加字段 TASK_TYPE
 8 DECLARE
 9   FLAG_NUM NUMBER;
10 BEGIN
11   SELECT COUNT(*) INTO FLAG_NUM
12   FROM USER_TABLES ATS
13   WHERE ATS.TABLE_NAME = UPPER('T_ZZ_DOUBLE_RANDOM_TASK');
14   IF FLAG_NUM > 0 THEN
15     SELECT COUNT(*) INTO FLAG_NUM FROM USER_TAB_COLUMNS T 
16     WHERE T.TABLE_NAME = UPPER('T_ZZ_DOUBLE_RANDOM_TASK') AND T.COLUMN_NAME = UPPER('TASK_TYPE');
17     IF FLAG_NUM=0 THEN
18       EXECUTE IMMEDIATE 'ALTER TABLE T_ZZ_DOUBLE_RANDOM_TASK ADD TASK_TYPE CHAR(1)';
19       -- ADD COMMENTS TO THE COLUMNS 
20       EXECUTE IMMEDIATE 'COMMENT ON COLUMN T_ZZ_DOUBLE_RANDOM_TASK.TASK_TYPE  IS ''任务类型: 1或空:市级双随机任务,2:镇街级随机任务''';
21     END IF;
22   END IF;
23 END;
24 /
25 COMMIT;
原文地址:https://www.cnblogs.com/zkongbai/p/8268770.html