备份数据表、还原数据表

CREATE OR REPLACE PROCEDURE backuporg
IS
-- declare fixed array
TYPE arry_var IS VARRAY(30) OF VARCHAR2(32);
tblnames arry_var;
v_sql varchar2(3999);
tblname varchar2(32);
baktblnamet varchar2(32);--今日的备份表名
today varchar2(5);--今天的日
tblexist number;
tblexist1 number;
--set serveroutput on size 10000000000;
BEGIN
DBMS_OUTPUT.ENABLE (buffer_size=>null);
-- init array
tblnames := arry_var(
'org_department',
'org_user',
'org_group',
'org_duty',
'org_job',
'org_department_principal',
'org_dept_principal',
'org_department_role',
'org_department_user_job',
'org_user_job',
'org_user_leader',
'org_user_privilege',
'org_user_role',
'org_job_leader',
'org_user_property',
'org_user_login_log',
'org_job_function',
'org_group_nested_group',
'org_employee',
'org_employee_job',
'org_duty_agent',
'org_department_function',
'org_department_employee_job',
'org_group_user',
'org_group_member',
'org_group_job',
'org_group_duty',
'org_group_department',
'org_duty_role',
'org_job_role');

--dbms_output.put_line(tblname(1));
--dbms_output.put_line(tblname(2));


for i in 1..tblnames.count
loop
tblname := tblnames(i)||'_x';
select to_char(sysdate,'yMMdd') into today from dual;
baktblnamet :=  substr(tblnames(i),0, 25)||today;

select count(1) into tblexist from all_tables where TABLE_NAME = upper(tblname) ;
select count(1) into tblexist1 from all_tables where TABLE_NAME = upper(baktblnamet) ;



if tblexist=1 then
 if tblexist1 = 0 then 
   v_sql:=  ' create table '||baktblnamet||' as select * from '||tblname;
   dbms_output.put_line(v_sql);
   execute immediate (v_sql);
  end if;

v_sql:= ' drop table '||tblname;
dbms_output.put_line(v_sql);
execute immediate (v_sql);
end if;

if tblexist=0 then

dbms_output.put_line(tblname ||' 不存在');

end if;


v_sql:= ' create table '||tblname||' as select * from '||tblnames(i);
dbms_output.put_line('创建表'||v_sql);
execute immediate (v_sql);

-- || ' create table '||tblname(i)||'_bak as select * from '||tblname(i)||';';
end loop;








END backuporg;


 

CREATE OR REPLACE PROCEDURE restoreorg_disable_FK(tblname in varchar2, n_enable in number)

IS
--类型定义
cursor c_constraint
is
select y.CONSTRAINT_NAME, y.TABLE_NAME from user_constraints y where y.R_CONSTRAINT_NAME =(
select x.CONSTRAINT_NAME from user_constraints x where x.TABLE_NAME = upper(tblname) and x.CONSTRAINT_TYPE = 'P');
--定义一个游标变量v_cinfo c_emp%ROWTYPE ，该类型为游标c_emp中的一行数据类型
c_row c_constraint%rowtype;
v_sql varchar2(3999);
BEGIN
DBMS_OUTPUT.ENABLE (buffer_size=>null);

for c_row in c_constraint loop
dbms_output.put_line(c_row.CONSTRAINT_NAME||'-'||c_row.TABLE_NAME);

if n_enable = 1 then
v_sql:= ' alter table '||c_row.TABLE_NAME||' enable constraint '|| c_row.CONSTRAINT_NAME ;--打开外键
dbms_output.put_line('打开外键'|| v_sql);
execute immediate (v_sql);
end if;

if n_enable = 0 then
v_sql:= ' alter table '||c_row.TABLE_NAME||' disable constraint '|| c_row.CONSTRAINT_NAME ;--关掉外键
dbms_output.put_line('关掉外键'|| v_sql);
execute immediate (v_sql);

end if;

end loop;

END restoreorg_disable_FK;

CREATE OR REPLACE PROCEDURE restoreorg

IS
-- declare fixed array
TYPE arry_var IS VARRAY(30) OF VARCHAR2(32);
tblnames arry_var;
v_sql varchar2(3999);
tblname varchar2(32);
baktblname varchar2(32);
tblexist number;
BEGIN
DBMS_OUTPUT.ENABLE (buffer_size=>null);
-- init array
tblnames := arry_var(
'org_department',
'org_user',
'org_group',
'org_duty',
'org_job',
'org_department_principal',
'org_dept_principal',
'org_department_role',
'org_department_user_job',
'org_user_job',
'org_user_leader',
'org_user_privilege',
'org_user_role',
'org_job_leader',
'org_user_property',
'org_user_login_log',
'org_job_function',
'org_group_nested_group',
'org_employee',
'org_employee_job',
'org_duty_agent',
'org_department_function',
'org_department_employee_job',
'org_group_user',
'org_group_member',
'org_group_job',
'org_group_duty',
'org_group_department',
'org_duty_role',
'org_job_role');

--dbms_output.put_line(tblname(1));
--dbms_output.put_line(tblname(2));

for i in 1..tblnames.count

loop
tblname := tblnames(i)||'_y';
select count(1) into tblexist from all_tables where TABLE_NAME = upper(tblname) ;

--if tblexist=1 then
--v_sql:= ' drop table '||tblname;--删除掉备份表
--dbms_output.put_line(v_sql);
--execute immediate (v_sql);
--end if;

--if tblexist=0 then

--dbms_output.put_line(tblname ||' 不存在');

--end if;


-- alter table
-- ORG_DEPT_PRINCIPAL_bak
--rename to
-- ORG_DEPT_PRINCIPAL_JOB

--将正式表重重名为带y备份表
--v_sql:= ' alter table '||tblnames(i)||' rename to '|| tblname ;--将原有表备份起来
--dbms_output.put_line('将正式表重名为带y备份表'||v_sql);
--execute immediate (v_sql);

restoreorg_disable_FK(tblnames(i), 0);
v_sql:= ' delete from   '||tblnames(i) ;--将原有表数据删除掉.
dbms_output.put_line('恢复原有表数据'||v_sql);
execute immediate (v_sql);



baktblname := tblnames(i)||'_x'; --备份表
v_sql:= 'insert into '|| tblnames(i) ||' select  *  from '||baktblname;--将原有表数据删除掉.
dbms_output.put_line('恢复原有表数据'||v_sql);
execute immediate (v_sql);



restoreorg_disable_FK(tblnames(i), 1);



--v_sql:= ' create table '||tblnames(i)||' as select * from '||baktblname ;--将备份表还原
--dbms_output.put_line('将备份表还原'||v_sql);
--execute immediate (v_sql);

end loop;

END restoreorg;