2023年7月7日发(作者：)

ORACLE数据库脚本规范数据库升级脚本编写参考⽅案在使⽤补丁程序对现场进⾏升级时，可能安装过程出现问题，脚本执⾏⼀部分⽽⽆法回滚，所以需要编写的SQL脚本能够重复执⾏。为此给⼤家提供⼀些范例进⾏参考，减少⼤家编写升级脚本的时间，提⾼⼯作效率。⼀、 ORACLE1. 新增表，查找系统视图user_all_tables，如果不存在则执⾏创建语句declare cnt integer;begin select count(0) into cnt

from user_all_tables where table_name = upper('NS_DATA_DICTIONARY');

if cnt = 0 then execute immediate 'create table NS_DATA_DICTIONARY( DATA_TYPE INTEGER, DATA_GROUP INTEGER, DATA_VALUE varchar2(50))'; end if;

end;/2. 新增字段，查找系统视图user_tab_columns，如果不存在则执⾏新增语句declare cnt integer;begin select count(0) into cnt

from user_tab_columns a where _name = upper('BMS_TX_PD') and _NAME = upper('APPROVE_FLAG');

if cnt = 0 then execute immediate 'ALTER TABLE BMS_TX_PD ADD APPROVE_FLAG INTEGER'; end if;end;/3. 删除字段，查找系统视图user_tab_columns，如果已存在则执⾏删除语句declare cnt integer;begin select count(0) into cnt from user_tab_columns a where _name = upper('BMS_TX_PD') and _NAME = upper('APPROVE_FLAG');

if cnt = 1 then execute immediate 'ALTER TABLE BMS_TX_PD DROP COLUMN APPROVE_FLAG '; end if;end;/4. 修改VARCHAR2字段长度，查找系统视图user_tab_columns，如果字段长度⼩于预期长度，则执⾏修改语句declare cnt integer;begin select count(0) into cnt

from user_tab_columns a where _name = upper('CNTPENDINGJOB') and _NAME = upper('SOURCE') and _LENGTH < 32;

if cnt = 1 then execute immediate 'ALTER TABLE CNTPENDINGJOB modify SOURCE VARCHAR2(32)'; end if;end;/5. 修改字段为可空，查找系统视图user_tab_columns，如果字段存在且不可为空，则执⾏修改语句declare cnt integer;begin select count(0) into cnt

from user_tab_columns a where _name = upper('BMS_TX_PD') and _NAME = upper('APPROVE_FLAG') and LE = upper('N');

if cnt = 1 then execute immediate 'ALTER TABLE BMS_TX_PD modify APPROVE_FLAG null'; end if;end;/6. 修改字段类型declare cnt integer; tempCnt integer; tempCnt2 integer;

begin select count(0) into cnt from user_tab_columns a where _name = upper('bmp_branch') and _NAME = upper('invalid_flag') and _TYPE = upper('VARCHAR2'); select count(0) into tempCnt from user_tab_columns a where _name = upper('bmp_branch') and _NAME = upper('invalid_flag_1');

if cnt = 0 then

if tempCnt = 1 then execute immediate ' select count(0)

from dual where exists (select null from bmp_branch a where d_flag_1 is not null)' into tempCnt2;

if (tempCnt2 = 0) then execute immediate 'alter table bmp_branch drop column invalid_flag_1'; execute immediate 'alter table bmp_branch add invalid_flag_1 integer'; end if; else tempCnt2 := 0;

execute immediate 'alter table bmp_branch add invalid_flag_1 integer'; end if;

if (tempCnt2 = 0) then execute immediate 'update bmp_branch set invalid_flag_1 = invalid_flag'; end if;

execute immediate 'update bmp_branch set invalid_flag = null';

execute immediate 'alter table bmp_branch modify invalid_flag varchar2(10)'; end if;

if (tempCnt > 0) then execute immediate 'update bmp_branch set invalid_flag = to_char(invalid_flag_1)';

execute immediate 'alter table bmp_branch drop column invalid_flag_1';

end if;end;/7. 新增序列declare cnt integer;begin select count(0) select count(0) into cnt

from user_sequences a where ce_name = upper('BP_PB_ORDER_ORDERID_SEQ');

if cnt = 0 then execute immediate 'create sequence BP_PB_ORDER_ORDERID_SEQ minvalue 1 maxvalue 999999999999999999999999999 start with 1 increment by 1 cache 20'; end if;

end;/8. 新增约束，查找系统视图user_constraints，如果不存在该约束，则进⾏创建declare cnt integer;begin select count(0) into cnt

from user_constraints a where aint_name = upper('NS_DATA_DICTIONARY_PK');

if cnt = 0 then execute immediate 'alter table NS_DATA_DICTIONARY add constraint NS_DATA_DICTIONARY_PK primary key(DATA_TYPE, DATA_GROUP)'; end if;

end;/9. 修改约束，⾸先查找约束是否存在，如果存在，先删除再创建declare cnt integer;begin select count(0) into cnt from user_constraints a where aint_name = upper('CKC_BANKTYPE_BP_BANK');

if (cnt > 0) then execute immediate 'alter table BP_BANK drop constraint CKC_BANKTYPE_BP_BANK'; end if;

execute immediate 'alter table BP_BANK add constraint CKC_BANKTYPE_BP_BANK check (BankType = 9 OR BankType = 5 OR BankType = 6 OR BankType = 2 OR BankType = 1 OR BankType = 0)';

end;/10. 新增类型，⾸先删除TABLE的类型，然后删除ROW的类型，其次重新创建ROW的，最后创建TABLE的类型declare cnt integer;begin select count(0)

into cnt

from user_objects where object_name = upper('Ns_GetAKmInfo_TAB');

if cnt > 0 then execute immediate 'drop type Ns_GetAKmInfo_TAB'; end if; select count(0)

into cnt

from user_objects where object_name = upper('Ns_GetAKmInfo_ROW');

if cnt > 0 then execute immediate 'drop type Ns_GetAKmInfo_ROW'; end if;

execute immediate 'CREATE TYPE Ns_GetAKmInfo_ROW as object ( YR INTEGER , KM VARCHAR2(18) , KMNAME VARCHAR2(60) , DIR INTEGER , KMGRP VARCHAR2(6) , KMLEVEL INTEGER , ISLEAF INTEGER , CASHTYPE INTEGER , QTYFLAG INTEGER , QTYUNIT VARCHAR2(8), BUSINESS INTEGER , OUTFLAG INTEGER , LIMITMODE INTEGER , QUOTA NUMBER(15,2) , USESTATE INTEGER , KMPATH VARCHAR2(255), ID INTEGER , BRNO VARCHAR2(4), INTRFLAG integer, MULTICOUNT integer )'; execute immediate 'CREATE TYPE Ns_GetAKmInfo_TAB AS TABLE OF Ns_GetAKmInfo_ROW';end;/11. 新增数据，使⽤主键或者业务主键关联，避免插⼊报错或者插⼊多条重复数据。新增数据升级脚本编写较复杂，得根据具体情况进⾏分析insert into BP_BANK (BANKNO, BANKNAME, BANKTYPE, LASTEXGTIME)

select '93', '⾮直连银⾏', 5, null from dual where not exists (select null from BP_BANK where BANKNO = '93');12、新建索引declare cnt integer;begin select count(0) into cnt

from user_indexes a where _name = upper('IX_CNTBUSSSHEET_NOTEGRP');

if cnt = 0 then execute immediate 'create index IX_CNTBUSSSHEET_NOTEGRP on CNTBUSSSHEET (BRNO, NOTEGRP)'; end if;

end;/