17,377
社区成员
发帖
与我相关
我的任务
分享
SQL>
SQL> create type typ_point1 as object(obj1 varchar2(3),obj2 varchar2(8))
2 /
Type created
SQL> create type typ_point1newt as table of typ_point1
2 /
Type created
SQL>
SQL>
SQL> create table test_array(
2 id number,
3 obj typ_point1newt
4 )
5 nested table obj store as nested_tab return as value
6 /
Table created
SQL>
SQL> --造些数据
SQL>
SQL> create or replace function f_dividing(i_str in varchar2) return typ_point1newt
2 PIPELINED is
3 begin
4 for i in 1 .. length(i_str) - length(replace(i_str, ','))+1 loop
5 pipe row(typ_point1(i,
6 substr(i_str,
7 instr(','||i_str, ',', 1, i ) ,
8 instr(i_str||',', ',', 1, i ) -
9 instr(','||i_str, ',', 1, i ) )));
10 end loop;
11 return;
12 end f_dividing;
13 /
Function created
SQL> insert into test_array values(1,f_dividing('a,b,c,d'));
1 row inserted
SQL> insert into test_array values(2,f_dividing('1,2,3,4,5'));
1 row inserted
SQL> commit;
Commit complete
SQL> --存储过程操作数组
SQL> set serverout on
SQL>
SQL> declare
2 v_obj typ_point1newt; --从表中获取数据
3 type typ_varray is varray(100) of varchar2(10);
4 v_array typ_varray:=typ_varray(); --定义数组
5 begin
6 select obj into v_obj from test_array where id=1;
7 for i in 1..v_obj.count loop
8 --存入数组
9 v_array.extend;
10 v_array(i):=v_obj(i).obj2;
11 end loop;
12 --输出数组
13 for j in 1..v_array.count loop
14 dbms_output.put_line(v_array(j));
15 end loop;
16 end;
17 /
a
b
c
d
PL/SQL procedure successfully completed
SQL>