需要实现以下目的，请大家帮帮忙
1，根据指定的时间将与T表相互关联的两个表（T1，T2）的数据插入到远程数据库中
2，然后再将T表中满足条件的数据插入到远程数据库中
3，删除T，T1，T2中已经插入到远程数据库中的数据
4，要求这1~3步中只要有1步出问题就需要全部“回滚”
5，如果有异常发生，通过LOG_ERROR表来记录错误信息。
6,对于P,P1,P2三个表的处理同（步骤1~步骤5）
CREATEPROCEDURE trans_data(v_date date)
IS
CURSOR t_cursor IS
SELECTid from T where t_data<v_date;
BEGIN
for t_record in t_cursor loop
INSERT INTO t1@remote_database select * from t1 where t1.id=t_record.id;
INSERT INTO t2@remote_database select * from t2 where t2.id=t_record.id;
INSERT INTO t@remote_database select * from t where t.id=t_record.id;
delete from t1 where t1.id=t_record.id;
deletefrom t2 where t2.id=t_record.id;
delete from t where t.id=t_record.id;
end loop;
CURSOR p_cursor IS
SELECTid from P where p_data<v_date;
for p_record in p_cursor loop
INSERT INTO p1@remote_database select * from p1 where p1.id=p_record.id;
INSERT INTO p2@remote_database select * from p2 where p2.id=p_record.id;
INSERT INTO p@remote_database select * from p where p.id=p_record.id;
delete from p1 where p1.id=p_record.id;
deletefrom p2 where p2.id=p_record.id;
delete from p where p.id=p_record.id;
end loop;
EXCEPTION
WHEN OTHERS THEN
err_num := SQLCODE;
err_msg := SUBSTR(SQLERRM, 1, 1000);
INSERT INTO LOG_ERROR VALUES ('WARNING! trans_dataNOT complete at '||TO_CHAR(SYSDATE,'yyyy/mm/dd hh24:mi:ss')||''||err_num||''||err_msg);
COMMIT;
END trans_data;
/ |
