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

me drop constraint PK_Constraints_name cascade';
end if;
--删除索引
select count(*) into tm_i from user_indexes where lower(table_name)=Lower('table_name') and lower(index_name)=lower('PK_Constraints_name');
if tm_i>0 then
execute immediate 'DROP INDEX PK_Constraints_name';
end if;
--删除主键END----------------------------------------------

select count(*) into tm_i from user_tab_cols where lower(table_name)=lower('table_name') and lower(column_name)=lower('Column_name');
if tm_i=0 then
execute immediate 'alter table table_name add Column_name varchar2(1) default ''0'' not null';
end if;

--创建主键
execute immediate 'alter table table_name add constraint PK_Constraints_name primary key (Column_name1, Column_name2)';
end;

--modify user_tab_cols 表修改字段
declare
tm_i integer;
begin
select count(*) into tm_i from user_tab_cols where lower(table_name)=lower('INSTRUCTIONS') and lower(column_name)=lower('VC_ONLINE_FLAG') And Lower(NULLABLE) = Lower('N');
if tm_i > 0 then
execute immediate 'alter table INSTRUCTIONS Modify VC_ONLINE_FLAG varchar2(1) default ''0'' not null';
else
execute immediate 'alter table INSTRUCTIONS Add VC_ONLINE_FLAG varchar2(1) default ''0'' not null';
end if;
end;

-- table 增加表
declare
tm_i integer;
begin
select count(*) into tm_i from user_tables where lower(table_name)=lower('qs_filedictionary');
if tm_i=0 then
execute immediate 'create table tmp___tmp as select * from qs_filedictionary';
end if;
end;

-- table 删除表
declare
tm_i integer;
begin
select count(*) into tm_i from user_tables where lower(table_name)=lower('qs_filedictionary');
if tm_i>0 then
execute immediate 'drop table qs_filedictionary';
end if;
Select Count(*) Into tm_i From User_Objects t Where Lower(t.OBJECT_NAME) = Lower('table_name') And Lower(t.OBJECT_TYPE) = Lower('Table');
If tm_i > 0 Then
Execute Immediate 'Drop table table_name';

End If;
end;

declare
tm_i integer;
begin
select count(*) into tm_i from user_tables where lower(table_name)=lower('userprogramlinks');
if tm_i>0 then
execute immediate 'drop table userprogramlinks';
end if;

end;

--删除触发器
Declare
tm_i Integer;
Begin
Select Count(*) Into tm_i From User_Objects t Where Lower(t.OBJECT_NAME) = Lower('Trigger_name') And Lower(t.OBJECT_TYPE) = Lower('Trigger');
If tm_i > 0 Then
Execute Immediate 'Drop Trigger Trigger_name';
End If;
End;

--禁用触发器
Declare
tm_i Integer;
Begin
Select Count(*) Into tm_i From User_Triggers t Where Lower(t.trigger_name) = Lower('trigger_name') And Lower(t.status) = Lower('ENABLED');
If tm_i > 0 Then
Execute Immediate 'ALTER TRIGGER trigger_name DISABLE';
End If;
End;

--删除视图，
Declare
tm_i Integer;
Begin
Select Count(*) Into tm_i From User_Objects t Where Lower(t.OBJECT_NAME) = Lower('Viewer_name') And Lower(t.OBJECT_TYPE) = Lower('View');
If tm_i > 0 Then
Execute Immediate 'Drop view viewer_name';
End If;
End;

--删除函数，
Declare
tm_i Integer;
Begin
Select Count(*) Into tm_i From User_Objects t Where Lower(t.OBJECT_NAME) = Lower('function_name') And Lower(t.OBJECT_TYPE) = Lower('Function');
If tm_i > 0 Then
Execute Immediate 'Drop Function function_name';
End

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