create procedure pro
as
begin
insert into tab2 select * from tab1 where col_date between ... and ...;
delete from tab1 where col_date between .. and ..;
commit;
exception
when others then
rollback;
end;
/
as
begin
insert into tab2 select * from tab1 where col_date between ... and ...;
delete from tab1 where col_date between .. and ..;
commit;
exception
when others then
rollback;
end;
/
as
begin
insert into tab2 select * from tab1 where col_date between ... and ...;
delete from tab1 where col_date between .. and ..;
commit;
exception
when others then
rollback;
end;
/
--TO_DATE: CHANGE STRING TO DATECREATE PROCEDURE I_D_P
AS
BEGIN
BEGIN
DELETE FROM TAB2 WHERE C_DATE BETWEEN TO_DATE('19920101','YYYYMMDD') AND TO_DATE('20030710','YYYYMMDD');
INSERT INTO TAB2 (SELECT * FROM TAB1 WHERE C_DATE BETWEEN TO_DATE('19920101','YYYYMMDD') AND TO_DATE('20030710','YYYYMMDD') );
DELETE FROM TAB1 WHERE C_DATE BETWEEN TO_DATE('19920101','YYYYMMDD') AND TO_DATE('20030710','YYYYMMDD'); EXCEPTION --选文件异常处理
WHEN NO_DATA_FOUND THEN --找不到对应的信息
<处理语句>
ROLLBACK;
WHEN OTHERS THEN --其它错误
<处理语句>
ROLLBACK;
END;
END I_D_P;
create database link dblinkname connect to system identified by manager using 'servicename';
create procedure pro
as
begin
insert into tab2@dblinkname select * from tab1 where col_date between ... and ...;
delete from tab1 where col_date between .. and ..;
commit;
exception
when others then
rollback;
end;
/不知道行不行?试试吧
create procedure proname(t1 in date,t2 in date)
as
col1 tb1.col1%type;
col2 tb1.col2%type;
.
.
.
cusor c1 is
select * from tb1 where col_date between t1 and t2;
begin
fech c1 into col1,col2...
if c1%found then
loop
insert into tb2@servicename values(col1,col2,....);
fech c1 into col1,col2...
exit when c1%notfound;
end loop;
end if
delete from tb1 where col_date between t1 and t2;
commit;
exception
when others then
rollback;
end;