IF sqlStrTemp IS NULL THEN sqlStrTemp := sqlStrTemp || 'SELECT * FROM (' || ' SELECT ' || '''' || t_data(v_index).A || '''' || ' AS A' || ',' || '''' || t_data(v_index).B || '''' || ' AS B' || ',' || || '''' || t_data(v_index).C || '''' || ' AS C' || ' FROM DUAL'; ELSE sqlStrTemp := sqlStrTemp || ' UNION ALL ' || t_data(v_index).A || '''' || ' AS A' || ',' || '''' || t_data(v_index).B || '''' || ' AS B' || ',' || || '''' || t_data(v_index).C || '''' || ' AS C' || ' FROM DUAL'; END IF; END LOOP; sqlStrTemp := sqlStrTemp || ') TEMP_DATA WHERE A<11'; EXECUTE IMMEDIATE sqlStrTemp BULK COLLECT INTO V_T_DATE(你想返回的记过集表) exception when others then null; end;
function return table variable然后
select * from tablevariable进行筛选
FOR index IN 1..t_data.COUNT-1 LOOP
IF sqlStrTemp IS NULL THEN
sqlStrTemp := sqlStrTemp
|| 'SELECT * FROM ('
|| ' SELECT ' || ''''
|| t_data(v_index).A || '''' || ' AS A' || ','
|| '''' || t_data(v_index).B || '''' || ' AS B' || ','
|| || '''' || t_data(v_index).C || '''' || ' AS C'
|| ' FROM DUAL';
ELSE
sqlStrTemp := sqlStrTemp
|| ' UNION ALL '
|| t_data(v_index).A || '''' || ' AS A' || ','
|| '''' || t_data(v_index).B || '''' || ' AS B' || ','
|| || '''' || t_data(v_index).C || '''' || ' AS C'
|| ' FROM DUAL';
END IF;
END LOOP; sqlStrTemp := sqlStrTemp || ') TEMP_DATA WHERE A<11'; EXECUTE IMMEDIATE sqlStrTemp BULK COLLECT INTO V_T_DATE(你想返回的记过集表)
exception when others then
null;
end;