procedure returning %ROWTYPE

PROCEDURE Get_emp_rec (Emp_number  IN  Emp_tab.Empno%TYPE,
                       Emp_ret     OUT Emp_tab%ROWTYPE) IS
BEGIN
   SELECT Empno, Ename, Job, Mgr, Hiredate, Sal, Comm, Deptno
      INTO Emp_ret
      FROM Emp_tab
      WHERE Empno = Emp_number;
END;

You could call this procedure from a PL/SQL block as follows:

 

DECLARE
   Emp_row      Emp_tab%ROWTYPE;     -- declare a record matching a
                                     -- row in the Emp_tab table
BEGIN
   Get_emp_rec(7499, Emp_row);   -- call for Emp_tab# 7499
   DBMS_OUTPUT.PUT(Emp_row.Ename || ' '                || Emp_row.Empno);
   DBMS_OUTPUT.PUT(' '           || Emp_row.Job || ' ' || Emp_row.Mgr);
   DBMS_OUTPUT.PUT(' '           || Emp_row.Hiredate   || ' ' || Emp_row.Sal);
   DBMS_OUTPUT.PUT(' '           || Emp_row.Comm || ' '|| Emp_row.Deptno);
   DBMS_OUTPUT.NEW_LINE;
END;

Using RECORD Type and TABLE TYPE

DECLARE
   TYPE name_rec IS RECORD ( first_name VARCHAR2(20), last_name VARCHAR2(25) );
   TYPE names IS table OF name_rec;
   l_name_rec name_rec;
   l_names names;
BEGIN
   l_names := names();
 
   for i in 1..10 loop
     l_name_rec.first_name := 'fname'||i;
     l_name_rec.last_name := 'Lname'|| i;
     l_names.extend(1);
     l_names(i) := l_name_rec  ;
   end loop ;
 
   /* call procedure whatever
        Bulk_update (job_id,  l_names ); */
 
   for i in l_names.first..l_names.last loop
      DBMS_OUTPUT.PUT_LINE('[' ||i ||'] ' ||l_names(i).first_name || ', '|| l_names(i).last_name  );
   end loop ;
 
END;
/

plan in prompt

ops$tkyte@ORA10G> exec dbms_stats.gather_table_stats( user, 'T', method_opt=>'for all 
indexed columns');
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA10G> select * from t where object_id = 42;
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1 Bytes=93)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T' (TABLE) (Cost=2 Card=1 Bytes=93)
   2    1     INDEX (RANGE SCAN) OF 'T_IDX' (INDEX) (Cost=1 Card=1)
 
 
 
ops$tkyte@ORA10G> set autotrace off

from asktom..

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:40230704959128