Re: please help! oracle stored procedure and jdbc

From: <victorme_at_my-deja.com>
Date: Wed, 03 Nov 1999 08:58:44 GMT
Message-ID: <7votg3$fkt$1@nnrp1.deja.com>

In article <4iCT3.18$Nn5.500_at_client>,
  "Gary T. Mui" <gmui_at_prescientmarkets.com> wrote:
> I am getting desperate in trying to implement something that I have
not been
> able to figure out (I have only used sybase till now) and have not
gotten
> much help from a couple Oracle DBA's.
>
> Is it possible to create an oracle stored procedure that will simply
allow
> me to query for multiple rows? I.e. can I execute a stored procedure
that
> might have a few parameters but basically does a 'select c1, c2, c3
from x'?
> I'm looking for the oracle equivalent of this sybase procedure:
>
> create proc selectX as
> {
> select c1, c2, c3 from x;
> }
>
> I understand that I will probably need to use cursors somehow, but I'm
new
> to oracle and haven't found a single example anywhere that can do
this. Is
> this possible?
>
> The second thing is that I want to call these procs from a java app
using
> JDBC.
>
> Any help would be greatly, greatly appreciated.
>
> Thanks,
> Gary Mui
> gmui_at_prescientmarkets.com
>
>

You can

        declare C1 Cursor
        as select a1,b1,c1 from x;

        declare SumA1 number(10);

BEGIN
  SumA1 := 0;
/* For every record */

  FOR C1rec in C1 LOOP

      SumA1 := SumA1 + C1rec.a1    /* C1rec.a1,C1rec.b1,C1rec.c1 current
                                      record values*/

  END LOOP; END; In Oracle Strored Procedure you can not fetch information to the default output , you can select information into the variables, if you know,that only one record will be selected or work with loop.

declare a number(10);
begin
  select a1
  into a
  from x;
end;

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Nov 03 1999 - 02:58:44 CST