目的:写一过程,将输入的一串以‘,’分隔的人名字符串分解成一个个的字符串组返回给调用者。以嵌套表的方式显示结果。
已完成步骤:
1.在数据库中已添加嵌套表如下: create or replace type jihb_str_type as object
(
str varchar2(1024)
); create or replace type jihb_str_table_type is table of jihb_str_type; create table jihb_str_table
(
id number(16) primary key,
str varchar2(1024),
str_splited jihb_str_table_type
)nested table str_splited store as str_splited;2.在pl/sql developer中写过程如下: procedure str_split(in_name string) is
str string(1024);
str_start integer; --字符串开始位置
len integer; --字符子串长度
i integer;
j integer:=0;
str_splited jihb_str_table_type;
begin
for i in 1..length(in_name)
loop
str_start:=j+1;
j:=instr(in_name,',',1,i);
len:=j-str_start;
if (j=0) then
str:=substr(in_name,str_start,length(in_name));
/*这里要怎么将str值赋予str_splited???*/
--dbms_output.put_line(str);
exit;
end if;
str:=substr(in_name,str_start,len);
/*这里要怎么将str值赋予str_splited???*/
--dbms_output.put_line(str); end loop;
insert into jihb_str_table values (SEQ_jihb_str_table.Nextval,in_name,str_splited);
end;因为是初学pl/sql,所以不知道怎么将str的值赋予str_splited,各位帮帮忙,谢谢!
已完成步骤:
1.在数据库中已添加嵌套表如下: create or replace type jihb_str_type as object
(
str varchar2(1024)
); create or replace type jihb_str_table_type is table of jihb_str_type; create table jihb_str_table
(
id number(16) primary key,
str varchar2(1024),
str_splited jihb_str_table_type
)nested table str_splited store as str_splited;2.在pl/sql developer中写过程如下: procedure str_split(in_name string) is
str string(1024);
str_start integer; --字符串开始位置
len integer; --字符子串长度
i integer;
j integer:=0;
str_splited jihb_str_table_type;
begin
for i in 1..length(in_name)
loop
str_start:=j+1;
j:=instr(in_name,',',1,i);
len:=j-str_start;
if (j=0) then
str:=substr(in_name,str_start,length(in_name));
/*这里要怎么将str值赋予str_splited???*/
--dbms_output.put_line(str);
exit;
end if;
str:=substr(in_name,str_start,len);
/*这里要怎么将str值赋予str_splited???*/
--dbms_output.put_line(str); end loop;
insert into jihb_str_table values (SEQ_jihb_str_table.Nextval,in_name,str_splited);
end;因为是初学pl/sql,所以不知道怎么将str的值赋予str_splited,各位帮帮忙,谢谢!
str_splited(k):=str;
k:=k+1;
str := SUBSTR (p_str, i);
str_split.EXTEND;
str_split (str_split.COUNT) := str;