Re: Select question

From: Mark D Powell <markp7832_at_my-deja.com>
Date: Mon, 21 Aug 2000 19:32:00 GMT
Message-ID: <8ns03f$4lm$1@nnrp1.deja.com>

In article <39A165A8.B397A7F3_at_accesscomm.ca>,   wcis_at_accesscomm.ca wrote:
> Hi,
>
> Would you please let me know if there is a way that I could use
> SELECT on a CURSOR. Say I have a cursor declared as follows:
>
> cursor CUSTOMER_CUR IS
> SELECT name, phone_num
> FROM customer;
> ......
>
> I want to be able to select from this cursor without looping though
 it.
> Can I use something like this:
>
> SELECT CUSTOMER_CUR.NAME
> FROM (DUAL??)
>
> Any tip would be appreciated
>
> Thanks
>

Depending what you want you can do one of the following:

open  customer_cur;
fetch customer_cur into variable;
close customer_cur;

But this will only give you access to the first row returned from the cursor which may not be what you want.

If you want all the rows from the cursor at one time then maybe a ref cursor will work for you. I believe this example comes from Oracle:

create or replace package body name_pck is

      procedure get_ednames (maxdeptno in number, a in out ecurtype) as
        begin
        open a for
          select   ename, dname
            from   emp, dept
           where   emp.deptno = dept.deptno
             and   emp.deptno = maxdeptno
          order by ename;
        end;

    end;
    /

    SQL> variable b refcursor;
    SQL> column ename heading Name
    SQL> column dname heading Department
    SQL> execute name_pck.get_ednames(30, :b)

       PL/SQL procedure successfully completed.

       Name        Department
       ---------- --------------
       ALLEN       SALES
       BLAKE       SALES
       JAMES       SALES
       MARTIN      SALES
       TURNER      SALES
       WARD        SALES

--
Mark D. Powell  -- The only advice that counts is the advice that
 you follow so follow your own advice --


Sent via Deja.com http://www.deja.com/
Before you buy.

Received on Mon Aug 21 2000 - 14:32:00 CDT