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

Hi,

ever looked at REF CURSOR?

SAMPLE (comes from C.Czernek):  

CREATE OR REPLACE PACKAGE testrowtype  

IS

        CURSOR ccc   
                IS  
                        SELECT   
                                ename, 
                                job, 
                                mgr, 
                                dname, 
                                loc 
                                                                          
                        FROM  
                                emp,  
                                dept  
                        WHERE  
                                emp.deptno=dept.deptno  
                        ;  
        type DLCur is ref cursor return ccc%rowtype;   
        PROCEDURE GetDLData(p_DLNO IN NUMBER,p_DLCursor in out DLCur) ;  
 

END;
/   

CREATE OR REPLACE PACKAGE BODY testrowtype IS

        PROCEDURE GetDLData(p_DLNO IN NUMBER,p_DLCursor in out DLCur)   
        IS   
        BEGIN   
   
        OPEN p_DLCursor FOR  
                        SELECT   
                                ename, 
                                job, 
                                mgr, 
                                dname, 
                                loc 
                        FROM  
                                emp,  
                                dept  
                        WHERE  
                                emp.deptno=dept.deptno;  
                         
        END;   
 

END;
/  

Joel schrieb:
>
> 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?

--

Regards

Matthias Gresz    :-)

GreMa_at_t-online.de