plsql - What is the standard way to return records from an Oracle function? -

i avoided "best" word question suitable word it.

what's best(most efficient) way of returning records function?

currently have like:

  function myfunct(param1 varchar2) return sys_refcursor     mycursor sys_refcursor;   begin     open mycursor     select *     mytable     field = param1;      return(mycursor);   end myfunct; 

i can run fine else reading (table type, implicit cursor, etc) confused suitable.

p.s. how can loop on cursor after call proc?

edit: i've read can iterate through cursors once (forums.oracle.com/thread/888365) in reality want loop contents several times. mean opt use associative arrays instead?

create or replace  package example_pkg      /*     ** record , nested table "dual" table     ** global, can use in other packages     */     type g_dual_ntt table of sys.dual%rowtype;     g_dual  g_dual_ntt;      /*     ** procedure public. may want use in different parts of code     */     function myfunct(param1 varchar2) return sys_refcursor;      /*     ** example work cursor     */     procedure example_prc;  end example_pkg;  create or replace  package body example_pkg      function myfunct(param1 varchar2) return sys_refcursor             mycursor sys_refcursor;     begin         open mycursor             select  dummy                dual               dummy = param1;          return(mycursor);     end myfunct;      procedure example_prc             mycursor sys_refcursor;         l_dual   g_dual_ntt; /* bulk collect there no need initialize collection */     begin         -- open cursor         mycursor := myfunct('x');         -- fetch cursor  /  @ onece         fetch mycursor bulk collect l_dual;         -- close cursor         close mycursor;          dbms_output.put_line('print: ');         indx in 1..l_dual.count loop             dbms_output.put_line('element: ' || l_dual(indx).dummy );         end loop;     end example_prc;  end example_pkg;  execute example_pkg.example_prc();  /* print:  element: x */ 

please take @ link: http://www.oracle-base.com/articles/misc/using-ref-cursors-to-return-recordsets.php

you might find useful...