常用的存储过程

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;