使用下面存储过程可以实现 create or replace procedure pro_insert_school(i_address in varchar2,--地址 i_name in varchar2--多个名字以,分隔，如'abc,bcd' ) as v_substr_name varchar2(50); v_loop_num number(10); v_name varchar2(32000); begin v_name:=','||i_name; select length(v_name)-length(replace(v_name,',','')) into v_loop_num from dual;--计算名字个数 for ii in 1..v_loop_num loop if ii<v_loop_num then v_substr_name:=substr(v_name,instr(v_name,',',ii)+1,instr(v_name,',',ii+1)-instr(v_name,',',ii)-1); else v_substr_name:=substr(v_name,instr(v_name,',',ii)+1); end if; --dbms_output.put_line(v_substr_name); insert into v_school(name,address)values （v_substr_name，i_address); end loop; end;
create or replace procedure pro_insert_school(i_address in varchar2,--地址
i_name in varchar2--多个名字以,分隔，如'abc,bcd'
)
as
v_substr_name varchar2(50);
v_loop_num number(10);
v_name varchar2(32000);
begin
v_name:=','||i_name;
select length(v_name)-length(replace(v_name,',','')) into v_loop_num from dual;--计算名字个数
for ii in 1..v_loop_num loop
if ii<v_loop_num then
v_substr_name:=substr(v_name,instr(v_name,',',ii)+1,instr(v_name,',',ii+1)-instr(v_name,',',ii)-1);
else
v_substr_name:=substr(v_name,instr(v_name,',',ii)+1);
end if;
--dbms_output.put_line(v_substr_name);
insert into v_school(name,address)values （v_substr_name，i_address);
end loop;
end;