我估计用一个Sql查找起来比较困难,比如如果有2004-02-30这样的内容就很难查找,我以前的数据库中就是有这样的数据,最好写一个过程,循环一下,转换一下出现异常的肯定就有问题了。 cursor cur is select khrq from table; open cur ; loop
fetch cur into v_khrq; exception when others then 错误提示! exit when cur%notfound; end loop;
只有阿林的方法可行,但是问题又来了: cursor cur is select khrq from table; 这个语句根本不会产生 exception异常是在 to_date(khrq,'YYYY-MM-DD') 过程中产生的。 我用了下面过程,按hh排序,把出错的记录的上一个户号存到一个表中: declare v_hh varchar2(10); v_hh1 varchar2(10);
vd_zcrq date; cursor cur is select to_date(zcrq,'YYYY-MM-DD'),hh from xjnd_dbk where zcrq is not null order by hh; begin open cur ; loop begin
insert into aa(c1) values (v_hh); exception when others then insert into aa(c1) values (v_hh1); commit; v_hh1:=v_hh; exit when cur%notfound;
end ;
end loop; close cur; end;运行后,表aa中只有一条记录为空,我又看了 xjnd_dbk里没有空的hh请阿林及其他高手帮我看一下。
try: declare vd_zcrq date; cursor cur is select zcrq,hh from xjnd_dbk where zcrq is not null; begin for c_temp in cur loop vd_zcrq:=to_date(c_temp.zcrq,'yyyy-mm-dd'); exception when others then insert into aa(c1) values (c_temp.hh); end loop; end; /
to bzszp(SongZip): 你的方法很不错。运行的时候出现一个错误declare v_khrq date; cursor cur is select khrq,bh from smnd where zcrq is not null; begin open cur; for c_temp in cur loop v_zcrq:=to_date(c_temp.khrq,'yyyy-mm-dd'); exception when others then insert into aa(c1) values (c_temp.bh); end loop; close cur; end;ORA-06550: line 8, column 5: PLS-00103: Encountered the symbol "EXCEPTION" when expecting one of the following: begin declare end exit for goto if loop mod null pragma raise return select update while <an identifier> <a double-quoted delimited-identifier> <a bind variable> << close current delete fetch lock insert open rollback savepoint set sql execute commit forall <a single-quoted SQL string> ORA-06550: line 12, column 1: PLS-00103: Encountered the symbol "CLOSE" when expecting one of the following: begin function package pragma procedure form ORA-06550: line 16, column 0: PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following: begin function package pragma procedure subtype type use <an identifier> <a double-quoted delimited-identifier> cursor form current 光标停在exception 前
修改BZSZP的过程 declare v_khrq date; cursor cur is select khrq,bh from smnd where zcrq is not null; begin open cur; for c_temp in cur loop begin v_zcrq:=to_date(c_temp.khrq,'yyyy-mm-dd'); exception when others then insert into aa(c1) values (c_temp.bh); end; end loop; close cur; end;
不需要open和close游标。 declare v_khrq date; cursor cur is select khrq,bh from smnd where zcrq is not null; begin for c_temp in cur loop begin v_zcrq:=to_date(c_temp.khrq,'yyyy-mm-dd'); exception when others then insert into aa(c1) values (c_temp.bh); end; end loop; end;另外可以用函数来解决。 09:42:37 SQL> create or replace function f_is_date(v_date varchar2) 09:42:41 2 return number is 09:42:41 3 v_return number; 09:42:42 4 v_temp date; 09:42:47 5 begin 09:42:51 6 v_temp:=to_date(v_date,'yyyy-mm-dd'); 09:42:51 7 return 1; 09:42:51 8 exception when others then 09:42:51 9 return 0; 09:42:51 10 end f_is_date; 09:42:51 11 /函数已创建。已用时间: 00: 00: 00.44 09:42:52 SQL> select 1 from dual where f_is_date('2004-01-01')=0;未选定行已用时间: 00: 00: 00.6209:43:19 SQL> select 1 from dual where f_is_date('2004-13-01')=0; 1 ---------- 1已用时间: 00: 00: 00.31 09:43:32 SQL> 创建上面的函数后直接 select khrq,bh from smnd where f_is_date(zcrq)=0; 就可以找到格式不正确的行了。
查查数据长度够不够,在看看month那位的数据有没有>12的
cursor cur is select khrq from table;
open cur ;
loop
fetch cur into v_khrq;
exception
when others then
错误提示!
exit when cur%notfound;
end loop;
cursor cur is select khrq from table;
这个语句根本不会产生 exception异常是在 to_date(khrq,'YYYY-MM-DD') 过程中产生的。
我用了下面过程,按hh排序,把出错的记录的上一个户号存到一个表中:
declare
v_hh varchar2(10);
v_hh1 varchar2(10);
vd_zcrq date;
cursor cur is select to_date(zcrq,'YYYY-MM-DD'),hh from xjnd_dbk where zcrq is not null order by hh;
begin
open cur ;
loop
begin
insert into aa(c1) values (v_hh);
exception
when others then
insert into aa(c1) values (v_hh1);
commit;
v_hh1:=v_hh;
exit when cur%notfound;
end ;
end loop;
close cur;
end;运行后,表aa中只有一条记录为空,我又看了 xjnd_dbk里没有空的hh请阿林及其他高手帮我看一下。
declare
vd_zcrq date;
cursor cur is select zcrq,hh from xjnd_dbk where zcrq is not null;
begin
for c_temp in cur loop
vd_zcrq:=to_date(c_temp.zcrq,'yyyy-mm-dd');
exception
when others then
insert into aa(c1) values (c_temp.hh);
end loop;
end;
/
你的方法很不错。运行的时候出现一个错误declare
v_khrq date;
cursor cur is select khrq,bh from smnd where zcrq is not null;
begin
open cur;
for c_temp in cur loop
v_zcrq:=to_date(c_temp.khrq,'yyyy-mm-dd');
exception
when others then
insert into aa(c1) values (c_temp.bh);
end loop;
close cur;
end;ORA-06550: line 8, column 5:
PLS-00103: Encountered the symbol "EXCEPTION" when expecting one of the following: begin declare end exit for goto if loop mod null pragma raise
return select update while <an identifier>
<a double-quoted delimited-identifier> <a bind variable> <<
close current delete fetch lock insert open rollback
savepoint set sql execute commit forall
<a single-quoted SQL string>
ORA-06550: line 12, column 1:
PLS-00103: Encountered the symbol "CLOSE" when expecting one of the following: begin function package pragma procedure form
ORA-06550: line 16, column 0:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following: begin function package pragma procedure subtype type use
<an identifier> <a double-quoted delimited-identifier> cursor
form current
光标停在exception 前
declare
v_khrq date;
cursor cur is select khrq,bh from smnd where zcrq is not null;
begin
open cur;
for c_temp in cur loop
begin
v_zcrq:=to_date(c_temp.khrq,'yyyy-mm-dd');
exception
when others then
insert into aa(c1) values (c_temp.bh);
end;
end loop;
close cur;
end;
declare
v_khrq date;
cursor cur is select khrq,bh from smnd where zcrq is not null;
begin
for c_temp in cur loop
begin
v_zcrq:=to_date(c_temp.khrq,'yyyy-mm-dd');
exception
when others then
insert into aa(c1) values (c_temp.bh);
end;
end loop;
end;另外可以用函数来解决。
09:42:37 SQL> create or replace function f_is_date(v_date varchar2)
09:42:41 2 return number is
09:42:41 3 v_return number;
09:42:42 4 v_temp date;
09:42:47 5 begin
09:42:51 6 v_temp:=to_date(v_date,'yyyy-mm-dd');
09:42:51 7 return 1;
09:42:51 8 exception when others then
09:42:51 9 return 0;
09:42:51 10 end f_is_date;
09:42:51 11 /函数已创建。已用时间: 00: 00: 00.44
09:42:52 SQL> select 1 from dual where f_is_date('2004-01-01')=0;未选定行已用时间: 00: 00: 00.6209:43:19 SQL> select 1 from dual where f_is_date('2004-13-01')=0; 1
----------
1已用时间: 00: 00: 00.31
09:43:32 SQL> 创建上面的函数后直接
select khrq,bh from smnd where f_is_date(zcrq)=0;
就可以找到格式不正确的行了。