嵌套表的使用实例

1.嵌套表的创建
Create Type TestRecord Is Object(Id int,Name Varchar2(20));
Create Type TestTable  Is Table Of TestRecord;
2。嵌套表的使用

CREATE OR REPLACE Procedure P_Test(retcur out pack_cursor.t_retdataset)
Is
rec1    CHECKRESULT_TYPE;
rec2    CHECKRESULT_TYPE;
datatab CHECKRESULTTable;
v_out   varchar2(100);
Create Type CHECKRESULTTable  Is Table Of CHECKRESULT_TYPE;
Begin
    --实例化一条记录
    rec1:=CHECKRESULT_TYPE('test01','test01','test01','test01','test01','test01','test01','test01','test01','test01');
    datatab := CHECKRESULTTable();
   
    datatab.extend(2);
    --往集合中插入数据
    datatab(1):=rec1;
   
    rec2:=CHECKRESULT_TYPE('test01','test01','test01','test01','test01','test01','test01','test01','test01','test01');
  
    datatab(2):=rec2;
 
    select  distinct siteid into v_out from table(datatab);
    
     --打开游标返回数据
     Open retcur For select * from table(cast(datatab as CHECKRESULTTable ));
     loop fetch retcur into v_out, v_out, v_out, v_out, v_out, v_out, v_out, v_out, v_out, v_out;
     exit when retcur%notfound;
     dbms_output.put_line(v_out);
     end loop;
End;

--create type  CHECKRESULT_TYPE IS OBJECT(siteid varchar2(20),sitename varchar2(20),sitelevel varchar2(20)
--, lastfeedback varchar2(20),tasktime varchar2(20),taskid varchar2(20),tasktype varchar2(20),
--tasktopic varchar2(500), taskstatus varchar2(20),annexurl varchar2(100));

--Create Type CHECKRESULTTable  Is Table Of CHECKRESULT_TYPE;