Re: PL/SQL-how to create a procedure that return a record?

From: Patrick Flahan <flahan_at_southeast.net>
Date: 1998/06/14
Message-ID: <6m18tv$c9f@news.southeast.net>#1/1

One way to do it something like this.

create or replace package RecordFetchPkg is
  Cursor curGetEmpDeptData (param_EmpID NUMBER)     is

      SELECT emp.*, dept.*
          FROM emp, dept
       WHERE emp.deptid = dept.deptid
             AND emp.empid = param_EmpID;

  recGetEmpDeptData curGetEmpDeptData%rowtype;

  procedure GetRowProc(param_EmpID IN NUMBER);

  FUNCTION GetRowFunc(param_EmpID IN NUMBER)     return recGetEmpDeptData;

end RecordFetchPkg;

---

--
create or replace package body RecordFetchPkg
is
  procedure GetRowProc(param_EmpID IN NUMBER)
    is
  begin
    open curGetEmpDeptData(param_EmpID);
    fetch curGetEmpDeptData into recGetEmpDeptData;
    close curGetEmpDeptData;
  end GetRowProc;

  --OR ------------------------------------------------

  FUNCTION GetRowFunc(param_EmpID IN NUMBER)
    return recGetEmpDeptData
    is
  begin

    open curGetEmpDeptData(param_EmpID);
    fetch curGetEmpDeptData into recGetEmpDeptData;
    close curGetEmpDeptData;
    return recGetEmpDeptData ;
  end GetRowFunc;
end RecordFetchPkg;
----------------------------------------------------------------------------
--------------------
And then you would declare a variable of type recGetEmpDeptData;

DECLARE
  recReturn RecordFetchPkg.recGetEmpDeptData%type;
begin
  RecordFetchPkg.GetRowProc(11);

  recReturn := RecordFetchPkg.GetRowFunc(11);

end;

This is kind of quick and rough but I hope this helps.

Patrick Flahan
flahan_at_leading.net




Joel wrote in message ...


>What should i  do to return a record from a procedure or fonction or what

>ever..

>

>A query like this :

>

>SELECT emp.*, dept.*

>FROM emp, dept

>WHERE emp.deptid = dept.deptid

>AND emp.empid = param_EmpID

>

>

>param_EmpID is a IN var that a pass to the procedure.

>

>How can a do that?

>

>

Received on Sun Jun 14 1998 - 00:00:00 CDT