我定义了 一个类型 和一个嵌套表 如下
create or replace type typ_point1 AS OBJECT(
代码1 varchar(3),
代码2 varchar(8),
代码3 Timestamp,
代码4 number(10),
代码5 number(11),
代码6 number(11),
代码7 number(16),
代码8 number(7),
代码9 number(7),
代码10 number(7),
代码11 varchar(4),
代码12 varchar(1),
代码13 number(8,2) ,
代码14 number(8,2) ,
代码15 number(7),
代码16 number(7) );
create or replace TYPE typ_point1newt as table of typ_point1new;
CREATE TABLE "C##GAOMIAO"."2MINS"
( "编号" NUMBER(12,0),
"上级编号" NUMBER(12,0),
"参照点" "C##GAOMIAO"."TYP_POINT1new" , )
在存储函数中 使用
SELECT 参照点 into wtf FROM LUOWEN_2MINS WHERE 上级编号 = INTPUT2 AND 编号 = I;
得到wtf （TYP_POINT1new）那么我如何用一个数组varray来得到里面的数据呢 例如 代码5 ，之后使用数组来进行运算。
------解决思路----------------------
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>