Re: Oracle stored procs

On 7 Jul 1997, Nathan Hughes wrote:

> 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 must define a cursor to hold your return value set, for queries that
> return more than one row. (It's actually a good idea to always use a
> cursor for your queries in PL/SQL). Read the app dev manual or get
> Feurstein's book, but basically
>
> create proc show_dept(dept_id number)
> is
>
> CURSOR emp_cur is
> select emp, job, sal from emp
> where deptno = dept_id;
>
> emp_rec emp_cur%ROWTYPE;
>
> BEGIN
> open emp_cur;
> fetch emp_cur into emp_rec;
> WHILE emp_cur%FOUND
> LOOP
> -- processing of emp_rec variable, whatever.
> fetch emp_cur into emp_rec;
> END;
> END;
>
> Hope this helps.
>
> --
> /(o\ Nathan D. Hughes
> \o)/ nhughes_at_umich.edu
>
>

Hi,
It seems u want to return multiple rows from stored procedure. This is supported from Oracle7.3 onwards and u have make use return cursor.
Hope this helps u
regards
Ragun Received on Wed Jul 09 1997 - 00:00:00 CDT