Re: Oracle stored procs

From: Garry Lawton <Garry.Lawton_at_uibk.ac.at>
Date: 1997/07/01
Message-ID: <5par76$1q6$1@dm2.uibk.ac.at>#1/1

In article <01bc8596$de5a26d0$c47b6496_at_kosh>, dwetzel1_at_rodalepress.com says...
>
>Okay, I'm confused... I want to write a stored proc that does something
>similar to this:
>
>create proc show_dept(dept_id number)
>is
>begin
> select emp, job, sal
> from emp
> where deptno= dept_id;
>end;
>
>In other words, I want to run a report that returns any number of rows
>based on inputted data.
>
>It's beginning to look to me like Oracle can only handle sp's that deal
>with one and only one row. That can't be right, I must be doing something
>wrong.

 You sure are!

 CREATE OR REPLACE PROCEDURE show_dept(dept_id_in IN NUMBER) IS

    CURSOR emp_cur IS SELECT ename, job, sal

                      FROM emp
                      WHERE deptno = dept_id_in;

 BEGIN     FOR emp_rec IN emp_cur LOOP

       DBMS_OUTPUT.PUT_LINE(emp_rec.ename||' '||
                            emp_rec.job||' '||
                            emp_rec.sal);

    END LOOP;  END show_dept;

 Regards Garry  

---

Garry Lawton                                      Tel: 43-512-507-2314
EDV-Zentrum der Universitaet Innsbruck            Fax: 43-512-507-2944
Technikerstr. 13 
6020 Innsbruck, Austria                e-mail: garry.lawton_at_uibk.ac.at
----------------------------------------------------------------------

Received on Tue Jul 01 1997 - 00:00:00 CDT