工作中经常要写oracle脚本,而且需要重复执行,因此在此总结一下常用的可重复性脚本的写法,以下都是通过举例子说明。
- --1、创建表
- DECLARE
- VC_STR VARCHAR2(5000);
- VN_COUNT NUMBER;
- BEGIN
- SELECT COUNT(*) INTO VN_COUNT
- FROM USER_TABLES
- WHERE TABLE_NAME = 'TB_BALANCE';
- --如果没有则新增表,如果有就不处理
- IF VN_COUNT < 1 THEN
- VC_STR := 'create table TB_BALANCE (
- ID NUMBER not null,
- BILL_TYPE CHAR(1),
- CORP_SCALE_CODE CHAR(4),
- INDUSTRY_CODE VARCHAR2(8),
- BALANCE NUMBER(18,2),
- END_DATE DATE
- )';
- EXECUTE IMMEDIATE VC_STR;
- END IF;
- END;
- /
-
- -- 2增加表字段
-
- DECLARE
- VN_COUNT NUMBER;
- V_STR VARCHAR2(1000);
- BEGIN
- select COUNT(*)
- INTO VN_COUNT
- from USER_TAB_COLUMNS
- where table_name = 'TB_BALANCE' AND COLUMN_NAME = 'CUR_CODE';
- IF VN_COUNT < 1 THEN
- V_STR := ' alter table TB_BALANCEadd(CUR_CODE VARCHAR2(50) default ''01'' not null)';
- EXECUTE IMMEDIATE V_STR;
- END IF;
- END;
- /
-
- --3、判断表bis_acc_his_bal是否已经建有名为PK_BIS_ACC_HIS_BAL的主键,没有则新增
- DECLARE
- VN_COUNT NUMBER;
- BEGIN
- SELECT COUNT(*)
- INTO VN_COUNT
- FROM user_constraints WHERE constraint_type = 'P' and constraint_name = 'PK_BIS_ACC_HIS_BAL' and table_name = 'BIS_ACC_HIS_BAL';
- IF VN_COUNT < 1 THEN
- EXECUTE IMMEDIATE 'alter table bis_acc_his_bal add constraint PK_BIS_ACC_HIS_BAL primary key(BANK_ACC, BAL_DATE)';
- END IF;
- COMMIT;
- END;
- /
- --4、插入数据
- DECLARE
- VN_COUNT NUMBER;
- BEGIN
- SELECT COUNT(*) INTO VN_COUNT FROM TB_BALANCE WHERE FORMULA_CODE = 'GETTOPDKYE';
- IF VN_COUNT <1 THEN
- INSERT INTO TB_BALANCE(ID, FORMULA_NAME, HELP, FORMULA_CODE)VALUES
- (NVL((SELECT MAX(ID) + 1 FROM RPT1104_REPORT_FORMULA),1),
- 'GETTOPDKYE(****)',
- 'GETTOPDKYE(*****):<br>
- 参数1:贷款科目的代码<br>
- 参数2:数据项,1-10<br>
- 参数3:第几列;包括:客户名称,客户代码,最高风险额,贷款余额<br>
- 示例:GETTOPDKYE(1303,1,1)<br*********<br>',
- 'GETTOPDKYE'
- );
- END IF;
- COMMIT;
- END;
- /
- --5、判断表bis_acc_his_bal是否已经建有名为PK_BIS_ACC_HIS_BAL的索引,有则删除
- DECLARE
- VN_COUNT NUMBER;
- BEGIN
- SELECT COUNT(*)
- INTO VN_COUNT
- FROM user_indexes WHERE index_name = 'PK_BIS_ACC_HIS_BAL' and table_name = 'BIS_ACC_HIS_BAL';
- IF VN_COUNT >= 1 THEN
- EXECUTE IMMEDIATE 'drop index PK_BIS_ACC_HIS_BAL';
- END IF;
- COMMIT;
- END;
- /
- --1、创建表
- DECLARE
- VC_STR VARCHAR2(5000);
- VN_COUNT NUMBER;
- BEGIN
- SELECT COUNT(*) INTO VN_COUNT
- FROM USER_TABLES
- WHERE TABLE_NAME = 'TB_BALANCE';
- --如果没有则新增表,如果有就不处理
- IF VN_COUNT < 1 THEN
- VC_STR := 'create table TB_BALANCE (
- ID NUMBER not null,
- BILL_TYPE CHAR(1),
- CORP_SCALE_CODE CHAR(4),
- INDUSTRY_CODE VARCHAR2(8),
- BALANCE NUMBER(18,2),
- END_DATE DATE
- )';
- EXECUTE IMMEDIATE VC_STR;
- END IF;
- END;
- /
- -- 2增加表字段
- DECLARE
- VN_COUNT NUMBER;
- V_STR VARCHAR2(1000);
- BEGIN
- select COUNT(*)
- INTO VN_COUNT
- from USER_TAB_COLUMNS
- where table_name = 'TB_BALANCE' AND COLUMN_NAME = 'CUR_CODE';
- IF VN_COUNT < 1 THEN
- V_STR := ' alter table TB_BALANCEadd(CUR_CODE VARCHAR2(50) default ''01'' not null)';
- EXECUTE IMMEDIATE V_STR;
- END IF;
- END;
- /
- --3、判断表bis_acc_his_bal是否已经建有名为PK_BIS_ACC_HIS_BAL的主键,没有则新增
- DECLARE
- VN_COUNT NUMBER;
- BEGIN
- SELECT COUNT(*)
- INTO VN_COUNT
- FROM user_constraints WHERE constraint_type = 'P' and constraint_name = 'PK_BIS_ACC_HIS_BAL' and table_name = 'BIS_ACC_HIS_BAL';
- IF VN_COUNT < 1 THEN
- EXECUTE IMMEDIATE 'alter table bis_acc_his_bal add constraint PK_BIS_ACC_HIS_BAL primary key(BANK_ACC, BAL_DATE)';
- END IF;
- COMMIT;
- END;
- /
- --4、插入数据
- DECLARE
- VN_COUNT NUMBER;
- BEGIN
- SELECT COUNT(*) INTO VN_COUNT FROM TB_BALANCE WHERE FORMULA_CODE = 'GETTOPDKYE';
- IF VN_COUNT <1 THEN
- INSERT INTO TB_BALANCE(ID, FORMULA_NAME, HELP, FORMULA_CODE)VALUES
- (NVL((SELECT MAX(ID) + 1 FROM RPT1104_REPORT_FORMULA),1),
- 'GETTOPDKYE(****)',
- 'GETTOPDKYE(*****):<br>
- 参数1:贷款科目的代码<br>
- 参数2:数据项,1-10<br>
- 参数3:第几列;包括:客户名称,客户代码,最高风险额,贷款余额<br>
- 示例:GETTOPDKYE(1303,1,1)<br*********<br>',
- 'GETTOPDKYE'
- );
- END IF;
- COMMIT;
- END;
- /
- --5、判断表bis_acc_his_bal是否已经建有名为PK_BIS_ACC_HIS_BAL的索引,有则删除
- DECLARE
- VN_COUNT NUMBER;
- BEGIN
- SELECT COUNT(*)
- INTO VN_COUNT
- FROM user_indexes WHERE index_name = 'PK_BIS_ACC_HIS_BAL' and table_name = 'BIS_ACC_HIS_BAL';
- IF VN_COUNT >= 1 THEN
- EXECUTE IMMEDIATE 'drop index PK_BIS_ACC_HIS_BAL';
- END IF;
- COMMIT;
- END;
- /