PL/SQL 表函数， Cursor Variable, pipelined table function

表函数： 返回varray 或nested table。使用方式： select * from table(func());

Pipelined table function returns a row to its invoker immediately after processing that row and continues to process rows.

sys_refcursor: PL/SQL预定义弱类型cursor variable. 常用于PL/SQL之间传递结果集，这时传递的只是一个指针。

declare
  v_cursor  sys_refcursor;
  v_query varchar2(4000) := 'select first_name, last_name from employees';
  type result is record(
    first_name varchar2(100),
    last_name  varchar2(100)
  );
type result_tab is table of  result;
v_result_tab result_tab;
begin
 open v_cursor for v_query ;
 loop
  fetch v_cursor bulk collect into v_result_tab limit 100;
  exit when v_cursor%notfound;
    if v_result_tab.count > 0 then
      FOR i IN v_result_tab.FIRST .. v_result_tab.LAST
        loop
          dbms_output.put_line(v_result_tab(i).last_name);
      END LOOP;
    end if;  
 end loop;
end;

Cursor variable 和pipelined table function常常被用于做ETL的Transformation。