create or procedure name_pro as v_max_ttime date; begin select max(ttime) into v_max_ttime from table2; if trunc(v_max_ttime)=to_date('2002-5-31','yyyy-mm-dd') then null; else insert into table2(name....) select name... from table1 where ttime>=v_max_ttime and ttime<=(v_max_ttime+1/24); end if; end; /declare jobno number; begin dbms_job.submit(jobno,'name_pro;',sysdate,'sysdate+1/24'); commit; end; /
这样: DECLARE t1 DATE; BEGIN t1:=to_date('20010501','yyyymmdd'); LOOP EXIT WHEN t1>to_date('2002053124','yyyymmddhh24'); INSERT INTO table2(NAME....) SELECT NAME... FROM table1 WHERE t1<ttime<=t1+1/24; t1:=t1+1/24; END LOOP; END;
date类型测试成功,将上面的过程定义改为: DECLARE t1 DATE; BEGIN t1:=to_date('2003022000','yyyymmddhh24'); LOOP EXIT WHEN t1>to_date('2003022123','yyyymmddhh24'); INSERT INTO datetst1(daytime,inum) SELECT daytime,inum FROM datetst WHERE t1<daytime and daytime<=t1+1/24; t1:=t1+1/24; END LOOP; END; 就可以了。 希望有大虾讲一讲如何实现将date改为timestamp(2)类型,谢谢!
as
v_max_ttime date;
begin
select max(ttime) into v_max_ttime from table2;
if trunc(v_max_ttime)=to_date('2002-5-31','yyyy-mm-dd') then
null;
else
insert into table2(name....)
select name... from table1 where ttime>=v_max_ttime and ttime<=(v_max_ttime+1/24);
end if;
end;
/declare
jobno number;
begin
dbms_job.submit(jobno,'name_pro;',sysdate,'sysdate+1/24');
commit;
end;
/
DECLARE
t1 DATE;
BEGIN
t1:=to_date('20010501','yyyymmdd');
LOOP
EXIT WHEN t1>to_date('2002053124','yyyymmddhh24');
INSERT INTO table2(NAME....)
SELECT NAME... FROM table1 WHERE t1<ttime<=t1+1/24;
t1:=t1+1/24;
END LOOP;
END;
正在试
SQL> desc datetst;
名称 是否为空? 类型
----------------------------------------- -------- ---------- DAYTIME DATE
INUM NUMBER(6)SQL> desc datetst1;
名称 是否为空? 类型
----------------------------------------- -------- -------------- DAYTIME DATE
INUM NUMBER(6)SQL> select * from datetst;DAYTIME INUM
---------- ----------
20-2月 -03 1
21-2月 -03 2
21-2月 -03 3
21-2月 -03 4
21-2月 -03 5
21-2月 -03 6已选择6行。SQL> select * from datetst1;未选定行
SQL> DECLARE
2 t1 DATE;
3 BEGIN
4 t1:=to_date('20030220','yyyymmdd');
5 LOOP
6 EXIT WHEN t1>to_date('2003022124','yyyymmddhh24');
7 INSERT INTO datetst1(daytime,inum)
8 SELECT daytime,inum FROM datetst WHERE t1<daytime<=t1+1/24;
9 t1:=t1+1/24;
10 END LOOP;
11 END;
12 /
INSERT INTO datetst1(daytime,inum)
*
ERROR 位于第 7 行:
ORA-06550: 第 8 行, 第 56 列:
PL/SQL: ORA-00933: SQL 命令未正确结束
ORA-06550: 第 7 行, 第 5 列:
PL/SQL: SQL Statement ignored以上就是我的测试过程,即使用date类型,也同不过呀~~
DECLARE
t1 DATE;
BEGIN
t1:=to_date('2003022000','yyyymmddhh24');
LOOP
EXIT WHEN t1>to_date('2003022123','yyyymmddhh24');
INSERT INTO datetst1(daytime,inum)
SELECT daytime,inum FROM datetst WHERE t1<daytime and daytime<=t1+1/24;
t1:=t1+1/24;
END LOOP;
END;
就可以了。
希望有大虾讲一讲如何实现将date改为timestamp(2)类型,谢谢!