1.备份表
create or replace procedure BUS_BACKUPTABLE(tableName in varchar2, backUpTableName in varchar2)
as
num number(10);
num1 number(10);
begin
select count(*) into num from user_tables where TABLE_NAME=upper(tableName);
if num != 0 then
select count(*) into num1 from user_tables where TABLE_NAME=upper(backUpTableName);
if num1 != 0 then
execute immediate 'drop table ' || backUpTableName;
end if;
execute immediate 'create table ' || backUpTableName || ' as select * from ' || tableName;
end if;
end;
/
exec BUS_BACKUPTABLE('OCS_SERVORDERCTRL', 'OCS_SERVORDERCTRL_BAK20111125');
drop procedure BUS_BACKUPTABLE;
2.建表
create or replace procedure BUS_CREATETABLE(tableName in varchar2, createSql in varchar2)
as
num number(10);
begin
select count(*) into num from user_tables where TABLE_NAME=upper(tableName);
if num = 0 then
execute immediate createSql;
end if;
end;
/
exec BUS_CREATETABLE('OCS_WorkOrderDone', 'create table OCS_WorkOrderDone(OrderSequence NUMBER(38,0) not null ,RecTime DATE not null,WorkOrderType Integer not null,SubscriberKey Integer not null,ProcessState Integer not null,LastExeTime DATE,RetryTimes Integer default 0 not null,LockFlag Integer not null,BMPID Integer not null,RelatedObjectID varchar(32),MessageBody varchar(4000),SendToCRMTime varchar(256),RecvFromCRMTime varchar(256),SubscriberNo varchar(64),primary key (OrderSequence))');
drop procedure BUS_CREATETABLE;
3.回滚修改字段的表
create or replace procedure BUS_ROOLBACK_ALTERTABLE(tableName in varchar2, backUpTableName in varchar2)
as
num number(10);
begin
select count(*) into num from user_tables where TABLE_NAME=upper(backUpTableName);
if num != 0 then
execute immediate 'drop table ' || tableName;
execute immediate 'create table ' || tableName || ' as select * from ' || backUpTableName;
end if;
end;
/
exec BUS_ROOLBACK_ALTERTABLE('OCS_SERVORDERCTRL', 'OCS_SERVORDERCTRL_BAK20111125');
drop procedure BUS_ROOLBACK_ALTERTABLE;
4.删表
create or replace procedure BUS_DROPTABLE(tableName in varchar2)
as
num number(10);
begin
select count(*) into num from user_tables where TABLE_NAME=upper(tableName);
if num != 0 then
execute immediate 'drop table ' || tableName;
end if;
end;
/
exec BUS_DROPTABLE('OCS_WorkOrderDone');
drop procedure BUS_DROPTABLE;
5.增加表字段
create or replace procedure BUS_ALTERTABLE_ADD(tableName in varchar2, columnName in varchar2, alterSQL in varchar2)
as
num number(10);
begin
select count(*) into num from user_tab_columns where table_name=upper(tableName) and COLUMN_NAME=upper(columnName);
if num = 0 then
execute immediate alterSQL;
end if;
end;
/
exec BUS_ALTERTABLE_ADD('OCS_SERVORDERCTRL', 'SERVICEID', 'Alter table OCS_SERVORDERCTRL add WORKORDER number(22) default 0');
drop procedure BUS_ALTERTABLE_ADD;
6.删除表字段
create or replace procedure BUS_ALTERTABLE_DROP(tableName in varchar2, columnName in varchar2)
as
num number(10);
begin
select count(*) into num from user_tab_columns where table_name=upper(tableName) and COLUMN_NAME=upper(columnName);
if num != 0 then
execute immediate 'alter table' || tableName || 'drop column ' || columnName;
end if;
end;
/
exec BUS_ALTERTABLE_DROP('OCS_SERVORDERCTRL', 'WORKORDER');
drop procedure BUS_ALTERTABLE_DROP;
7.建索引
create or replace procedure BUS_CREATEINDEX(indexName in varchar2, createSql in varchar2)
as
num number(10);
begin
select count(*) into num from user_indexes where INDEX_NAME=upper(indexName);
if num = 0 then
execute immediate createSql;
end if;
end;
/
exec BUS_CREATEINDEX('idx_WorkOrderDoneLastExeTime', 'create index idx_WorkOrderDoneLastExeTime on OCS_WorkOrderDone(LastExeTime)');
drop procedure BUS_CREATEINDEX;
8.删除索引
create or replace procedure BUS_DROPINDEX(indexName in varchar2)
as
num number(10);
begin
select count(*) into num from user_indexes where INDEX_NAME=upper(indexName);
if num != 0 then
execute immediate 'drop index ' || indexName;
end if;
end;
/
exec BUS_DROPINDEX('idx_WorkOrderDoneLastExeTime');
drop procedure BUS_DROPINDEX;
9.建seq
create or replace procedure BUS_CREATESEQ(seqName in varchar2, createSql in varchar2)
as
num number(10);
begin
select count(*) into num from user_sequences where upper(sequence_name) = upper(seqName);
if num = 0 then
execute immediate createSql;
end if;
end;
/
exec BUS_CREATESEQ('SEQ_OCS_WORKORDERDONE', 'CREATE SEQUENCE SEQ_OCS_WORKORDERDONE INCREMENT BY 1 START WITH 1 NOCYCLE CACHE 1000 ORDER');
drop procedure BUS_CREATESEQ;
10.删除序列
create or replace procedure BUS_DROPSEQ(seqName in varchar2)
as
num number(10);
begin
select count(*) into num from user_sequences where upper(sequence_name) = upper(seqName);
if num != 0 then
execute immediate 'drop sequence ' || seqName;
end if;
end;
/
exec BUS_DROPSEQ('SEQ_OCS_WORKORDERDONE');
drop procedure BUS_DROPSEQ;
11.删除菜单
create or replace procedure BUS_DELETE_MENU(busMenuID in varchar2)
as
begin
execute immediate 'delete from t_bme_i18n where i18nID=' || busMenuID;
execute immediate 'delete from t_bme_role_privilege where privilegeID=' || busMenuID;
execute immediate 'delete from t_bme_privilege where privilegeID=' || busMenuID;
execute immediate 'delete from t_bme_button where menuID=' || busMenuID;
execute immediate 'delete from t_bme_menu where menuID=' || busMenuID;
end;
/
exec BUS_DELETE_MENU('910424');
drop procedure BUS_DELETE_MENU;
12.删除视图
create or replace procedure BUS_DROP_VIEW(viewName in varchar2)
as
num number(10);
begin
select count(*) into num from user_views where VIEW_NAME=upper(viewName);
if num != 0 then
execute immediate 'drop view ' || viewName;
end if;
end;
/
exec BUS_DROP_VIEW('123');
drop procedure BUS_DROP_VIEW;