oracle批量备份表的存储过程

在一些重大变更前,后都需要备份涉及的重要表,当表很多且备份不是最新的时候,需要drop表,因为oracle没if exists 这种语法,写个存储过程来批量备份,当然也可以把加上参数,不过没什么必要,基本知道哪些表需要备份,执行下存储过程就OK

注意,这边如果当天存在同名备份表,会drop掉,

create or replace procedure BAK_CRM_PRO as
type type_array is varray(20) of varchar2(50);
back_tables type_array :=type_array(
'TBL_XXX1',
'TBL_XXX2',
'TBL_XXX3',
'TBL_XXX4',
'TBL_XXX5',
'TBL_XXX6',
'TBL_XXX7',
'TBL_XXX8',
'TBL_XXX9',
'TBL_XXX10'
);
bak_name varchar2(100);
temp_str varchar2(1000);
date_str varchar2(10);
temp_num number;
temp_num2 number;
begin
	select to_char(sysdate,'mmdd') into date_str from dual;
	for i in 1..back_tables.count loop
		bak_name:='BAK_'||date_str||'_'||back_tables(i);
		if (length(bak_name)>30) then
			bak_name:=regexp_replace(bak_name,'_([[:alpha:]])[[:alpha:]]+','_\1',8);
		end if;
		execute immediate 'select count(1)  from user_tables a where a.table_name=:1' into temp_num using bak_name ;
		dbms_output.put_line('temp_num='||temp_num);
		if(temp_num=1) then
			temp_str:='drop table '||bak_name;
			dbms_output.put_line(temp_str);
			execute immediate temp_str;
		end if;
		temp_str:='create table '||bak_name||' as select * from '||back_tables(i);
		dbms_output.put_line(temp_str);
		execute immediate temp_str;
		temp_num:=1;
		temp_num2 :=2;
		execute immediate 'select count(1) from '||back_tables(i) into temp_num;
		execute immediate 'select count(1) from '||bak_name into temp_num2;
		dbms_output.put_line('n1='||temp_num||' n2='||temp_num2);
		if(temp_num=temp_num2)
		then
			dbms_output.put_line('back '||bak_name||' success');
		end if;
	end loop;
end;

先编译下存储,然后调用就可以

另外plsql中编译存储过程在命令窗口,复制上面的代码,然后输入/ 和回车,就能编译,当然编译前需要替换成自己需要备份的表,就是tbl_xxx那部分

这边用到了dml动态sql,也可以用游标代替,不太熟悉这个

编译后执行 在sql窗口执行下面这个就可以

begin
  BAK_CRM_PRO_test();
end;

如果不需要这个存储过程可以删除

drop procedure BAK_CRM_PRO_test;