Re: Cursing over cursors!

From: Tony Noble <cz0763%zip02_at_mt.gov>
Date: 1995/07/20
Message-ID: <3um6ik$8qo_at_umt.umt.edu>#1/1

In article <3ugs04$ai3_at_knot.queensu.ca>, 3srf_at_qlink.queensu.ca says...
>
>Hello:
>
>I'm new to PL/SQL, and am having a little difficulty with cursors. Now
>that I finally understand them, I need a little help writing one.
>
>I want to do calculations in my select statement inside the cursor, and
>have the results placed in my target variables. In straight PL/SQL,
>I would have something like:
>
>select to_date(exception_start_date,'YYMMDD')
>into start
>from absence_exception_table;
>
>Of course, you can't do an "INTO" in cursors. I've also tried:
>
>select to_date(exception_start_date,'YYMMDD') start
>from absence_exception_table;
>
>...but I got an error for this too.
>
>What is the proper way of doing this? I've only got access to Oracle's
>PL/SQL User's Guide & Reference, and this manual is worse than the
>famous cruddy manuals for Borland products!
>
>Any information would be appreciated. Thanks in advance!
>
>-----------------------------------+------------------------------------

---

>Steve Frampton | Phone: (613) 547-5987, ext. 312
 or 331
>Computer Operator/Systems Clerk | Internet:
 frampton_at_admin.flarc.edu.on.ca
>Frontenac-Lennox & Addington RCSSB | DECNET: FLASSB::FRAMPTON
>-----------------------------------+------------------------------------

---

Whoa Steve! Relax!

To use a cursor you have to follow 4 basic steps:

1. Declare the cursor
2. Open the cursor
3. Fetch your information
4. Close the cursor

so you would have a PL/SQL block something like:

Declare
  Cursor C1 is
    Select to_date(exception_start_date,'YYMMDD')     From absence_exception_table;
    start date;
Begin
  Open C1;
  Fetch C1 into start;
  Close C1;
End;

This block could go into a procedure, a package or a trigger. There is a 'fairly good' description in the 'PL/SQL User's Guide and Reference' on pages 4-13 through 4-22 of my manual, dated December 1992, part number 800-20-1292, version 2.0.

Hope this helps,
Tony Noble
cz0763%zip02_at_mt.gov Received on Thu Jul 20 1995 - 00:00:00 CEST