Oracle – Passing cursor record to a function

oracleoracle10gplsqlsqlplus

I have a stored procedure. I want to call a function from it. Want to pass the retrieved cursor record to the function. how can i pass the retrieved cursor record as function argument and how can i access it inside the function? How do i declare the function?

CREATE OR REPLACE PROCEDURE service__update as

cursor c_getData is
    select    *
    from  service_1
    where status=5    ;


begin
    dbms_output.enable(null);    

    for rec in c_getData loop

    function(rec)

Best Answer

Assuming that you really want a function (which implies that you want to return a value) rather than a procedure (which does not return a value) and assuming that your cursor really is selecting every column from a single table, you can declare a function that takes an anchored %ROWTYPE 

SQL> create function get_empno( p_rec in emp%rowtype )
  2    return number
  3  is
  4  begin
  5    return p_rec.empno;
  6  end;
  7  /

Function created.

and then call that function from your procedure 

SQL> declare
  2    l_empno emp.empno%type;
  3  begin
  4    for i in (select * from emp)
  5    loop
  6      l_empno := get_empno( i );
  7      dbms_output.put_line( l_empno );
  8    end loop;
  9  end;
 10  /
7369
7499
7521
7566
7654
7698
7782
7788
7839
7844
7876
7900
7902
7934

PL/SQL procedure successfully completed.
Related Topic