数据库结构操作脚本 - 数据库编程

If;
End;

-- sequence 创建Sequence
declare
tm_i integer;
begin
select count(*) into tm_i from User_Objects t where Lower(t.OBJECT_NAME) = Lower('SEQ_PARAMLOGS') And Lower(t.OBJECT_TYPE) = Lower('Sequence');
if tm_i=0 then
execute immediate 'create sequence SEQ_PARAMLOGS_ID minvalue 1 maxvalue 999999999999999999999999999 start with 1 increment by 1 cache 20';
end if;
end;

-- sequence 删除Sequence
declare
tm_i integer;
begin
select count(*) into tm_i from User_Objects t where Lower(t.OBJECT_NAME) = Lower('SEQ_PARAMLOGS') And Lower(t.OBJECT_TYPE) = Lower('Sequence');
if tm_i>0 then
execute immediate 'Drop sequence seq_paramlogs';
end if;
end;

-- forientkey 创建外键
begin
for x in (
select a.constraint_name, a.table_name from user_constraints a inner join user_constraints b on a.r_constraint_name=b.constraint_name
where lower(b.table_name) = Lower('stockinfos') and Lower(a.constraint_type)=Lower('R')
) loop
execute immediate 'alter table '|| x.table_name || ' disable constraint '||x.constraint_name;
end loop;

for x in (
select a.constraint_name, a.table_name from user_constraints a inner join user_constraints b on a.r_constraint_name=b.constraint_name
where lower(b.table_name) = Lower('stockinfos') and Lower(a.constraint_type)=Lower('R')
) loop
execute immediate 'alter table '|| x.table_name || ' enable constraint '||x.constraint_name;
end loop;
end;

-- procedure 删除过程
declare
tm_i integer;

begin
select count(*) into tm_i from User_Objects t Where lower(t.object_name) = lower('up_report_data_10259') And Lower(t.OBJECT_TYPE) = Lower('Procedure');
if tm_i>0 then
execute immediate 'drop procedure up_report_data_10259';
end if;
end;

--Index 删除索引
Declare
tm_i Integer;
Begin
Select Count(*) Into tm_i From User_Objects t Where Lower(t.OBJECT_NAME) = Lower('Index_name') And Lower(t.OBJECT_TYPE) = Lower('Index');
If tm_i > 0 Then
Execute Immediate 'Drop Index index_name';
End If;
End;

-- 修改字段类型
declare
tm_i integer;
Begin
--如果字段关联了主键先删除相关主键
select count(*) into tm_i from user_constraints where Lower(table_name)=Lower('table_name') and lower(constraint_name)=lower('PK_Constraints_name');
if tm_i>0 then
execute immediate 'alter table table_name drop constraint PK_Constraints_name cascade';
end if;
--判断索引
select count(*) into tm_i from user_indexes where table_name='table_name' and lower(index_name)=lower('PK_Constraints_name');
if tm_i>0 then
execute immediate 'DROP INDEX PK_Constraints_name';
end if;
--判断字段
select count(*) into tm_i from user_tab_cols where table_name = upper('table_name') and column_name =upper('column_name_temp');
if tm_i<=0 then
execute immediate 'alter table table_name add column_name_temp varchar2(20) default '' '' not null';
End If;
--将旧列值更新到新列
execute immediate 'update table_name set column_name_temp=column_name';
Execute Immediate 'Update table_name Set column_name_temp = '' '' where column_name_temp = ''0'' ';
--删除原列
execute immediate 'alter Table table_name drop column column_name';
--重命名新列
execute immediate 'alter table table_name rename column column_name_temp to column_name';

--如果有主键重新创建主键

数据库结构操作脚本(四)