临时表的两种用法

先创建 行对象，再创建 行对象集合

create or replace type pmmgdover_record_aa as object (
            vdate         VARCHAR2(20),
       partnum       VARCHAR2(20),
       revision      VARCHAR2(20),
       gongling      VARCHAR2(30),
  lotnum        VARCHAR2(20),
  xialiaomianji VARCHAR2(20),
  isscls002     VARCHAR2(20),
  defectcode    VARCHAR2(20),
  defname       VARCHAR2(50),
  tichuproc     VARCHAR2(20),
  linename     NVARCHAR2(30),
  line        VARCHAR2(20),
  scraparea     NVARCHAR2(20),
  workno        VARCHAR2(30),
  pcname        VARCHAR2(30),
  zeprocline    VARCHAR2(20),
  zeprocode      VARCHAR2(20)
    );
    
    --drop type pmmgdover_record_aa_array;
    
create or replace type pmmgdover_record_aa_array as table of pmmgdover_record_aa; 



创建 function  




--create or replace function f_pmmgdover_record_aa_array(curdate varchar2 default null) return pmmgdover_record_aa_array as  
       aa_array pmmgdover_record_aa_array := pmmgdover_record_aa_array();
       v_number number;
       v_curdate varchar2(20); -- 結案日期
       v_curdate2 varchar2(20); -- 結案日期
       v_curdate3 varchar2(20); -- 結案日期
       --id1 number;
       --time1 date;
       --data1 varchar2(60);
     BEGIN
       
    -- Dbms_Output.enable(buffer_size=>null);
       
      if curdate is null then
        v_curdate:= to_char(sysdate-1,'yyyyMMdd');
        else
          v_curdate:=curdate;
        end if;
        
      --  dbms_output.put_line(v_curdate);
      v_curdate2:=to_char(to_date(v_curdate,'yyyyMMdd')-90,'yyyyMMdd');
     -- dbms_output.put_line(v_curdate2);
      v_curdate3:=to_char(to_date(v_curdate,'yyyyMMdd')-180,'yyyyMMdd');
        
      v_number:=0;

 
    
    
     for aa_rowdata in (
select 
        b.VDATE,
       partnum,
       revision,
       b.WORKNO gongling,
       lotnum,
     --  outputarea,
       c.lyarea xialiaomianji,
       info.ISSCLS002,
       defectcode,
       defname,
      tichuproc,
       linename,
       line,
       scraparea,
       info.LOTINFO032 workno,
       pcname,
       a.procline zeprocline,
       a.proccode zeprocode
  from  ---
                )
    loop
      v_number:=v_number+1;
       aa_array.extend;
       
       aa_array(v_number):=pmmgdover_record_aa('20201021','20201021','20201021','20201021','20201021','20201021','20201021','20201021','20201021','20201021','20201021','20201021','20201021','20201021','20201021','20201021','20201021');

       aa_array(v_number).vdate:=aa_rowdata.vdate;
       aa_array(v_number).partnum:=aa_rowdata.partnum;
       aa_array(v_number).revision:=aa_rowdata.revision;
       aa_array(v_number).gongling:=aa_rowdata.gongling;
       aa_array(v_number).lotnum:=aa_rowdata.lotnum;
       aa_array(v_number).xialiaomianji:=aa_rowdata.xialiaomianji;
       aa_array(v_number).isscls002:=aa_rowdata.isscls002;
       aa_array(v_number).defectcode:=aa_rowdata.defectcode;
       aa_array(v_number).defname:=aa_rowdata.defname;
       aa_array(v_number).tichuproc:=aa_rowdata.tichuproc;
       aa_array(v_number).linename:=aa_rowdata.linename;
       aa_array(v_number).line:=aa_rowdata.line;
       aa_array(v_number).scraparea:=aa_rowdata.scraparea;
       aa_array(v_number).workno:=aa_rowdata.workno;
       aa_array(v_number).pcname:=aa_rowdata.pcname;
       aa_array(v_number).zeprocline:=aa_rowdata.zeprocline;
       aa_array(v_number).zeprocode:=aa_rowdata.zeprocode;
       
       
       
       
    end loop;
    return aa_array;
     
     END;



使用的时候
  /*

?用行集合?型和其他表?合
 SELECT *
 FROM table(f_test_array()) T1
  ,table(f_test_array()) T2
  ,T_TEST_PIP T3
 WHERE T1.ID = T2.ID
 AND   T1.ID = T3.ID ;*/