请各位朋友帮助确认存储过程的错误
工作要求对客户表dclient表fax字段进行清洗，要求字段中只含有数字。
字段中有很多数据，
fax
FAX-87053000
FAX-85250208
FAX-7083488
7074837（F）
FAX-8810004
FAX-5170062
88060687FAX
85304470（张
88060687FAX
等数据，现要求取只取数字的结果，即查询结果为：
87053000
85250208
7083488
8810004
5170062
88060687
85304470
88060687
我按照网上的朋友提供的思路作出了存储过程，但是不能达到预期的效果，请各位朋友帮忙看看，我存储过程是这样的：
create or replace procedure test3 is
v_fax t97.dclient.fax%type;
bb varchar2(100);
dd varchar2(100);
i number(10);
cursor mycursor is
select fax from t97.dclient where rownum<100;
begin
bb:='';
dd:='';
open mycursor;
loop
fetch mycursor into v_fax;
for i in 0..length(v_fax) loop
bb:=substr(v_fax,i,1);
--dbms_output.put_line(bb);
if(ascii(bb)>=48 and ascii(bb)<=57)then
dd:=bb||dd;
end if;
end loop;
dbms_output.put_line(dd);
--v_fax:=dd;
--dbms_output.put_line('fax is'||v_fax);
exit when mycursor%notfound;
end loop;
close mycursor;
end ;
单步跟踪发现for i in 0..length(v_fax) loop这条语句有问题，但我还是搞不定，请各位朋友帮忙
问题点数：20、回复次数：13Top
1 楼duanzilin（寻）回复于 2006-02-18 16:51:52 得分 0
for i in 0..length(v_fax) loop
bb:=substr(v_fax,i,1);
---------------------------
错在这里，i应该从1开始循环，如果i从0开始循环的话substr(v_fax,0,1)和substr(v_fax,1,1)都是取子串的第一个字符
这个问题可以用translate函数嵌套方式解决，比如楼主的问题可以这样：
SELECT translate(fax,translate(fax,'0123456789','#'),'#') FROM dclient;
这里参数'#'可以替换成fax字段里不会出现的任意字符，或者替换成chr(30)，这个字符不能通过普通录入方式插入的，应该不会出现在fax字段中
Top
2 楼renwox88（）回复于 2006-02-18 18:05:34 得分 0
duanzilin(寻)，这为朋友：我的dclient表里340多万条记录，很多字符我是不知道的，要进行字段清洗，把所有的非数字型的数据全部都要去掉的，所有我才用存储过程的，fax字段里还有人的姓名之类的东西，我才用存储过程的啊。我将i该成1过的，还是不行啊，请你再帮忙看看啦，我是现在确实是搞不定啊！谢谢啊！Top
3 楼renwox88（）回复于 2006-02-18 19:15:08 得分 0
我做这件主要是进行数据清洗就就是要将dclient表的fax字段的不是数字的全都清除，就是要update一下啦，请各位朋友帮看看啦！Top
4 楼bluecocoqd（小骗骗）回复于 2006-02-18 20:32:56 得分 0
create or replace procedure test3 is
v_fax t97.dclient.fax%type;
bb varchar2(100);
dd varchar2(100);
i number(10);
vi_rowcount number(10);
cursor mycursor is
select fax from t97.dclient ;
begin
vi_rowcount := 0;
bb:='';
dd:='';
open mycursor;
loop
fetch mycursor into v_fax;
for i in 1..length(v_fax) loop
bb:=substr(v_fax,i,1);
if(ascii(bb)>=48 and ascii(bb)<=57)then
dd:=bb||dd;
end if;
end loop;
vi_rowcount := vi_rowcount + 1;
dbms_output.put_line(vi_rowcount||':'||dd);
exit when mycursor%notfound;
end loop;
close mycursor;
end ;
这样可以吗？Top
5 楼boydgmx（授人以鱼不如授人以渔(baidu&google)）回复于 2006-02-18 20:48:37 得分 20
创建一个函数：
CREATE OR REPLACE FUNCTION GET_NUMBER(theStr IN VARCHAR2 DEFAULT NULL) RETURN VARCHAR2
AS
CURR_CHAR VARCHAR2(3);
rtVal VARCHAR2(4000);
BEGIN
rtVal:=NULL;
IF theStr IS NOT NULL THEN
FOR i IN 1..LENGTH(theStr) LOOP
CURR_CHAR:=SUBSTR(theStr,i,1);
--ASCII(0..9)=48..57
IF ASCII(CURR_CHAR) BETWEEN 48 AND 57 THEN
rtVal:=rtVal || CURR_CHAR;
END IF;
END LOOP;
END IF;
RETURN rtVal;
EXCEPTION
WHEN OTHERS THEN RETURN NULL;
END;
/
SQL> SELECT GET_NUMBER('&STR') FROM DUAL;
输入 str 的值: 010-12345678
原值 1: SELECT GET_NUMBER('&STR') FROM DUAL
新值 1: SELECT GET_NUMBER('010-12345678') FROM DUAL
GET_NUMBER('010-12345678')
---------------------------------------------------------
01012345678
SQL> SELECT GET_NUMBER('&STR') FROM DUAL;
输入 str 的值: 12345678(梦霄)
原值 1: SELECT GET_NUMBER('&STR') FROM DUAL
新值 1: SELECT GET_NUMBER('12345678(梦霄)') FROM DUAL
GET_NUMBER('12345678(梦霄)')
---------------------------------------------------------
12345678
然后就可以直接更新你那个字段了：
UPDATE dclient SET fax=GET_NUMBER(fax) WHERE NVL(fax,'X')<>NVL(GET_NUMBER(fax),'X');
注意事项：
1、函数虽然要返回纯数字，但不能 RETURN NUMBER，因为这样一来 010 前面的0就没有了
2、UPDATE 的时候，WHERE子句中的 NVL 必不可少，有可能出现一个 fax内容全是汉字，这样函数返回值是NULL，如果不用 NVL，则该行就不能成功清洗Top
6 楼renwox88（）回复于 2006-02-18 21:01:34 得分 0
bluecocoqd你好啊，可是还调试不出来啊，还是显示 for i in 1..length(v_fax) loop这行有问题，我单步追踪了下，感觉运行应该对的，但是就是有问题啊！Top
7 楼bluecocoqd（小骗骗）回复于 2006-02-18 21:23:26 得分 0
什么错Top
8 楼bluecocoqd（小骗骗）回复于 2006-02-18 21:25:17 得分 0
create or replace procedure test3 is
v_fax t97.dclient.fax%type;
bb varchar2(100);
dd varchar2(100);
i number(10);
vi_len number(10);
vi_rowcount number(10);
cursor mycursor is
select fax from t97.dclient ;
begin
vi_rowcount := 0;
bb:='';
dd:='';
open mycursor;
loop
fetch mycursor into v_fax;
vi_len := length(v_fax);
for i in 1..vi_len loop
bb:=substr(v_fax,i,1);
if(ascii(bb)>=48 and ascii(bb)<=57)then
dd:=bb||dd;
end if;
end loop;
vi_rowcount := vi_rowcount + 1;
dbms_output.put_line(vi_rowcount||':'||dd);
exit when mycursor%notfound;
end loop;
close mycursor;
end ;
未必还会有问题？Top
9 楼renwox88（）回复于 2006-02-18 21:34:32 得分 0
boydgmx(梦霄) 这位朋友你好，2、UPDATE 的时候，WHERE子句中的 NVL 必不可少，有可能出现一个 fax内容全是汉字，这样函数返回值是NULL，如果不用 NVL，则该行就不能成功清洗这点我可以做到，因为汉字在 TO_MULTI_BYTE 和 TO_SINGLE_BYTE 之后均保持不变，
从而可以使用如下语句检测一个字符串中是否存在非汉字字符：
SELECT COUNT(*) FROM Yourtab WHERE TO_MULTI_BYTE(col)<>TO_SINGLE_BYTE(col);现在dclient表fax字段全都是汉字的只有2个，我直接就可以更新啦，你说的1、函数虽然要返回纯数字，但不能 RETURN NUMBER，因为这样一来 010 前面的0就没有了，恕我愚钝，没明白你说的什么意思？
Top
10 楼renwox88（）回复于 2006-02-18 22:04:32 得分 0
bluecocoqd这位朋友你好， 问题还是在 for i in 1..vi_len loop，提示显示数字或数字出错Top
11 楼boydgmx（授人以鱼不如授人以渔(baidu&google)）回复于 2006-02-19 16:12:26 得分 0
对于数字而言，前导0是没有意义的 010 = 10 ，但字符串 '010' <> '10'Top
12 楼renwox88（）回复于 2006-02-19 20:46:33 得分 0
梦宵，谢谢你的帮助啊！谢谢你的解答！Top
13 楼prcgolf（小鸟）回复于 2006-02-20 00:31:25 得分 0
upTop
