oracle里的procedure和数组、函数

 
create or replace procedure proName(
   aa   IN VARCHAR2,
   bb   IN INTEGER,            
   result     OUT types.cursorType
)
IS
  v_un    DATE;
  v_u1  VARCHAR2(1000);
  v_u2  VARCHAR2(1000);
  sqlmain    VARCHAR2(5000); --主SQL
  v_count       INT;   --数据集的行数
  a INTEGER := 1;
  tb_recv TYPE_TABLE := TYPE_TABLE(); --oracle里的数组
  TYPE cur IS REF CURSOR;
  cur_local cur;
begin

proName2(..,...,..);--调用另外的存储过程

tb_recv  := funName（..,..）;

sqlmain := ' select sysdate a1,sysdate a2 from dual';

if ...then
 sqlmain := sqlmain || ' and dual.dummy = ''X'' ';
end if;

OPEN cur_local FOR sqlmain;

    LOOP
      FETCH cur_local
        INTO   v_u1,v_u2     

      EXIT WHEN cur_local%NOTFOUND;

      tb_recv.extend;
      tb_recv(a) := TYPE_ETM062_01_APPROVETYPE(v_u1,v_u2 );
        a := a + 1;
   END LOOP;

   OPEN result FOR
             SELECT v_u1,v_u2    FROM TABLE(CAST(tb_recv AS TYPE_TABLE ));

 EXCEPTION
   WHEN OTHERS THEN
    ........
   ROLLBACK;

end proName;