oracle中for循环配合动态sql,oralce中for执行动态sql

--oralce中 for 执行动态sql

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;