Re: PL/SQL how-to procedure

chris
as mjc wrote using a cursor is very useful when useing select statements. when useing a cursor you can retrieve multiple rows from a table(avoiding having to handle a too_many_rows exception). you can either cycle through the cursor (illustrated by mjc) or use fetch statements like
OPEN cursor_name;
FETCH cursor_name INTO variable;
/* pl/sql code here */
CLOSE cursor_name;

some other useful tools with cursors are built in tests on the condition of the cursor. the two most useful ones I found are FOUND and ROWCOUNT - I think these features are only availible in version 2.3 - but here are some examples.

ROWCOUNT returns the number of rows that have been fetched from the cursor. FOUND true if the last FETCH was successful, false if unsuccessful.

DECLARE
  CURSOR cursor_name IS SELECT * FROM my_table;   current_row my_table%ROWTYPE;
  number_of_rows integer;
BEGIN
  OPEN cursor_name;
  FETCH cursor_name INTO current_row;
  IF cursor_name%FOUND THEN
    /* What to do if a row is found */
  END IF;
  number_of_rows := cursor_name%ROWCOUNT;   CLOSE cursor_name;
END; Hope this helps
HBJ mjc (cavett_at_globalnet.co.uk) wrote:
: Hi Chris -
 

: Can you please say what you want your procedure to do, as it's difficult to
: help out otherwise.
 

: Working 'blind' though: What you can use, is a cursor that effectively
: selects back rows from your table and you can then use that data as you
: wish - if you use a CURSOR FOR LOOP this does an implicit fetch and handles
: opening and closing of cursors which is neat! e.g.
 

: CREATE OR REPLACE PROCEDURE sel_table1
: IS
: DECLARE
: CURSOR c1 IS
: SELECT *
: FROM table1;
: col1_tot NUMBER := NULL;
: BEGIN
: FOR c1_cur IN c1 LOOP
: /*
: PL/SQL manipulation here
: where you can reference each tables column
: using the 'c1_cur' reference. . .
: e.g. col1_tot := col1_tot+c1_cur.col1
: */
: END LOOP;
: EXCEPTION
: /*
: exception handling
: */
: END;
: /
 

: Please email me if you need further assistance and I'll try help out!
 

: Regards
:
: - Martyn Cavett
: Senior ORACLE Analyst
: LGT Asset Management
: London, England.
: home: cavett_at_globalnet.co.uk
:

: CG <christian.gregory_at_citicorp.com> wrote in article
: <333C8093.19A5_at_citicorp.com>...
: > ...Can anyone tell me if it is possible to create a procedure
: > with a simple 'select' statement ?... ie. select * from table_a;
: >
: > this select statement will not have an into clause.
: >
: > the reason I ask is that I can't seem to be able to create a procedure
: > unless I have that into clause...
: >
: > Any help is appreciated .... chris
: >

--
I am the terry cloth God!!!!        cpark_at_osprey.unf.edu
Bow down and wash with me!!!!       djfly_at_faraday.ee.unf.edu
                                    cpark_at_resourcecenter.com