oralce聚合

CREATE OR REPLACE FUNCTION fun_checkguaranteetype(TranId varchar2) 
return varchar2 is V_result varchar2(1000); 
Begin
  declare
    zhiyavar  NUMBER; 
    diyavar   NUMBER; 
    dabaoren  NUMBER; 
    v_sql     varchar2(1000); 
    --定义type 
    TYPE accountno_type IS TABLE OF actaccount.accountno%TYPE INDEX BY BINARY_INTEGER; 
    TYPE id_type IS TABLE OF bizpawn.id%TYPE INDEX BY BINARY_INTEGER; 
   --集合列 
    accountno   accountno_type;    
    id   id_type;    
  begin
    v_sql :='select p1.accountno accountno ,pawn.id id,pawn.nocredencemode nocredencemode from bizpawn pawn,('; 
    v_sql :=v_sql||' select act.relationaccountid,act.accountno from actaccount act where act.id in'; 
    v_sql :=v_sql||'(select trans.accountid from acttransaction trans where trans.id in ('||TranId||')) ) p1 '; 
    v_sql :=v_sql||'where pawn.actaccountid=p1.relationaccountid'; 
    --执行动态sql 
    EXECUTE IMMEDIATE v_sql BULK COLLECT into accountno,id; 
    --for 
    for V1 IN  1 .. accountno.COUNT LOOP 
    --获取值 
    dbms_output('accountno:'||accountno(v1)||'---id:'||id(v1)); 
    END LOOP; 
    return (V_result); 
  end; 
end fun_checkguaranteetype;