描述主要的功能实现是:月底稽核,要按照日期查找每天不同标签对应的记录个数。使用for循环处理日期,每次自加1。 代码如下,编译通过,但是运行没有插入任何记录。 不知问题出在哪里? 望高手不吝赐教 !!! 谢谢 :-)CREATE OR REPLACE PROCEDURE MBAPP.aaa_for as
date_date DATE;
aaa_err number;
string_date varchar2(50);
i number;
max_i number;
BEGIN
string_date := '20080101';
max_i :=5;
for i in 1..max_i loop
date_date := to_date(string_date, 'yyyymmddhh24miss')+i-1; //主要是这里产生查找日期
select
(select count(*) from eai_audit
where interface_type = 'TF200720'
and seq = '-1'
and create_time between date_date and date_date +1
)
+
(select count(*) from eai_audit
where interface_type = 'TF202000'
and seq = '-1'
and create_time between date_date and date_date +1
)
into aaa_err from dual;
insert into table1(date_date,aaa_ERR)
values(
date_date,
aaa_ERR
); commit;
end loop;
EXCEPTION
WHEN OTHERS THEN null;
END;
/
date_date DATE;
aaa_err number;
string_date varchar2(50);
i number;
max_i number;
BEGIN
string_date := '20080101';
max_i :=5;
for i in 1..max_i loop
date_date := to_date(string_date, 'yyyymmddhh24miss')+i-1; //主要是这里产生查找日期
select
(select count(*) from eai_audit
where interface_type = 'TF200720'
and seq = '-1'
and create_time between date_date and date_date +1
)
+
(select count(*) from eai_audit
where interface_type = 'TF202000'
and seq = '-1'
and create_time between date_date and date_date +1
)
into aaa_err from dual;
insert into table1(date_date,aaa_ERR)
values(
date_date,
aaa_ERR
); commit;
end loop;
EXCEPTION
WHEN OTHERS THEN null;
END;
/
解决方案 »
- sql*plus 和 pl/sql develop dbms_output.put_line结果输出问题
- gc current block 2-way 冲到了等待事件 TOP1,高人过来瞄一眼,谢谢
- 我想在新增一条记录之后,取得一个由触发器生成的数据
- 请教:如此的Oracle数据该如何恢复?急盼高手赐教!
- 找ORACLE同行合租
- 删除数据库用户求助!
- 高分求解!(jsp中怎样保存图片文件到oracle数据库,求原代码)!!
- 菜鸟问题
- 问个弱智的问题,oracle中osdba组和osoper组怎么建立,如何把新建的帐号归入这些组中
- 写了一个存储过程,老师要求不用游标遍历,用集合,怎么改
- pl/sql developer的一个问题
- plsql过程问题,如何将一个索引表插入到对应的表中,
EXCEPTION
WHEN OTHERS THEN null;
先注释掉,执行,看看出什么错再进一步修改。
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at "MBAPP.ICS_FOR", line 18
ORA-06512: at line 2具体是哪里的问题呢? 是date类型不匹配吗,我改了半天了?求助!
2 date_date DATE;
3 aaa_err number;
4 string_date varchar2(50);
5 i number;
6 max_i number;
7 BEGIN
8 string_date := '20080101';
9 max_i :=5;
10 begin
11 for i in 1..max_i loop
12 date_date := to_date(string_date,'yyyymmddhh24miss')+i-1;
13 select
14 (select count(*) from dual)+
15 (select count(*) from dual
16 )into aaa_err from dual;
17 insert into table1(date_date,aaa_ERR)
18 values(date_date, aaa_ERR );
19 commit;
20 end loop;
21 EXCEPTION WHEN OTHERS THEN
22 dbms_output.put_line('Error');
23 end;
24 END;
25 /Procedure createdSQL> execute aaa_for;PL/SQL procedure successfully completedSQL> select * from table1;DATE_DATE AAA_ERR
----------- ----------
01/01/2008 2
02/01/2008 2
03/01/2008 2
04/01/2008 2
05/01/2008 2
and to_date(date_date) +1 可以了.thanks again
是字符转数字时出错。interface_type字段是char/varchar类型吗?