create or replace procedure splitstr(srcstr in varchar2,splitstr in varchar2) is
temp varchar2(10);
sposition1 integer;
sposition2 integer;
mysubstr varchar2(10);
starttimes integer;
begin
starttimes:=1;
sposition1:=1;
loop
sposition2:=instr(srcstr,splitstr,1,starttimes);
mysubstr:=substr(srcstr,sposition1,sposition2-sposition1);
sposition1:=sposition2+1;
starttimes:=starttimes+1;
select job into temp from emp where ename=mysubstr; //出现问题处
dbms_output.put_line(mysubstr);
dbms_output.put_line(temp);
exit when sposition2=0;
end loop;
dbms_output.put_line('end');
end;
/
上面是我创建的存储过程,目的是传入以某一分隔符分隔的字符串,在存储过程中进行拆分,并以拆分出的子串作为查询条件,查询以该子串为名字的员工的工作名称。
存储过程能成功创建,但是执行时出现如下问题:
exec splitstr('ALLEN;SMITH;',';');
ALLEN
SALESMAN
SMITH
CLERK
BEGIN splitstr('ALLEN;SMITH;',';'); END;*
第 1 行出现错误:
ORA-01403: 未找到数据
ORA-06512: 在 "MZQ.SPLITSTR", line 18
ORA-06512: 在 line 1问题在select job into temp from emp where ename=mysubstr处,怀疑是ename(scott用户表emp中的一个属性,类型为varchar(10))和mysubstr数据类型的问题。查了半天但是没有解决,故来此请教,往各位大侠赐教啊!不胜感激~
temp varchar2(10);
sposition1 integer;
sposition2 integer;
mysubstr varchar2(10);
starttimes integer;
begin
starttimes:=1;
sposition1:=1;
loop
sposition2:=instr(srcstr,splitstr,1,starttimes);
mysubstr:=substr(srcstr,sposition1,sposition2-sposition1);
sposition1:=sposition2+1;
starttimes:=starttimes+1;
select job into temp from emp where ename=mysubstr; //出现问题处
dbms_output.put_line(mysubstr);
dbms_output.put_line(temp);
exit when sposition2=0;
end loop;
dbms_output.put_line('end');
end;
/
上面是我创建的存储过程,目的是传入以某一分隔符分隔的字符串,在存储过程中进行拆分,并以拆分出的子串作为查询条件,查询以该子串为名字的员工的工作名称。
存储过程能成功创建,但是执行时出现如下问题:
exec splitstr('ALLEN;SMITH;',';');
ALLEN
SALESMAN
SMITH
CLERK
BEGIN splitstr('ALLEN;SMITH;',';'); END;*
第 1 行出现错误:
ORA-01403: 未找到数据
ORA-06512: 在 "MZQ.SPLITSTR", line 18
ORA-06512: 在 line 1问题在select job into temp from emp where ename=mysubstr处,怀疑是ename(scott用户表emp中的一个属性,类型为varchar(10))和mysubstr数据类型的问题。查了半天但是没有解决,故来此请教,往各位大侠赐教啊!不胜感激~
temp varchar2(10);
sposition1 integer;
sposition2 integer;
mysubstr varchar2(10);
starttimes integer;
begin
starttimes:=1;
sposition1:=1;
loop
sposition2:=instr(srcstr,splitstr,1,starttimes);
mysubstr:=substr(srcstr,sposition1,sposition2-sposition1); --感觉是这里取值出的问题,取出来的可能不对,导致后面不匹配
sposition1:=sposition2+1;
starttimes:=starttimes+1;
select job into temp from emp where ename=mysubstr; //出现问题处
dbms_output.put_line(mysubstr);
dbms_output.put_line(temp);
exit when sposition2=0; --这里也有问题,应该改成exit when sposition2 is null;
end loop;
dbms_output.put_line('end');
end;
/
放到sposition2变量赋值的后面。改正后能正确执行的方法如下:create or replace procedure splitstr(srcstr in varchar2,splitstr in varchar2) is
temp varchar2(10);
sposition1 integer;
sposition2 integer;
mysubstr varchar2(10);
starttimes integer;
begin
starttimes:=1;
sposition1:=1;
loop
sposition2:=instr(srcstr,splitstr,1,starttimes);
exit when sposition2=0;
mysubstr:=substr(srcstr,sposition1,sposition2-sposition1);
sposition1:=sposition2+1;
starttimes:=starttimes+1;
select job into temp from emp where ename=mysubstr;
dbms_output.put_line(mysubstr);
dbms_output.put_line(temp);
end loop;
dbms_output.put_line('end');
end;
/
执行结果:
SQL> exec splitstr('ALLEN;SMITH;',';');
ALLEN
SALESMAN
SMITH
CLERK
end
temp varchar2(10);
sposition1 integer;
sposition2 integer;
mysubstr varchar2(10);
starttimes integer;
begin
starttimes:=1;
sposition1:=1;
loop
sposition2:=instr(srcstr,splitstr,1,starttimes);
mysubstr:=substr(srcstr,sposition1,sposition2-sposition1); exit when sposition2=0;
sposition1:=sposition2+1;
starttimes:=starttimes+1;
select job into temp from emp where ename=mysubstr; //出现问题处
dbms_output.put_line(mysubstr);
dbms_output.put_line(temp);
end loop;
dbms_output.put_line('end');
end;
temp varchar2(10);
sposition1 integer;
sposition2 integer;
mysubstr varchar2(10);
starttimes integer;
begin
starttimes:=1;
sposition1:=1;
loop
sposition2:=instr(srcstr,splitstr,1,starttimes);
exit when sposition2=0;
mysubstr:=substr(srcstr,sposition1,sposition2-sposition1);
sposition1:=sposition2+1;
starttimes:=starttimes+1;
select job into temp from emp where ename=mysubstr;
dbms_output.put_line(mysubstr);
dbms_output.put_line(temp);
end loop;
dbms_output.put_line('end');
end;
/