这个过程应该怎么写？

[hosia]

数据库一中有表tab1,数据库二中有表tab2，现在需要将tab1中的部分数据(按时间为条件）插入到表tab2中，同时要将这些tab1中的这些数据删除掉。tab1和tab2的表结构完全相同。
请问可否用一个过程写出来，能的话，该怎么写？

[beckhambobo]

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;
/

[lilylamb]

将主键给我，我可以写个例子给你！！

[beckhambobo]

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;
/

[lilylamb]

不用主键可以用下面的过程试一下：

--I_D_P: PROCEDURE NAME
--TO_DATE: CHANGE STRING TO DATE

CREATE 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;

[gaobh]

可以通过创建dblink来实现两个数据库间的操作
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;
/

不知道行不行?试试吧

[劣势]

不用那么麻烦，beckhambobo(beckham) ( )说的就可以了，不过最好是加上数据库名

[hosia]

我现在希望通过游标将某个时间段的纪录取出来，一条一条的插入到tab2中，然后又将tab1中的该记录删除，不知道这样的能否可以写出来，哪位高手帮帮忙？

[Tom_Tan]

create databaselink dblinkname connect to username identified by password using servicename;
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;

[Tom_Tan]

在delete前加入:close c1;

[hosia]

可不可以在游标打开时删除呢？也就是说,在插入一条到tab2后，接着就将tab1中相应的纪录删除。可以吗？