表Ta：id1,id2, ...
表Tb：id1,id2,dtime, ...id1、id2均为整数id，dtime是日期时间，
上面给出的字段是两张表的主键。
现在想通过Ta的纪录生成Tb的记录。Tb表中的id1、id2均来自Ta，再加上一个时间值，时间值生成如下：
比如，给定一个日期时间：2008-12-10 0:00:00
每个5分钟一条记录，一直生成到另一个给定的时间，例如：2008-12-10 12:00:00
例如：
Ta:
1,1
1,2
2,1
2,2
Tb:
1,1,2008-12-10 0:00:00
1,1,2008-12-10 0:05:00
1,1,2008-12-10 0:10:00
....
1,1,2008-12-10 12:00:00
1,2,2008-12-10 0:00:00
1,2,2008-12-10 0:05:00
....
1,2,2008-12-10 12:00:00
2,1,2008-12-10 0:00:00
...
2,2,2008-12-10 12:00:00请大家给一个好的办法，多谢。
表Tb：id1,id2,dtime, ...id1、id2均为整数id，dtime是日期时间，
上面给出的字段是两张表的主键。
现在想通过Ta的纪录生成Tb的记录。Tb表中的id1、id2均来自Ta，再加上一个时间值，时间值生成如下：
比如，给定一个日期时间：2008-12-10 0:00:00
每个5分钟一条记录，一直生成到另一个给定的时间，例如：2008-12-10 12:00:00
例如：
Ta:
1,1
1,2
2,1
2,2
Tb:
1,1,2008-12-10 0:00:00
1,1,2008-12-10 0:05:00
1,1,2008-12-10 0:10:00
....
1,1,2008-12-10 12:00:00
1,2,2008-12-10 0:00:00
1,2,2008-12-10 0:05:00
....
1,2,2008-12-10 12:00:00
2,1,2008-12-10 0:00:00
...
2,2,2008-12-10 12:00:00请大家给一个好的办法，多谢。
is
begin
insert into tb(id1,id2,dtime)
select id1,id2,sysdate
from ta;
commit tran;
end procTest;然后把这个存储过程加入到job中
http://www.bitscn.com/oracle/optimize/200604/19366.html
我是想一次性插入。还有什么办法？
SQL> SELECT ID1, ID2, ALL_DATE
2 FROM TABLE_NAME TT,
12 (SELECT TO_DATE('2008-12-10 00:00:00', 'YYYY-MM-DD HH24:MI:SS') +
13 ((ROWNUM - 1) * 5) / (24 * 60) ALL_DATE
14 FROM DUAL
15 CONNECT BY ROWNUM <= (SELECT (TO_DATE('2008-12-10 12:00:00',
16 'YYYY-MM-DD HH24:MI:SS') -
17 TO_DATE('2008-12-10 00:00:00',
18 'YYYY-MM-DD HH24:MI:SS')) * 24 * 60 / 5
19 FROM DUAL)) ZZ
20 ORDER BY 1, 2, 3; ID1 ID2 ALL_DATE
---------- ---------- --------------------------------------------------
1 1 2008-12-10
1 1 2008-12-10 0:05:00
1 1 2008-12-10 0:10:00
1 1 2008-12-10 0:15:00
1 1 2008-12-10 0:20:00
1 1 2008-12-10 0:25:00
1 1 2008-12-10 0:30:00
1 1 2008-12-10 0:35:00
1 1 2008-12-10 0:40:00
1 1 2008-12-10 0:45:00
1 1 2008-12-10 0:50:00
1 1 2008-12-10 0:55:00
1 1 2008-12-10 1:00:00
1 1 2008-12-10 1:05:00
1 1 2008-12-10 1:10:00
1 1 2008-12-10 1:15:00
1 1 2008-12-10 1:20:00
1 1 2008-12-10 1:25:00
1 1 2008-12-10 1:30:00
1 1 2008-12-10 1:35:00 ID1 ID2 ALL_DATE
---------- ---------- --------------------------------------------------
1 1 2008-12-10 1:40:00
1 1 2008-12-10 1:45:00
1 1 2008-12-10 1:50:00
1 1 2008-12-10 1:55:00
1 1 2008-12-10 2:00:00
1 1 2008-12-10 2:05:00
1 1 2008-12-10 2:10:00
1 1 2008-12-10 2:15:00
1 1 2008-12-10 2:20:00
1 1 2008-12-10 2:25:00
1 1 2008-12-10 2:30:00
1 1 2008-12-10 2:35:00
1 1 2008-12-10 2:40:00
1 1 2008-12-10 2:45:00
1 1 2008-12-10 2:50:00
1 1 2008-12-10 2:55:00
1 1 2008-12-10 3:00:00
1 1 2008-12-10 3:05:00
1 1 2008-12-10 3:10:00
1 1 2008-12-10 3:15:00
1 1 2008-12-10 3:20:00 ID1 ID2 ALL_DATE
---------- ---------- --------------------------------------------------
1 1 2008-12-10 3:25:00
1 1 2008-12-10 3:30:00
1 1 2008-12-10 3:35:00
1 1 2008-12-10 3:40:00
1 1 2008-12-10 3:45:00
1 1 2008-12-10 3:50:00
1 1 2008-12-10 3:55:00
1 1 2008-12-10 4:00:00
1 1 2008-12-10 4:05:00
1 1 2008-12-10 4:10:00
1 1 2008-12-10 4:15:00
1 1 2008-12-10 4:20:00
1 1 2008-12-10 4:25:00
1 1 2008-12-10 4:30:00
1 1 2008-12-10 4:35:00
1 1 2008-12-10 4:40:00
1 1 2008-12-10 4:45:00
1 1 2008-12-10 4:50:00
1 1 2008-12-10 4:55:00
1 1 2008-12-10 5:00:00
1 1 2008-12-10 5:05:00 ID1 ID2 ALL_DATE
---------- ---------- --------------------------------------------------
1 1 2008-12-10 5:10:00
1 1 2008-12-10 5:15:00
1 1 2008-12-10 5:20:00
1 1 2008-12-10 5:25:00
1 1 2008-12-10 5:30:00
1 1 2008-12-10 5:35:00
1 1 2008-12-10 5:40:00
1 1 2008-12-10 5:45:00
1 1 2008-12-10 5:50:00
1 1 2008-12-10 5:55:00
1 1 2008-12-10 6:00:00
1 1 2008-12-10 6:05:00
1 1 2008-12-10 6:10:00
1 1 2008-12-10 6:15:00
1 1 2008-12-10 6:20:00
1 1 2008-12-10 6:25:00
1 1 2008-12-10 6:30:00
1 1 2008-12-10 6:35:00
1 1 2008-12-10 6:40:00
1 1 2008-12-10 6:45:00
1 1 2008-12-10 6:50:00 ID1 ID2 ALL_DATE
---------- ---------- --------------------------------------------------
1 1 2008-12-10 6:55:00
1 1 2008-12-10 7:00:00
1 1 2008-12-10 7:05:00
1 1 2008-12-10 7:10:00
1 1 2008-12-10 7:15:00
1 1 2008-12-10 7:20:00
1 1 2008-12-10 7:25:00
1 1 2008-12-10 7:30:00
1 1 2008-12-10 7:35:00
1 1 2008-12-10 7:40:00
1 1 2008-12-10 7:45:00
1 1 2008-12-10 7:50:00
1 1 2008-12-10 7:55:00
1 1 2008-12-10 8:00:00
1 1 2008-12-10 8:05:00
1 1 2008-12-10 8:10:00
1 1 2008-12-10 8:15:00
1 1 2008-12-10 8:20:00
1 1 2008-12-10 8:25:00
1 1 2008-12-10 8:30:00
1 1 2008-12-10 8:35:00 ID1 ID2 ALL_DATE
---------- ---------- --------------------------------------------------
1 1 2008-12-10 8:40:00
1 1 2008-12-10 8:45:00
1 1 2008-12-10 8:50:00
1 1 2008-12-10 8:55:00
1 1 2008-12-10 9:00:00
1 1 2008-12-10 9:05:00
1 1 2008-12-10 9:10:00
1 1 2008-12-10 9:15:00
1 1 2008-12-10 9:20:00
1 1 2008-12-10 9:25:00
1 1 2008-12-10 9:30:00
1 1 2008-12-10 9:35:00
1 1 2008-12-10 9:40:00
1 1 2008-12-10 9:45:00
1 1 2008-12-10 9:50:00
1 1 2008-12-10 9:55:00
1 1 2008-12-10 10:00:00
1 1 2008-12-10 10:05:00
1 1 2008-12-10 10:10:00
1 1 2008-12-10 10:15:00
1 1 2008-12-10 10:20:00 ID1 ID2 ALL_DATE
---------- ---------- --------------------------------------------------
1 1 2008-12-10 10:25:00
1 1 2008-12-10 10:30:00
1 1 2008-12-10 10:35:00
1 1 2008-12-10 10:40:00
1 1 2008-12-10 10:45:00
1 1 2008-12-10 10:50:00
1 1 2008-12-10 10:55:00
1 1 2008-12-10 11:00:00
1 1 2008-12-10 11:05:00
1 1 2008-12-10 11:10:00
1 1 2008-12-10 11:15:00
1 1 2008-12-10 11:20:00
1 1 2008-12-10 11:25:00
1 1 2008-12-10 11:30:00
1 1 2008-12-10 11:35:00
1 1 2008-12-10 11:40:00
1 1 2008-12-10 11:45:00
1 1 2008-12-10 11:50:00
1 1 2008-12-10 11:55:00
1 1 2008-12-10 12:00:00
... 580 rows selected
testrq date;
strsql varchar2(200);
cursor cur1 is select * from a;
begin
for cur1_result in cur1 loop
begin
testrq:=rq1;
while testrq<=rq2 loop
begin
strsql:='insert into b(id1,id2,dtime) values(:id1,:id2,:testrq)';
execute immediate strsql using cur1_result.id1,cur1_result.id2, testrq;
commit;
testrq:=testrq+ interval '5' minute;
end;
end loop;
end;
end loop;
end ;
调用：
call test1(to_date('2008-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss'),to_date('2008-01-01 01:00:00','yyyy-mm-dd hh24:mi:ss'))