oralce中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;