create function abc
return rowid
is
out_rowid rowid;
begin
select to_number(Y) ,rowid into out_rowid from song ;
exception
return(out_rowid);end abc;
Y是VARCHER型的字段,应该存储能够转换成数字的字符,但现在由于误操作,字段中有了不能转换成数字的字符,所以抛出了“无效数字”的异常(ora-01722)我想通过exception的方式,求出产生异常的rowid,不知如何作,请高手指点、最好写出完整的代码,谢谢了!
return rowid
is
out_rowid rowid;
begin
select to_number(Y) ,rowid into out_rowid from song ;
exception
return(out_rowid);end abc;
Y是VARCHER型的字段,应该存储能够转换成数字的字符,但现在由于误操作,字段中有了不能转换成数字的字符,所以抛出了“无效数字”的异常(ora-01722)我想通过exception的方式,求出产生异常的rowid,不知如何作,请高手指点、最好写出完整的代码,谢谢了!
2 INSERT INTO SONG VALUES('1123');
3 INSERT INTO SONG VALUES('178');
4 INSERT INTO SONG VALUES('1S1S');
5 INSERT INTO SONG VALUES('ABC');
6 END ;
7 /PL/SQL 过程已成功完成。SQL> SELECT * FROM SONG;Y
----------
1123
178
1S1S
ABC已选择4行。
SQL> create or replace function abc(
2 feild in varchar2
3 )return rowid
4 is
5 out_rowid rowid;
6 INT_Y INTEGER;
7 begin
8 select ROWID,to_number(Y) into out_rowid,INT_Y from SONG where y=feild;
9 return null;
10
11 exception
12 WHEN OTHERS THEN
13 IF SQLCODE=-1722 THEN
14 OUT_ROWID:=OUT_ROWID;
15 return(out_rowid);
16 END IF;
17 end abc;
18 /函数已创建。SQL> select abc(y) from song where abc(y) is not null;ABC(Y)
------------------
AAAAyEAAHAAAAAiAAC
AAAAyEAAHAAAAAiAAD已选择2行。
SQL> /S Y Z ROWID
---------- ---------- ---------- ------------------
222 ppp 1111 AAAHbrAAJAAAAKEAAA
00001 holen 1111 AAAHbrAAJAAAAKHAAA
00001 holen 1111 AAAHbrAAJAAAAKHAABSQL> select abc(y) from song where abc(y) is not null;ABC(Y)
------------------
AAAHbrAAJAAAAKEAAA
AAAHbrAAJAAAAKHAAA
AAAHbrAAJAAAAKHAAA
大侠,我执行了你的函数,可是rowid和select出的rowid有的时候不一致,这是为何亚?
----------
1123
178
1S1S
ABC
1S1S已选择5行。建一个临时表SQL> create global temporary table temp_table(y varchar2(10),row_id rowid);表已创建。SQL> create or replace package song_pkg
2 as
3 type t_ref is ref cursor return temp_table%rowtype;
4 function song_abc return t_ref;
5 end;
6 /程序包已创建。SQL> create or replace package body song_pkg
2 as
3 function song_abc
4 return t_ref
5 as
6 mycur t_ref ;
7 int_y INTEGER;
8 begin
9 for x in(select ROWID,y from SONG ) loop
10 begin
11 int_y:=to_number(x.y);
12 exception
13 when others then
14 int_y:=sqlcode;
15 if sqlcode=-6502 then
16 insert into temp_table values(x.y,x.rowid);
17 end if;
18 end;
19 end loop;
20
21 open mycur for select * from temp_table;
22 return mycur;
23 end;
24 end;
25 /程序包主体已创建。SQL> declare
2 type t_ref is ref cursor ;
3 mycur t_ref;
4 type
5 t_record_type is record(y song.y%type,row_id rowid);
6 r t_record_type;
7 begin
8 mycur:=song_pkg.song_abc;
9 fetch mycur into r;
10 while mycur%found loop
11 dbms_output.put_line(rpad(r.y,8,' ')||r.row_id);
12 fetch mycur into r;
13 end loop;
14 close mycur;
15 commit;
16 exception
17 when others then
18 dbms_output.put_line('no data!');
19 end;
20 /
1S1S AAAAyEAAHAAAAAiAAC
ABC AAAAyEAAHAAAAAiAAD
1S1S AAAAyEAAHAAAAAiAAEPL/SQL 过程已成功完成。