嵌套表的使用实例

来源：互联网发布：mac模拟人生3pack文件编辑：程序博客网时间：2024/06/06 02:16

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;

0 0